User's Guide Part V. The SQL Anywhere Programming Interfaces Chapter 34. The Embedded SQL InterfaceEmbedded SQL consists of SQL statements intermixed with C or C++ source code. These SQL statements are translated by a SQL preprocessorinto C or C++ source code. The SQL preprocessor is run before compilation.
This code, together with the SQL Anywhere interface library (which is a dynamic link library (DLL) on most platforms, but a normal library in DOS or QNX), communicates the appropriate information to the database engine when the program is run.
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, 1.5, 2.0, 4.0, 4.1 |
| Windows 95 and NT | Borland C++ | 4.5 |
| Windows 3.x | Watcom C/C++ | 9.0 and above |
| Windows 3.x | Microsoft C | 5.0, 5.1, 6.0 |
| Windows 3.x | Microsoft C++ | 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 + | 2.0 |
| OS/2 | Borland C++ for OS/2 | 1.0 |
| DOS | Watcom C/C++ | 9.0 and above |
| DOS | Microsoft C | 6.0, 7.0 |
| DOS | Microsoft Visual C/C++ | 1.0, 1.5 |
| DOS | Borland C++ | 3.0, 4.0 |
| DOS | Borland Turbo C | 2.0 |
| QNX | Watcom C/C++ | 9.5 and above |
The following sections present the development process required to build Embedded SQL programs. A section on running the SQL preprocessor is followed by a simple example. The remaining sections present various topics fundamental to the creation of Embedded SQL programs.
| Structure packing must be turned on All Embedded SQL programs must be compiled with the structure packing option of the compiler set to one-byte alignment (usually the default). |
The SQL runtime interface, or SQL Anywhere interface library, is implemented as a DLL (dynamic link library) for Windows 3.x and 95, OS/2 and Windows NT. 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 import libraries for Watcom C/C++, for Microsoft Visual C++, and for Borland C++.
Once the program has been successfully preprocessed and compiled, it is linked with the import libraryfor the SQL Anywhere interface library to form an executable file. When the database is running, this executable file will use the SQL Anywhere DLL to interact with the database. The database does not have to be running when the program is preprocessed.
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. SQL Anywhere also provides an alternative, and recommended, method, which avoids the use of import libraries. For more information, see "Loading the interface library dynamically".
Once the program has been successfully preprocessed and compiled, it is linked with the SQL Anywhere interface library to form an executable file. When the database is running this executable file interacts with the database. The database does not have to be running when the program is preprocessed.
The SQL runtime library is available in various memory models. Not all libraries are provided on the distribution diskettes.
For information on which libraries are provided, their names, and where they can be found, "Embedded SQL import libraries".
The SQL preprocessor is an executable named SQLPP.EXE.
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. SQLPP 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-filenamewith an extension of .C. If the sql-filename already has a .C extension, then the output filename extension will be CC by default.
The command-line switches for SQLPP are documented in "SQL Anywhere Components". In summary, they are as follows:
SQLPP [switches] sql-filename [output-filename]
-c Favor code size
-d Favor data size
-f Put far keyword on generated static data
-l userid,pswd Logon identification
-n Line numbers
-o operating-sys
Target operating system specification
DOS, DOS32, DOS286, WINDOWS, WIN32
OS232, WINNT, NETWARE, QNX32
(default is DOS, OS232, or WINNT)
-r generate reentrant code
-q Quiet mode---do not print banner
-s string-len Maximum string constant length for compiler
All header files are installed in the H subdirectory of the SQL Anywhere directory. For QNX they are in the INCLUDE subdirectory.
| Structure packing must be turned on All Embedded SQL programs must be compiled with the structure packing option of the compiler set to one-byte alignment (usually the default). |
| Filename | Description |
|---|---|
| SQLCA.H | Main SQL Anywhere 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 engine or SQL Anywhere Client 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. |
All import libraries are installed in a subdirectory of the SQL Anywhere installation directory (usually C:\SQLANY50). For QNX the standard installation directory is /usr/lib/sqlany50.
| 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 |
| 32-bit QNX | Watcom C/C++ | dblib3r.lib |
| DOS: compact memory model | Watcom C/C++ | DBLIBWCC.LIB |
| DOS: huge memory model | Watcom C/C++ | DBLIBWCH.LIB |
| DOS: large memory model | Watcom C/C++ | DBLIBWCL.LIB |
| DOS: medium memory model | Watcom C/C++ | DBLIBWCM.LIB |
| DOS: small memory model | Watcom C/C++ | DBLIBWCS.LIB |
| DOS: compact memory model | Microsoft. | DBLIBMCC.LIB |
| DOS: huge memory model | Microsoft C | DBLIBMCH.LIB |
| DOS: large memory model | Microsoft C | DBLIBMCL.LIB |
| DOS: medium memory model | Microsoft C | DBLIBMCM.LIB |
| DOS: small memory model | Microsoft C | DBLIBMCS.LIB |
| DOS: compact memory model | Borland C | DBLIBBCC.LIB |
| DOS: huge memory model | Borland C | DBLIBBCH.LIB |
| DOS: large memory model | Borland C | DBLIBBCL.LIB |
| DOS: medium memory model | Borland C | DBLIBBCM.LIB |
| DOS: small memory model | Borland C | DBLIBBCS.LIB |
| DOS: 32-bit Rational DOS4G DOS Extender | Watcom C/C++ | DBLIBWFG.LIB |
| DOS: 32-bit Pharlap DOS Extender | Watcom C/C++. | DBLIBWFP.LIB |
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 employeeSET 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) will be 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. "Fetching data" will discuss fetching data.
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 engine and to specify the userid that will be used for authorizing all statements executed during the connection (see "Authorization" below).
| There are two kinds of ordering on statements: These are the order in which they appear in the source file, and the order in which they are executed when the program is run. |
As noted above, the CONNECT statement must be the first executed. Note that 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 notably are the INCLUDE statement and the WHENEVER statement for specifying error processing.
The CONNECT statement specifies a userid and password that will be used for checking the authorization of any dynamic SQL statements used in the program (defined in "Dynamic statements" ). It will also be used for authorization of any static statements contained in modules which were preprocessed without the -l option on the SQLPP command line.
Static SQL statements in modules preprocessed with the -l option will have the authorization checked at execution time using the userid and password specified with the -l option. This is how privileged commands can be executed by non-privileged users under C program control.