Esempio n. 1
0
/*
 * 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;
}
Esempio n. 2
0
/*
 * 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;
}
Esempio n. 3
0
/*
 * 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;
}
Esempio n. 4
0
/*
 * 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;
}
Esempio n. 5
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;
}