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;
}
