postgres wont do preparedstatements

[Jan-Henrik Haukeland]

Okay, thanks we'll look into it.

A couple of comments to http://git.dbmail.eu/?p=paul/dbmail;a=shortlog;h=zdb 
.

URL_free must be called, unless you do not care about this memory leak  
since the app will be closed shortly. I'v updated the "constructor"  
and "desctructor" methods below.

- You are basically using one Connection which is never returned to  
the pool. I assume this is for testing, since this defer the purpose  
of a pool, also if you run in a single threaded application. Even if  
you only use one connection, returning it to the pool after use  
ensures that the connection does not go stale. If the connection has  
errors the pool will remove it if you run with a reaper thread, which  
is highly recommended. And each time you get a connection from the  
pool you are guaranteed that the connection is alive and connected to  
the database as the pool runs a ping test before it dishes out a  
connection.

- Apropos reaper thread. if your application is single threaded and  
not thread-safe nor reentrant, running a reaper thread in the pool is  
still completely safe since the pool itself is thread-safe and the  
reaper-thread will not affect your application at all. If dbmail will  
use tcp database connections, i.e. mysql or postgres then using a  
reaper thread is strongly recommended.

- Having Connection Pool as a global variable is okay, but Connection  
and ResultSet!?, but I assume this is also for testing.

- We could add a Connection_vexecuteQuery(T C, const char *sql,  
va_list ap) if you want to save those snprintf() calls to build the  
query and instead call executeQuery directly in the facade function  
db_query(), or you could skip the facade and call  
Connection_executeQuery() directly.

- If you compiled libzdb with exception (highly recommended) you need  
to enclose calls that can throw an exception, (basically all methods  
in Connection, PreparedStatement and ResultSet), in a try-catch  
statement. Below is an example of a mini-framework we use with libzdb  
and where we isolate database call and exception testing to a Service  
function, which may be helpful to take a look at.


int db_connect(void)
{
         URL_T url = URL_create("%s://%s:%u/%s?user=%s&password=%s",
         	_db_params->driver, _db_params->host, _db_params->port,
                 _db_params->db, _db_params->user, _db_params->pass);
         if (! (pool = ConnectionPool_new(url))) {
                 if (url) Url_free(&url);
                 TRACE(TRACE_FATAL,"error creating connection pool");
                 return -1;
         }

         ConnectionPool_start(pool);
         TRACE(TRACE_DEBUG, "connection pool started with [%d]  
connections",
                 ConnectionPool_getInitialConnections(pool));
	// Humm
         if (! (conn = ConnectionPool_getConnection(pool))) {
                 TRACE(TRACE_FATAL, "error getting connection from the  
pool");
                 return -1;
         }

         return 0;
}

int db_disconnect(void)
{
         URL_T url = ConnectionPool_getURL(pool);
         ConnectionPool_stop(pool);
         ConnectionPool_free(&pool);
         URL_free(&url);
         return 0;
}




// Mini framework dispatcher example

struct T {
         Connection_T con;
         void (*service)(T t);
        ...
};

// Dispatch database functions
static void dispatcher(...) {
         struct T t = {.service = list};
         if (...) {
                 t.service = create;
         } else if (...) {
                 t.service = update;
         } else if (...) {
                 t.service = delete;
         }
         Service_do(&t);
}
...
static void update(T t) {
         int i;
         params_t params = t->model;
         PreparedStatement_T p = Connection_prepareStatement(t->con,  
SQL_UPDATE);
         for (i = 0; params[i].name; i++)
                 PreparedStatement_setString(p, i, params[i].value);
         PreparedStatement_execute(p);
}

...
void Service_do(T t) {
         assert(t->service);
         if (! (t->con=  
ConnectionPool_getConnection(Connection_Pool))) {
                 sendError(t, SC_INTERNAL_SERVER_ERROR,
                         "SQLException: Connection not available\n");
                 return;
         }
         TRY
         {
                 Connection_beginTransaction(t->con);
                 t->service(t);
                 Connection_commit(t->con);
         }
         CATCH(SQLExeption)
         {
                 sendError(t, SC_INTERNAL_SERVER_ERROR,
			"SQLException: %s\n", Connection_getLastError(t->con));
                 Connection_rollback(t->con);
         }
         FINALLY
         {
                 Connection_close(t->con);
         }
         END_TRY;
}




On 24. feb.. 2008, at 22.21, Paul J Stevens wrote:

> Got it, sort of!
>
> Looks like it *does* work as long as the preparedstatement is not the
> very first query sent. As long as a Connection_executeQuery is sent
> first everything is peachy.
>
> Smells like a small initialization bug in libzdb. But you guys are
> better suited to find out.
>
>
>
>
> Paul J Stevens wrote:
>> I've tried both with ZBDEBUG=TRUE and of course with getLastError,  
>> but
>> both tell me nothing.
>>
>> And I didn't miss the getConnection part. Normal queries using
>> executeQuery run just fine, as do prepared statements with mysql.
>>
>> I'm attaching my testzdb.c which doesnt work.
>>
>> My git tree with zdb related work is visible at:
>>
>> http://git.dbmail.eu/?p=paul/dbmail;a=shortlog;h=zdb
>>
>> the calls that talk to zdb are all in dm_db.c starting around like  
>> 150.
>>
>>
>> thanks,
>>
>>
>>
>> Jan-Henrik Haukeland wrote:
>>> Ps. Also run with the debug flag set, ZBDEBUG = TRUE; Not that we
>>> output much debug info, but you may be lucky. Since postgres work  
>>> with
>>> pool.c this sounds strange, though pool.c of course may not test all
>>> cases. Please double check how you call libzdb if you have not  
>>> already
>>> done so first.
>>>
>>> Maybe you have a dbmail repository we can look at for the
>>> implementation?