I am using db2 V7.1 fixpack 10 on a win XP system with 1GB of memory and a 2.6GHz processor.

I have created a (rather complex) stored procedure using a few helper classes (the stored procedure class itself is nothing more than a wrapper around one of those helper classes). I have tested the helper classes from a standalone java app and all works fine. Also when testing the stored procedure from db2 stored procedure builder everything was ok.
But when I ran the stored procedure from a script using a call statement I get some errors I don't understand:
*) SQL0313N The number of host variables in the EXECUTE or OPEN statement is not equal to the number of input values required. SQLSTATE=07001
As I said the code works from a standalone app and I am absolutely sure all required host variables (and none more) are specified for the statement the database is complaining about
*) SQL0519N The PREPARE statement identifies the SELECT or VALUES statement of the open cursor "SQLCUR381". SQLSTATE=24506
In a few cases the error was correct: I had forgotten to close certain recordsets or statement in my code.
But in certain other cases I get the impression that I am not allowed to have more than one cursor open on a certain table. I have for example the following scenario: I have a resultset I traverse on table X and within that loop I have to acces table X again (the table contains a self reference - a record could contain a 'pointer' to a previous version of that record in the same table X).

To cut a long story short, here are my questions:
*) what is the difference between running a stored procedure from 'stored procedure builder' or from a script. Is the jvm environment different in both situations?
*) are there certain limitations on nesting statements in java stored procedure code, that is can I loop through a resultset and execute other queries (even on the same table as in the outer loop) within that loop?

Any help would be really appreciated, because I'm going berserk on this.

Thanks a lot.

Luc Feys

Hi,

Can you show me the SP code?

Abel.

Like I said the code is quite complex (at least for a stored procedure) and the errors are sometimes related to a completely different line of code than the line mentioned in the db2 error logs (since the errors are caused by a cursor that remains open while db2 thinks it shouldn't). I will take some excerpts, leave out the code that has no effect on the errors and try to specify what exactly happens. I hope this helps.

BTW I am using jdk 1.1.8

=== Example 1 ===
First I will show some code in which I was able to correct an SQL0519N error (but don't really understand why there was an error in the first place). In the code you see a recursive procedure. I had to split the loop over the "dossierstukkenPreviousResultSet" so I did not call the procedure "doorzoekTakkenVanafDossierstuk" while that ResultSet was open.

private void doorzoekTakkenVanafDossierstuk(int aDossierstukId, int aActiviteitId, Date aToestandDatum, Beslissing aBeslissing, Connection aConnection) throws SQLException {
//...
//a lot of code
//...
sql =
"SELECT DOSSIERSTUK_ID" +
" FROM VERGUN.DOSSIERSTUK AS DOSSIERSTUK " +
" WHERE PREV_DSTUK_ID = " + aDossierstukId;
PreparedStatement dossierstukkenPreviousStmt = aConnection.prepareStatement(sql);
ResultSet dossierstukkenPreviousResultSet = dossierstukkenPreviousStmt.executeQuery();
Vector ids = new Vector();
while (dossierstukkenPreviousResultSet.next()) {
ids.addElement(new Integer(dossierstukkenPreviousResultSet.getInt("DO SSIERSTUK_ID")));
}
dossierstukkenPreviousResultSet.close();
dossierstukkenPreviousStmt.close();
Enumeration idEnumerator = ids.elements();
while (idEnumerator.hasMoreElements()) {
int id = ((Integer)idEnumerator.nextElement()).intValue();
doorzoekTakkenVanafDossierstuk(id, aActiviteitId, aToestandDatum, aBeslissing, aConnection);
}
}

=== Example 2 ===
For this one I found a workaround too, but not a satisfying one. Here the error occurs on one of the lines marked with "-->". If I close the statement "activiteitStmt" (marked by the other "-->"), the SQL0519N error does not occur. The problem is that I am using the prepared stament "activiteitStmt" in the code I commented out, so closing it is not
really a valid option (otherwise it was of no use to prepare the statement in the first place).

private void doorzoekTakkenVanafDossierstuk(int aDossierstukId, int aActiviteitId, Date aToestandDatum, Beslissing aBeslissing, Connection aConnection) throws SQLException {
//...
//Checking aConnection parameter
//...

String sql =
"SELECT TYPE_DOSSIERSTUK.DOSSIERSTUK_D_S, " +
" DOSSIERSTUK.BESLISSING_RVS, " +
" DOSSIERSTUK.DOSSIERSTUK_D, DOSSIERSTUK.BESLUIT_D" +
", DOSSIERSTUK_ACT.VERGUND_S, DOSSIERSTUK_ACT.VERGUND_VAN_D, DOSSIERSTUK_ACT.VERGUND_TOT_D" +
", DOSSIERSTUK_ACT.GEWEIGERD_S, DOSSIERSTUK_ACT.GEWEIGERD_D" +
", DOSSIERSTUK_ACT.INGETROKKEN_S, DOSSIERSTUK_ACT.INGETROKKEN_D" +
", DOSSIERSTUK_ACT.STOPGEZET_S, DOSSIERSTUK_ACT.STOPGEZET_D" +
", DOSSIERSTUK_ACT.OPGEHEVEN_S, DOSSIERSTUK_ACT.OPGEHEVEN_D" +
" FROM VERGUN.DOSSIERSTUK AS DOSSIERSTUK" +
", VERGUN.DOSSIERSTUK_ACT AS DOSSIERSTUK_ACT" +
", VERGUN.TYPE_DOSSIERSTUK AS TYPE_DOSSIERSTUK " +
" WHERE DOSSIERSTUK.DOSSIERSTUK_ID = ?" +
" AND DOSSIERSTUK_ACT.DOSSIERSTUK_ID = DOSSIERSTUK.DOSSIERSTUK_ID " +
" AND DOSSIERSTUK_ACT.TYPE_ACTIVITEIT_ID = " + aActiviteitId +
" AND TYPE_DOSSIERSTUK.TYPE_DOSS_STUK_ID = DOSSIERSTUK.TYPE_DOSS_STUK_ID ";

PreparedStatement activiteitStmt = aConnection.prepareStatement(sql);
activiteitStmt.setInt(1, aDossierstukId);
ResultSet activiteitResultSet = activiteitStmt.executeQuery();
if (!activiteitResultSet.next()) {
//Als dit dossierstuk een RVS is en het vorig besluit wordt vernietigd, moeten we het besluit van twee dossierstukken eerder overnemen
activiteitResultSet.close();
--> activiteitStmt.close(); <-- The SQL0519N error disappears if I add this line, but this is not a very satisfying solution
sql =
"SELECT BESLISSING_RVS" +
" FROM VERGUN.DOSSIERSTUK" +
" WHERE DOSSIERSTUK_ID = " + aDossierstukId;
Statement beslissingRvsStmt = aConnection.createStatement();
--> ResultSet beslissingRvsResultSet = beslissingRvsStmt.executeQuery(sql); <-- This is where the SQL0519N error occurs
beslissingRvsResultSet.next();
String beslissingRvs = beslissingRvsResultSet.getString("BESLISSING_RVS") ;
beslissingRvsResultSet.close();
beslissingRvsStmt.close();
//...
//Code which can be commented out while still getting te same error message on the same line
//...
}
activiteitResultSet.close();
activiteitStmt.close();

//...
//The code from example 1
//...
} // End of method


So, this is the story so far. I am sure I will be able to find a workaround for every problem I am confronted with here, but I do not like to work this way. I prefer to know what I am doing (and what I am not supposed to do) instead of using this "trial and error" way of programming.

So if anyone would have any suggestions or be aware of any stored procedure rules I might be breaking here, please let me know.
Thanks a lot.

Luc Feys

I'm finally out of it. I was using two objects (O1 and O2), each retrieving its own connection object through the DriverManager (with url "jdbc:default:connection").
Since all calls to O2 were coming from O1, I passed the connection of O1 to O2 instead of having O2 create its own connection. And gone were all the mysterious errors.

Woohoo.