Re: SQL Cursor Not Open

[Alan Campin <alan0307d@xxxxxxxxx>]

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.