Possible ODBC Driver Bug (via VC++/MFC CRecordset)

Hello All,

I'm new to this list...and PostgreSQL. I've got my server all up and
running...and I am porting an application that had been using MSSQL as
its backend.

When I use my app to browse around (i.e., SELECTS), everything is
behaving nicely. I am not having such luck with UPDATEs.

I am using the [ODBC] CRecordset class as the base class of my access
classes. They are foreign variables of CRecordView-derived classes (the
usual way of going about things). When I change data on the form and
click apply, I get a strange behavior:

(1) The framework pops up an error message: "Update or Delete failed."
This error is dismissable by clicking "OK".

(2) In reality, the update did NOT fail. It is changed in the back-end.

Things to note: This all worked just fine in MSSQL...so it isn't a
problem in my code. My guess is that one of the two ODBC drivers in
non-conformant.

More details I've uncovered:

This error is being raised from execution of the following code:

// This should only fail if SQLSetPos returned
// SQL_SUCCESS_WITH_INFO explaining why

if (nRetCode == SQL_SUCCESS_WITH_INFO &&
GetRowStatus(1) != wExpectedRowStatus)
ThrowDBException(AFX_SQL_ERROR_UPDATE_DELETE_FAILED);

This code lives in CRecordset::ExecuteSetPosUpdate(); it is invoked to
check the return values after the call to:

AFX_ODBC_CALL(::SQLSetPos(m_hstmt, 1, wPosOption, SQL_LOCK_NO_CHANGE));

When MSSQL returns, nRetCode is set to 0 (SQL_SUCCESS). The call
GetRowStatus(1) will just return m_rgRowStatus[0]. With MSSQL, the
value of m_rgRowStatus[0] is 0 (SQL_ROW_SUCCESS) before the SQLSetPos
call and 2 afterward (SQL_ROW_UPDATED). Because nRetCode is not
SQL_SUCCESS_WITH_INFO, the first part of the conditional fails and the
exception is NOT thrown.

When PostgreSQL returns, nRetCode is set to 1 (SQL_SUCCESS_WITH_INFO).
With PostgreSQL, the value of m_rgRowStatus[0] is 0 (SQL_ROW_SUCCESS)
before the SQLSetPos call and IS STILL 0 afterward (SQL_ROW_UPDATED).
Because nRetCode is SQL_SUCCESS_WITH_INFO (as opposed to plain old
SQL_SUCCESS), the first part of the conditional is met. The second part
is now checked. Becuase this is an update, the preliminary work done by
this function sets wExpectedRowStatus to 2 (SQL_ROW_UPDATED). As noted
above, PostgreSQL is leaving the value of m_rgRowStatus[0] as 0
(SQL_ROW_UPDATED).

Because the MS ODBC driver is correctly setting m_rgRowStatus[0] to 2
(SQL_ROW_UPDATED) after the call to SQLSetPos [even though it doesn't
matter because it never gets checked since it only returns SQL_SUCCESS
without the extra _WITH_INFO], and because it works, I assume this is an
error in the psqlodbc driver.

More detailed info (versions, debud dump, etc. can be found below this
message). I'll be happy to provide any additional info needed.

I downloaded the driver source and started looking aroung at it. The
function that I believe is getting called [SC_pos_update()] will, if
successful, always return SQL_SUCCESS_WITH_INFO. Is that the
appropriate behavior? Why not just return the simple SQL_SUCCESS?

I have NEVER looked at the internals of ODBC before. This is starting
to give me a headache...though it is making me think about getting
actively involved with development of the driver [;)] Before I dig any
deeper...can anyone think of something simple that I may doing wrong?
Perhaps a driver setting (though I've tried most of the possible
combinations of things that sound like they could affect this)?

As an aside, can anyone tell me what I need to do to be able to debug
the driver DLL live using Visual Studio 6?

Take Care,

-Joe Papavero

------------------------------------------------------------------

PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
psqlodbc Driver version: 7.2.2

...
[SQLSetPos]PGAPI_SetPos fOption=2 irow=1 lock=0 currt=1
PGAPI_SetConnectOption: entering fOption = 102 vParam = 0
PGAPI_SetConnectOption: AUTOCOMMIT: transact_status=1, vparam=0
POS UPDATE 0+0 fi=14fecf0 ti=14feba0
0 used=-6,7cb114
1 used=7,7cb128
updstr=update "ICT_Campuses" set "campusName" = ? where ctid = '(0, 30)'
and oid = 16809
PGAPI_AllocStmt: entering...
**** PGAPI_AllocStmt: hdbc = 20857408, stmt = 22020544
CC_add_statement: self=20857408, stmt=22020544
0 used=-6
1 used=7
PGAPI_BindParameter: entering...
extend_parameter_bindings: entering ... self=22020644,
parameters_allocated=0, num_params=1
exit extend_parameter_bindings
PGAPI_BindParamater: ipar=0, paramType=1, fCType=1, fSqlType=12,
cbColDef=100, ibScale=-1, rgbValue=8177964, *pcbValue = 7, data_at_exec = 0
PGAPI_ExecDirect: entering...
**** PGAPI_ExecDirect: hstmt=22020544, statement='update "ICT_Campuses"
set "campusName" = ? where ctid = '(0, 30)' and oid = 16809'
PGAPI_ExecDirect: calling PGAPI_Execute...
PGAPI_Execute: entering...
PGAPI_Execute: clear errors...
recycle statement: self= 22020544
PGAPI_Execute: copying statement params: trans_status=0, len=81,
stmt='update "ICT_Campuses" set "campusName" = ? where ctid = '(0, 30)'
and oid = 16809'
ResolveOneParam: from(fcType)=1, to(fSqlType)=12
stmt_with_params = 'update "ICT_Campuses" set "campusName" =
'Branch2' where ctid = '(0, 30)' and oid = 16809'
about to begin a transaction on statement = 22020544
it's NOT a select statement: stmt=22020544
send_query(): conn=20857408, query='update "ICT_Campuses" set
"campusName" = 'Branch2' where ctid = '(0, 30)' and oid = 16809'
send_query: done sending query
in QR_Constructor
exit QR_Constructor
read 25, global_socket_buffersize=4096
send_query: got id = 'C'
send_query: ok - 'C' - BEGIN
send_query: setting cmdbuffer = 'BEGIN'
send_query: got id = 'P'
send_query: got id = 'C'
send_query: ok - 'C' - UPDATE 1
send_query: setting cmdbuffer = 'UPDATE 1'
send_query: returning res = 22028160
send_query: got id = 'Z'
PGAPI_ExecDirect: returned 0 from PGAPI_Execute
positioned load fi=14fecf0 ti=14feba0
...