Can't restore connection after connection reset

[Stanley]

AFIACT, I am using UnixODBC 3.51 (#define ODBCVER 0x0351) on Debian Linux version 4r3.

I have implemented a long-running database application. The problem I am seeing is that the database connection is getting reset by the OS overnight (my ssh debug session is also reset). I have added code to my application to detect this condition and recover, but I cannot get it to work properly.

I detect the disconnect by checking the attribute SQL_ATTR_CONNECTION_DEAD.

When I detect a disconnect I reconnect using SQLConnect, re-allocate my Statement Handles, and make all the appropriate calls to SQLBindCol. I then attempt to execute my query by calling SQLExecDirect, SQLRowCount, and SQLFetch.

All of the above calls return success, the row count is correct, and SQLFetch returns success the correct number of times. However, the buffers I have bound using SQLBindCol do not get new contents, but instead remain unchanged from the initial state. Given that SQLBindCol returned success, what could cause this sort of misbehavior, and what can I do about it??

[Nick Gorham]

On 12/08/13 18:45, Stanley wrote:

AFIACT, I am using UnixODBC 3.51 (#define ODBCVER 0x0351) on Debian
Linux version 4r3.

I have implemented a long-running database application. The problem I
am seeing is that the database connection is getting reset by the OS
overnight (my ssh debug session is also reset). I have added code to
my application to detect this condition and recover, but I cannot get
it to work properly.

I detect the disconnect by checking the attribute
SQL_ATTR_CONNECTION_DEAD.

When I detect a disconnect I reconnect using SQLConnect, re-allocate
my Statement Handles, and make all the appropriate calls to
SQLBindCol. I then attempt to execute my query by calling
SQLExecDirect, SQLRowCount, and SQLFetch.

All of the above calls return success, the row count is correct, and
SQLFetch returns success the correct number of times. However, the
buffers I have bound using SQLBindCol do not get new contents, but
instead remain unchanged from the initial state. Given that SQLBindCol
returned success, what could cause this sort of misbehavior, and what
can I do about it??

I can't think of a obvious reason why that would be. It should be the
same as if it was a brand new connection (in fact it is), the most
likely reason is the bound memory is not what you think it is, maybe you
are binding new memory, but looking in the old memory?

--
Nick

[Stanley]

Unlikely. I am using the exact same member function of the exact same object, and pointing to the same data members (I am writing this in C++).

class MyClass
...
private:
char m_Id[PID_SIZE+1];
char m_Name[PNAME_SIZE+1];
short m_Status;
char m_description[MAX_DESCR+1];
char m_ipAddress[IPADDR_LEN+1];
SQLTIME m_lasttime[TIME_SIZE+1];
};

void MyClass::setupQuery()
{
SQLFreeStmt(getHandle(), SQL_UNBIND);
// Bind the data buffers to the selected query columns
bindColumn( SQL_CHAR, &m_Id[0], PID_SIZE+1 );
bindColumn( SQL_CHAR, &m_Name[0], PNAME_SIZE+1 );
bindColumn( SQL_INTEGER, &m_Status, sizeof(m_playerStatus) );
bindColumn( SQL_CHAR, &m_description[0], MAX_DESCR+1 );
bindColumn( SQL_CHAR, &m_ipAddress[0], IPADDR_LEN+1 );
bindColumn( SQL_CHAR, &m_lasttime[0], TIME_SIZE );
}

[Stanley]

Oh, and PS, the member function bindColumn just calls SQLBinbdCol with appropriate error checks, getting the statement handle from the base class.

[Nick Gorham]

On 12/08/13 21:05, Stanley wrote:

Unlikely. I am using the exact same member function of the exact same
object, and pointing to the same data members (I am writing this in C++).

Can't offer much help then, if I was you, I would put some printf debug
at the point where the buffer is bound, and where the read is performed
to make sure the same address is used.

BTW, do you pass a indicator pointer into the actual SQLBindCol?

You dont say what driver you are using.

--
Nick

[Stanley]

In gathering information to answer your questions, I noticed an error: I had forgotten to reset the column numbers, so I was binding my buffers to non-existent columns.

Thanks, fresh eyes always seem to help somehow.

[Nick Gorham]

On 13/08/13 14:49, Stanley wrote:

In gathering information to answer your questions, I noticed an error:
I had forgotten to reset the column numbers, so I was binding my
buffers to non-existent columns.

That would do it :-)

--
Nick