ID Number: Q66750
1.10 1.11 4.20
OS/2
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.
Additional reference words: Embedded SQL for COBOL