Re: Cannot retrieve sequence number after an insert with OLE DB - Mailing list pgsql-odbc

Hi Philippe,

On 2014/09/18 18:51, Philippe Champignon wrote:
> Hello,
>
> I just submit a bug in the psqlODBC driver 9.2/9.3 (32-bit and 64-bit).
>
> *_The context :_*
>
> I have a Windows program that uses the database postgresql through OLE
> DB and psqlODBC driver. In the database, I have a table with a sequence
> that generates a number whenever you insert a new row in the table. The
> program inserts data into the table and retrieves the auto incremented
> sequence value.
>
> With Postgresql 8.2 and the psqlODBC 8.02.0400 driver, the program works
> perfectly. But with Postgresql 9.2 or 9.3 and the psqlODBC 9.02.0100 or
> 9.03.0300 driver it is impossible to retrieve the sequence number after
> the insertion of the line. There are always 0.
>
> *_Tests:_*
>
> *__*
>
> To identify the problem, I did several tests, which allowed me to find
> that it was the ODBC driver that is problematic.
>
> PsqlODBC version
>
>
>
> Postgresql version
>
>
>
> Result
>
> 8.02.0400
>
>
>
> 8.2
>
>
>
> OK
>
> 9.02.0100
>
>
>
> 9.2.4
>
>
>
> KO
>
> 8.02.0400
>
>
>
> 9.2.4
>
>
>
> OK
>
> 8.02.0500
>
>
>
> 9.2.5
>
>
>
> KO
>
> 9.03.0300
>
>
>
> 9.3.2
>
>
>
> KO
>
> With version 8.02.04 of the driver, the problem does not occur. But with
> the 8.02.05 driver version, I reproduce the problem.
>
> So I compared the 8.02.04 driver and driver 8.02.05 sources and
> positioned the traces of the driver to find the bug: it is in the
> SC_setInsertedTable function, when parsing the name of the table to find
> the schema.
>
> *_Explanation :_*
>
> With the driver 8.02.04, in traces, we see this:
>
> [11.819]conn=02354D10, query='INSERT INTO
> "iamaudit"."public"."AuditEvents"
>
("EvtCategory","EvtType","EvtOperation","TimeStamp","ResultCode","UserAuditID","SessionID","AccessPointID","AccessPointName","ClientIP","ClientHostname","ExtendedInfo")
> VALUES (1,1234,3,'2013-09-23
> 12:30:52'::timestamp,-2147467259,'champi','','','DEROBEE','','','')'
>
> [11.841]conn=02354D10, query='SELECT lastval()'
>
> [11.856]    [ fetched 1 rows ]
>
> [11.892]conn=02354D10, query='COMMIT'
>
> With the driver 8.02.05, in traces, we see this:
>
> [17.297]conn=02354D10, query='INSERT INTO
> "iamaudit"."public"."AuditEvents"
>
("EvtCategory","EvtType","EvtOperation","TimeStamp","ResultCode","UserAuditID","SessionID","AccessPointID","AccessPointName","ClientIP","ClientHostname","ExtendedInfo")
> VALUES (1,1234,3,'2013-09-23
> 12:41:27'::timestamp,-2147467259,'champi','','','DEROBEE','','','')'
>
> [17.336]conn=02354D10, query='select n.nspname, c.relname, a.attname,
> a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull,
> c.relhasrules, c.relkind, c.oid, d.adsrc from (((pg_catalog.pg_class c
> inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and
> c.relname = 'public' and n.nspname = 'iamaudit') inner join
> pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and
> a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid =
> a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid =
> a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum'
>
> [17.351]    [ fetched 0 rows ]
>
> [17.401]conn=02354D10, query='SELECT NULL'
>
> [17.405]    [ fetched 1 rows ]
>
> [17.439]conn=02354D10, query='COMMIT'
>
> The problem is revealed by the correction in 8.02.0402:
>
> 3.) Put back the @@IDENTITY implementation so as not to use lastval().
>
> During this correction, changed the use of lastval() that is dangerous
> by the use of curval (sequence) much more secure. Then, why this
> correction does not work in my case? In the trace, seen just after the
> insert, there is a select that returns no rows! This select is directed
> to search the sequences in the table. It does not work because it passes
> 'public' as table name  and 'iamaudit' as schema name instead of
> 'AuditEvents' and 'public'. Because in the insert, I use the string
> "iamaudit"."public"."AuditEvents"to identify the table. The table name
> is prefixed by the name of the base and the schema, which was not
> expected. Note that it is the OLE DB driver which writes the query and
> that uses this syntax.
>
> The analysis of the insert query is performed in the function
> SC_setInsertedTable (file execute.c). So I modified this function to
> update the parsing  to my case and I tested successfully this correction.

Thanks for your report.

Could you please try the attached patch setInsertedTable.patch or
replace the content of SC_setInsertedTable() function by the attached
setInsertedTable.txt?

regards,
Hiroshi Inoue