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 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; }
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(OCI_Thread *thread, void *data) { clock_t t_start = clock(); int err_code = 0; if (!OCI_ExecuteStmt((OCI_Statement *)data, "begin dbms_lock.sleep(10); end;")) { OCI_Error *err = OCI_GetLastError(); if (err) { err_code = OCI_ErrorGetOCICode(err); } } printf("call duration %d, expected oracle error is 1013, got %d", clock() - t_start, err_code); }
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; 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 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; }