Re: Bugs related to getting @@identity - Mailing list pgsql-odbc

* Christian Ullrich wrote on 2015-08-01:

> * From: Heikki Linnakangas
>
>> On 07/31/2015 09:30 PM, Christian Ullrich wrote:
>
>>> as there have been ominous rumblings lately about making another
>>> release, I wanted to remind you that I reported two bugs on 23 April.
>>> One breaks @@identity entirely [1], the other makes it unreliable with
>>> INSERTs into schema-qualified tables [2].
>
>> How do you use @@identity? I don't know what the idiomatic way to use it
>> is.
>>
>> I wrote a little test case for it, see attached patch. But it runs OK
>> with a fresh git checkout. Could you modify/expand this test case to
>> tickle the bugs you're seeing?
>
> Working on it; now I have to build the driver again. Joy!

It only took me four months (of procrastination), but now I know at
least how to trigger the bug, if not where exactly it is. It has to do
with how the "SELECT @@identity" is done. It only works if it is *not*
prepared.

My application is actually using ADO via the OLEDB bridge, and it
prepares all statements it generates. However, whether this statement is
prepared or not influences how the driver processes it.

I set a breakpoint early in SC_execute() (statement.c:1823, to be exact)
to print the statement text and connection state:

Unprepared:

SC_execute with CONN_CONNECTED (1): "INSERT INTO history ..." SC_execute
with CONN_CONNECTED (1): "select n.nspname, ..."
SC_execute with CONN_CONNECTED (1): "SELECT @@identity"

Prepared:

SC_execute with CONN_CONNECTED (1): "INSERT INTO history ..." SC_execute
with CONN_CONNECTED (1): "SELECT @@identity"
SC_execute with CONN_EXECUTING (3): "select n.nspname, ..."

The "select n.nspname" is the metadata query necessary to get the
autogenerated ID from the earlier INSERT. The difference is that in the
unprepared case, SQLExecDirect() is used, while the prepared statement
is run using SQLExecute().

This is the call stack for the prepared statement, bottom to top:

*1      psqlodbc35w.dll!SC_execute(StatementClass_ * self=0x0033d048)
  2      psqlodbc35w.dll!Exec_with_parameters_resolved(StatementClass_ *
stmt=0x0033d048, int * exec_end=0x0018af38)
  3      psqlodbc35w.dll!PGAPI_Execute(void * hstmt=0x0033d048, unsigned
short flag=0)
  4      psqlodbc35w.dll!PGAPI_ExecDirect(void * hstmt=0x0033d048, const
unsigned char * szSqlStr=0x0018b664, long cbSqlStr=-3, unsigned short
flag=0)
  5      psqlodbc35w.dll!PGAPI_Columns(void * hstmt=0x0032aa20, const
unsigned char * szTableQualifier=0x00000000, short cbTableQualifier=0,
const unsigned char * szTableOwner=0x048b4c80, short cbTableOwner=-3,
const unsigned char * szTableName=0x0489b428, short cbTableName=-3,
const unsigned char * szColumnName=0x00000000, short cbColumnName=0,
unsigned short flag=1, unsigned int reloid=0, short attnum=0)
  6      psqlodbc35w.dll!getColumnsInfo(ConnectionClass_ *
conn=0x002c1fc0, TABLE_INFO * wti=0x0018db54, unsigned int greloid=0,
StatementClass_ * stmt=0x002c15b8)
  7      psqlodbc35w.dll!getCOLIfromTI(const char * func=0x5dd05b38,
ConnectionClass_ * conn=0x002c1fc0, StatementClass_ * stmt=0x002c15b8,
const unsigned int reloid=0, TABLE_INFO * * pti=0x0018db48)
  8      psqlodbc35w.dll!inner_process_tokens(_QueryParse *
qp=0x0018de44, _QueryBuild * qb=0x0018dde4)
  9      psqlodbc35w.dll!prepareParametersNoDesc(StatementClass_ *
stmt=0x002c15b8, int fake_params=0)
  10     psqlodbc35w.dll!prepareParameters(StatementClass_ *
stmt=0x002c15b8, int fake_params=0)
  11     psqlodbc35w.dll!libpq_bind_and_exec(StatementClass_ *
stmt=0x002c15b8)
  12     psqlodbc35w.dll!SC_execute(StatementClass_ * self=0x002c15b8)
  13     psqlodbc35w.dll!Exec_with_parameters_resolved(StatementClass_ *
stmt=0x002c15b8, int * exec_end=0x0018e84c)
  14     psqlodbc35w.dll!PGAPI_Execute(void * hstmt=0x002c15b8, unsigned
short flag=1)
  15     psqlodbc35w.dll!SQLExecute(void * StatementHandle=0x002c15b8)


And here for the unprepared one:

*1      psqlodbc35w.dll!SC_execute(StatementClass_ * self=0x006801b8)
  2      psqlodbc35w.dll!Exec_with_parameters_resolved(StatementClass_ *
stmt=0x006801b8, int * exec_end=0x0018b3a0)
  3      psqlodbc35w.dll!PGAPI_Execute(void * hstmt=0x006801b8, unsigned
short flag=0)
  4      psqlodbc35w.dll!PGAPI_ExecDirect(void * hstmt=0x006801b8, const
unsigned char * szSqlStr=0x0018bacc, long cbSqlStr=-3, unsigned short
flag=0)
  5      psqlodbc35w.dll!PGAPI_Columns(void * hstmt=0x0067ff48, const
unsigned char * szTableQualifier=0x00000000, short cbTableQualifier=0,
const unsigned char * szTableOwner=0x0460ca68, short cbTableOwner=-3,
const unsigned char * szTableName=0x045f4ef0, short cbTableName=-3,
const unsigned char * szColumnName=0x00000000, short cbColumnName=0,
unsigned short flag=1, unsigned int reloid=0, short attnum=0)
  6      psqlodbc35w.dll!getColumnsInfo(ConnectionClass_ *
conn=0x0063efd0, TABLE_INFO * wti=0x0018dfbc, unsigned int greloid=0,
StatementClass_ * stmt=0x0063fa50)
  7      psqlodbc35w.dll!getCOLIfromTI(const char * func=0x5dd05b38,
ConnectionClass_ * conn=0x0063efd0, StatementClass_ * stmt=0x0063fa50,
const unsigned int reloid=0, TABLE_INFO * * pti=0x0018dfb0)
  8      psqlodbc35w.dll!inner_process_tokens(_QueryParse *
qp=0x0018e3c4, _QueryBuild * qb=0x0018e364)
  9      psqlodbc35w.dll!copy_statement_with_parameters(StatementClass_
* stmt=0x0063fa50, int buildPrepareStatement=0)
  10     psqlodbc35w.dll!Exec_with_parameters_resolved(StatementClass_ *
stmt=0x0063fa50, int * exec_end=0x0018e740)
  11     psqlodbc35w.dll!PGAPI_Execute(void * hstmt=0x0063fa50, unsigned
short flag=1)
  12     psqlodbc35w.dll!PGAPI_ExecDirect(void * hstmt=0x0063fa50, const
unsigned char * szSqlStr=0x0067b448, long cbSqlStr=17, unsigned short
flag=1)
  13     psqlodbc35w.dll!SQLExecDirectW(void *
StatementHandle=0x0063fa50, unsigned short * StatementText=0x040b2db8,
long TextLength=17)

--
Christian