INF: Using a Prepared Cursor in Embedded SQL for COBOL

  - Microsoft Embedded SQL version 4.2 for COBOL

SUMMARY

The code listed below demonstrates how to use a prepared cursor to return multiple-row result sets from Microsoft's SQL Server using an Embedded SQL for COBOL program.

For more information about cursors, see the "Microsoft Embedded SQL for COBOL Programmer's Reference."

MORE INFORMATION

In many programs, each SQL batch (that is, EXEC SQL END-EXEC) is precompiled into a stored procedure on a SQL server. This makes it impossible to run that program on any other SQL server. One way to work around this is to use a prepared cursor to view data. With prepared cursors, programs do not need to be compiled for each SQL server that might be used because the statement is sent to the SQL server as a batch at run time. However, this does require extra coding to connect to the SQL server.

The program below connects to any server that is viewable from the workstation and queries the master..syslogins table to view the login accounts for a particular database.

The basic structure is as follows:

1. Get a servername and username from the user.

2. Connect to that server using execute immediate so that this will not be precompiled as a stored procedure on a particular SQL server, demonstrating another way around precompiled stored procedures.

3. Set the current connection to that server. This is used to allow multiple server connections.

4. Get a database name from the user to query for logins.

5. Declare the cursor, naming a prepared statement to be used.

6. Prepare the statement that the cursor will use with a parameter. The question mark (?) denotes a parameter that will be replaced by a program variable when the cursor is opened.

7. Open the cursor with a program variable as the parameter.

8. Fetch each row, ignoring truncated character string warnings (this sets sqlcode = 1) until an error is returned (sqlcode = -1), or no more rows are available (sqlcode = 100).

9. Close the cursor.

10. Disconnect from the server.

Sample Code

       WORKING-STORAGE SECTION.

       EXEC SQL INCLUDE SQLCA END-EXEC

       EXEC SQL BEGIN DECLARE SECTION END-EXEC
       01  server-name     pic x(80).
       01  user-name       pic x(80).
       01  sys-suid        pic s9(4) packed-decimal.
       01  sys-status      pic s9(4) packed-decimal.
       01  sys-accdate     pic x(11).
       01  sys-dbname      pic x(30).
       01  sys-name        pic x(30).
       01  prep            pic x(255).
       EXEC SQL END DECLARE SECTION END-EXEC

       PROCEDURE DIVISION.


      * Connect to server, execute immediate needed because this *
      * can't be pre-compiled into a stored procedure. *
       display "Enter servername[.database]:" accept server-name
       display "Enter username[.password]:" accept user-name
       EXEC SQL
         connect to :server-name as conn user :user-name
       END-EXEC
       if sqlcode not = 0
         perform sql-error
       end-if
       EXEC SQL
         set connection conn
       END-EXEC
       if sqlcode not = 0
         perform sql-error
         stop run
       end-if
      * Declare the cursor, prepare the select, open the cursor, *
      * fetch results, and close the cursor. *
       display "Enter a database:" accept sys-dbname
       EXEC SQL
         declare cursor-select cursor for prepared-select
       END-EXEC
       if sqlcode not = 0
         perform sql-error
       else
      * The '?' is a parameter marker that will be replaced by a *
      * program variable when the cursor is opened. *
         move "select suid, status, convert(char(11),accdate), dbname,
      -    "name from master..syslogins where dbname = ?" to prep
         EXEC SQL
           prepare prepared-select from :prep
         END-EXEC
         if sqlcode not = 0
           perform sql-error
         else
           EXEC SQL
             open cursor-select using :sys-dbname
           END-EXEC
           if sqlcode not = 0
             perform sql-error
           else
             display spaces
             display "suid  status  accdate      dbname
      -              "        name"
             display "----- ------  -----------  ----------------------
      -              "------- ----------------------"

      * When no more rows are returned, sqlcode = 0. *
             perform fetch-rows until sqlcode < 0 or sqlcode = 100
             EXEC SQL
             close cursor-select
             END-EXEC
             if sqlcode not = 0
               perform sql-error
             end-if
           end-if
         end-if
       end-if
       EXEC SQL
         disconnect conn
       END-EXEC
       if sqlcode not = 0
         perform sql-error
       end-if
       stop run.

       fetch-rows.
      * Fetch the next row from the table and display it. *
       EXEC SQL
         fetch cursor-select into :sys-suid, :sys-status, :sys-accdate,
                                  :sys-dbname, :sys-name
       END-EXEC
      * Ignore warnings about truncation of character strings. *
       if sqlcode = 0 or sqlcode = 1
         display sys-suid" "sys-status"   "sys-accdate"
                 "sys-dbname " "   sys-name
       else
         if sqlcode not equal 100           *> 100 when no more rows
           perform sql-error
         end-if
       end-if.

       sql-error.
      * SQL Server error message handler.
      * Return codes *
       if sqlcode not equal 1
         display "SQL error SQLCODE=" sqlcode
       end-if
      * Errors *
       if sqlcode equal -1
         display "MESSAGE=" sqlerrmc
         display "SQLERRD(1)="sqlerrd(1) " SQLERRD(2)="sqlerrd(2)
       end-if
      * Warnings *
       if sqlcode equal 1
         if sqlwarn0 = "W"
           if sqlwarn1 = "W"
             display "CHARACTER STRING TRUNCATED DURING OUTPUT BIND"
           end-if
           if sqlwarn2 = "W"
             display "NULL VALUES WERE TRUNCATED"
           end-if
           if sqlwarn3 = "W"
             display "# OF COLUMNS RETURNED DOESN'T MATCH # OF HVARS"
           if sqlwarn4 = "W"
             display "AN UPDATE OR DELETE CLAUSE DIDN'T HAVE A WHERE"
           end-if
         end-if
       end-if.