RE: SQL Statements in ILE RPG

And I've seen examples where one SQLSTT can translate to more than one 
SQLCOD.

For the sake of being more compatible with the newbies from other 
databases I do try to use SQLSTT over SQLCOD. (And who knows, maybe some 
day I'll have to migrate over to those other databases).  But there are 
times I still check SQLCOD when I need to break down a particular SQLSTT.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Haas, Matt" <Matt.Haas@xxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
06/21/2004 01:54 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: SQL Statements in ILE RPG






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.