/*************************************************************************
*
* Function: sql_free_result
*
* Purpose: database specific free_result. Frees memory allocated
* for a result set
*
*************************************************************************/
static int sql_free_result(rlm_sql_handle_t *handle, rlm_sql_config_t *config) {
int x;
int num_fields;
rlm_sql_oracle_conn_t *conn = handle->conn;
/* Cancel the cursor first */
x=OCIStmtFetch(conn->queryHandle,
conn->errHandle,
0,
OCI_FETCH_NEXT,
OCI_DEFAULT);
num_fields = sql_num_fields(handle, config);
if (num_fields >= 0) {
for(x=0; x < num_fields; x++) {
free(conn->results[x]);
}
free(conn->results);
free(conn->indicators);
}
conn->results=NULL;
return 0;
}
/*
Return a row from a previous cllExecSqlWithResult call.
*/
int
cllGetRow( icatSessionStruct *icss, int statementNumber ) {
static OCIStmt *p_statement;
int nCols, stat;
icatStmtStrct *myStatement;
myStatement = icss->stmtPtr[statementNumber];
nCols = myStatement->numOfCols;
p_statement = ( OCIStmt * )myStatement->stmtPtr;
stat = OCIStmtFetch( p_statement, p_err, ( ub4 )1, ( ub2 )0,
( ub4 ) OCI_DEFAULT );
if ( stat != OCI_SUCCESS && stat != OCI_NO_DATA ) {
logOraError( LOG_ERROR, p_err, stat );
_cllFreeStatementColumns( icss, statementNumber );
myStatement->numOfCols = 0;
rodsLog( LOG_ERROR, "cllGetRow: Fetch failed: %d", stat );
return -1;
}
if ( stat == OCI_SUCCESS ) {
return 0;
}
_cllFreeStatementColumns( icss, statementNumber );
myStatement->numOfCols = 0;
return 0;
}
/*************************************************************************
*
* Function: sql_free_result
*
* Purpose: database specific free_result. Frees memory allocated
* for a result set
*
*************************************************************************/
static int sql_free_result(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
int x;
int num_fields;
rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn;
/* Cancel the cursor first */
x=OCIStmtFetch(oracle_sock->queryHandle,
oracle_sock->errHandle,
0,
OCI_FETCH_NEXT,
OCI_DEFAULT);
num_fields = sql_num_fields(sqlsocket, config);
if (num_fields >= 0) {
for(x=0; x < num_fields; x++) {
free(oracle_sock->results[x]);
}
free(oracle_sock->results);
free(oracle_sock->indicators);
}
oracle_sock->results=NULL;
return 0;
}
/*************************************************************************
*
* Function: sql_fetch_row
*
* Purpose: database specific fetch_row. Returns a rlm_sql_row_t struct
* with all the data for the query in 'handle->row'. Returns
* 0 on success, -1 on failure, SQL_DOWN if database is down.
*
*************************************************************************/
static int sql_fetch_row(rlm_sql_handle_t *handle, rlm_sql_config_t *config) {
int x;
rlm_sql_oracle_conn_t *conn = handle->conn;
if (conn->ctx == NULL) {
radlog(L_ERR, "rlm_sql_oracle: Socket not connected");
return SQL_DOWN;
}
handle->row = NULL;
x=OCIStmtFetch(conn->queryHandle,
conn->errHandle,
1,
OCI_FETCH_NEXT,
OCI_DEFAULT);
if (x == OCI_SUCCESS) {
handle->row = conn->results;
return 0;
}
if (x == OCI_ERROR) {
radlog(L_ERR,"rlm_sql_oracle: fetch failed in sql_fetch_row: %s",
sql_error(handle, config));
return sql_check_error(handle, config);
}
else {
return -1;
}
}
/*************************************************************************
*
* Function: sql_fetch_row
*
* Purpose: database specific fetch_row. Returns a SQL_ROW struct
* with all the data for the query in 'sqlsocket->row'. Returns
* 0 on success, -1 on failure, SQL_DOWN if database is down.
*
*************************************************************************/
static int sql_fetch_row(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
int x;
rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn;
if (oracle_sock->conn == NULL) {
radlog(L_ERR, "rlm_sql_oracle: Socket not connected");
return SQL_DOWN;
}
sqlsocket->row = NULL;
x=OCIStmtFetch(oracle_sock->queryHandle,
oracle_sock->errHandle,
1,
OCI_FETCH_NEXT,
OCI_DEFAULT);
if (x == OCI_SUCCESS) {
sqlsocket->row = oracle_sock->results;
return 0;
}
if (x == OCI_ERROR) {
radlog(L_ERR,"rlm_sql_oracle: fetch failed in sql_fetch_row: %s",
sql_error(sqlsocket, config));
return sql_check_error(sqlsocket, config);
}
else {
return -1;
}
}
/*
=begin
--- OCIStmt#fetch([nrows [, orientation [, mode]]])
fetch data from select statement.
fetched data are stored to previously defined ((<define handle|OCIDefine>)).
:nrows
number of rows to fetch. If zero, cancel the cursor.
The default value is 1.
Because array fetch is not supported, valid value is 0 or 1.
:orientation
orientation to fetch. ((|OCI_FETCH_NEXT|)) only valid.
The default value is ((|OCI_FETCH_NEXT|)).
:mode
((|OCI_DEFULT|)) only valid.
The default value is ((|OCI_DEFAULT|)).
:return value
array of define handles, which are defined previously,
or nil when end of data.
correspond native OCI function: ((|OCIStmtFetch|))
=end
*/
static VALUE oci8_stmt_fetch(int argc, VALUE *argv, VALUE self)
{
VALUE vnrows;
VALUE vorientation;
VALUE vmode;
oci8_handle_t *h;
ub4 nrows;
ub2 orientation;
ub4 mode;
sword rv;
rb_scan_args(argc, argv, "03", &vnrows, &vorientation, &vmode);
Get_Handle(self, h); /* 0 */
Get_Int_With_Default(argc, 1, vnrows, nrows, 1); /* 1 */
Get_Int_With_Default(argc, 2, vorientation, orientation, OCI_FETCH_NEXT); /* 2 */
Get_Int_With_Default(argc, 3, vmode, mode, OCI_DEFAULT); /* 3 */
rv = OCIStmtFetch(h->hp, h->errhp, nrows, orientation, mode);
if (rv == OCI_NO_DATA) {
return Qnil;
}
if (IS_OCI_ERROR(rv)) {
oci8_raise(h->errhp, rv, h->hp);
}
return rb_ivar_get(self, oci8_id_define_array);
}
sword
OracleResultSet::fetch()
{
return OCIStmtFetch( _parent->getStatementHandle(),
_parent->getErrorHandle(), 1,
OCI_FETCH_NEXT, OCI_DEFAULT );
}
// После фетча - перекодируем date и выкачиваем blob.s
int ora_fetch(database *db) {
t_ora *o = db->h; int i; db_col *c;
//printf("Here fetch!"); getch();
if (!o) return ora_error(db);
//printf("Try real fetch!, OCI_NO_DATA=%d",OCI_NO_DATA); getch();
if (++o->rownum>1) i = OCIStmtFetch( o->stmt, o->errhp, (ub4) 1,
(ub4) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT);
else i=db->err_code; // Первый фетч делает сам Оракл...
//printf("Returns code=%d... Try real fetch!",i); getch();
db->err_code = 0; // Clear last error code...
if (i == OCI_NO_DATA )
{ ora_error(db); db->err_code = 0; return 0; }
if (!ora_ok(i)) return ora_error(db);
debugf(" ..ora10 start fetch ...\n");
for(i = db->out.count-1, c = db->out.cols;i>=0; i--, c++) {
c->null=(((short)c->null)!=0); // Перекодировка флажков is_null..
//if(!c->null)
if(c->type==dbDate)
{
unsigned char *D=(void*)c->dbvalue;
if (c->null) *(double*)c->value=0;
else *(double*)c->value=dt_encode((D[0]-100)*100+(D[1]-100),
D[2],D[3],D[4]-1,D[5]-1,D[6]-1);
} else if (c->type==dbBlob) // Вытягиваем блобу...
{
db_blob_handle *b=(void*)c->value; // Это указатель на "вытащенный блоб", его нужно установить на...
int cnt_read = 0, offset = 0, blob_length = 0;
debugf(" ..ora10 download blob to handle=%d\n",b);
if (c->null) { b->len=0; continue;}
if (OCI_SUCCESS!=OCILobGetLength (o->svchp,o->errhp,o->blob_read, &blob_length)) return ora_error(db);
// - Сюда сливаются БЛОБЫ от SELCETA -- db->out.blob,&db->out.bsize,&db->out.blen
if (blob_length +1 >= db->out.bsize)
{
int sz = blob_length+1024; // Новая длина
void *p;
// Еще место нужно... Realloc...
debugf(" ..ora10 blob size=%d\n",sz);
db->out.bsize = sz;
p = realloc(db->out.blob,db->out.bsize); // Думаем что памяти достаточно
if (!p) { sprintf(db->error,"vdb - no memory"); db->err_code = -2; return 0; }
db->out.blob = p ;
}
debugf(" .. ora Ok - begin read\n");
if (OCI_SUCCESS!=OCILobRead(o->svchp,o->errhp,o->blob_read, // Интересно, такое бывает?
&cnt_read, 1, db->out.blob, db->out.bsize, 0, 0 csid_csfrm)) return ora_error(db);
debugf(" .. ora ReadDone, no error cnt_read=%d\n",cnt_read);
db->out.blen = cnt_read+1; // Zero termanited на всякий случай...
// Корректируем указатель, т.к. блоб мог реаллокнуться..
debugf(" .. ora Setb->data\n");
b->data = db->out.blob; // Все заточено под один БЛОБ
b->len = cnt_read;
b->data[b->len] = 0; // Терминирование нулем - вещь полезная...
debugf(" ora OK flashed to bh=%d- do it again\n",b);
}
}
debugf(" ..ora - FetchDone OK\n");
return 1;
}
/* This select empno, ename from myemp table or empview view
- can take different SQL texts
*/
void multirow_fetch_from_emp(OCISvcCtx *svchp, OCIStmt * stmthp, OCIError *errhp,
thdata *pthdata)
{
#define ARRAY_SIZE 3
OCIDefine *defhp1, *defhp2;
ub4 empid[ARRAY_SIZE];
sb2 empid_ind[ARRAY_SIZE];
char lname[ARRAY_SIZE][30];
sb2 lname_ind[ARRAY_SIZE];
ub2 lname_len[ARRAY_SIZE];
boolean done=FALSE;
ub4 rows = 0;
ub4 i =0;
sb4 status;
/* Define output buffers */
checkerr(errhp, OCIDefineByPos (stmthp, &defhp1, errhp, 1,
(void *) empid, (sb4) sizeof(empid[0]),
SQLT_INT, (void *) empid_ind,
(ub2 *) NULL, (ub2 *) NULL, OCI_DEFAULT));
checkerr(errhp, OCIDefineByPos (stmthp, &defhp2, errhp, 2,
(void *) lname[0], (sb4) sizeof(lname[0]),
SQLT_STR, (void *) lname_ind,
(ub2 *) lname_len, (ub2 *) NULL,
OCI_DEFAULT));
/* Fetch ARRAY_SIZE rows at a time */
while (!done)
{
status = OCIStmtFetch(stmthp, errhp, ARRAY_SIZE,
OCI_FETCH_NEXT, OCI_DEFAULT);
if ((status == OCI_SUCCESS) || (status == OCI_NO_DATA))
{
if (status == OCI_SUCCESS)
rows = ARRAY_SIZE; /* all rows asked for were obtained */
else if (status == OCI_NO_DATA)
{
/* might have gotten fewer rows */
checkerr(errhp, OCIAttrGet(stmthp, OCI_HTYPE_STMT,
&rows, (ub4 *) NULL,
OCI_ATTR_ROWS_FETCHED, errhp));
done = TRUE;
}
for (i=0; i < rows; i++)
{
if (verbose_flag)
printf ("empno=%d, ename=%s\n", empid[i], lname[i]);
}
}
else
{
checkerr (errhp, status);
done =TRUE;
}
}
}
from regions where region_id = :regionID";
void query_salary(OCISvcCtx *svchp, OCIError *errhp, thdata *pthdata)
{
OCIBind *bndp1;
OCIDefine *defhp1, *defhp2, *defhp3;
OCIStmt *stmthp;
ub4 region_id;
char region_name[100];
ub4 num_rows = 1;
if (verbose_flag)
printf ("demonstrating single row select\n");
checkerr(errhp,
OCIStmtPrepare2 (svchp, &stmthp, /* returned stmt handle */
errhp, /* error handle */
(const OraText *) MY_SELECT, /*input statement text*/
strlen((char *) MY_SELECT), /* length of text */
NULL, 0, /* tagging parameters: optional */
OCI_NTV_SYNTAX, OCI_DEFAULT));
/* bind input parameters */
checkerr(errhp, OCIBindByName(stmthp, &bndp1, errhp, (text *) ":regionID",
-1, (void *) &(pthdata->region_id),
sizeof(pthdata->region_id),
SQLT_INT, (void *) NULL,
(ub2 *) NULL, (ub2 *) NULL, 0, (ub4 *) NULL,
OCI_DEFAULT));
/* execute the statement */
checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, 0, 0,
(OCISnapshot *) NULL, (OCISnapshot *) NULL,
OCI_DEFAULT));
/* Define output buffers */
checkerr(errhp, OCIDefineByPos (stmthp, &defhp1, errhp, 1,
(void *) ®ion_id, (sb4) sizeof(region_id),
SQLT_INT, (void *) NULL, (ub2 *) NULL,
(ub2 *) NULL, OCI_DEFAULT));
checkerr(errhp, OCIDefineByPos (stmthp, &defhp2, errhp, 2,
(void *)region_name,(sb4)sizeof(region_name),
SQLT_STR, (void *) NULL, (ub2 *) NULL,
(ub2 *) NULL, OCI_DEFAULT));
/* Fetch one row */
checkerr(errhp, OCIStmtFetch(stmthp, errhp, num_rows, OCI_FETCH_NEXT,
OCI_DEFAULT));
if (verbose_flag)
printf("fetched results: region_id=%d, region_name=%s, \n",
region_id, region_name);
/* release the statement handle */
checkerr(errhp, OCIStmtRelease(stmthp, errhp,
(OraText *) NULL, 0, OCI_DEFAULT));
}
void getTriggerBySelect(DATABASE *db, list<string> *putList, char *schema_name){
char sqlbuf[1024]={0};
int ret;
char trigger_name[128] = { 0 };
OCIDefine *defcolp[2];
sword indp[2];
sprintf(sqlbuf,
"SELECT DISTINCT(name) FROM ALL_SOURCE WHERE OWNER='%s' AND TYPE='TRIGGER'", schema_name);
msg("sqlbuf=%s\n",sqlbuf);
ret = OCIStmtPrepare2(db->svchp, &db->stmt, db->errhp,
(text *) sqlbuf, strlen(sqlbuf), NULL, 0,
OCI_NTV_SYNTAX, OCI_DEFAULT);
if (ret != OCI_SUCCESS) {
oraError8(db);
msg("error OCIStmtPrepare2:%s\n", db->errmsg);
ret = -2;
return;
}
ret = OCIDefineByPos(db->stmt, &defcolp[0], db->errhp,
(ub4) 1,
(dvoid *) trigger_name,//OCIDefineByPos primary_key,30长度，若不够30则填充空格(十进制32)
(sb4) 128, SQLT_STR, (dvoid *) &indp[0], (ub2 *) 0,
(ub2 *) 0, (ub4) OCI_DEFAULT);
if (ret != OCI_SUCCESS) {
oraError8(db);
msg("error OCIDefineByPos:%s\n", db->errmsg);
ret = -2;
}
// ret = OCIStmtExecute(db->svchp, db->stmt, db->errhp, (ub4) 0,
// 0, 0, 0, OCI_DEFAULT);
ret = executeQuery8(db, db->stmt);
if (ret != OCI_SUCCESS) {
// oraError8(db);
msg("error OCIStmtExecute:%s\n", db->errmsg);
ret = -2;
OCIStmtRelease(db->stmt, db->errhp, NULL, 0, OCI_DEFAULT);
return;
}
for (;;) {
ret = OCIStmtFetch(db->stmt, db->errhp, 1,
OCI_FETCH_NEXT, OCI_DEFAULT);
if (ret == OCI_NO_DATA) {
break;
} else {
msg("trigger_name=%s\n", trigger_name);
if(strncmp(trigger_name, "BIN$", 4)!=0)
putList->push_back(trigger_name);
}
memset(trigger_name, 0, sizeof(trigger_name));
}
OCIStmtRelease(db->stmt, db->errhp, NULL, 0, OCI_DEFAULT);
}
static GdaRow *
fetch_next_oracle_row (GdaOracleRecordset *model, G_GNUC_UNUSED gboolean do_store, GError **error)
{
int result;
GdaRow *prow = NULL;
GdaOraclePStmt *ps = GDA_ORACLE_PSTMT (((GdaDataSelect*)model)->prep_stmt);
OracleConnectionData *cdata;
GdaConnection *cnc;
cnc = gda_data_select_get_connection ((GdaDataSelect*) model);
cdata = (OracleConnectionData*) gda_connection_internal_get_provider_data_error (cnc, error);
if (!cdata)
return NULL;
/* fetch row */
if (cdata->major_version > 9)
result = OCIStmtFetch2 (ps->hstmt,
cdata->herr,
(ub4) 1,
(ub2) OCI_FETCH_NEXT,
(sb4) 1,
(ub4) OCI_DEFAULT);
else
result = OCIStmtFetch (ps->hstmt,
cdata->herr,
(ub4) 1,
(ub2) OCI_FETCH_NEXT,
(ub4) OCI_DEFAULT);
if (result == OCI_NO_DATA) {
GDA_DATA_SELECT (model)->advertized_nrows = model->priv->next_row_num;
return NULL;
}
else {
GdaConnectionEvent *event;
if ((event = gda_oracle_check_result (result, cnc, cdata, OCI_HTYPE_ERROR,
_("Could not fetch next row")))) {
/* set @error */
g_set_error (error, GDA_SERVER_PROVIDER_ERROR, GDA_SERVER_PROVIDER_DATA_ERROR,
"%s", gda_connection_event_get_description (event));
return NULL;
}
}
prow = new_row ((GdaDataSelect*) model, cnc, ps);
gda_data_select_take_row ((GdaDataSelect*) model, prow, model->priv->next_row_num);
model->priv->next_row_num ++;
return prow;
}
/* select test */
static int doSelect(OCISvcCtx *svcCtx, OCIStmt *stmthp, OCIError *errhp, int empno, int *rcnt) {
OCIBind *bndp = (OCIBind *) 0;
text *sql = (text *) "SELECT ENAME,JOB FROM EMP WHERE EMPNO >= :1" ;
char emp[20];
char job[20];
OCIDefine *stmtdef1 = (OCIDefine *) 0;
OCIDefine *stmtdef2 = (OCIDefine *) 0;
sword status = OCIStmtPrepare(stmthp, errhp, (text *) sql, (ub4) strlen((char *) sql), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
// THIS IS A C FILE SO DECLARATIONS UP TOP
btlogger_debug( "TxLog doSelect: :1=%d", empno);
/* bind empno to the statement */
if (status == OCI_SUCCESS)
status = OCIBindByPos(stmthp, &bndp, errhp, 1, (dvoid *) &empno, (sword) sizeof (empno),
SQLT_INT, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
/* define the output variables for the select */
if (status == OCI_SUCCESS)
status = OCIDefineByPos(stmthp, &stmtdef1, errhp, 1, (dvoid *)&emp,(sword) sizeof (emp), SQLT_STR,
(dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT);
if (status == OCI_SUCCESS)
status = OCIDefineByPos(stmthp, &stmtdef2, errhp, 2, (dvoid *)&job,(sword) sizeof (job), SQLT_STR,
(dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT);
/* exectute the select */
if (status == OCI_SUCCESS)
status = OCIStmtExecute(svcCtx, stmthp, errhp, (ub4) 0, (ub4) 0, (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);
btlogger_debug( "TxLog executing statement: %s :1=%d", sql, empno);
*rcnt = 0;
if (status != OCI_SUCCESS && status != OCI_NO_DATA) {
show_error(errhp, status);
return status;
} else {
while (1) {
status = OCIStmtFetch(stmthp, errhp, (ub4) 1, (ub4) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT);
if (status != OCI_SUCCESS && status != OCI_SUCCESS_WITH_INFO)
break;
btlogger_debug( "TxLog Name: %s Job: %s", emp, job);
(*rcnt) += 1;
}
btlogger_debug( "TxLog result: %d", *rcnt);
return OCI_SUCCESS;
}
}
/*
** Get another row of the given cursor.
*/
static int cur_fetch (lua_State *L) {
cur_data *cur = getcursor (L);
sword status = OCIStmtFetch (cur->stmthp, cur->errhp, 1,
OCI_FETCH_NEXT, OCI_DEFAULT);
if (status == OCI_NO_DATA) {
/* No more rows */
lua_pushnil (L);
return 1;
} else if (status != OCI_SUCCESS) {
/* Error */
return checkerr (L, status, cur->errhp);
}
if (lua_istable (L, 2)) {
int i;
const char *opts = luaL_optstring (L, 3, "n");
if (strchr (opts, 'n') != NULL)
/* Copy values to numerical indices */
for (i = 1; i <= cur->numcols; i++) {
int ret = pushvalue (L, cur, i);
if (ret != 1)
return ret;
lua_rawseti (L, 2, i);
}
if (strchr (opts, 'a') != NULL)
/* Copy values to alphanumerical indices */
for (i = 1; i <= cur->numcols; i++) {
column_data *col = &(cur->cols[i-1]);
int ret;
lua_pushlstring (L, col->name, col->namelen);
if ((ret = pushvalue (L, cur, i)) != 1)
return ret;
lua_rawset (L, 2);
}
lua_pushvalue(L, 2);
return 1; /* return table */
}
else {
int i;
luaL_checkstack (L, cur->numcols, LUASQL_PREFIX"too many columns");
for (i = 1; i <= cur->numcols; i++) {
int ret = pushvalue (L, cur, i);
if (ret != 1)
return ret;
}
return cur->numcols; /* return #numcols values */
}
}
void exec_sql_stmt(char *stmt)
{
int ret_val;
// allocate a statement handle
ret_val = OCIHandleAlloc(db_connection2->envhp, (dvoid **)&(db_connection2->stmthp), OCI_HTYPE_STMT, 0, NULL);
// prepare the statement
ret_val = OCIStmtPrepare(db_connection2->stmthp,
db_connection2->errhp,
(unsigned char *)stmt,
strlen(stmt),
OCI_NTV_SYNTAX,
OCI_DEFAULT);
if (ret_val == OCI_ERROR)
{
return;
}
// Execute the statement with 1(NON_SELECT_TYPE) as the fourth parameter
// to tell that it not a select statement.
ret_val = OCIStmtExecute(db_connection2->svchp,
db_connection2->stmthp,
db_connection2->errhp,
1,
0,
NULL,
NULL,
OCI_DEFAULT | OCI_COMMIT_ON_SUCCESS);
if (ret_val == OCI_ERROR)
{
return;
}
// close the statement
ret_val = OCIStmtFetch(db_connection2->stmthp,
db_connection2->errhp,
0,
OCI_FETCH_NEXT,
OCI_DEFAULT);
if (ret_val == OCI_ERROR)
{
return;
}
// Close the statement handle
OCIHandleFree(db_connection2->stmthp, OCI_HTYPE_STMT);
}
JP_INTERNAL(int) JP_Fetch(TJQuery &qry)
{
if (qry.rowsDone >= qry.rowsRead)
{
if (qry.error == OCI_NO_DATA)
return qry.error;
if (qry.rowsDone > 0)
qry.Clear();
qry.conn.result = OCIStmtFetch(qry.ociStmt,
qry.conn.ociError,
qry.noRows,
OCI_FETCH_NEXT,
OCI_DEFAULT);
if (qry.conn.result)
{
qry.error = _Result(qry);
if (qry.error != OCI_NO_DATA)
return qry.error;
qry.conn.result = 0;
}
qry.rowsIndex = 0;
sword AGRC = OCIAttrGet(qry.ociStmt,
OCI_HTYPE_STMT,
&qry.rowsRead,
0,
OCI_ATTR_ROW_COUNT,
qry.conn.ociError);
if (AGRC)
{
qry.conn.result = AGRC;
return _Result(qry);
}
if (qry.rowsRead == 0
|| qry.rowsRead == qry.rowsDone)
return qry.error;
}
else
qry.rowsIndex++;
qry.rowsDone++;
return _Result(qry);
}
bool COracleRecordset::Next()
{
b4 res;
CServerIo::trace(1,"COracleRecordset Next()");
if(m_bEof)
return false;
res = OCIStmtFetch(m_hStmt, m_parent->m_hErr, 1, OCI_FETCH_NEXT, OCI_DEFAULT);
if(res == OCI_NO_DATA)
{
CServerIo::trace(1,"COracleRecordset Next() OCI_NO_DATA");
m_bEof = true;
return false;
}
if(!m_parent->CheckError(m_parent->m_hErr, res))
return false;
CServerIo::trace(1,"COracleRecordset Next(), done - there is more data");
return true;
}
static int oci_stmt_fetch(pdo_stmt_t *stmt, enum pdo_fetch_orientation ori, long offset) /* {{{ */
{
#if HAVE_OCISTMTFETCH2
ub4 ociori;
#endif
pdo_oci_stmt *S = (pdo_oci_stmt*)stmt->driver_data;
#if HAVE_OCISTMTFETCH2
switch (ori) {
case PDO_FETCH_ORI_NEXT: ociori = OCI_FETCH_NEXT; break;
case PDO_FETCH_ORI_PRIOR: ociori = OCI_FETCH_PRIOR; break;
case PDO_FETCH_ORI_FIRST: ociori = OCI_FETCH_FIRST; break;
case PDO_FETCH_ORI_LAST: ociori = OCI_FETCH_LAST; break;
case PDO_FETCH_ORI_ABS: ociori = OCI_FETCH_ABSOLUTE; break;
case PDO_FETCH_ORI_REL: ociori = OCI_FETCH_RELATIVE; break;
}
S->last_err = OCIStmtFetch2(S->stmt, S->err, 1, ociori, offset, OCI_DEFAULT);
#else
S->last_err = OCIStmtFetch(S->stmt, S->err, 1, OCI_FETCH_NEXT, OCI_DEFAULT);
#endif
if (S->last_err == OCI_NO_DATA) {
/* no (more) data */
return 0;
}
if (S->last_err == OCI_NEED_DATA) {
oci_stmt_error("OCI_NEED_DATA");
return 0;
}
if (S->last_err == OCI_SUCCESS_WITH_INFO || S->last_err == OCI_SUCCESS) {
return 1;
}
oci_stmt_error("OCIStmtFetch");
return 0;
} /* }}} */
int main(int argc, char *argv[])
{
long ii,
rows,
status;
sb2 indicator;
char data[80];
char *dbuser,
*dbpass,
*dbconn;
OCIEnv *envhp = NULL;
OCIStmt *stmthp = NULL;
OCIDefine *defnhp = NULL;
OCIParam *parmhp = NULL;
OCIError *errhp = NULL;
OCISvcCtx *svchp = NULL;
/* Set the database user, password and connection. */
dbuser = DBUSER ? DBUSER : "";
dbpass = DBPASS ? DBPASS : "";
dbconn = DBCONN ? DBCONN : "";
/* Initialize the OCI process environment. */
status = OCIInitialize(OCI_DEFAULT, 0, 0, 0, 0);
if (status != OCI_SUCCESS)
{
fprintf(stderr, "OCIInitialize: %ld\n", status);
exit(EXIT_FAILURE);
}
/* Allocate and initialize the environment handle pointer. */
status = OCIEnvInit(&envhp, OCI_DEFAULT, 0, 0);
if (status != OCI_SUCCESS)
{
fprintf(stderr, "OCIEnvInit: %ld\n", status);
exit(EXIT_FAILURE);
}
/* Allocate the error handle pointer. */
status = OCIHandleAlloc(envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR, 0, 0);
if (status != OCI_SUCCESS)
{
fprintf(stderr, "OCIHandleAlloc: %ld\n", status);
exit(EXIT_FAILURE);
}
/* Connect to the database. */
status = OCILogon(envhp, errhp, &svchp,
(text *) dbuser, strlen(dbuser),
(text *) dbpass, strlen(dbpass),
(text *) dbconn, strlen(dbconn));
if (status != OCI_SUCCESS)
{
fprintf(stderr, "OCILogon: %ld\n", status);
exit(EXIT_FAILURE);
}
/* Allocate a statement handle for ourselves. */
status = OCIHandleAlloc(envhp, (dvoid **) &stmthp, OCI_HTYPE_STMT, 0, 0);
if (status != OCI_SUCCESS)
{
fprintf(stderr, "OCIHandleAlloc: %ld\n", status);
exit(EXIT_FAILURE);
}
/* Prepare the SQL statement. */
status = OCIStmtPrepare(stmthp, errhp, (text *) SQLSTMT, strlen(SQLSTMT),
OCI_NTV_SYNTAX, OCI_DEFAULT);
if (status != OCI_SUCCESS)
{
fprintf(stderr, "OCIStmtPrepare: %ld\n", status);
exit(EXIT_FAILURE);
}
for (ii=0; ii<2; ii++)
{
/* Execute the SQL statement. */
status = OCIStmtExecute(svchp, stmthp, errhp, 0, 0, NULL, NULL,OCI_DEFAULT);
if (status != OCI_SUCCESS)
{
fprintf(stderr, "OCIStmtExecute: %ld\n", status);
exit(EXIT_FAILURE);
}
/* Bind the the output variable for this column. */
status = OCIDefineByPos(stmthp, &defnhp, errhp, 1, data, sizeof(data),
SQLT_STR, &indicator, NULL, NULL, OCI_DEFAULT);
if (status != OCI_SUCCESS)
{
fprintf(stderr, "OCIDefineByPos: %ld\n", status);
exit(EXIT_FAILURE);
}
/* Fetch each row. */
for (rows=0; ; rows++)
{
status = OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT);
if (status == OCI_NO_DATA)
{
break;
}
else if (status != OCI_SUCCESS)
{
fprintf(stderr, "OCIStmtFetch: %ld\n", status);
exit(EXIT_FAILURE);
}
printf("INDICATOR [%d] VALUE [%s]\n", indicator, data);
}
printf("Command affected %ld row(s)\n\n", rows);
}
exit(EXIT_SUCCESS);
}
static int oci_stmt_execute(pdo_stmt_t *stmt) /* {{{ */
{
pdo_oci_stmt *S = (pdo_oci_stmt*)stmt->driver_data;
ub4 rowcount;
b4 mode;
if (!S->stmt_type) {
STMT_CALL_MSG(OCIAttrGet, "OCI_ATTR_STMT_TYPE",
(S->stmt, OCI_HTYPE_STMT, &S->stmt_type, 0, OCI_ATTR_STMT_TYPE, S->err));
}
if (stmt->executed) {
/* ensure that we cancel the cursor from a previous fetch */
OCIStmtFetch(S->stmt, S->err, 0, OCI_FETCH_NEXT, OCI_DEFAULT);
}
#ifdef OCI_STMT_SCROLLABLE_READONLY /* needed for oci8 ? */
if (S->exec_type == OCI_STMT_SCROLLABLE_READONLY) {
mode = OCI_STMT_SCROLLABLE_READONLY;
} else
#endif
if (stmt->dbh->auto_commit && !stmt->dbh->in_txn) {
mode = OCI_COMMIT_ON_SUCCESS;
} else {
mode = OCI_DEFAULT;
}
STMT_CALL(OCIStmtExecute, (S->H->svc, S->stmt, S->err,
(S->stmt_type == OCI_STMT_SELECT && !S->have_blobs) ? 0 : 1, 0, NULL, NULL,
mode));
if (!stmt->executed) {
ub4 colcount;
/* do first-time-only definition of bind/mapping stuff */
/* how many columns do we have ? */
STMT_CALL_MSG(OCIAttrGet, "ATTR_PARAM_COUNT",
(S->stmt, OCI_HTYPE_STMT, &colcount, 0, OCI_ATTR_PARAM_COUNT, S->err));
stmt->column_count = (int)colcount;
if (S->cols) {
int i;
for (i = 0; i < stmt->column_count; i++) {
if (S->cols[i].data) {
switch (S->cols[i].dtype) {
case SQLT_BLOB:
case SQLT_CLOB:
/* do nothing */
break;
default:
efree(S->cols[i].data);
}
}
}
efree(S->cols);
}
S->cols = ecalloc(colcount, sizeof(pdo_oci_column));
}
STMT_CALL_MSG(OCIAttrGet, "ATTR_ROW_COUNT",
(S->stmt, OCI_HTYPE_STMT, &rowcount, 0, OCI_ATTR_ROW_COUNT, S->err));
stmt->row_count = (long)rowcount;
return 1;
} /* }}} */
static int oci_stmt_dtor(pdo_stmt_t *stmt) /* {{{ */
{
pdo_oci_stmt *S = (pdo_oci_stmt*)stmt->driver_data;
HashTable *BC = stmt->bound_columns;
HashTable *BP = stmt->bound_params;
int i;
if (S->stmt) {
/* cancel server side resources for the statement if we didn't
* fetch it all */
OCIStmtFetch(S->stmt, S->err, 0, OCI_FETCH_NEXT, OCI_DEFAULT);
/* free the handle */
OCIHandleFree(S->stmt, OCI_HTYPE_STMT);
S->stmt = NULL;
}
if (S->err) {
OCIHandleFree(S->err, OCI_HTYPE_ERROR);
S->err = NULL;
}
/* need to ensure these go away now */
if (BC) {
zend_hash_destroy(BC);
FREE_HASHTABLE(stmt->bound_columns);
stmt->bound_columns = NULL;
}
if (BP) {
zend_hash_destroy(BP);
FREE_HASHTABLE(stmt->bound_params);
stmt->bound_params = NULL;
}
if (S->einfo.errmsg) {
pefree(S->einfo.errmsg, stmt->dbh->is_persistent);
S->einfo.errmsg = NULL;
}
if (S->cols) {
for (i = 0; i < stmt->column_count; i++) {
if (S->cols[i].data) {
switch (S->cols[i].dtype) {
case SQLT_BLOB:
case SQLT_CLOB:
OCIDescriptorFree(S->cols[i].data, OCI_DTYPE_LOB);
break;
default:
efree(S->cols[i].data);
}
}
}
efree(S->cols);
S->cols = NULL;
}
efree(S);
stmt->driver_data = NULL;
return 1;
} /* }}} */
/*
* must be called after an execute
*/
static int statement_fetch_impl(lua_State *L, statement_t *statement, int named_columns) {
int rc;
sword status;
int i;
bindparams_t *bind;
char errbuf[100];
int errcode;
if (!statement->stmt) {
luaL_error(L, DBI_ERR_FETCH_INVALID);
return 0;
}
statement_fetch_metadata(L, statement);
bind = statement->bind;
status = OCIStmtFetch(statement->stmt, statement->conn->err, 1, OCI_FETCH_NEXT, OCI_DEFAULT);
if (status == OCI_NO_DATA) {
/* No more rows */
lua_pushnil(L);
return 1;
} else if (status != OCI_SUCCESS) {
OCIErrorGet((dvoid *)statement->conn->err, (ub4)1, (text *)NULL, &errcode, errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);
luaL_error(L, DBI_ERR_FETCH_FAILED, errbuf);
}
if (statement->num_columns) {
int i;
int d = 1;
lua_newtable(L);
for (i = 0; i < statement->num_columns; i++) {
lua_push_type_t lua_push = oracle_to_lua_push(bind[i].data_type, bind[i].null);
const char *name = strlower(bind[i].name);
const char *data = bind[i].data;
if (lua_push == LUA_PUSH_NIL) {
if (named_columns) {
LUA_PUSH_ATTRIB_NIL(name);
} else {
LUA_PUSH_ARRAY_NIL(d);
}
} else if (lua_push == LUA_PUSH_INTEGER) {
int val = atoi(data);
if (named_columns) {
LUA_PUSH_ATTRIB_INT(name, val);
} else {
LUA_PUSH_ARRAY_INT(d, val);
}
} else if (lua_push == LUA_PUSH_NUMBER) {
double val = strtod(data, NULL);
if (named_columns) {
LUA_PUSH_ATTRIB_FLOAT(name, val);
} else {
LUA_PUSH_ARRAY_FLOAT(d, val);
}
} else if (lua_push == LUA_PUSH_STRING) {
if (named_columns) {
LUA_PUSH_ATTRIB_STRING(name, data);
} else {
LUA_PUSH_ARRAY_STRING(d, data);
}
} else if (lua_push == LUA_PUSH_BOOLEAN) {
int val = atoi(data);
if (named_columns) {
LUA_PUSH_ATTRIB_BOOL(name, val);
} else {
LUA_PUSH_ARRAY_BOOL(d, val);
}
} else {
luaL_error(L, DBI_ERR_UNKNOWN_PUSH);
}
}
} else {
/*
* no columns returned by statement?
*/
lua_pushnil(L);
}
return 1;
}
// 数据库查询操作
int COracleDB::oracle_select( const char *sql, OracleResult *sql_result )
{
int ret = DB_ERR_SUCCESS;
if ( _handle == NULL )
return DB_ERR_NOCONNECTION;
ORACLE_HANDLE* handle = ( ORACLE_HANDLE* ) _handle;
sb2 ind[MAX_COL];
ub2 collen[MAX_COL];
text * colbuf[MAX_COL];
OCIDefine *defhp[MAX_COL];
int i = 0;
int status = 0;
ub4 col_num = 0;
OCIParam * colhp;
while ( 1 ) {
status = OCIStmtPrepare( handle->stmthp, handle->errhp, ( text * ) sql, ( ub4 ) strlen( ( const char * ) sql ),
( ub4 ) OCI_NTV_SYNTAX, ( ub4 ) OCI_DEFAULT );
if ( status != OCI_STILL_EXECUTING )
break;
my_usleep();
}
time_t t1 = time( NULL );
while ( 1 ) {
status = OCIStmtExecute( handle->svchp, handle->stmthp, handle->errhp, 0, 0, NULL, NULL, OCI_DEFAULT );
if ( status != OCI_STILL_EXECUTING )
break;
if ( time( NULL ) - t1 > 60 ) {
// timeout
ret = DB_ERR_TIMEOUT;
break;
}
my_usleep();
}
if ( ret == DB_ERR_SUCCESS && status != OCI_SUCCESS_WITH_INFO && status != OCI_SUCCESS ) {
if ( status == OCI_NO_DATA ) {
} else {
int errcode = checkerr( handle->errhp, status, _errinfo, sql );
if ( errcode == 1012 || errcode == 12705 || errcode == 3113 || errcode == 3114 || errcode == 12541
|| errcode == 12547 ) {
ret = DB_ERR_NOCONNECTION;
} else {
ret = DB_ERR_FAILED;
}
}
}
if ( ret != DB_ERR_SUCCESS )
return ret;
while ( 1 ) {
status = OCIAttrGet( handle->stmthp, OCI_HTYPE_STMT, & col_num, 0, OCI_ATTR_PARAM_COUNT, handle->errhp );
if ( status != OCI_STILL_EXECUTING )
break;
my_usleep();
}
if ( status ) {
int errcode = checkerr( handle->errhp, status, _errinfo, sql );
if ( errcode == 1012 || errcode == 12705 || errcode == 3113 || errcode == 3114 || errcode == 12541
|| errcode == 12547 ) {
ret = DB_ERR_NOCONNECTION;
} else {
ret = DB_ERR_FAILED;
}
}
if ( ret != DB_ERR_SUCCESS )
return ret;
for ( i = 1; i <= (int)col_num ; i ++ ) {
while ( 1 ) {
status = OCIParamGet( handle->stmthp, OCI_HTYPE_STMT, handle->errhp, ( dvoid ** ) & colhp, i );
if ( status != OCI_STILL_EXECUTING )
break;
my_usleep();
}
text *name;
ub4 namelen = 128;
while ( 1 ) {
status = OCIAttrGet( ( dvoid* ) colhp, OCI_DTYPE_PARAM, & name, & namelen, OCI_ATTR_NAME, handle->errhp );
if ( status != OCI_STILL_EXECUTING )
break;
my_usleep();
}
while ( 1 ) {
status = OCIAttrGet( ( dvoid* ) colhp, OCI_DTYPE_PARAM, & collen[i - 1], 0, OCI_ATTR_DATA_SIZE,
handle->errhp );
if ( status != OCI_STILL_EXECUTING )
break;
my_usleep();
}
colbuf[i - 1] = ( text * ) malloc( ( int ) collen[i - 1] + 1 );
while ( 1 ) {
status = OCIDefineByPos( handle->stmthp, & defhp[i - 1], handle->errhp, i, ( ub1* ) colbuf[i - 1],
collen[i - 1] + 1, SQLT_STR, & ind[i - 1], 0, ( ub2* ) 0, OCI_DEFAULT );
if ( status != OCI_STILL_EXECUTING )
break;
my_usleep();
}
}
int j = 0;
t1 = time( NULL );
int count = 0;
// 设置取数据列数
sql_result->SetColumn( col_num );
while ( 1 ) {
status = OCIStmtFetch( handle->stmthp, handle->errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT );
if ( status == OCI_NO_DATA ) {
break;
}
if ( status == OCI_STILL_EXECUTING ) {
if ( time( NULL ) - t1 > 60 ) {
ret = DB_ERR_TIMEOUT;
break;
}
count ++;
if ( count > 100 ) {
my_usleep();
count = 0;
}
continue;
}
int errcode = 0;
errcode = checkerr( handle->errhp, status, _errinfo, sql );
if ( 0 == errcode || 1406 == errcode ) {
for ( i = 0; i < (int)col_num ; i ++ ) {
if ( ind[i] == - 1 )
colbuf[i][0] = '\0';
sql_result->AddValue( j, i, ( const char * ) colbuf[i] );
}
j ++;
t1 = time( NULL );
} else {
if ( errcode == 1012 || errcode == 12705 || errcode == 3113 || errcode == 3114 || errcode == 12541
|| errcode == 12547 ) {
ret = DB_ERR_NOCONNECTION;
} else {
ret = DB_ERR_FAILED;
}
break;
}
}
// 释放所有列名称
for ( i = 0; i < (int)col_num ; i ++ ) {
if ( colbuf[i] )
free( colbuf[i] );
}
return ret;
}
static int create_source_byselect(char *souname, char *ddl_sql, DATABASE *db, int soutype){
char sqlbuf[1024]={0};
int ret;
char buftext[40000];
int pos=0,cpos= 0;
OCIDefine *defcolp[2];
sword indp[2];
char soutype_name[32]={0};
if(soutype==7){
strcpy(soutype_name, "PROCEDURE");
}else if(soutype==8){
strcpy(soutype_name, "FUNCTION");
}else if(soutype==9){
strcpy(soutype_name, "PACKAGE");
}else if(soutype==11){
strcpy(soutype_name, "PACKAGE BODY");
}else if(soutype==12){
strcpy(soutype_name, "TRIGGER");
}else if(soutype==13){
strcpy(soutype_name, "TYPE");
}else if(soutype==14){
strcpy(soutype_name, "TYPE BODY");
}else{
strcpy(soutype_name, "UNKNOWN");
}
sprintf(sqlbuf,"SELECT TEXT FROM ALL_SOURCE WHERE OWNER='%s' AND TYPE='%s' AND NAME='%s' order by line",
login_info1.schema, soutype_name, souname);
msg("sqlbuf=%s\n", sqlbuf);
ret = OCIStmtPrepare2(db->svchp, &db->stmt, db->errhp,
(text *) sqlbuf, strlen(sqlbuf), NULL, 0,
OCI_NTV_SYNTAX, OCI_DEFAULT);
if (ret != OCI_SUCCESS) {
oraError8(db);
msg("error OCIStmtPrepare2:%s\n", db->errmsg);
ret = -2;
return ret;
}
ret = OCIDefineByPos(db->stmt, &defcolp[0], db->errhp,
(ub4) 1, (dvoid *) buftext, (sb4) 4000,
SQLT_STR, (dvoid *) &indp[0], (ub2 *) 0, (ub2 *) 0,
(ub4) OCI_DEFAULT);
// ret = OCIStmtExecute(db->svchp, db->stmt, db->errhp, (ub4) 0,
// 0, 0, 0, OCI_DEFAULT);
ret = executeQuery8(db, db->stmt);
if (ret != OCI_SUCCESS) {
// oraError8(db);
msg("error OCIStmtExecute 333:%s\n", db->errmsg);
ret = -2;
OCIStmtRelease(db->stmt, db->errhp, NULL, 0, OCI_DEFAULT);
return ret;
}
pos= 0;
cpos= 0;
pos= pos + sprintf(ddl_sql+pos, "CREATE OR REPLACE ");
for (;;) {
ret = OCIStmtFetch(db->stmt, db->errhp, 1,
OCI_FETCH_NEXT, OCI_DEFAULT);
if (ret == OCI_NO_DATA) {
// msg("NO DATA\n");
break;
} else {
msg("%s", buftext);
char bepart[4000]={0};
if(cpos==0){
//todo buftext="TRIGGER Test_Increase" souname="TEST_INCREASE"
char *p= strstr(buftext, souname);
char tmp[4000]={0};
if(p){
strncpy(tmp, buftext, p-buftext);
char *pa= strchr(p, ' ');
p= strchr(tmp, '.');
if(p && pa){
p= strrchr(tmp, ' ');
strncpy(bepart, tmp, p-tmp);
pos= pos + sprintf(ddl_sql+pos, "%s \"%s\" %s", bepart, souname, pa+1);
}else if(p){
p= strrchr(tmp, ' ');
strncpy(bepart, tmp, p-tmp);
pos= pos + sprintf(ddl_sql+pos, "%s \"%s\" ", bepart, souname);
}else{
pos= pos + sprintf(ddl_sql+pos, "%s", buftext);
}
}else{
pos= pos + sprintf(ddl_sql+pos, "%s", buftext);
}
}else{
pos= pos + sprintf(ddl_sql+pos, "%s", buftext);
}
}
memset(buftext, 0, sizeof(buftext));
cpos++;
}
// pos= pos + sprintf(ddl_sql+pos, "/");
OCIStmtRelease(db->stmt, db->errhp, NULL, 0, OCI_DEFAULT);
return ret;
}
// Remove the update queue tables associted with every data source.
void remove_update_queue_tables_and_native_triggers(void)
{
char statement[DB_MAX_SIZE];
OCIParam *param_desc;
OCIDefine *defn_handle;
int length;
int result;
short len, status;
char column_val[NUM_COLS][DB_MAX_SIZE]; // column_val[0] - updqueuename
// column_val[1] - instrigname
// column_val[2] - updtrigname
// column_val[3] - deltrigname
// allocate a statement handle
OCIHandleAlloc(db_connection1->envhp, (dvoid **)&(db_connection1->stmthp), OCI_HTYPE_STMT, 0, NULL);
// prepare the statement
statement[0] = '\0';
sprintf(statement, "select updqueuename, instrigname, updtrigname, deltrigname from vl_datasrc");
result = OCIStmtPrepare(db_connection1->stmthp,
db_connection1->errhp,
(unsigned char *)statement,
strlen(statement),
OCI_NTV_SYNTAX,
OCI_DEFAULT);
checkerr(db_connection1->errhp, result);
// The way we execute the statement depends on whether or not there
// are any dynamic variables to bind.
result = OCIStmtExecute(db_connection1->svchp,
db_connection1->stmthp,
db_connection1->errhp,
0,
0,
NULL,
NULL,
OCI_DEFAULT);
checkerr(db_connection1->errhp, result);
if (result == OCI_ERROR || result == OCI_NO_DATA)
{
return;
}
do
{
int i;
for (i = 1; i <= NUM_COLS; i++)
{
// Get the description of the current column
result = OCIParamGet(db_connection1->stmthp,
OCI_HTYPE_STMT,
db_connection1->errhp,
¶m_desc,
i);
checkerr(db_connection1->errhp, result);
// set the column's maximum length
result = OCIAttrGet(param_desc,
OCI_DTYPE_PARAM,
&len,
NULL,
OCI_ATTR_DATA_SIZE,
db_connection1->errhp);
checkerr(db_connection1->errhp, result);
// add one space for the NULL terminator
length = (short) (len + 1);
// Get the value of the current column.
result = OCIDefineByPos(db_connection1->stmthp,
&defn_handle,
db_connection1->errhp,
i,
column_val[i-1],
length,
SQLT_STR,
&status,
NULL,
NULL,
OCI_DEFAULT);
checkerr(db_connection1->errhp, result);
}
// Fetch the next row
result = OCIStmtFetch(db_connection1->stmthp,
db_connection1->errhp,
1, // number of rows to fetch
OCI_FETCH_NEXT,
OCI_DEFAULT );
// No more rows to fetch
if (result == OCI_NO_DATA)
{
break;
}
else
{
char drop_stmt[DB_MAX_SIZE];
checkerr(db_connection1->errhp, result);
drop_stmt[0] = '\0';
sprintf(drop_stmt, "drop trigger \"%s\"", column_val[1]);
exec_sql_stmt(drop_stmt);
puts(column_val[1]);
drop_stmt[0] = '\0';
sprintf(drop_stmt, "drop trigger \"%s\"", column_val[2]);
exec_sql_stmt(drop_stmt);
puts(column_val[2]);
drop_stmt[0] = '\0';
sprintf(drop_stmt, "drop trigger \"%s\"", column_val[3]);
exec_sql_stmt(drop_stmt);
puts(column_val[3]);
drop_stmt[0] = '\0';
sprintf(drop_stmt, "drop table \"%s\"", column_val[0]);
exec_sql_stmt(drop_stmt);
puts(column_val[0]);
}
} while (result != OCI_NO_DATA);
// close the statement
result = OCIStmtFetch(db_connection1->stmthp,
db_connection1->errhp,
0,
OCI_FETCH_NEXT,
OCI_DEFAULT);
checkerr(db_connection1->errhp, result);
// Close the statement handle
OCIHandleFree(db_connection1->stmthp, OCI_HTYPE_STMT);
}
int DataBase::_DB_FetchData()
{
return( _DB_CheckErr( OCIStmtFetch( DBSession->selectp, DBSession->errhp, (ub4)1, (ub4)OCI_FETCH_NEXT, (ub4) OCI_DEFAULT ) ) );
}
static int yada_oracle_fetch(yada_t *_yada, yada_rc_t *rrc, yada_rc_t *brc)
{
char errbuf[1024];
sb4 errcode;
int i;
yada_bindset_t *bs;
bindset_ele_t *bse;
extinfo *info;
bs = brc->data;
info = (extinfo*)rrc->data;
/* check if database connection disappeared */
if(info->invalid)
{
_yada_set_err(_yada, -1, "database unavailable");
return(0);
}
/* check database connection */
if(!_yada->_mod->env && !_yada->connect(_yada, NULL, NULL))
return(0);
/* create oracle bindings on the fly */
if(info->bind)
{
if(yada_oracle_bind(_yada, info->stmt, bs))
return(0);
info->bind = 0;
}
/* fetch rows into buffers */
#ifdef OBSOLETE_OCI
switch(OCIStmtFetch(info->stmt, _yada->_mod->err, 1,
OCI_FETCH_NEXT, OCI_DEFAULT))
{
#else
switch(OCIStmtFetch2(info->stmt, _yada->_mod->err, 1,
OCI_FETCH_NEXT, 0, OCI_DEFAULT))
{
#endif
/************************************************/
case OCI_SUCCESS:
/************************************************/
/* fetch succeeded */
break;
/************************************************/
case OCI_SUCCESS_WITH_INFO:
/************************************************/
/* fetch succeeded -- store warning info */
OCIErrorGet(_yada->_mod->err, 1, NULL, &errcode, errbuf, sizeof(errbuf),
OCI_HTYPE_ERROR);
_yada_set_err(_yada, errcode, errbuf);
break;
/************************************************/
case OCI_NO_DATA:
/************************************************/
/* no more data to fetch */
_yada->error = 0;
return(0);
/************************************************/
default:
/************************************************/
OCIErrorGet(_yada->_mod->err, 1, NULL, &errcode, errbuf, sizeof(errbuf),
OCI_HTYPE_ERROR);
_yada_set_err(_yada, errcode, errbuf);
_yada->disconnect(_yada);
return(0);
/************************************************/
} /* switch(status) */
/* tweek bound data */
for(i=0, bse=bs->ele; i<bs->eles; i++, bse++)
{
if(bse->t <= 0)
{
switch(*(sb2*)&bse->ind)
{
/************************************************/
case 0:
/************************************************/
/* value contained a result */
*(void**)bse->ptr = bse->var.buf;
break;
/************************************************/
case -1:
/************************************************/
/* value was NULL */
*(void**)bse->ptr = NULL;
break;
/************************************************/
default:
/************************************************/
/* value didn't fit in buffer */
_yada_set_err(_yada, ENAMETOOLONG, "value too large for buffer");
return(0);
/************************************************/
} /* switch(indicator) */
} /* if(bind_ptr) */
switch(bse->t)
{
/************************************************/
case -'d':
/************************************************/
/* update pointer if value was successful */
*(void**)bse->ptr = (*(sb2*)&bse->ind ? NULL : &bse->var.i);
break;
/************************************************/
case 'l':
/************************************************/
/* update user-supplied value */
*(long long*)bse->ptr = (*(sb2*)&bse->ind ? 0 : atoll(bse->tmp));
break;
/************************************************/
case -'l':
/************************************************/
/* update pointer if value was successful */
if(*(sb2*)&bse->ind)
*(void**)bse->ptr = NULL;
else
{
bse->var.l = atoll(bse->tmp);
*(void**)bse->ptr = &bse->var.l;
}
break;
/************************************************/
case 'b':
case -'b':
/************************************************/
/* convert size to int */
bse++;
i++;
*(int*)bse->ptr = (int)*(sb2*)&bse->var.i;
break;
/************************************************/
} /* switch(bind_type) */
} /* foreach(binding) */
return(1);
}
/******************************************************************************/
/** get the last insert id
* @returns last insert id, or 0 on failure
*/
static uint64_t yada_oracle_insert_id(yada_t *_yada, char *table, char *col)
{
/* this needs to be user set */
return(0);
}
/******************************************************************************/
/** create and init yada struct
* @returns non zero on success, 0 on error
*/
int yada_mod_init(yada_t *_yada)
{
if(!(_yada->_mod = calloc(1, sizeof(yada_modpriv_t))))
return(0);
_yada_set_err(_yada, 0, "no error reported");
/* yada module base functions */
_yada->type_id = YADA_ORACLE;
_yada->connect = yada_oracle_connect;
_yada->disconnect = yada_oracle_disconnect;
/* native prepare funtions */
_yada->prepare = _yada_prepare;
_yada->preparef = _yada_preparef;
/* yada compat prepare funtions */
_yada->yprepare = _yada_prepare;
_yada->ypreparef = _yada_preparef;
_yada->xprepare = _yada_xprepare;
_yada->execute = _yada_execute;
_yada->xexecute = _yada_xexecute;
_yada->query = _yada_query;
_yada->xquery = _yada_xquery;
/* yada util functions */
_yada->escstr = yada_oracle_escstr;
_yada->dumpexec = _yada_dumpexec;
/* yada compat bind functions */
_yada->bind = _yada_bind;
_yada->fetch = yada_oracle_fetch;
/* var args functions */
_yada->vquery = _yada_vquery;
_yada->vbind = _yada_vbind;
_yada->vexecute = _yada_vexecute;
/* transaction functions */
_yada->trx = yada_oracle_trx;
_yada->commit = yada_oracle_commit;
_yada->rollback = yada_oracle_rollback;
_yada->insert_id = yada_oracle_insert_id;
/* private interfaces */
_yada->_priv->exec = yada_oracle__exec;
_yada->_priv->query = yada_oracle__query;
_yada->_priv->destroy = yada_oracle__destroy;
_yada->_priv->free_rc[YADA_STATEMENT] = _yada_free_stmt;
_yada->_priv->free_rc[YADA_RESULT] = yada_oracle_free_result;
_yada->_priv->free_rc[YADA_BINDSET] = yada_oracle_free_bindset;
return(1);
}
static int oracle_select(void *theconn, const Octstr *sql, List *binds, List **res)
{
List *row;
OCIStmt *stmt;
OCIParam *dparam;
sword status;
ub4 columns;
ub4 i;
struct data_s {
text *data;
ub2 size;
sb2 ind;
ub2 type;
};
struct data_s *data;
struct ora_conn *conn = (struct ora_conn*) theconn;
int binds_len = (binds ? gwlist_len(binds) : 0);
*res = NULL;
/* allocate statement handle */
status = OCIHandleAlloc(conn->envp, (dvoid**)&stmt, OCI_HTYPE_STMT, 0,0);
if (OCI_SUCCESS != status) {
oracle_checkerr(conn->errhp, status);
return -1;
}
/* prepare statement */
status = OCIStmtPrepare(stmt, conn->errhp, (unsigned char*)octstr_get_cstr(sql),
octstr_len(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);
if (OCI_SUCCESS != status) {
oracle_checkerr(conn->errhp, status);
OCIHandleFree(stmt, OCI_HTYPE_STMT);
return -1;
}
/* bind variables */
for (i = 0; i < binds_len; i++) {
OCIBind *bndhp = NULL;
Octstr *bind = gwlist_get(binds, i);
status = OCIBindByPos(stmt, &bndhp,
conn->errhp, (i+1), (dvoid *) octstr_get_cstr(bind),
(sword) octstr_len(bind)+1, SQLT_STR, (dvoid *) 0, (ub2 *)0,
(ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT);
if (OCI_SUCCESS != status) {
oracle_checkerr(conn->errhp, status);
OCIHandleFree(stmt, OCI_HTYPE_STMT);
return -1;
}
}
/* execute our statement */
status = OCIStmtExecute(conn->svchp, stmt, conn->errhp, 0, 0, NULL, NULL,
OCI_DEFAULT);
if (OCI_SUCCESS != status && OCI_NO_DATA != status) {
oracle_checkerr(conn->errhp, status);
OCIHandleFree(stmt, OCI_HTYPE_STMT);
return -1;
}
/* receive column count */
status = OCIAttrGet(stmt, OCI_HTYPE_STMT, &columns, 0, OCI_ATTR_PARAM_COUNT,
conn->errhp);
if (status != OCI_SUCCESS) {
oracle_checkerr(conn->errhp, status);
OCIHandleFree(stmt, OCI_HTYPE_STMT);
return -1;
}
debug("dbpool.oracle",0,"SQL has %d columns", columns);
/* allocate array of pointers */
debug("dbpool.oracle",0,"alloc size=%ld",sizeof(text*)*columns);
data = gw_malloc(sizeof(struct data_s)*columns);
debug("dbpool.oracle",0,"retrieve data_size");
/* retrieve data size for every column and allocate it */
for (i=0 ; i < columns; i++) {
OCIDefine *defh;
status = OCIParamGet(stmt, OCI_HTYPE_STMT, conn->errhp,
(dvoid**) &dparam, i+1);
if (status != OCI_SUCCESS) {
oracle_checkerr(conn->errhp, status);
columns = i;
for (i = 0; i < columns; i++)
gw_free(data[i].data);
gw_free(data);
OCIHandleFree(stmt, OCI_HTYPE_STMT);
return -1;
}
status = OCIAttrGet(dparam, OCI_DTYPE_PARAM, (dvoid*) &data[i].size,
0, OCI_ATTR_DATA_SIZE, conn->errhp);
if (status != OCI_SUCCESS) {
oracle_checkerr(conn->errhp, status);
columns = i;
for (i = 0; i < columns; i++)
gw_free(data[i].data);
gw_free(data);
OCIHandleFree(stmt, OCI_HTYPE_STMT);
return -1;
}
status = OCIAttrGet(dparam, OCI_DTYPE_PARAM, (dvoid*) &data[i].type,
0, OCI_ATTR_DATA_TYPE, conn->errhp);
if (status != OCI_SUCCESS) {
oracle_checkerr(conn->errhp, status);
columns = i;
for (i = 0; i < columns; i++)
gw_free(data[i].data);
gw_free(data);
OCIHandleFree(stmt, OCI_HTYPE_STMT);
return -1;
}
/* convert all data types to C-Strings except DATE */
if (data[i].type != SQLT_DAT) {
data[i].size++; /* terminating zero */
data[i].type = SQLT_STR;
}
debug("dbpool.oracle",0,"alloc size=%d", data[i].size);
data[i].data = gw_malloc(data[i].size);
/* bind allocated values to statement handle */
status = OCIDefineByPos(stmt, &defh, conn->errhp, i+1, data[i].data,
data[i].size, data[i].type, &data[i].ind,
0, 0, OCI_DEFAULT);
if (status != OCI_SUCCESS) {
oracle_checkerr(conn->errhp, status);
columns = i;
for (i = 0; i <= columns; i++)
gw_free(data[i].data);
gw_free(data);
OCIHandleFree(stmt, OCI_HTYPE_STMT);
return -1;
}
}
*res = gwlist_create();
/* fetch data */
while ((status = OCIStmtFetch(stmt, conn->errhp, 1,
OCI_FETCH_NEXT, OCI_DEFAULT)) == OCI_SUCCESS ||
status == OCI_SUCCESS_WITH_INFO) {
row = gwlist_create();
for (i = 0; i < columns; i++) {
if (data[i].data == NULL || data[i].ind == -1) {
gwlist_insert(row, i, octstr_create(""));
} else {
gwlist_insert(row, i, octstr_create_from_data((const char*)data[i].data, data[i].size));
}
/* debug("dbpool.oracle",0,"inserted value = '%s'",
octstr_get_cstr(gwlist_get(row,i))); */
}
gwlist_append(*res, row);
}
/* ignore OCI_NO_DATA error */
if (status != OCI_NO_DATA) {
List *row;
oracle_checkerr(conn->errhp, status);
for (i = 0; i < columns; i++)
gw_free(data[i].data);
gw_free(data);
while ((row = gwlist_extract_first(*res)) != NULL)
gwlist_destroy(row, octstr_destroy_item);
gwlist_destroy(*res, NULL);
*res = NULL;
OCIHandleFree(stmt, OCI_HTYPE_STMT);
return -1;
}
for (i = 0; i < columns; i++)
gw_free(data[i].data);
gw_free(data);
OCIHandleFree(stmt, OCI_HTYPE_STMT);
return 0;
}
/* ODCIIndexFetch function */
OCINumber *qxiqtbspf(
OCIExtProcContext *ctx,
qxiqtim *self,
qxiqtin *self_ind,
OCINumber *nrows,
short nrows_ind,
OCIArray **rids,
short *rids_ind,
ODCIEnv *env,
ODCIEnv_ind *env_ind)
{
sword status;
OCIEnv *envhp = (OCIEnv *) 0; /* env. handle */
OCISvcCtx *svchp = (OCISvcCtx *) 0; /* service handle */
OCIError *errhp = (OCIError *) 0; /* error handle */
OCISession *usrhp = (OCISession *) 0; /* user handle */
qxiqtcx *icx = (qxiqtcx *) 0; /* state to be saved for later calls */
int idx = 1;
int nrowsval;
OCIArray *ridarrp = *rids; /* rowid collection */
OCIString *ridstr = (OCIString *)0;
int done = 0;
int retval = (int)ODCI_SUCCESS;
OCINumber *rval = (OCINumber *)0;
ub1 *key; /* key to retrieve context */
ub4 keylen; /* length of key */
/*******************/
/* Get OCI handles */
/*******************/
if (qxiqtce(ctx, errhp, OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp)))
return(rval);
/* set up return code */
rval = (OCINumber *)OCIExtProcAllocCallMemory(ctx, sizeof(OCINumber));
if (qxiqtce(ctx, errhp,
OCINumberFromInt(errhp, (dvoid *)&retval, sizeof(retval),
OCI_NUMBER_SIGNED, rval)))
return(rval);
/* get the user handle */
if (qxiqtce(ctx, errhp, OCIAttrGet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
(dvoid *)&usrhp, (ub4 *)0,
(ub4)OCI_ATTR_SESSION, errhp)))
return(rval);
/********************************/
/* Retrieve context from key */
/********************************/
key = OCIRawPtr(envhp, self->sctx_qxiqtim);
keylen = OCIRawSize(envhp, self->sctx_qxiqtim);
if (qxiqtce(ctx, errhp, OCIContextGetValue((dvoid *)usrhp, errhp,
key, (ub1)keylen,
(dvoid **)&(icx))))
return(rval);
/* get value of nrows */
if (qxiqtce(ctx, errhp, OCINumberToInt(errhp, nrows,
sizeof(nrowsval),
OCI_NUMBER_SIGNED,
(dvoid *)&nrowsval)))
return(rval);
/****************/
/* Fetch rowids */
/****************/
while (!done)
{
if (idx > nrowsval)
done = 1;
else
{
status = OCIStmtFetch(icx->stmthp, errhp, (ub4)1, (ub2) 0,
(ub4)OCI_DEFAULT);
if (status == OCI_NO_DATA)
{
short col_ind = OCI_IND_NULL;
/* have to create dummy oci string */
OCIStringAssignText(envhp, errhp, (text *)"dummy",
(ub2)5, &ridstr);
/* append null element to collection */
if (qxiqtce(ctx, errhp, OCICollAppend(envhp, errhp,
(dvoid *)ridstr,
(dvoid *)&col_ind,
(OCIColl *)ridarrp)))
return(rval);
done = 1;
}
else if (status == OCI_SUCCESS)
{
OCIStringAssignText(envhp, errhp, (text *)icx->ridp,
(ub2)18, (OCIString **)&ridstr);
/* append rowid to collection */
if (qxiqtce(ctx, errhp, OCICollAppend(envhp, errhp,
(dvoid *)ridstr,
(dvoid *)0,
(OCIColl *)ridarrp)))
return(rval);
idx++;
}
else if (qxiqtce(ctx, errhp, status))
return(rval);
}
}
/* free ridstr finally */
if (ridstr &&
(qxiqtce(ctx, errhp, OCIStringResize(envhp, errhp, (ub4)0,
&ridstr))))
return(rval);
*rids_ind = OCI_IND_NOTNULL;
return(rval);
}
static void get_db_charsets(conn_info_t *params_p, ub2 *char_csid,
ub2 *nchar_csid)
{
OCIDefine *defnp1 = (OCIDefine *) NULL;
OCIDefine *defnp2 = (OCIDefine *) NULL;
oratext parm[PARM_BUFLEN];
oratext value[OCI_NLS_MAXBUFSZ];
ub2 parm_len = 0;
ub2 value_len = 0;
oci_t ocistruct;
oci_t *ocip = &ocistruct;
*char_csid = 0;
*nchar_csid = 0;
memset (ocip, 0, sizeof(ocistruct));
if (OCIEnvCreate(&ocip->envp, OCI_OBJECT, (dvoid *)0,
(dvoid * (*)(dvoid *, size_t)) 0,
(dvoid * (*)(dvoid *, dvoid *, size_t))0,
(void (*)(dvoid *, dvoid *)) 0,
(size_t) 0, (dvoid **) 0))
{
ocierror(ocip, (char *)"OCIEnvCreate() failed", TRUE);
}
if (OCIHandleAlloc((dvoid *) ocip->envp, (dvoid **) &ocip->errp,
(ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0))
{
ocierror(ocip, (char *)"OCIHandleAlloc(OCI_HTYPE_ERROR) failed", TRUE);
}
OCICALL(ocip,
OCILogon(ocip->envp, ocip->errp, &ocip->svcp,
params_p->user, params_p->userlen,
params_p->passw, params_p->passwlen,
params_p->dbname, params_p->dbnamelen));
OCICALL(ocip,
OCIHandleAlloc((dvoid *) ocip->envp, (dvoid **) &ocip->stmtp,
(ub4) OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));
/* Execute stmt to select the db nls char and nchar character set */
OCICALL(ocip,
OCIStmtPrepare(ocip->stmtp, ocip->errp,
(CONST text *)GET_DB_CHARSETS,
(ub4)strlen((char *)GET_DB_CHARSETS),
(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));
OCICALL(ocip,
OCIDefineByPos(ocip->stmtp, &defnp1,
ocip->errp, (ub4) 1, parm,
PARM_BUFLEN, SQLT_CHR, (void*) 0,
&parm_len, (ub2 *)0, OCI_DEFAULT));
OCICALL(ocip,
OCIDefineByPos(ocip->stmtp, &defnp2,
ocip->errp, (ub4) 2, value,
OCI_NLS_MAXBUFSZ, SQLT_CHR, (void*) 0,
&value_len, (ub2 *)0, OCI_DEFAULT));
OCICALL(ocip,
OCIStmtExecute(ocip->svcp, ocip->stmtp,
ocip->errp, (ub4)0, (ub4)0,
(const OCISnapshot *)0,
(OCISnapshot *)0, (ub4)OCI_DEFAULT));
while (OCIStmtFetch(ocip->stmtp, ocip->errp, 1,
OCI_FETCH_NEXT, OCI_DEFAULT) == OCI_SUCCESS)
{
value[value_len] = '\0';
if (parm_len == strlen("NLS_CHARACTERSET") &&
!memcmp(parm, "NLS_CHARACTERSET", parm_len))
{
*char_csid = OCINlsCharSetNameToId(ocip->envp, value);
printf("Outbound database NLS_CHARACTERSET = %.*s (csid = %d) \n",
value_len, value, *char_csid);
}
else if (parm_len == strlen("NLS_NCHAR_CHARACTERSET") &&
!memcmp(parm, "NLS_NCHAR_CHARACTERSET", parm_len))
{
*nchar_csid = OCINlsCharSetNameToId(ocip->envp, value);
printf("Outbound database NLS_NCHAR_CHARACTERSET = %.*s (csid = %d) \n",
value_len, value, *nchar_csid);
}
}
disconnect_db(ocip);
}