Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
SQL Anywhere 17 » SQL Anywhere Server - Programming

Embedded SQL

SQL statements embedded in a C or C++ source file are referred to as Embedded SQL. A preprocessor translates these statements into calls to a runtime library. Embedded SQL is an ISO/ANSI and IBM standard.

Embedded SQL is portable to other databases and other environments, and is functionally equivalent in all operating environments. It is a comprehensive, low-level interface that provides all the functionality available in the product. Embedded SQL requires knowledge of C or C++ programming languages.

Embedded SQL applications

You can develop C or C++ applications that access the database server using the Embedded SQL interface. The command line database tools are examples of applications developed in this manner.


Applications connecting to the database server using Embedded SQL.

Embedded SQL is a database programming interface for the C and C++ programming languages. It consists of SQL statements intermixed with (embedded in) C or C++ source code. These SQL statements are translated by a Embedded SQL preprocessor into C or C++ source code, which you then compile.

At runtime, Embedded SQL applications use an interface library called DBLIB to communicate with a database server. DBLIB is a dynamic link library (DLL) or shared object on most platforms.

  • On Windows operating systems, the interface library is dblib17.dll.

  • On Unix operating systems, the interface library is libdblib17.so, libdblib17.sl, or libdblib17.a, depending on the operating system.

  • On Mac OS X, the interface library is libdblib17.dylib.1.

Two flavors of Embedded SQL are provided. Static Embedded SQL is simpler to use, but is less flexible than dynamic Embedded SQL.


In this section:
    Development process overview
    Once the program has been successfully preprocessed and compiled, it can be linked with an import library for DBLIB to form an executable file. When the database server is running, this executable file uses DBLIB to interact with the database server.
    The Embedded SQL preprocessor
    The Embedded SQL preprocessor is an executable named sqlpp.
    Supported compilers
    Both Windows and Unix compilers have been used with the Embedded SQL preprocessor.
    Embedded SQL header files
    All header files are installed in the SDK\Include subdirectory of your software installation directory.
    Import libraries
    Import libraries are installed in subdirectories of your software installation directory.
    Embedded SQL program example
    A very simple example of an Embedded SQL program is presented here.
    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 (;).
    Loading DBLIB dynamically under Windows
    Load DBLIB dynamically from your Embedded SQL application using the esqldll.c module in the SDK\C subdirectory of your software installation directory so that you do not need to link against the import library.
    Sample Embedded SQL programs
    Sample Embedded SQL programs are included with the software.
    Embedded SQL data types
    To transfer information between a program and the database server, every piece of data must have a data type.
    Host variables in Embedded SQL
    Host variables are C variables that are identified to the Embedded SQL preprocessor. Host variables can be used to send values to the database server or receive values from the database server.
    The SQL Communication Area (SQLCA)
    The SQL Communication Area (SQLCA) is an area of memory that is used on every database request for communicating statistics and errors from the application to the database server and back to the application.
    Static and dynamic SQL
    There are two ways to embed SQL statements into a C program: statically or dynamically.
    The SQL descriptor area (SQLDA)
    The SQLDA (SQL Descriptor Area) is an interface structure that is used for dynamic SQL statements. The structure is used to pass information regarding host variables and SELECT statement results to and from the database. The SQLDA is defined in the header file sqlda.h.
    How to fetch data using Embedded SQL
    Fetching data in Embedded SQL is done using the SELECT statement.
    Wide inserts using Embedded SQL
    The INSERT statement can be used to insert more than one row at a time, which may improve performance. This is called a wide insert or an array insert.
    Wide deletes using Embedded SQL
    The DELETE statement can be used to delete an arbitrary set of rows, which may improve performance. This is called a wide delete or an array delete.
    Wide merges using Embedded SQL
    The MERGE statement can be used to merge multiple sets of rows into a table, which may improve performance. This is called a wide merge or an array merge.
    How to send and retrieve long values using Embedded SQL
    The method for sending and retrieving LONG VARCHAR, LONG NVARCHAR, and LONG BINARY values in Embedded SQL applications is different from that for other data types.
    Simple stored procedures in Embedded SQL
    You can create and call stored procedures using Embedded SQL.
    Request management with Embedded SQL
    Since a typical Embedded SQL application must wait for the completion of each database request before carrying out the next step, an application that uses multiple execution threads can carry on with other tasks.
    Database backup with Embedded SQL
    The recommended way to backup a database is to use the BACKUP statement.
    Library function reference
    The Embedded SQL preprocessor generates calls to functions in the interface library or DLL. In addition to the calls generated by the Embedded SQL preprocessor, a set of library functions is provided to make database operations easier to perform.
    Embedded SQL statement summary
    All Embedded SQL statements must be preceded with EXEC SQL and end with a semicolon (;).