Highlighted
Absent Member.
Absent Member.
2209 views

COBOL DB2 PROCEDURE

Jump to solution

I am new to COBOL language. After I studied through the tutorials with Visual COBOL.  I tried to write my first DB2 stored procedure.

This is my environment setup:

Windows 10 Enterprise Edition.    I installed DB2 10.5 Express Edition , Visual Studio 2015 Enterprise Edition, Visual COBOL 2.3  personal edition.

I compile the following code as Library Project, output as DB2PROC.dll

       identification division.
       program-id. DB2PROC.

       environment division.
       configuration section.

       data division.
       
       linkage section.
       01 INPARAM      PIC X(20).                 
       01 OUTPARAM  PIC X(80).

       procedure division USING INPARAM
                                                      OUTPARAM.
       string INPARAM delimited by spaces  
              ' COBOL WORLD!!!'delimited by space  into OUTPARAM
           goback.
       end program DB2PROC.


I copied DB2PROC.dll into IBM\SQLLIB\FUNCTION  folder, I also copied COBOL run-time library  cblrtsm.dll in the same folder.

Then I created DB2 stored procedure:

CREATE PROCEDURE DB2PROC
(IN INPARAM CHAR(20), OUT OUTPARAM  CHAR(80))
RESULT SETS 0
MODIFIES SQL DATA
LANGUAGE COBOL
EXTERNAL NAME  'C:\Program Files\IBM\SQLLIB\FUNCTION\DB2PROC.dll'
FENCED
NOT THREADSAFE
NO DBINFO
PROGRAM TYPE SUB
PARAMETER STYLE GENERAL;

From  IBM data studio, when I call that procedure, it gives me error:

[jcc][t4][1065][12306][3.68.61] Caught java.io.CharConversionException.  See attached Throwable for details. ERRORCODE=-4220, SQLSTATE=null
Run of routine failed.
 - Roll back completed successfully.

But if I change the 2nd parameter as INOUT

CREATE PROCEDURE DB2PROC
(IN INPARAM CHAR(20), INOUT OUTPARAM  CHAR(80))

The error is gone,  I can run it successfully.  but OUTPARAM does not return anything to me.

Could someone help me to point out what is wrong with my code?

Really appreciated.

0 Likes
1 Solution

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: COBOL DB2 PROCEDURE

Jump to solution

There is an example program in the Visual COBOL Samples Browser that defines a DB2 SP in COBOL that uses IN, INOUT and OUT parameters.

I just tried it here and it worked perfectly. The script to define the procedure to DB2 is:

DROP PROCEDURE GETEMPSVR;

CREATE PROCEDURE GETEMPSVR

(IN  EMPNO CHAR(6),

 INOUT SQLCD    INT ,

 OUT FIRSTNME CHAR(12),

 OUT LASTNAME CHAR(15),

 OUT HIREDATE CHAR(10),

 OUT SALARY   DEC(9,2) )  

 DYNAMIC RESULT SETS 0

   LANGUAGE COBOL

   PARAMETER STYLE GENERAL

   NO DBINFO

   FENCED

   NOT THREADSAFE

   READS SQL DATA

   PROGRAM TYPE SUB

   EXTERNAL NAME "getempsvr!getempsvr"  ;

Open up the Samples Browser from the Start menu and select SQL in the left hand window and then Db2storedproc (VS) in the left hand side. This goes against the DB2 Sample database which needs to be installed.

Please give this a try and see if you can get it to work.

Thanks.

View solution in original post

4 Replies
Highlighted
Absent Member.
Absent Member.

RE: COBOL DB2 PROCEDURE

Jump to solution

I update my COBOL code as:

       identification division.
       program-id. DB2PROC.

       environment division.
       configuration section.

       data division.
       
       linkage section.
       01 INPARAM      PIC X(20).                 
       01 OUTPARAM  PIC X(80).
 
    

       procedure division USING INPARAM
                                OUTPARAM.
       string INPARAM delimited by spaces  
              ' COBOL WORLD!!!' into OUTPARAM
           goback.
       end program DB2PROC.

If I define second parameter as INOUT,  I can get the result back

If I define second parameter as OUT,  I get error:

Could be possible that  COBOL run-time library: cblrtsm.dll  does not support DB2 OUT parameter?

Or some trick I did not know?

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: COBOL DB2 PROCEDURE

Jump to solution

I use below code to verify that DB2 Express Edition does support OUT parameter.  I create a simple DB2 stored procedure:

CREATE OR REPLACE PROCEDURE DB2PROC_DB2 (IN INPARAM CHAR(20), OUT OUTPARAM  CHAR(80))

P1: BEGIN

    DECLARE v_INPARAM CHAR(20);

    SET v_INPARAM = INPARAM;

    SET OUTPARAM =  TRIM(v_INPARAM) || ' COBOL WORLD!!!';

END P1

I call above Stored Procedure,  OUT parameter works perfectly:

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: COBOL DB2 PROCEDURE

Jump to solution

There is an example program in the Visual COBOL Samples Browser that defines a DB2 SP in COBOL that uses IN, INOUT and OUT parameters.

I just tried it here and it worked perfectly. The script to define the procedure to DB2 is:

DROP PROCEDURE GETEMPSVR;

CREATE PROCEDURE GETEMPSVR

(IN  EMPNO CHAR(6),

 INOUT SQLCD    INT ,

 OUT FIRSTNME CHAR(12),

 OUT LASTNAME CHAR(15),

 OUT HIREDATE CHAR(10),

 OUT SALARY   DEC(9,2) )  

 DYNAMIC RESULT SETS 0

   LANGUAGE COBOL

   PARAMETER STYLE GENERAL

   NO DBINFO

   FENCED

   NOT THREADSAFE

   READS SQL DATA

   PROGRAM TYPE SUB

   EXTERNAL NAME "getempsvr!getempsvr"  ;

Open up the Samples Browser from the Start menu and select SQL in the left hand window and then Db2storedproc (VS) in the left hand side. This goes against the DB2 Sample database which needs to be installed.

Please give this a try and see if you can get it to work.

Thanks.

View solution in original post

Highlighted
Absent Member.
Absent Member.

RE: COBOL DB2 PROCEDURE

Jump to solution

Thank you , Chris

    I tested the sample,  it works.  

Then I compare the sample code with my own code.  I correct my code as :

       identification division.
       program-id. DB2OUT.

       environment division.
       configuration section.

       data division.
       
       linkage section.
       01 INPARAM      PIC X(20).                 
       01 OUTPARAM     PIC X(80).
       procedure division USING INPARAM,
                                OUTPARAM.
       move spaces to OUTPARAM                      
       string INPARAM delimited by spaces  
              ' COBOL WORLD!!!' into OUTPARAM
           goback.
       end program DB2OUT.

The major update is,  I use comma (,) between 2 parameters now.

If I define the stored procedure with IN and INOUT,  I do not need to use comma in COBOL to separate the parameters

but if I define the stored procedure with IN and OUT.  I do need to use comma in COBOL (very tricky here) to void the run-time error.

Now, I saw the result correctly:

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.