Sybase IN and OUT parameters

Lukas Eder picture Lukas Eder · Apr 29, 2011 · Viewed 11.9k times · Source

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

Answer

Vladimir Dyuzhev picture Vladimir Dyuzhev · May 10, 2011

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));