This sample demonstrates the use of cursors for a dynamic SQL SELECT statement. It is a slight modification of the static cursor example. If you have not yet looked at Static Cursor sample, it would be helpful to do so before looking at this sample. See Static cursor sample.
For information on where the source code can be found and how to build this sample program, see Sample embedded SQL programs.
The dcur program allows the user to select a table to look at with the n command. The program then presents as much information from that table as fits on the screen.
When this program is run, it prompts for a connection string of the form:
The C program with the embedded SQL is held in the samples-dir\SQLAnywhere\C directory. For Windows CE, a dynamic cursor example is located in the samples-dir\SQLAnywhere\CE\esql_sample directory. The program looks much like the static cursor sample with the exception of the connect, open_cursor, and print functions.
The connect function uses the embedded SQL interface function db_string_connect to connect to the database. This function provides the extra functionality to support the connection string that is used to connect to the database.
The open_cursor routine first builds the SELECT statement
SELECT * FROM table-name
where table-name is a parameter passed to the routine. It then prepares a dynamic SQL statement using this string.
The embedded SQL DESCRIBE command is used to fill in the SQLDA structure the results of the SELECT statement.
Size of the SQLDA
An initial guess is taken for the size of the SQLDA (3). If this is not big enough, the actual size of the select list returned by the database server is used to allocate a SQLDA of the correct size.
The SQLDA structure is then filled with buffers to hold strings that represent the results of the query. The fill_s_sqlda routine converts all data types in the SQLDA to DT_STRING and allocates buffers of the appropriate size.
A cursor is then declared and opened for this statement. The rest of the routines for moving and closing the cursor remain the same.
The fetch routine is slightly different: it puts the results into the SQLDA structure instead of into a list of host variables. The print routine has changed significantly to print results from the SQLDA structure up to the width of the screen. The print routine also uses the name fields of the SQLDA to print headings for each column.