Programming Interfaces Guide
   CHAPTER 5. The Open Client Interface     

Using SQL in Open Client applications

This section provides a very brief introduction to using SQL in Open Client applications, with a particular focus on Adaptive Server Anywhere-specific issues.

     For an introduction to the concepts, see Using SQL in Applications. For a complete description, see your Open Client documentation.

  Executing SQL statements

You send SQL statements to a database by including them in Client Library function calls. For example, the following pair of calls executes a DELETE statement:

ret = ct_command(cmd, CS_LANG_CMD,
                  "DELETE FROM employee
                   WHERE emp_id=105"
                 CS_NULLTERM, 
                 CS_UNUSED);
ret = ct_send(cmd);

The ct_command function is used for a wide range of purposes.

  Using prepared statements

The ct_dynamic function is used to manage prepared statements. This function takes a type parameter which describes the action you are taking.

  To use a prepared statement in Open Client:

1. Prepare the statement using the ct_dynamic function, with a CS_PREPARE type parameter.

2. Set statement parameters using ct_param.

3. Execute the statement using ct_dynamic with a CS_EXECUTE type parameter.

4. Free the resources associated with the statement using ct_dynamic with a CS_DEALLOC type parameter.

     For more information on using prepared statements in Open Client, see your Open Client documentation

  Using cursors

The ct_cursor function is used to manage cursors. This function takes a type parameter which describes the action you are taking.

Supported cursor types 

Not all the types of cursor that Adaptive Server Anywhere supports are available through the Open Client interface. You cannot use scroll cursors, dynamic scroll cursors, and insensitive cursors through Open Client.

Uniqueness and updateability are two properties of cursors. Cursors can be unique (each row carries primary key or uniqueness information, regardless of whether it is used by the application) or not. Cursors can be read only or updateable. If a cursor is updateable and not unique, performance may suffer, as no prefetching of rows is done in this case, regardless of the CS_CURSOR_ROWS setting (see below).

The steps in using cursors 

In contrast to some other interfaces, such as Embedded SQL, Open Client associates a cursor with a SQL statement expressed as a string. Embedded SQL first prepares a statement, and then the cursor is declared using the statement handle.

  To use cursors in Open Client:

1. To declare a cursor in Open Client, you use ct_cursor with CS_CURSOR_DECLARE as the type parameter.

2. After declaring a cursor, you can control how many rows are prefetched to the client side each time a row is fetched from the server by using ct_cursor with CS_CURSOR_ROWS as the type parameter.

   Storing prefetched rows at the client side cuts down the number of calls to the server, and this improves overall throughput as well as turnaround time. Prefetched rows are not immediately passed on to the application, they are stored in a buffer at the client side ready for use.

   The setting of the PREFETCH database option controls prefetching of rows for other interfaces. It is ignored by Open Client connections. The CS_CURSOR_ROWS setting is ignored for non-unique, updateable cursors.

3. To open a cursor in Open Client, you use ct_cursor with CS_CURSOR_OPEN as the type parameter.

4. To fetch each row in to the application, you use ct_fetch.

5. To close a cursor, you use ct_cursor with CS_CURSOR_CLOSE.

6. In Open Client, you also need to deallocate the resources associated with a cursor. You do this using ct_cursor with CS_CURSOR_DEALLOC. You can also use CS_CURSOR_CLOSE with the additional parameter CS_DEALLOC to carry out these operations in a single step.

   In Embedded SQL, cursors are not deallocated. Because Embedded SQL cursors are associated with prepared statements, the resources are associated with the statement itself, and you need to drop the statement in order to free the resources.

  Modifying rows through a cursor

With Open Client, you can delete or update rows in a cursor, as long as the cursor is for a single table. The user must have permissions to update the table and the cursor must be marked for update.

  To modify rows through a cursor:

1. Instead of carrying out a fetch, you can delete or update the current row of the cursor using ct_cursor with CS_CURSOR_DELETE or CS_CURSOR_UPDATE, respectively.

You cannot insert rows through a cursor in Open Client applications.

  Describing query results in Open Client

Open Client handles result sets in a different way from some other Adaptive Server Anywhere interfaces.

In Embedded SQL and ODBC, you describe a query or stored procedure in order to set up the proper number and types of variables to receive the results. The description is done on the statement itself.

In Open Client, you do not need to describe a statement. Instead, each row returned from the server can carry a description of its contents. If you use ct_command and ct_send to execute statements, you can use the ct_results function to handle all aspects of rows returned in queries.

If you do not wish to use this row-by-row method of handling result sets, you can use ct_dynamic to prepare a SQL statement, and use ct_describe to describe its result set. This corresponds more closely to the describing of SQL statements in other interfaces.