Re: SQL Cursor Not Open

[WAJE0822 <waje0822@xxxxxxxxx>]

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.