Microsoft KB Archive/66750

= INF: Using a Prepared Cursor in Embedded SQL for COBOL =

PSS ID Number: 66750

Article Last Modified on 2/14/2005

-

The information in this article applies to:


 * Microsoft Embedded SQL for COBOL 4.2

-



This article was previously published under Q66750



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 &quot;Microsoft Embedded SQL for COBOL Programmer's Reference.&quot;



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 &quot;Enter servername[.database]:&quot; accept server-name display &quot;Enter username[.password]:&quot; 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 &quot;Enter a database:&quot; 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 &quot;select suid, status, convert(char(11),accdate), dbname, -   &quot;name from master..syslogins where dbname = ?&quot; 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 &quot;suid status  accdate      dbname -             &quot;        name&quot; display &quot;- -- ---  -- -             &quot;--- --&quot;

* 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&quot; &quot;sys-status&quot;  &quot;sys-accdate&quot; &quot;sys-dbname &quot; &quot;  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 &quot;SQL error SQLCODE=&quot; sqlcode end-if * Errors * if sqlcode equal -1 display &quot;MESSAGE=&quot; sqlerrmc display &quot;SQLERRD(1)=&quot;sqlerrd(1) &quot; SQLERRD(2)=&quot;sqlerrd(2) end-if * Warnings * if sqlcode equal 1 if sqlwarn0 = &quot;W&quot; if sqlwarn1 = &quot;W&quot; display &quot;CHARACTER STRING TRUNCATED DURING OUTPUT BIND&quot; end-if if sqlwarn2 = &quot;W&quot; display &quot;NULL VALUES WERE TRUNCATED&quot; end-if if sqlwarn3 = &quot;W&quot; display &quot;# OF COLUMNS RETURNED DOESN'T MATCH # OF HVARS&quot; if sqlwarn4 = &quot;W&quot; display &quot;AN UPDATE OR DELETE CLAUSE DIDN'T HAVE A WHERE&quot; end-if end-if end-if.

Additional query words: Embedded SQL COBOL

Keywords: kbprogramming KB66750

Technology: kbAudDeveloper kbSQLEmCOBOL kbSQLServSearch kbZNotKeyword3

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.