Re: SQL Statements in ILE RPG

[Mike Wills <koldark@xxxxxxxxx>]

Where do you lookup the error codes for SQLSTT? Is it in one of the DB2 manuals?

On Mon, 21 Jun 2004 14:54:56 -0400, Haas, Matt <matt.haas@xxxxxxxxxxx> wrote:
> 
> I'd suggest checking SQLSTT instead of SQLCOD. If the first two characters 
> are 00 or 01, the data's safe to use. 02 indicates no records found (or end 
> of cursor). Anything else indicates an error.
> 
> I use this data structure to make testing these values a little easier:
> 
> DSQLState         DS
> D SQLClass                1      2A
> D SQLStateCode            3      5A
> 
> All you have to do is set SQLState = SQLSTT and then you can just check 
> SQLClass.
> 
> SQLCOD's have changed in the past since they're unique to DB2/400 but 
> SQLSTT's are based on a common standard that is less likely to change. One 
> final point on SQLSTT vs. SQLCOD is that a given SQLCOD can map to more than 
> on SQLSTT which means that SQLSTT's are usually more helpful when debugging.
> 
> Matt
> 
> 
> -----Original Message-----
> From: Guy Henza [mailto:guyhenza@xxxxxxxxxxx]
> Sent: Monday, June 21, 2004 2:36 PM
> To: rpg400-l@xxxxxxxxxxxx
> Subject: SQL Statements in ILE RPG
> 
> I wrote a nice little embeded SQL in a RPGLE program.  I tested the
> statement in STRSQL and got 2965 rows returned.  When I run it in my RPGLE I
> get 39 rows...
> 
> C/exec sql declare c1 cursor for
> C+    select cnme, bsdst#, substring(bssdat,1,6), sum(bsexbs)
> C+      from bsf100s
> C+      join brcm    on bsdst# = rcusno
> C+      join rcm     on bsdst# = ccust
> C+     where substring(bssdat,1,6) between :strdat and :enddat
> C+       and bsrtyp = 'S'
> C+     group by cnme, bsdst#, substring(bssdat,1,6)
> C+     order by cnme, bsdst#, substring(bssdat,1,6)
> C/end-exec
> 
> C/exec sql
> C+  open c1
> C/end-exec
> 
> C     sqlcod        doune     0
> C/exec sql
> C+ FETCH NEXT FROM C1 INTO :icnme, :idst#, :isdat, :iexbs
> C/end-exec
> 
> I changed doune 0 to doueq 100 and got most of my expected data.  BUT I'm
> still missing some.
> 
> The SQLCODE is also set by the database manager after each SQL statement is
> executed as follows:
> 
> If SQLCODE = 0 and SQLWARN0 is blank, execution was successful.
> If SQLCODE = 100, no data was found. For example, a FETCH statement returned
> no data, because the cursor was positioned after the last row of the result
> table.
> If SQLCODE > 0 and not = 100, execution was successful with a warning.
> If SQLCODE = 0 and SQLWARN0 = 'W', execution was successful with a warning.
> If SQLCODE < 0, execution was not successful.
> A complete listing of DB2 UDB for iSeries SQLCODEs and their corresponding
> SQLSTATEs is provided in the SQL Messages and Codes book in the iSeries
> Information Center.
> 
> I tried reading the "SQL Messages and Codes book" and I think Greek would be
> easier to understand.
> 
> IF SQLCOD is not equal 0 ang not equal to 100 can I still use the data?
> 
> Guy Henza
> guyhenza@xxxxxxxxxxx
> 
> _________________________________________________________________
> MSN Toolbar provides one-click access to Hotmail from any Web page - FREE
> download! http://toolbar.msn.click-url.com/go/onm00200413ave/direct/01/
> 
> --
> 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.
> 
> 




-- 
Mike Wills
iSeries Programmer/Lawson Administrator
koldark@xxxxxxxxx
http://www.koldark.net