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 SAP Sybase IQ server using the SAP Sybase IQ embedded SQL interface. The command line database tools are examples of applications developed in this manner.

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 SQL preprocessor into C or C++ source code, which you then compile.

At runtime, embedded SQL applications use an SAP Sybase IQ 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 dblib16.dll.

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

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

SAP Sybase IQ provides two flavors of embedded SQL. Static embedded SQL is simpler to use, but is less flexible than dynamic embedded SQL.

• Development Process Overview
  Overview of the embedded SQL development process.
• The SQL Preprocessor
  The SQL preprocessor is an executable named iqiqsqlpp.
• Supported Compilers
  The C language SQL preprocessor has been used with the following compilers:
• Embedded SQL Header Files
  All header files are installed in the SDK\Include subdirectory of your SAP Sybase IQ installation directory.
• Import Libraries
  On Windows platforms, all import libraries are installed in the SDK\Lib subdirectories, under the SAP Sybase IQ installation directory. Windows import libraries are stored in the SDK\Lib\x86 and SDK\Lib\x64 subdirectories. An export definition list is stored in SDK\Lib\Def\dblib.def.
• Sample Embedded SQL Program
  The following is a very simple example of an embedded SQL program.
• 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.
• 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 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 SAP Sybase IQ installation. They are placed in the %ALLUSERSPROFILE%\SybaseIQ\samples\SQLAnywhere\C directory.
• Embedded SQL Data Types
  To transfer information between a program and the database server, every piece of data must have a data type. The embedded SQL data type constants are prefixed with DT_, and can be found in the sqldef.h header file. You can create a host variable of any one of the supported types. You can also use these types in a SQLDA structure for passing data to and from the database.
• Host Variables in Embedded SQL
  Host variables are C variables that are identified to the 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. The SQLCA is used as a handle for the application-to-database communication link. It is passed in to all database library functions that need to communicate with the database server. It is implicitly passed on all embedded SQL statements.
• Static and Dynamic SQL
  There are two ways to embed SQL statements into a C program:
• 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. There are two cases:
• 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. The standard SQLDA fields are limited to 32767 bytes of data as the fields holding the length information (sqllen, *sqlind) are 16-bit values. Changing these values to 32-bit values would break existing applications.
• Simple Stored Procedures in Embedded SQL
  You can create and call stored procedures in 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 DATABASE statement.
• Library Function Reference
  The SQL preprocessor generates calls to functions in the interface library or DLL. In addition to the calls generated by the SQL preprocessor, a set of library functions is provided to make database operations easier to perform. Prototypes for these functions are included by the EXEC SQL INCLUDE SQLCA statement.
• Embedded SQL Statement Summary
  ALL embedded SQL statements must be preceded with EXEC SQL and end with a semicolon (;).