Hi,
I am attempting to use iBATIS 3 with Sybase ASE 15.
I was having issues getting both output parameters and result set(s)
back from a stored procedure. If there were no output parameters I
would get the result set back, but as soon as I added output parameters
to the stored procedure, I would get empty result sets back. The same
was true for return values and result sets. Using the JConnect driver,
I would receive an error stating that the result set was already closed.
Using the jTDS driver, I would receive an error stating that output
parameters could not be retrieved because there were still unprocessed
result sets.
After some research, I learned that the TDS data stream is structured
such that the result sets come in first, followed by the output
parameters and return values. Both drivers appear to operate in a way
that necessitates retrieving the result sets before the output
parameters and return values. Right off the bat this behavior causes a
problem with scrollable result sets, and I don't know how to fix that.
However, it should be workable with non-scrollable result sets, since
each result set is presumably going to be processed in its entirety
anyway.
I have found a solution that works for me. I understand that it is not
the best solution, but it does allow functionality where there was none
before. If anyone has a better solution or wishes to point out
consequences that I have overlooked, either of which I regard as a very
real possibility, then I would love to hear from you.
Looking through the iBATIS source (3.0.0.240), I changed this (in
CallableStatementHandler.java):
public List query(Statement statement, ResultHandler resultHandler)
throws SQLException {
CallableStatement cs = (CallableStatement) statement;
cs.execute();
resultSetHandler.handleOutputParameters(cs);
return resultSetHandler.handleResultSets(cs);
}
to this:
public List query(Statement statement, ResultHandler resultHandler)
throws SQLException {
CallableStatement cs = (CallableStatement) statement;
cs.execute();
List rs = resultSetHandler.handleResultSets(cs);
resultSetHandler.handleOutputParameters(cs);
return rs;
}
to see what effect it would have. With FORWARD_ONLY result sets, this
appears to have fixed the problem. I suspect that it introduces further
problems with scrollable result sets, and this is not an ideal solution
for that reason. However, considering that anything is better than
nothing and limiting the discussion to FORWARD_ONLY result sets, would
it be possible to modify iBATIS' behavior to handle the result sets
first in the case of FORWARD_ONLY result sets? This would allow the
retrieval of simultaneous return values, output parameters, and result
sets (restricted to FORWARD_ONLY) from stored procedures when using
iBATIS with Sybase, which is currently not possible.
Thanks,
Jeff Thiele.
