AS400 Stored Procedures

I have someone calling into our AS400 using stored procedure.  When the procedure has no parameters on the input, he gets back a response.  When there are parameters on the input, he gets this:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S02]: Base table or view not found: 0 [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7967 - PREPARE of statement QZ9691D4402AC68000 completed. (SQLPrepare[0] at /builddir/build/BUILD/php-5.3.16/ext/pdo_odbc/odbc_driver.c:206)' in /var/www/vhosts/hmit.net/httpdocs/stored-procedure.php:26 Stack trace: #0 /var/www/vhosts/hmit.net/httpdocs/stored-procedure.php(26): PDO->prepare('call ycslibr.yc...') #1 {main} thrown in /var/www/vhosts/hmit.net/httpdocs/stored-procedure.php on line 26


Any ideas?
nickmbnabbAsked:
Who is Participating?
 
tliottaConnect With a Mentor Commented:
I don't know what you used to display your screen-shot. It sure doesn't seem to be a system screen, so it's perhaps from some 3rd-party product. That doesn't help us much since we can't know how to interpret what it says.

However, if I assume that I understand the screen-shot correctly, then this is probably wrong:

Query was:
call ycslibr.ycso82rv('XXXX 1113200')

Open in new window


According to your screen-shot, the parameter is defined as CHARACTER data type possibly of length (15). But your CALL statement is passing a VARCHAR value, not CHARACTER. Therefore there is probably a signature mismatch and DB2 can't locate any stored proc named 'ycso82rv' with a VARCHAR parm.

The result is a "not found".

There's guesswork there since the stored proc definition isn't shown, and it's not very clear how the CALL is executed. But you'll almost always get an error if you try to call a stored proc without matching parm data types.

Tom
0
 
momi_sabagCommented:
looks like the parameter contains a table name to select from and the passed in value does not represent a valid table (either table does not exists or the caller has no permissions to use it)
0
 
nickmbnabbAuthor Commented:
When I create the stored procedure, where is it storing these parameters?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Dave FordSoftware Developer / Database AdministratorCommented:
Can you "manually" run the stored procedure (with the same parameters) from either the green-screen (StrSQL) or from the GUI (System i Navigator)?

What happens when you try?
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
> When I create the stored procedure, where is it storing these parameters?

You can see the stored procedures parameters either through the GUI (System i Navigator) or in table SYSPARMS in library QSYS2. Personally, I prefer the GUI. It's way easier.

HTH,
DaveSlash
0
 
nickmbnabbAuthor Commented:
When I use Client Access and the same sign on as the outside person, it works.
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
So, if you can successfully run the proc (using the same parameters as the PHP of course), then the problem must lie in the PHP connection to DB2.

You say the PHP can successfully call a procedure that has no parameters, right?
0
 
nickmbnabbAuthor Commented:
Correct, I made a copy of the stored procedure with no parameters, and the correct result set was passed back.
0
 
nickmbnabbAuthor Commented:
I have it now where the no-parameter call sends back a good result set.  The parameter call sends back an empty result set.  In both programs I am forcing the same contents into the parameter.

What could be making the parameter go awry in the php call?  Both calls work properly through Client Access.
0
 
nickmbnabbAuthor Commented:
Here's a screen shot of my SYSPARMS:
sysparms
Here's what comes back with the parm run:

Query was:
call ycslibr.ycso82rv('XXXX 1113200')
Results:
Array ( )

When I change the program to force XXXX 1113200 into pmcont, it runs correctly:

Query was:
call ycslibr.ycso82rv('XXXX 1113200')
Results:
Array ( [0] => Array ( [ZWBTYPE] => 3 [0] => 3 [ZWBYARD] => K700 [1] => K700 [ZWBACNM] => SOLUM A. KALLNES ENTERPRISE [2] => SOLUM A. KALLNES ENTERPRISE [ZWBVNBR] => XXXX 1113200 [3] => XXXX 1113200 [ZWBBL#] => 6111112620 [4] => 6071602620 [ZWBIT#] => [5] => [ZWBFREXZ] => 20120713 [6] => 20120713 [ZWBPDFRZ] => 00000000 [7] => 00000000 [ZWBHOLD] => N [8] => N [ZWBPCSZ] => 00037 [9] => 00037 [ZWBPKG] => CTNS [10] => CTNS [ZWBWGTZ] => 0191361 [11] => 0191361 [ZWBFSTS] => R [12] => R [ZWBCSTS] => [13] => [ZWBEXMS] => 8 [14] => 8 [ZWBCESC] => C [15] => C [ZWBLOC] => K700 [16] => K700 [ZWBGUDTZ] => 00000000 [17] => 00000000 [ZWBGCHGZ] => 000000000 [18] => 000000000 [ZWBLCGU] => [19] => [ZWBDTYP] => P [20] => P [ZWBLCHGZ] =>


NOTE:  I put in a test file to write the PMCONT parameter as soon as it comes into the program, and it looks fine.  Yet, it doesn't work unless I force in the contents.  Also, there's  a two-parameter program in which only the second parameter is being sent, nulls allowed, and when I look at my test file in which I wrote both parameters, the first one shows as having the contents of the second one with a few blanks first.
0
 
nickmbnabbAuthor Commented:
I just looked at my test file in Hex, and noticed the blanks in the last 3 positions of the incoming field were not blanks.  I'll have the guy writing the PHP address that.
0
 
nickmbnabbAuthor Commented:
OK, thanks, that was it.  We coordinated using Varying Char, and all is working.
0
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.

All Courses

From novice to tech pro — start learning today.