Connection_lastRowId on postgresql

[Jan-Henrik Haukeland]

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 at pacific.net.sg Tue Dec 04 19:52:17  
2007\n"
"X-Envelope-From: <nobody at 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 at pacific.net.sg>)\n"
"        id 1IzWJv-0000Ep-5f\n"
"        for wallace at nowhere.com; Tue, 04 Dec 2007 19:52:17 +0800\n"
"From: \"Wallace\" <nobody at pacific.net.sg>\n"
"To: wallace <wallace at nowhere.com>\n"
"Subject: Test 11\n"
"Message-Id: <E1IzWJv-0000Ep-5f at 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 at 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;
}