/*
* Reconnect if connection is broken
*/
static int reconnect(const db1_con_t* _h)
{
int ret = 0;
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
char conn_str[MAX_CONN_STR_LEN];
LM_ERR("Attempting DB reconnect\n");
/* Disconnect */
SQLDisconnect (CON_CONNECTION(_h));
/* Reconnect */
if (!db_unixodbc_build_conn_str(CON_ID(_h), conn_str)) {
LM_ERR("failed to build connection string\n");
return ret;
}
ret = SQLDriverConnect(CON_CONNECTION(_h), (void *)1,
(SQLCHAR*)conn_str, SQL_NTS, outstr, sizeof(outstr),
&outstrlen, SQL_DRIVER_COMPLETE);
if (!SQL_SUCCEEDED(ret)) {
LM_ERR("failed to connect\n");
db_unixodbc_extract_error("SQLDriverConnect", CON_CONNECTION(_h),
SQL_HANDLE_DBC, NULL);
return ret;
}
ret = SQLAllocHandle(SQL_HANDLE_STMT, CON_CONNECTION(_h),
&CON_RESULT(_h));
if (!SQL_SUCCEEDED(ret)) {
LM_ERR("Statement allocation error %d\n", (int)(long)CON_CONNECTION(_h));
db_unixodbc_extract_error("SQLAllocStmt", CON_CONNECTION(_h), SQL_HANDLE_DBC,NULL);
return ret;
}
return ret;
}
/*
* Send an SQL query to the server
*/
static int db_unixodbc_submit_query(const db1_con_t* _h, const str* _s)
{
int ret = 0;
SQLCHAR sqlstate[7];
if (!_h || !_s || !_s->s) {
LM_ERR("invalid parameter value\n");
return -1;
}
/* first do some cleanup if required */
if(CON_RESULT(_h))
{
SQLCloseCursor(CON_RESULT(_h));
SQLFreeHandle(SQL_HANDLE_STMT, CON_RESULT(_h));
}
ret = SQLAllocHandle(SQL_HANDLE_STMT, CON_CONNECTION(_h), &CON_RESULT(_h));
if (!SQL_SUCCEEDED(ret))
{
LM_ERR("statement allocation error %d\n",
(int)(long)CON_CONNECTION(_h));
db_unixodbc_extract_error("SQLAllocStmt", CON_CONNECTION(_h), SQL_HANDLE_DBC,
(char*)sqlstate);
/* Connection broken */
if( !strncmp((char*)sqlstate,"08003",5) ||
!strncmp((char*)sqlstate,"08S01",5) ) {
ret = reconnect(_h);
if( !SQL_SUCCEEDED(ret) ) return ret;
} else {
return ret;
}
}
ret=SQLExecDirect(CON_RESULT(_h), (SQLCHAR*)_s->s, _s->len);
/* Handle SQL_NO_DATA as a valid return code. DELETE and UPDATE statements may return this return code if nothing was deleted/updated. */
if (!SQL_SUCCEEDED(ret) && (ret != SQL_NO_DATA))
{
SQLCHAR sqlstate[7];
LM_ERR("rv=%d. Query= %.*s\n", ret, _s->len, _s->s);
db_unixodbc_extract_error("SQLExecDirect", CON_RESULT(_h), SQL_HANDLE_STMT,
(char*)sqlstate);
/* Connection broken */
if( !strncmp((char*)sqlstate,"08003",5) ||
!strncmp((char*)sqlstate,"08S01",5) ||
!strncmp((char*)sqlstate,"HY000",5) /* ODBC 3 General error */
)
{
ret = reconnect(_h);
if( SQL_SUCCEEDED(ret) ) {
/* Try again */
ret=SQLExecDirect(CON_RESULT(_h), (SQLCHAR*)_s->s, _s->len);
if (!SQL_SUCCEEDED(ret)) {
LM_ERR("rv=%d. Query= %.*s\n", ret, _s->len, _s->s);
db_unixodbc_extract_error("SQLExecDirect", CON_RESULT(_h),
SQL_HANDLE_STMT, (char*)sqlstate);
/* Close the cursor */
SQLCloseCursor(CON_RESULT(_h));
SQLFreeHandle(SQL_HANDLE_STMT, CON_RESULT(_h));
}
}
}
else {
/* Close the cursor */
SQLCloseCursor(CON_RESULT(_h));
SQLFreeHandle(SQL_HANDLE_STMT, CON_RESULT(_h));
}
}
/* Store the ODBC query result */
CON_QUERY_RESULT(_h) = ret;
return ret;
}
/*
* Send an SQL query to the server
*/
static int db_unixodbc_submit_query(const db_con_t* _h, const str* _s)
{
int ret = 0;
SQLCHAR sqlstate[7];
if (!_h || !_s || !_s->s) {
LM_ERR("invalid parameter value\n");
return -1;
}
/* first do some cleanup if required */
if(CON_RESULT(_h))
{
SQLCloseCursor(CON_RESULT(_h));
SQLFreeHandle(SQL_HANDLE_STMT, CON_RESULT(_h));
}
ret = SQLAllocHandle(SQL_HANDLE_STMT, CON_CONNECTION(_h), &CON_RESULT(_h));
if (!SQL_SUCCEEDED(ret))
{
LM_ERR("statement allocation error %d\n",
(int)(long)CON_CONNECTION(_h));
db_unixodbc_extract_error("SQLAllocStmt", CON_CONNECTION(_h), SQL_HANDLE_DBC,
(char*)sqlstate);
/* Connection broken */
if( !strncmp((char*)sqlstate,"08003",5) ||
!strncmp((char*)sqlstate,"08S01",5) ) {
ret = reconnect(_h);
if( !SQL_SUCCEEDED(ret) ) return ret;
} else {
return ret;
}
}
ret=SQLExecDirect(CON_RESULT(_h), (SQLCHAR*)_s->s, _s->len);
if (!SQL_SUCCEEDED(ret))
{
SQLCHAR sqlstate[7];
LM_ERR("rv=%d. Query= %.*s\n", ret, _s->len, _s->s);
db_unixodbc_extract_error("SQLExecDirect", CON_RESULT(_h), SQL_HANDLE_STMT,
(char*)sqlstate);
/* Connection broken */
if( !strncmp((char*)sqlstate,"08003",5) ||
!strncmp((char*)sqlstate,"08S01",5)
)
{
ret = reconnect(_h);
if( SQL_SUCCEEDED(ret) ) {
/* Try again */
ret=SQLExecDirect(CON_RESULT(_h), (SQLCHAR*)_s->s, _s->len);
if (!SQL_SUCCEEDED(ret)) {
LM_ERR("rv=%d. Query= %.*s\n", ret, _s->len, _s->s);
db_unixodbc_extract_error("SQLExecDirect", CON_RESULT(_h),
SQL_HANDLE_STMT, (char*)sqlstate);
/* Close the cursor */
SQLCloseCursor(CON_RESULT(_h));
SQLFreeHandle(SQL_HANDLE_STMT, CON_RESULT(_h));
}
}
}
else {
/* Close the cursor */
SQLCloseCursor(CON_RESULT(_h));
SQLFreeHandle(SQL_HANDLE_STMT, CON_RESULT(_h));
}
}
return ret;
}
/*
* Get and convert columns from a result
*/
int db_unixodbc_get_columns(const db1_con_t* _h, db1_res_t* _r)
{
int col;
SQLSMALLINT cols; /* because gcc don't like RES_COL_N */
if ((!_h) || (!_r)) {
LM_ERR("invalid parameter\n");
return -1;
}
/* Save number of columns in the result structure */
SQLNumResultCols(CON_RESULT(_h), &cols);
RES_COL_N(_r) = cols;
if (!RES_COL_N(_r)) {
LM_ERR("no columns returned from the query\n");
return -2;
} else {
LM_DBG("%d columns returned from the query\n", RES_COL_N(_r));
}
if (db_allocate_columns(_r, RES_COL_N(_r)) != 0) {
LM_ERR("could not allocate columns\n");
return -3;
}
for(col = 0; col < RES_COL_N(_r); col++)
{
RES_NAMES(_r)[col] = (str*)pkg_malloc(sizeof(str));
if (! RES_NAMES(_r)[col]) {
LM_ERR("no private memory left\n");
db_free_columns(_r);
return -4;
}
LM_DBG("allocate %lu bytes for RES_NAMES[%d] at %p\n",
(unsigned long)sizeof(str),col, RES_NAMES(_r)[col]);
char columnname[80];
SQLRETURN ret;
SQLSMALLINT namelength, datatype, decimaldigits, nullable;
SQLULEN columnsize;
ret = SQLDescribeCol(CON_RESULT(_h), col + 1, (SQLCHAR *)columnname, 80,
&namelength, &datatype, &columnsize, &decimaldigits, &nullable);
if(!SQL_SUCCEEDED(ret)) {
LM_ERR("SQLDescribeCol failed: %d\n", ret);
db_unixodbc_extract_error("SQLExecDirect", CON_RESULT(_h), SQL_HANDLE_STMT,
NULL);
// FIXME should we fail here completly?
}
/* The pointer that is here returned is part of the result structure. */
RES_NAMES(_r)[col]->s = columnname;
RES_NAMES(_r)[col]->len = namelength;
LM_DBG("RES_NAMES(%p)[%d]=[%.*s]\n", RES_NAMES(_r)[col], col,
RES_NAMES(_r)[col]->len, RES_NAMES(_r)[col]->s);
switch(datatype)
{
case SQL_SMALLINT:
case SQL_INTEGER:
case SQL_TINYINT:
case SQL_DECIMAL:
case SQL_NUMERIC:
LM_DBG("use DB1_INT result type\n");
RES_TYPES(_r)[col] = DB1_INT;
break;
case SQL_BIGINT:
LM_DBG("use DB1_BIGINT result type\n");
RES_TYPES(_r)[col] = DB1_BIGINT;
break;
case SQL_REAL:
case SQL_FLOAT:
case SQL_DOUBLE:
LM_DBG("use DB1_DOUBLE result type\n");
RES_TYPES(_r)[col] = DB1_DOUBLE;
break;
case SQL_TYPE_TIMESTAMP:
case SQL_DATE:
case SQL_TIME:
case SQL_TIMESTAMP:
case SQL_TYPE_DATE:
case SQL_TYPE_TIME:
LM_DBG("use DB1_DATETIME result type\n");
RES_TYPES(_r)[col] = DB1_DATETIME;
break;
case SQL_CHAR:
case SQL_VARCHAR:
case SQL_WCHAR:
case SQL_WVARCHAR:
LM_DBG("use DB1_STRING result type\n");
RES_TYPES(_r)[col] = DB1_STRING;
break;
case SQL_BINARY:
case SQL_VARBINARY:
case SQL_LONGVARBINARY:
case SQL_BIT:
case SQL_LONGVARCHAR:
case SQL_WLONGVARCHAR:
LM_DBG("use DB1_BLOB result type\n");
RES_TYPES(_r)[col] = DB1_BLOB;
break;
default:
LM_WARN("unhandled data type column (%.*s) type id (%d), "
"use DB1_STRING as default\n", RES_NAMES(_r)[col]->len,
RES_NAMES(_r)[col]->s, datatype);
RES_TYPES(_r)[col] = DB1_STRING;
break;
}
}
return 0;
}
/*
* Create a new connection structure,
* open the UNIXODBC connection and set reference count to 1
*/
struct my_con* db_unixodbc_new_connection(struct db_id* id)
{
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
int ret;
struct my_con* ptr;
char conn_str[MAX_CONN_STR_LEN];
if (!id)
{
LM_ERR("invalid parameter value\n");
return 0;
}
ptr = (struct my_con*)pkg_malloc(sizeof(struct my_con));
if (!ptr)
{
LM_ERR("no more memory left\n");
return 0;
}
memset(ptr, 0, sizeof(struct my_con));
ptr->ref = 1;
// allocate environment handle
ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &(ptr->env));
if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO))
{
LM_ERR("could not alloc a SQL handle\n");
if (ptr) pkg_free(ptr);
return 0;
}
// set the environment
ret = SQLSetEnvAttr(ptr->env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO))
{
LM_ERR("could not set the environment\n");
goto err1;
}
// allocate connection handle
ret = SQLAllocHandle(SQL_HANDLE_DBC, ptr->env, &(ptr->dbc));
if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO))
{
LM_ERR("could not alloc a connection handle %d\n", ret);
goto err1;
}
if (!db_unixodbc_build_conn_str(id, conn_str)) {
LM_ERR("failed to build connection string\n");
goto err2;
}
LM_DBG("opening connection: unixodbc://xxxx:xxxx@%s/%s\n", ZSW(id->host),
ZSW(id->database));
ret = SQLDriverConnect(ptr->dbc, NULL, (SQLCHAR*)conn_str, SQL_NTS,
outstr, sizeof(outstr), &outstrlen,
SQL_DRIVER_COMPLETE);
if (SQL_SUCCEEDED(ret))
{
LM_DBG("connection succeeded with reply <%s>\n", outstr);
if (ret == SQL_SUCCESS_WITH_INFO)
{
LM_DBG("driver reported the following diagnostics\n");
db_unixodbc_extract_error("SQLDriverConnect", ptr->dbc, SQL_HANDLE_DBC, NULL);
}
}
else
{
LM_ERR("failed to connect\n");
db_unixodbc_extract_error("SQLDriverConnect", ptr->dbc, SQL_HANDLE_DBC, NULL);
goto err2;
}
ptr->stmt_handle = NULL;
ptr->timestamp = time(0);
ptr->id = id;
return ptr;
err1:
SQLFreeHandle(SQL_HANDLE_ENV, &(ptr->env));
if (ptr) pkg_free(ptr);
return 0;
err2:
SQLFreeHandle(SQL_HANDLE_ENV, &(ptr->env));
SQLFreeHandle(SQL_HANDLE_DBC, &(ptr->dbc));
if (ptr) pkg_free(ptr);
return 0;
}
