ECPG is the standard, in the PostgreSQL database built-in, client programming interface for embedding SQL in programs written in the C programming language.^[1] It provides the option for accessing the PostgreSQL database directly from the C code in the application, using SQL commands.

Usage

The usage can be divided to 2 steps. First, a .pgc file has to be created, which consists of C code with embedded SQL code. In such file SQL code will be inserted directly to the application's C code. The SQL commands have to be inserted into the C code in following way:

// ... C code ...
EXEC SQL <sql-statements>;
// ... C code ...

An example how to connect to a database:

EXEC SQL CONNECT TO databasename[@hostname][:port] [AS connectionname] [USER username];

The embedded SQL part will be processed through ECPG preprocessor where SQL code will be replaced with the calls to the ecpg library (libecpg.a or libecpg.so). The .pcg file will be also preprocessed with ecpg, which converts it to a .c file according to the ANSI standards. Therefore, in the second step, the generated .c file can be directly compiled with a standard C compiler.^[2]

Following command will create from the my_c_file_with_embedded_sql_commands.pcg file a my_c_file_with_embedded_sql_commands.c file, which can be processed further as a pure C code.

$ ecpg my_c_file_with_embedded_sql_commands.pcg

There is also source code of the ecpg available in the PostgreSQL Source Code git repository.

Note: While compiling the preprocessed .c code, do not forget to link the ecpg library (libepcg), so that the generated calls can find their linked methods.

Using host variables

An important part when embedding SQL database commands in application's code is the data exchange between application and database. For this purpose, host variables can be used. Host variables can be directly used from the embedded SQL code, so there is no need to generate SQL statements with values from the C code manually as string at the runtime.

Assuming there is a variable named variablename in your C code:

EXEC SQL INSERT INTO tablename VALUES (:variablename);

This can be used in any statement, INSERT statement was chosen just as a simple example for illustration.

The above example shows how to pass a C variable to the SQL, but data can be passed also in the opposite direction: back to the application. The following example shows how to pass value from SQL back to the application's C variable.

EXEC SQL BEGIN DECLARE SECTION;
VARCHAR variablename;
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT columnname INTO :variablename FROM tablename;

For simplicity, let's assume there is only one row in the table table name. This statement will insert the value of the column columnname into the variable variable. Every command that supports the INTO clause can be used in this way, for example the FETCH command.

Error handling

For better error handling, ECPG also provides structure called SQL communication area (sqlca). This structure will be filled after every execution of sql statement (Every thread has its own sqlca^[3]) and contains warning and error information, e.g. the return code. The data in sqlca will be filled accordingly to the database response and can be used for debugging purposes.

Other interfaces

Since ECPG supports embedding SQL in the C programming language, it also indirectly supports embedding in the C++ programming language. The SQL parts are translated into C library calls. These are generated inside of an extern "C" clause, which provides linkage between modules written in different programming languages.^[4] Using ECPG with the C++ code has some limitations, since the ECPG preprocessor does not understand the specific syntax and reserved words in the C++ programming language. Usage of such syntax and words can lead to unexpected behaviour of the application. It is recommended to separate the embedded SQL commands in a linked C module, which will be linked and called from the C++ application.^[5]

Besides the internal ECPG, there are also different external interfaces for C++, Java, Lua, .NET, Node.js, Python, PHP and others available for the PostgreSQL database, that can be added in order to extend the embedded SQL options. There are also other databases that support embedded SQL, also in other languages than C such as Java, .NET, Fortran, COBOL, PL/I.