static void
trace(plruby_context_t *ctx, int level, const char *fmt, ...)
{
OCIStmt *stmtp = NULL;
OCIBind *bindp = NULL;
OCIInd ind = 0;
char msg[2000];
ub2 msglen;
const char *stmt = "BEGIN DBMS_OUTPUT.PUT_LINE(:1); END;";
sword rv;
va_list ap;
if (level > trace_level) {
return;
}
va_start(ap, fmt);
vsnprintf(msg, sizeof(msg), fmt, ap);
va_end(ap);
msg[sizeof(msg) - 1] = '\0';
msglen = strlen(msg);
rv = OCIStmtPrepare2(ctx->svchp, &stmtp, ctx->errhp, (OraText*)stmt, strlen(stmt), NULL, 0, OCI_NTV_SYNTAX, OCI_DEFAULT);
if (rv != OCI_SUCCESS) {
return;
}
OCIBindByPos(stmtp, &bindp, ctx->errhp, 1, (OraText*)msg, msglen, SQLT_CHR, &ind, &msglen, NULL, 0, NULL, OCI_DEFAULT);
OCIStmtExecute(ctx->svchp, stmtp, ctx->errhp, 1, 0, NULL, NULL, OCI_DEFAULT);
OCIStmtRelease(stmtp, ctx->errhp, NULL, 0, OCI_DEFAULT);
}
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);
}
OCIStmt*
qdbl_oracle_statement_prepare(struct QdblOracleConnection* self, const char* statement, qlong_t size, struct QCLogger* logger)
{
/* variables */
sword status;
OCIStmt* ocistmt;
/* create the statement */
status = OCIStmtPrepare2( self->ocicontext, &ocistmt, self->ocierr, (const text*)statement, size, 0, 0, OCI_NTV_SYNTAX, OCI_DEFAULT );
if( qdbl_oracle_error(self->ocierr, status, self->logger, "prepare statement") )
{
return 0;
}
return ocistmt;
}
where empno = :id";
void update_salary(OCISvcCtx *svchp, OCIError *errhp, thdata *pthdata)
{
OCIBind *bndp1, *bndp2;
OCIStmt *stmthp;
int i;
if (verbose_flag)
printf ("updating salaries \n");
checkerr(errhp,
OCIStmtPrepare2 (svchp, &stmthp, /* returned stmt handle */
errhp, /* error handle */
(const OraText *) MY_DML, /* input statement text*/
strlen((char *) MY_DML), /* length of text */
NULL, 0, /* tagging parameters: optional */
OCI_NTV_SYNTAX, OCI_DEFAULT));
/* do parameter bindings */
checkerr(errhp, OCIBindByPos(stmthp, &bndp1, errhp, 1,
(void *) (pthdata->salaries),
(sword) sizeof(*(pthdata->salaries)),
SQLT_INT, (void *) NULL, (ub2 *) NULL,
(ub2 *) NULL, 0, (ub4 *) NULL, OCI_DEFAULT));
checkerr(errhp, OCIBindByPos(stmthp, &bndp2, errhp, 2,
(void *) (pthdata->id),
(sword) sizeof(*(pthdata->id)), SQLT_INT,
(void *) NULL, (ub2 *) NULL, (ub2 *) NULL, 0,
(ub4 *) NULL, OCI_DEFAULT));
/* execute the statement and commit */
checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, update_num, 0,
(OCISnapshot *) NULL, (OCISnapshot *) NULL,
OCI_COMMIT_ON_SUCCESS));
/* release the statement handle */
checkerr(errhp, OCIStmtRelease(stmthp, errhp,
(OraText *) NULL, 0, OCI_DEFAULT));
if (verbose_flag)
printf("updated salaries successfully\n");
}
myemp where empno > :EMPNO order by empno";
void multirow_fetch(OCISvcCtx *svchp, OCIError *errhp, thdata *pthdata)
{
OCIStmt *stmthp;
OCIBind *bndp;
if (verbose_flag)
printf ("demonstrating array fetching\n");
/* get a prepared statement handle */
checkerr(errhp, OCIStmtPrepare2 (svchp, &stmthp, errhp,
(const OraText *) MY_SELECT2,
strlen((char *) MY_SELECT2),
(OraText*) NULL, 0,
OCI_NTV_SYNTAX, OCI_DEFAULT));
/* bind input parameters */
checkerr(errhp, OCIBindByName(stmthp, &bndp, errhp, (text *) ":EMPNO",
-1, (void *) &(pthdata->multirow_fetch_id),
sizeof(int), SQLT_INT, (void *) NULL,
(ub2 *) NULL, (ub2 *) NULL, 0, (ub4 *) NULL,
OCI_DEFAULT));
/* Execute the query */
checkerr(errhp, OCIStmtExecute (svchp, stmthp, errhp, 0, 0,
(OCISnapshot *) NULL, (OCISnapshot *) NULL,
OCI_DEFAULT));
/* separating fetching rows into this method to help understand stage6 ref cursors */
multirow_fetch_from_emp(svchp, stmthp, errhp, pthdata);
if (verbose_flag)
printf ("array fetch successful\n");
/* release the statement handle */
checkerr(errhp, OCIStmtRelease(stmthp, errhp,
(OraText *) NULL, 0, OCI_DEFAULT));
}
static gboolean
oracle_sql_prepare (GSQLCursor *cursor, gchar *sql)
{
GSQL_TRACE_FUNC;
sword ret;
unsigned char op[2000];
GSQLEOracleSession *spec_session;
GSQLEOracleCursor *spec_cursor;
g_return_val_if_fail (GSQL_IS_CURSOR (cursor), FALSE);
g_return_val_if_fail (GSQL_IS_SESSION (cursor->session), FALSE);
spec_session = (GSQLEOracleSession *) cursor->session->spec;
spec_cursor = (GSQLEOracleCursor *) cursor->spec;
ret = OCIStmtPrepare2(spec_session->svchp,
&(spec_cursor->statement),
spec_cursor->errhp,
(text *) sql,
strlen(sql),
NULL, 0,
OCI_NTV_SYNTAX,
OCI_DEFAULT);
if ( oracle_check_error (cursor, ret))
return FALSE;
OCIAttrGet(spec_cursor->statement, OCI_HTYPE_STMT,
(dvoid*) &(spec_cursor->statement_type), 0, OCI_ATTR_STMT_TYPE,
spec_cursor->errhp);
g_signal_connect (G_OBJECT (cursor), "close", G_CALLBACK (on_cursor_close), NULL);
return TRUE;
}
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;
}
static int yada_oracle__exec(yada_t *_yada, char *sqlstr, int sqlstr_len)
{
char errbuf[1024];
sb4 errcode;
ub4 rows;
OCIStmt *stmt = NULL;
/* check database connection */
if(!_yada->_mod->env && !_yada->connect(_yada, NULL, NULL))
return(0);
if(!sqlstr_len)
sqlstr_len = strlen(sqlstr);
/* prepare the statement */
#ifdef OBSOLETE_OCI
/* allocate error handle */
if(OCIHandleAlloc(_yada->_mod->env, (void *)&stmt,
OCI_HTYPE_STMT, 0, NULL) != OCI_SUCCESS)
{
OCIErrorGet(_yada->_mod->env, 1, NULL, &errcode, errbuf, sizeof(errbuf),
OCI_HTYPE_ENV);
_yada_set_err(_yada, errcode, errbuf);
return(0);
}
if(OCIStmtPrepare(stmt, _yada->_mod->err, (text*)sqlstr,
(ub4)sqlstr_len, OCI_NTV_SYNTAX, OCI_DEFAULT) != OCI_SUCCESS)
{
#else
if(OCIStmtPrepare2(_yada->_mod->ctx, &stmt, _yada->_mod->err, (text*)sqlstr,
(ub4)sqlstr_len, NULL, 0, OCI_NTV_SYNTAX, OCI_DEFAULT) != OCI_SUCCESS)
{
#endif
OCIErrorGet(_yada->_mod->err, 1, NULL, &errcode, errbuf, sizeof(errbuf),
OCI_HTYPE_ERROR);
_yada_set_err(_yada, errcode, errbuf);
OCIHandleFree(stmt, OCI_HTYPE_STMT);
return(-1);
}
/* execute the statement */
if(OCIStmtExecute(_yada->_mod->ctx, stmt, _yada->_mod->err,
(ub4)1, (ub4)0, NULL, NULL, OCI_DEFAULT) != OCI_SUCCESS)
{
OCIErrorGet(_yada->_mod->err, 1, NULL, &errcode, errbuf, sizeof(errbuf),
OCI_HTYPE_ERROR);
_yada_set_err(_yada, errcode, errbuf);
OCIStmtRelease(stmt, _yada->_mod->err, NULL, 0, OCI_DEFAULT);
OCIHandleFree(stmt, OCI_HTYPE_STMT);
_yada->disconnect(_yada);
return(-1);
}
/* determine number of affected rows */
if(OCIAttrGet(stmt, OCI_HTYPE_STMT, &rows, NULL,
OCI_ATTR_ROW_COUNT, _yada->_mod->err) != OCI_SUCCESS)
{
OCIErrorGet(_yada->_mod->err, 1, NULL, &errcode, errbuf, sizeof(errbuf),
OCI_HTYPE_ERROR);
_yada_set_err(_yada, errcode, errbuf);
OCIStmtRelease(stmt, _yada->_mod->err, NULL, 0, OCI_DEFAULT);
OCIHandleFree(stmt, OCI_HTYPE_STMT);
_yada->disconnect(_yada);
return(-1);
}
/* commit if not in a transaction */
if(!(_yada->_mod->flags & ORAF_INTRX))
yada_oracle_commit(_yada);
/* release prepared statement */
OCIStmtRelease(stmt, _yada->_mod->err, NULL, 0, OCI_DEFAULT);
OCIHandleFree(stmt, OCI_HTYPE_STMT);
return(rows);
}
/******************************************************************************/
/** prepare a statement
* @return YADA_RESULT resource on success
*/
static yada_rc_t* yada_oracle__query(yada_t *_yada, char *sqlstr,
int sqlstr_len)
{
char errbuf[1024];
sb4 errcode;
yada_rc_t *_yrc;
OCIStmt *stmt = NULL;
/* check database connection */
if(!_yada->_mod->env && !_yada->connect(_yada, NULL, NULL))
return(0);
if(!sqlstr_len)
sqlstr_len = strlen(sqlstr);
/* prepare the statement */
#ifdef OBSOLETE_OCI
/* allocate error handle */
if(OCIHandleAlloc(_yada->_mod->env, (void*)&stmt,
OCI_HTYPE_STMT, 0, NULL) != OCI_SUCCESS)
{
OCIErrorGet(_yada->_mod->env, 1, NULL, &errcode, errbuf, sizeof(errbuf),
OCI_HTYPE_ENV);
_yada_set_err(_yada, errcode, errbuf);
return(NULL);
}
if(OCIStmtPrepare(stmt, _yada->_mod->err, (text*)sqlstr,
(ub4)sqlstr_len, OCI_NTV_SYNTAX, OCI_DEFAULT) != OCI_SUCCESS)
{
#else
if(OCIStmtPrepare2(_yada->_mod->ctx, &stmt, _yada->_mod->err, (text*)sqlstr,
(ub4)sqlstr_len, NULL, 0, OCI_NTV_SYNTAX, OCI_DEFAULT) != OCI_SUCCESS)
{
#endif
OCIErrorGet(_yada->_mod->err, 1, NULL, &errcode, errbuf, sizeof(errbuf),
OCI_HTYPE_ERROR);
_yada_set_err(_yada, errcode, errbuf);
OCIHandleFree(stmt, OCI_HTYPE_STMT);
return(NULL);
}
/* execute the statement */
if(OCIStmtExecute(_yada->_mod->ctx, stmt, _yada->_mod->err,
(ub4)0, (ub4)0, NULL, NULL, OCI_DEFAULT) != OCI_SUCCESS)
{
OCIErrorGet(_yada->_mod->err, 1, NULL, &errcode, errbuf, sizeof(errbuf),
OCI_HTYPE_ERROR);
_yada_set_err(_yada, errcode, errbuf);
OCIStmtRelease(stmt, _yada->_mod->err, NULL, 0, OCI_DEFAULT);
OCIHandleFree(stmt, OCI_HTYPE_STMT);
_yada->disconnect(_yada);
return(NULL);
}
/* return statement handle */
if(!(_yrc = _yada_rc_new(_yada)))
{
OCIStmtRelease(stmt, _yada->_mod->err, NULL, 0, OCI_DEFAULT);
OCIHandleFree(stmt, OCI_HTYPE_STMT);
_yada_set_yadaerr(_yada, YADA_ENOMEM);
return(NULL);
}
_yrc->t = YADA_RESULT;
/* alocate space for extended info */
if(!(_yrc->data = malloc(sizeof(extinfo))))
{
_yada->free(_yada, _yrc);
OCIStmtRelease(stmt, _yada->_mod->err, NULL, 0, OCI_DEFAULT);
OCIHandleFree(stmt, OCI_HTYPE_STMT);
_yada_set_yadaerr(_yada, YADA_ENOMEM);
return(NULL);
}
((extinfo*)_yrc->data)->stmt = stmt;
((extinfo*)_yrc->data)->bind = 1;
((extinfo*)_yrc->data)->invalid = 0;
return(_yrc);
}
/******************************************************************************/
/** free prepared statement
*/
static void yada_oracle_free_result(yada_t *_yada, yada_rc_t *_yrc)
{
if(!((extinfo*)_yrc->data)->invalid)
{
OCIStmtRelease(((extinfo*)_yrc->data)->stmt, _yada->_mod->err,
NULL, 0, OCI_DEFAULT);
OCIHandleFree(((extinfo*)_yrc->data)->stmt, OCI_HTYPE_STMT);
}
free(_yrc->data);
}
/******************************************************************************/
/** free bindset
*/
static void yada_oracle_free_bindset(yada_t *_yada, yada_rc_t *_yrc)
{
int i;
yada_bindset_t *bs;
bindset_ele_t *bse;
bs = _yrc->data;
/* free any allocated buffers */
for(i=0, bse=bs->ele; i<bs->eles; bse++, i++)
{
switch(bse->t)
{
/************************************************/
case -'b':
/************************************************/
/* free allocated buffer */
free(bse->var.buf);
/* fallthrough */
/************************************************/
case 'b':
/************************************************/
/* skip length element */
bse++;
break;
/************************************************/
case 'l':
case -'l':
/************************************************/
free(bse->tmp);
break;
/************************************************/
case -'s':
case -'e':
case -'v':
/************************************************/
/* free allocated buffer */
free(bse->var.buf);
break;
/************************************************/
}
} /* foreach(binding) */
/* call generic free function */
yada_free_bindset(_yada, _yrc);
}
static void
oracle_dbms_output (GSQLCursor *cursor)
{
GSQL_TRACE_FUNC;
GSQLEOracleSession *session;
GSQLWorkspace *workspace;
OCIBind *bind_p1 = NULL, *bind_p2 = NULL;
OCIStmt *sth = NULL;
sb2 ind = OCI_IND_NULL;
OCIError *errhp;
const gchar *sql = "begin dbms_output.get_line (line=>:p1, status=>:p2); end;";
gchar *mess = NULL;
// FIXME: use gconf
#define DBMS_OUTPUT_BUFFER 2048
gchar p1_value[DBMS_OUTPUT_BUFFER];
gint p2_value = 0, ret;
session = (GSQLEOracleSession *) cursor->session->spec;
workspace = gsql_session_get_workspace (cursor->session);
OCIHandleAlloc ((dvoid *)(session->envhp),
(dvoid **)&(errhp),
OCI_HTYPE_ERROR, 0, (dvoid **) 0);
OCIStmtPrepare2(session->svchp,
&sth,
errhp,
(text *) sql,
strlen(sql),
NULL, 0,
OCI_NTV_SYNTAX,
OCI_DEFAULT);
OCIBindByPos (sth, &bind_p1, errhp, (ub4) 1,
(dvoid *) p1_value, (sb4) DBMS_OUTPUT_BUFFER,
(ub2) SQLT_STR,
(dvoid *) &ind, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0,
(ub4) OCI_DEFAULT);
OCIBindByPos (sth, &bind_p2, errhp, (ub4) 2,
(dvoid *) &p2_value, (sb4) sizeof(p2_value),
(ub2) SQLT_INT,
(dvoid *) &ind, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0,
(ub4) OCI_DEFAULT);
while (p2_value != 1)
{
ret = OCIStmtExecute(session->svchp, sth,
errhp, 1, 0, 0, 0, OCI_DEFAULT);
if (ret != OCI_SUCCESS)
{
GSQL_DEBUG ("Couldn't get dbms output: %s",
oracle_get_error_string (errhp));
break;
}
if (g_utf8_strlen (p1_value, DBMS_OUTPUT_BUFFER) > 0)
{
mess = gsql_utils_escape_string (p1_value);
GSQL_DEBUG ("DBMS_OUTPUT: %s", mess);
gsql_message_add (workspace, GSQL_MESSAGE_OUTPUT, mess);
g_free (mess);
}
}
OCIHandleFree((dvoid *)sth, (ub4)OCI_HTYPE_STMT);
OCIHandleFree((dvoid *)errhp, (ub4)OCI_HTYPE_ERROR);
}
INTF_RET oci_exec_sql(const void *conn_handle, unsigned long & stmt_handle, const unsigned char * query_str, int query_str_len, inp_t *params_head, void * column_list)
{
function_success = SUCCESS;
#if DEBUG < DBG_5
fprintf(fp_log, "Executing \"%.*s;\"\n", query_str_len, query_str);
#endif
OCISvcCtx *svchp = (OCISvcCtx *)conn_handle;
OCIStmt *stmthp = NULL;
stmt_handle = 0;
/* Get a prepared statement handle */
checkerr(errhp, OCIStmtPrepare2(svchp,
&stmthp, /* returned statement handle */
errhp, /* error handle */
(OraText *) query_str, /* the statement text */
query_str_len, /* length of the text */
NULL, 0, /* tagging parameters: optional */
OCI_NTV_SYNTAX, OCI_DEFAULT));
if(function_success != SUCCESS) return function_success;
/* Bind variables */
ub2 type = SQLT_INT;
int idx = 1;
for(inp_t *param = params_head; param != NULL; param = param->next) {
switch (param->dty) {
case NUMBER:
type = SQLT_INT;
break;
case STRING:
type = SQLT_STR;
break;
}
param->bndp = NULL;
checkerr(errhp, OCIBindByPos(stmthp, (OCIBind **)&(param->bndp), errhp, idx,
(dvoid *) param->valuep, (sword) param->value_sz, type,
(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT));
if(function_success != SUCCESS) return function_success;
idx++;
}
ub4 stmt_typ = OCI_STMT_SELECT;
ub4 itrs = 0;
checkerr(errhp, OCIAttrGet((dvoid*) stmthp, (ub4) OCI_HTYPE_STMT,
(dvoid*) &stmt_typ, (ub4 *)NULL, (ub4)OCI_ATTR_STMT_TYPE, errhp));
if(function_success != SUCCESS) return function_success;
if(stmt_typ != OCI_STMT_SELECT)
itrs = 1;
/* execute the statement and commit */
checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, itrs, 0,
(OCISnapshot *)NULL, (OCISnapshot *)NULL,
OCI_COMMIT_ON_SUCCESS));
if(function_success != SUCCESS) return function_success;
if(stmt_typ == OCI_STMT_SELECT) {
OCIParam *mypard;
num_cols = 1;
sb4 parm_status;
/* Request a parameter descriptor for position 1 in the select-list */
parm_status = OCIParamGet(stmthp, OCI_HTYPE_STMT, errhp, (dvoid **)&mypard,
(ub4) num_cols);
checkerr(errhp, parm_status);
if(function_success != SUCCESS) return function_success;
/* Loop only if a descriptor was successfully retrieved for
* current position, starting at 1
*/
text *col_name;
ub4 len = 0;
char * data_type = NULL;
if (columns != NULL)
free(columns);
columns = NULL;
while (parm_status == OCI_SUCCESS) {
columns = (column_info *)realloc(columns, num_cols * sizeof(column_info));
column_info * cur_clm = &(columns[num_cols-1]);
/* Retrieve the data type attribute */
len = 0;
checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM,
(dvoid*) &len, (ub4 *)0, (ub4)OCI_ATTR_DATA_TYPE,
errhp));
if(function_success != SUCCESS) return function_success;
cur_clm->dtype = len;
switch (len) {
case SQLT_NUM:
case SQLT_VNU:
case SQLT_LNG:
data_type = (char*)"number";
break;
case SQLT_AVC:
case SQLT_AFC:
case SQLT_CHR:
case SQLT_STR:
case SQLT_VCS:
data_type = (char*)"string";
break;
case SQLT_INT:
case SQLT_UIN:
data_type = (char*)"integer";
break;
case SQLT_DAT:
data_type = (char*)"date";
break;
case SQLT_FLT:
data_type = (char*)"double";
break;
default:
data_type = (char*)"undefined";
break;
}
/* Retrieve the data size attribute */
len = 0;
checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM,
(dvoid*) &len, (ub4 *)0, (ub4)OCI_ATTR_DATA_SIZE,
errhp));
if(function_success != SUCCESS) return function_success;
cur_clm->dlen = len;
/* Retrieve the column name attribute */
len = 0;
checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM,
(dvoid**) &col_name, (ub4 *) &len, (ub4) OCI_ATTR_NAME,
errhp));
if(function_success != SUCCESS) return function_success;
char * column_name = new char[len+1];
sprintf_s(column_name, len+1, "%.*s", len, col_name);
append_coldef_to_list(column_name, data_type, cur_clm->dlen, column_list);
delete column_name;
col_name = NULL;
/* Increment counter and get next descriptor, if there is one */
if(OCI_SUCCESS != OCIDescriptorFree(mypard, OCI_DTYPE_PARAM))
return FAILURE;
num_cols++;
parm_status = OCIParamGet(stmthp, OCI_HTYPE_STMT, errhp, (dvoid **)&mypard,
(ub4) num_cols);
}
--num_cols;
if(function_success != SUCCESS)
return function_success;
REMOTE_LOG("Port: Returning Columns");
stmt_handle = (unsigned long)stmthp;
} else {
if(stmthp != NULL) {
checkerr(errhp, OCIStmtRelease(stmthp, errhp, NULL, 0, OCI_DEFAULT));
if(function_success != SUCCESS) return function_success;
}
REMOTE_LOG("Port: Executed non-select statement!");
}
return function_success;
}
