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,
- 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
- 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).