Skip site navigation (1) Skip section navigation (2)

Odd memory leak or crash

From: markw(at)mohawksoft(dot)com
To: pgsql-odbc(at)postgresql(dot)org
Subject: Odd memory leak or crash
Date: 2008-02-28 21:58:06
Message-ID: 33726.216.150.195.129.1204235886.squirrel@mail.mohawksoft.com (view raw or flat)
Thread:
Lists: pgsql-odbc
I have fairly simple application that calls a stored procedure in
PostgreSQL. I am using a late model PostgreSQL ODBC driver.

When I use the PostgreSQL SQL driver the system goes for ever. When I use
the ODBC driver it fails after about 80K or 90K operations.

The symptoms are SQLExecDirect returns -1 and no status message and the
program crashes.

Could you look over my execute, connect, and disconnect code and tell me
if your see anything obvious I am doing wrong? I can't seem to see
anything.


My execute function looks like this:
DWORD ODBCData::sql_execute (char *c_sql)
{
#ifdef POSIX
	vmon_log(VLOG_TRACE,"sql_execute:%s", c_sql);
#endif
#ifdef WIN32
	vmon_log(VLOG_TRACE,"sql_execute:%ws", c_sql);
#endif
	DWORD dwErrTimeout = 1;
	SQLRETURN rc;
	DWORD dw = SQL_ERROR;
	time_t comm_start = time (0);
	int diag = ODBC_DIAG_SUCCESS;

	SQLUCHAR * sql = m_sqlbuf;

	cvtstrwid (m_sqlbuf, c_sql, MAX_STRING, sizeof (UCHR), sizeof (char));

	for (int retries = 0; !test_state (STATE_EXIT); retries++)
	{
		/*
		 * If this is not the first time, slow down a bit
		 */
		if (diag != ODBC_DIAG_SUCCESS)
			stopgate.Wait ();
		rc = SQLExecDirect (m_ODBC_stmt, sql, SQL_NTS);
		diag = odbc_diag ("SQLExecDirect", rc);
		if (diag == ODBC_DIAG_SUCCESS)
		{
			SQLFreeStmt (m_ODBC_stmt, SQL_CLOSE);
			dw = SQL_SUCCESS;
			break;
		}
		else if (diag == ODBC_DIAG_RECONN)
		{
			Disconnect ();
			Connect ();
			continue;
		}
		else if (diag == ODBC_DIAG_FAIL)
		{
			vmon_log(VLOG_ERROR,"Internal Error: Can't execute SQL command");
			dw = SQL_SUCCESS;
			break;
		}
		if (!CommTimeout (comm_start))
			break;
	}
	return dw;
	vmon_log(VLOG_TRACE,"sql_execute:end");
}

odbc_diag looks like this:
int ODBCDataConnector::odbc_diag (char *fn, int rc)
{
	char *notes = "";
	int retval = ODBC_DIAG_RETRY;
	m_ODBC_Error = 0;
	m_ODBC_Message_Len = 0;

	if (rc == SQL_SUCCESS)		/* It Worked! */
	{
		vmon_log(VLOG_TRACE,"ODBC: SQL_SUCCESS");
		return ODBC_DIAG_SUCCESS;
	}
	else if (rc == SQL_NO_DATA)	/* It worked, but there is no data */
	{
		vmon_log(VLOG_TRACE,"ODBC: SQL_NO_DATA");
		return ODBC_DIAG_SUCCESS;
	}
	else if (rc == SQL_SUCCESS_WITH_INFO)
	{
		vmon_log(VLOG_TRACE,"ODBC: SQL_SUCCESS_WITH_INFO");
		/*
		 * It worked, but is a little chatty about it.
		 */
		get_diag_info ();
		if (!m_has_warned++)
		{
#ifdef POSIX
			vmon_log(VLOG_WARN,"%s SUCCESS_WITH_INFO [%s]", fn, m_ODBC_Message);
#endif
#ifdef WIN32_UNICODE
			vmon_log(VLOG_WARN,"%s SUCCESS_WITH_INFO [%ws]", fn, m_ODBC_Message);
#endif
			return ODBC_DIAG_SUCCESS;
		}
	}
	else
	{
		/*
		 * Well, it failed, lets figure out why and how it failed
		 */
		get_diag_info ();

		if (rc == SQL_STILL_EXECUTING)
		{
			vmon_log(VLOG_TRACE,"ODBC: SQL_STILL_EXECUTING");
			/*
			 * It thinks it is still executing? Just try again, I guess
			 */
			retval = ODBC_DIAG_RETRY;
		}
		else if (rc == SQL_INVALID_HANDLE)
		{
			vmon_log(VLOG_TRACE,"ODBC: SQL_INVALID_HANDLE");
			/*
			 * Something bad here, probably a programming error, but who
			 * knows, just disconnect for now
			 */
			retval = ODBC_DIAG_RECONN;
		}
		else if (rc == SQL_ERROR)
		{
			SQLINTEGER nativeError = 0;
			SQLSMALLINT msgLen = 0;
			vmon_log(VLOG_TRACE,"ODBC: SQL_ERROR");
			/*
			 * Get the SQL Error data beyond Diag
			 */
			SQLError (m_ODBC_Env, m_ODBC_Conn, m_ODBC_stmt, m_ODBC_Status,
					  &nativeError, m_ODBC_Message, ODBC_BUF_LEN - 1, &msgLen);

			m_ODBC_Message[msgLen] = 0;
			vmon_log(VLOG_DEBUG,"SQLError [%ls] [%ls]", m_status, m_errmsg);
			/*
			 * Search list of known codes and handle them as directed
			 */
			retval = ODBC_DIAG_RECONN;
			for (int i = 0; errcodes[i].code; i++)
			{
				if (chkcode (errcodes[i].code, m_errmsg))
				{
					notes = errcodes[i].note;
					retval = errcodes[i].fail;
					break;
				}
			}
		}
		else if (rc == SQL_NEED_DATA)
		{
			vmon_log(VLOG_DEBUG,"ODBC: SQL_NEED_DATA");
			/*
			 * This should never happen, we can only warn about it and punt
			 */
			retval = ODBC_DIAG_FAIL;
		}
		if (!m_has_warned++)
		{
			vmon_log(VLOG_ERROR,"%s SQL Error %d %s [%s] [%s]", fn, rc, notes,
m_status, m_errmsg);
		}
	}
	return retval;
}

My connect and disconnect code look like this:

void ODBCDataConnector::Disconnect (void)
{
	if (m_ODBC_stmt)
	{
		SQLFreeHandle (SQL_HANDLE_STMT, m_ODBC_stmt);
		m_ODBC_stmt = NULL;
	}
	if (m_ODBC_Conn)
	{
		SQLDisconnect (m_ODBC_Conn);
		SQLFreeHandle (SQL_HANDLE_DBC, m_ODBC_Conn);
		m_ODBC_Conn = NULL;
	}
	if (m_ODBC_Env)
	{

		SQLFreeHandle (SQL_HANDLE_ENV, m_ODBC_Env);
		m_ODBC_Env = NULL;
	}
}
int ODBCDataConnector::Connect ()
{
	vmon_log(VLOG_TRACE,"ODBC:Connect");
	SQLRETURN rc;
	/*
	 * Allocate an environment handle. This should not fail. It is not
	 * recoverable
	 */
	rc = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_ODBC_Env);

	if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
	{
		if (!m_has_warned++)
			vmon_log(VLOG_WARN,"Can not create ODBC environment handle");
		return DCN_ERROR;		/* What else can we do? */
	}

	rc = SQLSetEnvAttr (m_ODBC_Env, SQL_ATTR_ODBC_VERSION, (void *)
SQL_OV_ODBC3, 0);

	if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
	{
		if (!m_has_warned++)
			vmon_log(VLOG_WARN,"Can not set ODBC environment attribute");
		Disconnect ();
		return DCN_ERROR;
	}
	/*
	 * Allocate a database connection handle. This should not fail. It is not
	 * recoverable
	 */
	rc = SQLAllocHandle (SQL_HANDLE_DBC, m_ODBC_Env, &m_ODBC_Conn);

	if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
	{
		if (!m_has_warned++)
			vmon_log(VLOG_WARN,"Can not allocate ODBC connection handle");
		Disconnect ();
		return DCN_ERROR;
	}
	SQLSetConnectAttr (m_ODBC_Conn, SQL_LOGIN_TIMEOUT, (SQLPOINTER *) 5, 0);

	/*
	 * This can fail
	 */
	rc = SQLConnect (m_ODBC_Conn, (SQLUCHAR*)m_target, SQL_NTS, 0, 0, 0, 0);

	if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
	{
		if (!m_has_warned++)
			vmon_log(VLOG_WARN,"Can not connect to ODBC database");
		Disconnect ();

		return DCN_ERROR;
	}

	/*
	 * This should not fail
	 */
	rc = SQLAllocHandle (SQL_HANDLE_STMT, m_ODBC_Conn, &m_ODBC_stmt);

	if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
	{
		if (!m_has_warned++)
			vmon_log(VLOG_WARN,"Can not allocate ODBC statement handle");
		Disconnect ();
		return DCN_ERROR;
	}

	SQLSMALLINT len;
	rc = SQLGetInfo (m_ODBC_Conn, SQL_DBMS_NAME, (SQLPOINTER) m_type_str,
ODBC_BUF_LEN, &len);

	if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
	{
		if (!m_has_warned++)
			vmon_log(VLOG_WARN,"Can not get information about connection");
		Disconnect ();
		return DCN_ERROR;
	}

	m_type_str[len] = 0;

#ifdef UNICODE
	wcslwr (m_type_str);
	if (wcsstr (m_type_str, L"oracle"))
		m_type = SQLDCN_DBTYPE_ORACLE;
	else if (wcsstr (m_type_str, L"postgres"))
		m_type = SQLDCN_DBTYPE_POSTGRES;
	else if (wcsstr (m_type_str, L"sqlite"))
		m_type = SQLDCN_DBTYPE_SQLITE;
	else
		m_type = SQLDCN_DBTYPE_UNKNOWN;
#else
	vstrlwr ((char *) m_type_str);
	if (strstr ((char *) m_type_str, "oracle"))
		m_type = SQLDCN_DBTYPE_ORACLE;
	else if (strstr ((char *) m_type_str, "postgres"))
		m_type = SQLDCN_DBTYPE_POSTGRES;
	else if (strstr ((char *) m_type_str, "sqlite"))
		m_type = SQLDCN_DBTYPE_SQLITE;
	else
		m_type = SQLDCN_DBTYPE_UNKNOWN;

#endif
	return DCN_SUCCESS;
}


Responses

pgsql-odbc by date

Next:From: Bruce McAlisterDate: 2008-02-28 22:59:01
Subject: Re: ODBC Version supporting specif databases?
Previous:From: Luiz K. MatsumuraDate: 2008-02-28 20:50:03
Subject: Re: ODBC Version supporting specif databases?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group