Programming Interfaces Guide
   CHAPTER 2. The Embedded SQL Interface     

Application development using Embedded SQL

Embedded SQL consists of SQL statements intermixed with C or C++ source code. These SQL statements are translated by a SQL preprocessor into C or C++ source code. The SQL preprocessor is run before compilation.

This code, together with the Adaptive Server Anywhere interface library communicates the appropriate information to the database server when the program is run. The interface library is a dynamic link library (DLL) or shared library on most platforms.

  Supported compilers

The C language SQL preprocessor has been used in conjunction with the following compilers:

Operating system	Compiler	Version
Windows 95 and NT	Watcom C/C++	9.5 and above
Windows 95 and NT	Microsoft Visual C/C++	1.0 and above
Windows 95 and NT	Borland C++	4.5
Windows 3.x	Watcom C/C++	9.0 and above
Windows 3.x	Microsoft C / C++	5.0, 5.1, 6.0, 7.0
Windows 3.x	Microsoft Visual C/C++	1.0, 1.5
Windows 3.x	Borland C++	2.0, 3.0, 4.0, 4.5
OS/2	Watcom C/C++	9.0 and above
OS/2	IBM C Set + and Visual Age	2.0, 3.0
OS/2	Borland C++ for OS/2	1.0
UNIX	GNU gcc, native compiler

  Development process overview

Once the program has been successfully preprocessed and compiled, it is linked with the import library for the Adaptive Server Anywhere interface library to form an executable file. When the database is running, this executable file will use the Adaptive Server Anywhere DLL to interact with the database. The database does not have to be running when the program is preprocessed.

One Windows 3.x import library works for all compilers and memory models. One OS/2 import library works for all compilers and memory models. For Windows 95 and Windows NT, there are separate import libraries for Watcom C/C++, for Microsoft Visual C++, and for Borland C++.

Watcom C/C++ supports 32-bit application development under Windows 3.x. For this environment, a static interface library (not a DLL) is provided.

     Using import libraries is the standard development method for applications that call functions in DLLs. Adaptive Server Anywhere also provides an alternative, and recommended, method, which avoids the use of import libraries. For more information, see Loading the interface library dynamically.

  Running the SQL preprocessor

The SQL preprocessor is an executable named sqlpp.exe.

Command line 

The SQLPP command line is as follows:

SQLPP [ switches ] sql-filename [output-filename]

The SQL preprocessor processes a C program with Embedded SQL before the C or C++ compiler is run. The preprocessor translates the SQL statements into C/C++ language source that is put into the output file. The normal extension for source programs with Embedded SQL is .sqc. The default output filename is the sql-filename with an extension of .c. If the sql-filename already has a .c extension, then the output filename extension is .cc by default.

     For a full listing of the command-line switches, see The SQL preprocessor.

  Embedded SQL header files

All header files are installed in the .h subdirectory of your Adaptive Server Anywhere installation directory.

Filename	Description
sqlca.h	Main header file included in all Embedded SQL programs. This file includes the structure definition for the SQL Communication Area (SQLCA) and prototypes for all Embedded SQL database interface functions.
sqlda.h	SQL Descriptor Area structure definition included in Embedded SQL programs that use dynamic SQL.
sqldef.h	Definition of Embedded SQL interface data types. This file also contains structure definitions and return codes needed for starting the database server from a C program.
sqlerr.h	Definitions for error codes returned in the sqlcode field of the SQLCA.
sqlstate.h	Definitions for ANSI/ISO SQL standard error states returned in the sqlstate field of the SQLCA.
pshpk1.h, pshpk2.h, poppk.h	These headers ensure that structure packing is handled correctly. They support Watcom C/C++, Microsoft Visual C++, IBM Visual Age, and Borland C/C++ compilers.

  Import libraries

All import libraries are installed in the lib subdirectory, under the operating system subdirectory of the Adaptive Server Anywhere installation directory. For example, Windows 95 and Windows NT import libraries are stored in the win32\lib subdirectory.

Operating system	Compiler	Import library
Windows 95 and NT	Watcom C/C++	dblibwfw.lib
Windows 95 and NT	Watcom C/C++ stack calling convention.	dblibfws.lib
Windows 95 and NT	Watcom C/C++	dblibtw.lib
Windows 95 and NT	Borland C++	dblibtb.lib
Windows 95 and NT	Microsoft Visual C++	dblibtm.lib
Windows 3.x	All compilers	dblibw.lib
OS/2	All compilers	dblib2.lib

  A simple example

The following is a very simple example of an Embedded SQL program.

#include <stdio.h>
EXEC SQL INCLUDE SQLCA;
main()
{
   db_init( &sqlca );
   EXEC SQL WHENEVER SQLERROR GOTO error;
   EXEC SQL CONNECT "dba" IDENTIFIED BY "sql";
   EXEC SQL UPDATE employee 
      SET emp_lname =    'Plankton'
      WHERE emp_id = 195;
   EXEC SQL COMMIT WORK;
   EXEC SQL DISCONNECT;
   db_fini( &sqlca );
   return( 0 );
   
   error:
   printf( "update unsuccessful -- sqlcode = %ld.n",
   sqlca.sqlcode );
   return( -1 );
}

This example connects to the database, updates the surname of employee number 1056, commits the change and exits. There is virtually no interaction between the SQL and C code. The only thing the C code is used for in this example is control flow. The WHENEVER statement is used for error checking. The error action (GOTO in this example) is executed after any SQL statement that causes an error.

Note that the first section of this chapter uses UPDATE and INSERT examples because they are simpler.

     For a description of fetching data, see Fetching data.

  Structure of Embedded SQL programs

SQL statements are placed (embedded) within regular C or C++ code. All Embedded SQL statements start with the words EXEC SQL and end with a semicolon (;). Normal C language comments are allowed in the middle of Embedded SQL statements.

Every C program using Embedded SQL must contain the following statement before any other Embedded SQL statements in the source file.

EXEC SQL INCLUDE SQLCA;

The first Embedded SQL statement executed by the C program must be a CONNECT statement. The CONNECT statement is used to establish a connection with the database server and to specify the user ID that is used for authorizing all statements executed during the connection.

The CONNECT statement must be the first Embedded SQL statement executed. Some Embedded SQL commands do not generate any C code, or do not involve communication with the database. These commands are thus allowed before the CONNECT statement. Most notable are the INCLUDE statement and the WHENEVER statement for specifying error processing.

  User IDs and database authorization

The CONNECT statement specifies a user ID and password to be used for checking the permissions for any dynamic SQL statements that are used in the program (defined in Dynamic SQL statements ). It is also used for authorization of any static statements that are contained in modules preprocessed without the SQL preprocessor -l option. The -l option provides user identification.

Static SQL statements in modules that are preprocessed with the -l option will have the authorization checked at execution time using the user ID and password specified with the -l option. This is how privileged commands can be executed by non-privileged users under C program control.

  Loading the interface library dynamically

The usual practice for developing applications that use functions from DLLs is to link the application against an import library, which contains the required function definitions.

This section describes an alternative to using an import library for developing Adaptive Server Anywhere applications. The Adaptive Server Anywhere interface library can be loaded dynamically, without having to link against the import library, using the esqldll.c module in the src subdirectory of your installation directory. Using esqldll.c is recommended, because it is easier to use and more robust in its ability to locate the interface DLL.

  To load the interface DLL dynamically:

1. Your program must call db_init_dll to load the DLL, and must call db_fini_dll to free the DLL. The db_init_dll call must be before any function in the database interface, and no function in the interface can be called after db_fini_dll.

   You must still call the db_init and db_fini library functions.

2. You must #include the esqldll.h header file before the EXEC SQL INCLUDE SQLCA statement or #include <sqlca.h> line in your Embedded SQL program.

3. A SQL OS macro must be defined. The header file sqlca.h, which is included by esqdll.c, attempts to determine the appropriate macro and defines it. However, certain combinations of platforms and compilers may cause this to fail. In this case, you must add a #define to the top of this file, or make the definition by using a compiler option.

   Macro	Platforms
   _SQL_OS_WINNT	Windows 95 and Windows NT
   _SQL_OS_WINDOWS	Windows 3.x
   _SQL_OS_OS232	OS/2

4. Compile esqldll.c.

5. Instead of linking against the imports library, link the object module esqldll.obj with your Embedded SQL application objects.

Example 

The following example illustrates how to use esqldll.c and esqldll.h.

#include <stdio.h>
#include "esqldll.h"

EXEC SQL INCLUDE SQLCA;
#include "sqldef.h"
#include <windows.h>

EXEC SQL BEGIN DECLARE SECTION;
int            x;
a_sql_statement_number       stat1;
EXEC SQL END DECLARE SECTION;

#define TRUE 1
#define FALSE 0


void printSQLError( void )
{
   char      buffer[200];
   
   sqlerror_message( &sqlca, buffer, sizeof(buffer) );
   #ifdef _SQL_OS_WINDOWS
   printf( "Error %ld -- %Fs\n", SQLCODE, buffer );
   #else
   printf( "Error %ld -- %s\n", SQLCODE, buffer );
   #endif
}

char *dllpaths[] = { "s:\\jasonhi\\",
          NULL };


#include <windows.h>

int main( void )
{
       
   struct sqlda _fd_ *   sqlda1;
   int         result;
   char      string[200];
   
   printf( "Initing DLL\n" );
   
   result = db_init_dll( dllpaths );
   switch( result ) {
   case ESQLDLL_OK:
      printf("OK\n");
      break;
      case ESQLDLL_DLL_NOT_FOUND:
      printf("DLL NOT FOUND\n");
      return( 10 );
   case ESQLDLL_WRONG_VERSION:
      printf("WRONG VERSION\n");
      return( 10 );
   }
   if( !db_init( &sqlca ) ) {
      printf("db_init failed.\n");
      db_fini_dll();
   return( 10 );
   }
   
   #ifdef _SQL_OS_WINNT
   result = db_string_connect( &sqlca, "UID=dba;PWD=sql;DBF=d:\\asa6\\sample.db");

   #elif defined( _SQL_OS_WINDOWS )
   result = db_string_connect( &sqlca, "UID=dba;PWD=sql;DBF=c:\\wsql50\\sample.db");

   #elif defined( _SQL_OS_OS232 )
   result = db_string_connect( &sqlca, "UID=dba;PWD=sql;DBF=h:\\wsql50\\sample.db");

   #endif
   
   if( ! result ) {
      printf( "db_string_connect returned = %d\n", result ); 
   printSQLError();
   }
   sqlda1 = alloc_descriptor( sqlcaptr, 20 ); 
   EXEC SQL PREPARE :stat1 FROM 
      'select * from employee 
      WHERE empnum = 80921';
   if( SQLCODE != 0 ) {
      printSQLError();
   }
   EXEC SQL DECLARE curs CURSOR FOR :stat1;
   if( SQLCODE != 0 ) {
      printSQLError();
   }
   EXEC SQL OPEN curs;
   if( SQLCODE != 0 ) {
      printSQLError();
   } 
   EXEC SQL DESCRIBE :stat1 INTO sqlda1;
   if( SQLCODE != 0 ) {
      printSQLError();
   }
   sqlda1->sqlvar[1].sqltype = 460;
   fill_sqlda( sqlda1 );
   EXEC SQL FETCH FIRST curs INTO DESCRIPTOR sqlda1; 
   if( SQLCODE != 0 ) {
      printSQLError();
   }
   printf( "name = %Fs\n", (char _fd_ *)sqlda1->sqlvar[1].sqldata );
   x = sqlda1->sqld;
   printf( "COUNT = %d\n", x );
   
   free_filled_sqlda( sqlda1 );
   db_string_disconnect( &sqlca, "" );
   db_fini( &sqlca );
   db_fini_dll();
   
   return( 0 );
}