jConnect, ASA, and BIGINT
Michael Landon
ASA 8 and 9
We have an stored function that returns a BIGINT. We tried creating a
CallableStatement to execute this method, however if we tried to retrieve
the value (using CallableStatment.getLong) an exception was thrown:
java.sql.SQLException: JZ0SG: A CallableStatement did not return as many
output parameters as the application had registered for it.
at
com.sybase.jdbc2.jdbc.ErrorMessage.raiseError(ErrorMessage.java:546)
at
com.sybase.jdbc2.jdbc.ParamManager.nextResult(ParamManager.java:707)
at
com.sybase.jdbc2.jdbc.ParamManager.getOutValueAt(ParamManager.java:482)
at
com.sybase.jdbc2.jdbc.SybCallableStatement.getLong(SybCallableStatement.java:313)
If we change the return type of the function to integer...everything works
ok.
In addition, we tried sending in a long to an sproc that had a BIGINT
parameter (using CallableStatement.setLong). As long as the number was <=
2147483647 it worked...but as soon as it ventured above an integer, an
exception was thrown.
Does jConnect not support get/set of long values to/from stored
procedures/functions?
Michael
Paul
Could you post your sample code with the sproc text? Something is not
making sense here. I would think the long should work. I would like to see
the repro to determine if there is some problem here.
pv
"Michael Landon" <mlandon@i_removeme_bnads.com> wrote in message
news:41eeb774$1@forums-1-dub...
Michael Landon
String ATEST = "{?= call DBA.sf_BIGINTTEST( ? )}";
CallableStatement cs = null;
try {
cs = c.prepareCall(ATEST);
// set timeout
cs.setQueryTimeout(this.QUERYTIMEOUT);
cs.registerOutParameter(1, java.sql.Types.BIGINT);
cs.setLong(2, 0L);
cs.executeUpdate();
// get value back...
id = cs.getLong(1);
}
finally {
try { if(cs != null) cs.close(); } catch(Exception e)
{ e.printStackTrace(); }
cs = null;
} // end try-finally
SQL:
create function
DBA.sf_BIGINTTEST(in intVal bigint)
returns integer
not deterministic
begin
declare intReturn bigint;
set intReturn=cast(2147483647 as bigint)+intVal;
return intReturn
end
Using the above code, all works OK. To make it fail, just change the sproc
to return "BIGINT" and pass in a value to the sproc > 0. The other thing to
try is to attempt sending in a value > Integer.MAX_VALUE....won't work
either.
Michael
"Paul" <Pa...@sybase.com> wrote in message news:41efec99$1@forums-2-dub...
Michael Landon
M
"Michael Landon" <mlandon@i_removeme_bnads.com> wrote in message
news:41f04b94$1@forums-2-dub...
Paul
You should go ahead and log a tech support case with Sybase to have this
looked at closer - if I get some time today or tomorrow I will check into
your repro.
pv
"Michael Landon" <mlandon@i_removeme_bnads.com> wrote in message
news:420103d0$1@forums-2-dub...
Paul
I can repro the behavior but at this point I cannot explain what is wrong.
WHen using bigint for the return then the ASA does NOT send back return
status and I am not clear exactly what it is doing. When using int, a
return status is sent back 0x7FFFFFFF, which matches with the proc.
When I look at the TDS for the parameter format, jConnect sends up EXACTLY
the same info - no change there , which is what I expected.
At this point I am thinking this is an ASA specific problem, not jConnect.
The reason it throws the SQLException is because the ASA server never sends
the parameter value back to the client - and jConnect has this parameter
registered as an output parameter - if nothing comes back I would expect
jConnect to throw this.
At this point you need to post this info to the ASA newsgroup to find out
what is wrong with the ASA OR - since you have a simple and valid repro you
might consider cutting to the chase and logging a Sybase Tech Support case
to have the ASA investigated.
PV
"Michael Landon" <mlandon@i_removeme_bnads.com> wrote in message
news:420103d0$1@forums-2-dub...