Call the LookupEMP Stored Procedure from COBOL

Takes you through the process of calling your published stored procedure from a COBOL program.

Save and close the SQLServerSP solution

  1. From the Visual Studio main menu, click File > Close Solution.
  2. If prompted to save, click OK.

Create the SQLServerSPCall project

  1. On the What would you like to do? dialog box, click Create a new project.
  2. On the Create a new project dialog box, scroll down the list, and select SQL Server Database Project, and then click Next.
    Attention: Be sure that you select the SQL Server Database Project template for COBOL and Windows as other templates with this name might exist.
  3. Complete the fields as follows:
    Project name SQLServerSPCall
    Location c:\tutorials\SQL
    Framework Choose the appropriate .NET Framework version from the drop-down list.
    Note: Must be version 4.5 or later.
  4. Check Place solution and project in the same directory.
  5. Click Create.

Add a 32-bit solution platform

Because Visual Studio runs in 32-bit, and the connection you've created using SQL Server runs in 64-bit, you need to add a 32-bit solution platform before you can execute your stored procedure.

  1. From the Solution Explorer, right-click the SQLServerSP solution; then select Configuration Manager.
  2. From the Active solution platform drop-down list, do one of the following:
    • If x86 is an option, select it.
    • If x86 is not an option:
      1. Select <New...>.
      2. From the Type or select the new platform drop-down list, select x86; then click OK.
  3. On the Configuration Manager, click Close.

Set project properties

  1. From the Solution Explorer, double-click Properties under your SQLServerSPCall project.
  2. On the SQL tab, select OpenESQL from the ESQL Preprocessor drop-down list.
  3. Click Add.
  4. On the Available Directives list, click DBMAN; then click OK.
  5. Repeat steps 3 and 4, but this time add the BEHAVIOR directive.
    Note: The default value for both DBMAN (ADO) and BEHAVIOR (OPTIMIZE) are correct, so you don't need to make any value changes.
  6. Click Save Save; then close the Properties window.

Code a COBOL program

You now code a COBOL program to call your stored procedure.

  1. If Program1.cbl is not open in the COBOL editor, double-click it from the Solution Explorer. If it is open, click its tab to bring it into focus.
  2. Replace all of the code in the program with the following code:
           program-id. Program1 as "SQLServerSPCall.Program1".
    
           data division.
            working-storage section.
            exec sql include sqlca end-exec.
    
            01 empid       PIC X(6).  *>string.
            01 lastname    PIC X(50). *>string.
            01 firstname   PIC X(50). *>string.
           
            01 connectString  string.
            01 spReturnCode binary-long.
           
            procedure division.
                exec sql connect to "LookupEMP" end-exec
        
                if sqlcode <> 0
                   display "CONNECT FAILED"
                end-if
            
                set empid to "000020"
                exec sql
                     :spReturnCode = call "LookupEMP" (:empid INOUT, :lastname OUT, :firstname OUT)
                end-exec
               
                if sqlcode <> 0
                     display "Call FAILED"
                else
                     display "User = " firstname " " lastname
                end-if
           
                exec sql disconnect all end-exec.
                goback.
               
            end program Program1.
    Note: You could also use the OpenESQL Assistant to generate the CALL statement from the Auxiliary Code tab and insert it into the program rather than coding it manually as done here.
  3. Click Save Save to save Program1.cbl.

Run the COBOL Program

  1. In the COBOL editor, insert a breakpoint at the goback statement.
  2. Press F5 to run the program in the debugger.
    When the debugger hits the breakpoint, you should see the following in a generated console window as a result of calling the stored procedure:
    User = THOMPSON                 MICHAEL
  3. Press F5 to stop debugging.