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; }
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; }
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 ociw_res_step(db_wrap_result * self) { RES_DECL(DB_WRAP_E_BAD_ARG); return OCI_FetchNext(wres->result) ? 0 : DB_WRAP_E_DONE ; }
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; }
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; }
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_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; }
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; }
static HB_ERRCODE ocilibGoTo( SQLBASEAREAP pArea, HB_ULONG ulRecNo ) { OCI_Statement * st = ( ( SDDDATA * ) pArea->pSDDData )->pStmt; OCI_Resultset * rs = OCI_GetResultset( st ); while( ulRecNo > pArea->ulRecCount && ! pArea->fFetched ) { PHB_ITEM pArray; HB_USHORT ui; if( ! OCI_FetchNext( rs ) ) { pArea->fFetched = HB_TRUE; break; } pArray = hb_itemArrayNew( pArea->area.uiFieldCount ); for( ui = 1; ui <= pArea->area.uiFieldCount; ++ui ) { PHB_ITEM pItem = NULL; LPFIELD pField = pArea->area.lpFields + ui - 1; switch( pField->uiType ) { case HB_FT_STRING: if( OCI_IsNull( rs, ui ) ) { char * pStr = ( char * ) hb_xgrab( ( HB_SIZE ) pField->uiLen + 1 ); memset( pStr, ' ', pField->uiLen ); pStr[ pField->uiLen ] = '\0'; pItem = hb_itemPutCLPtr( NULL, pStr, pField->uiLen ); } else { const dtext * val; if( ( val = OCI_GetString( rs, ui ) ) != NULL ) pItem = D_HB_ITEMPUTSTRLEN( NULL, val, ( HB_SIZE ) dtslen( val ) ); /* TODO: Pad it to pField->uiLen size with spaces? */ } break; case HB_FT_LONG: case HB_FT_INTEGER: if( pField->uiDec == 0 ) #if HB_VMLONG_MAX == INT32_MAX || defined( HB_LONG_LONG_OFF ) pItem = hb_itemPutNIntLen( NULL, OCI_GetInt( rs, ui ), pField->uiLen ); #else pItem = hb_itemPutNIntLen( NULL, OCI_GetBigInt( rs, ui ), pField->uiLen ); #endif else pItem = hb_itemPutNDLen( NULL, OCI_GetDouble( rs, ui ), pField->uiLen, pField->uiDec ); break; case HB_FT_VARLENGTH: case HB_FT_MEMO: { OCI_Long * val = OCI_GetLong( rs, ui ); if( val ) { unsigned int uiSize = OCI_LongGetSize( val ); if( OCI_LongGetType( val ) == OCI_CLONG ) pItem = D_HB_ITEMPUTSTRLEN( NULL, ( D_HB_CHAR * ) OCI_LongGetBuffer( val ), uiSize ); else pItem = hb_itemPutCL( NULL, ( char * ) OCI_LongGetBuffer( val ), uiSize ); } break; } case HB_FT_IMAGE: case HB_FT_BLOB: case HB_FT_OLE: { OCI_Long * val = OCI_GetLong( rs, ui ); if( val ) pItem = hb_itemPutCL( NULL, ( char * ) OCI_LongGetBuffer( val ), OCI_LongGetSize( val ) ); break; } case HB_FT_CURRENCY: case HB_FT_CURDOUBLE: case HB_FT_FLOAT: case HB_FT_DOUBLE: pItem = hb_itemPutNDLen( NULL, OCI_GetDouble( rs, ui ), pField->uiLen, pField->uiDec ); break; case HB_FT_DATE: { OCI_Date * date = OCI_GetDate( rs, ui ); int iYear, iMonth, iDay; if( date && OCI_DateGetDate( date, &iYear, &iMonth, &iDay ) ) pItem = hb_itemPutD( NULL, iYear, iMonth, iDay ); break; } case HB_FT_TIME: { OCI_Date * date = OCI_GetDate( rs, ui ); int iYear, iMonth, iDay, iHour, iMin, iSec; if( date && OCI_DateGetDateTime( date, &iYear, &iMonth, &iDay, &iHour, &iMin, &iSec ) ) pItem = hb_itemPutTDT( NULL, hb_dateEncode( iYear, iMonth, iDay ), hb_timeEncode( iHour, iMin, iSec, 0 ) ); break; } case HB_FT_TIMESTAMP: { OCI_Timestamp * ts = OCI_GetTimestamp( rs, ui ); int iYear, iMonth, iDay, iHour, iMin, iSec, iFSec; if( ts && OCI_TimestampGetDateTime( ts, &iYear, &iMonth, &iDay, &iHour, &iMin, &iSec, &iFSec ) ) pItem = hb_itemPutTDT( NULL, hb_dateEncode( iYear, iMonth, iDay ), hb_timeEncode( iHour, iMin, iSec, iFSec / 1000000 ) ); break; } } if( pItem ) { hb_arraySetForward( pArray, ui, pItem ); hb_itemRelease( pItem ); } } if( pArea->ulRecCount + 1 <= pArea->ulRecMax ) { pArea->pRow = ( void ** ) hb_xrealloc( pArea->pRow, ( pArea->ulRecMax + SQLDD_ROWSET_RESIZE ) * sizeof( void * ) ); pArea->pRowFlags = ( HB_BYTE * ) hb_xrealloc( pArea->pRowFlags, ( pArea->ulRecMax + SQLDD_ROWSET_RESIZE ) * sizeof( HB_BYTE ) ); pArea->ulRecMax += SQLDD_ROWSET_RESIZE; } pArea->ulRecCount++; pArea->pRow[ pArea->ulRecCount ] = pArray; pArea->pRowFlags[ pArea->ulRecCount ] = SQLDD_FLAG_CACHED; }