Embedded SQL

Including Files

A program that uses Embedded SQL can include other Embedded SQL files with the EXEC SQL INCLUDE command:

EXEC SQL INCLUDE file;

When the source code that contains the EXEC SQL INCLUDE file is parsed by ecpg, the files to be included will be parsed by ecpg and then included. If file is not found, ecpg will attempt to include the file.h file

Note that ecpg parses the file included by EXEC SQL INCLUDE statements in a case-sensitive manner. For example,

EXEC SQL INCLUDE my_file.h

includes the my_file.h file, while

EXEC SQL INCLUDE MY_FILE.h

includes the MY_FILE.h file. EXEC SQL INCLUDE can be used to include other header files as long as case sensitivity is observed. Be aware that:

EXEC SQL INCLUDE file.h

is not the same as:

#include <file.h>

In the former, file.h is parsed by ecpg and the resultant code is included. The latter include statement is ignored by ecpg and picked up by the C preprocessor during the compile stage.

Variable Declaration

All variables that will be shared between normal host code and SQL code must be defined in a variable declaration section that begins with

EXEC SQL BEGIN DECLARE SECTION;

and ends with

EXEC SQL END DECLARE SECTION;

Variables declared within this section are called "host variables"; they are declared in C format. For example:

EXEC SQL BEGIN DECLARE SECTION;
int fooBar;
char dooDad[16];
EXEC SQL END DECLARE SECTION;

A special type, VARCHAR, corresponds to the SQL VARCHAR type and can be declared in the variable declaration section:

VARCHAR name[len];

For example:

VARCHAR foo[100];

The VARCHAR type is converted by ecpg to a structure that contains two members; a character array of the specified length (arr) and an int that is used to store the number of characters in the array (len). Thus, the declaration

VARCHAR foo[100];

is converted to:

struct varchar_foo{
   int len;
   char arr[100];
} foo;

The variable declaration section should be set in the same places as normal C variable declarations.

When a host variable is referenced in an Embedded SQL statement, it must be prefixed by a colon (':'). For example:

EXEC SQL BEGIN DECLARE SECTION;
char *con = "db@machine";
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT TO :con as conname;

Connecting to a Database

The following is used to connect to a database:

EXEC SQL CONNECT TO connection_target
   [AS connection_name]
   [USER username]
   [USING password];

connection_target can be specified in one of the following ways:

username can be specified in any of the following ways:

• userid

• userid/password

• userid IDENTIFID BY password

• userid USING password

where username, userid, and password can all be character host variables.

Disconnecting from a Database

Disconnecting from a database can be done with the following Embedded SQL statement:

EXEC SQL DISCONNECT connection_target

where connection_target is either:

SQL Commands

In general, any normal SQL statement can be embedded in your C code. Here are some examples:

When selecting data from a database, it is generally easier to use SELECT statements when one view is expected, and cursors when more than one is expected.

SELECT a1, a2, ..., an FROM t1, t2, ..., tn INTO :host1, :host2, ..., :hostn retrieves columns a1 to an, storing a1 in the host variable host1, a2 in the host variable host2, and so on.

FETCH cursor INTO :host1, :host2, ..., :hostn works in a similar fashion, except that using the cursor cursor to retrieve the next row. Refer to the Red Hat Database SQL Guide and Reference for more information on cursors.

Error Handling

Error handling is done via the SQL Communication Area (SQLCA). The SQLCA is defined with the following Embedded SQL statement:

EXEC SQL INCLUDE sqlca;

Note that "sqlca" is in lowercase.

Including sqlca defines a struct called sqlca and declares a variable sqlca of type struct sqlca. The definition of struct sqlca is:

struct sqlca
{
   char         sqlcaid[8];
   long         sqlabc;
   long         sqlcode;
   struct
   {
      int          sqlerrml;
      char         sqlerrmc[SQLERRMC_LEN];
   } sqlerrm;
   char         sqlerrp[8];
   long         sqlerrd[6];

   /* Element 0: empty
    * 1: OID of processed tuple if applicable
    * 2: number of rows processed	after an INSERT,
    * UPDATE or DELETE statement
    * 3: empty
    * 4: empty
    * 5: empty
    */
   char         sqlwarn[8];

   /* Element 0: set to 'W' if at least one other is 'W'
    * 1: if 'W' then at least one character string
    * value was truncated when it was
    * stored into a host variable.
    * 2: if 'W' then a (hopefully) non-fatal notice occurred
    * 3: empty
    * 4: empty
    * 5: empty
    * 6: empty
    * 7: empty
    */
   char         sqlext[8];
};

If an error occurred in the last executed SQL statement, then sqlca.sqlcode will be set to a non-zero value. A value less than zero means that the error is a serious error, such as the database definition does not match the query given. A value larger than zero indicates a normal error such as the table did not contain the requested row. A list of error codes follows:

sqlca.sqlerrm.sqlerrmc will contain a string that describes the error. The string ends with the line number in the source file where the error occurred.

To turn on error handling, the following can be done after sqlca has been included:

EXEC SQL WHENEVER condition action;

where condition is one of the following:

and action is one of the following:

For example:

EXEC SQL WHENEVER SQLERROR GOTO error;
/* ... program code ...*/
error:
   fprintf(stderr, "an error occurred\n");

Example Program

Suppose that we have a database called user on the computer called box, and that this database contains one table called emp that contains the name and ID of all employees. Let emp be defined by:

CREATE TABLE emp (id INTEGER, name TEXT);

The following Embedded SQL program opens a connection to the database server (running on toolittle) and retrieves the ID number of the employee called "fred".

#include <stdio.h>

/* include SQL Communication Area code */
EXEC SQL INCLUDE sqlca;

/* shared variables */
EXEC SQL BEGIN DECLARE SECTION;
int id;
char *name = "fred";
char *db = "user@box";
EXEC SQL END DECLARE SECTION;

/* print all fatal errors */
EXEC SQL WHENEVER SQLERROR SQLPRINT;
EXEC SQL WHENEVER NOT FOUND DO print_not_found();

void print_not_found()
{
	printf("No results were returned.\n");
}

int main()
{
/* open a database connection */
EXEC SQL CONNECT TO :db AS conn;
if (!ECPGstatus(__LINE__, "conn"))
{
	fprintf(stderr, "Unable to connect to database.\n");
	return(1);
}

/* execute query */
EXEC SQL SELECT id INTO :id FROM emp WHERE name = :name;

	if (sqlca.sqlcode == 0)
		printf("ID of %s: %d\n", name, id);

/* disconnect */
EXEC SQL DISCONNECT conn;

	return(0);
}