SQL Error trapping

[dpr...@bcpl.net]

I am using the "RUNSQLSTM" to pass a few variables for date selection
to create a data file. There are records that have "bad" data in a
field or two. This statement is SUBSTR intensive but works fine with
a "clean" test file. At first when I got the "-601 error, I thought it
was my code, but I later learned it was the actual data.
I would like to monitor for SQL error messages (and hopefully answer
them) from a CL. I mean, if I am going to write an RPG to access the
SQLSTATE or SQLCODE, I might as well do it in RPG. I would prefer to
use SQL for the file creation, form printing, frontended by a CL.
I cannot trap SQL messages from CL, I have looked and looked all over
IBM for actual examples for SQL error trapping to no avail. In fact,
other than an endless stream of SQLSTATE, SQLCODE, and reference
numbers, I canot find one single example of SQL message handling!
Even in the RPG, SQLRPGLE, and RGPLE code sources the states are
mention over and over, but I have yet to see an actuall FULL example
code where the actual error SR's are shown. Could a kind soul please
show me a full example of an SQLRPGLE source from top to bottom, ie...
All SR's, SQL execs, HDCO specs, and all error handling related
source. I know that is a biggie, but I am so frustrated!
Last but not least, I created an ISQL (which will later go to source
file for runsqlstm) that creates a table. I created an INSERT ISQL
that fills this table. I try to put them both on my screen at the same
time using the ; as the separator for the create/insert ISQL.
Just won't work, is there a separator for ISQL to allow two thingys in
the same source? (thingys is the best I can do right now)
TIA ANY help will be totally appreciated!

[Rolf P Mittag]

dpr...@bcpl.net wrote:

...

> I would like to monitor for SQL error messages (and hopefully answer
> them) from a CL.

not possible

> SQLSTATE or SQLCODE, I might as well do it in RPG.

why not ?

. . .

> Even in the RPG, SQLRPGLE, and RGPLE code sources the states are
> mention over and over, but I have yet to see an actuall FULL example
> code where the actual error SR's are shown.

there's no magic there. the SQLCOD and SQLSTT fields are declared
by the precompiler and filled after every executable embedded
SQL statement (i.e. not after declare)

so just

c/Exec Sql
c+ your Sql statement here
c/End-Exec

c if SQLCOD = *zero
* everything ok
c else
* SQLCOD > *zero = warning (e.g. end of file)
* SQLCOD < *zero = error
c endIf

the meaning of the Sql Codes are listet in the manual

hth
Rolf

[Seb]

Hi ,

Which sort of "bad" data ? If there are null data, don't matter monitor
errors; just try
SUBSTR(IFNULL( - - - -, ' '), - , - ) or other combinations.

To monitor SQL errors in HLL programs (Cpbol, RPG, ...) :
EXEC SQL WHENEVER SQLERROR GOTO HANDLER END-EXEC.
or
EXEC SQL WHENEVER SQLWARNING CONTINUE END-EXEC.
or
EXEC SQL WHENEVER NOT FOUND GOTO ENDDATA END-EXEC
Perhaps this could work in an *TXT source for RUNSQLSTM , without EXEC SQL .

I'm not an expert; I just hope this will point you in the right direction.

Seb

<dpr...@bcpl.net> a écrit dans le message news:
8l44iuk9hinuqb1s3...@4ax.com...

[Herb Bujak]

I've got a command that I can send you that will do this for you. I use it
all the time to run SQL stmts from within a CL program and it issues an *ESC
message that can be monitored for if the command fails. It's different from
RUNSQLSTM in that you specify the SQL stmt right there instead of in a
source member. For instance, you could say ...

ZEXECSQL SQLSTMT('delete from filea where count = 0')

and it would do just that. I've got a V5R1 version in a save file if you
want it. Just let me know and I'll send it to you.

<dpr...@bcpl.net> wrote in message
news:8l44iuk9hinuqb1s3...@4ax.com...