Thread: ResultSet with more than 5 rows causes error

I'm using jdbc4 driver with postgresql 8.1, java sdk 6
and netbeans 5.5
The task to perform is to copy rows from one table to
another (tables with same structure) in a distinct
database, using stored procedure.
This code works fine when the ResultSet has max 5
rows. If it has 6 rows, when calling getObject(int
index) an error occurs.
The error seems to be PSQLException, telling that the
ResultSet is bad positioned and an hit could be
calling next(). But it's not
clear: what I have caught is a NullPointerException


public void upload(Connection localConnection,
Connection remoteConnection) throws SQLException{



ResultSet source;
ResultSetMetaData md;
SQLWarning warning;
Object data;
int colCount;
try {
if (localConnection != null && remoteConnection !=
null && !localConnection.isClosed() &&
!remoteConnection.isClosed()) {
remoteConnection.setAutoCommit(false);
CallableStatement selectCall =
localConnection.prepareCall(Constant.FUNCTION_SELECT);

CallableStatement insertCall =
remoteConnection.prepareCall(Constant.FUNCTION_INSERT);

source = selectCall.executeQuery();
md = source.getMetaData();
colCount = md.getColumnCount();
while(source.next()) {
insertCall.registerOutParameter(1, Types.INTEGER);
for (int i = 1; i <= colCount; i++) {
data = source.getObject(i);
insertCall.setObject(i+1, data);
}
insertCall.execute();
warning = insertCall.getWarnings();
insertCall.clearParameters();
}
source.close();
selectCall.close();
insertCall.close();
remoteConnection.commit();
remoteConnection.close();
}
} catch (PSQLException ex) {
remoteConnection.rollback();
remoteConnection.close();
}
catch (SQLException ex) {
remoteConnection.rollback();
remoteConnection.close();
}
catch (Exception ex) {
remoteConnection.rollback();
remoteConnection.close();
}

}

Abybody can help me to find an answer?

Thanks in advance


      ___________________________________
L'email della prossima generazione? Puoi averla con la nuova Yahoo! Mail: http://it.docs.yahoo.com/nowyoucan.html

----- Original Message -----
From: "Giuseppe Sacco" <giuseppe@eppesuigoccas.homedns.org>
To: "Ludovico Bianchini" <metlud@yahoo.it>
Cc: <pgsql-jdbc@postgresql.org>
Sent: Thursday, September 27, 2007 6:27 PM
Subject: Re: [JDBC] ResultSet with more than 5 rows causes error


> Il giorno gio, 27/09/2007 alle 12.20 +0200, Ludovico Bianchini ha
> scritto:
> [...]
>> while(source.next()) {
>> insertCall.registerOutParameter(1, Types.INTEGER);
>> for (int i = 1; i <= colCount; i++) {
>> data = source.getObject(i);
>> insertCall.setObject(i+1, data);
>> }
>> insertCall.execute();
> [...]
>
> I never used CallableStatement, but I do think you should use
> insertCall.setObject(i,data)
> instead of
> insertCall.setObject(i+1,data)
>
>

The stored procedure returns an integer parameter, which must be registered
with

insertCall.registerOutParameter(1, Types.INTEGER);

In a first attempt I wrote

insertCall.setObject(i,data);

but an error happened: the last parameter was not set. So: if there is a
return value, the arguments index starts from 2.
Really it's work fine for first 5 rows.

With this modification (create a new CallableStatement for every row)

[..]
insertCall.execute();
insertCall.close();
insertCall = remoteConnection.prepareCall(Costanti.FUNCTION_INSERT);
[..]

 the code works, but I think the problem has to be solve deeply.

Chiacchiera con i tuoi amici in tempo reale!
 http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com

Il giorno gio, 27/09/2007 alle 12.20 +0200, Ludovico Bianchini ha
scritto:
[...]
> while(source.next()) {
> insertCall.registerOutParameter(1, Types.INTEGER);
> for (int i = 1; i <= colCount; i++) {
> data = source.getObject(i);
> insertCall.setObject(i+1, data);
> }
> insertCall.execute();
[...]

I never used CallableStatement, but I do think you should use
    insertCall.setObject(i,data)
instead of
    insertCall.setObject(i+1,data)

Il giorno gio, 27/09/2007 alle 18.45 +0200, Ludovico Bianchini ha
scritto:
[...]
> The stored procedure returns an integer parameter, which must be registered
> with
>
> insertCall.registerOutParameter(1, Types.INTEGER);
>
> In a first attempt I wrote
>
> insertCall.setObject(i,data);
>
> but an error happened: the last parameter was not set. So: if there is a
> return value, the arguments index starts from 2.

I think OUT parameters are not related to the setObject(...) calls.
These parameters are for the resultset associated the execute(...)
method.

Try again as you did earlier, it should work. If it doesn't, then please
post that error message.

On Thu, 27 Sep 2007, Ludovico Bianchini wrote:

> Sorry, the error does not seem to be in the ResultSet but instead in the
> Callable Statement, method execute().
>
> java.lang.NullPointerException
>        at
> org.postgresql.core.v3.SimpleParameterList.getV3Length(SimpleParameterList.java:228)
>        at
> org.postgresql.core.v3.QueryExecutorImpl.sendBind(QueryExecutorImpl.java:807)

It is likely failing at execution number 6 because that's the point where
the driver switches over to using server prepared statements instead of
executing things on the fly.  I don't have time to take a look at this
now, but it's likely a driver problem.  Hopefully I'll be able to look at
this on Sunday/Monday.

Kris Jurka

Kris Jurka wrote:
>
>
> On Thu, 27 Sep 2007, Ludovico Bianchini wrote:
>
>> Sorry, the error does not seem to be in the ResultSet but instead in
>> the Callable Statement, method execute().
>>
>> java.lang.NullPointerException
>>        at
>> org.postgresql.core.v3.SimpleParameterList.getV3Length(SimpleParameterList.java:228)
>>
>>        at
>> org.postgresql.core.v3.QueryExecutorImpl.sendBind(QueryExecutorImpl.java:807)
>>
>
> It is likely failing at execution number 6 because that's the point
> where the driver switches over to using server prepared statements
> instead of executing things on the fly.  I don't have time to take a
> look at this now, but it's likely a driver problem.  Hopefully I'll be
> able to look at this on Sunday/Monday.

If this is the case, couldn't he use "?prepareThreshold=0" at the end of
his connect URL as a temporary workaround?  It will be slower, but it
would unblock him.

Regards,
Eric Faulhaber

On Thu, 27 Sep 2007, Eric Faulhaber wrote:

> If this is the case, couldn't he use "?prepareThreshold=0" at the end of his
> connect URL as a temporary workaround?  It will be slower, but it would
> unblock him.
>

Yes, but it's pretty much equivalent to his current workaround of creating
a new CallableStatement object inside the loop and that doesn't affect
other queries.

Kris Jurka

On Thu, 27 Sep 2007, Ludovico Bianchini wrote:

> Sorry, the error does not seem to be in the ResultSet but instead in the
> Callable Statement, method execute().
>
> java.lang.NullPointerException
>        at
> org.postgresql.core.v3.SimpleParameterList.getV3Length(SimpleParameterList.java:228)
>        at
> org.postgresql.core.v3.QueryExecutorImpl.sendBind(QueryExecutorImpl.java:807)
>        at

I've managed to reproduce this problem with the attached test case.
At fault are the ill conceived side effects in
org.postgresql.core.v3.SimpleParameterList#getTypeOID.  An accessor
like getTypeOID shouldn't be responsible for setting a value for the
out parameter.  getTypeOID is called from QueryExecutorImpl's
sendParse method, which obviously isn't called when we reuse a server
side prepared statement and don't need to reparse.  This leaves an
uninitialized value in the paramValues array which we die on later.

How can overriding things in getTypeOID be wise without doing the
same overriding in getTypeOIDs?  In any case that all seems like the
wrong approach.  It would be ideal to be able to handle all of this in
registerOutParameter, but that is tricky for INOUT parameters which
can come from two places in either order.  What about putting all the
oid/value hacking into checkAllParametersSet which we can be sure will
be called?

It won't get called for describe only executions, which doing a
little testing doesn't seem to work for CallableStatements in the
first place.  Maybe for clarity a separate method should be added to
SimpleParameterList to do the OUT parameter mashing.

Dave, your thoughts?

Kris Jurka

On Mon, 1 Oct 2007, Kris Jurka wrote:

> I've managed to reproduce this problem with the attached test case.
> At fault are the ill conceived side effects in
> org.postgresql.core.v3.SimpleParameterList#getTypeOID.  An accessor
> like getTypeOID shouldn't be responsible for setting a value for the
> out parameter.  getTypeOID is called from QueryExecutorImpl's
> sendParse method, which obviously isn't called when we reuse a server
> side prepared statement and don't need to reparse.  This leaves an
> uninitialized value in the paramValues array which we die on later.
>

I've applied a fix for this in CVS to 8.1, 8.2 and 8.3dev and it will be
included in the next release.

Kris Jurka