Possible ODBC Driver Bug (via VC++/MFC CRecordset) - Mailing list pgsql-hackers

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
aselect 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
...