[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2163
  • Last Modified:

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?
0
nickmbnabb
Asked:
nickmbnabb
1 Solution
 
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
 
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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
 
tliottaCommented:
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
 
nickmbnabbAuthor Commented:
OK, thanks, that was it.  We coordinated using Varying Char, and all is working.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now