|
midrange.com
If you want to know what an SQLCOD value means you can display them by
concatenating the last four digits of the SQLCOD value to 'SQL' and using the
DSPMSGD command to display the associated message.
For example, if SQLCOD = 0000030 the equivalent error message would be SQL0030.
DSPMSGD SQL0030 MSGF(QSQLMSG) gives first level text of 'Number of host
variables less than result values.' with the option of viewing more information
about the error. If SQLCOD > 10000 use 'SQ' instead of 'SQL' for the
concatenation.
This is how I do it in a program:
EvalR ChrErr# = Success + %Char(sqlCod);
Select;
When sqlCod < 0;
ChrErr# = %XLate('-' :'0' :ChrErr#);
spmMsgID = 'SQL' + %subst(ChrErr# :4 :4);
When sqlCod < 10000;
spmMsgID = 'SQL' + %subst(ChrErr# :4 :4);
Other;
spmMsgID = 'SQ' + %subst(ChrErr# :3 :5);
EndSl;
Rick
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Haas, Matt
Sent: Monday, June 21, 2004 2:46 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL Statements in ILE RPG
There's a manual called "SQLSTTs and SQLCODs" or something like that that lists
both. I know it's in the InfoCenter but I don't remember exactly how to get to
it.
Matt
-----Original Message-----
From: Mike Wills [mailto:koldark@xxxxxxxxx]
Sent: Monday, June 21, 2004 3:10 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: SQL Statements in ILE RPG
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
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
