Jan-Henrik,
This call always returns 0 on a postgresql connection. I assume this is because my tables are without OIDs. Is that intentional? I can work around it. But perhaps a small note in the api docs would be in order?
What command did you use in dbmail to get last insert row id in postgres?
On 28. feb.. 2008, at 21.55, Paul J Stevens wrote:
Jan-Henrik,
This call always returns 0 on a postgresql connection. I assume this is because my tables are without OIDs. Is that intentional? I can work around it. But perhaps a small note in the api docs would be in order?
Jan-Henrik Haukeland wrote:
What command did you use in dbmail to get last insert row id in postgres?
Well, until now we were using a sequence and called "select curval('sequence_identifier')" after each insert if a last inserted row id was required.
But I've decided to switch to using the 'RETURNING id' instead to avoid the additional roundtrip for the extra query. I use executeQuery for the INSERTs involved, passing the connector and result pointer to my facade for lastRowId. That call then tries to retrieve the id from the resultset if lastRowId returns 0 and the resultset is not null. Appears to work just fine.
Of course 'RETURNING x' is a postgresql construct but I have a framework in place for dealing with backend specific dialect fragments.
On 28. feb.. 2008, at 21.55, Paul J Stevens wrote:
Jan-Henrik,
This call always returns 0 on a postgresql connection. I assume this is because my tables are without OIDs. Is that intentional? I can work around it. But perhaps a small note in the api docs would be in order?
-- To unsubscribe: http://www.tildeslash.com/mailman/listinfo/libzdb-general
I'll see if we can come up with something better, that is, more general to be used in Connection_lastRowId() for postgres, since this method works fine for mysql and sqlite it should so for postgres as well. If you should come over anything for postgres also, please let me know as well.
On 29. feb.. 2008, at 09.35, Paul J Stevens wrote:
Jan-Henrik Haukeland wrote:
What command did you use in dbmail to get last insert row id in postgres?
Well, until now we were using a sequence and called "select curval('sequence_identifier')" after each insert if a last inserted row id was required.
But I've decided to switch to using the 'RETURNING id' instead to avoid the additional roundtrip for the extra query. I use executeQuery for the INSERTs involved, passing the connector and result pointer to my facade for lastRowId. That call then tries to retrieve the id from the resultset if lastRowId returns 0 and the resultset is not null. Appears to work just fine.
Of course 'RETURNING x' is a postgresql construct but I have a framework in place for dealing with backend specific dialect fragments.
On 28. feb.. 2008, at 21.55, Paul J Stevens wrote:
Jan-Henrik,
This call always returns 0 on a postgresql connection. I assume this is because my tables are without OIDs. Is that intentional? I can work around it. But perhaps a small note in the api docs would be in order?
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?
Jan-Henrik Haukeland wrote:
I'll see if we can come up with something better, that is, more general to be used in Connection_lastRowId() for postgres, since this method works fine for mysql and sqlite it should so for postgres as well. If you should come over anything for postgres also, please let me know as well.
On 29. feb.. 2008, at 09.35, Paul J Stevens wrote:
Jan-Henrik Haukeland wrote:
What command did you use in dbmail to get last insert row id in postgres?
Well, until now we were using a sequence and called "select curval('sequence_identifier')" after each insert if a last inserted row id was required.
But I've decided to switch to using the 'RETURNING id' instead to avoid the additional roundtrip for the extra query. I use executeQuery for the INSERTs involved, passing the connector and result pointer to my facade for lastRowId. That call then tries to retrieve the id from the resultset if lastRowId returns 0 and the resultset is not null. Appears to work just fine.
Of course 'RETURNING x' is a postgresql construct but I have a framework in place for dealing with backend specific dialect fragments.
On 28. feb.. 2008, at 21.55, Paul J Stevens wrote:
Jan-Henrik,
This call always returns 0 on a postgresql connection. I assume this is because my tables are without OIDs. Is that intentional? I can work around it. But perhaps a small note in the api docs would be in order?
-- To unsubscribe: http://www.tildeslash.com/mailman/listinfo/libzdb-general
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);
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.
#include <stdio.h> #include <string.h> #include <assert.h>
#include <URL.h> #include <ResultSet.h> #include <PreparedStatement.h> #include <Connection.h> #include <ConnectionPool.h> #include <SQLException.h>
/*
create table test ( id INTEGER PRIMARY KEY AUTOINCREMENT, data blob NOT NULL);
*/
const char *blob1 = "From nobody@pacific.net.sg Tue Dec 04 19:52:17 2007\n" "X-Envelope-From: nobody@pacific.net.sg\n" "Received: from [127.0.0.1] (port=49353 helo=test11)\n" " by centos.nowhere.com with smtp (Exim 4.63)\n" " (envelope-from nobody@pacific.net.sg)\n" " id 1IzWJv-0000Ep-5f\n" " for wallace@nowhere.com; Tue, 04 Dec 2007 19:52:17 +0800\n" "From: "Wallace" nobody@pacific.net.sg\n" "To: wallace wallace@nowhere.com\n" "Subject: Test 11\n" "Message-Id: E1IzWJv-0000Ep-5f@centos.nowhere.com\n" "Date: Tue, 04 Dec 2007 19:52:16 +0800\n" "\n" "\n" "This line works, however,\n" "From what I know, this line gets truncated\n" "This line gets truncated\n" "This other line get truncated too\n";
const char *blob2 = "("Tue, 06 Aug 2002 19:54:41 +0200" "[dovecot] mbox support" (("Marcus Rueckert" NIL "rueckert" "informatik.uni- rostock.de")) ((NIL NIL "dovecot-bounce" "procontrol.fi")) (("Marcus Rueckert" NIL "rueckert" "informatik.uni-rostock.de")) (("dovecot mailing list" NIL "dovecot" "procontrol.fi")) NIL NIL NIL "0000420020806175441.GA7148@linux.taugt.net")";
//#define DMTEST #define BUFSIZE 8192 int main(void) { const char *in = blob1; int i = 0; long long int id; ResultSet_T res; PreparedStatement_T s; ZBDEBUG=1; // URL_T url = URL_new("mysql://test:test@localhost:3306/ test"); URL_T url = URL_new("sqlite:///tmp/test.db"); assert(url); ConnectionPool_T pool = ConnectionPool_new(url); assert(pool); ConnectionPool_start(pool); Connection_T con = ConnectionPool_getConnection(pool); assert(con);
TRY { s = Connection_prepareStatement(con, "INSERT INTO test (data) values ( ? )"); for (i=0; i<20; i++) { PreparedStatement_setString(s,1,in); PreparedStatement_execute(s); id = Connection_lastRowId(con);
if (id) printf("Last row id: %lld\n", id); else printf("Error: no last rowid\n"); }
res = Connection_executeQuery(con, "SELECT id,data FROM test LIMIT 10"); while(ResultSet_next(res)) { const char *out = ResultSet_getString(res,2); if (strcmp(in, out) != 0) { printf("Error mismatch\n[%s]\n[%s] \n", in, out); } else { printf("Row matches\n"); } } } CATCH(SQLException) { printf("SQLException: %s\n", Connection_getLastError(con)); } FINALLY { Connection_close(con); } END_TRY;
return 0; }
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).
Paul J Stevens wrote:
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).
I've figured out what was wrong. I need to call ResultSet_next() before I can determine the lastRowId() after calling executeQuery. Makes sense after reading the code. (sqlite3_step only being called from ResultSet_next).
However, using this pattern means I have to a full Connection_preparedStatement, PreparedStatement_setXXX, PreparedStatement_executeQuery, Connection_clear for each row I insert where I need to obtain the ROWID. More room for improvement, imo.
This makes me suspicious a more general rule may apply: can I re-use a prepared statement that returns a result set? I suspect not, at least not for sqlite....
Instead of this workaround, it would be much better if we could change Connection_lastRowId() to work with postgresql somehow. Any ideas anyone?
And yes, reusing a prepared statement after it returns a result set is problematic for SQLite, hence Connection_clear() was exposed. Creating a new statement for the result set instead of reusing the prepared one should work, but it has not been a big enough nag to refactor.
On 1. mai. 2008, at 17.45, Paul J Stevens wrote:
Paul J Stevens wrote:
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).
I've figured out what was wrong. I need to call ResultSet_next() before I can determine the lastRowId() after calling executeQuery. Makes sense after reading the code. (sqlite3_step only being called from ResultSet_next).
However, using this pattern means I have to a full Connection_preparedStatement, PreparedStatement_setXXX, PreparedStatement_executeQuery, Connection_clear for each row I insert where I need to obtain the ROWID. More room for improvement, imo.
This makes me suspicious a more general rule may apply: can I re-use a prepared statement that returns a result set? I suspect not, at least not for sqlite....
Jan-Henrik Haukeland wrote:
Instead of this workaround, it would be much better if we could change Connection_lastRowId() to work with postgresql somehow. Any ideas anyone?
And yes, reusing a prepared statement after it returns a result set is problematic for SQLite, hence Connection_clear() was exposed. Creating a new statement for the result set instead of reusing the prepared one should work, but it has not been a big enough nag to refactor.
It's not a big problem for me either - yet. But it does impose a specific pattern in my code that is not evident at first glance. This is only an issue during big loops triggering massive amounts of insert queries, each of which *must* return the lastrowid. But that only affects throughput, so it's not critical - unlike the mysql bug :-(.
I also noticed you're using the deprecated sqlite3 interface for statements, right? I'm asking because I'm seeing very undescriptive errors raised by executing statements in the sqlite code: SQL logic error or missing database.
It looks like all that is needed is replacing sqlite3_prepare with sqlite3_prepare_v2. I'll do some testing today...
later,
On 2. mai. 2008, at 09.35, Paul J Stevens wrote:
Jan-Henrik Haukeland wrote:
And yes, reusing a prepared statement after it returns a result set is problematic for SQLite, hence Connection_clear() was exposed. Creating a new statement for the result set instead of reusing the prepared one should work, but it has not been a big enough nag to refactor.
It's not a big problem for me either - yet. But it does impose a specific pattern in my code that is not evident at first glance. This is only an issue during big loops triggering massive amounts of insert queries, each of which *must* return the lastrowid. But that only affects throughput,
Well, almost all of this would not be necessary if a workaround for Connection_lastRowId() on postgres could be found. This is the root cause and trying to find a solution for this, if possible, would be best.
so it's not critical - unlike the mysql bug :-(.
Yes, the mysql bug is very bad, and a workaround *must* be found. I'll look into that.
I also noticed you're using the deprecated sqlite3 interface for statements, right? I'm asking because I'm seeing very undescriptive errors raised by executing statements in the sqlite code: SQL logic error or missing database.
It looks like all that is needed is replacing sqlite3_prepare with sqlite3_prepare_v2. I'll do some testing today...
sqlite3_prepare_v2() was first added in SQLite version 3.3.9 and libzdb should work with 3.x. But you are right, using this interface, if available, is preferable with regards to error reporting. I'll use an #ifdef to add it.
Jan-Henrik Haukeland wrote:
Yes, the mysql bug is very bad, and a workaround *must* be found. I'll look into that.
I hope we can figure this one out. It's my only real showstopper since my code is now stabile for sqlite and postgres.
sqlite3_prepare_v2() was first added in SQLite version 3.3.9 and libzdb should work with 3.x. But you are right, using this interface, if available, is preferable with regards to error reporting. I'll use an #ifdef to add it.
Please do. I did a small change against libzdb today and tested my code with the v2 interface. A two line change and no more undescriptive errors - just table lock issues under high concurrencies. And those are pretty much unavoidable I guess, unless I set initialConnections/maxConnections both at one. But then throughput is terrible. So I'll take my chances with the table locks and suck up the errors.
On 2. mai. 2008, at 22.25, Paul J Stevens wrote:
just table lock issues under high concurrencies. And those are pretty much unavoidable I guess
Increasing timeout can help reduce the lock issue in SQLite. Try to increase Connection_setQueryTimeout() to e.g. 5000. Default is 3000. Its a trade off, but if you run a request per thread its usually better to have the statement go through and live with an occasional spike in throughput.
On 2. mai. 2008, at 22.25, Paul J Stevens wrote:
Jan-Henrik Haukeland wrote:
Yes, the mysql bug is very bad, and a workaround *must* be found. I'll look into that.
I hope we can figure this one out. It's my only real showstopper since my code is now stabile for sqlite and postgres.
If found a simple workaround by playing around in GDB. Seems to work, but only tested with the test program you sent. Please let me know if this works for you.
Yeah! It works for me :-)
No more showstoppers for me - at least not libzdb related.
Thanks a lot.
Jan-Henrik Haukeland wrote:
On 2. mai. 2008, at 22.25, Paul J Stevens wrote:
Jan-Henrik Haukeland wrote:
Yes, the mysql bug is very bad, and a workaround *must* be found. I'll look into that.
I hope we can figure this one out. It's my only real showstopper since my code is now stabile for sqlite and postgres.
If found a simple workaround by playing around in GDB. Seems to work, but only tested with the test program you sent. Please let me know if this works for you. -- To unsubscribe: http://www.tildeslash.com/mailman/listinfo/libzdb-general
On 3. mai. 2008, at 22.50, Paul J Stevens wrote:
Yeah! It works for me :-)
No more showstoppers for me - at least not libzdb related.
Thanks a lot.
Great! I'll release libzdb 2.2.1 then soon. Good luck with the new dbmail release! Thanks a lot to you too for all your testing and excellent bug reports.
-
Jan-Henrik Haukeland -
Paul J Stevens