I expend a lot of time trying to retrieve data from a Stored Procedure, here is the code
CREATE PROCEDURE aprocedure(
IN idin CHAR,
OUT returnvalue CHAR)
AS:
SET returnvalue=
(SELECT something
FROM sometable
WHERE id=idin)
I could create it, with no problems, but when I tried to call it like this:
call someprocedure('theid', ?)
Error -313 kept poping out, I did my homework and check the web, IBM forums were no help at all, I couldnt find any documentation, specifications, or anything that make this more clear, also SQL error code -313 means that the number of parameters in the procedure does not match the number of parameters you're using when you call it. So, after too much research, I started thinking that DB2 with JDBC driver and or SQuirreL have trouble when returning OUT values, (I also installed a DB2 CTL client, created a local database, created a table, created the procedure, I called, and everything worked nicely) so I change my code to this (to use a Result Set instead of an OUT):
CREATE PROCEDURE someprocedure(IN idin CHAR (22))
DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT something FROM sometable WHERE id=idin;
OPEN cursor1;
END P1
aaaaaaaaaaaand NOTHING, SQuirreL gave me some error codes, when trying to create it, so... I enter that same code in Aqua Data Studio 4.7, and worked like a charm, I call the procedure from Aqua Data like this:
call someprocedure('theid');
and it returned what was supposed to return, I tried that same sentence with SQuirreL... and it WORKED too !!
Im sure that my sintaxys was correct all along, even with the OUT type of return, so, my question, finally is this.
Does SQuirreL check the input you enter before passing it to the JDBC? Also Where do I can find how exactly DB2 is altering SQL code?? because we all know that all DBM change the SQL a bit, but MySQL have great documentation... and i honestly couldnt find any good one on DB2, also im talking about "pure" SQL since in DB2 you can enter stored procedures in C , Java etc...
