/*
* Send an SQL query to the server
*/
static int db_oracle_submit_query(const db_con_t* _h, const str* _s)
{
OCIBind* bind[MAX_BIND_HANDLES];
OCIDate odt[sizeof(bind)/sizeof(bind[0])];
str tmps;
sword status;
int pass;
ora_con_t* con = CON_ORA(_h);
query_data_t* pqd = con->pqdata;
size_t hc = pqd->_n + pqd->_nw;
OCIStmt *stmthp;
if (hc >= sizeof(bind)/sizeof(bind[0])) {
LM_ERR("too many bound. Rebuild with MAX_BIND_HANDLES >= %u\n",
(unsigned)hc);
return -1;
}
if (!pqd->_rs) {
/*
* This method is at ~25% faster as set OCI_COMMIT_ON_SUCCESS
* in StmtExecute
*/
tmps.len = snprintf(st_buf, sizeof(st_buf),
"begin %.*s; commit write batch nowait; end;",
_s->len, _s->s);
if ((unsigned)tmps.len >= sizeof(st_buf))
return sql_buf_small();
tmps.s = st_buf;
_s = &tmps;
}
pass = 1;
if (!con->connected) {
status = db_oracle_reconnect(con);
if (status != OCI_SUCCESS) {
LM_ERR("can't restore connection: %s\n", db_oracle_error(con, status));
return -2;
}
LM_INFO("connection restored\n");
--pass;
}
repeat:
stmthp = NULL;
status = OCIHandleAlloc(con->envhp, (dvoid**)(dvoid*)&stmthp,
OCI_HTYPE_STMT, 0, NULL);
if (status != OCI_SUCCESS)
goto ora_err;
status = OCIStmtPrepare(stmthp, con->errhp, (text*)_s->s, _s->len,
OCI_NTV_SYNTAX, OCI_DEFAULT);
if (status != OCI_SUCCESS)
goto ora_err;
if (hc) {
bmap_t bmap;
size_t pos = 1;
int i;
memset(bind, 0, hc*sizeof(bind[0]));
for (i = 0; i < pqd->_n; i++) {
if (db_oracle_val2bind(&bmap, &pqd->_v[i], &odt[pos]) < 0)
goto bind_err;
status = OCIBindByPos(stmthp, &bind[pos], con->errhp,
pos, bmap.addr, bmap.size, bmap.type,
NULL, NULL, NULL, 0, NULL, OCI_DEFAULT);
if (status != OCI_SUCCESS)
goto ora_err;
++pos;
}
for (i = 0; i < pqd->_nw; i++) {
if (db_oracle_val2bind(&bmap, &pqd->_w[i], &odt[pos]) < 0) {
bind_err:
OCIHandleFree(stmthp, OCI_HTYPE_STMT);
LM_ERR("can't map values\n");
return -3;
}
status = OCIBindByPos(stmthp, &bind[pos], con->errhp,
pos, bmap.addr, bmap.size, bmap.type,
NULL, NULL, NULL, 0, NULL, OCI_DEFAULT);
if (status != OCI_SUCCESS)
goto ora_err;
++pos;
}
}
// timelimited operation
status = begin_timelimit(con, 0);
if (status != OCI_SUCCESS) goto ora_err;
do status = OCIStmtExecute(con->svchp, stmthp, con->errhp,
!pqd->_rs, 0, NULL, NULL,
pqd->_rs ? OCI_STMT_SCROLLABLE_READONLY : OCI_DEFAULT);
while (wait_timelimit(con, status));
if (done_timelimit(con, status)) goto stop_exec;
switch (status) {
case OCI_SUCCESS_WITH_INFO:
LM_WARN("driver: %s\n", db_oracle_errorinfo(con));
//PASS THRU
case OCI_SUCCESS:
if (pqd->_rs)
*pqd->_rs = stmthp;
else
OCIHandleFree(stmthp, OCI_HTYPE_STMT);
return 0;
default:
pass = -pass;
break;
}
ora_err:
LM_ERR("driver: %s\n", db_oracle_error(con, status));
stop_exec:
if (stmthp)
OCIHandleFree(stmthp, OCI_HTYPE_STMT);
if (pass == -1 && !con->connected) {
/* Attemtp to reconnect */
if (db_oracle_reconnect(con) == OCI_SUCCESS) {
LM_NOTICE("attempt repeat after reconnect\n");
pass = 0;
goto repeat;
}
LM_ERR("connection loss\n");
}
return -4;
}
bool COracleRecordset::Init(COracleConnection *parent, OCIStmt *hStmt, bool select)
{
sb4 status;
int is_string_data=0;
m_bEof = false;
m_parent = parent;
m_hStmt = hStmt;
if (select)
{
CServerIo::trace(3,"COracleRecordset::Init - OCIStmtExecute(Describe_Only,select=%d)",select?0:1);
status=OCIStmtExecute(parent->m_hSvcCtx, hStmt, parent->m_hErr, select?0:1, 0, NULL, NULL, OCI_DESCRIBE_ONLY);
} else {
CServerIo::trace(3,"COracleRecordset::Init - OCIStmtExecute(%s,select=%d)",parent->m_bAutoCommit?"OCI_COMMIT_ON_SUCCESS":"OCI_DEFAULT",select?0:1);
status=OCIStmtExecute(parent->m_hSvcCtx, hStmt, parent->m_hErr, select?0:1, 0, NULL, NULL, parent->m_bAutoCommit?OCI_COMMIT_ON_SUCCESS:OCI_DEFAULT);
}
CServerIo::trace(3,"COracleRecordset::Init - CheckError(%d)",(int)status);
if(!parent->CheckError(parent->m_hErr,status))
return false;
CServerIo::trace(3,"COracleRecordset::Init - OCIAttrGet(OCI_ATTR_PARAM_COUNT)");
status=OCIAttrGet(hStmt, OCI_HTYPE_STMT, &m_num_fields, 0, OCI_ATTR_PARAM_COUNT, parent->m_hErr);
CServerIo::trace(3,"COracleRecordset::Init - CheckError(%d) m_num_fields=%d",(int)status,(int)m_num_fields);
if(!parent->CheckError(parent->m_hErr,status))
return false;
CServerIo::trace(3,"COracleRecordset::Init - resize");
m_sqlfields.resize(m_num_fields);
CServerIo::trace(3,"COracleRecordset::Init - for loop");
for(size_t n=0; n<m_num_fields; n++)
{
OCIParam *hParam;
wchar_t szCol[128];
ub4 ubCol = sizeof(szCol);
unsigned char *szaColPtr;
ub4 ubaCol = 0;
CServerIo::trace(3,"COracleRecordset::Init - for loop %d",(int)n);
CServerIo::trace(3,"COracleRecordset::Init - OCIParamGet(OCI_HTYPE_STMT) n==%d",n+1);
OCIParamGet(hStmt, OCI_HTYPE_STMT, parent->m_hErr, (void**)&hParam, n+1);
CServerIo::trace(3,"COracleRecordset::Init - OCIAttrGet(OCI_ATTR_DATA_TYPE)");
OCIAttrGet(hParam, OCI_DTYPE_PARAM, &m_sqlfields[n].type, NULL, OCI_ATTR_DATA_TYPE, parent->m_hErr);
CServerIo::trace(3,"COracleRecordset::Init - OCIAttrGet(OCI_ATTR_TYPECODE)");
OCIAttrGet(hParam, OCI_DTYPE_PARAM, &m_sqlfields[n].typecode, NULL, OCI_ATTR_TYPECODE, parent->m_hErr);
switch (m_sqlfields[n].type) {
case OCI_TYPECODE_REF:
CServerIo::trace(3," - TYPECODE REF");
break;
case OCI_TYPECODE_DATE:
CServerIo::trace(3," - TYPECODE DATE");
break;
case OCI_TYPECODE_REAL:
CServerIo::trace(3," - TYPECODE REAL");
break;
case OCI_TYPECODE_DOUBLE:
CServerIo::trace(3," - TYPECODE DOUBLE");
break;
case OCI_TYPECODE_FLOAT:
CServerIo::trace(3," - TYPECODE FLOAT");
break;
case OCI_TYPECODE_NUMBER:
CServerIo::trace(3," - TYPECODE NUMBER");
break;
case OCI_TYPECODE_DECIMAL:
CServerIo::trace(3," - TYPECODE DECIMAL");
break;
case OCI_TYPECODE_OCTET:
CServerIo::trace(3," - TYPECODE OCTET");
break;
case OCI_TYPECODE_INTEGER:
CServerIo::trace(3," - TYPECODE INTEGER");
break;
case OCI_TYPECODE_SMALLINT:
CServerIo::trace(3," - TYPECODE SMALLINT");
break;
case OCI_TYPECODE_RAW:
CServerIo::trace(3," - TYPECODE RAW");
break;
case OCI_TYPECODE_VARCHAR2:
CServerIo::trace(3," - TYPECODE VARCHAR2");
break;
case OCI_TYPECODE_VARCHAR:
CServerIo::trace(3," - TYPECODE VARCHAR");
break;
case OCI_TYPECODE_CHAR:
CServerIo::trace(3," - TYPECODE CHAR");
break;
case OCI_TYPECODE_VARRAY:
CServerIo::trace(3," - TYPECODE VARRAY");
break;
case OCI_TYPECODE_TABLE:
CServerIo::trace(3," - TYPECODE TABLE");
break;
case OCI_TYPECODE_CLOB:
CServerIo::trace(3," - TYPECODE CLOB");
break;
case OCI_TYPECODE_BLOB:
CServerIo::trace(3," - TYPECODE BLOB");
break;
case OCI_TYPECODE_BFILE:
CServerIo::trace(3," - TYPECODE BFILE");
break;
case OCI_TYPECODE_OBJECT:
CServerIo::trace(3," - TYPECODE OBJECT");
break;
case OCI_TYPECODE_NAMEDCOLLECTION:
CServerIo::trace(3," - TYPECODE NAMED COLL.");
break;
default:
CServerIo::trace(3," - TYPECODE unknown");
}
CServerIo::trace(3,"COracleRecordset::Init - OCIAttrGet(OCI_ATTR_NAME)");
szaColPtr=NULL;
OCIAttrGet(hParam, OCI_DTYPE_PARAM, (void**)&szaColPtr, &ubaCol, OCI_ATTR_NAME, parent->m_hErr);
if (szaColPtr!=NULL)
CServerIo::trace(3," - ATTR_NAME %s (%d).",szaColPtr,(int)ubaCol);
else
CServerIo::trace(3," - ATTR_NAME NULL (%d).",(int)ubaCol);
CServerIo::trace(3,"COracleRecordset::Init - OCIAttrGet(OCI_ATTR_PRECISION)");
OCIAttrGet(hParam, OCI_DTYPE_PARAM, &m_sqlfields[n].precision, NULL, OCI_ATTR_PRECISION, parent->m_hErr);
CServerIo::trace(3,"COracleRecordset::Init - OCIAttrGet(OCI_ATTR_SCALE)");
OCIAttrGet(hParam, OCI_DTYPE_PARAM, &m_sqlfields[n].scale, NULL, OCI_ATTR_SCALE, parent->m_hErr);
CServerIo::trace(3,"COracleRecordset::Init - OCIAttrGet(OCI_ATTR_DATA_SIZE)");
OCIAttrGet(hParam, OCI_DTYPE_PARAM, &m_sqlfields[n].size, NULL, OCI_ATTR_DATA_SIZE, parent->m_hErr);
CServerIo::trace(3," - ATTR_DATA_SIZE %d.",(int)m_sqlfields[n].size);
m_sqlfields[n].field = n;
m_sqlfields[n].hStmt = m_hStmt;
m_sqlfields[n].name = cvs::wide((char *)szaColPtr);
switch(m_sqlfields[n].type)
{
case OCI_TYPECODE_REAL:
case OCI_TYPECODE_FLOAT:
case OCI_TYPECODE_DOUBLE:
CServerIo::trace(3," ::Init - OCI_TYPECODE_REAL");
m_sqlfields[n].fldtype=SQLT_FLT;
m_sqlfields[n].fldlen=sizeof(double);
break;
case OCI_TYPECODE_OCTET:
case OCI_TYPECODE_INTEGER:
case OCI_TYPECODE_SMALLINT:
CServerIo::trace(3," ::Init - OCI_TYPECODE_OCTET");
m_sqlfields[n].fldtype=SQLT_INT;
m_sqlfields[n].fldlen=sizeof(int);
break;
case OCI_TYPECODE_NUMBER:
CServerIo::trace(3," ::Init - OCI_TYPECODE_NUMBER");
if(m_sqlfields[n].precision)
{
m_sqlfields[n].fldtype=SQLT_FLT;
m_sqlfields[n].fldlen=sizeof(double);
}
else
{
m_sqlfields[n].fldtype=SQLT_INT;
m_sqlfields[n].fldlen=sizeof(int);
}
break;
case OCI_TYPECODE_VARCHAR2:
case OCI_TYPECODE_VARCHAR:
case OCI_TYPECODE_CHAR:
case OCI_TYPECODE_CLOB:
case OCI_TYPECODE_BLOB:
case OCI_TYPECODE_DATE:
CServerIo::trace(3," ::Init - OCI_TYPECODE_VARCHAR2");
m_sqlfields[n].fldtype=SQLT_STR;
m_sqlfields[n].fldlen=m_sqlfields[n].size+1;
is_string_data=1;
break;
case OCI_TYPECODE_REF:
case OCI_TYPECODE_RAW:
case OCI_TYPECODE_VARRAY:
case OCI_TYPECODE_TABLE:
case OCI_TYPECODE_BFILE:
case OCI_TYPECODE_OBJECT:
case OCI_TYPECODE_NAMEDCOLLECTION:
default:
CServerIo::trace(3," ::Init - Unsuppoered datatype");
//CServerIo::error("Field type %d unsupported for field %s\n",m_sqlfields[n].type,(const char *)cvs::narrow(szCol));
CServerIo::error("Field type %d unsupported for field %s\n",m_sqlfields[n].type,szaColPtr);
return false;
}
CServerIo::trace(3," ::Init - end of datatype");
if(m_sqlfields[n].fldlen)
{
OCIDefine *hDef = NULL;
CServerIo::trace(3," ::Init - m_sqlfields[n].fldlen=%d",(int)m_sqlfields[n].fldlen);
if ((m_sqlfields[n].fldtype==SQLT_STR) ||
(is_string_data))
{
// the result of the OCI DefineByPos is a buffer of
// characters. We could leave just that but
// this (incorrectly?) assumes the OCI client is
// going to present this data as UTF8. In truth
// it is based on the bind OCI_HTYPE_BIND attribute
// OCI_ATTR_CHARSET_ID
CServerIo::trace(3," ::Init - OCIAttrGet(OCI_ATTR_CHARSET_ID)");
OCIAttrGet(hParam, OCI_DTYPE_PARAM, &m_sqlfields[n].csid, NULL, OCI_ATTR_CHARSET_ID, parent->m_hErr);
if (m_sqlfields[n].csid == OCI_UCS2ID)
CServerIo::trace(3," ::Init - UCS2 Charset");
if (m_sqlfields[n].csid == 2000 /* OCI_UTF16ID */)
CServerIo::trace(3," ::Init - UTF16 Charset");
else
CServerIo::trace(3," ::Init - Charset %d (not UCS2)",(int)m_sqlfields[n].csid);
/* The only valid handle types for setting charset are:
Bind Handle
Define Handle
Direct Path Loading Handle
Direct Path Column Parameter
*/
/*
CServerIo::trace(3,"COracleRecordset::Init - OCIParamGet(OCI_DTYPE_PARM)");
OCIParamGet(hParam, OCI_DTYPE_PARM, parent->m_hErr, (void **)&hColDesc, 1);
CServerIo::trace(3," ::Init - Force Direct Path Loading Handle to UCS2 Charset");
m_sqlfields[n].csid=OCI_UCS2ID;
status=OCIAttrSet((void *)hColDesc, OCI_DTYPE_PARAM,(void *)&m_sqlfields[n].csid,0,OCI_ATTR_CHARSET_ID,parent->m_hErr);
CServerIo::trace(3," ::Init - Force Direct Path Loading Handle to UCS2 Charset (%d)",(int)status);
if(!parent->CheckError(parent->m_hErr,status)
CServerIo::trace(1,"Unable to set UCS2 character set \"%s\".",parent->ErrorString());
OCIDescriptorFree((dvoid *)hColDesc, OCI_DTYPE_PARAM));
*/
}
m_sqlfields[n].data = malloc((m_sqlfields[n].fldlen+2)+(is_string_data)?m_sqlfields[n].fldlen+2:0);
memset(m_sqlfields[n].data,0,m_sqlfields[n].fldlen+(is_string_data)?m_sqlfields[n].fldlen+2:0);
CServerIo::trace(3," ::Init - OCIDefineByPos()");
m_sqlfields[n].datalen[0]=0 /*m_sqlfields[n].fldlen*/ ;
status=OCIDefineByPos(m_hStmt,&hDef,parent->m_hErr,n+1,(void **)m_sqlfields[n].data,m_sqlfields[n].fldlen,m_sqlfields[n].fldtype,0,(unsigned short *)&m_sqlfields[n].datalen,0,OCI_DEFAULT);
CServerIo::trace(3," ::Init - OCIDefineByPos()=%d,%d",status,(int)m_sqlfields[n].datalen[0]);
if(!parent->CheckError(parent->m_hErr,status))
{
CServerIo::trace(1,"Unable to bind column %s due to error \"%s\".",(const char*)szaColPtr,parent->ErrorString());
return false;
}
if (is_string_data)
{
if (m_sqlfields[n].csid<OCI_UCS2ID)
{
m_sqlfields[n].csid=OCI_UCS2ID;
CServerIo::trace(3," ::Init - Define Handle to UCS2 Charset");
status=OCIAttrSet((void *)hDef, OCI_HTYPE_DEFINE,(void *)&m_sqlfields[n].csid,0,OCI_ATTR_CHARSET_ID,parent->m_hErr);
CServerIo::trace(3," ::Init - Define Handle to UCS2 Charset (%d)",(int)status);
if(!parent->CheckError(parent->m_hErr,status))
CServerIo::trace(1,"Unable to set UCS2 character set \"%s\".",parent->ErrorString());
}
}
/*
szaColPtr=NULL; ubaCol=0;
OCIAttrGet(hParam, OCI_HTYPE_DEFINE, (void**)&szaColPtr, &ubaCol, OCI_ATTR_NAME, parent->m_hErr);
if (szaColPtr!=NULL)
{
CServerIo::trace(3," - ATTR_NAME (Define Handle) %s (%d).",szaColPtr,(int)ubaCol);
m_sqlfields[n].name = cvs::wide((char *)szaColPtr);
}
else
CServerIo::trace(3," - ATTR_NAME (Define Handle) NULL (%d).",(int)ubaCol);
*/
CServerIo::trace(3," ::Init - complete");
}
}
if (select)
{
CServerIo::trace(3,"COracleRecordset::Init - OCIStmtExecute(%s,select=%d)",parent->m_bAutoCommit?"OCI_COMMIT_ON_SUCCESS":"OCI_DEFAULT",select?0:1);
status=OCIStmtExecute(parent->m_hSvcCtx, hStmt, parent->m_hErr, select?0:1, 0, NULL, NULL, parent->m_bAutoCommit?OCI_COMMIT_ON_SUCCESS:OCI_DEFAULT);
CServerIo::trace(3,"COracleRecordset::Init - CheckError(%d)",(int)status);
if(!parent->CheckError(parent->m_hErr,status))
return false;
}
if(m_num_fields)
{
CServerIo::trace(3," ::Init - m_num_fields=%d",(int)(m_num_fields));
if(!Next() && !m_bEof)
return false;
}
CServerIo::trace(3," ::Init - return");
return true;
}
/*
* Add a new status change to the database
* Params:
* session - user session name
* uid - uid of person changing status
* status - type of new status (away|back|...)
* descr - new description
* change_time - time of status change (no. of seconds since 1st Jan 1970)
* quiet - print error messages or not
* Returns 0 on success.
*/
int oralog_db_new_status(char *session, char *uid, char *status, char *descr, time_t change_time, int quiet)
{
OCIStmt *hp_stmt = NULL; /* SQL statement handle */
OCIBind *bind[5] = { NULL }; /* Needed for C-var to SQL-var binding */
sword retstat = 0; /* Return value of function */
int errors = 0; /* Error counter */
uword invalid = 0; /* for DateCheck() */
OCIDate oci_date; /* for 'change_time' - oracle internal representation */
struct tm *tm_chtime = NULL; /* for 'change_time' - standard C representation */
int print_errors = (quiet) ? 0 : 1; /* if nonzero then print errors into status window */
OCIError *hp_error = NULL; /* oci error handle */
/* SQL statement to execute */
text *sqlstmt = (text *) "INSERT INTO status_changes VALUES( status_changes_seq.nextval, :session_uid, :changes_uid, :status, :descr, :change_time)";
pthread_mutex_lock(&oralog_oper_lock);
if (!oralog_is_connected()) {
debug("[logsoracle] can't log status - not connected\n");
pthread_mutex_unlock(&oralog_oper_lock);
return 1;
}
check_string_len(session);
check_string_len(uid);
check_string_len(status);
check_string_len(descr);
/* Create local handles (OCIBind's are created automaticly) */
OCIHandleAlloc( (dvoid *)hp_env, (dvoid **)&hp_stmt, OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
OCIHandleAlloc( (dvoid *)hp_env, (dvoid **)&hp_error, OCI_HTYPE_ERROR, 0, (dvoid **) 0);
/* Create DATE */
tm_chtime = localtime(&change_time);
memset(&oci_date, 0, sizeof(OCIDate));
OCIDateSetTime( &oci_date, (ub1)tm_chtime->tm_hour, (ub1)tm_chtime->tm_min, (ub1)tm_chtime->tm_sec );
OCIDateSetDate( &oci_date, (sb2)tm_chtime->tm_year+1900, (ub1)tm_chtime->tm_mon+1, (ub1)tm_chtime->tm_mday );
/* check if provided 'change_time' was mapped correctly */
retstat = OCIDateCheck(hp_error, &oci_date, &invalid);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* prepare SQL statement */
debug("[logsoracle] preparing new status statement\n");
retstat = OCIStmtPrepare(hp_stmt, hp_error, (text *)sqlstmt, (ub4)ora_strlen((char *)sqlstmt), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* bind variables */
debug("[logsoracle] binding..\n");
retstat = OCIBindByName(hp_stmt, &bind[0], hp_error, (text *) ":session_uid", -1, (dvoid *)session,
ora_strlen(session)+1, SQLT_STR, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
retstat = OCIBindByName(hp_stmt, &bind[1], hp_error, (text *) ":changes_uid",
-1, (dvoid *) uid,
ora_strlen(uid)+1, SQLT_STR, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
retstat = OCIBindByName(hp_stmt, &bind[2], hp_error, (text *) ":status",
-1, (dvoid *) status,
ora_strlen(status)+1, SQLT_STR, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
retstat = OCIBindByName(hp_stmt, &bind[3], hp_error, (text *) ":descr",
-1, (dvoid *) descr,
ora_strlen(descr)+1, SQLT_STR, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* Oracle type DATE can be easily bind by using OCIDate struct and SQLT_ODT datatype */
retstat = OCIBindByName(hp_stmt, &bind[4], hp_error, (text *) ":change_time",
-1, (dvoid *) &oci_date,
(sword) sizeof(OCIDate), SQLT_ODT, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* Statement ready - execute */
debug("[logsoracle] executing\n");
retstat = OCIStmtExecute(hp_service, hp_stmt, hp_error, (ub4) 1, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* Commit transaction */
if(!errors) {
debug("[logsoracle] commit\n");
OCITransCommit(hp_service, hp_error, (ub4) 0);
}
else {
debug("[logsoracle] errors present - aborting transaction\n");
OCITransRollback(hp_service, hp_error, (ub4) OCI_DEFAULT);
}
/* Cleanup (bind handles should be removed as a part of statement) */
if(hp_stmt)
OCIHandleFree(hp_stmt, OCI_HTYPE_STMT);
if(hp_error)
OCIHandleFree(hp_error, OCI_HTYPE_ERROR);
pthread_mutex_unlock(&oralog_oper_lock);
return 0;
}
/*
* Add a new message to the database.
* session - session name
* sender_uid -
* rcpts -
* content -
* recv_time - time of arrival
* quiet - print error messages?
* Returns 0 on success
*/
int oralog_db_new_msg(char *session, char *sender_uid, char **rcpts, char *content, time_t recv_time, int quiet)
{
int i;
/* There will be at least 2 INSERTs - 1 into 'messages' table and 1 (at least)
into the 'recipients' table. */
text *sqlstmt_msg = (text *)"INSERT INTO messages VALUES(messages_seq.nextval, :session_uid, :sender_uid, :content, :recv_time) RETURNING id INTO :msg_id";
text *sqlstmt_rcp = (text *)"INSERT INTO recipients VALUES(recipients_seq.nextval, :recipient_uid, :msg_id)";
/* Statement & bind handles */
OCIStmt *hp_stmt_msg = NULL;
OCIStmt *hp_stmt_rcp = NULL;
OCIBind *bind_msg[5] = { NULL };
OCIBind *bind_rcp[2] = { NULL };
/* Control vars */
sword retstat = 0;
int errors = 0;
int print_errors = (quiet) ? 0 : 1;
OCIError *hp_error = NULL;
/* Date handling */
uword invalid = 0;
OCIDate oci_recvtime;
struct tm *tm_recvtime = NULL;
/* ID of inserted message will be put here */
OCINumber msg_id;
int init_val = 0; /* this variable is needed for initialization only */
pthread_mutex_lock(&oralog_oper_lock);
if (!oralog_is_connected()) {
debug("[logsoracle] can't log msg - not connected\n");
pthread_mutex_unlock(&oralog_oper_lock);
return 1;
}
check_string_len(session);
check_string_len(sender_uid);
check_string_len(content);
/* Create local handles (OCIBind's are created automaticly) */
OCIHandleAlloc( (dvoid *)hp_env, (dvoid **)&hp_stmt_msg, OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
OCIHandleAlloc( (dvoid *)hp_env, (dvoid **)&hp_stmt_rcp, OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
OCIHandleAlloc( (dvoid *)hp_env, (dvoid **)&hp_error, OCI_HTYPE_ERROR, 0, (dvoid **) 0);
/* Create DATE in Oracle format */
tm_recvtime = localtime(&recv_time);
memset(&oci_recvtime, 0, sizeof(OCIDate));
OCIDateSetTime( &oci_recvtime, (ub1)tm_recvtime->tm_hour, (ub1)tm_recvtime->tm_min, (ub1)tm_recvtime->tm_sec );
OCIDateSetDate( &oci_recvtime, (sb2)tm_recvtime->tm_year+1900, (ub1)tm_recvtime->tm_mon+1, (ub1)tm_recvtime->tm_mday );
/* check if provided 'recv_time' was mapped correctly */
retstat = OCIDateCheck(hp_error, &oci_recvtime, &invalid);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* prepare SQL statements */
debug("[logsoracle] preparing new messages insert statement\n");
retstat = OCIStmtPrepare(hp_stmt_msg, hp_error, (text *)sqlstmt_msg, (ub4)ora_strlen((char *)sqlstmt_msg), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
debug("[logsoracle] preparing new recipients insert statement\n");
retstat = OCIStmtPrepare(hp_stmt_rcp, hp_error, (text *)sqlstmt_rcp, (ub4)ora_strlen((char *)sqlstmt_rcp), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* bind the placeholders */
debug("[logsoracle] binding messages insert..\n");
retstat = OCIBindByName(hp_stmt_msg, &bind_msg[0], hp_error, (text *) ":session_uid", -1, (dvoid *)session,
ora_strlen(session)+1, SQLT_STR, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
retstat = OCIBindByName(hp_stmt_msg, &bind_msg[1], hp_error, (text *) ":sender_uid", -1, (dvoid *)sender_uid,
ora_strlen(sender_uid)+1, SQLT_STR, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
retstat = OCIBindByName(hp_stmt_msg, &bind_msg[2], hp_error, (text *) ":content", -1, (dvoid *)content,
ora_strlen(content)+1, SQLT_STR, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
retstat = OCIBindByName(hp_stmt_msg, &bind_msg[3], hp_error, (text *) ":recv_time",
-1, (dvoid *) &oci_recvtime,
(sword) sizeof(OCIDate), SQLT_ODT, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* Create a new OCINumber - will be under msg_id */
retstat = OCINumberFromInt(hp_error, &init_val, sizeof(init_val), OCI_NUMBER_SIGNED, &msg_id);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* bind the OCINumber */
retstat = OCIBindByName(hp_stmt_msg, &bind_msg[4], hp_error, (text *) ":msg_id",
-1, (dvoid *) &msg_id,
(sword) sizeof(msg_id), SQLT_VNU, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* Statement ready - execute */
debug("[logsoracle] executing insert on messages\n");
retstat = OCIStmtExecute(hp_service, hp_stmt_msg, hp_error, (ub4) 1, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* You can check recieved message ID like this:
retstat = OCINumberToInt(hp_error, &msg_id, sizeof(int), OCI_NUMBER_SIGNED, &tmpval);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
debug("[logsoracle] recieved message id: %d\n", tmpval);
*/
/* Insert into recipients table */
if(rcpts) {
for(i=0; rcpts[i] != NULL; i++) {
check_string_len(rcpts[i]);
debug("[logsoracle] binding recipients\n");
retstat = OCIBindByName(hp_stmt_rcp, &bind_rcp[0], hp_error, (text *) ":recipient_uid", -1, (dvoid *)rcpts[i],
ora_strlen(rcpts[i])+1, SQLT_STR, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
retstat = OCIBindByName(hp_stmt_rcp, &bind_rcp[1], hp_error, (text *) ":msg_id",
-1, (dvoid *) &msg_id,
(sword) sizeof(msg_id), SQLT_VNU, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
debug("[logsoracle] executing insert on recipients\n");
retstat = OCIStmtExecute(hp_service, hp_stmt_rcp, hp_error, (ub4) 1, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
}
}
/* Commit transaction */
if(!errors) {
debug("[logsoracle] commit\n");
OCITransCommit(hp_service, hp_error, (ub4) 0);
}
else {
debug("[logsoracle] errors present - aborting transaction\n");
OCITransRollback(hp_service, hp_error, (ub4) OCI_DEFAULT);
}
/* Cleanup (bind handles should be removed as a part of statement) */
if(hp_stmt_msg)
OCIHandleFree(hp_stmt_msg, OCI_HTYPE_STMT);
if(hp_stmt_rcp)
OCIHandleFree(hp_stmt_rcp, OCI_HTYPE_STMT);
if(hp_error)
OCIHandleFree(hp_error, OCI_HTYPE_ERROR);
pthread_mutex_unlock(&oralog_oper_lock);
return 0;
}
/*
* Execute SQL statement. For non-select statements only.
*/
int zbx_db_vexecute(const char *fmt, va_list args)
{
char *sql = NULL;
int ret = ZBX_DB_OK;
double sec = 0;
#if defined(HAVE_IBM_DB2)
SQLHANDLE hstmt = 0;
SQLRETURN ret1;
SQLLEN row1;
SQLLEN rows = 0;
#elif defined(HAVE_MYSQL)
int status;
#elif defined(HAVE_ORACLE)
OCIStmt *stmthp = NULL;
sword err = OCI_SUCCESS;
#elif defined(HAVE_POSTGRESQL)
PGresult *result;
char *error = NULL;
#elif defined(HAVE_SQLITE3)
int err;
char *error = NULL;
#endif
if (0 != CONFIG_LOG_SLOW_QUERIES)
sec = zbx_time();
sql = zbx_dvsprintf(sql, fmt, args);
if (0 == txn_init && 0 == txn_level)
zabbix_log(LOG_LEVEL_DEBUG, "query without transaction detected");
if (1 == txn_error)
{
zabbix_log(LOG_LEVEL_DEBUG, "ignoring query [txnlev:%d] [%s] within failed transaction", txn_level, sql);
ret = ZBX_DB_FAIL;
goto clean;
}
zabbix_log(LOG_LEVEL_DEBUG, "query [txnlev:%d] [%s]", txn_level, sql);
#if defined(HAVE_IBM_DB2)
/* allocate a statement handle */
if (SUCCEED != zbx_ibm_db2_success(SQLAllocHandle(SQL_HANDLE_STMT, ibm_db2.hdbc, &hstmt)))
ret = ZBX_DB_DOWN;
/* directly execute the statement; returns SQL_NO_DATA_FOUND when no rows were affected */
if (ZBX_DB_OK == ret && SUCCEED != zbx_ibm_db2_success_ext(SQLExecDirect(hstmt, (SQLCHAR *)sql, SQL_NTS)))
ret = ZBX_DB_DOWN;
/* get number of affected rows */
if (ZBX_DB_OK == ret && SUCCEED != zbx_ibm_db2_success(SQLRowCount(hstmt, &rows)))
ret = ZBX_DB_DOWN;
/* process other SQL statements in the batch */
while (ZBX_DB_OK == ret && SUCCEED == zbx_ibm_db2_success(ret1 = SQLMoreResults(hstmt)))
{
if (SUCCEED != zbx_ibm_db2_success(SQLRowCount(hstmt, &row1)))
ret = ZBX_DB_DOWN;
else
rows += row1;
}
if (ZBX_DB_OK == ret && SQL_NO_DATA_FOUND != ret1)
ret = ZBX_DB_DOWN;
if (ZBX_DB_OK != ret)
{
zbx_ibm_db2_log_errors(SQL_HANDLE_DBC, ibm_db2.hdbc);
zbx_ibm_db2_log_errors(SQL_HANDLE_STMT, hstmt);
ret = (SQL_CD_TRUE == IBM_DB2server_status() ? ZBX_DB_FAIL : ZBX_DB_DOWN);
}
else if (0 <= rows)
{
ret = (int)rows;
}
if (hstmt)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
#elif defined(HAVE_MYSQL)
if (NULL == conn)
{
zabbix_errlog(ERR_Z3003);
ret = ZBX_DB_FAIL;
}
else
{
if (0 != (status = mysql_query(conn, sql)))
{
zabbix_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);
switch (mysql_errno(conn))
{
case CR_CONN_HOST_ERROR:
case CR_SERVER_GONE_ERROR:
case CR_CONNECTION_ERROR:
case CR_SERVER_LOST:
case ER_SERVER_SHUTDOWN:
case ER_ACCESS_DENIED_ERROR: /* wrong user or password */
case ER_ILLEGAL_GRANT_FOR_TABLE: /* user without any privileges */
case ER_TABLEACCESS_DENIED_ERROR:/* user without some privilege */
case ER_UNKNOWN_ERROR:
ret = ZBX_DB_DOWN;
break;
default:
ret = ZBX_DB_FAIL;
break;
}
}
else
{
do
{
if (0 != mysql_field_count(conn))
{
zabbix_log(LOG_LEVEL_DEBUG, "cannot retrieve result set");
break;
}
else
ret += (int)mysql_affected_rows(conn);
/* more results? -1 = no, >0 = error, 0 = yes (keep looping) */
if (0 < (status = mysql_next_result(conn)))
zabbix_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);
}
while (0 == status);
}
}
#elif defined(HAVE_ORACLE)
err = OCIHandleAlloc((dvoid *)oracle.envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
if (OCI_SUCCESS == err)
{
err = OCIStmtPrepare(stmthp, oracle.errhp, (text *)sql, (ub4)strlen((char *)sql),
(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
}
if (OCI_SUCCESS == err)
{
err = OCIStmtExecute(oracle.svchp, stmthp, oracle.errhp, (ub4)1, (ub4)0,
(CONST OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_COMMIT_ON_SUCCESS);
if (OCI_SUCCESS == err)
{
ub4 nrows = 0;
err = OCIAttrGet((void *)stmthp, OCI_HTYPE_STMT, (ub4 *)&nrows,
(ub4 *)0, OCI_ATTR_ROW_COUNT, oracle.errhp);
ret = nrows;
}
}
if (OCI_SUCCESS != err)
{
zabbix_errlog(ERR_Z3005, err, zbx_oci_error(err), sql);
ret = (OCI_SERVER_NORMAL == OCI_DBserver_status() ? ZBX_DB_FAIL : ZBX_DB_DOWN);
}
if (NULL != stmthp)
{
(void)OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT);
stmthp = NULL;
}
#elif defined(HAVE_POSTGRESQL)
result = PQexec(conn,sql);
if (NULL == result)
{
zabbix_errlog(ERR_Z3005, 0, "result is NULL", sql);
ret = (CONNECTION_OK == PQstatus(conn) ? ZBX_DB_FAIL : ZBX_DB_DOWN);
}
else if (PGRES_COMMAND_OK != PQresultStatus(result))
{
error = zbx_dsprintf(error, "%s:%s",
PQresStatus(PQresultStatus(result)),
PQresultErrorMessage(result));
zabbix_errlog(ERR_Z3005, 0, error, sql);
zbx_free(error);
ret = (CONNECTION_OK == PQstatus(conn) ? ZBX_DB_FAIL : ZBX_DB_DOWN);
}
if (ZBX_DB_OK == ret)
ret = atoi(PQcmdTuples(result));
PQclear(result);
#elif defined(HAVE_SQLITE3)
if (0 == txn_level && PHP_MUTEX_OK != php_sem_acquire(&sqlite_access))
{
zabbix_log(LOG_LEVEL_CRIT, "ERROR: cannot create lock on SQLite3 database");
exit(FAIL);
}
lbl_exec:
if (SQLITE_OK != (err = sqlite3_exec(conn, sql, NULL, 0, &error)))
{
if (SQLITE_BUSY == err)
goto lbl_exec;
zabbix_errlog(ERR_Z3005, 0, error, sql);
sqlite3_free(error);
switch (err)
{
case SQLITE_ERROR: /* SQL error or missing database; assuming SQL error, because if we
are this far into execution, zbx_db_connect() was successful */
case SQLITE_NOMEM: /* A malloc() failed */
case SQLITE_TOOBIG: /* String or BLOB exceeds size limit */
case SQLITE_CONSTRAINT: /* Abort due to constraint violation */
case SQLITE_MISMATCH: /* Data type mismatch */
ret = ZBX_DB_FAIL;
break;
default:
ret = ZBX_DB_DOWN;
break;
}
}
if (ZBX_DB_OK == ret)
ret = sqlite3_changes(conn);
if (0 == txn_level)
php_sem_release(&sqlite_access);
#endif /* HAVE_SQLITE3 */
if (0 != CONFIG_LOG_SLOW_QUERIES)
{
sec = zbx_time() - sec;
if (sec > (double)CONFIG_LOG_SLOW_QUERIES / 1000.0)
zabbix_log(LOG_LEVEL_WARNING, "slow query: " ZBX_FS_DBL " sec, \"%s\"", sec, sql);
}
if (ZBX_DB_FAIL == ret && 0 < txn_level)
{
zabbix_log(LOG_LEVEL_DEBUG, "query [%s] failed, setting transaction as failed", sql);
txn_error = 1;
}
clean:
zbx_free(sql);
return ret;
}
/******************************************************************************
* *
* Function: zbx_db_vselect *
* *
* Purpose: execute a select statement *
* *
* Return value: data, NULL (on error) or (DB_RESULT)ZBX_DB_DOWN *
* *
******************************************************************************/
DB_RESULT zbx_db_vselect(const char *fmt, va_list args)
{
char *sql = NULL;
DB_RESULT result = NULL;
double sec = 0;
#if defined(HAVE_IBM_DB2)
int i;
SQLRETURN ret = SQL_SUCCESS;
#elif defined(HAVE_ORACLE)
sword err = OCI_SUCCESS;
ub4 counter;
#elif defined(HAVE_POSTGRESQL)
char *error = NULL;
#elif defined(HAVE_SQLITE3)
int ret = FAIL;
char *error = NULL;
#endif
if (0 != CONFIG_LOG_SLOW_QUERIES)
sec = zbx_time();
sql = zbx_dvsprintf(sql, fmt, args);
if (1 == txn_error)
{
zabbix_log(LOG_LEVEL_DEBUG, "ignoring query [txnlev:%d] [%s] within failed transaction", txn_level, sql);
goto clean;
}
zabbix_log(LOG_LEVEL_DEBUG, "query [txnlev:%d] [%s]", txn_level, sql);
#if defined(HAVE_IBM_DB2)
result = zbx_malloc(result, sizeof(ZBX_IBM_DB2_RESULT));
memset(result, 0, sizeof(ZBX_IBM_DB2_RESULT));
/* allocate a statement handle */
if (SUCCEED != zbx_ibm_db2_success(ret = SQLAllocHandle(SQL_HANDLE_STMT, ibm_db2.hdbc, &result->hstmt)))
goto error;
/* directly execute the statement */
if (SUCCEED != zbx_ibm_db2_success(ret = SQLExecDirect(result->hstmt, (SQLCHAR *)sql, SQL_NTS)))
goto error;
/* identify the number of output columns */
if (SUCCEED != zbx_ibm_db2_success(ret = SQLNumResultCols(result->hstmt, &result->ncolumn)))
goto error;
if (0 == result->ncolumn)
goto error;
result->nalloc = 0;
result->values = zbx_malloc(result->values, sizeof(char *) * result->ncolumn);
result->values_cli = zbx_malloc(result->values_cli, sizeof(char *) * result->ncolumn);
result->values_len = zbx_malloc(result->values_len, sizeof(SQLINTEGER) * result->ncolumn);
for (i = 0; i < result->ncolumn; i++)
{
/* get the display size for a column */
if (SUCCEED != zbx_ibm_db2_success(ret = SQLColAttribute(result->hstmt, (SQLSMALLINT)(i + 1),
SQL_DESC_DISPLAY_SIZE, NULL, 0, NULL, &result->values_len[i])))
{
goto error;
}
result->values_len[i] += 1; /* '\0'; */
/* allocate memory to bind a column */
result->values_cli[i] = zbx_malloc(NULL, result->values_len[i]);
result->nalloc++;
/* bind columns to program variables, converting all types to CHAR */
if (SUCCEED != zbx_ibm_db2_success(ret = SQLBindCol(result->hstmt, (SQLSMALLINT)(i + 1),
SQL_C_CHAR, result->values_cli[i], result->values_len[i], &result->values_len[i])))
{
goto error;
}
}
error:
if (SUCCEED != zbx_ibm_db2_success(ret) || 0 == result->ncolumn)
{
zbx_ibm_db2_log_errors(SQL_HANDLE_DBC, ibm_db2.hdbc);
zbx_ibm_db2_log_errors(SQL_HANDLE_STMT, result->hstmt);
IBM_DB2free_result(result);
result = (SQL_CD_TRUE == IBM_DB2server_status() ? NULL : (DB_RESULT)ZBX_DB_DOWN);
}
#elif defined(HAVE_MYSQL)
if (NULL == conn)
{
zabbix_errlog(ERR_Z3003);
result = NULL;
}
else
{
if (0 != mysql_query(conn, sql))
{
zabbix_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);
switch (mysql_errno(conn))
{
case CR_CONN_HOST_ERROR:
case CR_SERVER_GONE_ERROR:
case CR_CONNECTION_ERROR:
case CR_SERVER_LOST:
case ER_SERVER_SHUTDOWN:
case ER_ACCESS_DENIED_ERROR: /* wrong user or password */
case ER_ILLEGAL_GRANT_FOR_TABLE: /* user without any privileges */
case ER_TABLEACCESS_DENIED_ERROR:/* user without some privilege */
case ER_UNKNOWN_ERROR:
result = (DB_RESULT)ZBX_DB_DOWN;
break;
default:
result = NULL;
break;
}
}
else
result = mysql_store_result(conn);
}
#elif defined(HAVE_ORACLE)
result = zbx_malloc(NULL, sizeof(ZBX_OCI_DB_RESULT));
memset(result, 0, sizeof(ZBX_OCI_DB_RESULT));
err = OCIHandleAlloc((dvoid *)oracle.envhp, (dvoid **)&result->stmthp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
if (OCI_SUCCESS == err)
{
err = OCIStmtPrepare(result->stmthp, oracle.errhp, (text *)sql, (ub4)strlen((char *)sql),
(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
}
if (OCI_SUCCESS == err)
{
err = OCIStmtExecute(oracle.svchp, result->stmthp, oracle.errhp, (ub4)0, (ub4)0,
(CONST OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_COMMIT_ON_SUCCESS);
}
if (OCI_SUCCESS == err)
{
/* get the number of columns in the query */
err = OCIAttrGet((void *)result->stmthp, OCI_HTYPE_STMT, (void *)&result->ncolumn,
(ub4 *)0, OCI_ATTR_PARAM_COUNT, oracle.errhp);
}
if (OCI_SUCCESS != err)
goto error;
assert(0 < result->ncolumn);
result->values = zbx_malloc(NULL, result->ncolumn * sizeof(char *));
memset(result->values, 0, result->ncolumn * sizeof(char *));
for (counter = 1; OCI_SUCCESS == err && counter <= result->ncolumn; counter++)
{
OCIParam *parmdp = NULL;
OCIDefine *defnp = NULL;
ub4 char_semantics;
ub2 col_width;
/* request a parameter descriptor in the select-list */
err = OCIParamGet((void *)result->stmthp, OCI_HTYPE_STMT, oracle.errhp, (void **)&parmdp, (ub4)counter);
if (OCI_SUCCESS == err)
{
/* retrieve the length semantics for the column */
char_semantics = 0;
err = OCIAttrGet((void *)parmdp, (ub4)OCI_DTYPE_PARAM, (void *)&char_semantics, (ub4 *)0,
(ub4)OCI_ATTR_CHAR_USED, (OCIError *)oracle.errhp);
}
if (OCI_SUCCESS == err)
{
col_width = 0;
if (char_semantics)
{
/* retrieve the column width in characters */
err = OCIAttrGet((void *)parmdp, (ub4)OCI_DTYPE_PARAM, (void *)&col_width, (ub4 *)0,
(ub4)OCI_ATTR_CHAR_SIZE, (OCIError *)oracle.errhp);
}
else
{
/* retrieve the column width in bytes */
err = OCIAttrGet((void *)parmdp, (ub4)OCI_DTYPE_PARAM, (void *)&col_width, (ub4 *)0,
(ub4)OCI_ATTR_DATA_SIZE, (OCIError *)oracle.errhp);
}
}
col_width++;
result->values[counter - 1] = zbx_malloc(NULL, col_width);
memset(result->values[counter - 1], 0, col_width);
if (OCI_SUCCESS == err)
{
/* represent any data as characters */
err = OCIDefineByPos(result->stmthp, &defnp, oracle.errhp, counter,
(dvoid *)result->values[counter - 1], col_width, SQLT_STR,
(dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT);
}
/* free cell descriptor */
OCIDescriptorFree(parmdp, OCI_DTYPE_PARAM);
parmdp = NULL;
}
error:
if (OCI_SUCCESS != err)
{
zabbix_errlog(ERR_Z3005, err, zbx_oci_error(err), sql);
OCI_DBfree_result(result);
result = (OCI_SERVER_NORMAL == OCI_DBserver_status() ? NULL : (DB_RESULT)ZBX_DB_DOWN);
}
#elif defined(HAVE_POSTGRESQL)
result = zbx_malloc(NULL, sizeof(ZBX_PG_DB_RESULT));
result->pg_result = PQexec(conn, sql);
result->values = NULL;
result->cursor = 0;
result->row_num = 0;
if (NULL == result->pg_result)
zabbix_errlog(ERR_Z3005, 0, "result is NULL", sql);
if (PGRES_TUPLES_OK != PQresultStatus(result->pg_result))
{
error = zbx_dsprintf(error, "%s:%s",
PQresStatus(PQresultStatus(result->pg_result)),
PQresultErrorMessage(result->pg_result));
zabbix_errlog(ERR_Z3005, 0, error, sql);
zbx_free(error);
PG_DBfree_result(result);
result = (CONNECTION_OK == PQstatus(conn) ? NULL : (DB_RESULT)ZBX_DB_DOWN);
}
else /* init rownum */
result->row_num = PQntuples(result->pg_result);
#elif defined(HAVE_SQLITE3)
if (0 == txn_level && PHP_MUTEX_OK != php_sem_acquire(&sqlite_access))
{
zabbix_log(LOG_LEVEL_CRIT, "ERROR: cannot create lock on SQLite3 database");
exit(FAIL);
}
result = zbx_malloc(NULL, sizeof(ZBX_SQ_DB_RESULT));
result->curow = 0;
lbl_get_table:
if (SQLITE_OK != (ret = sqlite3_get_table(conn,sql, &result->data, &result->nrow, &result->ncolumn, &error)))
{
if (SQLITE_BUSY == ret)
goto lbl_get_table;
zabbix_errlog(ERR_Z3005, 0, error, sql);
sqlite3_free(error);
SQ_DBfree_result(result);
switch (ret)
{
case SQLITE_ERROR: /* SQL error or missing database; assuming SQL error, because if we
are this far into execution, zbx_db_connect() was successful */
case SQLITE_NOMEM: /* a malloc() failed */
case SQLITE_MISMATCH: /* data type mismatch */
result = NULL;
break;
default:
result = (DB_RESULT)ZBX_DB_DOWN;
break;
}
}
if (0 == txn_level)
php_sem_release(&sqlite_access);
#endif /* HAVE_SQLITE3 */
if (0 != CONFIG_LOG_SLOW_QUERIES)
{
sec = zbx_time() - sec;
if (sec > (double)CONFIG_LOG_SLOW_QUERIES / 1000.0)
zabbix_log(LOG_LEVEL_WARNING, "slow query: " ZBX_FS_DBL " sec, \"%s\"", sec, sql);
}
if (NULL == result && 0 < txn_level)
{
zabbix_log(LOG_LEVEL_DEBUG, "query [%s] failed, setting transaction as failed", sql);
txn_error = 1;
}
clean:
zbx_free(sql);
return result;
}
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;
}
static int oracle_update(void *theconn, const Octstr *sql, List *binds)
{
OCIStmt *stmt;
sword status;
ub4 rows = 0, i;
struct ora_conn *conn = (struct ora_conn*) theconn;
int binds_len = (binds ? gwlist_len(binds) : 0);
/* 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;
}
debug("dbpool.oracle",0,"OCIStmt allocated");
/* 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;
}
debug("dbpool.oracle",0,"OCIStmtPrepare done");
/* bind variables */
for (i = 0; i < binds_len; i++) {
Octstr *bind = gwlist_get(binds, i);
OCIBind *bndhp = NULL;
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, 1, 0, NULL, NULL,
/*OCI_DEFAULT*/ OCI_COMMIT_ON_SUCCESS);
if (OCI_SUCCESS != status && OCI_NO_DATA != status) {
oracle_checkerr(conn->errhp, status);
OCIHandleFree(stmt, OCI_HTYPE_STMT);
return -1;
}
debug("dbpool.oracle",0,"OCIStmtExecute done");
/* retrieve #rows processed so far */
status = OCIAttrGet(stmt, OCI_HTYPE_STMT, &rows, 0, OCI_ATTR_ROW_COUNT,
conn->errhp);
if (status != OCI_SUCCESS) {
oracle_checkerr(conn->errhp, status);
/* we doesn't return error here, because sql is executed and commited already */
}
debug("dbpool.oracle",0,"rows processed = %d", rows);
OCIHandleFree(stmt, OCI_HTYPE_STMT);
return (int) rows;
}
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);
}
/*
Execute a SQL command that returns a result table, and and bind the
default row. Also check and bind the global array of bind variables
(if any).
*/
int
cllExecSqlWithResult( icatSessionStruct *icss, int *stmtNum, const char *sql ) {
OCIEnv *p_env;
OCISvcCtx *p_svc;
static OCIStmt *p_statement;
static OCIDefine *p_dfn = ( OCIDefine * ) 0;
int stat, stat2, i, j;
char *cptr;
char sqlConverted[MAX_SQL_SIZE];
icatStmtStrct *myStatement;
int statementNumber;
int counter;
OCIParam *mypard = ( OCIParam * ) 0;
ub2 dtype;
ub2 col_width;
ub4 char_semantics;
OraText *colName;
static int columnLength[MAX_TOKEN];
static sb2 indicator[MAX_TOKEN];
p_svc = ( OCISvcCtx * )icss->connectPtr;
p_env = ( OCIEnv * )icss->environPtr;
i = convertSqlToOra( sql, sqlConverted );
if ( i != 0 ) {
rodsLog( LOG_ERROR, "cllExecSqlWithResult: SQL too long" );
return CAT_OCI_ERROR;
}
/* Allocate SQL statement */
stat = OCIHandleAlloc( ( dvoid * ) p_env, ( dvoid ** ) &p_statement,
OCI_HTYPE_STMT, ( size_t ) 0, ( dvoid ** ) 0 );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlWithResult: OCIHandleAlloc failed: %d",
stat );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
/* set up our statement */
statementNumber = -1;
for ( i = 0; i < MAX_NUM_OF_CONCURRENT_STMTS && statementNumber < 0; i++ ) {
if ( icss->stmtPtr[i] == 0 ) {
statementNumber = i;
}
}
if ( statementNumber < 0 ) {
rodsLog( LOG_ERROR,
"cllExecSqlWithResult: too many concurrent statements" );
return -2;
}
myStatement = ( icatStmtStrct * )malloc( sizeof( icatStmtStrct ) );
icss->stmtPtr[statementNumber] = myStatement;
myStatement->numOfCols = 0;
myStatement->stmtPtr = p_statement;
/* Prepare SQL statement */
stat = OCIStmtPrepare( p_statement, p_err, ( OraText * )sqlConverted,
( ub4 ) strlen( sqlConverted ),
( ub4 ) OCI_NTV_SYNTAX, ( ub4 ) OCI_DEFAULT );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlWithResult: OCIStmtPrepare failed: %d", stat );
rodsLog( LOG_ERROR, sqlConverted );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
if ( bindTheVariables( p_statement, sqlConverted ) != 0 ) {
logTheBindVariables( LOG_ERROR );
return CAT_OCI_ERROR;
}
logTheBindVariables( 0 );
rodsLogSql( sqlConverted );
/* Execute statement */
stat = OCIStmtExecute( p_svc, p_statement, p_err, ( ub4 ) 0, ( ub4 ) 0,
( CONST OCISnapshot * ) NULL, ( OCISnapshot * ) NULL,
OCI_DEFAULT );
stat2 = logExecuteStatus( stat, sqlConverted, "cllExecSqlWithResult" );
if ( stat2 ) {
return stat2;
}
*stmtNum = statementNumber; /* return index to statement handle */
/* get the number of columns and width of the columns */
/* Request a parameter descriptor for position 1 in the select-list */
counter = 1;
stat = OCIParamGet( ( dvoid * )p_statement, OCI_HTYPE_STMT, p_err,
( dvoid ** )&mypard, ( ub4 ) counter );
/* Loop only if a descriptor was successfully retrieved for
current position, starting at 1 */
while ( stat == OCI_SUCCESS ) {
/* Retrieve the datatype attribute */
stat = OCIAttrGet( ( dvoid* ) mypard, ( ub4 ) OCI_DTYPE_PARAM,
( dvoid* ) &dtype, ( ub4 * ) 0, ( ub4 ) OCI_ATTR_DATA_TYPE,
( OCIError * ) p_err );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlWithResult: OCIAttrGet failed: %d",
stat );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
/* Retrieve the length semantics for the column */
char_semantics = 0;
stat = OCIAttrGet( ( dvoid* ) mypard, ( ub4 ) OCI_DTYPE_PARAM,
( dvoid* ) &char_semantics, ( ub4 * ) 0,
( ub4 ) OCI_ATTR_CHAR_USED,
( OCIError * ) p_err );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlWithResult: OCIAttrGet failed: %d",
stat );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
/* Retrieve the column width in characters */
col_width = 0;
if ( char_semantics ) {
stat = OCIAttrGet( ( dvoid* ) mypard, ( ub4 ) OCI_DTYPE_PARAM,
( dvoid* ) &col_width, ( ub4 * ) 0,
( ub4 ) OCI_ATTR_CHAR_SIZE,
( OCIError * ) p_err );
}
else {
stat = OCIAttrGet( ( dvoid* ) mypard, ( ub4 ) OCI_DTYPE_PARAM,
( dvoid* ) &col_width, ( ub4 * ) 0,
( ub4 ) OCI_ATTR_DATA_SIZE,
( OCIError * ) p_err );
}
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlWithResult: OCIAttrGet failed: %d",
stat );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
/* get the col name */
stat = OCIAttrGet( ( dvoid* ) mypard, ( ub4 ) OCI_DTYPE_PARAM,
&colName, ( ub4 * ) 0,
( ub4 ) OCI_ATTR_NAME,
( OCIError * ) p_err );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlWithResult: OCIAttrGet failed: %d",
stat );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
columnLength[counter] = col_width;
i = counter - 1;
columnLength[i] = col_width;
if ( strlen( ( char * )colName ) > col_width ) {
columnLength[i] = strlen( ( char* )colName );
}
myStatement->resultColName[i] = ( char * )malloc( ( int )columnLength[i] + 2 );
strncpy( myStatement->resultColName[i],
( char * )colName, columnLength[i] );
/* Big cludge/hack, but it looks like an OCI bug when the colName
is exactly 8 bytes (or something). Anyway, when the column name
is "RESC_NETRESC_DEF_PATH" it's actually the running together of
two names, so the code below corrects it. */
if ( strcmp( myStatement->resultColName[i], "RESC_NETRESC_DEF_PATH" ) == 0 ) {
strncpy( myStatement->resultColName[i],
"RESC_NET", columnLength[i] );
}
/* Second case, second cludge/hack */
if ( strcmp( myStatement->resultColName[i],
"USER_DISTIN_NAMEUSER_INFO" ) == 0 ) {
strncpy( myStatement->resultColName[i],
"USER_DISTIN_NAME", columnLength[i] );
}
/* convert the column name to lower case to match postgres */
cptr = ( char* )myStatement->resultColName[i];
for ( j = 0; j < columnLength[i]; j++ ) {
if ( *cptr == '\0' ) {
break;
}
if ( *cptr == ':' ) {
break;
}
if ( *cptr >= 'A' && *cptr <= 'Z' ) {
*cptr += ( ( int )'a' - ( int )'A' );
}
cptr++;
}
myStatement->resultValue[i] = ( char * )malloc( ( int )columnLength[i] + 2 );
strcpy( ( char * )myStatement->resultValue[i], "" );
stat = OCIDefineByPos( p_statement, &p_dfn, p_err, counter,
( dvoid * ) myStatement->resultValue[i],
( sword ) columnLength[i], SQLT_STR,
( dvoid * )&indicator[i],
( ub2 * )0, ( ub2 * )0, OCI_DEFAULT );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlWithResult: OCIDefineByPos failed: %d", stat );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
/* increment counter and get next descriptor, if there is one */
counter++;
stat = OCIParamGet( ( dvoid * )p_statement, OCI_HTYPE_STMT, p_err,
( dvoid ** )&mypard, ( ub4 ) counter );
}
if ( counter == 1 ) {
rodsLog( LOG_ERROR, "cllExecSqlWithResult: SQLNumResultCols failed: %d",
stat );
return -2;
}
myStatement->numOfCols = counter - 1;
return 0;
}
/*
Execute a SQL command which has no resulting table. Examples include
insert, delete, update.
*/
int
cllExecSqlNoResult( icatSessionStruct *icss, const char *sqlInput ) {
int stat, stat2, stat3;
OCIEnv *p_env;
OCISvcCtx *p_svc;
static OCIStmt *p_statement;
char sql[MAX_SQL_SIZE];
ub4 rows_affected;
ub4 *pUb4;
stat = convertSqlToOra( ( char* )sqlInput, sql );
if ( stat != 0 ) {
rodsLog( LOG_ERROR, "cllExecSqlNoResult: SQL too long" );
return CAT_OCI_ERROR;
}
p_svc = ( OCISvcCtx * )icss->connectPtr;
p_env = ( OCIEnv * )icss->environPtr;
if ( strcmp( sql, "commit" ) == 0 ) {
rodsLogSql( sql );
stat = OCITransCommit( p_svc, p_err, ( ub4 ) OCI_DEFAULT );
stat2 = logExecuteStatus( stat, sql, "cllExecSqlNoResult" );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlNoResult: OCITransCommit failed: %d",
stat );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
return 0;
}
if ( strcmp( sql, "rollback" ) == 0 ) {
rodsLogSql( sql );
stat = OCITransRollback( p_svc, p_err, ( ub4 ) OCI_DEFAULT );
stat2 = logExecuteStatus( stat, sql, "cllExecSqlNoResult" );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlNoResult: OCITransRollback failed: %d",
stat );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
return 0;
}
/* Allocate SQL statement */
stat = OCIHandleAlloc( ( dvoid * ) p_env, ( dvoid ** ) &p_statement,
OCI_HTYPE_STMT, ( size_t ) 0, ( dvoid ** ) 0 );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlNoResult: OCIHandleAlloc failed: %d", stat );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
/* Prepare SQL statement */
stat = OCIStmtPrepare( p_statement, p_err, ( OraText * )sql,
( ub4 ) strlen( sql ),
( ub4 ) OCI_NTV_SYNTAX, ( ub4 ) OCI_DEFAULT );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlNoResult: OCIStmtPrepare failed: %d", stat );
rodsLog( LOG_ERROR, sql );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
if ( bindTheVariables( p_statement, sql ) != 0 ) {
logTheBindVariables( LOG_ERROR );
return CAT_OCI_ERROR;
}
logTheBindVariables( 0 );
rodsLogSql( sql );
/* Execute statement */
stat = OCIStmtExecute( p_svc, p_statement, p_err, ( ub4 ) 1, ( ub4 ) 0,
( CONST OCISnapshot * ) NULL, ( OCISnapshot * ) NULL,
OCI_DEFAULT );
stat2 = logExecuteStatus( stat, sql, "cllExecSqlNoResult" );
if ( stat == OCI_NO_DATA ) { /* Don't think this ever happens, but... */
return CAT_SUCCESS_BUT_WITH_NO_INFO;
}
/* malloc it so that it's aligned properly, else can get
bus errors when doing 64-bit addressing */
pUb4 = ( ub4* ) malloc( sizeof( rows_affected ) );
*pUb4 = 0;
rows_affected = 0;
if ( stat == OCI_ERROR ) {
rodsLog( LOG_ERROR, "cllExecSqlNoResult: OCIStmtExecute failed: %d", stat );
logOraError( LOG_ERROR, p_err, stat );
free( pUb4 );
if ( stat2 == CATALOG_ALREADY_HAS_ITEM_BY_THAT_NAME ) {
return stat2;
}
return CAT_OCI_ERROR;
}
stat3 = OCIAttrGet( ( dvoid * )p_statement, OCI_HTYPE_STMT, pUb4, 0,
OCI_ATTR_ROW_COUNT, ( OCIError * ) p_err );
rows_affected = *pUb4;
free( pUb4 );
stat = OCIHandleFree( ( dvoid * )p_statement, OCI_HTYPE_STMT ); /* free the
statement */
if ( stat3 != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlNoResult: OCIAttrGet failed: %d", stat );
return stat3;
}
/* rodsLog(LOG_NOTICE, "cllExecSqlNoResult: OCIAttrGet, rows_affected: %d",
rows_affected); */
if ( rows_affected == 0 ) {
return CAT_SUCCESS_BUT_WITH_NO_INFO;
}
return stat2;
}
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;
}
CPLErr OGROCIStatement::Execute( const char *pszSQLStatement,
int nMode )
{
/* -------------------------------------------------------------------- */
/* Prepare the statement if it is being passed in. */
/* -------------------------------------------------------------------- */
if( pszSQLStatement != NULL )
{
CPLErr eErr = Prepare( pszSQLStatement );
if( eErr != CE_None )
return eErr;
}
if( hStatement == NULL )
{
CPLError( CE_Failure, CPLE_AppDefined,
"No prepared statement in call to OGROCIStatement::Execute(NULL)" );
return CE_Failure;
}
/* -------------------------------------------------------------------- */
/* Determine if this is a SELECT statement. */
/* -------------------------------------------------------------------- */
ub2 nStmtType;
if( poSession->Failed(
OCIAttrGet( hStatement, OCI_HTYPE_STMT,
&nStmtType, 0, OCI_ATTR_STMT_TYPE, poSession->hError ),
"OCIAttrGet(ATTR_STMT_TYPE)") )
return CE_Failure;
int bSelect = (nStmtType == OCI_STMT_SELECT);
/* -------------------------------------------------------------------- */
/* Work out some details about execution mode. */
/* -------------------------------------------------------------------- */
if( nMode == -1 )
{
if( bSelect )
nMode = OCI_DEFAULT;
else
nMode = OCI_COMMIT_ON_SUCCESS;
}
/* -------------------------------------------------------------------- */
/* Execute the statement. */
/* -------------------------------------------------------------------- */
if( poSession->Failed(
OCIStmtExecute( poSession->hSvcCtx, hStatement,
poSession->hError, (ub4)bSelect ? 0 : 1, (ub4)0,
(OCISnapshot *)NULL, (OCISnapshot *)NULL, nMode ),
pszCommandText ) )
return CE_Failure;
if( !bSelect )
{
ub4 row_count;
if( poSession->Failed(
OCIAttrGet( hStatement, OCI_HTYPE_STMT,
&row_count, 0, OCI_ATTR_ROW_COUNT, poSession->hError ),
"OCIAttrGet(OCI_ATTR_ROW_COUNT)") )
return CE_Failure;
nAffectedRows = row_count;
return CE_None;
}
/* -------------------------------------------------------------------- */
/* Count the columns. */
/* -------------------------------------------------------------------- */
for( nRawColumnCount = 0; true; nRawColumnCount++ )
{
OCIParam *hParmDesc;
if( OCIParamGet( hStatement, OCI_HTYPE_STMT, poSession->hError,
(dvoid**)&hParmDesc,
(ub4) nRawColumnCount+1 ) != OCI_SUCCESS )
break;
}
panFieldMap = (int *) CPLCalloc(sizeof(int),nRawColumnCount);
papszCurColumn = (char **) CPLCalloc(sizeof(char*),nRawColumnCount+1);
panCurColumnInd = (sb2 *) CPLCalloc(sizeof(sb2),nRawColumnCount+1);
/* ==================================================================== */
/* Establish result column definitions, and setup parameter */
/* defines. */
/* ==================================================================== */
poDefn = new OGRFeatureDefn( pszCommandText );
poDefn->SetGeomType(wkbNone);
poDefn->Reference();
for( int iParm = 0; iParm < nRawColumnCount; iParm++ )
{
OGRFieldDefn oField( "", OFTString );
OCIParam *hParmDesc;
ub2 nOCIType;
ub4 nOCILen;
/* -------------------------------------------------------------------- */
/* Get parameter definition. */
/* -------------------------------------------------------------------- */
if( poSession->Failed(
OCIParamGet( hStatement, OCI_HTYPE_STMT, poSession->hError,
(dvoid**)&hParmDesc, (ub4) iParm+1 ),
"OCIParamGet") )
return CE_Failure;
if( poSession->GetParmInfo( hParmDesc, &oField, &nOCIType, &nOCILen )
!= CE_None )
return CE_Failure;
if( oField.GetType() == OFTBinary )
{
/* We could probably generalize that, but at least it works in that */
/* use case */
if( EQUAL(oField.GetNameRef(), "DATA_DEFAULT") && nOCIType == SQLT_LNG )
{
oField.SetType(OFTString);
}
else
{
panFieldMap[iParm] = -1;
continue;
}
}
poDefn->AddFieldDefn( &oField );
panFieldMap[iParm] = poDefn->GetFieldCount() - 1;
/* -------------------------------------------------------------------- */
/* Prepare a binding. */
/* -------------------------------------------------------------------- */
int nBufWidth = 256, nOGRField = panFieldMap[iParm];
OCIDefine *hDefn = NULL;
if( oField.GetWidth() > 0 )
/* extra space needed for the decimal separator the string
terminator and the negative sign (Tamas Szekeres)*/
nBufWidth = oField.GetWidth() + 3;
else if( oField.GetType() == OFTInteger )
nBufWidth = 22;
else if( oField.GetType() == OFTReal )
nBufWidth = 36;
else if ( oField.GetType() == OFTDateTime )
nBufWidth = 40;
else if ( oField.GetType() == OFTDate )
nBufWidth = 20;
papszCurColumn[nOGRField] = (char *) CPLMalloc(nBufWidth+2);
CPLAssert( ((long) papszCurColumn[nOGRField]) % 2 == 0 );
if( poSession->Failed(
OCIDefineByPos( hStatement, &hDefn, poSession->hError,
iParm+1,
(ub1 *) papszCurColumn[nOGRField], nBufWidth,
SQLT_STR, panCurColumnInd + nOGRField,
NULL, NULL, OCI_DEFAULT ),
"OCIDefineByPos" ) )
return CE_Failure;
}
return CE_None;
}
