From cc4f57695782356a5aa26dfbc35b2c1a8ec4ec4e Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Mon, 21 Oct 2002 18:04:05 +0000 Subject: [PATCH] Improve ECPG documentation. --- doc/src/sgml/ecpg.sgml | 1964 ++++++++++++++++---------------- doc/src/sgml/ref/ecpg-ref.sgml | 486 +++----- 2 files changed, 1152 insertions(+), 1298 deletions(-) diff --git a/doc/src/sgml/ecpg.sgml b/doc/src/sgml/ecpg.sgml index d0700b78a6..920ea5f1ef 100644 --- a/doc/src/sgml/ecpg.sgml +++ b/doc/src/sgml/ecpg.sgml @@ -1,955 +1,1019 @@ - - - - - Linus - Tolke - - - Michael - Meskes - - - - 1996-1997 - Linus Tolke - - - 1998 - Michael Meskes - - Transcribed 1998-02-12 - - - <application>ecpg</application> - Embedded <acronym>SQL</acronym> - in <acronym>C</acronym> - - embedded SQLin C + + <application>ECPG</application> - Embedded <acronym>SQL</acronym> in C + + embedded SQLin C + + + This chapter describes the embedded SQL package + for PostgreSQL. It works with + C and C++. It was written by + Linus Tolke (linus@epact.se) and Michael Meskes + (meskes@postgresql.org). + + + + Admittedly, this documentation is quite incomplete. But since this + interface is standardized, additional information can be found in + many resources about SQL. + + + + The Concept - This describes the embedded SQL package for - PostgreSQL. It works with - C and C++. It was written by - Linus Tolke (linus@epact.se) and Michael Meskes - (meskes@debian.org). The package is installed with the - PostgreSQL distribution, and carries a similar license. + An embedded SQL program consists of code written in an ordinary + programming language, in this case C, mixed with SQL commands in + specially marked sections. To build the program, the source code + is first passed to the embedded SQL preprocessor, which converts it + to an ordinary C program, and afterwards it can be processed by a C + compilation tool chain. - - Why Embedded <acronym>SQL</acronym>? + + Embedded SQL has advantages over other methods + for handling SQL commands from C code. First, it + takes care of the tedious passing of information to and from + variables in your C program. Secondly, embedded + SQL in C is defined in the SQL standard and supported by many other + SQL databases. The PostgreSQL implementation is designed to match + this standard as much as possible, and it is usually possible to + port embedded SQL programs written for other + RDBMS to PostgreSQL + with relative ease. + - - Embedded SQL has advantages over other methods - for handling SQL queries. It takes care of - the tedious passing of information to and from variables in your - C or C++ program. Many - RDBMS packages support this embedded language. - + + As indicated, programs written for the embedded SQL interface are + normal C programs with special code inserted to perform + database-related actions. This special code always has the form + +EXEC SQL ...; + + These statements syntactically take the place of a C statement. + Depending on the particular statement, they may appear in the + global context or within a function. Embedded SQL statements + follow the case-sensitivity rules of normal SQL code, and not those + of C. + - - There is an ANSI standard describing how the embedded language - should work. ecpg was designed to match - this standard as much as possible. It is possible to port embedded - SQL programs written for other - RDBMS to PostgreSQL. - - + + The following sections explain all the embedded SQL statements. + + - - The Concept + + Connecting to the Database Server - - You write your program in C/C++ with special - SQL constructs. When declaring variables to be - used in SQL statements, you need to put them in a - special declare section. You use a special syntax for the - SQL queries. - + + One connects to a database using the following statement: + +EXEC SQL CONNECT TO target AS connection-name USER user-name; + + The target can be specified in the + following ways: + + + + + dbname@hostname:port + + + + + + tcp:postgresql://hostname:port/dbname?options + + + + + + unix:postgresql://hostname:port/dbname?options + + + + + + character variable + + + + + + character string + + + + + + DEFAULT + + + + - - Before compiling you run the file through the embedded - SQL C preprocessor and it - converts the SQL statements you used to function - calls with the variables used as arguments. Both query input and - result output variables are passed. - + + There are also different ways to specify the user name: + + + + + userid + + + + + + userid/password + + + + + + userid IDENTIFIED BY password + + + + + + userid USING password + + + + The userid and + password may be a constant text, a + character variable, or a character string. + - - After compiling, you must link with a special library that contains - needed functions. These functions fetch information from the - arguments, perform the SQL query using the - libpq interface, and put the result in the - arguments specified for output. - - + + The connection-name is used to handle + multiple connections in one program. It can be omitted if a + program uses only one connection. + + - - How To Use <application>ecpg</application> + + Closing a Connection - - This section describes how to use ecpg. - + + To close a connection, use the following statement: + +EXEC SQL DISCONNECT [connection]; + + The connection can be specified + in the following ways: + + + + + connection-name + + + + + + DEFAULT + + + + + + CURRENT + + + + + + ALL + + + + + - - Preprocessor + + Running SQL Commands - - The preprocessor is called ecpg. After - installation it resides in the PostgreSQL - bin/ directory. - - - - Library + + Any SQL command can be run from within an embedded SQL application. + Below are some examples of how to do that. + - - The ecpg library is called - libecpg.a or libecpg.so. - Additionally, the library uses the libpq - library for communication to the - PostgreSQL server. You will have to link - your program using -lecpg -lpq. - + + Creating a table: + +EXEC SQL CREATE TABLE foo (number integer, ascii char(16)); +EXEC SQL CREATE UNIQUE INDEX num1 ON foo(number); +EXEC SQL COMMIT; + + - - The library has some methods that are hidden but may prove - useful. + + Inserting rows: + +EXEC SQL INSERT INTO foo (number, ascii) VALUES (9999, 'doodad'); +EXEC SQL COMMIT; + + - - - - ECPGdebug(int on, FILE - *stream) turns on debug - logging if called with the first argument non-zero. Debug - logging is done on stream. Most - SQL statement log their arguments and results. - + + Deleting rows: + +EXEC SQL DELETE FROM foo WHERE number = 9999; +EXEC SQL COMMIT; + + - - The most important function , ECPGdo, logs - all SQL statements with both the expanded - string, i.e. the string with all the input variables inserted, - and the result from the PostgreSQL - server. This can be very useful when searching for errors in - your SQL statements. - - + + Singleton Select: + +EXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE ascii = 'doodad'; + + - - - ECPGstatus() - This method returns TRUE if we are connected to a database and FALSE if not. - - - - - + + Select using Cursors: + +EXEC SQL DECLARE foo_bar CURSOR FOR + SELECT number, ascii FROM foo + ORDER BY ascii; +EXEC SQL FETCH foo_bar INTO :FooBar, DooDad; +... +EXEC SQL CLOSE foo_bar; +EXEC SQL COMMIT; + + + + + Updates: + +EXEC SQL UPDATE foo + SET ascii = 'foobar' + WHERE number = 9999; +EXEC SQL COMMIT; + + - - Error handling + + The tokens of the form + :something are + host variables, that is, they refer to + variables in the C program. They are explained in the next + section. + - - To detect errors from the PostgreSQL - server, include a line like: + + In the default mode, statements are committed only when + EXEC SQL COMMIT is issued. The embedded SQL + interface also supports autocommit of transactions (as known from + other interfaces) via the command-line option + to ecpg (see below) or via the EXEC SQL + SET AUTOCOMMIT TO ON statement. In autocommit mode, each + query is automatically committed unless it is inside an explicit + transaction block. This mode can be explicitly turned off using + EXEC SQL SET AUTOCOMMIT TO OFF. + + + + + Passing Data + + + To pass data from the program to the database, for example as + parameters in a query, or to pass data from the database back to + the program, the C variables that are intended to contain this data + need to be declared in a specially marked section, so the embedded + SQL preprocessor is made aware of them. + + + + This section starts with -exec sql include sqlca; +EXEC SQL BEGIN DECLARE SECTION; - in the include section of your file. This will define a struct and - a variable with the name sqlca as follows: + and ends with -struct sqlca -{ - char sqlcaid[8]; - long sqlabc; - long sqlcode; - struct - { - int sqlerrml; - char sqlerrmc[70]; - } sqlerrm; - char sqlerrp[8]; - long sqlerrd[6]; - /* 0: empty */ - /* 1: OID of processed tuple if applicable */ - /* 2: number of rows processed in an INSERT, UPDATE */ - /* or DELETE statement */ - /* 3: empty */ - /* 4: empty */ - /* 5: empty */ - char sqlwarn[8]; - /* 0: set to 'W' if at least one other is 'W' */ - /* 1: if 'W' at least one character string */ - /* value was truncated when it was */ - /* stored into a host variable. */ - /* 2: empty */ - /* 3: empty */ - /* 4: empty */ - /* 5: empty */ - /* 6: empty */ - /* 7: empty */ - char sqlext[8]; -} sqlca; +EXEC SQL END DECLARE SECTION; - + Between those lines, there must be normal C variable declarations, such as + +int x; +char foo[16], bar[16]; + + - - If an no error occurred in the last SQL statement. - sqlca.sqlcode will be 0 (ECPG_NO_ERROR). If - sqlca.sqlcode is less that zero, this is a - serious error, like the database definition does not match the - query. If it is greater than zero, it is a normal error like the - table did not contain the requested row. - + + The declarations are also echoed to the output file as a normal C + variables, so there's no need to declare them again. Variables + that are not intended to be used with SQL commands can be declared + normally outside these special sections. + - - sqlca.sqlerrm.sqlerrmc will contain a string - that describes the error. The string ends with the line number in - the source file. - + + The definition of a structure or union also must be listed inside a + DECLARE section. Otherwise the preprocessor cannot + handle these types since it does not know the definition. + - - These are the errors that can occur: + + The special types VARCHAR and VARCHAR2 + are converted into a named struct for every variable. A + declaration like: + +VARCHAR var[180]; + + is converted into: + +struct varchar_var { int len; char arr[180]; } var; + + This structure is suitable for interfacing with SQL datums of type + VARCHAR. + - - - -12, Out of memory in line %d. - - - Should not normally occur. This indicates your virtual memory is - exhausted. - - - + + To use a properly declared C variable in an SQL statement, write + :varname where an expression is + expected. See the previous section for some examples. + + - - -200 (ECPG_UNSUPPORTED): Unsupported type %s on line %d. - - - Should not normally occur. This indicates the preprocessor has - generated something that the library does not know about. - Perhaps you are running incompatible versions of the - preprocessor and the library. - - - + + Error Handling - - -201 (ECPG_TOO_MANY_ARGUMENTS): Too many arguments line %d. - - - This means that PostgreSQL has - returned more arguments than we have matching variables. - Perhaps you have forgotten a couple of the host variables in - the INTO :var1,:var2-list. - - - + + The embedded SQL interface provides a simplistic and a complex way + to handle exceptional conditions in a program. The first method + causes a message to printed automatically when a certain condition + occurs. For example: + +EXEC SQL WHENEVER sqlerror sqlprint; + + or + +EXEC SQL WHENEVER not found sqlprint; + + This error handling remains enabled throughout the entire program. + - - -202 (ECPG_TOO_FEW_ARGUMENTS): Too few arguments line %d. - - - This means that PostgreSQL has - returned fewer arguments than we have host variables. Perhaps - you have too many host variables in the INTO - :var1,:var2-list. - - - + + + This is not an exhaustive example of usage + for the EXEC SQL WHENEVER statement. Further + examples of usage may be found in SQL manuals (e.g., + The LAN TIMES Guide to SQL by Groff and Weinberg). + + - - -203 (ECPG_TOO_MANY_MATCHES): Too many matches line %d. - - - This means the query has returned several rows but the - variables specified are not arrays. The - SELECT command was not unique. - - - + + For a more powerful error handling, the embedded SQL interface + provides a struct and a variable with the name + sqlca as follows: + +struct sqlca +{ + char sqlcaid[8]; + long sqlabc; + long sqlcode; + struct + { + int sqlerrml; + char sqlerrmc[70]; + } sqlerrm; + char sqlerrp[8]; + + long sqlerrd[6]; + /* 0: empty */ + /* 1: OID of processed tuple if applicable */ + /* 2: number of rows processed in an INSERT, UPDATE */ + /* or DELETE statement */ + /* 3: empty */ + /* 4: empty */ + /* 5: empty */ + + char sqlwarn[8]; + /* 0: set to 'W' if at least one other is 'W' */ + /* 1: if 'W' at least one character string */ + /* value was truncated when it was */ + /* stored into a host variable. */ + /* 2: empty */ + /* 3: empty */ + /* 4: empty */ + /* 5: empty */ + /* 6: empty */ + /* 7: empty */ + + char sqlext[8]; +} sqlca; + + (Many of the empty fields may be used in a future release.) + - - -204 (ECPG_INT_FORMAT): Not correctly formatted int type: %s line %d. - - - This means the host variable is of type int and - the field in the PostgreSQL database - is of another type and contains a value that cannot be - interpreted as an int. The library uses - strtol() for this conversion. - - - + + If no error occurred in the last SQL statement, + sqlca.sqlcode will be 0 + (ECPG_NO_ERROR). If sqlca.sqlcode is + less that zero, this is a serious error, like the database + definition does not match the query. If it is greater than zero, it + is a normal error like the table did not contain the requested row. + - - -205 (ECPG_UINT_FORMAT): Not correctly formatted unsigned type: %s line %d. - - - This means the host variable is of type unsigned - int and the field in the - PostgreSQL database is of another type - and contains a value that cannot be interpreted as an - unsigned int. The library uses - strtoul() for this conversion. - - - + + sqlca.sqlerrm.sqlerrmc will contain a string + that describes the error. The string ends with the line number in + the source file. + - - -206 (ECPG_FLOAT_FORMAT): Not correctly formatted floating-point type: %s line %d. - - - This means the host variable is of type float and - the field in the PostgreSQL database - is of another type and contains a value that cannot be - interpreted as a float. The library uses - strtod() for this conversion. - - - + + These are the errors that can occur: + + + + -12, Out of memory in line %d. + + + Should not normally occur. This indicates your virtual memory + is exhausted. + + + + + + -200 (ECPG_UNSUPPORTED): Unsupported type %s on line %d. + + + Should not normally occur. This indicates the preprocessor has + generated something that the library does not know about. + Perhaps you are running incompatible versions of the + preprocessor and the library. + + + + + + -201 (ECPG_TOO_MANY_ARGUMENTS): Too many arguments line %d. + + + This means that the server has returned more arguments than we + have matching variables. Perhaps you have forgotten a couple + of the host variables in the INTO + :var1,:var2 list. + + + + + + -202 (ECPG_TOO_FEW_ARGUMENTS): Too few arguments line %d. + + + This means that the server has returned fewer arguments than we + have host variables. Perhaps you have too many host variables + in the INTO :var1,:var2 list. + + + + + -203 (ECPG_TOO_MANY_MATCHES): Too many matches line %d. + + + This means the query has returned several rows but the + variables specified are not arrays. The + SELECT command was not unique. + + + + + + -204 (ECPG_INT_FORMAT): Not correctly formatted int type: %s line %d. + + + This means the host variable is of type int and + the field in the PostgreSQL database + is of another type and contains a value that cannot be + interpreted as an int. The library uses + strtol() for this conversion. + + + + + + -205 (ECPG_UINT_FORMAT): Not correctly formatted unsigned type: %s line %d. + + + This means the host variable is of type unsigned + int and the field in the + PostgreSQL database is of another + type and contains a value that cannot be interpreted as an + unsigned int. The library uses + strtoul() for this conversion. + + + + + + -206 (ECPG_FLOAT_FORMAT): Not correctly formatted floating-point type: %s line %d. + + + This means the host variable is of type float and + the field in the PostgreSQL database + is of another type and contains a value that cannot be + interpreted as a float. The library uses + strtod() for this conversion. + + + + + + -207 (ECPG_CONVERT_BOOL): Unable to convert %s to bool on line %d. + + + This means the host variable is of type bool and + the field in the PostgreSQL database + is neither 't' nor 'f'. + + + + + + -208 (ECPG_EMPTY): Empty query line %d. + + + The query was empty. (This cannot normally happen in an + embedded SQL program, so it may point to an internal error.) + + + + + + -209 (ECPG_MISSING_INDICATOR): NULL value without indicator in line %d. + + + A null value was returned and no null indicator variable was + supplied. + + + + + + -210 (ECPG_NO_ARRAY): Variable is not an array in line %d. + + + An ordinary variable was used in a place that requires an + array. + + + + + + -211 (ECPG_DATA_NOT_ARRAY): Data read from backend is not an array in line %d. + + + The database returned an ordinary variable in a place that + requires array value. + + + + + + -220 (ECPG_NO_CONN): No such connection %s in line %d. + + + The program tried to access a connection that does not exist. + + + + + + -221 (ECPG_NOT_CONN): Not connected in line %d. + + + The program tried to access a connection that does exist but is + not open. + + + + + + -230 (ECPG_INVALID_STMT): Invalid statement name %s in line %d. + + + The statement you are trying to use has not been prepared. + + + + + + -240 (ECPG_UNKNOWN_DESCRIPTOR): Descriptor %s not found in line %d. + + + The descriptor specified was not found. The statement you are + trying to use has not been prepared. + + + + + + -241 (ECPG_INVALID_DESCRIPTOR_INDEX): Descriptor index out of range in line %d. + + + The descriptor index specified was out of range. + + + + + + -242 (ECPG_UNKNOWN_DESCRIPTOR_ITEM): Descriptor %s not found in line %d. + + + The descriptor specified was not found. The statement you are trying to use has not been prepared. + + + + + + -243 (ECPG_VAR_NOT_NUMERIC): Variable is not a numeric type in line %d. + + + The database returned a numeric value and the variable was not + numeric. + + + + + + -244 (ECPG_VAR_NOT_CHAR): Variable is not a character type in line %d. + + + The database returned a non-numeric value and the variable was + numeric. + + + + + + -400 (ECPG_PGSQL): Postgres error: %s line %d. + + + Some PostgreSQL error. The message + contains the error message from the + PostgreSQL backend. + + + + + + -401 (ECPG_TRANS): Error in transaction processing line %d. + + + PostgreSQL signaled that we cannot + start, commit, or rollback the transaction. + + + + + + -402 (ECPG_CONNECT): Could not connect to database %s in line %d. + + + The connect to the database did not work. + + + + + + 100 (ECPG_NOT_FOUND): Data not found line %d. + + + This is a normal error that tells you that what + you are querying cannot be found or you are at the end of the + cursor. + + + + + + - - -207 (ECPG_CONVERT_BOOL): Unable to convert %s to bool on line %d. - - - This means the host variable is of type bool and - the field in the PostgreSQL database - is neither 't' nor 'f'. - - - + + Including Files - - -208 (ECPG_EMPTY): Empty query line %d. - - - PostgreSQL returned PGRES_EMPTY_QUERY, probably - because the query indeed was empty. - - - + + To include an external file into your embedded SQL program, use: + +EXEC SQL INCLUDE filename; + + The embedded SQL preprocessor will look for a file named + filename.h, + preprocess it, and include it in the resulting C output. Thus, + embedded SQL statements in the included file are handled correctly. + - - -209 (ECPG_MISSING_INDICATOR): NULL value without indicator in line %d. - - - PostgreSQL returned ECPG_MISSING_INDICATOR - because a NULL was returned and no NULL indicator variable was supplied. - - - + + Note that this is not the same as + +#include <filename.h> + + because the file would not be subject to SQL command preprocessing. + Naturally, you can continue to use the C + #include directive to include other header + files. + - - -210 (ECPG_NO_ARRAY): Variable is not an array in line %d. - - - PostgreSQL returned ECPG_NO_ARRAY - because an ordinary variable was used in a place that requires - an array. - - - + + + The include file name is case-sensitive, even though the rest of + the EXEC SQL INCLUDE command follows the normal + SQL case-sensitivity rules. + + + - - -211 (ECPG_DATA_NOT_ARRAY): Data read from backend is not an array in line %d. - - - PostgreSQL returned ECPG_DATA_NOT_ARRAY - because the database returned an ordinary variable in a place - that requires array value. - - - + + Processing Embedded SQL Programs - - -220 (ECPG_NO_CONN): No such connection %s in line %d. - - - The program tried to access a connection that does not exist. - - - + + Now that you have an idea how to form embedded SQL C programs, you + probably want to know how to compile them. Before compiling you + run the file through the embedded SQL + C preprocessor, which converts the + SQL statements you used to special function + calls. After compiling, you must link with a special library that + contains the needed functions. These functions fetch information + from the arguments, perform the SQL query using + the libpq interface, and put the result + in the arguments specified for output. + - - -221 (ECPG_NOT_CONN): Not connected in line %d. - - - The program tried to access a connection that does exist but is - not open. - - - + + The preprocessor program is called ecpg and is + included in a normal PostgreSQL installation. Embedded SQL + programs are typically named with an extension + .pgc. If you have a program file called + prog1.pgc, you can preprocess it by simply + calling + +ecpg prog1.pgc + + This will create a file called prog1.c. If + your input files do not follow the suggested naming pattern, you + can specify the output file explicitly using the + option. + - - -230 (ECPG_INVALID_STMT): Invalid statement name %s in line %d. - - - The statement you are trying to use has not been prepared. - - - + + The preprocessed file can be compiled normally, for example + +cc -c prog1.c + + The generated C source files include headers files from the + PostgreSQL installation, so if you installed PostgreSQL in a + location that is not searched by default, you have to add an option + such as -I/usr/local/pgsql/include to the + compilation command line. + - - -240 (ECPG_UNKNOWN_DESCRIPTOR): Descriptor %s not found in line %d. - - - The descriptor specified was not found. The statement you are trying to use has not been prepared. - - - + + To link an embedded SQL program, you need to include the + libecpg library, like so: + +cc -o myprog prog1.o prog2.o ... -lecpg + + Again, you might have to add an option like + -L/usr/local/pgsql/lib to that command line. + - - -241 (ECPG_INVALID_DESCRIPTOR_INDEX): Descriptor index out of range in line %d. - - - The descriptor index specified was out of range. - - - + + If you manage the build process of a larger project using + make, it may be convenient to include + the following implicit rule to your makefiles: + +ECPG = ecpg - - -242 (ECPG_UNKNOWN_DESCRIPTOR_ITEM): Descriptor %s not found in line %d. - - - The descriptor specified was not found. The statement you are trying to use has not been prepared. - - - +%.c: %.pgc + $(ECPG) $< + + - - -243 (ECPG_VAR_NOT_NUMERIC): Variable is not a numeric type in line %d. - - - The database returned a numeric value and the variable was not - numeric. - - - + + The complete syntax of the ecpg command is + detailed in &cite-reference;. + + - - -244 (ECPG_VAR_NOT_CHAR): Variable is not a character type in line %d. - - - The database returned a non-numeric value and the variable was - numeric. - - - + + Library Functions - - -400 (ECPG_PGSQL): Postgres error: %s line %d. - - - Some PostgreSQL error. - The message contains the error message from the - PostgreSQL backend. - - - + + The libecpg library primarily contains + hidden functions that are used to implement the + functionality expressed by the embedded SQL commands. But there + are some functions that can usefully be called directly. Note that + this makes your code unportable. + - - -401 (ECPG_TRANS): Error in transaction processing line %d. - - - PostgreSQL signaled that we cannot start, - commit or rollback the transaction. - - - + + + + ECPGdebug(int on, FILE + *stream) turns on debug + logging if called with the first argument non-zero. Debug logging + is done on stream. Most + SQL statement log their arguments and results. + - - -402 (ECPG_CONNECT): Could not connect to database %s in line %d. - - - The connect to the database did not work. - - - + + The most important function, ECPGdo, logs + all SQL statements with both the expanded + string, i.e. the string with all the input variables inserted, + and the result from the PostgreSQL + server. This can be very useful when searching for errors in your + SQL statements. + + - - 100 (ECPG_NOT_FOUND): Data not found line %d. - - - This is a normal error that tells you that what you are querying cannot - be found or you are at the end of the cursor. - - - - - + + + ECPGstatus() This method returns true if we + are connected to a database and false if not. - - + + + - - Limitations + + Porting From Other <acronym>RDBMS</acronym> Packages - - What will never be included and why it cannot be done: + + The design of ecpg follows the SQL + standard. Porting from a standard RDBMS should not be a problem. + Unfortunately there is no such thing as a standard RDBMS. Therefore + ecpg tries to understand syntax + extensions as long as they do not create conflicts with the + standard. + - - - Oracle's single tasking - - - Oracle version 7.0 on AIX 3 uses OS-supported locks in shared - memory that allow an application designer to link an application - in a single tasking way. Instead of starting one client - process per application process, both the database part and the - application part run in the same process. In later versions of - Oracle this is no longer supported. - + + The following list shows all the known incompatibilities. If you + find one not listed please notify the developers. Note, however, + that we list only incompatibilities from a preprocessor of another + RDBMS to ecpg and not + ecpg features that these RDBMS do not + support. + - - This would require a total redesign of the - PostgreSQL access model and the - performance gain does not justify the effort. - - - - - - + + + Syntax of FETCH + FETCHembedded SQL + + + + The standard syntax for FETCH is: + +FETCH direction amount IN|FROM cursor + + Oracle + Oracle, however, does not use the + keywords IN or FROM. This + feature cannot be added since it would create parsing conflicts. + + + + + + + + For the Developer + + + This section explain how ecpg works + internally. This information can occasionally be useful to help + users understand how to use ecpg. + - - Porting From Other <acronym>RDBMS</acronym> Packages + + The Preprocessor - The design of ecpg follows the SQL - standard. Porting from a standard RDBMS should not be a problem. - Unfortunately there is no such thing as a standard RDBMS. Therefore - ecpg tries to understand syntax - extensions as long as they do not create conflicts with the - standard. + The first four lines written by ecpg to the + output are fixed lines. Two are comments and two are include + lines necessary to interface to the library. Then the + preprocessor reads through the file and writes output. Normally + it just echoes everything to the output. - The following list shows all the known incompatibilities. If you - find one not listed please notify the developers. Note, however, that - we list only incompatibilities from a precompiler of another RDBMS - to ecpg and not - ecpg features that these RDBMS do not - support. + When it sees an EXEC SQL statement, it + intervenes and changes it. The command starts with exec + sql and ends with ;. Everything in + between is treated as an SQL statement and + parsed for variable substitution. - - - Syntax of FETCH - FETCHembedded SQL - - - The standard syntax for FETCH is: - - - FETCH [direction] [amount] IN|FROM cursor. - - - Oracle - Oracle, however, does not use the keywords IN - or FROM. This feature cannot be added since it would create parsing - conflicts. - - - - + Variable substitution occurs when a symbol starts with a colon + (:). The variable with that name is looked up + among the variables that were previously declared within a + EXEC SQL DECLARE section. Depending on whether the + variable is being use for input or output, a pointer to the + variable is output to allow access by the function. - - - - For the Developer - This section explain how ecpg - works internally. It contains valuable information to help users - understand how to use ecpg. + For every variable that is part of the SQL + query, the function gets other arguments: + + + + + The type as a special symbol. + + + + + + A pointer to the value or a pointer to the pointer. + + + + + + The size of the variable if it is a char or varchar. + + + + + + The number of elements in the array (for array fetches). + + + + + + The offset to the next element in the array (for array fetches). + + + + + + The type of the indicator variable as a special symbol. + + + + + + A pointer to the value of the indicator variable or a pointer + to the pointer of the indicator variable. + + + + + + 0 + + + + + + Number of elements in the indicator array (for array fetches). + + + + + + The offset to the next element in the indicator array (for + array fetches). + + + - - The Preprocessor - - - The first four lines written by ecpg to the output are fixed lines. - Two are comments and two are include lines necessary to interface - to the library. - - - - Then the preprocessor reads through the file and writes output. - Normally it just echoes everything to the output. - - - - When it sees an EXEC SQL statement, it - intervenes and changes it. The EXEC SQL - statement can be one of these: - - - - Declare sections - - - Declare sections begin with: - -exec sql begin declare section; - - and end with: - -exec sql end declare section; - - In this section only variable declarations are allowed. Every - variable declared within this section is stored in a list - of variables indexed by name together with its corresponding - type. - - - - In particular the definition of a structure or union also must - be listed inside a declare section. Otherwise - ecpg cannot handle these types since - it does not know the definition. - - - - The declaration is also echoed to the file to make it a normal - C variable. - - - - The special types VARCHAR and - VARCHAR2 are converted into a named struct for - every variable. A declaration like: - -VARCHAR var[180]; - - is converted into: - -struct varchar_var { int len; char arr[180]; } var; - - - - - - - Include statements - - - An include statement looks like: - -exec sql include filename; - - Note that this is NOT the same as: - -#include <filename.h> - - - - - Instead the file specified is parsed by - ecpg so the contents of the file are - included in the resulting C code. This way you are able to - specify EXEC SQL commands in an include file. - - - - - - Connect statement - - - A connect statement looks like: - -exec sql connect to connection target; - - It creates a connection to the specified database. - - - - The connection target can be specified in the - following ways: - - - - dbname[@server][:port][as connection - name][user user name] - - - - - - tcp:postgresql://server[:port][/dbname][as - connection name][user user name] - - - - - - unix:postgresql://server[:port][/dbname][as - connection name][user user name] - - - - - - character variable[as - connection name][user user name] - - - - - - character string[as - connection name][user] - - - - - - default - - - - - - user - - - - - - - There are also different ways to specify the user name: - - - - - userid - - - - - - userid/password - - - - - - userid identified by password - - - - - - userid using password - - - - - - - Finally, the userid and password may be a constant text, a - character variable, or a character string. - - - - - - Disconnect statements - - - A disconnect statement looks like: - -exec sql disconnect [connection target]; - - It closes the connection to the specified database. - - - - The connection target can be specified in the - following ways: - - - - - connection name - - - - - - default - - - - - - current - - - - - - all - - - - - - - - - Open cursor statement - - - An open cursor statement looks like: - -exec sql open cursor; - - and is not copied to the output. Instead, the cursor's - DECLARE command is used because it opens the cursor - as well. - - - - - - Commit statement - - - A commit statement looks like: - -exec sql commit; - - - - - - - Rollback statement - - - A rollback statement looks like: + + Note that not all SQL commands are treated in this way. For + instance, an open cursor statement like -exec sql rollback; +EXEC SQL OPEN cursor; - - - - - - Other statements - - - Other SQL statements are used by - starting with exec sql and ending with - ;. Everything in between is treated as an - SQL statement and parsed for variable - substitution. - - - - Variable substitution occurs when a symbol starts with a colon - (:). The variable with that name is looked - up among the variables that were previously declared within a - declare section. Depending on whether the variable is - being use for input or output, a pointer to the variable is - output to allow access by the function. - - - - For every variable that is part of the SQL - query, the function gets other arguments: - - - - - The type as a special symbol. - - - - - - A pointer to the value or a pointer to the pointer. - - - - - - The size of the variable if it is a char or varchar. - - - - - - The number of elements in the array (for array fetches). - - - - - - The offset to the next element in the array (for array fetches). - - - - - - The type of the indicator variable as a special symbol. - - - - - - A pointer to the value of the indicator variable or a pointer to the pointer of the indicator variable. - - - - - - 0. - - - - - - Number of elements in the indicator array (for array fetches). - - - - - - The offset to the next element in the indicator array (for - array fetches). - - - - - - - - - - - - - A Complete Example + is not copied to the output. Instead, the cursor's + DECLARE command is used because it opens the cursor as + well. + - - Here is a complete example describing the output of the preprocessor of a - file foo.pgc: + + Here is a complete example describing the output of the + preprocessor of a file foo.pgc (details may + change with each particular version of the preprocessor): -exec sql begin declare section; +EXEC SQL BEGIN DECLARE SECTION; int index; int result; -exec sql end declare section; +EXEC SQL END DECLARE SECTION; ... -exec sql select res into :result from mytable where index = :index; +EXEC SQL SELECT res INTO :result FROM mytable WHERE index = :index; - is translated into: + is translated into: /* Processed by ecpg (2.6.0) */ /* These two include files are added by the preprocessor */ @@ -964,109 +1028,101 @@ exec sql select res into :result from mytable where index = :index; int result; /* exec sql end declare section */ ... -ECPGdo(__LINE__, NULL, "select res from mytable where index = ? ", +ECPGdo(__LINE__, NULL, "SELECT res FROM mytable WHERE index = ? ", ECPGt_int,&(index),1L,1L,sizeof(int), ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_int,&(result),1L,1L,sizeof(int), ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); #line 147 "foo.pgc" - (The indentation in this manual is added for readability and not - something the preprocessor does.) - - + (The indentation in this manual is added for readability and not + something the preprocessor does.) + + - - The Library + + The Library - - The most important function in the library is - ECPGdo. It takes a variable number of - arguments. Hopefully there are no computers that limit the - number of variables that can be accepted by a varargs() function. This - can easily add up to 50 or so arguments. - + + The most important function in the library is + ECPGdo. It takes a variable number of + arguments. Hopefully there are no computers that limit the number + of variables that can be accepted by a + varargs() function. This can easily add up to + 50 or so arguments. + - - The arguments are: + + The arguments are: - - - A line number - - - This is a line number of the original line; used in error messages only. - - - + + + A line number + + + This is a line number of the original line; used in error + messages only. + + + - - A string - - - This is the SQL query that is to be issued. - It is modified by the input variables, i.e. the variables that - where not known at compile time but are to be entered in the - query. Where the variables should go the string contains - ?. - - - + + A string + + + This is the SQL query that is to be issued. + It is modified by the input variables, i.e. the variables that + where not known at compile time but are to be entered in the + query. Where the variables should go the string contains + ?. + + + - - Input variables - - - As described in the section about the preprocessor, every input variable - gets ten arguments. - - - + + Input variables + + + As described in the section about the preprocessor, every + input variable gets ten arguments. + + + - - ECPGt_EOIT - - - An enum telling that there are no more input variables. - - - + + ECPGt_EOIT + + + An enum telling that there are no more input + variables. + + + - - Output variables - - - As described in the section about the preprocessor, every input variable - gets ten arguments. These variables are filled by the function. - - - + + Output variables + + + As described in the section about the preprocessor, every + input variable gets ten arguments. These variables are filled + by the function. + + + ECPGt_EORT - - An enum telling that there are no more variables. - - - - - - - - In the default mode, queries are committed only when exec - sql commit is issued. Ecpg - also supports auto-commit of transactions via the - command-line option or via the exec - sql set autocommit to on statement. In - autocommit mode, each query is automatically - committed unless it is inside an explicit transaction block. This - mode can be explicitly turned off using exec sql set - autocommit to off. - - - - + + An enum telling that there are no more variables. + + + + + + + + - ecpg + ecpg 1 Application - - ecpg - - - embedded SQL C preprocessor - + ecpg + embedded SQL C preprocessor - - 1999-07-20 - ecpg - -v - -t - -I include-path - -o outfile + option file + Description - ecpg - is an embedded SQL preprocessor for the C language and the - PostgreSQL. It - enables development of C programs with embedded SQL code. + ecpg is the embedded SQL preprocessor for C + programs. It converts C programs with embedded SQL statements to + normal C code by replacing the SQL invocations with special + function calls. The output files can then be processed with any C + compiler tool chain. - Linus Tolke (linus@epact.se) was the - original author of ecpg (up to version 0.2). - Michael Meskes (meskes@debian.org) - is the current author and maintainer of ecpg. - Thomas Good (tomg@q8.nrnet.org) - is the author of the last revision of the ecpg man page, on which - this document is based. + ecpg will convert each input file given on the + command line to the corresponding C output file. Input files + preferrably have the extension .pgc, in which + case the extension will be replaced by .c to + determine the output file name. If the extension of the input file + is not .pgc, then the output file name is + computed by appending .c to the full file name. + The output file name can also be overridden using the + option. - - - - - Options - - - ecpg accepts the following command - line arguments: - - - -v - - - Print version information. - - - - - - -t - - - Turn on auto-commit of transactions. In this mode, each query is - automatically committed unless it is inside an explicit - transaction block. In the default mode, queries are committed - only when exec sql commit is issued. - - - - - - -I include-path - - - Specify an additional include path. - Defaults are . (current directory), - /usr/local/include, the - PostgreSQL include path which is - defined at compile time (default: - /usr/local/pgsql/include), and - /usr/include. - - - - - - -o outfile - - - Specifies that ecpg should write all its output to outfile. - If no such option is given the output is written to - name.c, - assuming the input file was - named name.pgc. - If the input file does have the expected - .pgc suffix, then the output file will have - .pgc appended to the input file name. - - - - - - file - - - The files to be processed. - - - - - + + This reference page does not describe the embedded SQL language. + See &cite-programmer; for that. + - Exit Status + Options - ecpg returns 0 to the shell on - successful completion, non-zero for errors. + ecpg accepts the following command-line + arguments: + + + + + + + Automatically generate C code from SQL code. Currently, this + works for EXEC SQL TYPE. + + + + + + + + + Define a C preprocessor symbol. + + + + + + + + + Specify an additional include path, used to find files included + via EXEC SQL INCLUDE. Defaults are + . (current directory), + /usr/local/include, the + PostgreSQL include directory which + is defined at compile time (default: + /usr/local/pgsql/include), and + /usr/include, in that order. + + + + + + + + + Specifies that ecpg should write all + its output to the given filename. + + + + + + + + + Turn on autocommit of transactions. In this mode, each query is + automatically committed unless it is inside an explicit + transaction block. In the default mode, queries are committed + only when EXEC SQL COMMIT is issued. + + + + + + + + + Print additional information including the version and the + include path. + + + + + + + + + Show a brief summary of the command usage, then exit. + + + + + + + + + Output version information, then exit. + + + + - Usage - - - Preprocessing for Compilation - - - An embedded SQL source file must be preprocessed before - compilation: - -ecpg [ -d ] [ -o file ] file.pgc - - - where the optional flag turns on debugging. - The .pgc extension is an - arbitrary means of denoting ecpg source. - - - - You may want to redirect the preprocessor output to a log file. - - + Notes - - Compiling and Linking + + When compiling the preprocessed C code files, the compiler needs to + be able to find the ECPG header files in the + PostgreSQL include directory. Therefore, one might have to use the + - - Assuming the PostgreSQL binaries are in - /usr/local/pgsql, you will need to compile - and link your preprocessed source file: + + Programs using C code with embedded SQL have to be linked against + the libecpg library, for example using the + flags -L/usr/local/pgsql/lib -lecpg. + - -gcc -g -I /usr/local/pgsql/include [ -o file ] file.c -L /usr/local/pgsql/lib -lecpg -lpq - - - + + The value of either of these directories that is appropriate for + the installation can be found out using . + - - Grammar - - - Libraries - - - The preprocessor will prepend two directives to the source: - - -#include <ecpgtype.h> -#include <ecpglib.h> - - - - - - Variable Declaration - - - Variables declared within ecpg source code must be prepended with: - - -EXEC SQL BEGIN DECLARE SECTION; - - - - - Similarly, variable declaration sections must terminate with: - - -EXEC SQL END DECLARE SECTION; - - - - - Prior to version 2.1.0, each variable had to be declared - on a separate line. As of version 2.1.0 multiple variables may - be declared on a single line: - -char foo[16], bar[16]; - - - - - - - - Error Handling - - - The SQL communication area is defined with: - - -EXEC SQL INCLUDE sqlca; - - - - - - The sqlca is in lowercase. - While SQL convention may be - followed, i.e., using uppercase to separate embedded SQL - from C statements, sqlca (which includes the sqlca.h - header file) must be lowercase. This is because the - EXEC SQL prefix indicates that this inclusion will be parsed by - ecpg. - ecpg observes case sensitivity - (SQLCA.h will not be found). - EXEC SQL INCLUDE - can be used to include other header files - as long as case sensitivity is observed. - - - - - The sqlprint command is used with the EXEC SQL WHENEVER - statement to turn on error handling throughout the - program: - -EXEC SQL WHENEVER sqlerror sqlprint; - - - and - - -EXEC SQL WHENEVER not found sqlprint; - - - - - - This is not an exhaustive example of usage for - the EXEC SQL WHENEVER statement. - Further examples of usage may - be found in SQL manuals (e.g., The LAN TIMES Guide to SQL by - Groff and Weinberg). - - - - - - Connecting to the Database Server - - - One connects to a database using the following: - - -EXEC SQL CONNECT TO dbname; - - - where the database name is not quoted. Prior to version 2.1.0, the - database name was required to be inside single quotes. - - - - Specifying a server and port name in the connect statement is also - possible. The syntax is: - - -dbname[@server][:port] - - - or - - -<tcp|unix>:postgresql://server[:port][/dbname][?options] - - - - - - Queries - - - In general, SQL queries acceptable to other applications such as - psql can be embedded into your C - code. Here are some examples of how to do that. - - - - Create Table: - - -EXEC SQL CREATE TABLE foo (number int4, ascii char(16)); -EXEC SQL CREATE UNIQUE index num1 on foo(number); -EXEC SQL COMMIT; - - - - - Insert: - - -EXEC SQL INSERT INTO foo (number, ascii) VALUES (9999, 'doodad'); -EXEC SQL COMMIT; - - - - - Delete: - - -EXEC SQL DELETE FROM foo WHERE number = 9999; -EXEC SQL COMMIT; - - - - - Singleton Select: - - -EXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE ascii = 'doodad'; - - - - - Select using Cursors: - - -EXEC SQL DECLARE foo_bar CURSOR FOR - SELECT number, ascii FROM foo - ORDER BY ascii; -EXEC SQL FETCH foo_bar INTO :FooBar, DooDad; -... -EXEC SQL CLOSE foo_bar; -EXEC SQL COMMIT; - - + + Examples - - Updates: + + If you have an embedded SQL C source file named + prog1.pgc, you can create an executable + program using the following sequence of commands: -EXEC SQL UPDATE foo - SET ascii = 'foobar' - WHERE number = 9999; -EXEC SQL COMMIT; +ecpg prog1.pgc +cc -I/usr/local/pgsql/include -c prog1.c +cc -o prog1 prog1.o -L/usr/local/pgsql/lib -lecpg - - - - - - Notes - - The complete structure definition MUST be listed - inside the declare section. - - - - See the TODO file in the source for some more - missing features. - @@ -399,7 +197,7 @@ EXEC SQL COMMIT; PostgreSQL Programmer's Guide for a more - detailed description of the embedded SQL interface. + detailed description of the embedded SQL interface -- 2.40.0