User's Guide Part V. The SQL Anywhere Programming Interfaces Chapter 34. The Embedded SQL InterfaceTwo Embedded SQL examples are included with the SQL 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 SQL Anywhere which demonstrate features available for particular platforms.
All of the examples are installed as part of the SQL Anywhere installation. They are placed in the CXMP subdirectory of the SQL Anywhere installation directory (usually C:\SQLANY50). Under QNX, they are found in /usr/sqlany50/sample.
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 SQL Anywhere. For OS/2 the command is MAKEALL.CMD. For QNX, use the shell script makeall. The format of the command is as follows:
makeall {Example} {Platform} {Compiler} [DOS Extender]
The first parameter is the name of the example program that you want to compile. It will be one of:
The second parameter is the platform in which the program will be run. The platform can be one of the following:
The third parameter is the compiler to use to compile the program. The compiler can be one of:
The fourth parameter is optional, and specifies the DOS Extender being used to run the program. This option only applies when the platform is DOS286 or DOS32. The DOS Extender can be one of:
All example programs present 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:
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 attempt to mimic the console type interface of the DOS examples. As such, they do not repaint their Windows on WM_PAINT messages except to reprint the prompt.
To run these programs, execute them by selecting Run from the Program Manager File menu and typing:
c:\sqlany50\cxmp\curwin.exe
or create a Program Manager icon for them.
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.
See "Database examples" for where the source code can be found and how to build this example program.
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. In order to reduce the amount of code 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.Cfor Windows and Windows NT. The example programs each supply the following three routines which are called from the mainlines.
The function of the mainline is to:
Connecting to the database is accomplished with the Embedded SQL CONNECT command supplying the appropriate userid 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 (end of cursor) and prints appropriate messages when they arise. Also, the SQL 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 format 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. In this case, this is accomplished with the ROLLBACK WORK statement followed by a DISCONNECT.
/* CUR.SQC General SQL code for Cursex example (all platforms)*/#include <stdio.h>#include <ctype.h>EXEC SQL INCLUDE SQLCA;#include "sqldef.h"#include "example.h"extern int PageSize;typedef struct a_student {unsigned long studnum;char name[ 41 ];char sex[ 2 ]; /* M or F plus null char */char birthdate[ 15 ];} a_student;static void printSQLError(){char buffer[ 200 ];Displaytext( 0, "SQL error -- %s\n",sqlerror_message( &sqlca, buffer, sizeof( buffer ) ) );}static int warning( char *msg ){if( SQLCODE == SQLE_NOTFOUND ) {Displaytext( 2, "Not found - past count %ld -- %s\n",SQLCOUNT, msg );} else {Displaytext( 2, "Unexpected warning %ld -- %s\n",SQLCODE, msg );}return( TRUE );}EXEC SQL WHENEVER SQLERROR { printSQLError(); return( FALSE ); };static int connect(){EXEC SQL CONNECT "DBA" IDENTIFIED BY "SQL";return( TRUE );/* errors will return FALSE: - see WHENEVER above */}static int release(){EXEC SQL ROLLBACK WORK;EXEC SQL DISCONNECT;db_fini( &sqlca );return( TRUE );}static int open_cursor(){EXEC SQL DECLARE C1 CURSOR FORSELECT studnum, initials || ' ' || surname, sex, birthdateFROM admin.student;EXEC SQL OPEN C1;return( TRUE );}static int close_cursor(){EXEC SQL CLOSE C1;return( TRUE );}static int fetch_row(EXEC SQL BEGIN DECLARE SECTION;unsigned long *studnum,char *name,char *sex,char *birthdateEXEC SQL END DECLARE SECTION;){EXEC SQL FETCH RELATIVE 1 C1INTO :studnum, :name, :sex, :birthdate;if( SQLCODE ) {warning( "Fetching" );return( FALSE );} else {return( TRUE );}}static int move(EXEC SQL BEGIN DECLARE SECTION;int relposEXEC SQL END DECLARE SECTION;){EXEC SQL FETCH RELATIVE :relpos C1;return( TRUE );}static int top(){EXEC SQL FETCH ABSOLUTE 0 C1;return( TRUE );}static int bottom(){EXEC SQL FETCH ABSOLUTE -1 C1;return( TRUE );}static void help(){Displaytext( 0, "Cursex Demonstration Program Commands:\n" );Displaytext( 0, "p - Print current page\n" );Displaytext( 0, "u - Move up a page\n" );Displaytext( 0, "d - Move down a page\n" );Displaytext( 0, "b - Move to bottom page\n" );Displaytext( 0, "t - Move to top page\n" );Displaytext( 0, "q - Quit\n" );Displaytext( 0, "h - Help (this screen)\n" );}static void print(){a_student s;int i;int status;for( i = 0; i < PageSize; ) {++i;status = fetch_row( &s.studnum, s.name, s.sex, s.birthdate );if( status ) {Displaytext( 0, "%6ld", s.studnum );Displaytext( 10, "%-30.30s", s.name );Displaytext( 30, "%-3.3s", s.sex );Displaytext( 40, "%-15.15s\n", s.birthdate );} else {break;}}move( -i );}extern int WSQLEX_Init(){if( !db_init( &sqlca ) ) {Display_systemerror("Unable to initialize database interface\n" );return( FALSE );}if( !db_find_engine( &sqlca, NULL ) ) {Display_systemerror( "Database Engine/Station not running" );return( FALSE );}if( !connect() ) {Display_systemerror( "Could not connect" );return( FALSE );}open_cursor();help();return( TRUE );}extern void WSQLEX_Process_Command( int selection ){switch( tolower( selection ) ) {case 'p': print();break;case 'u': move( -PageSize );print();break;case 'd': move( PageSize );print();break;case 't': top();print();break;case 'b': bottom();move( -PageSize );print();break;case 'h': help();break;default: Displaytext( 0, "Invalid command, press 'h' for help\n" );}}extern int WSQLEX_Finish(){close_cursor();release();return( TRUE );}
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.
See "Database examples" for where the source code can be found and how to build this example program.
The dcur program allows the user to specify the table he wishes to look at with the 'n' command. The program then presents as much information from that table as will fit on the screen. The SQL 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:\sqlany50\sademo.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 functions db_string_connect to connect to the database. This function provides the extra functionality to support the connection string used to connect to the database.
The open_cursor routine first builds the select statement:
SELECT * FROM tablename
where tablename is a parameter passed in 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 for 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, then the actual size of the select list returned by the database engine is used to allocate an SQLDA of the right size. The SQLDA structure is then filled with buffers to hold strings representing the results of the query. Note that the fill_s_sqlda routine converts all data types in the SQLDA to DT_STRING (see "SQLDEF.H header file") 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.
/* DCUR.SQC General SQL code for Dcursex example (all platforms)*/#include <stdio.h>#include <ctype.h>#include <string.h>#include <stdlib.h>EXEC SQL INCLUDE SQLCA;EXEC SQL INCLUDE SQLDA;#include "sqldef.h"#include "example.h"#define MAX_COL_WIDTH 50extern int PageSize;char TableName[MAX_TABLE_NAME];SQLDA _fd_ *SqlDA;#ifdef _SQL_OS_NETWARE#define Stringncopy(x,y,z) strncpy(x,y,z)#define Stringcat(x,y) strcat(x,y)#else#ifdef __SMALLDATA__#define Stringncopy(x,y,z) _fstrncpy(x,y,z)#define Stringcat(x,y) _fstrcat(x,y)#else#define Stringncopy(x,y,z) strncpy(x,y,z)#define Stringcat(x,y) strcat(x,y)#endif#endifEXEC SQL BEGIN DECLARE SECTION;char CursName[ 20 ];EXEC SQL END DECLARE SECTION;static void printSQLError(){char buffer[ 200 ];Displaytext( 0, "SQL error -- %s\n",sqlerror_message( &sqlca, buffer, sizeof( buffer ) ) );}static int warning( char *msg ){if( SQLCODE == SQLE_NOTFOUND ) {if( SQLCOUNT >= 0 ) {Displaytext( 0, "Not found - past bottom of table\n" );} else {Displaytext( 0, "Not found - past top of table\n" );}} else {Displaytext( 0,"Unexpected warning %ld -- %s\n", SQLCODE, msg );}return( TRUE );}EXEC SQL WHENEVER SQLERROR { printSQLError(); return( FALSE ); };static int open_cursor(){EXEC SQL BEGIN DECLARE SECTION;char buff[ 100 ];a_sql_statement_number stat;EXEC SQL END DECLARE SECTION;int n;sprintf( buff, "select * from %s", TableName );/* Note that database fills in statement number on prepare */EXEC SQL PREPARE :stat FROM :buff;/* Note that we must initialize the cursor name */strcpy( CursName, "table_cursor" );EXEC SQL DECLARE :CursName CURSOR FOR :stat;EXEC SQL OPEN :CursName;SqlDA = alloc_sqlda( 3 );EXEC SQL DESCRIBE :stat INTO SqlDA;if( SqlDA->sqld > SqlDA->sqln ) {n = SqlDA->sqld;free_sqlda( SqlDA );SqlDA = alloc_sqlda( n );EXEC SQL DESCRIBE :stat INTO SqlDA;}fill_s_sqlda( SqlDA, 1000 );return( TRUE );}static int close_cursor(){EXEC SQL CLOSE :CursName;free_filled_sqlda( SqlDA );SqlDA = NULL;return( TRUE );}static int fetch_row(){EXEC SQL FETCH RELATIVE 1 :CursName USING DESCRIPTOR SqlDA;if( SQLCODE < 0 || SQLCODE == SQLE_NOTFOUND ) {warning( "Fetching" );return( FALSE );} else if( SQLCODE != 0 ) {warning( "Fetching" );return( TRUE );} else {return( TRUE );}}static int move(EXEC SQL BEGIN DECLARE SECTION;int relposEXEC SQL END DECLARE SECTION;){EXEC SQL FETCH RELATIVE :relpos :CursName;if( SQLCODE == SQLE_NOTFOUND && SQLCOUNT == 0 ) {} else if( SQLCODE ) {warning( "Moving" );return( FALSE );}return( TRUE );}static int top(){EXEC SQL FETCH ABSOLUTE 0 :CursName;return( TRUE );}static int bottom(){EXEC SQL FETCH ABSOLUTE -1 :CursName;return( TRUE );}static void help(){Displaytext( 0, "DCursex Demonstration Program Commands:\n" );Displaytext( 0, "p - Print current page\n" );Displaytext( 0, "u - Move up a page\n" );Displaytext( 0, "d - Move down a page\n" );Displaytext( 0, "b - Move to bottom page\n" );Displaytext( 0, "t - Move to top page\n" );Displaytext( 0, "i - Insert a new row\n" );Displaytext( 0, "n - New table\n" );Displaytext( 0, "q - Quit\n" );Displaytext( 0, "h - Help (this screen)\n" );}static int col_width( SQLDA _fd_ *da, int col ){int col_name_len;int data_len;SQLDA_VARIABLE _fd_ *sqlvar;sqlvar = &da->sqlvar[ col ];col_name_len = sqlvar->sqlname.length;data_len = sqlvar->sqllen;if( data_len > col_name_len ) {col_name_len = data_len;}if( strlen( NULL_TEXT ) > col_name_len ) {col_name_len = strlen( NULL_TEXT );}if( col_name_len > MAX_COL_WIDTH ) {return( MAX_COL_WIDTH );}return( col_name_len );}static void print_headings( SQLDA _fd_ *da ){int i;int width;int total;char colname[ SQL_MAX_NAME_LEN + 1 ];char _fd_ *sqlname;total = 0;for( i = 0; i < da->sqld; ++i ) {width = col_width( da, i );sqlname = da->sqlvar[ i ].sqlname.data;Stringncopy( colname, sqlname,da->sqlvar[ i ].sqlname.length );colname[ da->sqlvar[ i ].sqlname.length ] = '\0';Displaytext( total, "%-*.*s", width, width, colname );total += width+1;}Displaytext( 0, "\n" );}static void print_data( SQLDA _fd_ *da ){int i;int width;int total;SQLDA_VARIABLE _fd_ *sqlvar;char _sqldafar *data;total = 0;for( i = 0; i < da->sqld; ++i ) {width = col_width( da, i );sqlvar = &da->sqlvar[ i ];if( *( sqlvar->sqlind ) < 0 ) {data = NULL_TEXT;} else {data = (char _sqldafar *)sqlvar->sqldata;}#if _sqlfar_isfarDisplaytext( total, "%-*.*Fs", width, width,(char far *)data );#elseDisplaytext( total, "%-*.*s", width, width, data );#endiftotal += width+1;}Displaytext( 0, "\n" );}static void print(){int i;if( SqlDA == NULL ) {Displaytext( 0, "*** Error: Cursor not open\n" );return;}print_headings( SqlDA );for( i = 0; i < PageSize; ) {++i;if( fetch_row() ) {print_data( SqlDA );} else {break;}}move( -i );}static int insert(){char prompt[ 80 ];char * tempptr;int templen;int i;for( i = 0; i < SqlDA->sqld; i++ ) {strcpy( prompt, "Enter a value for '" );Stringcat( prompt, SqlDA->sqlvar[i].sqlname.data);strcat( prompt, "'" );tempptr = (char *) SqlDA->sqlvar[i].sqldata;templen = SqlDA->sqlvar[i].sqllen;Getvalue( prompt, tempptr, templen );if( strlen( (char *)SqlDA->sqlvar[i].sqldata ) == 0&& (SqlDA->sqlvar[i].sqltype & DT_NULLS_ALLOWED) != 0 ) {*SqlDA->sqlvar[i].sqlind = -1;} else {*SqlDA->sqlvar[i].sqlind = 0;}}EXEC SQL PUT :CursName USING DESCRIPTOR SqlDA;return( TRUE );}extern int WSQLEX_Init(){char parmstr[ 251 ];if( !db_init( &sqlca ) ) {Display_systemerror("Unable to initialize database interface\n" );return( FALSE );}Getvalue( "Enter connection string", parmstr, 250 );if( strlen( parmstr ) == 0 ) {strcpy( parmstr,"UID=dba;PWD=sql;DBF=c:\\wsql\sample.db;ENG=sample" );}db_string_connect( &sqlca, parmstr );if( SQLCODE != SQLE_NOERROR ) {printSQLError();db_fini( &sqlca );return( FALSE );}Getvalue( "Enter table name", TableName, MAX_TABLE_NAME );open_cursor();help();return( TRUE );}extern void WSQLEX_Process_Command( int selection ){switch( tolower( selection ) ) {case 'p': print();break;case 'u': move( -PageSize );print();break;case 'd': move( PageSize );print();break;case 't': top();print();break;case 'b': bottom();move( -PageSize );print();break;case 'h': help();break;case 'n': close_cursor();Getvalue( "Enter table name",TableName, MAX_TABLE_NAME );open_cursor();break;case 'i': insert();break;default: Displaytext( 0,"Invalid command, press 'h' for help\n" );}}extern int WSQLEX_Finish(){close_cursor();EXEC SQL ROLLBACK WORK;EXEC SQL DISCONNECT;db_fini( &sqlca );return( TRUE );}
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 the linked executable to be run as either a regular executable or as an NT service.
To run one of the compiled examples as an NT 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.