× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. October 2012

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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.