Thread: PoolingDataSource executeUpdate

Hi everybody!
First excuse me for my poor english, I'm not a native english speaker :-).

I have problems with the ConnectionPooling Implementation of the 4.3 postgresql 
jdbc driver: (pg74.214.jdbc3.jar).
I'm using the 4.3-1 postgresql server under windows 2000 with cygwin.
There is my problem:
I am trying to comunicate with this datasource from a web application(the web-
server is websphere 5.1 and we use jsp,servlets...).

First I cannot manage to configure the datasource so that the application takes 
it in account (with WSAD5.1 I configure a new datasource implemented by 
org.postgresql.jdbc3.Jdbc3PoolingDataSource and with the serverName 
dataBaseName,user,maxConnections and password properties set), I launch the 
server and during the execution I get an exception which says something like  
Driver cast exception this driver cannot be used as one phase.

So I tried another thing, instead of configuring the datasource with the 
websephere interface I configure it with javacode (I know that I may not do 
that because the code results to be less portable). So there is my code:

Jdbc3PoolingDataSource pgSource = new Jdbc3PoolingDataSource();    pgSource.setServerName("localhost");
pgSource.setDatabaseName(dbName);   pgSource.setUser(userName);    pgSource.setPassword(password);
pgSource.setMaxConnections(numberOfMaxConnections);   ds = (javax.sql.DataSource)pgSource;    
 
and to obtain a connection I do:

Connection con = ds.getConnection.

I launch the server and test it. All the select queries work well, but when I 
try to do the first update of the database:

query = new StringBuffer();
query.append("UPDATE ");
query.append(ServiceTable);
query.append(" SET ");
query.append(ServiceDescDocId);
query.append(" = ?, ");
query.append(ServiceHelpDocId);
query.append(" = ? WHERE ");
query.append(ServiceId);
query.append(" = ?");        
// Execute the query:
psmt = con.prepareStatement(query.toString());
psmt.setString(1, ddId);
psmt.setString(2, hdId);    
psmt.setString(3, svcId);
numUpdates = psmt.executeUpdate();

What happens there is that with Connection Pooling the executeUpdate method 
always returns 0, and that the database is not upgraded.
I'm currently forced to use the Jdbc3SimpleDataSource class (with this class 
the application runs perfectly) but I would like to use Connection Pools to 
improve the application's performances.

Thanks for having read my mail until the end :)              

ALBERDI Ion wrote:

> What happens there is that with Connection Pooling the executeUpdate method
> always returns 0, and that the database is not upgraded.
> I'm currently forced to use the Jdbc3SimpleDataSource class (with this class
> the application runs perfectly) but I would like to use Connection Pools to
> improve the application's performances.

I notice that the default autocommit setting in
org.postgresql.jdbc2.optional.ConnectionPool is false. This is the
opposite of the required Connection default and seems like a bug to me.

If your application is expecting connections obtained from the
datasource to have autocommit on by default, this could be the problem.

-O

On Thu, 24 Jun 2004, Oliver Jowett wrote:

> ALBERDI Ion wrote:
>
> > What happens there is that with Connection Pooling the executeUpdate method
> > always returns 0, and that the database is not upgraded.
> > I'm currently forced to use the Jdbc3SimpleDataSource class (with this class
> > the application runs perfectly) but I would like to use Connection Pools to
> > improve the application's performances.
>
> I notice that the default autocommit setting in
> org.postgresql.jdbc2.optional.ConnectionPool is false. This is the
> opposite of the required Connection default and seems like a bug to me.

True, but that doesn't explain why executeUpdate returns an affected row
count of zero.

Kris Jurka

Kris Jurka wrote:
>
> On Thu, 24 Jun 2004, Oliver Jowett wrote:
>
>
>>ALBERDI Ion wrote:
>>
>>
>>>What happens there is that with Connection Pooling the executeUpdate method
>>>always returns 0, and that the database is not upgraded.
>>>I'm currently forced to use the Jdbc3SimpleDataSource class (with this class
>>>the application runs perfectly) but I would like to use Connection Pools to
>>>improve the application's performances.
>>
>>I notice that the default autocommit setting in
>>org.postgresql.jdbc2.optional.ConnectionPool is false. This is the
>>opposite of the required Connection default and seems like a bug to me.
>
>
> True, but that doesn't explain why executeUpdate returns an affected row
> count of zero.

I was thinking along the lines of an insert on a separate connection not
being committed, or the insert not being visible to the update's
transaction (which will be a very long transaction if the app is
expecting autocommit..). i.e. executeUpdate() is fine, it's just that
the update sees a different set of data to what is expected by the app.

-O