/*
* Query table for specified rows
* _h: structure representing database connection
* _k: key names
* _op: operators
* _v: values of the keys that must match
* _c: column names to return
* _n: number of key=values pairs to compare
* _nc: number of columns to return
* _o: order by the specified column
*/
int db_oracle_query(const db1_con_t* _h, const db_key_t* _k, const db_op_t* _op,
const db_val_t* _v, const db_key_t* _c, int _n, int _nc,
const db_key_t _o, db1_res_t** _r)
{
query_data_t cb;
OCIStmt* reshp;
int rc;
if (!_h || !CON_TABLE(_h) || !_r) {
LM_ERR("invalid parameter value\n");
return -1;
}
cb._rs = &reshp;
cb._v = _v;
cb._n = _n;
cb._w = NULL;
cb._nw = 0;
CON_ORA(_h)->pqdata = &cb;
CON_ORA(_h)->bindpos = 0;
rc = db_do_query(_h, _k, _op, _v, _c, _n, _nc, _o, _r,
db_oracle_val2str, db_oracle_submit_query, db_oracle_store_result);
CON_ORA(_h)->pqdata = NULL; /* paranoid for next call */
return rc;
}
/*
* Update some rows in the specified table
* _h: structure representing database connection
* _k: key names
* _o: operators
* _v: values of the keys that must match
* _uk: updated columns
* _uv: updated values of the columns
* _n: number of key=value pairs
* _un: number of columns to update
*/
int db_oracle_update(const db_con_t* _h, const db_key_t* _k, const db_op_t* _o,
const db_val_t* _v, const db_key_t* _uk, const db_val_t* _uv,
int _n, int _un)
{
query_data_t cb;
int rc;
if (!_h || !CON_TABLE(_h)) {
LM_ERR("invalid parameter value\n");
return -1;
}
cb._rs = NULL;
cb._v = _uv;
cb._n = _un;
cb._w = _v;
cb._nw = _n;
CON_ORA(_h)->pqdata = &cb;
CON_ORA(_h)->bindpos = 0;
CON_RESET_CURR_PS(_h); /* no prepared statements support */
rc = db_do_update(_h, _k, _o, _v, _uk, _uv, _n, _un,
db_oracle_val2str, db_oracle_submit_query);
CON_ORA(_h)->pqdata = NULL; /* paranoid for next call */
return rc;
}
/*
* Read database answer and fill the structure
*/
int db_oracle_store_result(const db_con_t* _h, db_res_t** _r)
{
dmap_t dmap;
int rc;
db_res_t* r;
ora_con_t* con;
OCIStmt* hs;
if (!_h || !_r) {
badparam:
LM_ERR("invalid parameter\n");
return -1;
}
con = CON_ORA(_h);
{
query_data_t *pcb = con->pqdata;
if (!pcb || !pcb->_rs)
goto badparam;
hs = *pcb->_rs;
pcb->_rs = NULL; /* paranoid for next call */
}
rc = -1;
if (_r) *_r = NULL; /* unification for all errors */
r = db_new_result();
if (!r) {
LM_ERR("no memory left\n");
goto done;
}
if (get_columns(con, r, hs, &dmap) < 0) {
LM_ERR("error while getting column names\n");
goto done;
}
if (get_rows(con, r, hs, &dmap) < 0) {
LM_ERR("error while converting rows\n");
db_free_columns(r);
goto done;
}
rc = 0;
*_r = r;
done:
OCIHandleFree(hs, OCI_HTYPE_STMT);
return rc;
}
/*
* Delete a row from the specified table
* _h: structure representing database connection
* _k: key names
* _o: operators
* _v: values of the keys that must match
* _n: number of key=value pairs
*/
int db_oracle_delete(const db1_con_t* _h, const db_key_t* _k, const db_op_t* _o,
const db_val_t* _v, int _n)
{
query_data_t cb;
int rc;
if (!_h || !CON_TABLE(_h)) {
LM_ERR("invalid parameter value\n");
return -1;
}
cb._rs = NULL;
cb._v = _v;
cb._n = _n;
cb._w = NULL;
cb._nw = 0;
CON_ORA(_h)->pqdata = &cb;
CON_ORA(_h)->bindpos = 0;
rc = db_do_delete(_h, _k, _o, _v, _n, db_oracle_val2str, db_oracle_submit_query);
CON_ORA(_h)->pqdata = NULL; /* paranoid for next call */
return rc;
}
/*
* Convert value to sql-string as db bind index
*/
int db_oracle_val2str(const db1_con_t* _c, const db_val_t* _v, char* _s, int* _len)
{
int ret;
if (!_c || !_v || !_s || !_len || *_len <= 0) {
LM_ERR("invalid parameter value\n");
return -1;
}
ret = snprintf(_s, *_len, ":%u", ++CON_ORA(_c)->bindpos);
if ((unsigned)ret >= (unsigned)*_len)
return sql_buf_small();
*_len = ret;
return 0;
}
/*
* Execute a raw SQL query
*/
int db_oracle_raw_query(const db1_con_t* _h, const str* _s, db1_res_t** _r)
{
query_data_t cb;
OCIStmt* reshp;
int len;
const char *p;
if (!_h || !_s || !_s->s) {
badparam:
LM_ERR("invalid parameter value\n");
return -1;
}
memset(&cb, 0, sizeof(cb));
p = _s->s;
len = _s->len;
while (len && *p == ' ') ++p, --len;
#define _S_DIFF(p, l, S) (l <= sizeof(S)-1 || strncasecmp(p, S, sizeof(S)-1))
if (!_S_DIFF(p, len, "select ")) {
if (!_r) goto badparam;
cb._rs = &reshp;
} else {
if ( _S_DIFF(p, len, "insert ")
&& _S_DIFF(p, len, "delete ")
&& _S_DIFF(p, len, "update "))
{
LM_ERR("unknown raw_query: '%.*s'\n", _s->len, _s->s);
return -2;
}
#undef _S_DIFF
if (_r) goto badparam;
cb._rs = NULL;
}
len = db_do_raw_query(_h, _s, _r, db_oracle_submit_query, db_oracle_store_result);
CON_ORA(_h)->pqdata = NULL; /* paranoid for next call */
return len;
}
/*
* Send an SQL query to the server
*/
static int db_oracle_submit_query(const db1_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;
}