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

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/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.

 

 

Environment

Release: All releases
Component:

Resolution

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 <sql.schema>.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 IDMS SQL Reference, section SQL Communication Area, SQLSTATE.