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;
}
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
|
