ecpg

I l @ ve RuBoard

ecpg

ecpg is a set of applications and libraries designed to help facilitate an easy way to include SQL commands within C source code. Embedded SQL in C, or ecpg , is a multiplatform tool that many RDBMSs support.

The concept behind Embedded SQL is that a developer can simply type SQL queries directly into his or her C source code, and the ecpg preprocessor translates those simple SQL statements into more complex functions, thereby obviating that work needing to be done by the developer.

The output of the ecpg program is standard C code; this can then be linked against the libpq and ecpg libraries and compiled directly to binary code.

The general flow of creating a program with ecpg is illustrated in Figure 13.1.

Figure 13.1. The flow of program creation with ecpg .

For a complete discussion of the command-line options that can be accepted by ecpg , refer to the section titled "ecpg" in Chapter 6, "User Executable Files."

Embedded SQL makes use of the syntax in the following section to perform standard database operations.

Declaring and Defining Variables

The following code is used to define the variables needed by the underlying C program when data is passed to or from the PostgreSQL back end.

 EXEC SQL BEGIN DECLARE SECTION;  [Variable Definitions]  EXEC SQL END DECLARE SECTION; 

For instance, the following code defines variables to hold the employee_id and employee_name of your fictional database:

 EXEC SQL BEGIN DECLARE SECTION;  int empl_id;  varchar empl_name[30];  EXEC SQL END DECLARE SECTION; 

Obviously, this section must occur before any use can be made of the empl_id and empl_name variables, and the types must match their corresponding PostgreSQL data type. Here is a brief table that matches PostgreSQL to standard C data types:

PostgreSQL	C
SMALLINT	short
INTEGER	int
INT2	short
INT4	int
FLOAT	float
FLOAT4	float
FLOAT8	double
DOUBLE	double
DECIMAL(p,s)	double
CHAR(n)	char x[n+1]
VARCHAR(n)	struct
DATE	char[12]
TIME	char[9]
TIMESTAMP	char[28]

Connecting to a Database

Embedded SQL in C uses the following syntax for connecting to a back-end server:

 EXEC SQL CONNECT TO  dbname  

The actual database name can be specified as follows :

 dbname[@server][:port] 

Executing Queries

Once connected, queries can be sent to the back end for processing by using the following syntax:

 EXEC SQL  query  

In general, almost all query actions require that an explicit COMMIT command be issued. The exception is SELECT commands; they can be issued on a single line. The following are some examples of typical usage:

 EXEC SQL SELECT * FROM payroll WHERE name='Jason Smith';  EXEC SQL INSERT INTO payroll VALUES       ('Steven', 'Berkeley', 'CA');  EXEC SQL COMMIT;  EXEC SQL UPDATE payroll SET l_name='Wickes'       WHERE f_name='Steven';  EXEC SQL COMMIT;  EXEC SQL DECLARE my_cur CURSOR FOR       SELECT * FROM payroll       WHERE state='CA';  EXEC SQL FETCH my_cur INTO :name;  [some code]  EXEC SQL CLOSE my_cur;  EXEC SQL COMMIT;  EXEC SQL DELETE * FROM payroll;  EXEC SQL COMMIT; 

Error Handling

The epcg communications area must be defined with the following command:

 EXEC SQL INCLUDE sqlca; 

Additionally, error reporting then can be turned on with the following:

 EXEC SQL WHENEVER sqlerror sqlprint; 

I l @ ve RuBoard