RE: SQL status code values

["Goodbar, Loyd (ETS - Water Valley)" <LGoodbar@xxxxxxxxxxxxxx>]

Chris,

Here is some code I have to retrieve a formatted message based on the SQL
error code SQLCOD, message data, and lookup to the message file.

Here is the call, which uses the SQL error code and SQL error message data:

c                   eval      sqlmessage = rtvsqlmsg( sqlcod : sqlerm ) 

Required code:

The subprocedure TOOLSSRC/SERVICEPGM,TTTGETSQLM: 

Code: 
      *-- For service program modules, start here. 
     h nomain 

      *-- For /copy subprocedure use, make sure you get these in your
program. 
      /copy toolssrc/formats,errorcode 
      /copy toolssrc/apiibm,apimsg 

      *-- For /copy or inline subprocedure use, start here. 
     p RtvSqlMsg       b                   export 

     d RtvSqlMsg       pi         32000a   varying 
     d  sqlcod                        5p 0 const 
     d  sqlerm                       70a   value 

     d sqlabs          s              5p 0 inz 
     d sqlchar         s              6a   inz 
     d sqlmsgid        s              7a   inz 

     d sqlmsgf         ds            20 
     d  sqlmsgfile                   10a   inz('QSQLMSG') overlay(sqlmsgf:1)

     d  sqlmsglib                    10a   inz('QSYS') overlay(sqlmsgf:11) 

     d returnval       s          32000a   inz varying 

     d SUCCESS         c                   'The SQL statement has run + 
     d                                     successfully.' 

     c                   if        sqlcod = 0 
     c                   eval      returnval = SUCCESS 
     c                   return    returnval 
     c                   endif 

     c                   eval      sqlabs = %abs(sqlcod) 
     c                   eval      sqlchar = %editw(sqlabs:'0     ') 

     c                   eval      sqlmsgid = 'SQ' + %subst(sqlchar:2:5) 
     c                   if        %subst(sqlmsgid:3:1) = '0' 
     c                   eval      %subst(sqlmsgid:3:1) = 'L' 
     c                   endif 

     c                   callp     RtvMsg( 
     c                             rtvm0100 : 
     c                             %size(rtvm0100) : 
     c                             'RTVM0100' : 
     c                             sqlmsgid : 
     c                             sqlmsgf : 
     c                             sqlerm  : 
     c                             %size(sqlerm) : 
     c                             '*YES' : 
     c                             '*NO' : 
     c                             errc0100 
     c                             ) 

     c                   eval      returnval = %subst(rtvm01_data:1: 
     c                             rtvm01_msgret) 

     c                   return    returnval 

     p RtvSqlMsg       e 


Support code TOOLSSRC/FORMATS,ERRORCODE: 

Code: 
     d errc0100        ds 
     d  errc01bytpro                 10i 0 
     d  errc01bytava                 10i 0 
     d  errc01excid                   7a 
     d                                1a 
     d  errc01excdta                 40a 


Support code TOOLSSRC/APIIBM,APIMSG: 

Code: 
     d RtvMsg          pr                  extpgm('QMHRTVM') 
     d  messageinfo               32000a 
     d  messageinfol                 10i 0 const 
     d  format                        8a   const 
     d  msgid                         7a   const 
     d  msgfile                      20a   const 
     d  replaceval                32000a   const options(*varsize) 
     d  replacevall                  10i 0 const 
     d  substitute                   10a   const 
     d  rtnfmtctrl                   10a   const 
     d  errorcode                   272a   options(*varsize) 
      * 
     d  rtvoption                    10a   const options(*nopass) 
     d  ccsidconvert                 10i 0 const options(*nopass) 
     d  ccsidreplace                 10i 0 const options(*nopass) 

     d rtvm0100        ds 
     d  rtvm01_bytret                10i 0 
     d  rtvm01_bytava                10i 0 
     d  rtvm01_msgret                10i 0 
     d  rtvm01_msgava                10i 0 
     d  rtvm01_hlpret                10i 0 
     d  rtvm01_hlpava                10i 0 
     d  rtvm01_data               32000a 



Loyd Goodbar
Senior programmer/analyst
BorgWarner
E/TS Water Valley
662-473-5713
-----Original Message-----
From: Rick.Chevalier@xxxxxxxxxxxxxxx [mailto:Rick.Chevalier@xxxxxxxxxxxxxxx]

Sent: Friday, August 13, 2004 09:11
To: midrange-l@xxxxxxxxxxxx
Subject: RE: SQL status code values

Chris,

The SQL errors returned by SQLCOD correspond to SQL error messages.  The
following shows how to convert them to messages.

1. Determine the SQL code.  Variable name is SQLCOD.
2. If the SQL code is negative, replace the '-' with a zero and concatenate
the last 4 positions to 'SQL'.  
3. If the SQL code is positive and < 10,000 concatenate the last 4 positions
to 'SQL'.
4. If the SQL code is > 10,000 concatenate the last 5 positions to 'SQ'.
5. You should now have a message number in the form SQLXXXX or SQXXXXX.
6. Run the command DSPMSGD SQLXXXX MSGF(QSQLMSG)

In code it looks like this:
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;                                         

HTH,

Rick
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of c.chambers@xxxxxxx
Sent: Friday, August 13, 2004 8:26 AM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL status code values


Can - anyone direct me to a list of values returned in the SQLSTATE keyword
in embedded SQL statements.
 
Or more specifically what does Status 42704 mean?
 
I have searched in vain 
 
Regards etc,
 
Chris