AW: SQL Cursor Not Open -- RPG400-L

After each fetch I test the SQLCOD value and if its not 0 I'll close the

cursor and leave the loop

I'd not check SQLCODE for *Zeros, but for = 100 (not found or SQLSTATE =
'02000') and < *Zeros (Error - or Position 1-2 in the SQLSTATE is > '02')!
SQL may send a warning (SQLCODE > *Zeros and <> 100 or Position 1-2 in the
SQLSTATE = '01'), but the data is nevertheless returned.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von WAJE0822
Gesendet: Thursday, 25.10 2012 23:10
An: RPG programming on the IBM i / System i
Betreff: Re: SQL Cursor Not Open

Its been my experience that you must check the value of SQLCOD after every
SQL statement. I generally execute a select (either prepared or static),
open the cursor, and then fetch rows from the resulting dataset in a loop
conditioned by the SQLCOD having a value of 0. After each fetch I test the
SQLCOD value and if its not 0 I'll close the cursor and leave the loop. If
I execute additional select statements within the major fetch loop and fail
to test the resulting SQLCOD and my select fails, then the program will
error with a Cursor not open error.
Hope this helps
Bill Erhardt

On Oct 25, 2012, at 4:27 PM, Alan Campin <alan0307d@xxxxxxxxx> wrote:

The most common error made with SQL that I have seen is not error
checking each statement.

Just curious, why are you preparing the statement? Why not use static
compiling?

On Thu, Oct 25, 2012 at 1:33 PM, Jerry C. Adams <midrange@xxxxxxxx> wrote:

I hadn't thought of checking the state after each SQL statement.
Usually just check for the end-of-cursor condition. I'll add that
test tomorrow when I get back to this.

None of the columns/fields are NULL.

Jerry C. Adams
IBM i Programmer/Analyst
Sex is one of the nine reasons for reincarnation; the other eight are
unimportant.
--
A&K Wholesale
Home Office: 615-832-2730
email: midrange@xxxxxxxx

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Briggs, Trevor (TBriggs2)
Sent: Thursday, October 25, 2012 1:59 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Cursor Not Open

You should really check SQLCOD after every SQL statement, not just
the FETCH. My suspicion is that it's actual failing at the PREPARE
statement for some reason. Are any of the columns NULL enabled?

Trevor Briggs
Analyst/Programmer
Lincare, Inc.
(727) 431-1246
TBriggs2@xxxxxxxxxxx

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jerry C. Adams
Sent: Thursday, October 25, 2012 2:42 PM
To: 'RPG programming on the IBM i / System i'
Subject: SQL Cursor Not Open

First: V5R1

I have a simple (I thought) SQLRPGLE program that is supposed to
select all of the records from our Item Master, put them in order,
and then use the retrieved records to print a report. Done this a
few dozen times in the past, but this time no dice. Under debug the
program ends (normally, not an
abend) when I test the value of SQLSTT, which is 24501 (Cursor is not

open).

Relevant, I think, code:

<code>

$BuildSQL();

/end-free

C/Exec SQL

C+ Prepare MySQLStmt from :SQLString

C/End-Exec

C/Exec SQL

C+ Declare NVList Cursor for MySQLStmt

C/End-Exec

C/Exec SQL

C+ Open NVList

C/End-Exec

/free

DOW 1 = 1;

/end-free

C/Exec SQL

C+ Fetch Next from NVList into :NVLine

C/End-Exec

/free

IF SQLStt = '02000' OR SQLCOD < *Zeros;

</endcode>

The $BuildSQL subprocedure successfully builds the SQLString value:

SELECT imloc, imdesc, imumi, imbal, impidx, impitm, imclas,

FROM "A.IVMAST" ORDER BY imloc, impitm FOR Read Only

NVLine is a data structure with the field names equivalent to the
SELECT with the proper attributes and order.

SQLSST = '24501' and SQLCOD = -501 under debug. Both of which mean
that the sursor is not open.

This is a single mod program; the only subprocedure is $BuildSQL
which only creates the SQLString. The code snippet is from a
subroutine. So I don't see that the cursor could be closed by ending
the activation group, which was the compile option.

I have compared this to similar programs I wrote using the same

methodology.

I, also, know/suspect that I am overlooking the obvious and will be
embarrassed when we find out what is wrong, but I have looked at this
and debugged it for the last hour.

Thanks.

Jerry C. Adams

IBM i Programmer/Analyst

Never argue with a women when she's tired -- or rested.

--

A&K Wholesale

Home Office: 615-832-2730

email: midrange@xxxxxxxx

--
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 message originates from Lincare Holdings Inc. It contains
information which may be confidential or privileged and is intended
only for the individual or entity named above.
It is prohibited for anyone else to disclose, copy, distribute or use
the contents of this message.
All personal messages express views solely of the sender, which are
not to be attributed to Lincare Holdings Inc., and may not be copied
or distributed without this disclaimer.
If you received this message in error, please notify us immediately
at MailAdmin@xxxxxxxxxxx or (800) 284-2006.
*********************************************************************
*******
*********************************************************************
*******
****************************************************

--
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.