Connection_lastRowId on postgresql

[Paul J Stevens]

Jan-Henrik Haukeland wrote:
> On 25. april. 2008, at 14.34, Paul J Stevens wrote:
> 
>> Following up because my work-around fails for sqlite.
>>
>> The work-around entails using the 'RETURNING id' clause for  
>> postgres, using
>> executeQuery for INSERTs on tables with auto_increment primary keys,  
>> and using
>> the returned ResultSet to retrieve the new id if  
>> Connection_lastRowId fails
>> (with postgres).
>>
>> This does the job for postgres and mysql, but alas, sqlite fails.
>>
>> test program attached, as always.
>>
>> Any ideas?
> 
> Hi Paul, yes I have a couple of ideas,
> 
> 1) To enable auto increment in SQLite, use AUTOINCREMENT  and create  
> your sqlite table like so,
> 
> create table test (id INTEGER PRIMARY KEY AUTOINCREMENT, data blob NOT  
> NULL);

That is not quite correct. Use of the autoincrement keyword only affects
the algorithm used to select the next ROWID. As long as a field is
INTEGER PRIMARY KEY, that field is used and will automatically contain a
new unique ROWID value.

http://www.sqlite.org/autoinc.html


> 
> 2) There is a bug in your test code. If you use  
> PreparedStatement_execute() instead of  
> PreparedStatement_executeQuery() it works as expected. Enclosed  
> working fixed test program for SQLite.

That was my point. I *need* to used executeQuery to enable the postgres
work-around. Remember that my code needs to work for all backends. Using
executeQuery works for both postgres (using the returned resultset) as
well as for mysql (getting lastRowId directly).


-- 
  ________________________________________________________________
  Paul Stevens                                      paul at nfg.nl
  NET FACILITIES GROUP                     GPG/PGP: 1024D/11F8CD31
  The Netherlands________________________________http://www.nfg.nl