 | The topic of this article may not meet Wikipedia's general notability guideline .(December 2016) |
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.
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:
EXECSQLCONNECTTOdatabasename[@hostname][:port][ASconnectionname][USERusername];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.
$ ecpgmy_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.
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:
EXECSQLINSERTINTOtablenameVALUES(: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.
EXECSQLBEGINDECLARESECTION;VARCHARvariablename;EXECSQLENDDECLARESECTION;EXECSQLSELECTcolumnnameINTO:variablenameFROMtablename;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.
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.
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.
PostgreSQL, also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley. In 1996, the project was renamed to PostgreSQL to reflect its support for SQL. After a review in 2007, the development team decided to keep the name PostgreSQL and the alias Postgres.
Structured Query Language (SQL) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e., data incorporating relations among entities and variables.
ScriptBasic is a scripting language variant of BASIC. The source of the interpreter is available as a C program under the LGPL license.
In computer science, a preprocessor is a program that processes its input data to produce output that is used as input in another program. The output is said to be a preprocessed form of the input data, which is often used by some subsequent programs like compilers. The amount and kind of processing done depends on the nature of the preprocessor; some preprocessors are only capable of performing relatively simple textual substitutions and macro expansions, while others have the power of full-fledged programming languages.
The C preprocessor is the macro preprocessor for several computer programming languages, such as C, Objective-C, C++, and a variety of Fortran languages. The preprocessor provides inclusion of header files, macro expansions, conditional compilation, and line control.
SQLite is a database engine written in the C programming language. It is not a standalone app; rather, it is a library that software developers embed in their apps. As such, it belongs to the family of embedded databases. It is the most widely deployed database engine, as it is used by several of the top web browsers, operating systems, mobile phones, and other embedded systems.
A stored procedure is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database data dictionary.
Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to the SQL used to interact with relational databases. T-SQL expands on the SQL standard to include procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE and UPDATE statements.
An SQL INSERT statement adds one or more records to any single table in a relational database.
In computer programming, a directive or pragma is a language construct that specifies how a compiler should process its input. Directives are not part of the grammar of a programming language, and may vary from compiler to compiler. They can be processed by a preprocessor to specify compiler behavior, or function as a form of in-band parameterization.
A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new record is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries. Triggers can also be used to log historical data, for example to keep track of employees' previous salaries.
A relational database management system uses SQL MERGE statements to INSERT new records or UPDATE existing records depending on whether condition matches. It was officially introduced in the SQL:2003 standard, and expanded in the SQL:2008 standard.
SQLJ is a working title for efforts to combine Java and SQL. It was a common effort started around 1997 by engineers from IBM, Oracle, Compaq, Informix, Sybase, Cloudscape and Sun Microsystems.
Snippet is a programming term for a small region of re-usable source code, machine code, or text. Ordinarily, these are formally defined operative units to incorporate into larger programming modules. Snippet management is a feature of some text editors, program source code editors, IDEs, and related software. It allows the user to avoid repetitive typing in the course of routine edit operations.
Embedded SQL is a method of combining the computing power of a programming language and the database manipulation capabilities of SQL. Embedded SQL statements are SQL statements written inline with the program source code, of the host language. The embedded SQL statements are parsed by an embedded SQL preprocessor and replaced by host-language calls to a code library. The output from the preprocessor is then compiled by the host compiler. This allows programmers to embed SQL statements in programs written in any number of languages such as C/C++, COBOL and Fortran. This differs from SQL-derived programming languages that don't go through discrete preprocessors, such as PL/SQL and T-SQL.
In C and C++ programming language terminology, a translation unit is the ultimate input to a C or C++ compiler from which an object file is generated. A translation unit roughly consists of a source file after it has been processed by the C preprocessor, meaning that header files listed in #include directives are literally included, sections of code within #ifndef may be included, and macros have been expanded.
In database management systems (DBMS), a prepared statement, parameterized statement, or parameterized query is a feature where the database pre-compiles SQL code and stores the results, separating it from data. Benefits of prepared statements are:
QUEL is a relational database query language, based on tuple relational calculus, with some similarities to SQL. It was created as a part of the Ingres DBMS effort at University of California, Berkeley, based on Codd's earlier suggested but not implemented Data Sub-Language ALPHA. QUEL was used for a short time in most products based on the freely available Ingres source code, most notably in an implementation called POSTQUEL supported by POSTGRES. As Oracle and DB2 gained market share in the early 1980s, most companies then supporting QUEL moved to SQL instead. QUEL continues to be available as a part of the Ingres DBMS, although no QUEL-specific language enhancements have been added for many years.
PL/SQL is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database, Times Ten in-memory database, and IBM Db2. Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.
Tcl is a high-level, general-purpose, interpreted, dynamic programming language. It was designed with the goal of being very simple but powerful. Tcl casts everything into the mold of a command, even programming constructs like variable assignment and procedure definition. Tcl supports multiple programming paradigms, including object-oriented, imperative, functional, and procedural styles.
{{cite book}}: CS1 maint: multiple names: authors list (link)