Open ServerConnect Programmer's Reference for COBOL

Home

Open ServerConnect Programmer's Reference for COBOL

Chapter 2: Topics

Cursors

Open ServerConnect supports cursor transactions. This section explains what cursors are and how they work.

The Sybase generic Open Client libraries (DB-Library and Client-Library), Adaptive Server, and Open Server support cursors. Cursor support at the mainframe allows clients using these products to include cursors when accessing mainframe data.

Note: Open ClientConnect versions 2.0 and 3.x do not support cursors.

What Is a Cursor?

A cursor is a symbolic name that is linked with a SQL statement. Declaring a cursor establishes this link. The SQL statement can be one of the following:

A SQL select statement
A Transact-SQL execute statement
A dynamic SQL prepared statement

The SQL statement associated with a cursor is called the body of the cursor. When a client opens a cursor, it executes the body of the cursor, which in turn generates a result set. The Open ServerConnect application is responsible for detecting cursor requests and passing cursor results back to the client.

Benefits of Using Cursors

Cursors allow a client application to retrieve and change data in a powerful, flexible manner. They allow applications to access and move around in a set of data rows, rather than merely retrieve a complete result set.

Moreover, a single connection can have multiple cursors open at the same time. All of the cursor result sets are simultaneously available to the application, which can fetch them at will. This is in direct contrast to other types of result sets, which must be handled one row at a time, in a sequential fashion.

Further, a client application can update underlying database tables while actively fetching rows in a cursor result set.

How Cursors Work in Open ServerConnect

The following steps show how Open ServerConnect handles a cursor request:

Open ServerConnect receives a client request.
The Gateway-Library transaction determines the type of request by calling TDINFPGM and checking the value of the REQUEST-TYPE argument.
If the type of request is TDS-CURSOR-EVENT, the transaction calls TDCURPRO to determine what cursor command the client sent and which cursor is affected. The transaction then processes the command and returns results to the client.

Types of Cursor Commands

Table 2-4 summarizes the types of cursor commands a client can issue. The "Command" column in the table shows the value in the CURSOR-COMMAND field of the CURSOR-DESC structure.

Table 2-4: Summary of cursor commands
Command
Action
TDS-CURSOR-DECLARE
0x001
Associate a cursor ID with the body of the cursor.
TDS-CURSOR-OPENCMD
0x002
Execute the body of the cursor and generate a cursor result set.
TDS-CURSOR-FETCH
0x003
Fetch rows from the cursor result set.
TDS-CURSOR-DELETE
0x004
Delete the contents of the current cursor row.
TDS-CURSOR-UPDATE
0x005
Update the contents of the current cursor row.
TDS-CURSOR-INFO
0x006
Report the status of the cursor, or set the cursor row fetch count.
TDS-CURSOR-CLOSE
0x007
Make the cursor result set unavailable.
Reopening a cursor regenerates the cursor result set.
TDS-CURSOR-DEALLOC
0x008
Render the cursor nonexistent.
A cursor that is deallocated cannot be re-opened.

**Table 2-4: Summary of cursor commands**
Command	Action
TDS-CURSOR-DECLARE 0x001	Associate a cursor ID with the body of the cursor.
TDS-CURSOR-OPENCMD 0x002	Execute the body of the cursor and generate a cursor result set.
TDS-CURSOR-FETCH 0x003	Fetch rows from the cursor result set.
TDS-CURSOR-DELETE 0x004	Delete the contents of the current cursor row.
TDS-CURSOR-UPDATE 0x005	Update the contents of the current cursor row.
TDS-CURSOR-INFO 0x006	Report the status of the cursor, or set the cursor row fetch count.
TDS-CURSOR-CLOSE 0x007	Make the cursor result set unavailable. Reopening a cursor regenerates the cursor result set.
TDS-CURSOR-DEALLOC 0x008	Render the cursor nonexistent. A cursor that is deallocated cannot be re-opened.

A typical client application issues cursor commands in the order in which they are listed in Table 2-4: Summary of cursor commands, but the order can vary. For example, a client can fetch against a cursor, close the cursor, then reopen and fetch it again.

Because a client and server can exchange information about multiple cursors in a single connection session, they need a means of uniquely identifying each cursor. An Open ServerConnect application responds to a cursor declaration by sending back a unique cursor ID. The ID is an integer. The client and the server refer to the cursor by this ID for the lifetime of the cursor.

Declare Cursor

When the cursor command is TDS-CURSOR-DECLARE, the client is declaring a new cursor. In response, the Gateway-Library transaction calls the following functions:

Function
Action
TDCURPRO
Determine:
The type of cursor command (DECLARE, in this case)
Whether or not this cursor can be used to update database tables.
TDNUMPRM
Retrieve the number of parameters associated with the cursor.
TDINFPRM
Get the format of each associated parameter (once for each parameter).
TDRCVSQL
Retrieve the SQL text associated with the cursor.
[application logic]
[Declare the cursor to the application.]
TDCURPRO
Assign a cursor ID to the cursor.
TDSNDDON
Send the reply to the client.
TDGETREQ
Retrieve the next part of the cursor request.

Open Cursor

When the cursor command is TDS-CURSOR-OPENCMD, the client is executing the body of the cursor and generating a cursor result set. In response, the Gateway-Library transaction calls the following functions:

Function
Action
TDCURPRO
Determine the type of cursor command (OPEN, in this case).
TDNUMPRM
Retrieve the number of parameters associated with the cursor.
TDRCVPRM
Retrieve cursor parameters (once for each parameter).
TDCURPRO
Send the cursor status to the client.
TDESCRIB
Describe column results to the client (once for each column).
TDSETUDT
[optional] Set user datatype, if needed.
TDSNDDON
Send the reply to the client.
TDGETREQ
Retrieve the next part of the cursor request.

Fetch Rows

When the cursor command is TDS-CURSOR-FETCH, the client is fetching a row through a cursor. In response, the Gateway-Library transaction calls the following functions:

Function
Action
TDCURPRO
Determine the type of cursor command (FETCH).
[application logic]
[Adjust the cursor.]
TDSNDROW
Send back n rows of results.
TDSNDDON
Send the reply to the client.
TDGETREQ
Retrieve the next part of the cursor request.

Delete Cursor

When the cursor command is TDS-CURSOR-DELETE, the client is deleting the current cursor row. In response, the Gateway-Library transaction calls the following functions:

Function
Action
TDCURPRO
Determine the type of cursor command.
[application logic]
[Adjust the cursor.]
TDSNDDON
Send the reply to the client.
TDGETREQ
Retrieve the next part of the cursor request.

Update Cursor

When the cursor command is TDS-CURSOR-UPDATE, the client is updating the contents of the current cursor row. In response, the Gateway-Library transaction calls:

Function
Action
TDCURPRO
Determine the type of cursor command
TDRCVSQL
Retrieve the SQL text associated with the cursor.
[application logic]
[Adjust the cursor]
TDSNDDON
Send the reply to the client.

Request Cursor Status

The Client-Library command ct_cmd_props can request information on cursor options, identifiers, and status. When the cursor command is TDS-CURSOR-INFO with the option CUR-ASKSTATUS (ct_cmd_props (CS-CUR-STATUS)), the client is requesting the status of the cursor. In response, the Gateway-Library transaction calls the following functions:

Function
Action
TDCURPRO
Determine the type of cursor command.
TDCURPRO
Send the cursor status to the client.
TDSNDDON
Send the reply to the client.
TDGETREQ
Retrieve the next part of the cursor request.

Get Fetch Count

The Client-Library command ct_cursor can request cursor row information. When the cursor command is TDS-CURSOR-INFO with the option CUR-SETROW (ct_cursor (CS-CURSOR-ROW)), the client is setting the row fetch count. In response, the Gateway-Library transaction calls the following functions:

Function
Action
TDCURPRO
Determine:
The type of cursor command
The number of rows to be returned with each fetch command.
[application logic]
[Adjust the cursor.]
TDCURPRO
Send the cursor status to the client.
TDGETREQ
Retrieve the next part of the cursor request.

Close Cursor or Deallocate Cursor

When the cursor command is TDS-CURSOR-CLOSE, the client is requesting to close a cursor. This can be a request to both close and deallocate the cursor, or to close it only.

When the cursor command is TDS-CURSOR-DEALLOC, the client is requesting to deallocate a cursor.

In response to either command, the Gateway-Library transaction calls the following functions:

Function
Action
TDCURPRO
Determine:
The type of cursor command
Whether the cursor should also be deallocated.
[application logic]
[Close the cursor.]
TDCURPRO
Send the cursor status to the client.
TDSNDDON
Send the reply to the client.
TDGETREQ
Retrieve the next part of the cursor request.

CURSOR-DESC Structure

A CURSOR-DESC structure contains information about a cursor, including the following:

The cursor's unique ID
The type of cursor command most recently issued by the client
The status of the cursor

A CURSOR-DESC structure is defined in SYGWCOB as follows:

CURSOR-ID                 PIC S9(9) USAGE COMP SYNC.
NUMBER-OF-UPDATE-COLUMNS  PIC S9(9) USAGE COMP SYNC.
FETCH-COUNT               PIC S9(9) USAGE COMP SYNC.
CURSOR-STATUS             PIC S9(9) USAGE COMP SYNC.
CURSOR-COMMAND            PIC S9(9) USAGE COMP SYNC.
COMMAND-OPTIONS           PIC S9(9) USAGE COMP SYNC
FETCH-TYPE                PIC S9(9) USAGE COMP SYNC.
ROW-OFFSET                PIC S9(9) USAGE COMP SYNC.
CURSOR-NAME-LENGTH        PIC S9(9) USAGE COMP SYNC.
CURSOR-NAME               PIC X(30). 
TABLE-NAME-LENGTH         PIC S9(9) USAGE COMP-SYNC.
TABLE-NAME                PIC X(30).

Fields in a CURSOR-DESC Structure

The following table describes each field in a CURSOR-DESC structure:

Table 2-5: Fields in a CURSOR-DESC structure
Field Name
Description
Notes
CURSOR-ID
The current cursor identifier.
The Open ServerConnect application must set CURSOR-ID when responding to a TDS-CURSOR-DECLARE (DECLARE CURSOR) command from the client. This happens when the client sends a DECLARE CURSOR command that has CURSOR-NAME as a required parameter.
The Gateway-Library transaction receives the DECLARE CURSOR command from the client, calls TDCURPRO to specify a unique cursor identifier in the CURSOR-ID field, and returns the unique cursor ID to the client.
The client uses the unique cursor ID (instead of the initial cursor name) in the CURSOR-ID field of the CURSOR-DESC structure for all subsequent commands regarding this cursor.
NUMBER-OF-
UPDATE-
COLUMNS
The number of columns in a cursor update clause.
NUMBER-OF-UPDATE-COLUMNS is set to 0 if there are no update columns. This information is available at declare time.
FETCH-COUNT
The current row fetch count for this cursor (the number of rows that are sent to the client in response to a TDS-CURSOR-
FETCH command).
FETCH-COUNT is described when a TDS-CURSOR-INFO command is received from the client, or sent to the client in response to such a command. FETCH-COUNT is set to 1 if the client has not explicitly set a row fetch count. If the Open ServerConnect application cannot support the requested fetch count, it can set this field to a different value before responding.
CURSOR-STATUS
The status of the current cursor.
Open ServerConnect sets the cursor status in response to the cursor command received from the client. See Table 2-6 for a list of legal values.
CURSOR-
COMMAND
The current cursor command type.
See Table 2-7 for a list of legal values.
COMMAND-
OPTIONS
Any options associated with the cursor command.
Not all commands have associated options. The value of COMMAND-OPTIONS depends on the cursor command. Table 2-7 describes the possible values for COMMAND-OPTIONS.
FETCH-TYPE
The type of fetch requested by a client.
FETCH-TYPE is described when a TDS-CURSOR-FETCH command is received from the client. The valid fetch types and their meanings are as follows:
- TDS-NEXT - next row
- TDS-PREV - previous row
- TDS-FIRST - first row
- TDS-LAST - last row
- TDS-ABSOLUTE - row identified in the
ROW-OFFSET field
- TDS-RELATIVE - current row plus or
minus the value in the ROW-OFFSET field

Requests to Open ServerConnect always have a FETCH-TYPE of TDS-NEXT.
ROW-OFFSET
The row position for TDS-ABSOLUTE or TDS-RELATIVE fetches.
ROW-OFFSET is undefined for all other fetch types. ROW-OFFSET is described when a TDS-CURSOR-FETCH command is received from the client.
CURSOR-NAME-
LENGTH
The length of the cursor name in CURSOR-NAME.
CURSOR-NAME-LENGTH is zero if not used. If used, CURSOR-NAME-LENGTH is the actual length.
CURSOR-NAME
The name of the current cursor.
TABLE-NAME-
LENGTH
The length of the table name in TABLE-NAME.
TABLE-NAME-LENGTH is zero if not used. If used, TABLE-NAME-LENGTH is the actual length. TABLE-NAME-LENGTH is described when a TDS-CURSOR-UPDATE or TDS-CURSOR-DELETE command is received from the client.
TABLE-NAME
The table name associated with a cursor update or delete command.
TABLE-NAME is described when a TDS-CURSOR-UPDATE or TDS-CURSOR-DELETE command is received from the client.

**Table 2-5: Fields in a CURSOR-DESC structure**
Field Name	Description	Notes
CURSOR-ID	The current cursor identifier.	The Open ServerConnect application must set CURSOR-ID when responding to a TDS-CURSOR-DECLARE (DECLARE CURSOR) command from the client. This happens when the client sends a DECLARE CURSOR command that has CURSOR-NAME as a required parameter. The Gateway-Library transaction receives the DECLARE CURSOR command from the client, calls TDCURPRO to specify a unique cursor identifier in the CURSOR-ID field, and returns the unique cursor ID to the client. The client uses the unique cursor ID (instead of the initial cursor name) in the CURSOR-ID field of the CURSOR-DESC structure for all subsequent commands regarding this cursor.
NUMBER-OF- UPDATE- COLUMNS	The number of columns in a cursor update clause.	NUMBER-OF-UPDATE-COLUMNS is set to 0 if there are no update columns. This information is available at declare time.
FETCH-COUNT	The current row fetch count for this cursor (the number of rows that are sent to the client in response to a TDS-CURSOR- FETCH command).	FETCH-COUNT is described when a TDS-CURSOR-INFO command is received from the client, or sent to the client in response to such a command. FETCH-COUNT is set to 1 if the client has not explicitly set a row fetch count. If the Open ServerConnect application cannot support the requested fetch count, it can set this field to a different value before responding.
CURSOR-STATUS	The status of the current cursor.	Open ServerConnect sets the cursor status in response to the cursor command received from the client. See Table 2-6 for a list of legal values.
CURSOR- COMMAND	The current cursor command type.	See Table 2-7 for a list of legal values.
COMMAND- OPTIONS	Any options associated with the cursor command.	Not all commands have associated options. The value of COMMAND-OPTIONS depends on the cursor command. Table 2-7 describes the possible values for COMMAND-OPTIONS.
FETCH-TYPE	The type of fetch requested by a client.	FETCH-TYPE is described when a TDS-CURSOR-FETCH command is received from the client. The valid fetch types and their meanings are as follows: - TDS-NEXT - next row - TDS-PREV - previous row - TDS-FIRST - first row - TDS-LAST - last row - TDS-ABSOLUTE - row identified in the ROW-OFFSET field - TDS-RELATIVE - current row plus or minus the value in the ROW-OFFSET field Requests to Open ServerConnect always have a FETCH-TYPE of TDS-NEXT.
ROW-OFFSET	The row position for TDS-ABSOLUTE or TDS-RELATIVE fetches.	ROW-OFFSET is undefined for all other fetch types. ROW-OFFSET is described when a TDS-CURSOR-FETCH command is received from the client.
CURSOR-NAME- LENGTH	The length of the cursor name in CURSOR-NAME.	CURSOR-NAME-LENGTH is zero if not used. If used, CURSOR-NAME-LENGTH is the actual length.
CURSOR-NAME	The name of the current cursor.
TABLE-NAME- LENGTH	The length of the table name in TABLE-NAME.	TABLE-NAME-LENGTH is zero if not used. If used, TABLE-NAME-LENGTH is the actual length. TABLE-NAME-LENGTH is described when a TDS-CURSOR-UPDATE or TDS-CURSOR-DELETE command is received from the client.
TABLE-NAME	The table name associated with a cursor update or delete command.	TABLE-NAME is described when a TDS-CURSOR-UPDATE or TDS-CURSOR-DELETE command is received from the client.

Values for CURSOR-STATUS

The CURSOR-STATUS field of the CURSOR-DESC structure is a bit mask that can take any combination of the values described in the following table.

Table 2-6: Values for CURSOR-STATUS (CURSOR-DESC)
Value
Meaning
TDS-CURSTAT-DECLARED
The cursor is declared. This status is reset after the next cursor command is processed.
TDS-CURSTAT-OPEN
The cursor is open.
TDS-CURSTAT-ROWCNT
The cursor specified the number of rows that should be returned for the TDS-CURSOR-FETCH command.
TDS-CURSTAT-RDONLY
The cursor is read only; it cannot be updated. The Open ServerConnect application should return an error to the client if TDS-CURSOR-UPDATE or TDS-CURSOR-DELETE is received for this cursor.
TDS-CURSTAT-UPDATABLE
The cursor can be updated.
TDS-CURSTAT-CLOSED
The cursor is closed, but not deallocated. It can be opened again later. This status is also set upon declaration of a cursor. Open ServerConnect clears it when a TDS-CURSOR-OPEN is received and resets it when a TDS-CURSOR-CLOSE is received.
TDS-CURSTAT-DEALLOC
The cursor is closed and deallocated. No other status flags should be set at this time.

**Table 2-6: Values for *CURSOR-STATUS* (CURSOR-DESC)**
Value	Meaning
TDS-CURSTAT-DECLARED	The cursor is declared. This status is reset after the next cursor command is processed.
TDS-CURSTAT-OPEN	The cursor is open.
TDS-CURSTAT-ROWCNT	The cursor specified the number of rows that should be returned for the TDS-CURSOR-FETCH command.
TDS-CURSTAT-RDONLY	The cursor is read only; it cannot be updated. The Open ServerConnect application should return an error to the client if TDS-CURSOR-UPDATE or TDS-CURSOR-DELETE is received for this cursor.
TDS-CURSTAT-UPDATABLE	The cursor can be updated.
TDS-CURSTAT-CLOSED	The cursor is closed, but not deallocated. It can be opened again later. This status is also set upon declaration of a cursor. Open ServerConnect clears it when a TDS-CURSOR-OPEN is received and resets it when a TDS-CURSOR-CLOSE is received.
TDS-CURSTAT-DEALLOC	The cursor is closed and deallocated. No other status flags should be set at this time.

Values for CURSOR-COMMAND and COMMAND-OPTIONS

The CURSOR-COMMAND field of the CURSOR-DESC structure indicates the command to be processed. It can take one of the values described in the following table. TDCURPRO can update this field with the next command to process for a given cursor. The table also lists the relevant COMMAND-OPTIONS values.

Table 2-7: Values for CURSOR-COMMAND and COMMAND-OPTIONS
CURSOR-COMMAND Value
Meaning
Legal Values for COMMAND-OPTIONS
TDS-CURSOR-CLOSE
Cursor close command.
TDS-CURSOR-DEALLOC or TDS-CURSOR-UNUSED.
TDS-CURSOR-DEALLOC indicates that the cursor will never be reopened. The Open ServerConnect application should delete all associated cursor resources. The cursor ID number can be reused.
TDS-CURSOR-DECLARE
Cursor declare command. The application can obtain the actual text of the cursor statement through TDRCVSQL.
TDS-CURSOR-UPDATABLE, TDS-CURSOR-RDONLY, or TDS-CURSOR-DYNAMIC.
TDS-CURSOR-DYNAMIC indicates that the client is declaring the cursor against a dynamically prepared SQL statement. In this case, the text of the cursor statement is actually the name of the prepared statement.
TDS-CURSOR-DELETE
Cursor delete command. Performs a positional row delete through a cursor.
There are no valid options for this command. COMMAND-OPTIONS always has the value TDS-CURSOR-UNUSED.
TDS-CURSOR-FETCH
Cursor fetch command. Performs a row fetch through a cursor.
There are no valid options for this command. COMMAND-OPTIONS always has the value TDS-CURSOR-UNUSED.
TDS-CURSOR-INFO
Cursor information command. The client sends this command to the Open ServerConnect application to set the cursor row fetch count or to request cursor status information. The Open ServerConnect application sends this command to the client in response to any cursor command (including TDS-CURSOR-INFO itself) to describe the current cursor.
TDS-CURSOR-SETROWS when the client is describing the current row fetch count. The FETCH-COUNT field contains the requested fetch count.

TDS-CURSOR-ASKSTATUS when the client is requesting status information about the current cursor. This generally occurs when the client sends an attention and wants to see which cursors are still available afterwards. The CURSOR-ID field contains 0. The Open ServerConnect application should send back a TDS-CURSOR-INFO response for each cursor currently available.

TDS-CURSOR-INFORMSTATUS when the Open ServerConnect application is responding to a TDS-CURSOR-INFO command. The CURSOR-STATUS field contains the cursor status.
TDS-CURSOR-OPEN
Cursor open command.
TDS-CURSOR-HASARGS or TDS-CURSOR-UNUSED.
TDS-CURSOR-UPDATE
Cursor update command. Performs a positional row update through a cursor. The Open ServerConnect application can obtain the actual text of the cursor update statement by calling TDRCVSQL.
TDS-CURSOR-UNUSED.

Table 2-7: Values for *CURSOR-COMMAND* and *COMMAND-OPTIONS*
CURSOR-COMMAND Value	Meaning	Legal Values for COMMAND-OPTIONS
TDS-CURSOR-CLOSE	Cursor close command.	TDS-CURSOR-DEALLOC or TDS-CURSOR-UNUSED. TDS-CURSOR-DEALLOC indicates that the cursor will never be reopened. The Open ServerConnect application should delete all associated cursor resources. The cursor ID number can be reused.
TDS-CURSOR-DECLARE	Cursor declare command. The application can obtain the actual text of the cursor statement through TDRCVSQL.	TDS-CURSOR-UPDATABLE, TDS-CURSOR-RDONLY, or TDS-CURSOR-DYNAMIC. TDS-CURSOR-DYNAMIC indicates that the client is declaring the cursor against a dynamically prepared SQL statement. In this case, the text of the cursor statement is actually the name of the prepared statement.
TDS-CURSOR-DELETE	Cursor delete command. Performs a positional row delete through a cursor.	There are no valid options for this command. COMMAND-OPTIONS always has the value TDS-CURSOR-UNUSED.
TDS-CURSOR-FETCH	Cursor fetch command. Performs a row fetch through a cursor.	There are no valid options for this command. COMMAND-OPTIONS always has the value TDS-CURSOR-UNUSED.
TDS-CURSOR-INFO	Cursor information command. The client sends this command to the Open ServerConnect application to set the cursor row fetch count or to request cursor status information. The Open ServerConnect application sends this command to the client in response to any cursor command (including TDS-CURSOR-INFO itself) to describe the current cursor.	TDS-CURSOR-SETROWS when the client is describing the current row fetch count. The FETCH-COUNT field contains the requested fetch count. TDS-CURSOR-ASKSTATUS when the client is requesting status information about the current cursor. This generally occurs when the client sends an attention and wants to see which cursors are still available afterwards. The CURSOR-ID field contains 0. The Open ServerConnect application should send back a TDS-CURSOR-INFO response for each cursor currently available. TDS-CURSOR-INFORMSTATUS when the Open ServerConnect application is responding to a TDS-CURSOR-INFO command. The CURSOR-STATUS field contains the cursor status.
TDS-CURSOR-OPEN	Cursor open command.	TDS-CURSOR-HASARGS or TDS-CURSOR-UNUSED.
TDS-CURSOR-UPDATE	Cursor update command. Performs a positional row update through a cursor. The Open ServerConnect application can obtain the actual text of the cursor update statement by calling TDRCVSQL.	TDS-CURSOR-UNUSED.

Handling Cursor Requests

An Open ServerConnect application uses a TDS-CURSOR-EVENT handler to handle cursor requests. The handler includes code to detect which of the cursor commands was issued and to respond with the appropriate information.

The first task inside the event handler is to determine the current cursor and the cursor command that triggered the TDS-CURSOR-EVENT. It does this by calling TDCURPRO with the ACTION argument set to TDS-GET. Open ServerConnect fills the CURSOR-COMMAND field of the Open ServerConnect application CURSOR-DESC structure with the command type.

The application can then decide what other information it needs to retrieve, if any, as well as what data to send back to the client. In some cases, it may need to retrieve parameter formats and parameters; in others, it may want to know the status of the current cursor and the number of rows to fetch. It may only need to send back a TDS-CURSOR-INFO command, or it may need to send back result data or return parameters.

How to Respond to Specific Cursor Requests

This section contains information on how a TDS-CURSOR-EVENT handler should respond to specific types of cursor requests.

On each cursor declare request, the Open ServerConnect application must set a unique cursor identifier before TDCURPRO, with ACTION set to TDS-SET. Open ServerConnect sets CURSOR-STATUS and CURSOR-COMMAND in the CURSOR-DESC structure.

Table 2-8 summarizes the valid exchange of cursor requests and responses between a client and an Open ServerConnect application. The forward arrow indicates that ACTION is set to TDS-GET and the application is retrieving information from the client. The backward arrow indicates that ACTION is set to TDS-SET and the application is sending information to the client.

Table 2-8: Valid cursor requests and responses
Client Action
Open ServerConnect Application Response
Declares a cursor.

(CURSOR-COMMAND field of CURSOR-DESC contains TDS-CURSOR-DECLARE.)
� Retrieve CURSOR-COMMAND value from CURSOR-DESC.
(TDCURPRO)

� Retrieve number of cursor parameters, if any.
(TDNUMPRM)

� Retrieve format of cursor parameters, if any.
(TDINFPRM)

� Retrieve actual text of cursor command.
(TDRCVSQL)

� Set cursor ID. Set CURSOR-ID field to unique cursor ID.
(TDCURPRO)

� Send a DONE packet.
(TDSNDDON with STATUS argument set to TDS-DONE-FINAL.)
Requests the status of the current cursor or sends a fetch count.

(CURSOR-COMMAND field of CURSOR-DESC contains TDS-CURSOR-INFO.)
� Retrieve CURSOR-COMMAND, CURSOR-ID, and COMMAND-OPTIONS values from CURSOR-DESC structure.
(TDCURPRO)

� Send number of rows to be returned per fetch, if client set COMMAND-OPTIONS field to TDS-CURSOR-SETROWS.
(TDCURPRO)

� Send status of all available cursors, if client set COMMAND-OPTIONS field to TDS-CURSOR-ASKSTATUS.
Set CURSOR-ID field to cursor ID.
(TDCURPRO once for each active declared, opened or closed cursor.)

� Send a DONE packet.
(TDSNDDON with STATUS argument set to TDS-DONE-FINAL.)

Note:
If the client request is ct_cmd_props with cursor options, then CURSOR-COMMAND field is TDS-CURSOR-INFO with TDS-CURSOR-ASKSTATUS option.

If the client request is ct_cursor (CS-CURSOR-ROWS), then CURSOR-COMMAND field is TDS-CURSOR-INFO with TDS-CURSOR-SETROWS option.
Opens a cursor.

(CURSOR-COMMAND field of CURSOR-DESC contains TDS-CURSOR-OPEN.)
� Retrieve CURSOR-COMMAND and CURSOR-ID values from CURSOR-DESC structure.
(TDCURPRO)

� Retrieve number of cursor parameters, if any.
(TDNUMPRM)

� Retrieve format of cursor parameters and actual parameters, if any.
(TDINFPRM, TDRCVPRM)

� Send cursor status. Set CURSOR-ID to current cursor ID.
(TDCURPRO)

� Describe result row formats.
(TDESCRIB with TYPE argument set to TDS-ROWDATA.)

� Send a DONE packet.
(TDSNDDON with STATUS argument set to TDS-DONE-FINAL.)
Fetches rows.

(CURSOR-COMMAND field of CURSOR-DESC contains TDS-CURSOR-FETCH.)
� Retrieve CURSOR-COMMAND and CURSOR-ID values from CURSOR-DESC structure.
(TDCURPRO)
� Send result rows, FETCH-COUNT times.
(TDSNDROW)
� Send a DONE packet.
(TDSNDDON with STATUS argument set to TDS-DONE-FINAL.)
Sends a cursor close command.

(CURSOR-COMMAND field of CURSOR-DESC contains TDS-CURSOR-CLOSE.)
� Retrieve CURSOR-COMMAND and CURSOR-ID values from CURSOR-DESC structure.
(TDCURPRO)
� Send cursor status. Open ServerConnect sets cursor status, not the application.
(TDCURPRO)
� Send a DONE packet.
(TDSNDDON with STATUS argument set to TDS-DONE-FINAL.)
Updates a cursor.

CURSOR-COMMAND field of CURSOR-DESC contains TDS-CURSOR-UPDATE.)
� Retrieve CURSOR-COMMAND and CURSOR-ID values from CURSOR-DESC structure.
(TDCURPRO)
� Retrieve actual text of cursor command.
(TDRCVSQL)
� Send a DONE packet.
(TDSNDDON with STATUS argument set to TDS-DONE-FINAL.)
Deletes a cursor.

CURSOR-COMMAND field of CURSOR-DESC contains TDS-CURSOR-DELETE.)
� Retrieve CURSOR-COMMAND and CURSOR-ID values from CURSOR-DESC structure.
(TDCURPRO)
� Send a DONE packet.
(TDSNDDON with STATUS argument set to TDS-DONE-FINAL)

**Table 2-8: Valid cursor requests and responses**
Client Action	Open ServerConnect Application Response
Declares a cursor. (CURSOR-COMMAND field of CURSOR-DESC contains TDS-CURSOR-DECLARE.)	� Retrieve CURSOR-COMMAND value from CURSOR-DESC. (TDCURPRO) � Retrieve number of cursor parameters, if any. (TDNUMPRM) � Retrieve format of cursor parameters, if any. (TDINFPRM) � Retrieve actual text of cursor command. (TDRCVSQL) � Set cursor ID. Set CURSOR-ID field to unique cursor ID. (TDCURPRO) � Send a DONE packet. (TDSNDDON with STATUS argument set to TDS-DONE-FINAL.)
Requests the status of the current cursor or sends a fetch count. (CURSOR-COMMAND field of CURSOR-DESC contains TDS-CURSOR-INFO.)	� Retrieve CURSOR-COMMAND, CURSOR-ID, and COMMAND-OPTIONS values from CURSOR-DESC structure. (TDCURPRO) � Send number of rows to be returned per fetch, if client set COMMAND-OPTIONS field to TDS-CURSOR-SETROWS. (TDCURPRO) � Send status of all available cursors, if client set COMMAND-OPTIONS field to TDS-CURSOR-ASKSTATUS. Set CURSOR-ID field to cursor ID. (TDCURPRO once for each active declared, opened or closed cursor.) � Send a DONE packet. (TDSNDDON with STATUS argument set to TDS-DONE-FINAL.) Note: If the client request is ct_cmd_props with cursor options, then CURSOR-COMMAND field is TDS-CURSOR-INFO with TDS-CURSOR-ASKSTATUS option. If the client request is ct_cursor (CS-CURSOR-ROWS), then CURSOR-COMMAND field is TDS-CURSOR-INFO with TDS-CURSOR-SETROWS option.
Opens a cursor. (CURSOR-COMMAND field of CURSOR-DESC contains TDS-CURSOR-OPEN.)	� Retrieve CURSOR-COMMAND and CURSOR-ID values from CURSOR-DESC structure. (TDCURPRO) � Retrieve number of cursor parameters, if any. (TDNUMPRM) � Retrieve format of cursor parameters and actual parameters, if any. (TDINFPRM, TDRCVPRM) � Send cursor status. Set CURSOR-ID to current cursor ID. (TDCURPRO) � Describe result row formats. (TDESCRIB with TYPE argument set to TDS-ROWDATA.) � Send a DONE packet. (TDSNDDON with STATUS argument set to TDS-DONE-FINAL.)
Fetches rows. (CURSOR-COMMAND field of CURSOR-DESC contains TDS-CURSOR-FETCH.)	� Retrieve CURSOR-COMMAND and CURSOR-ID values from CURSOR-DESC structure. (TDCURPRO) � Send result rows, FETCH-COUNT times. (TDSNDROW) � Send a DONE packet. (TDSNDDON with STATUS argument set to TDS-DONE-FINAL.)
Sends a cursor close command. (CURSOR-COMMAND field of CURSOR-DESC contains TDS-CURSOR-CLOSE.)	� Retrieve CURSOR-COMMAND and CURSOR-ID values from CURSOR-DESC structure. (TDCURPRO) � Send cursor status. Open ServerConnect sets cursor status, not the application. (TDCURPRO) � Send a DONE packet. (TDSNDDON with STATUS argument set to TDS-DONE-FINAL.)
Updates a cursor. CURSOR-COMMAND field of CURSOR-DESC contains TDS-CURSOR-UPDATE.)	� Retrieve CURSOR-COMMAND and CURSOR-ID values from CURSOR-DESC structure. (TDCURPRO) � Retrieve actual text of cursor command. (TDRCVSQL) � Send a DONE packet. (TDSNDDON with STATUS argument set to TDS-DONE-FINAL.)
Deletes a cursor. CURSOR-COMMAND field of CURSOR-DESC contains TDS-CURSOR-DELETE.)	� Retrieve CURSOR-COMMAND and CURSOR-ID values from CURSOR-DESC structure. (TDCURPRO) � Send a DONE packet. (TDSNDDON with STATUS argument set to TDS-DONE-FINAL)

Additional Information:

The Open ServerConnect application response to a cursor command always concludes with a call to TDSNDDON with a status argument of TDS-DONE-FINAL.
After the Open ServerConnect application issues the first TDCURPRO command with ACTION set to TDS-SET, any further information the application sends applies to this cursor until a TDSNDDON with a STATUS argument of TDS-DONE-FINAL is issued.
Internally, Open ServerConnect replaces the parameter formats received when the client declares a cursor with those received when the client opens a cursor. This is necessary if the format of the parameter passed is not exactly the same as that of the parameter declaration. For example, a parameter may be declared as a TDS-INT, but the parameter being passed when the cursor is opened may be of type TDS-SMALLINT.
In response to a TDS-CURSOR-FETCH command, TDSNDROW sends a single row of data, and should be called as many times as the number in the current cursor's row fetch count.

Processing Cursor Requests

The Open ServerConnect application program uses TDINFPGM and TDGETREQ to determine what type of request the client sent. For cursor requests, the application processes cursor commands and generates result sets.

Multiple cursor commands per transaction invocation are not allowed because TRS can only pass one cursor command per TDS-CURSOR event. To process multiple commands, use the Open ServerConnect long-running transaction and accept each new command request with TDGETREQ.

Cursors are limited to SQL statements and cannot be used with other types of languages.

Cursor support is not available for Japanese or DBCS.

For example, if a client sends an OPEN CURSOR request to a DB2 application, the Open ServerConnect application is responsible for defining and executing the actual DB2 OPEN CURSOR command. Open ServerConnect is merely the transport mechanism for cursor commands.

Function	Action
TDCURPRO	Determine: The type of cursor command (DECLARE, in this case) Whether or not this cursor can be used to update database tables.
TDNUMPRM	Retrieve the number of parameters associated with the cursor.
TDINFPRM	Get the format of each associated parameter (once for each parameter).
TDRCVSQL	Retrieve the SQL text associated with the cursor.
[application logic]	[Declare the cursor to the application.]
TDCURPRO	Assign a cursor ID to the cursor.
TDSNDDON	Send the reply to the client.
TDGETREQ	Retrieve the next part of the cursor request.


	Expand Search