AW: Retrieving/processing SQL messages in SQLRPGLE

Hi,

If you are not yet on release V5R3, you can get the error message as
follows:
All SQL messages are stored in the message file QSQLMSG.
The message id for an SQL code consists of SQL + the absolute value of the
SQLCOD. (if the absolute SQLCODE is < 10000). If it's > than 1000 the
message-id is SQ + the absolute value of the SQLCOD.
I.e. SQLCOD -204 --> Message-Id SQL0204.

The variable message textes are returned in the field SQLERM in the SQLCA.
With this information it's quite easy to retrieve the message text from the
message file.

If you are already on release V5R3 or higher, you can retrieve the message
text over the SQL-Statement GET DIAGNOSTICS.
The following statement can be executed after any SQL statement to retrieve
the message text. (MsgText is a variable defined in the D-Specs.
C                   IF        SQLCODE < *Zeros                             
C/EXEC SQL          Get Diagnostics Condition 1 :MsgText = MESSAGE_TEXT    
C/END-EXEC                     
C                   EndIf                                                  

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them?  Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Dan
Gesendet: Friday, February 09, 2007 18:20
An: RPG400-L@xxxxxxxxxxxx
Betreff: Retrieving/processing SQL messages in SQLRPGLE


I've created a utility that consists of a command and an RPG program that
processes an SQL command.  It's mostly for use in CL programs when a CPYF
won't do and, as much as I loved OPNQRYF "back in the day", I've moved on to
SQL.  The problem with SQL in a CL program is that RUNSQLSTM requires the
statement to be in a separate source file, which just always bugged the h*ll
out of me.  I know about the other non-IBM utility that passes the SQL
statement in parameter pieces to feed a qmqry, but that maxxed out at a 550
length statement.  When I was in the middle of modifying that to lengthen
it, someone on this list or midrange-l suggested using embedded SQL in RPG:
Execute Immediate.  This has worked great.  And, look ma!, no weird string
manipulation!

It works great, but there's a feature I'd really like to incorporate to make
it complete.  Currently, I do this:
    If SQLStt <> '00000';
      Dump(A) 'Open';
    Endif;
right after the Execute Immediate :SQLstmt.  I would like to throw back to
the calling application any messages that would appear as you'd see if you
were running from within STRSQL (interactive SQL).  This would include
errors and warnings, of course, but also informational messages, like "99
rows inserted in FILE in LIBRARY" messages.  "Throwing back to the calling
application" means that the message(s) would appear in the job log and on
the display's status line (if running interactively).

I was sure I'd find something in the archives to help me avoid having to
reinvent the wheel, but I gave up after combing through 20 or so threads.  I
know how to use the QMHSNDPM API, but how do I pick up the specific SQL
messages (and their data) to spit back through the API?

TIA,
Dan