Sybase Technical Library - Product Manuals Home
[Search Forms] [Previous Section with Hits] [Next Section with Hits] [Clear Search] Expand Search
Changing the Transformation mode [Table of Contents] Chapter 7 Using Sybase mode commands

Access Service User's Guide DirectConnect for AS/400

[-] Chapter 6 Issuing SQL statements
[-] SQL Transformation modes
[-] Types of commands

Types of commands

As a developer, you can issue the following types of commands:

The first three topics are presented in this chapter. RPC requests are described in Chapter 8, "Issuing Remote Procedure Calls"

Language commands

Language commands are the simplest way to issue a request, but there are drawbacks to using them:

Long character-string substitution is not done for select statements.

For information about how to issue a language command, see the appropriate Sybase documentation.

Dynamic commands

Dynamic commands are available only with Open Client CT-Library System 10 or greater. This section describes how DirectConnect processes dynamic commands through the AS/400.

Dynamic commands allow an application to execute SQL statements, such as insert, update, and delete, that contain variables the values of which are determined at run-time, in the following manner:

Following is an example of a dynamic SQL command: 

insert into table2 (col1, col2) values (SALES1, SALES2)

Following is the same command using parameter markers: 

insert into table2 (col1, col2) values (?, ?)

The access service prepares the preceding statement. Then, the statement executes and substitutes the values SALES1 and SALES2 into the parameter markers.

You can prepare and execute dynamic SQL statements by mapping these commands to AS/400 capabilities. For example:

For more information about how to prepare and execute statements with dynamic SQL, see the Sybase Open Client Client-Library/C Reference Manual and the IBM AS/400 DB2/400 SQL Reference Manual.

Capabilities and limitations of dynamic commands

Following is a description of dynamic command capabilities that DirectConnect supports, as well as the limitations that apply.

For general information about dynamic SQL and dynamic commands, see the Sybase Open Client Embedded SQL Programmer's Guide and the IBM AS/400 DB2/400 SQL Reference manual.

The access service does not support Embedded SQL (ESQL). However, OmniConnect (ASE/CIS) does support Embedded SQL. You may configure OmniConnect (ASE/CIS) in front of DirectConnect to support this functionality.

Using Dynamic commands

When you write an application using dynamic commands, consider the following:

Following is an example of a code fragment that prepares and executes an insert statement. 

/*
 ** This sample code fragment illustrates the CT-Library calls
 ** used to prepare and execute a SQL statement. Error handling
 ** and other details are omitted for the sake of brevity.
 ** NOTE: Ellipses in the following represent code that you must supply.
 */
     /*
     ** Prepare the statement.  The statement we will prepare is:
     ** INSERT INTO TEST VALUES (?, ?, ?, ?).
     ** We will name the dynamic statement DYN1.
     */
     retcode = ct_dynamic( cmd, CS_PREPARE, "DYN1", CS_NULLTERM,
         "INSERT INTO TEST VALUES (?, ?, ?, ?)", CS_NULLTERM );
     /*
     ** Send the batch and check results.
     */
     retcode = ct_send( cmd );
     retcode = handleresults( cmd );
     /*
     ** Now execute the prepared statement with a set of parameter
     ** values. Allocate a CS_DATAFMT structure for each parameter.
     */
     dfmt = malloc( 4 * sizeof(struct CS_DATAFMT) );
     /*
     ** Fill in the structure and set the datalength, null indicator,
     ** and data value for each parameter.
     */
     dfmt[0].datatype = CS_CHAR_TYPE;
     dfmt[0].status = CS_INPUTVAL;
     dfmt[0].maxlength = strlen( "col1val" );
     dataptr[0] = "col1val";
     datalen[0] = strlen( "col1val" );
     nullind[0] = 0;
     ...
     /*
     ** Execute the statement.
     */
     retcode = ct_dynamic( cmd, CS_EXECUTE, "DYN1", CS_NULLTERM,
         NULL, CS_UNUSED );
     /*
     ** Describe and send the parameters.
     */
     for (i=0; i<4; i++)
     {
         retcode = ct_param( cmd, &dfmt[i], dataptr[0], datalen[i], 
             nullind[i] );
     }
     /*
     ** Send the batch and check results.
     */
     retcode = ct_send( cmd );
     retcode = handleresults( cmd );

For more information about SQL processing, see the IBM AS/400 DB2/400 SQL Reference manual.

Error handling

Special error handling is not required.

Cursor commands

Cursor commands, which are available only with Open Client CT-Library System 10 or greater, give an application the power to retrieve and change data in a flexible way. For example, you can use cursor commands to process multiple result sets that are simultaneously available to the application, instead of one at a time using the language command.

Cursor commands require you to specify a SQL select statement that goes through SQL transformation, as follows:

The access service does not accept language event-based cursor commands. You must use ct_cursor commands.

DB-Library also supports a form of cursors but somewhat differently. The following table shows the differences between DB-Library cursors and CT-Library cursors.

Comparison of DB-Library and CT-Library cursors

Characteristic

DB-Library-based Cursor

CT-Library-based Cursor

Relationship to Adaptive Server cursor

Called an "emulated" or " client-side" cursor, does not correspond to an Adaptive Server cursor.

Called a "native" or "server-side" cursor, corresponds to an actual Adaptive Server cursor.

Cursor row position

Defined by the client.

Defined by the server.

Fetch capability

Can fetch backward .

Can fetch forward only.

Memory requirements

More memory is required if querying large row sizes, unless a smaller number of rows in the fetch buffer is specified.

No additional memory is required, regardless of the row sizes.

Access to Open Server application

Not available, unless required DB-Library stored procedures are installed.

Any System 10 or greater Open Server application is coded to support cursors.

The access service supports CT-Library-based cursors by mapping the corresponding Open Server commands to AS/400 capabilities. Because AS/400 cursors do not provide all the capabilities of Open Server cursors, some limitations apply and are included in the following section.

For more information about AS/400 capabilities related to commit and rollback statements, see the IBM AS/400 DB2/400 SQL Reference manual.

Capabilities and Limitations of Cursor Commands

Following is a description of cursor capabilities that DirectConnect supports, as well as the limitations that apply:

Using Read-Only cursors

When you write an application using read-only cursors, consider the following:

Read-only cursors have two fetch modes, called blocking and nonblocking:

The following code fragment shows the sequence of CT-Library calls used to declare, open, fetch, close, and free a read-only cursor. 

/*
 ** This sample code fragment illustrates the CT-Library calls that are 
 ** used to declare, open, fetch, close and deallocate a read-only
 ** cursor. Error handling and other details are omitted for the sake of
 ** brevity.
 ** NOTE: Ellipses in the following represent code that you must supply.
 */
     /*
     ** Initialize CT-Lib, establish a connection to DirectConnect.
     */
     ...
     /*
     ** Set cursor behavior on COMMIT so that cursors will maintain
     ** their position and not be closed.
     */
     retcode = ct_cmd_alloc( connection, &cmd );
     strcpy( lang, "SET CLOSEONENDTRAN OFF" );
     retcode = ct_command( cmd, CS_LANG_CMD, lang, CS_NULLTERM,
         CS_UNUSED );
     /*
     ** Send the batch and check results.
     */
     retcode = ct_send( cmd );
     retcode = handleresults( cmd );
     /*
     ** We are going to declare the cursor on the statement:
     **      SELECT * FROM AUTHORS WHERE STATE = ?
     **
     ** Allocate a CS_DATAFORMAT structure for the parameter and fill 
     ** in the relevant fields. In this case, the parameter is char(2).
     */
     dfmt = malloc( sizeof(CS_DATAFMT) );
     memset( dfmt, 0, sizeof(CS_DATAFMT) );
     dfmt->status = CS_INPUTVALUE;
     dfmt->format = CS_UNUSED;
     dfmt->datatype = CS_CHAR_TYPE;
     /*
     **     Declare the cursor, using cursor name "CURS1".
     */
     retcode = ct_cursor( cmd, CS_CURSOR_DECLARE, "CURS1", CS_NULLTERM,
         "SELECT * FROM AUTHORS WHERE STATE = ?", CS_NULLTERM,
         CS_READ_ONLY );
     /*
     ** Describe the parameter.
     */
     retcode = ct_param( cmd, &dfmt, NULL, CS_UNUSED, 0 );
     /*
     ** Set the fetch count to 20.
     */
     retcode = ct_cursor( cmd, CS_CURSOR_ROWS, NULL, CS_UNUSED,
         NULL, CS_UNUSED, 20 );
     /*
     ** Send the batch and check results.
     */
     retcode = ct_send( cmd );
     retcode = handleresults( cmd );
     /*
     ** Now open the cursor with host variable value = 'CA'
     */
     strcpy( parmval, "CA" );
     datlen = 2;
     nullind = 0;
     retcode = ct_cursor( cmd, CS_CURSOR_OPEN, NULL, CS_UNUSED, NULL,
         CS_UNUSED, CS_UNUSED );
     /*
     ** Send the parameter.
     */
     retcode = ct_param( cmd, &dfmt, parmval, datlen, nullind );
     /*
     ** Send the batch and check results.
     */
     retcode = ct_send( cmd );
     retcode = handleresults( cmd );
     /*
     ** Describe and bind the result set.
     ** Find out how many columns there are in this result set.
     ** Make sure that there is at least 1 column.
     */
     retcode = ct_res_info( cmd, CS_NUMDATA, &ncols, CS_UNUSED, NULL );
     /*
     ** Allocate memory for each column's CS_DATAFMT, a data pointer,
     ** data length, and null indicator.
     */
     ...
     /*
     ** Loop through the columns getting a description of each one and
     ** binding each one to a program variable.
     */
     for (i = 0; i < ncols; i++)
     {
         /*
         ** Get the column description. ct_describe() fills the
         ** datafmt parameter with a description of the column.
         */
         retcode = ct_describe( cmd, (i + 1), &(coldata[i].dfmt) );
         /*
         ** Update the datafmt structure to indicate the form in
         ** which we want to get the data. Set the datatype,
         ** format, maximum length, etc.
         */
         ...
         /*
         ** Allocate memory for the actual column data.
         */
         ...
         /*
         ** Now bind.
         */
         retcode = ct_bind( cmd, (i + 1), &(coldata[i].dfmt ),
         coldata[i].data, &( coldata[i].datlen ),&( coldata
         [i].nullind) );
     }
     /*
     ** Fetch 1 row.
     */
     retcode = ct_fetch( cmd, CS_UNUSED, CS_UNUSED, CS_UNUSED,
         &rows_read );
     /*
     ** Check the results of the fetch.
     */
     if (retcode == CS_ROW_FAIL)
     {
     /*
     ** Fetch failed.
     */
     ...
     }
     else if (retcode != CS_SUCCEED)
 {
     if (retcode == CS_END_DATA)
     {
         /*
         ** End of data has been reached.
         */
         retcode = handleresults( cmd );
         goto CLOSECURS;
     }
     else
     {
         /*
         ** Fetch failed.
         */
         ...
     }
 }
 /*
 ** We have a row of data.
 */
 ...
 CLOSECURS:
 /*
 ** Close and free the cursor.
 */
 retcode = ct_cursor( cmd, CS_CURSOR_CLOSE, NULL, CS_UNUSED, NULL,
 CS_UNUSED, CS_DEALLOC );
 /*
 ** Send the batch and check results.
 */
 retcode = ct_send( cmd );
 retcode = handleresults( cmd );

Using updatable cursors

When you write an application using updatable cursors, consider the following:

Following is a code fragment that shows the sequence of CT-Library calls used to delete and update a row on an updatable cursor. 

/*
 ** This sample code fragment illustrates the CT-Library calls 
 ** used to update and delete a row on an updatable cursor.
 **
 ** Error handling and other details are omitted for the sake of brevity.
 ** NOTE: Ellipses in the following represent code that you must supply.
 */
     /*
     ** See the previous example for the cursor declare, open and
     ** fetch. The differences are:
     ** 1. The statement to declare is
     **    SELECT * FROM AUTHORS WHERE STATE = ? FOR UPDATE OF PHONE
     ** 2. The declare must specify CS_FOR_UPDATE instead of 
         CS_READ_ONLY.
     ** 3. The setting of the fetch count is eliminated.
     */
     /*
     ** Assume that you have fetched rows (one at a time) until you see
     ** a row you want to delete. This code deletes the most recently
     ** fetched row.
     */
     retcode = ct_cursor( cmd, CS_CURSOR_DELETE, "AUTHORS",
         CS_NULLTERM, NULL,        CS_UNUSED, CS_UNUSED );
     /*
     ** Send the batch and check results.
     */
     retcode = ct_send( cmd );
     retcode = handleresults( cmd );
     /*
     ** Fetch more rows until we see a row that we want to update.
     */
     ...
     /*
     ** Update the most recently-fetched row.  For this example, we 
     ** will update the PHONE column in the AUTHORS table, and we
     ** will use a parameter in the update statement for the value of
     ** PHONE. Fill in the CS_DATAFMT structure for the parameter and 
     ** the parmval, atlen, and nullind values.
     */
     dfmt.status = CS_INPUTVALUE;
     dfmt.datatype = CS_CHAR_TYPE;
     strcpy( parmval, "303-443-2706" );
     datlen = strlen( "303-443-2706" );
     nullind = 0;
     /*
     ** Send the cursor update.
     */
     retcode = ct_cursor( cmd, CS_CURSOR_UPDATE, "AUTHORS",
         CS_NULLTERM,"UPDATE AUTHORS SET PHONE = ?", CS_NULLTERM,
         CS_UNUSED );
     /*
     ** Send the parameter description and value.
     */
     retcode = ct_param( cmd, &dfmt, parmval, datlen, nullind );
     /*
     ** Send the batch and check results.
     */
     retcode = ct_send( cmd );
     retcode = handleresults( cmd );

Error handling for cursor declare and open commands

In general, the client application handles errors the same as any other CT-Library application. However, handling errors from a cursor declare or cursor open requires special consideration, as described next.

Cursor declare

If a cursor declare fails because, for example, a table does not exist or the user does not have sufficient privilege to access a table, the application should issue a ct_cancel and drop the command. This cleans up the necessary structures in Open Client/Server and synchronizes the client with both the access service and the AS/400.

Cursor open

If a cursor open fails because, for example, the number of parameters described in the open does not match the number of parameter markers in the declare, then the client application should close and free the cursor, and then drop the command. The client application should not issue a ct_cancel in this case.

Warning!

If a cursor declare contains parameter markers but the cursor open does not describe any parameters, DirectConnect goes into an error state. As a result, the client's connection to the database is dropped when the next command is executed. To recover, the client application must close the connection to the access service and open a new one.

For more information about error handling, see the Sybase Open Client Client-Library/C Reference Manual.

Changing the Transformation mode [Table of Contents] Chapter 7 Using Sybase mode commands