Charles;
Years ago (mid-90s) I created a function sqlErrorTrap() which I execute after each sql statement.
D sqlErrorTrap...
D PR 10I 0
D 10I 0 Const sqlCod
D 100A Const Varying Sql Operator
D 100A Const Varying Sql Operand
D 10A Const Options(*NOPASS) Message Type
D 256A Const Varying Options(*NOPASS) Call Stack Entry
I have thought about creating a new function to trap sql state, although I have not had the time or the inclination. The reason to re-design it is that sqlcod can be a moving target but this function only relies on positive/negative sqlcodes.
What it does: If the sqlCode is < 0 then takes the absolute value of the sqlCode and concats that to SQL and sends the message to the current call stack, with *DIAG as the message type. It then send a home grown message, with the sqlCode value, the derived message id, the sql Operator and Sql Operand parameters to the current call stack, with the message type specified by the parameter. If the message type parameter is not specified then it is sent as an *ESCAPE message.
If an *ESCAPE message is not sent the sqlCod value is returned so it can be tested.
The most useful piece of this is that you always have messages in your joblog which you can track back to the embedded sql that caused it.
I added the call stack entry parameter a few years ago because I needed to throw a message to the database handler (from inside a trigger program) and have only used it twice.
Example:
Exec Sql
Select mvareaid
Into :data.AreaId
From mgpvendor
Where mvvendor = :data.Vendor And
mvstate = :data.State;
If sqlErrorTrap(sqlCod : 'Select' : 'Vendor/State') = 100;
Exec Sql
Select '1'
Into :found
From mgpvendor
Where mvvendor = :data.Vendor
Fetch First Row Only;
If sqlErrorTrap(sqlCod : 'Select' : 'Vendor') = 100;
Duane Christen
--
Duane Christen
Senior Software Engineer
(319) 790-7162
Duane.Christen@xxxxxxxxxx
Visit PAETEC.COM
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Wednesday, September 01, 2010 1:01 PM
To: RPG programming on the IBM i / System i
Subject: Re: Does the recommendation against variable names starting with SQL apply to constants?
Not sure if I should be flattered or concerned that I've got a follower! :)
In any case, you're right I wouldn't like SUCCESSFUL_SQL_STATE.
Not a big fan of STATE_SQL_SUCCESSFUL either for that matter, it just doesn't read right to me:
if sqlState = STATE_SQL_SUCCESSFUL;
readability of the code is a big one for me.
I think the SQL_STATE needs to be there, simply to avoid namespace clashes.
The only thing that reads right to me is SQL_STATE_SUCCESSFUL, so if I were to change it I suppose I'd go with Birgitta's suggestion of cSQL_STATE_SUCCESSFUL as it's pretty easy to ignore the 'c'.
I suppose another thought would be QSQL_STATE_SUCCESSFUL or maybe QSQLSTATE_SUCCESSFUL. I like those since they given an indication that the values are "owned" by IBM. In fact, my copy member is actually names QSQLSTATES. But are there any recommendations against starting a variable or constant with 'Q'? I know you're not supposed to create objects on the system that start with 'Q'.
But I'm leaning towards Scott's example of not trying to fix it before it actually breaks something. :)
Charles
On Wed, Sep 1, 2010 at 4:09 AM, David FOXWELL <David.FOXWELL@xxxxxxxxx> wrote:
-----Message d'origine-----
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] De la part de Schutte, Michael
D.
You know what they say about assuming? You could just change the
naming.
SUCCESSFUL_SQL_STATE
NO_DATA_SQL_STATE
NO_MORE_RS_SQL_STATE
I've been a follower of Charles' naming techniques for a while now. I don't think he'd like those names.
Maybe :
STATE_SQL_SUCCESSFUL
STATE_SQL_NO_DATA
STATE_SQL_NO_MORE_RS
would be more his style. Although I'm not sure why SQL and STATE need to be included in the constant names.
Presumably, they would be used like : if SQLSTATE = SQL_STATE_SUCCESS
Why not
RS_NOT_FOUND
RS_FOUND
RS_END_OF_FILE
etc.
--
This is the RPG programming on the IBM i / System i (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 IBM i / System i (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.
midrange.com
