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; }