Beispiel #1
0
/*
 * 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;
}
Beispiel #3
0
/*
 * 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;
}
Beispiel #4
0
/*
 * 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;
}
Beispiel #5
0
/*
 * 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;
}
Beispiel #6
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;
}
Beispiel #7
0
/*
 * 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;
}