IDMS

We are using an SQL schema to access network database (No SQL tables are involved). We created a stored procedure (NOT Table stored procedure) that calls a COBOL DC program. The COBOL program does network DML calls and returns the results back to the stored procedure. The stored procedure is called by a Java program running on JBOSS server using JDBC drivers. We have used the sample programs in the books to get us here. We will appreciate if someone can share the following with us:

1. Techniques used to pass errors back from the COBOL program to the Java client. Are there any system level variables that can be leveraged ?

2. A Sample Stored Procedure definition and the called COBOL program. Again, we have used what is in books but will appreciate if someone can share a little more sophisticated use of features and definitions.

Thanks

Sat Pal 

Hi Sat,

    Your IDMS SQL Stored Procedure program can set the SQLSTATE of your query just like IDMS Table Procedure programs can.  The doc snippet below was taken from section entitled "Special Considerations for SQL-invoked External Routines" within the SQL Reference topic.  Note that, "SQL-invoked External Routines" includes table procedures, procedures and functions.  This section talks about error handling for sql-invoked external routines. 

__________________________________________________________________________________________________

Error Handling

The SQL-invoked external routine has two arguments to signal an exception condition back to CA IDMS. These arguments consist of a five-character SQLSTATE code and an 80-byte message area. The following table lists valid SQLSTATE codes and their descriptions.

CA IDMS examines the SQLSTATE value to determine whether the operation was successful. An SQLSTATE value of 0200 indicates that all rows have been returned. It is meaningful only on a Next Row request and cannot be set while at the same time returning a row since CA IDMS ignores parameter arguments if an SQLSTATE value of 02000 is set.

If an SQLSTATE value indicates that an error or warning condition exists, CA IDMS embeds the message text returned by the SQL-invoked external routine in a standard DB message and returns it to the calling application through the message area of the SQLCA. It also translates the SQLSTATE value into one of the following SQLCODE values:

If the SQL-invoked external routine signals an error, CA IDMS automatically rolls out all database changes that the SQL-invoked external routine made while processing the SQL statement that caused the SQL-invoked external routine to be invoked. For example, if the invoking SQL statement was a searched update and ten rows had been updated before the error was detected, changes to all ten rows are rolled out automatically. Database changes made prior to the execution of the searched update statement are not rolled out.

________________________________________________________________________________________________________________

Thanks to Carla Pereira for providing this info...

Dave

Thanks Dave and Carla.

I have a stored procedure that call a COBOL program. When I set the SQLSTATE in COBOL (Move '38100' to SQLSTATE), the stored and procedure and calling client (Java) does not receive the error. Is there some record I need to include in LINKAGE section to make this happen. In an ADS dialog, I can include ADSO-SQLPROC-COM-AREA to set the error that is then recognized by the stored procedure and calling client (Java). Is there an equivalent communication area in COBOL that needs to be included in LINKAGE section ? 

Hi, Sat,

I can see you have a support issue on this topic. 

I have replied with some comments in that issue.

Best regards,

Ian Hill.

Hi Sat, 

     The SQLSTATE variable must be referenced in the Parameter list of your COBOL SQL Procedure Program.   Have a look at the "Calling Arguments" topic under the topic entitled "Writing an External Procedure in COBOL, PL/I or Assembler". Here's a snippet from a working COBOL Table Procedure Program...   Note that all of these procedures have the same parm list:

PROCEDURE DIVISION USING
   E-ID
   E-NAME
   E-ADDRESS
   E-ID-I
   E-NAME-I
   E-ADDRESS-I
   RESULT-IND
   SQLSTATE
   PROCEDURE-NAME
   SPECIFIC-NAME
   MESSAGE-TEXT
   SQL-COMMAND-CODE
   SQL-OP-CODE

   (snip...).  

And a snippet of the LINKAGE SECTION:

LINKAGE SECTION.
   77 E-ID PIC 9(4).
   77 E-NAME PIC X(25).
   77 E-ADDRESS PIC X(46).
   77 E-ID-I PIC S9(4) COMP SYNC.
   77 E-NAME-I PIC S9(4) COMP SYNC.
   77 E-ADDRESS-I PIC S9(4) COMP SYNC.
   77 RESULT-IND PIC S9(4) COMP SYNC.
   77 SQLSTATE PIC X(5).
   77 PROCEDURE-NAME PIC X(18).
   77 SPECIFIC-NAME PIC X(8).
   77 MESSAGE-TEXT PIC X(80).
   01 SQL-COMMAND-CODE PIC S9(8) COMP SYNC.
   01 SQL-OP-CODE PIC S9(8) COMP SYNC.
      88 OPEN-SCAN VALUE +12.
      88 NEXT-ROW VALUE +16.
      88 CLOSE-SCAN VALUE +20.
      88 SUSPEND-SCAN VALUE +24.
      88 RESUME-SCAN VALUE +28.

Dave

Thank you Dave. Ian updated the CA support ticket with the info as well. This is exactly what I needed.

Sat Pal