int main(void) { OCI_Connection *cn; OCI_Statement *st; OCI_Resultset *rs; if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT)) return EXIT_FAILURE; cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT); st = OCI_StatementCreate(cn); OCI_Prepare(st, "update products set code = code+10 returning code into :i"); OCI_RegisterInt(st, ":i"); OCI_Execute(st); rs = OCI_GetResultset(st); while (OCI_FetchNext(rs)) printf("%i\n", OCI_GetInt(rs, 1)); printf("count : %i\n", OCI_GetRowCount(rs)); OCI_Commit(cn); OCI_Cleanup(); return EXIT_SUCCESS; }
static HB_ERRCODE ocilibExecute( SQLDDCONNECTION * pConnection, PHB_ITEM pItem ) { OCI_Statement * st = OCI_StatementCreate( ( ( SDDCONN * ) pConnection->pSDDConn )->pConn ); void * hStatement; char * szError; HB_ERRCODE errCode; if( ! st ) { szError = ocilibGetError( &errCode ); hb_errRT_OCIDD( EG_OPEN, ESQLDD_STMTALLOC, szError, hb_itemGetCPtr( pItem ), errCode ); hb_xfree( szError ); return HB_FAILURE; } if( OCI_ExecuteStmt( st, M_HB_ITEMGETSTR( pItem, &hStatement, NULL ) ) ) { hb_strfree( hStatement ); /* TODO: new id */ hb_rddsqlSetError( 0, NULL, hb_itemGetCPtr( pItem ), NULL, ( unsigned long ) OCI_GetAffectedRows( st ) ); OCI_StatementFree( st ); return HB_SUCCESS; } else hb_strfree( hStatement ); szError = ocilibGetError( &errCode ); hb_rddsqlSetError( errCode, szError, hb_itemGetCPtr( pItem ), NULL, 0 ); hb_xfree( szError ); OCI_StatementFree( st ); return HB_FAILURE; }
int main(void) { OCI_Connection *cn; OCI_Statement *st; int code; if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT)) return EXIT_FAILURE; cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT); st = OCI_StatementCreate(cn); OCI_Prepare(st, "delete from test_fetch where code = :code"); OCI_BindInt(st, ":code", &code); code = 5; OCI_Execute(st); code = 12; OCI_Execute(st); OCI_Commit(cn); OCI_Cleanup(); return EXIT_SUCCESS; }
int main(void) { OCI_Connection *cn; OCI_Statement *st; int res = 0; if (!OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT)) { return EXIT_FAILURE; } cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT); st = OCI_StatementCreate(cn); /* pl/sql call */ OCI_Prepare(st, "begin :res := trunc(sysdate+1)-trunc(sysdate-1); end;"); OCI_BindInt(st, ":res", &res); OCI_Execute(st); printf("result : %i\n", res); OCI_StatementFree(st); OCI_ConnectionFree(cn); OCI_Cleanup(); return EXIT_SUCCESS; }
void Statement::createOciStatement(Connection *connection) { this->connection=connection; ociStmt = OCI_StatementCreate(connection->getOciConnection()); DbUtil::checkForOciError(this); OCI_AllowRebinding(ociStmt, true); }
int main(void) { OCI_Connection *cn; OCI_Statement *st; OCI_Resultset *rs; char rowid[OCI_SIZE_ROWID + 1] = ""; if (!OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT)) { return EXIT_FAILURE; } cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT); st = OCI_StatementCreate(cn); OCI_Immediate(cn, "select rowid from products where code = 1", OCI_ARG_TEXT, rowid); OCI_Prepare(st, "select code, name, rowid from products where rowid = :id"); OCI_BindString(st, ":id", rowid, (unsigned int) strlen(rowid)); OCI_Execute(st); rs = OCI_GetResultset(st); OCI_FetchNext(rs); printf("code [%d], name [%s], rowid [%s]", OCI_GetInt(rs, 1), OCI_GetString(rs, 2), OCI_GetString(rs, 3)); OCI_StatementFree(st); OCI_ConnectionFree(cn); OCI_Cleanup(); return EXIT_SUCCESS; }
double get_estoque(requisicao_estoque *req) { double saldo; OCI_Statement *stmt = OCI_StatementCreate(oracledb); check_ociliberror(stmt); OCI_Prepare(stmt, "select sum(SALD_CTR) from table(saldo_inicial_tipoest(:emp, :est, :prod, :data, 'S', null, 0))"); OCI_BindShort(stmt, ":emp", &req->empresa); OCI_BindShort(stmt, ":est", &req->controle); OCI_BindString(stmt, ":prod", req->produto, 0); OCI_Date *d = OCI_DateCreate(NULL); OCI_DateSetDate(d, req->data_ano, req->data_mes, req->data_dia); OCI_BindDate(stmt, ":data", d); check_ociliberrorb(OCI_Execute(stmt)); OCI_Resultset *rs = OCI_GetResultset(stmt); check_ociliberror(rs); if (OCI_FetchNext(rs)) { saldo = OCI_GetDouble(rs, 1); } else { saldo = 0.0; } OCI_FreeStatement(stmt); return saldo; }
int main(void) { OCI_Connection *cn; OCI_Statement *st; OCI_Error *err; int tab_int[1000]; char tab_str[1000][21]; int i; if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT | OCI_ENV_CONTEXT)) return EXIT_FAILURE; /* ... create connection and statement ... */ cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT); st = OCI_StatementCreate(cn); /* binding */ OCI_Prepare(st, "insert into products values(:i, :s)"); OCI_BindArraySetSize(st, 1000); OCI_BindArrayOfInts(st, ":i", (int*) tab_int, 0); OCI_BindArrayOfStrings(st, ":s", (char*) tab_str, 20, 0); /* filling arrays */ for(i=0;i<1000;i++) { tab_int[i] = i+1; sprintf(tab_str[i],"Name %d",i+1); } /* execute */ if (!OCI_Execute(st)) { printf("Number of DML array errors : %d\n", OCI_GetBatchErrorCount(st)); err = OCI_GetBatchError(st); while (err) { printf("Error at row %d : %s\n", OCI_ErrorGetRow(err), OCI_ErrorGetString(err)); err = OCI_GetBatchError(st); } } printf("row processed : %d\n", OCI_GetAffectedRows(st)); OCI_Commit(cn); OCI_Cleanup(); return EXIT_SUCCESS; }
int ociw_query_result(db_wrap * self, char const * sql, size_t len, db_wrap_result ** tgt) { CONN_DECL(DB_WRAP_E_BAD_ARG); if (! sql || !*sql || !len || !tgt) return DB_WRAP_E_BAD_ARG; OCI_Statement * st = OCI_StatementCreate(ociconn); if (! st) { lerr("Creation of OCI_Statement failed.\n"); return DB_WRAP_E_CHECK_DB_ERROR; } db_wrap_result * wres = (db_wrap_result*)malloc(sizeof(db_wrap_result)); if (! wres) return DB_WRAP_E_ALLOC_ERROR; *wres = ociw_res_empty; ociw_result_impl * impl = (ociw_result_impl*)malloc(sizeof(ociw_result_impl)); if (! impl) { OCI_StatementFree(st); free(wres); return DB_WRAP_E_ALLOC_ERROR; } *impl = ociw_result_impl_empty; impl->st = st; wres->impl.data = impl; #if 1 if (! OCI_ExecuteStmt(st, sql)) { wres->api->finalize(wres); /* i don't quite know why, but fetching the OCI error state after this returns a 0 error code and empty error string. */ return DB_WRAP_E_CHECK_DB_ERROR; } #else if (! //OCI_ExecuteStmt(st, sql) OCI_Prepare(st, sql) ) { lerr("Preparation of OCI_Statement failed: [%s]\n",OCI_GetSql(st)); wres->api->finalize(wres); return DB_WRAP_E_CHECK_DB_ERROR; } if (! OCI_Execute(st)) { lerr("Execution of prepared OCI_Statement failed: [%s]\n",OCI_GetSql(st)); wres->api->finalize(wres); return DB_WRAP_E_CHECK_DB_ERROR; } #endif impl->result = OCI_GetResultset(st) /* MIGHT be null - the docs are not really clear here what happens on an empty result set. */ ; *tgt = wres; return 0; }
boolean OCI_API OCI_QueueStart ( OCI_Connection *con, const otext *queue_name, boolean enqueue, boolean dequeue ) { OCI_Statement *st = NULL; OCI_LIB_CALL_ENTER(boolean, FALSE) OCI_CHECK_PTR(OCI_IPC_CONNECTION, con) OCI_CHECK_PTR(OCI_IPC_STRING, queue_name) st = OCI_StatementCreate(con); if (st) { call_status = OCI_Prepare ( st, OTEXT("DECLARE ") OTEXT(" v_enqueue BOOLEAN := FALSE; ") OTEXT(" v_dequeue BOOLEAN := FALSE; ") OTEXT("BEGIN ") OTEXT(" IF (:enqueue = 1) then ") OTEXT(" v_enqueue := TRUE; ") OTEXT(" END IF; ") OTEXT(" IF (:dequeue = 1) then ") OTEXT(" v_dequeue := TRUE; ") OTEXT(" END IF; ") OTEXT(" DBMS_AQADM.START_QUEUE ") OTEXT(" (") OTEXT(" queue_name => :queue_name, ") OTEXT(" enqueue => v_enqueue, ") OTEXT(" dequeue => v_dequeue ") OTEXT(" ); ") OTEXT("END; ") ); call_status = call_status && OCI_BindString(st, OTEXT(":queue_name"), (otext *) queue_name, 0); call_status = call_status && OCI_BindInt(st, OTEXT(":enqueue"), &enqueue); call_status = call_status && OCI_BindInt(st, OTEXT(":dequeue"), &dequeue); call_status = call_status && OCI_Execute(st); OCI_StatementFree(st); } call_retval = call_status; OCI_LIB_CALL_EXIT() }
boolean OCI_API OCI_QueueAlter ( OCI_Connection *con, const otext *queue_name, unsigned int max_retries, unsigned int retry_delay, unsigned int retention_time, const otext *comment ) { OCI_Statement *st = NULL; OCI_LIB_CALL_ENTER(boolean, FALSE) OCI_CHECK_PTR(OCI_IPC_CONNECTION, con) OCI_CHECK_PTR(OCI_IPC_STRING, queue_name) st = OCI_StatementCreate(con); if (st) { call_status = OCI_Prepare ( st, OTEXT("BEGIN ") OTEXT(" DBMS_AQADM.ALTER_QUEUE ") OTEXT(" (") OTEXT(" queue_name => :queue_name, ") OTEXT(" max_retries => :max_retries, ") OTEXT(" retry_delay => :retry_delay, ") OTEXT(" retention_time => :retention_time, ") OTEXT(" comment => :comment ") OTEXT(" ); ") OTEXT("END; ") ); call_status = call_status && OCI_BindString(st, OTEXT(":queue_name"), (otext *) queue_name, 0); call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":max_retries"), &max_retries); call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":retry_delay"), &retry_delay); call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":retention_time"), &retention_time); call_status = call_status && OCI_BindString(st, OTEXT(":comment"), (otext *) (comment ? comment : OCI_STRING_EMPTY), 0); call_status = call_status && OCI_Execute(st); OCI_StatementFree(st); } call_retval = call_status; OCI_LIB_CALL_EXIT() }
int main(void) { OCI_Connection *cn; OCI_Statement *st; OCI_Resultset *rs; product_t prd; product_ind_t ind; char buf[100]; int i = 0; if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT)) return EXIT_FAILURE; cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT); st = OCI_StatementCreate(cn); OCI_ExecuteStmt(st, "select * from products"); rs = OCI_GetResultset(st); OCI_SetStructNumericType(rs, 1, OCI_NUM_INT); OCI_SetStructNumericType(rs, 3, OCI_NUM_DOUBLE); while (OCI_FetchNext(rs)) { i++; OCI_GetStruct(rs, &prd, &ind); OCI_DateToText(prd.creation, "DD-MM-YYYY", 100, buf); printf("row #%d \n" "...prd.code : %d \n" "...prd.name : %s \n" "...prd.price : %g \n" "...prd.creation : %s \n" " \n", i, prd.code, prd.name, prd.price, buf ); } printf("\n\n%d row(s) fetched\n", OCI_GetRowCount(rs)); OCI_Cleanup(); return EXIT_SUCCESS; }
boolean OCI_API OCI_QueueTableAlter ( OCI_Connection *con, const otext *queue_table, const otext *comment, unsigned int primary_instance, unsigned int secondary_instance ) { OCI_Statement *st = NULL; OCI_LIB_CALL_ENTER(boolean, FALSE) OCI_CHECK_PTR(OCI_IPC_CONNECTION, con) OCI_CHECK_PTR(OCI_IPC_STRING, queue_table) st = OCI_StatementCreate(con); if (st) { call_status = OCI_Prepare ( st, OTEXT("BEGIN ") OTEXT(" DBMS_AQADM.ALTER_QUEUE_TABLE ") OTEXT(" (") OTEXT(" queue_table => :queue_table, ") OTEXT(" comment => :comment, ") OTEXT(" primary_instance => :primary_instance, ") OTEXT(" secondary_instance => :secondary_instance ") OTEXT(" ); ") OTEXT("END; ") ); call_status = call_status && OCI_BindString(st, OTEXT(":queue_table"), (otext *) queue_table, 0); call_status = call_status && OCI_BindString(st, OTEXT(":comment"), (otext *) (comment ? comment: OCI_STRING_EMPTY), 0); call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":primary_instance"), &primary_instance); call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":secondary_instance"), &secondary_instance); call_status = call_status && OCI_Execute(st); OCI_StatementFree(st); } call_retval = call_status; OCI_LIB_CALL_EXIT() }
int main(void) { OCI_Connection *cn; OCI_Statement *st; OCI_Resultset *rs; OCI_Lob *lob1, *lob2; char temp[SIZE_BUF+1]; int code, n; if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT)) return EXIT_FAILURE; cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT); st = OCI_StatementCreate(cn); OCI_ExecuteStmt(st, "select code, content from test_lob for update"); rs = OCI_GetResultset(st); while (OCI_FetchNext(rs)) { code = OCI_GetInt(rs, 1); lob1 = OCI_GetLob(rs, 2); lob2 = OCI_LobCreate(cn, OCI_CLOB); n = OCI_LobWrite(lob1, "Today, ", 7); OCI_LobSeek(lob1, n, OCI_SEEK_SET); n = OCI_LobWrite(lob2, "I'm going to the cinema !", 25); OCI_LobAppendLob(lob1, lob2); OCI_LobSeek(lob1, 0, OCI_SEEK_SET); n = OCI_LobRead(lob1, temp, SIZE_BUF); temp[n] = 0; printf("code: %i, action : %s\n", code, temp); OCI_LobFree(lob2); } printf("\n%d row(s) fetched\n", OCI_GetRowCount(rs)); OCI_Cleanup(); return EXIT_SUCCESS; }
boolean OCI_API OCI_QueueTableDrop ( OCI_Connection *con, const otext *queue_table, boolean force ) { OCI_Statement *st = NULL; OCI_LIB_CALL_ENTER(boolean, FALSE) OCI_CHECK_PTR(OCI_IPC_CONNECTION, con) OCI_CHECK_PTR(OCI_IPC_STRING, queue_table) st = OCI_StatementCreate(con); if (st) { call_status = OCI_Prepare ( st, OTEXT("DECLARE ") OTEXT(" v_force BOOLEAN := FALSE; ") OTEXT("BEGIN ") OTEXT(" IF (:force = 1) then ") OTEXT(" v_force := TRUE; ") OTEXT(" END IF; ") OTEXT(" DBMS_AQADM.DROP_QUEUE_TABLE ") OTEXT(" (") OTEXT(" queue_table => :queue_table, ") OTEXT(" force => v_force ") OTEXT(" ); ") OTEXT("END; ") ); call_status = call_status && OCI_BindString(st, OTEXT(":queue_table"), (otext *)queue_table, 0); call_status = call_status && OCI_BindInt(st, OTEXT(":force"), &force); call_status = call_status && OCI_Execute(st); OCI_StatementFree(st); } call_retval = call_status; OCI_LIB_CALL_EXIT() }
void long_oracle_call(void *data) { OCI_Statement *st = OCI_StatementCreate((OCI_Connection *) data); OCI_Resultset *rs; /* execute a query that takes a long time to process */ OCI_ExecuteStmt(st, "select code, content from huge_table"); rs = OCI_GetResultset(st); while (OCI_FetchNext(rs)) { printf("%i - %s", OCI_GetInt(rs, 1), OCI_GetString(rs, 2)); } SetEvent(evt); }
int main(void) { OCI_Connection *cn; OCI_Statement *st; OCI_Object *racing_car; OCI_TypeInfo *vehicule_type_inf; OCI_TypeInfo *car_type_inf; OCI_TypeInfo *racing_car_type_inf; char buffer[512]; unsigned int size = sizeof(buffer) - 1; if (!OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT)) { return EXIT_FAILURE; } cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT); st = OCI_StatementCreate(cn); racing_car = OCI_ObjectCreate(cn, OCI_TypeInfoGet(cn, "racing_car_type", OCI_TIF_TYPE)); OCI_Prepare(st, "begin :obj := racing_car_type(1,'Formula1', 123456789, 300); end;"); OCI_BindObject(st, "obj", racing_car); OCI_Execute(st); OCI_ObjectToText(racing_car, &size, buffer); printf("Object => %s\n", buffer); racing_car_type_inf = OCI_ObjectGetTypeInfo(racing_car); car_type_inf = OCI_TypeInfoGetSuperType(racing_car_type_inf); vehicule_type_inf = OCI_TypeInfoGetSuperType(car_type_inf); printf("Is type '%s' final => %d\n", OCI_TypeInfoGetName(racing_car_type_inf), OCI_TypeInfoIsFinalType(racing_car_type_inf)); printf("Is type '%s' final => %d\n", OCI_TypeInfoGetName(car_type_inf), OCI_TypeInfoIsFinalType(car_type_inf)); printf("Is type '%s' final => %d\n", OCI_TypeInfoGetName(vehicule_type_inf), OCI_TypeInfoIsFinalType(vehicule_type_inf)); OCI_ObjectFree(racing_car); OCI_StatementFree(st); OCI_ConnectionFree(cn); OCI_Cleanup(); return EXIT_SUCCESS; }
int main(void) { OCI_Connection *cn; OCI_Statement *st; OCI_Resultset *rs; int code = 1; char name[50]; if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT)) return EXIT_FAILURE; cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT); st = OCI_StatementCreate(cn); /* sql format with params ----------------------------------------------- */ OCI_ExecuteStmtFmt(st, "select article from test_fetch where code = %i", code); rs = OCI_GetResultset(st); while (OCI_FetchNext(rs)) printf("article : %s\n", OCI_GetString(rs, 1)); /* sql immediate (parse, exec, one fetch) ------------------------------- */ OCI_Immediate(cn, "select code, article from test_fetch where code = 1", OCI_ARG_INT, &code, OCI_ARG_TEXT, name); printf("article : %s - code %i\n", name, code); /* sql immediate (parse, exec, one fetch) with params ------------------- */ OCI_ImmediateFmt(cn, "select article from test_fetch where code = %i", code, OCI_ARG_TEXT, name); printf("article : %s\n", name); OCI_Cleanup(); return EXIT_SUCCESS; }
int main(void) { OCI_Connection *cn; OCI_Statement *st; OCI_Resultset *rs; OCI_Ref *ref; if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT)) return EXIT_FAILURE; cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT); st = OCI_StatementCreate(cn); OCI_ExecuteStmt(st, "select ref(e) from table_obj e"); rs = OCI_GetResultset(st); printf("\n\n=> fetch refs from object table\n\n"); while (OCI_FetchNext(rs)) { dump_ref(OCI_GetRef(rs, 1)); } printf("\n\n=> bind a local ref object to a PL/SQL statement\n\n"); ref = OCI_RefCreate(cn, OCI_TypeInfoGet(cn, "ARTICLE_T", OCI_TIF_TYPE)); OCI_Prepare(st, "begin " " select ref(e) into :r from table_obj e where e.id = 1; " "end; "); OCI_BindRef(st, ":r", ref); OCI_Execute(st); dump_ref(ref); OCI_RefFree(ref); OCI_Cleanup(); return EXIT_SUCCESS; }
boolean OCI_API OCI_QueueDrop ( OCI_Connection *con, const otext *queue_name ) { OCI_Statement *st = NULL; OCI_LIB_CALL_ENTER(boolean, FALSE) OCI_CHECK_PTR(OCI_IPC_CONNECTION, con) OCI_CHECK_PTR(OCI_IPC_STRING, queue_name) st = OCI_StatementCreate(con); if (st) { call_status = OCI_Prepare ( st, OTEXT("BEGIN ") OTEXT(" DBMS_AQADM.DROP_QUEUE ") OTEXT(" (") OTEXT(" queue_name => :queue_name ") OTEXT(" ); ") OTEXT("END; ") ); call_status = call_status && OCI_BindString(st, OTEXT(":queue_name"), (otext *) queue_name, 0); call_status = call_status && OCI_Execute(st); OCI_StatementFree(st); } call_retval = call_status; OCI_LIB_CALL_EXIT() }
int main(void) { OCI_Connection *cn; OCI_Statement *st; int i; if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT)) return EXIT_FAILURE; cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT); st = OCI_StatementCreate(cn); OCI_SetBindAllocation(st, OCI_BAM_INTERNAL); OCI_Prepare(st, "insert into test_array values (:tab)"); OCI_BindArraySetSize(st, NB_ELEMS); OCI_BindArrayOfDates(st, ":tab", NULL, 0); { OCI_Date ** tab = (OCI_Date **) OCI_BindGetData(OCI_GetBind(st, 1)); for (i=0; i < NB_ELEMS; i++) { OCI_DateSysDate(tab[i]); } OCI_Execute(st); } OCI_Commit(cn); OCI_Cleanup(); return EXIT_SUCCESS; }
int main(int argc, char **argv) { OCI_Connection *cn; OCI_Statement *st; OCI_Object *obj_sdo; OCI_Coll *coll_inf, *coll_ord; OCI_Elem *elem_inf, *elem_ord; OCI_TypeInfo *tif_inf, *tif_ord, *tif_sdo; int i; /* check command line */ if (argc < 3) { fprintf(stderr, "Usage: %s user password\n", argv[0]); return EXIT_FAILURE; } /* init OCILIB */ if (OCI_Initialize(error, NULL, OCI_ENV_DEFAULT)) { /* connect to oracle */ if (cn = OCI_ConnectionCreate(NULL, argv[1], argv[2], OCI_SESSION_DEFAULT)) { printf ("\nConnected to Oracle.\n"); /* retreive type info */ tif_sdo = OCI_TypeInfoGet(cn, "MDSYS.SDO_GEOMETRY", OCI_TIF_TYPE); tif_inf = OCI_TypeInfoGet(cn, "MDSYS.SDO_ELEM_INFO_ARRAY", OCI_TIF_TYPE); tif_ord = OCI_TypeInfoGet(cn, "MDSYS.SDO_ORDINATE_ARRAY", OCI_TIF_TYPE); /* create sdo object */ obj_sdo = OCI_ObjectCreate(cn, tif_sdo); /* create sub arrays */ coll_inf = OCI_CollCreate(tif_inf); coll_ord = OCI_CollCreate(tif_ord); /* create sub array element accessors */ elem_inf = OCI_ElemCreate(tif_inf); elem_ord = OCI_ElemCreate(tif_ord); /* build ordinates collection with test values */ for (i = 0; i < NB_ELEM; i++) { OCI_ElemSetDouble(elem_ord, (double) i); OCI_CollAppend(coll_ord, elem_ord); OCI_CollAppend(coll_ord, elem_ord); } /* setup information collection attribute 'starting_offset' */ OCI_ElemSetUnsignedInt(elem_inf, 1); OCI_CollAppend(coll_inf, elem_inf); /* setup information collection attribute 'element_type' */ OCI_ElemSetUnsignedInt(elem_inf, 1); OCI_CollAppend(coll_inf, elem_inf); /* setup information collection attribute 'interpretation' */ OCI_ElemSetUnsignedInt(elem_inf, 1); OCI_CollAppend(coll_inf, elem_inf); /* set sdo object member attributes */ OCI_ObjectSetInt(obj_sdo, "SDO_GTYPE", 4); OCI_ObjectSetNull(obj_sdo, "SDO_SRID"); OCI_ObjectSetNull(obj_sdo, "SDO_POINT"); OCI_ObjectSetColl(obj_sdo, "SDO_ELEM_INFO", coll_inf); OCI_ObjectSetColl(obj_sdo, "SDO_ORDINATES", coll_ord); /*create statement object */ st = OCI_StatementCreate(cn); /* prepare, bind and excute statement then commit*/ OCI_Prepare(st, "INSERT INTO test_insert (gid, geometry) VALUES (1, :sdo)"); OCI_BindObject(st, "sdo", obj_sdo); OCI_Execute(st); OCI_Commit(cn); /* free local objects */ OCI_ObjectFree(obj_sdo); OCI_CollFree(coll_inf); OCI_CollFree(coll_ord); OCI_ElemFree(elem_inf); OCI_ElemFree(elem_ord); } } /* disconnect from oracle and cleanup OCILIB */ OCI_Cleanup(); printf ("\nDisconnected from Oracle.\n"); return EXIT_SUCCESS; }
boolean OCI_API OCI_QueueCreate ( OCI_Connection *con, const otext *queue_name, const otext *queue_table, unsigned int queue_type, unsigned int max_retries, unsigned int retry_delay, unsigned int retention_time, boolean dependency_tracking, const otext *comment ) { OCI_Statement *st = NULL; OCI_LIB_CALL_ENTER(boolean, FALSE) OCI_CHECK_PTR(OCI_IPC_CONNECTION, con) OCI_CHECK_PTR(OCI_IPC_STRING, queue_name) OCI_CHECK_PTR(OCI_IPC_STRING, queue_table) OCI_CHECK_ENUM_VALUE(con, NULL, queue_type, QueueTypeValues, OTEXT("Queue type")) st = OCI_StatementCreate(con); if (st) { call_status = OCI_Prepare ( st, OTEXT("DECLARE ") OTEXT(" v_dependency_tracking BOOLEAN := FALSE; ") OTEXT("BEGIN ") OTEXT(" IF (:dependency_tracking = 1) then ") OTEXT(" v_dependency_tracking := TRUE; ") OTEXT(" END IF; ") OTEXT(" DBMS_AQADM.CREATE_QUEUE ") OTEXT(" (") OTEXT(" queue_name => :queue_name, ") OTEXT(" queue_table => :queue_table, ") OTEXT(" queue_type => :queue_type, ") OTEXT(" max_retries => :max_retries, ") OTEXT(" retry_delay => :retry_delay, ") OTEXT(" retention_time => :retention_time, ") OTEXT(" dependency_tracking => v_dependency_tracking, ") OTEXT(" comment => :comment ") OTEXT(" ); ") OTEXT("END; ") ); call_status = call_status && OCI_BindString(st, OTEXT(":queue_name"), (otext *) queue_name, 0); call_status = call_status && OCI_BindString(st, OTEXT(":queue_table"), (otext *) queue_table, 0); call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":queue_type"), &queue_type); call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":max_retries"), &max_retries); call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":retry_delay"), &retry_delay); call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":retention_time"), &retention_time); call_status = call_status && OCI_BindInt(st, OTEXT(":dependency_tracking"), &dependency_tracking); call_status = call_status && OCI_BindString(st, OTEXT(":comment"), (otext *) (comment ? comment : OCI_STRING_EMPTY), 0); call_status = call_status && OCI_Execute(st); OCI_StatementFree(st); } call_retval = call_status; OCI_LIB_CALL_EXIT() }
boolean OCI_API OCI_QueueTableCreate ( OCI_Connection *con, const otext *queue_table, const otext *queue_payload_type, const otext *storage_clause, const otext *sort_list, boolean multiple_consumers, unsigned int message_grouping, const otext *comment, unsigned int primary_instance, unsigned int secondary_instance, const otext *compatible ) { OCI_Statement *st = NULL; OCI_LIB_CALL_ENTER(boolean, FALSE) OCI_CHECK_PTR(OCI_IPC_CONNECTION, con) OCI_CHECK_PTR(OCI_IPC_STRING, queue_table) OCI_CHECK_PTR(OCI_IPC_STRING, queue_payload_type) OCI_CHECK_ENUM_VALUE(con, NULL, message_grouping, GroupingModeValues, OTEXT("Grouping mode")) st = OCI_StatementCreate(con); if (st) { call_status = OCI_Prepare ( st, OTEXT("DECLARE ") OTEXT(" v_multiple_consumers BOOLEAN := FALSE; ") OTEXT("BEGIN ") OTEXT(" IF (:multiple_consumers = 1) then ") OTEXT(" v_multiple_consumers := TRUE; ") OTEXT(" END IF; ") OTEXT(" DBMS_AQADM.CREATE_QUEUE_TABLE ") OTEXT(" (") OTEXT(" queue_table => :queue_table, ") OTEXT(" queue_payload_type => :queue_payload_type, ") OTEXT(" storage_clause => :storage_clause, ") OTEXT(" sort_list => :sort_list, ") OTEXT(" multiple_consumers => v_multiple_consumers, ") OTEXT(" message_grouping => :message_grouping, ") OTEXT(" comment => :comment, ") OTEXT(" primary_instance => :primary_instance, ") OTEXT(" secondary_instance => :secondary_instance, ") OTEXT(" compatible => :compatible") OTEXT(" ); ") OTEXT("END; ") ); call_status = call_status && OCI_BindString(st, OTEXT(":queue_table"), (otext *) queue_table, 0); call_status = call_status && OCI_BindString(st, OTEXT(":queue_payload_type"), (otext *) queue_payload_type, 0); call_status = call_status && OCI_BindString(st, OTEXT(":storage_clause"), (otext *) (storage_clause ? storage_clause : OCI_STRING_EMPTY), 0); call_status = call_status && OCI_BindString(st, OTEXT(":sort_list"), (otext *) (sort_list ? sort_list : OCI_STRING_EMPTY), 0); call_status = call_status && OCI_BindInt(st, OTEXT(":multiple_consumers"), &multiple_consumers); call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":message_grouping"), &message_grouping); call_status = call_status && OCI_BindString(st, OTEXT(":comment"), (otext *)( comment ? comment : OCI_STRING_EMPTY), 0); call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":primary_instance"), &primary_instance); call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":secondary_instance"), &secondary_instance); call_status = call_status && OCI_BindString(st, OTEXT(":compatible"), (otext *) (compatible ? compatible : OCI_STRING_EMPTY), 0); call_status = call_status && OCI_Execute(st); OCI_StatementFree(st); } call_retval = call_status; OCI_LIB_CALL_EXIT() }
static HB_ERRCODE ocilibOpen( SQLBASEAREAP pArea ) { OCI_Statement * st = OCI_StatementCreate( ( ( SDDCONN * ) pArea->pConnection->pSDDConn )->pConn ); OCI_Resultset * rs; SDDDATA * pSDDData; void * hQuery; HB_USHORT uiFields, uiIndex; PHB_ITEM pItemEof, pItem; HB_ERRCODE errCode; char * szError; HB_BOOL bError; pArea->pSDDData = memset( hb_xgrab( sizeof( SDDDATA ) ), 0, sizeof( SDDDATA ) ); pSDDData = ( SDDDATA * ) pArea->pSDDData; if( ! st ) { szError = ocilibGetError( &errCode ); hb_errRT_OCIDD( EG_OPEN, ESQLDD_STMTALLOC, szError, pArea->szQuery, errCode ); hb_xfree( szError ); return HB_FAILURE; } pItem = hb_itemPutC( NULL, pArea->szQuery ); if( ! OCI_ExecuteStmt( st, M_HB_ITEMGETSTR( pItem, &hQuery, NULL ) ) ) { hb_strfree( hQuery ); hb_itemRelease( pItem ); szError = ocilibGetError( &errCode ); OCI_StatementFree( st ); hb_errRT_OCIDD( EG_OPEN, ESQLDD_INVALIDQUERY, szError, pArea->szQuery, errCode ); hb_xfree( szError ); return HB_FAILURE; } else { hb_strfree( hQuery ); hb_itemRelease( pItem ); } rs = OCI_GetResultset( st ); uiFields = ( HB_USHORT ) OCI_GetColumnCount( rs ); SELF_SETFIELDEXTENT( ( AREAP ) pArea, uiFields ); pItemEof = hb_itemArrayNew( uiFields ); pItem = hb_itemNew( NULL ); #if 0 HB_TRACE( HB_TR_ALWAYS, ( "fieldcount=%d", iNameLen ) ); #endif errCode = 0; bError = HB_FALSE; for( uiIndex = 0; uiIndex < uiFields; ++uiIndex ) { DBFIELDINFO pFieldInfo; PHB_ITEM pName; OCI_Column * col = OCI_GetColumn( rs, uiIndex + 1 ); unsigned int uiDataType; unsigned int uiSize; int iDec; HB_BOOL bNullable; if( ! col ) { hb_itemRelease( pItemEof ); hb_itemRelease( pItem ); szError = ocilibGetError( NULL ); OCI_StatementFree( st ); hb_errRT_OCIDD( EG_OPEN, ESQLDD_STMTDESCR + 1001, szError, pArea->szQuery, 0 ); hb_xfree( szError ); return HB_FAILURE; } pName = D_HB_ITEMPUTSTR( NULL, OCI_ColumnGetName( col ) ); pFieldInfo.atomName = hb_itemGetCPtr( pName ); uiDataType = OCI_ColumnGetType( col ); uiSize = OCI_ColumnGetSize( col ); iDec = OCI_ColumnGetPrecision( col ); bNullable = ( HB_BOOL ) OCI_ColumnGetNullable( col ); if( bNullable ) pFieldInfo.uiFlags |= HB_FF_NULLABLE; pFieldInfo.uiLen = ( HB_USHORT ) uiSize; pFieldInfo.uiDec = ( HB_USHORT ) iDec; #if 0 HB_TRACE( HB_TR_ALWAYS, ( "field: name=%s type=%d len=%d dec=%d nullable=%d %d %d %d %d", pFieldInfo.atomName, uiDataType, uiSize, iDec, bNullable, OCI_ColumnGetScale( col ), OCI_ColumnGetPrecision( col ), OCI_ColumnGetFractionalPrecision( col ), OCI_ColumnGetLeadingPrecision( col ) ) ); #endif switch( uiDataType ) { case OCI_CDT_TEXT: pFieldInfo.uiType = HB_FT_STRING; break; case OCI_CDT_NUMERIC: pFieldInfo.uiType = HB_FT_LONG; /* For plain 'NUMERIC', precision is zero and scale is -127 */ if( OCI_ColumnGetPrecision( col ) > 0 ) pFieldInfo.uiLen = ( HB_USHORT ) OCI_ColumnGetPrecision( col ); if( OCI_ColumnGetScale( col ) >= 0 ) pFieldInfo.uiDec = ( HB_USHORT ) OCI_ColumnGetScale( col ); else pFieldInfo.uiDec = ( HB_USHORT ) hb_setGetDecimals(); break; case OCI_CDT_LONG: pFieldInfo.uiType = HB_FT_VARLENGTH; break; case OCI_CDT_RAW: pFieldInfo.uiType = HB_FT_BLOB; break; case OCI_CDT_DATETIME: case OCI_CDT_TIMESTAMP: case OCI_CDT_INTERVAL: pFieldInfo.uiType = HB_FT_TIME; break; default: #if 0 HB_TRACE( HB_TR_ALWAYS, ( "new sql type=%d", uiDataType ) ); #endif bError = HB_TRUE; errCode = ( HB_ERRCODE ) uiDataType; pFieldInfo.uiType = 0; pFieldInfo.uiType = HB_FT_STRING; break; } if( ! bError ) { switch( pFieldInfo.uiType ) { case HB_FT_STRING: { char * pStr = ( char * ) hb_xgrab( ( HB_SIZE ) pFieldInfo.uiLen + 1 ); memset( pStr, ' ', pFieldInfo.uiLen ); pStr[ pFieldInfo.uiLen ] = '\0'; hb_itemPutCLPtr( pItem, pStr, pFieldInfo.uiLen ); break; } case HB_FT_MEMO: case HB_FT_VARLENGTH: case HB_FT_BLOB: hb_itemPutC( pItem, NULL ); break; case HB_FT_INTEGER: hb_itemPutNI( pItem, 0 ); break; case HB_FT_LONG: if( pFieldInfo.uiDec == 0 ) hb_itemPutNLLen( pItem, 0, pFieldInfo.uiLen ); else hb_itemPutNDLen( pItem, 0.0, pFieldInfo.uiLen, pFieldInfo.uiDec ); break; case HB_FT_DOUBLE: hb_itemPutNDLen( pItem, 0.0, pFieldInfo.uiLen, pFieldInfo.uiDec ); break; case HB_FT_LOGICAL: hb_itemPutL( pItem, HB_FALSE ); break; case HB_FT_DATE: hb_itemPutDL( pItem, 0 ); break; case HB_FT_TIME: case HB_FT_TIMESTAMP: hb_itemPutTDT( pItem, 0, 0 ); break; default: hb_itemClear( pItem ); bError = HB_TRUE; } hb_arraySetForward( pItemEof, uiIndex + 1, pItem ); if( ! bError ) bError = ( SELF_ADDFIELD( ( AREAP ) pArea, &pFieldInfo ) == HB_FAILURE ); } hb_itemRelease( pName ); if( bError ) break; } hb_itemRelease( pItem ); if( bError ) { hb_itemRelease( pItemEof ); OCI_StatementFree( st ); hb_errRT_OCIDD( EG_CORRUPTION, ESQLDD_INVALIDFIELD, "Invalid field type", pArea->szQuery, errCode ); return HB_FAILURE; } pArea->ulRecCount = 0; pArea->ulRecMax = SQLDD_ROWSET_INIT; pArea->pRow = ( void ** ) hb_xgrab( SQLDD_ROWSET_INIT * sizeof( void * ) ); memset( pArea->pRow, 0, SQLDD_ROWSET_INIT * sizeof( void * ) ); pArea->pRowFlags = ( HB_BYTE * ) hb_xgrab( SQLDD_ROWSET_INIT * sizeof( HB_BYTE ) ); memset( pArea->pRowFlags, 0, SQLDD_ROWSET_INIT * sizeof( HB_BYTE ) ); pArea->pRow[ 0 ] = pItemEof; pArea->pRowFlags[ 0 ] = SQLDD_FLAG_CACHED; pSDDData->pStmt = st; return HB_SUCCESS; }
boolean block, unsigned int delivery_mode ) { OCI_LIB_CALL_ENTER(boolean, FALSE) OCI_CHECK_PTR(OCI_IPC_CONNECTION, con) OCI_CHECK_PTR(OCI_IPC_STRING, queue_table) OCI_CHECK_ENUM_VALUE(con, NULL, delivery_mode, DeliveryModeValues, OTEXT("Delivery mode")) if (con->ver_num >= OCI_10_1) { OCI_Statement *st = NULL; st = OCI_StatementCreate(con); if (st) { call_status = OCI_Prepare ( st, OTEXT("DECLARE ") OTEXT(" v_purge_options DBMS_AQADM.AQ$_PURGE_OPTIONS_T; ") OTEXT(" v_block BOOLEAN := FALSE; ") OTEXT("BEGIN ") OTEXT(" v_purge_options.block := FALSE; ") OTEXT(" v_purge_options.delivery_mode := :delivery_mode; ") OTEXT(" IF (:block = 1) then ") OTEXT(" v_purge_options.block := TRUE; ") OTEXT(" END IF; ")
int main(void) { OCI_Connection *cn; OCI_Statement *st; OCI_Resultset *rs; OCI_Coll *coll; OCI_Iter *iter; OCI_Elem *elem; OCI_TypeInfo *type; OCI_Object *obj; int i, n; if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT)) return EXIT_FAILURE; cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT); /* Varray binding -------------------------------------------------------- */ st = OCI_StatementCreate(cn); /* create the collection */ type = OCI_TypeInfoGet(cn, "Varray_type", OCI_TIF_TYPE); coll = OCI_CollCreate(type); /* bind the local collection to a PL/SQL procedure */ OCI_Prepare(st, "begin load_array(:array); end;"); OCI_BindColl(st, ":array", coll); OCI_Execute(st); /* the procedure has filled the collection and we can iterate it using an iterator */ iter = OCI_IterCreate(coll); elem = OCI_IterGetNext(iter); while (elem != NULL) { printf("value %s\n", OCI_ElemGetString(elem)); elem = OCI_IterGetNext(iter); } OCI_IterFree(iter); OCI_CollFree(coll); /* Varray SQL fetch ------------------------------------------------------- */ /* query on a table with varray column */ OCI_ExecuteStmt(st, "SELECT * from table_article"); rs = OCI_GetResultset(st); while (OCI_FetchNext(rs)) { /* iterate the collection using an iterator */ coll = OCI_GetColl(rs, 2); iter = OCI_IterCreate(coll); elem = OCI_IterGetNext(iter); printf("article #%d\n", OCI_GetInt(rs, 1)); while (elem != NULL) { obj = OCI_ElemGetObject(elem); printf(".... code %d, name%s \n", OCI_ObjectGetInt(obj, "ID"), OCI_ObjectGetString(obj, "NAME")); elem = OCI_IterGetNext(iter); } OCI_IterFree(iter); } /* Nested table fetch ------------------------------------------------------- */ /* query on a table with nested table column */ OCI_ExecuteStmt(st, "SELECT * from table_sales"); rs = OCI_GetResultset(st); while (OCI_FetchNext(rs)) { coll = OCI_GetColl(rs, 2); printf("Sale #%d\n", OCI_GetInt(rs, 1)); /* iterate the collection by accessing element by index */ n = OCI_CollGetSize(coll); for(i = 1; i <= n; i++) { elem = OCI_CollGetAt(coll, i); obj = OCI_ElemGetObject(elem); printf(".... employee %s, amount %s \n", OCI_ObjectGetString(obj, "EMP"), OCI_ObjectGetString(obj, "AMOUNT")); } } OCI_Cleanup(); return EXIT_SUCCESS; }
int main(void) { OCI_Connection *cn; OCI_Statement *st; OCI_Thread *th; if (!OCI_Initialize(NULL, NULL, OCI_ENV_THREADED | OCI_ENV_CONTEXT)) { return EXIT_FAILURE; } th = OCI_ThreadCreate(); cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT); st = OCI_StatementCreate(cn); OCI_ThreadRun(th, long_oracle_call, st); sleep(1); OCI_Break(cn); OCI_ThreadJoin(th); OCI_ThreadFree(th); OCI_StatementFree(st); OCI_ConnectionFree(cn); OCI_Cleanup(); return EXIT_SUCCESS; }