SQLSTATE value set in a PROCEDURE is not returned to application program.

Document ID : KB000023369
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction

If a PROCEDURE or TABLE PROCEDURE sets an SQLSTATE value, the SQLSTATE value returned to the calling program is always 38000, regardless of what was set.

Instructions

This is the way the interface was designed to work. In the following text, the word "procedure" is used to indicate both PROCEDURE and TABLE PROCEDURE.

If a procedure sets an SQLSTATE value, it must be of the form 38xxx. If it is, then the SQLSTATE returned to the SQL session is 38000. The value set by the procedure can be seen in the text accompanying the message. For example ...

The code may do something like this:

 
  IF DB-ANY-ERROR THEN DO.                    
      ...                                     
      MOVE '38001'      TO SQLPROC-SQLSTATE.  
      ...                                     
      LEAVE ADS.                              
  END.                                        
 

If a call to such a procedure drives the above code, the output will look something like this:

 
  SELECT * FROM IJHSQL.COUNTRY WHERE ID='DK';                               
  *+ Status = -4       SQLSTATE = 38000        Messages follow:             
  *+ DB001075 C-4M321: Procedure COUNTRYD exception 38001 SEQ=0002 STAT= 326

If the procedure sets a value that does not match 38xxx, then the message returned to SQL is DB001077 with an SQLSTATE of 39001, as opposed to the DB001075/38000.

This behavior is consistent whether the procedure is being invoked from OCF, IDMSBCF or an application program.

Additional Information

For more information, see the CA IDMS SQL Reference Guide, Appendix C "SQL Communication Area", "SQLSTATE".