Odd Dynamic SQL Error message--SOLVED

Posted by: Bob Cozzi

Rogue Programmer

Cozzi Productions, Inc.

www.cozTools.com/qryfile

Chicagoland

Odd Dynamic SQL Error message--SOLVED
has no ratings.

Published: 03 Apr 2012
Revised: 23 Jan 2013 - 4110 days ago
Last viewed on: 25 Apr 2024 (5085 views)

Main URL:

Odd Dynamic SQL Error message--SOLVED Published by: Bob Cozzi on 03 Apr 2012 view comments(2)

I'm doing this in a program:

 for i = 1 to 9;
exec SQL PREPARE dynPrep FROM :dynSelect; 
exec SQL DECLARE i1 CURSOR for dynPrep;   
exec SQL OPEN    i1;                      
exec SQL FETCH   i1  INTO :qtyOH;

  // write to a subfile

exec SQL CLOSE i1;
 endfor;

The code runs fine for 5 or 6 iterations, then it returns SQLSTATE 26501. "Prepared statement dynprep not found."

Unique? i know. Not sure where go to from here though.

Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)

Posted by: DaleB

Premium member *

Reading, PA

Comment on: Odd Dynamic SQL Error message--SOLVED
Posted: 12 years 21 days 14 hours 35 minutes ago
Edited: Wed, 04 Apr, 2012 at 08:18:42 (4404 days ago)

Do you use the name dynPrep for a prepared statement in another program/module in the same job? SQL statement and cursor names have job scope. I started naming mine using the program or module as part of the name, SOMEMOD_dynPrep.

Oh, and there doesn't seem to be a way to explicitly drop or delete a prepared statement. When the last SQLxxx program or module exits, that's when it gets cleaned up. May be tied to the close cursors option, though that's just a guess.

Posted by: bobcozzi

Site Admin ****

Chicagoland

www.cozTools.com/qryfile

Comment on: Odd Dynamic SQL Error message--SOLVED
Posted: 12 years 21 days 13 hours 43 minutes ago
Edited: Wed, 04 Apr, 2012 at 09:10:07 (4404 days ago)

I think I have it figured out--but it was entirely by accident.

I was working in library named COZTEST on the primary system. All of the secondary system have library COZTEST on it when I go on them to do custom work.

Two of the systems, system D & E are at v5r2 and do not have a license to PDM/SEU and therefore I push objects out to them, rather than do customer work. Therefore I never created a library named COZTEST on those 2 systems.

When i was testing this app, I compiled it into COZTEST on the primary and let it run. Every system except the two that don't have COZTEST on it were working. I assumed this was due to PTFs or Internet issues.

So we decided to say screw-it, and put it into production in (we'll call it) PRODLIB.

PRODLIB is on every system.

I only moved it into PRODLIB on the primary system since it wasn't going to be deployed at the remote locations--just locally. After I did that, and ran it for demo purposes, all prepared to blame PTFs or the Internet, the connection to that remote site worked just fine. No PREPARE stmt error in SQL.

Then I remembered playing with SQL Packages a few months ago, and not fully understanding them, so I continued to ignore them.

But the linkage in my brain was already established at this point. And here's what was happening:

The program was running in library COZTEST on System A and connecting to 5 outlying systems. It uses SQL's new 3-level naming to go after files on those remote systems (which uses DDM under the covers on IBM i). Apparently when an program with Embedded SQL runs, it requires an SQL PKG (package). By default that SQL Package is created on the target/remote system, in the same library in which the host program is located. This is controlled via the SQLPKG parm on the CRTxxx command. Since library COZTEST was used on the HOST, only remote locations that also have COZTEST library on them will be able to create and transfer said SQLPKG to those remote sites. After moving the program into production, and running out of PRODLIB (which is also installed on all of the remote locations) the program and SQL process within it, worked just fine.