Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
Multiple cursors???
has no ratings.
Published: 02 Nov 2015
Revised: 02 Nov 2015 - 506 days ago
Last viewed on: 22 Mar 2017 (1068 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Multiple cursors??? Published by: Chris Proctor on 02 Nov 2015 view comments(8)

Good morning. I have a program that I'm working on where I want to use multiple cursors. The first one works fine, but the second one says that it couldn't find a prepare statement even tho I did specify it. When I finished with the first cursor I did close it, so I thought that would take care of it, but apparently not! Lol.

Any suggestions? Thanks!!

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: DaleB
Premium member *
Reading, PA
Comment on: Multiple cursors???
Posted: 1 years 4 months 21 days 4 hours 25 minutes ago

Sounds like something I ran into once. Are you using the same statement name for two or more PREPAREs? I don't mean the host variable; I mean the SQL statement-name, as in prepStmt below:

Exec SQL PREPARE prepStmt from :Stmt1;
Exec SQL DECLARE c1 CURSOR FOR prepStmt;
// Open, Fetch, Close c1
Exec SQL PREPARE prepStmt from :Stmt2;
Execl SQL DECLARE c2 CURSOR FOR prepStmt;
// Open, Fetch, Close c2

What I found was the name of the PREPAREd statement, prepStmt, is never deallocated. There's no way to reuse it. It only goes away when the SQL run time ends, typicaly when module exits. Work around was each PREPARE has to specify a different statement name (s1, s2, ...).

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Multiple cursors???
Posted: 1 years 4 months 21 days 4 hours 14 minutes ago

Yes, CURSOR names and the PREPARE ID have to be unique within a program.

CURSOR have to be unique (mostly) within the Activation Group/Job.

 

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Multiple cursors???
Posted: 1 years 4 months 21 days 4 hours 9 minutes ago
Edited: Mon, 02 Nov, 2015 at 13:17:37 (506 days ago)

The only thing that is the same is that I was using one variable, :sqlstmt, for the definition of the SQL SELECT. I tried using three different variable names and that didn't do anything for me either. Here's my cursors:

exec sql prepare rtnCursor from :sqlstmt1;

exec sql declare c1 cursor for rtnCursor;

exec sql close c1;

exec sql prepare canCursor from :sqlstmt2;

exec sql declare c2 cursor for canCursor;

exec sql close c2;

exec sql prepare adjCursor from :sqlstmt3;

exec sql declare c3 cursor for adjCursor;

exec sql close c3;

 

 

 

 

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Multiple cursors???
Posted: 1 years 4 months 21 days 3 hours 32 minutes ago

The host variable that's used by the PREPARE doesn't matter. Could even be a literal.

I tend to put my DECLARE, PREPARE, and OPEN in the same procedure, FETCH in its own, and CLOSE in its own. The main body of the code may occasionally do an UPDATE WHERE CURRENT OF. But all in the same module, so I've never run into problems with multiple SQL activations.

Are you calling it more than once? I also see at the top that I have Exec SQL SET OPTION CLOSQLCSR = *ENDMOD. When the module exits, cursors are implicitly closed, and SQL prepared statments are discarded; i.e., when my module exits, the prepared statement name is freed for reuse. The default is CLOSQLSCR = *ENDACTGRP, which more than likely lasts a lot longer than the current module.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Multiple cursors???
Posted: 1 years 4 months 21 days 3 hours 29 minutes ago

The other thing could be, is this module called by, or does it call, another SQLRPGLE module? If so, does the called or caller also PREPARE a statement that has the same name (rtnCursor, canCursor, or adjCursor)? The statement name is for your entire session, basically. Try making the statement names specific to the program, PgmX_rtnCursor.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Multiple cursors???
Posted: 1 years 4 months 21 days 2 hours 32 minutes ago

Start Debug and run the failing program again and review the joblog messages. I would put a breakpoint right after the 2nd (failing) PREPARE to see what's in the joblog and idientify the messages related to it.

 

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Multiple cursors???
Posted: 1 years 4 months 21 days 2 hours 25 minutes ago
Edited: Mon, 02 Nov, 2015 at 15:17:27 (506 days ago)

Bob and Dale, so sorry to have bugged you guys. It was my bad. I was missing an "and" in my SQL statement. Thanks for all the help.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Multiple cursors???
Posted: 1 years 4 months 21 days 1 hours 38 minutes ago
Edited: Mon, 02 Nov, 2015 at 16:46:12 (506 days ago)

If that's the actual code, then you're getting the correct error.

Look at the WHERE clause, it is wrong.

 

SDSTAT = 'X' SHSLLO IN(500,507,511)

You are missing the AND/OR between the two, but the first SQL stmt has it correctly.