User:E1328167/sandbox

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

Other Interfaces
Since ECPG supports embedding SQL in the C programming language, it also indirectly supports embedding in the C++ programming language. The SQL code is translated into C library calls. These are generated inside of an extern "C" clause, which provides linkage between modules written in different programming languages. 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.

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

Usage
The usage can be divided to 2 steps. First, a .pcg 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:

An example how to connect to a database:

The embedded SQL part will be processed trough ECPG preprocessor where SQL code will be replaced with the call 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.

Following command will create from the my_c_file_with_embedded_sql_commands.c from the my_c_file_with_embedded_sql_commands.pcg file, which can be processed further as a pure C code. There is also source code available for the ecpg, in the PostgreSQL Source Code git repository.

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 the 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 the values from the C code manually as string at the runtime.

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

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 opposite direction: back to the application. The following example shows how to pass value from SQL back to the application's C variable.

For simplicity, let's assume there is only one row in the table "tablename". This statement will insert the value of the column "columnname" into the variable "variable". The FETCH command can be also used in this way, since in this command the "INTO" clause also can be used.

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 ) 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.