asked on

SQL0469 on DB2 stored procedure CALL

[IBM iSeries running AS400]

I created the following stored procedure:

=======
CREATE PROCEDURE TBLNAME.proc_test (IN p_custnum VARCHAR(8),
IN p_zipcode CHAR(5),
OUT r_valid CHAR(1),
OUT r_bal DECIMAL(9,2))
LANGUAGE SQL
BEGIN
[etc..]
=======

The procedure has been created successfuly. ("Statement ran successfully")

Now to give it a try, I use the CALL statement. I found out that if you don't call the stored procedure with exactly the same number as parameters (including the OUT parameters), it won't work. So what I usually do is to call the procedure using NULL as parameter for the OUT parameters. But on this one, it just won't work:

CALL TBLNAME.proc_test('12345678','12345',NULL,NULL)

[SQL0469] IN, OUT, or INOUT not valid for parameter 3 in procedure PROC_TEST in IVRCIS. Cause . . . . . : The IN, INOUT, or OUT attribute specified for parameter 3 on the DECLARE PROCEDURE or CREATE PROCEDURE statement is not valid. The parameter name is R_VALID. One of the following errors occurred: -- The attribute is not consistent with the parameter on the CALL statement. If the parameter was declared INOUT or OUT, the parameter on the CALL statement must be specified as a host variable. -- The attribute was specified as INOUT or OUT and REXX was specified as the language. The attribute must be IN if REXX is specified. -- A parameter in an SQL procedure is declared as OUT and is used as input in the routine body or is declared as IN and is modified in the routine body. -- A parameter in an SQL function is modified in the routine body. Recovery . . . : Either change the attribute of the parameter on the DECLARE PROCEDURE or CREATE PROCEDURE statement or change the parameter. Do not modify parameters in an SQL function. Try the request again.

I tried pretty much all combinations of call, including:
CALL TBLNAME.proc_test('12345678','12345',NULL,NULL)
CALL TBLNAME.proc_test('12345678','12345','a',1)
CALL TBLNAME.proc_test('12345678','12345',?,?)
CALL TBLNAME.proc_test('12345678','12345')

and I am not able to run the stored procedure...

Anyone can help ?
I'm using the "Run SQL Scripts" GUI from iSeries Navigator v5R2.

Thanks !!

sachinwadhwa

for OUT parameters, you must specify host variables (which will have data returned from stored procedure, once it ran sucessfully)

*******************

IN parameter
The argument must be assignable to the parameter.
The assignment of a string argument uses the storage assignment rules.

OUT parameter
The argument must be a single variable or parameter marker (SQLSTATE 42886).
The argument must be assignable to the parameter.
The assignment of a string argument uses the retrieval assignment rules.

INOUT parameter
The argument must be a single variable or parameter marker (SQLSTATE 42886).
The argument must be assignable to the parameter.
The assignment of a string argument uses the storage assignment rules on invocation and the retrieval assignment rules on return.

ghp7000

try CALL TBLNAME.proc_test('12345678','12345',?,?)

Member_2_276102

Also, the parm is defined as CHAR(). Make sure that you aren't attempting to use a VARCHAR instead.

Tom

nauliv

ASKER

sachinwadhwa: can you please post an example of how to test the stored procedure from "Run SQL Script" using the host variables you mentionned ?

nauliv

ASKER

ghp7000: as mentionned in my post, I already tried to put a questionmark for the OUT parameters, and it doesn't work.

I get the following error:

> CALL TBLNAME.proc_test('12345678','12345',?,?)
The number of parameter values set or registered does not match the number of parameters.

nauliv

ASKER

tliotta: are you saying i need to make sure i am not using varchar for R_VALID, inside the code of the stored proc ?

Here is actually the only portion of code inside the stored procedure that handles r_valid:

IF v_id = 0 THEN
SET r_valid = 'n';
ELSE
SET r_valid = 'y';
END IF';

Based on your comment, I changed this portion of code to:

IF v_id = 0 THEN
SET r_valid = CAST('n' AS CHAR(1));
ELSE
SET r_valid = CAST('y' AS CHAR(1));
END IF';

But I get the same error message:

[SQL0469] IN, OUT, or INOUT not valid for parameter 3 in procedure PROC_TEST in IVRCIS. Cause . . . . . : The IN, INOUT, or OUT attribute specified for parameter 3 on the DECLARE PROCEDURE or CREATE PROCEDURE statement is not valid. The parameter name is R_VALID [bla...bla..bla...]

There must be a way to CALL this stored procedure ??!!!! [frustrated mode ON]

nauliv

ASKER

All:

I tried to remove the R_VALID parameter, and run it again with parameters '12345678','12345',NULL
to see if the problem was within the code with R_VALID, but now i'm getting the same problem with R_BAL...

Also, I need to mention that I have another stored procedure in there, with only one parameter, which is an OUT.
proc_test_this(OUT v_total INTEGER)

And it works fine when I call it with proc_test_this(NULL):

>CALL DBNAME.proc_test_this(NULL)
Output Parameter V_TOTAL = 1234

Any ideas ?

Member_2_276102

nauliv:

You can call the stored proc, but almost certainly only from a compiled program. The 'parameter markers' are used when the statement is being PREPAREd. Once PREPAREd, the statement can be executed while supplying host variables that supply or receive values to/from the marked parameters.

The compiled program might use embedded SQL to execute the CALL or it might use SQL CLI or some other means. However it's done, you will need to declare the variable types and sizes to match what the stored proc expects. Even for basic IN-only parms, VARCHAR and CHAR can and generally does fail. The most common way to fail in that case is simply that the signature doesn't match, so the stored proc can't even be located by the server. (Signature is based on name plus variable definitions. Multiple stored procs of the same name can co-exist if parameter lists differ -- i.e., over-loading is allowed.)

You can call the stored proc. You just can't call it by way of a dynamic CALL statement; not with parms that require two-way transfer of parm values.

Tom

nauliv

ASKER

Tom,

Thanks for your response. What method do you suggest to use to test the stored procedure ? (use various input parameters, and look at what is being returned).

Thanks!

Member_2_276102

nauliv:

(Lots of guesswork since I have no knowledge of client internals except ones I've written myself.)

INTEGER and VARCHAR seem to be easy enough for various clients to handle. But CHAR and apparently DECIMAL look to be trouble. An INTEGER has a commonly known definition. And VARCHAR has a length attribute that can go along with the data.

But CHAR has an absolute length that the client needs to know in order to set memory aside. Likewise with DECIMAL. With a VARCHAR, a variable length field can be declared for a maximum length in the client's memory space or I suppose it can be allocated dynamically when the length attribute appears. And INTEGER will always use 4 bytes. AFAIK, there is no similar length attribute that's available for CHAR or DECIMAL. You're responsible for ensuring that memory is allocated correctly and then for matching the memory to the parms.

I might write a temporary stored proc that simply called the proc I wanted to test -- i.e., a proxy. It could do any needed CASTs or conversion and relay values in a form that was useful, perhaps as VARCHARs for example. That might be the easiest. It ought to avoid most of these problems.

Can you supply the code for proc proc_test_this()? I should mention that a trivial proc with an OUT INTEGER returns SQL0469 in STRSQL here. But my test was on V5R1. You're running in iNav Run SQL Scripts -- is that iNav V5R2 or are you connecting to a V5R2 system? We have systems up through V5R4, but I need to make some authority changes before I test the same way you're running.

Tom

nauliv

ASKER

The procedure that works fine is this one:

CREATE PROCEDURE
PROC_TEST_THIS (OUT v_total INTEGER)
LANGUAGE SQL
BEGIN
SELECT COUNT(*)
INTO v_total
FROM CUST;
END

If I run (within "Run SQL Script" tool):

CALL PROC_TEST_THIS(NULL), I get:
Output Parameter V_TOTAL = 1234

No error message.....

nauliv

ASKER

Ok, I got confirmation from IBM techsupport. It is simply IMPOSSIBLE to run such a stored procedure from within an interactive SQL client. It has to be called from an external program. On the iSeries AS/400, a possibility is Java:

import java.sql.*;

public class hostStatusCall
{
public static void main(String[] args) throws SQLException
{
Connection con = null;
String url = "jdbc:as400://192.168.1.2";

DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
System.out.println ("Connecting to AS/400...");
con = DriverManager.getConnection (url, "login", "password");

// con.close();

}
}

I think we should leave this ticket public, it might help other users.
Thanks for your participation everyone.

ASKER CERTIFIED SOLUTION

Member_2_276102

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nauliv

ASKER

Then here are some points for you, so it won't get deleted :)

Member_2_276102

Appreciated. Wish I could've said something more than "I don't think it can be done outside a program."

Tom

bharath tata

Hi,

I'm facing the same problem,i'm not able to call the stored procedure with an output.when i declare the same stored procedure with no output parameters it works.

Will it work when i call the same procedure from .net through ODBC connection..?

Any help is appreciated.

Thank you