ecpg

Practical PostgreSQL
Prev		Next

Synopsis

ecpg [ -v ] [ -t ] [ -I include-path ] [ -o outfile ]  file1 [ file2 ] [ ... ]

Parameters

-v: Pass this parameter to print ecpg's version information.
-t: Pass this parameter to disable auto-transaction mode.
-I path: Use this parameter to start epcg with an additional include path. By default, it will examine the ./, /usr/local/include, PostgreSQL's defined include path, and /usr/include.
-o: Passing this parameter specifies that ecpg will write all output to file; if no specific filename is given, output will be sent to name.c, where name is the name of the input file.
file: This parameter takes the name of file(s) you wish to process.

Results

Upon completeion, ecpg will either create a file or write to standard output. If there is an error, a -1 value will be returned.

Description

The ecpg program is an embedded C preprocessor; use it to embed SQL code within C language code.

Examples

Preprocessing a File

You must preprocess a file with embedded SQL code before it can be properly compiled:

ecpg [ -d ] [ -o file ] file.pgc

Using the -d flag turns on debugging. The file extension .pgc is an arbitrary ecpg source identifier; you can use whateve extension you desire.

	Keep a log
	Use `>>` to redirect output to a file if you wish to keep a log.

Compiling and Linking

Issue the following command to compile and link your file, after it has been preprocessed (this example assumes your PostgreSQL files were installed into /usr/local/pgsql):

gcc -g -I /usr/local/pgsql/include [ -o file ] file.c -L /usr/local/pgsql/lib -lecpg -lpq

Syntax

Header files

The preprocesser adds two header directives to the beginning of your C source file:

#include <ecpgtype.h>
#include <ecpglib.h>

Variable Declaration

To declare varaibles within ecpg source code, begin them with the following:

EXEC SQL BEGIN DECLARE SECTION;

To terminate a variable declaration section, use:

EXEC SQL END DECLARE SECTION;

You can declare multiple variables on a single line by using a comma to separate their declarations, such as:

char  var1(16), var2(16);

Error Handling

Define the SQL communications area with:

EXEC SQL INCLUDE sqlca;

You must enter sqlca in lowercase, as it will be interpreted literally as the header file name, and ecpg observes case sensitivity.

Use the sqlprint command with EXEC SQL WHENEVER to turn on error handling:

EXEC SQL WHENEVER sqlerror sqlprint;

and

EXEC SQL WHENEVER not found sqlprint;

There are many more ways to use the EXEC SQL WHENEVER statement. For more examples on its usage, refer to an SQL manual.

Connecting to a Database

If your database is local, use the following example to connect to the backend process running it:

EXEC SQL CONNECT dbname;

If you are connecting a database hosted remotes, the following example explains the eyntax used to enter a hostname and port:

dbname[@server][:port]

<tcp|unix>:postgresql://server[:port][/dbname][?options]

Issuing SQL Statements

The ecpg preprocessor generally accepts most SQL statements that are acceptable by programs such as psql. Various examples of how this is done can be found below.

Creating a table:

EXEC SQL CREATE TABLE mytable (ascii char(16), number int4, number2 int4);
EXEC SQL CREATE UNIQUE index index1 on mytable (number);
EXEC SQL CREATE index index2 on mytable (number2);
EXEC SQL COMMIT;

Inserting data:

EXEC SQL INSERT INTO mytable (ascii, number, number 2) VALUES ('stuff', 10, 110);
EXEC SQL COMMIT;

Deleting data:

EXEC SQL DELETE FROM mytable WHERE number = 10;
EXEC SQL COMMIT;

Querying with select:

EXEC SQL SELECT mytable INTO :somevariable FROM anothertable WHERE ascii = 'stuff';

Select using Cursors:

EXEC SQL DECLARE mycursor CURSOR FOR
    SELECT ascii, number, number2 FROM 
    ORDER BY number;
EXEC SQL FETCH mycursor INTO :somevariable, someothervariable;
...
EXEC SQL CLOSE mycursor;
EXEC SQL COMMIT;

Updating:

EXEC SQL UPDATE mytable
    SET ascii = 'otherstuff'
    WHERE number = 10;
EXEC SQL COMMIT;