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 !!
LVL 7
naulivAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sachinwadhwaCommented:
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.
0
ghp7000Commented:
try CALL TBLNAME.proc_test('12345678','12345',?,?)
0
tliottaCommented:
Also, the parm is defined as CHAR(). Make sure that you aren't attempting to use a VARCHAR instead.

Tom
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

naulivAuthor Commented:
sachinwadhwa: can you please post an example of how to test the stored procedure from "Run SQL Script" using the host variables you mentionned ?
0
naulivAuthor Commented:
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.
0
naulivAuthor Commented:
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]
0
naulivAuthor Commented:
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 ?
0
tliottaCommented:
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
0
naulivAuthor Commented:
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!
0
tliottaCommented:
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
0
naulivAuthor Commented:
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.....
0
naulivAuthor Commented:
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.
0
tliottaCommented:
Very interesting... I imagine that effectively explains how it happens via iNav Run SQL Scripts.

It also suggests that SQL CLI should be a possibility for creating your own interactive SQL client natively on iSeries. I'd then expect EDRS access to work as well. Neither would require Java, but both require some learning to use the APIs.

Your final resolution seems valuable to others, so I'd agree the thread should not be deleted.

Tom
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
naulivAuthor Commented:
Then here are some points for you, so it won't get deleted :)
0
tliottaCommented:
Appreciated. Wish I could've said something more than "I don't think it can be done outside a program."

Tom
0
bharath tataCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.