RE: embedded sql

Sorry about that Rob, I will be sure the wife gives me four lashes with the 
noodle tonight 

On Wed, 26 Oct 2005 14:52:40 -0500, rob wrote
> Now that sounds like a Frequently Asked Question.
> http://faq.midrange.com/data/cache/1.html
> Programming, SQL, Where can I look up SQLCOD and SQLSTATE values?
> 
> Rob Berendt
> -- 
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
> 
> "Douglas W. Palme" <dpalme@xxxxxxxxxxx> 
> Sent by: rpg400-l-bounces@xxxxxxxxxxxx
> 10/26/2005 02:48 PM
> Please respond to
> RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
> 
> To
> RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
> cc
> 
> Fax to
> 
> Subject
> RE: embedded sql
> 
> Where can I get a listing of what the various codes mean Matt?
> 
> On Wed, 26 Oct 2005 13:39:59 -0600, Tyler, Matt wrote
> > No it means you attempted to put data into a field that is shorter than
> > the actual data length from the data base. Use SUBSTR() in the query 
> > or make the host field bigger.  This is a warning only, the record 
> > was still passed to your program from the database.
> > 
> > Thank you,
> > Matt Tyler
> > WinCo Foods, LLC
> > mattt@xxxxxxxxxxxxxx
> > 
> > -----Original Message-----
> > From: rpg400-l-bounces@xxxxxxxxxxxx
> > [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Douglas W. Palme
> > Sent: Wednesday, October 26, 2005 1:35 PM
> > To: RPG programming on the AS400 / iSeries
> > Subject: RE: embedded sql
> > 
> > When I run the query in interactive mode it runs fine and returns 
> > the correct number of results....however after the fourth record is 
> > read in the program it generates an sqlstt code of '01004' which 
> > appears to be some kind of sort error.  Here is the code:
> > 
> > C     NOCATEGORY    BEGSR
> > 
> > C/EXEC SQL 
> > C+    DECLARE NOCAT CURSOR 
> > C+    FOR SELECT 
> > C+        ILNSOLDTO, 
> > C+        ICMNAME, 
> > C+        ILNSLSMN, 
> > C+        SUM(ILNEPRICE), 
> > C+        SUM(ILNEUAVCST), 
> > C+        SUM(ILNEPRICE) - SUM(ILNEUAVCST) AS MARGIN 
> > C+    FROM CLASSCATLF 
> > C+    WHERE 
> > C+        ILNINVDATE >= :DSDATE AND 
> > C+        ILNINVDATE <= :DEDATE AND 
> > C+        ITMCLASS = :DCLASS 
> >  C+    GROUP BY 
> >  C+        ILNSLSMN, ILNSOLDTO, ICMNAME
> >  C+    ORDER BY 
> >  C+        ILNSLSMN, 
> >  C+        MARGIN DESC 
> >  C/END-EXEC
> > 
> >  C/EXEC SQL 
> >  C+    OPEN NOCAT 
> > C/END-EXEC 
> > C                   WRITE     HEADINGS 
> > C                   DOW       sqlstt = SQLSTATEOK 
> > C/EXEC SQL 
> > C+ 
> > C+    FETCH NOCAT 
> > C+    INTO :DCUSTNO, :DNAME, :DSLS, :DSALES, :DCOST, :DMARGIN
> > C/END-EXEC 
> > C                   IF        *IN99 = *ON 
> > ...
> > 
> > On Wed, 26 Oct 2005 10:20:24 -0500, Holden Tommy wrote
> > > You can check the SQLCOD or the SQLSTT values to determine if the
> > > statement was successful or not.
> > > 
> > > SQLCOD=*zeros = success
> > > SQLCOD=100 = end of cursor(EOF)
> > > 
> > > For SQLSTT values you can find most of them at 
> > > http://wiki.midrange.com/index.php/SQLSTATE_Constants
> > > 
> > > Thanks,
> > > Tommy Holden
> > > 
> > > -----Original Message-----
> > > From: rpg400-l-bounces@xxxxxxxxxxxx
> > > [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Douglas W. Palme
> > > Sent: Wednesday, October 26, 2005 10:09 AM
> > > To: RPG Group
> > > Subject: embedded sql
> > > 
> > > When using embedded SQL in RPG, I need to know when there are no more
> > > records to process from the query, I know that I can use a count(*)
> > > statement but that seems completely inefficient.
> > > 
> > > Is there not an SQL variable that I can use to determine that the
> > SELECT
> > > INTO statement has no more records to process?
> > > 
> > > I checked the embedded SQL programming guide but did not find 
> > > anything relevant to RPG
> > > 
> > > If you bought it, it was hauled by a truck - somewhere, sometime.
> > > 
> > > -- 
> > > This is the RPG programming on the AS400 / iSeries (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 AS400 / iSeries (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.
> > 
> > If you bought it, it was hauled by a truck - somewhere, sometime.
> > 
> > --
> > 
> > This is the RPG programming on the AS400 / iSeries (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 AS400 / iSeries (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.
> 
> If you bought it, it was hauled by a truck - somewhere, sometime.
> 
> -- 
> This is the RPG programming on the AS400 / iSeries (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 AS400 / iSeries (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.


If you bought it, it was hauled by a truck - somewhere, sometime.