Database examples

Programming Interfaces Guide
CHAPTER 2. The Embedded SQL Interface

Database examples

Two Embedded SQL examples are included with the Adaptive Server Anywhere installation. The static cursor Embedded SQL example, cur.sqc, demonstrates the use of static SQL statements. The dynamic cursor Embedded SQL example, dcur.sqc, demonstrates the use of dynamic SQL statements. In addition to these examples, you may find other programs and source files as part of the installation of Adaptive Server Anywhere which demonstrate features available for particular platforms.

File locations

Source code for the examples is installed as part of the Adaptive Server Anywhere installation. They are placed in the cxmp subdirectory of your Adaptive Server Anywhere installation directory.

Building the examples

Along with the sample program is a batch file, makeall.bat, that can be used to compile the sample program for the various environments and compilers supported by Adaptive Server Anywhere. For OS/2 the command is makeall.cmd. For UNIX, use the shell script makeall.

The format of the command is as follows:

makeall {Example} {Platform} {Compiler}

The first parameter is the name of the example program that you want to compile. It is one of:

cur static cursor example
dcur dynamic cursor example

The second parameter is the platform in which the program is run. The platform can be one of the following:

WINDOWS compile for 16-bit Windows.
WIN32 compile for 32-bit Windows 3.x using the Watcom 32-bit support.
WINNT compile for Windows NT.
OS232 compile for 32-bit OS/2.
NETWARE compile for Netware NLM.
QNX compile for QNX.

The third parameter is the compiler to use to compile the program. The compiler can be one of:

WC use Watcom C/C++
MC use Microsoft C
BC use Borland C
CS use IBM C Set++ or Visual Age

Running the example programs

Each example program presents a console-type user interface where it prompts you for a command. The various commands manipulate a database cursor and print the query results on the screen. Simply type the letter of the command you wish to perform. Some systems may require you to press Enter after the letter.

The commands are similar to the following, depending on which program you run:

p print current page
u move up a page
d move down a page
b move to the bottom of the page
t move to the top of the page
i insert a row (dcur only)
n new table (dcur only)
q quit
h help (this list of commands)

Windows and Windows NT examples

The Windows versions of the example programs are real Windows programs. However, to keep the user interface code relatively simple, some simplifications have been made. In particular, these applications do not repaint their Windows on WM_PAINT messages except to reprint the prompt.

To run these programs, execute them using the curwin executable name:

Static cursor example

This example demonstrates the use of cursors. The particular cursor used here retrieves certain information from the employee table in the sample database. The cursor is declared statically, meaning that the actual SQL statement to retrieve the information is "hard coded" into the source program. This is a good starting point for learning how cursors work. The next example (Dynamic cursor example) takes this first example and converts it to use dynamic SQL statements.

For information on where the source code can be found and how to build this example program , see Database examples.

The C program with the Embedded SQL is shown below. The program looks much like a standard C program except there are Embedded SQL instructions that begin with EXEC SQL.

To reduce the amount of code that is duplicated by the cur and dcur example programs (and the odbc example), the mainlines and the data printing functions have been placed into a separate file. This is mainch.c for character mode systems, and mainwin.c for windowing environments.

The example programs each supply the following three routines, which are called from the mainlines.

WSQLEX_Init Connects to the database and opens the cursor
WSQLEX_Process_Command Processes commands from the user, manipulating the cursor as necessary.
WSQLEX_Finish Closes the cursor and disconnect from the database.

The function of the mainline is to:

Call the WSQLEX_Init routine
Loop, getting commands from the user and calling WSQL_Process_Command until the user quits
Call the WSQLEX_Finish routine

Connecting to the database is accomplished with the Embedded SQL CONNECT command supplying the appropriate user ID and password.

The open_cursor routine both declares a cursor for the specific SQL command and also opens the cursor.

Printing a page of information is accomplished by the print routine. It loops pagesize times fetching a single row from the cursor and printing it out. Note that the fetch routine checks for warning conditions (such as End of Cursor) and prints appropriate messages when they arise. Also, the cursor is repositioned by this program to the row before the one that is displayed at the top of the current page of data.

The move, top and bottom routines use the appropriate form of the FETCH statement to position the cursor. Note that this form of the FETCH statement doesn't actually get the data — it only positions the cursor. Also, a general relative positioning routine, move, has been implemented to move in either direction depending on the sign of the parameter.

When the user quits, the cursor is closed and the database connection is also released. The cursor is closed by a ROLLBACK WORK statement, and the connection is release by a DISCONNECT.

Dynamic cursor example

This example demonstrates the use of cursors for a dynamic SQL SELECT statement. It is a slight modification of the previous example. If you have not yet looked at Static cursor example it would be helpful to do so before looking at this example.

For information on where the source code can be found and how to build this example program Database examples.

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 will fit on the screen. The SELECT statement is built up in a program array using the C library function rintf.

When this program is run, it prompts for a connection string of the form:

uid=dba;pwd=sql;dbf=c:\asa6\asademo.db

The C program with the Embedded SQL is shown below. The program looks much like the previous example 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 tablename

where tablename 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 right 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.

Windows NT Service examples

The example programs cur.sqc and dcur.sqc, when compiled for Windows NT, run optionally as services.

The two files containing the example code for NT services are the source file ntsvc.c and the header file ntsvc.h. The code allows a linked executable to be run either as a regular executable or as an NT service.

To run one of the compiled examples as an NT service:

Start Sybase Central, and open the Services folder.
Select a service type of Sample Application, and click OK.
Enter a service name in the appropriate field.
Select the sample program (curwnt.exe or dcurwnt.exe) from the cxmp subdirectory of the installation directory.
Click OK to install the service.
Click Start on the main window to start the service.

When run as a service, the programs display the normal user interface if possible. They also write the output to the Application Event Log. If it is not possible to start the user interface, the programs print one page of data to the Application Event Log and stop.

These examples have been tested with the Watcom C/C++ 10.5 compiler and the Microsoft Visual C++ 2.0 compiler.