I'm going nuts about how the Sybase JDBC driver handles stored procedures with mixed IN
and OUT
parameters. Check out this simple stored procedure:
CREATE OR REPLACE PROCEDURE p (IN i1 INT, OUT o1 INT, IN i2 INT, OUT o2 INT)
BEGIN
set o1 = i1;
set o2 = i2;
END
And here's how I'd call it with JDBC:
CallableStatement c = connection.prepareCall("{ call dba.p(?, ?, ?, ?) }");
c.setInt(1, 1);
c.setInt(3, 2);
c.registerOutParameter(2, Types.INTEGER);
c.registerOutParameter(4, Types.INTEGER);
c.execute();
System.out.println(c.getObject(2));
System.out.println(c.getObject(4));
But this results in
1
null
What's going on?? Is that a really wicked bug in the JDBC driver or am I completely missing something? By trial and error, I found this to be a way how it works:
c.setInt(1, 1);
c.setInt(2, 2);
c.registerOutParameter(3, Types.INTEGER);
c.registerOutParameter(4, Types.INTEGER);
c.execute();
System.out.println(c.getObject(3));
System.out.println(c.getObject(4));
Now the result is
1
2
Does the JDBC driver secretly re-order IN
and OUT
parameters??
I'm using SQL Anywhere 12 and jconn3.jar
Looks like a bug in the driver.
I suspect the buggy driver expects parameters to be passed/registered in the order (i.e. 1,2,3,4). When you do registerOut(2), the statement apparently forgets you did set(3) :-)
Or, may be, all OUT should be done after all IN. Then again, this is a bug in the driver.
UPDATE
Wait, you didn't change the procedure for the second variant? That result doesn't make any sense. Unless, as you said, driver does reordering. Which is unusual, to say the least.
UPDATE 2
I have decompiled the driver. It does some pretty funny games around out parameters, and with all this joggling I feel they have a fair potential for a bug there, but so far I do not see it plainly.
The only funny thing I noticed is that apparently if the parameter at position n is not out, the driver will scan parameters forward until it will find the value; if value is not found, it goes to the next row:
s.registerOutParameter(5,Types.INT);
...
// no out value at position 4, it will go to 5 and get the value
rs.getInteger(4);
UPDATE 3
It may be interesting to see the output of all 4 parameters in example 1, i.e.:
CallableStatement c = connection.prepareCall("{ call dba.p(?, ?, ?, ?) }");
c.setInt(1, 1);
c.setInt(3, 2);
c.registerOutParameter(2, Types.INTEGER);
c.registerOutParameter(4, Types.INTEGER);
c.execute();
System.out.println(c.getObject(1));
System.out.println(c.getObject(2));
System.out.println(c.getObject(3));
System.out.println(c.getObject(4));