Jerry, sorry, forgot to mention that quote is from DB2 for i SQL REFERENCE 7.1
I don't have anything older at hand....
Back to the question of a cursor, it sounds like the sql statement
you are performing the EXECUTE IMMEDIATE on is dynamic but is *not* a cursor
so I was likely *wrong* to conclude: " you do have an open cursor"
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jerry C. Adams
Sent: Monday, April 30, 2012 12:56 PM
To: 'RPG programming on the IBM i / System i'
Subject: RE: File Still Open
Gary,
Which manual? I don't recall a cursor being used (explicitly named, that
is) on either of these SQL statements.
Jerry C. Adams
IBM i Programmer/Analyst
I cannot imagine any condition which could cause this ship to founder. I cannot conceive of any vital disaster happening to the vessel. Modern shipbuilding has gone beyond that. -E.IO. Smith, captain of the Titanic
--
A&K Wholesale
Murfreesboro, TN
615-867-5070
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Gary Thompson
Sent: Monday, April 30, 2012 1:47 PM
To: RPG programming on the IBM i / System i
Subject: RE: File Still Open
From the manual: "EXECUTE IMMEDIATE combines the basic functions of the
PREPARE
and EXECUTE statements."
so you do have an open cursor - not so sure about the CLOSE statement, but I have a habit of specifying CLOSQLCSR(*ENDMOD).
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Jerry C. Adams
Sent: Monday, April 30, 2012 12:07 PM
To: 'RPG programming on the IBM i / System i'
Subject: RE: File Still Open
I am just a tad confused about this cursor stuff. I have several programs where I (a) build a string, (b) PREPARE from the string, (c) DECLARE the cursor, (d) OPEN the cursor, (e) FETCH from the cursor, and (f) CLOSE the cursor when I'm through. That is, I always explicitly CLOSE the cursor in these situations.
This program just builds a string and then EXECUTE IMMEDIATE :the_string.
(In fact, other than setting the SQL Options, that's the only line conditioned by /EXEC SQL.) Since I never DECLAREd a cursor in this program, I couldn't very well CLOSE it inside the program, could I? If I could, how (other than the CLOSQLCSR parameter on the CRTSQLRPGI command)? I did not find any H-spec keywords that would do it.
Jerry C. Adams
A Confused cowboy from New Mexico.
It's like what Yogi said - What did Yogi say? -George Bell, Chicago White Sox
--
A&K Wholesale
Murfreesboro, TN
615-867-5070
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Alan Campin
Sent: Monday, April 30, 2012 12:21 PM
To: RPG programming on the IBM i / System i
Subject: Re: File Still Open
Even using the *ENDMOD may not close the cursor. SQL has soft and hard closes. It try's to keep the file open in case it needs to be opened again.
You may close the cursor, do *ENDMOD and still see it. If it is a soft close and some other process needs the file in a higher level of open, the database releases it to be closed. If it is a hard close(File still open), the database won't release it.
On Mon, Apr 30, 2012 at 11:09 AM, Jerry C. Adams <midrange@xxxxxxxx> wrote:
Thanks, Rory. That did the trick. Never used that before because,
usually, I use cursors frequently and always CLOSE cursor inside the
RPGLE
program.
Didn't know I had a cursor in this one since I never DECLAREd one. I'm
a real put putz when it comes to this SQL stuff. The default (at
5.1 anyway) is *ENDACTGRP. If I had called the program via a CL, I
could have used the RCLACTGRP command to the same effect, right?
Jerry C. Adams
IBM i Programmer/Analyst
We've got to pause and ask ourselves: How much clean air do we need?
-Lee Iacocca
--
A&K Wholesale
Murfreesboro, TN
615-867-5070
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Rory Hewitt
Sent: Monday, April 30, 2012 11:24 AM
To: RPG programming on the IBM i / System i
Subject: Re: File Still Open
Have you tried specifying COLSQLCSR(*ENDMOD) on the CRTSQL* command?
The default is CLOSQLCSR(*ENDSQL), I believe, which probably isn't
what you want.
On Mon, Apr 30, 2012 at 9:19 AM, Jerry C. Adams <midrange@xxxxxxxx> wrote:
First, V5R1.
I have an SQLRPGLE program that uses embedded SQL to update multiple
records (potentially, anyway) in a master file. I build a string
based upon the user's options and then use EXECUTE IMMEDIATE
:sqlstring. The master file is referenced only in the sqlstring;
i.e., it is not defined in the F-specs.
The string is: UPDATE TSTQS36F/"A.IVMAST" SET impidx = 'X ' ,
imprcd =
'
'
WHERE impidx = 'CONW' AND imprcd = ' '
I never noticed before that, when the program ends, the master file
is still open when I run DSPJOB option 14. Is this normal, some
keyword I need to add to the UPDATE string, or something else to be
sure the file is closed?
Jerry C. Adams
IBM i Programmer/Analyst
Everything that can be invented, has been invented. -Charles Duell, U.S.
Patent Office Director (1899)
--
A&K Wholesale
Murfreesboro, TN
615-867-5070
--
This is the RPG programming on the IBM i / System i (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.
--
Rory Hewitt
http://www.linkedin.com/in/roryhewitt
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
As an Amazon Associate we earn from qualifying purchases.