diff options
| -rw-r--r-- | doc/src/sgml/ecpg.sgml | 6667 | ||||
| -rw-r--r-- | doc/src/sgml/stylesheet-man.xsl | 44 |
2 files changed, 5319 insertions, 1392 deletions
diff --git a/doc/src/sgml/ecpg.sgml b/doc/src/sgml/ecpg.sgml index 645a6faafae..835d35ab991 100644 --- a/doc/src/sgml/ecpg.sgml +++ b/doc/src/sgml/ecpg.sgml @@ -13,7 +13,7 @@ Linus Tolke (<email>linus@epact.se</email>) and Michael Meskes (<email>meskes@postgresql.org</email>). Originally it was written to work with <acronym>C</acronym>. It also works with <acronym>C++</acronym>, but - it does not recognize all <acronym>C++</acronym> constructs yet. + it does not recognize all <acronym>C++</acronym> constructs yet. </para> <para> @@ -28,10 +28,13 @@ <para> 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 + specially marked sections. To build the program, the source code (<filename>*.pgc</filename>) is first passed through the embedded SQL preprocessor, which converts it - to an ordinary C program, and afterwards it can be processed by a C - compiler. + to an ordinary C program (<filename>*.c</filename>), and afterwards it can be processed by a C + compiler. (For details about the compiling and linking see <xref linkend="ecpg-process">). + Converted ECPG applications call functions in the libpq library + through the embedded SQL library (ecpglib), and communicate with + the PostgreSQL server using the normal frontend-backend protocol. </para> <para> @@ -71,7 +74,15 @@ EXEC SQL ...; </sect1> <sect1 id="ecpg-connect"> - <title>Connecting to the Database Server</title> + <title>Managing Database Connections</title> + + <para> + This section describes how to open, close, and switch database + connections. + </para> + + <sect2 id="ecpg-connecting"> + <title>Connecting to the database server</title> <para> One connects to a database using the following statement: @@ -200,10 +211,91 @@ EXEC SQL CONNECT TO :target USER :user; example above to encapsulate the connection target string somewhere. </para> - </sect1> + </sect2> + + <sect2 id="ecpg-set-connection"> + <title>Choosing a connection</title> + + <para> + SQL statements in embedded SQL programs are by default executed on + the current connection, that is, the most recently opened one. If + an application needs to manage multiple connections, then there are + two ways to handle this. + </para> + + <para> + The first option is to explicitly choose a connection for each SQL + statement, for example: +<programlisting> +EXEC SQL AT <replaceable>connection-name</replaceable> SELECT ...; +</programlisting> + This option is particularly suitable if the application needs to + use several connections in mixed order. + </para> + + <para> + If your application uses multiple threads of execution, they cannot share a + connection concurrently. You must either explicitly control access to the connection + (using mutexes) or use a connection for each thread. If each thread uses its own connection, + you will need to use the AT clause to specify which connection the thread will use. + </para> + + <para> + The second option is to execute a statement to switch the current + connection. That statement is: +<programlisting> +EXEC SQL SET CONNECTION <replaceable>connection-name</replaceable>; +</programlisting> + This option is particularly convenient if many statements are to be + executed on the same connection. It is not thread-aware. + </para> + + <para> + Here is an example program managing multiple database connections: +<programlisting><![CDATA[ +#include <stdio.h> + +EXEC SQL BEGIN DECLARE SECTION; + char dbname[1024]; +EXEC SQL END DECLARE SECTION; + +int +main() +{ + EXEC SQL CONNECT TO testdb1 AS con1 USER testuser; + EXEC SQL CONNECT TO testdb2 AS con2 USER testuser; + EXEC SQL CONNECT TO testdb3 AS con3 USER testuser; - <sect1 id="ecpg-disconnect"> - <title>Closing a Connection</title> + /* This query would be executed in the last opened database "testdb3". */ + EXEC SQL SELECT current_database() INTO :dbname; + printf("current=%s (should be testdb3)\n", dbname); + + /* Using "AT" to run a query in "testdb2" */ + EXEC SQL AT con2 SELECT current_database() INTO :dbname; + printf("current=%s (should be testdb2)\n", dbname); + + /* Switch the current connection to "testdb1". */ + EXEC SQL SET CONNECTION con1; + + EXEC SQL SELECT current_database() INTO :dbname; + printf("current=%s (should be testdb1)\n", dbname); + + EXEC SQL DISCONNECT ALL; + return 0; +} +]]></programlisting> + + This example would produce this output: +<screen> +current=testdb3 (should be testdb3) +current=testdb2 (should be testdb2) +current=testdb1 (should be testdb1) +</screen> + </para> + </sect2> + + <sect2 id="ecpg-disconnect"> + <title>Closing a connection</title> <para> To close a connection, use the following statement: @@ -247,6 +339,8 @@ EXEC SQL DISCONNECT <optional><replaceable>connection</replaceable></optional>; It is good style that an application always explicitly disconnect from every connection it opened. </para> + </sect2> + </sect1> <sect1 id="ecpg-commands"> @@ -257,6 +351,9 @@ EXEC SQL DISCONNECT <optional><replaceable>connection</replaceable></optional>; Below are some examples of how to do that. </para> + <sect2 id="ecpg-executing"> + <title>Executing SQL statements</title> + <para> Creating a table: <programlisting> @@ -283,6 +380,26 @@ EXEC SQL COMMIT; </para> <para> + Updates: +<programlisting> +EXEC SQL UPDATE foo + SET ascii = 'foobar' + WHERE number = 9999; +EXEC SQL COMMIT; +</programlisting> + </para> + + <para> + <literal>SELECT</literal> statements that return a single result + row can also be executed using + <literal>EXEC SQL</literal> directly. To handle result sets with + multiple rows, an application has to use a cursor; + see <xref linkend="ecpg-cursors"> below. (As a special case, an + application can fetch multiple rows at once into an array host + variable; see <xref linkend="ecpg-variables-arrays">.) + </para> + + <para> Single-row select: <programlisting> EXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE ascii = 'doodad'; @@ -290,6 +407,33 @@ EXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE ascii = 'doodad'; </para> <para> + Also, a configuration parameter can be retreived with the + <literal>SHOW</literal> command: +<programlisting> +EXEC SQL SHOW search_path INTO :var; +</programlisting> + </para> + + <para> + The tokens of the form + <literal>:<replaceable>something</replaceable></literal> are + <firstterm>host variables</firstterm>, that is, they refer to + variables in the C program. They are explained in <xref + linkend="ecpg-variables">. + </para> + </sect2> + + <sect2 id="ecpg-cursors"> + <title>Using cursors</title> + + <para> + To retrieve a result set holding multiple rows, an application has + to declare a cursor and fetch each row from the cursor. The steps + to use a cursor are the following: declare a cursor, open it, fetch + a row from the cursor, repeat, and finally close it. + </para> + + <para> Select using cursors: <programlisting> EXEC SQL DECLARE foo_bar CURSOR FOR @@ -304,72 +448,150 @@ EXEC SQL COMMIT; </para> <para> - Updates: -<programlisting> -EXEC SQL UPDATE foo - SET ascii = 'foobar' - WHERE number = 9999; -EXEC SQL COMMIT; -</programlisting> + For more details about declaration of the cursor, + see <xref linkend="ecpg-sql-declare">, and + see <xref linkend="sql-fetch"> for <literal>FETCH</literal> command + details. </para> - <para> - The tokens of the form - <literal>:<replaceable>something</replaceable></literal> are - <firstterm>host variables</firstterm>, that is, they refer to - variables in the C program. They are explained in <xref - linkend="ecpg-variables">. - </para> + <note> + <para> + The ECPG <command>DECLARE</command> command does not actually + cause a statement to be sent to the PostgreSQL backend. The + cursor is opened in the backend (using the + backend's <command>DECLARE</command> command) at the point when + the <command>OPEN</command> command is executed. + </para> + </note> + </sect2> + + <sect2 id="ecpg-transactions"> + <title>Managing transactions</title> <para> In the default mode, statements are committed only when <command>EXEC SQL COMMIT</command> is issued. The embedded SQL interface also supports autocommit of transactions (similar to - <application>libpq</> behavior) via the <option>-t</option> command-line - option to <command>ecpg</command> (see below) or via the <literal>EXEC SQL - SET AUTOCOMMIT TO ON</literal> statement. In autocommit mode, each - command is automatically committed unless it is inside an explicit - transaction block. This mode can be explicitly turned off using - <literal>EXEC SQL SET AUTOCOMMIT TO OFF</literal>. + <application>libpq</> behavior) via the <option>-t</option> + command-line option to <command>ecpg</command> (see <xref + linkend="app-ecpg">) or via the <literal>EXEC SQL SET AUTOCOMMIT TO + ON</literal> statement. In autocommit mode, each command is + automatically committed unless it is inside an explicit transaction + block. This mode can be explicitly turned off using <literal>EXEC + SQL SET AUTOCOMMIT TO OFF</literal>. </para> - </sect1> - <sect1 id="ecpg-set-connection"> - <title>Choosing a Connection</title> + <para> + The following transaction management commands are available: - <para> - The SQL statements shown in the previous section are executed on - the current connection, that is, the most recently opened one. If - an application needs to manage multiple connections, then there are - two ways to handle this. - </para> + <variablelist> + <varlistentry> + <term><literal>EXEC SQL COMMIT</literal></term> + <listitem> + <para> + Commit an in-progress transaction. + </para> + </listitem> + </varlistentry> - <para> - The first option is to explicitly choose a connection for each SQL - statement, for example: + <varlistentry> + <term><literal>EXEC SQL ROLLBACK</literal></term> + <listitem> + <para> + Roll back an in-progress transaction. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>EXEC SQL SET AUTOCOMMIT TO ON</literal></term> + <listitem> + <para> + Enable autocommit mode. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>SET AUTOCOMMIT TO OFF</literal></term> + <listitem> + <para> + Disable autocommit mode. This is the default. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </sect2> + + <sect2 id="ecpg-prepared"> + <title>Prepared statements</title> + + <para> + When the values to be passed to an SQL statement are not known at + compile time, or the same statement is going to be used many + times, then prepared statements can be useful. + </para> + + <para> + The statement is prepared using the + command <literal>PREPARE</literal>. For the values that are not + known yet, use the + placeholder <quote><literal>?</literal></quote>: <programlisting> -EXEC SQL AT <replaceable>connection-name</replaceable> SELECT ...; +EXEC SQL PREPARE stmt1 FROM "SELECT oid, datname FROM pg_database WHERE oid = ?"; </programlisting> - This option is particularly suitable if the application needs to - use several connections in mixed order. - </para> + </para> - <para> - If your application uses multiple threads of execution, they cannot share a - connection concurrently. You must either explicitly control access to the connection - (using mutexes) or use a connection for each thread. If each thread uses its own connection, - you will need to use the AT clause to specify which connection the thread will use. - </para> + <para> + If a statement returns a single row, the application can + call <literal>EXECUTE</literal> after + <literal>PREPARE</literal> to execute the statement, supplying the + actual values for the placeholders with a <literal>USING</literal> + clause: +<programlisting> +EXEC SQL EXECUTE stmt1 INTO :dboid, :dbname USING 1; +</programlisting> + </para> - <para> - The second option is to execute a statement to switch the current - connection. That statement is: + <para> + If a statement return multiple rows, the application can use a + cursor declared based on the prepared statement. To bind input + parameters, the cursor must be opened with + a <literal>USING</literal> clause: <programlisting> -EXEC SQL SET CONNECTION <replaceable>connection-name</replaceable>; +EXEC SQL PREPARE stmt1 FROM "SELECT oid,datname FROM pg_database WHERE oid > ?"; +EXEC SQL DECLARE foo_bar CURSOR FOR stmt1; + +/* when end of result set reached, break out of while loop */ +EXEC SQL WHENEVER NOT FOUND DO BREAK; + +EXEC SQL OPEN foo_bar USING 100; +... +while (1) +{ + EXEC SQL FETCH NEXT FROM foo_bar INTO :dboid, :dbname; + ... +} +EXEC SQL CLOSE foo_bar; </programlisting> - This option is particularly convenient if many statements are to be - executed on the same connection. It is not thread-aware. - </para> + </para> + + <para> + When you don't need the prepared statement anymore, you should + deallocate it: +<programlisting> +EXEC SQL DEALLOCATE PREPARE <replaceable>name</replaceable>; +</programlisting> + </para> + + <para> + For more details about <literal>PREPARE</literal>, + see <xref linkend="ecpg-sql-prepare">. Also + see <xref linkend="ecpg-dynamic"> for more details about using + placeholders and input parameters. + </para> + </sect2> </sect1> <sect1 id="ecpg-variables"> @@ -391,7 +613,13 @@ EXEC SQL SET CONNECTION <replaceable>connection-name</replaceable>; variables</firstterm>. </para> - <sect2> + <para> + Another way to exchange values between PostgreSQL backends and ECPG + applications is the use of SQL descriptors, described + in <xref linkend="ecpg-descriptors">. + </para> + + <sect2 id="ecpg-variables-overview"> <title>Overview</title> <para> @@ -416,7 +644,7 @@ EXEC SQL INSERT INTO sometable VALUES (:v1, 'foo', :v2); </para> </sect2> - <sect2> + <sect2 id="ecpg-declare-sections"> <title>Declare Sections</title> <para> @@ -467,107 +695,8 @@ EXEC SQL int i = 4; </para> </sect2> - <sect2> - <title>Different types of host variables</title> - <para> - As a host variable you can also use arrays, typedefs, structs and - pointers. Moreover there are special types of host variables that exist - only in ECPG. - </para> - - <para> - A few examples on host variables: - <variablelist> - <varlistentry> - <term>Arrays</term> - <listitem> - <para> - One of the most common uses of an array declaration is probably the - allocation of a char array as in: -<programlisting> -EXEC SQL BEGIN DECLARE SECTION; - char str[50]; -EXEC SQL END DECLARE SECTION; -</programlisting> - Note that you have to take care of the length for yourself. If you use - this host variable as the target variable of a query which returns a - string with more than 49 characters, a buffer overflow occurs. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>Typedefs</term> - <listitem> - <para> - Use the <literal>typedef</literal> keyword to map new types to already - existing types. -<programlisting> -EXEC SQL BEGIN DECLARE SECTION; - typedef char mychartype[40]; - typedef long serial_t; -EXEC SQL END DECLARE SECTION; -</programlisting> - Note that you could also use: -<programlisting> -EXEC SQL TYPE serial_t IS long; -</programlisting> - This declaration does not need to be part of a declare section. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>Pointers</term> - <listitem> - <para> - You can declare pointers to the most common types. Note however that - you cannot use pointers as target variables of queries without - auto-allocation. See <xref linkend="ecpg-descriptors"> for more - information on auto-allocation. - </para> -<programlisting> -EXEC SQL BEGIN DECLARE SECTION; - int *intp; - char **charp; -EXEC SQL END DECLARE SECTION; -</programlisting> - </listitem> - </varlistentry> - - <varlistentry> - <term>Special types of variables</term> - <listitem> - <para> - ECPG contains some special types that help you to interact easily with - data from the SQL server. For example it has implemented support for - the <type>varchar</>, <type>numeric</>, <type>date</>, <type>timestamp</>, and <type>interval</> types. - <xref linkend="ecpg-pgtypes"> contains basic functions to deal with - those types, such that you do not need to send a query to the SQL - server just for adding an interval to a timestamp for example. - </para> - <para> - The special type <type>VARCHAR</type> - is converted into a named <type>struct</> for every variable. A - declaration like: -<programlisting> -VARCHAR var[180]; -</programlisting> - is converted into: -<programlisting> -struct varchar_var { int len; char arr[180]; } var; -</programlisting> - This structure is suitable for interfacing with SQL datums of type - <type>varchar</type>. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </sect2> - - <sect2> - <title><command>SELECT INTO</command> and <command>FETCH INTO</command></title> + <sect2 id="ecpg-retrieving"> + <title>Retrieving query results</title> <para> Now you should be able to pass data generated by your program into @@ -577,6 +706,9 @@ struct varchar_var { int len; char arr[180]; } var; <command>FETCH</command>. These commands have a special <literal>INTO</literal> clause that specifies which host variables the retrieved values are to be stored in. + <command>SELECT</command> is used for a query that returns only + single row, and <command>FETCH</command> is used for a query that + returns multiple rows, using a cursor. </para> <para> @@ -617,7 +749,8 @@ EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test; ... -do { +do +{ ... EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2; ... @@ -634,7 +767,951 @@ do { </para> </sect2> - <sect2> + <sect2 id="ecpg-variables-type-mapping"> + <title>Type mapping</title> + + <para> + When ECPG applications exchange values between the PostgreSQL + server and the C application, such as when retrieving query + results from the server or executing SQL statements with input + parameters, the values need to be converted between PostgreSQL + data types and host language variable types (C language data + types, concretely). One of the main points of ECPG is that it + takes care of this automatically in most cases. + </para> + + <para> + In this respect, there are two kinds of data types: Some simple + PostgreSQL data types, such as <type>integer</type> + and <type>text</type>, can be read and written by the application + directly. Other PostgreSQL data types, such + as <type>timestamp</type> and <type>numeric</type> can only be + accessed through special library functions; see + <xref linkend="ecpg-special-types">. + </para> + + <para> + <xref linkend="ecpg-datatype-hostvars-table"> shows which PostgreSQL + data types correspond to which C data types. When you wish to + send or receive a value of a given PostgreSQL data type, you + should declare a C variable of the corresponding C data type in + the declare section. + </para> + + <table id="ecpg-datatype-hostvars-table"> + <title>Mapping between PostgreSQL data types and C variable types</title> + <tgroup cols="2"> + <thead> + <row> + <entry>PostgreSQL data type</entry> + <entry>Host variable type</entry> + </row> + </thead> + + <tbody> + <row> + <entry><type>smallint</type></entry> + <entry><type>short</type></entry> + </row> + + <row> + <entry><type>integer</type></entry> + <entry><type>int</type></entry> + </row> + + <row> + <entry><type>bigint</type></entry> + <entry><type>long long int</type></entry> + </row> + + <row> + <entry><type>decimal</type></entry> + <entry><type>decimal</type><footnote id="ecpg-datatype-table-fn"><para>This type can only be accessed through special library functions; see <xref linkend="ecpg-special-types">.</para></footnote></entry> + </row> + + <row> + <entry><type>numeric</type></entry> + <entry><type>numeric</type><footnoteref linkend="ecpg-datatype-table-fn"></entry> + </row> + + <row> + <entry><type>real</type></entry> + <entry><type>float</type></entry> + </row> + + <row> + <entry><type>double precision</type></entry> + <entry><type>double</type></entry> + </row> + + <row> + <entry><type>serial</type></entry> + <entry><type>int</type></entry> + </row> + + <row> + <entry><type>bigserial</type></entry> + <entry><type>long long int</type></entry> + </row> + + <row> + <entry><type>oid</type></entry> + <entry><type>unsigned int</type></entry> + </row> + + <row> + <entry><type>character(<replaceable>n</>)</type>, <type>varchar(<replaceable>n</>)</type>, <type>text</type></entry> + <entry><type>char[<replaceable>n</>+1]</type>, <type>VARCHAR[<replaceable>n</>+1]</type><footnote><para>declared in <filename>ecpglib.h</filename></para></footnote></entry> + </row> + + <row> + <entry><type>name</type></entry> + <entry><type>char[NAMEDATALEN]</type></entry> + </row> + + <row> + <entry><type>timestamp</type></entry> + <entry><type>timestamp</type><footnoteref linkend="ecpg-datatype-table-fn"></entry> + </row> + + <row> + <entry><type>interval</type></entry> + <entry><type>interval</type><footnoteref linkend="ecpg-datatype-table-fn"></entry> + </row> + + <row> + <entry><type>date</type></entry> + <entry><type>date</type><footnoteref linkend="ecpg-datatype-table-fn"></entry> + </row> + + <row> + <entry><type>boolean</type></entry> + <entry><type>bool</type><footnote><para>declared in <filename>ecpglib.h</filename> if not native</para></footnote></entry> + </row> + </tbody> + </tgroup> + </table> + + <sect3 id="ecpg-char"> + <title>Handling character strings</title> + + <para> + To handle SQL character string data types, such + as <type>varchar</type> and <type>text</type>, there are two + possible ways to declare the host variables. + </para> + + <para> + One way is using <type>char[]</type>, an array + of <type>char</type>, which is the most common way to handle + character data in C. +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; + char str[50]; +EXEC SQL END DECLARE SECTION; +</programlisting> + Note that you have to take care of the length yourself. If you + use this host variable as the target variable of a query which + returns a string with more than 49 characters, a buffer overflow + occurs. + </para> + + <para> + The other way is using the <type>VARCHAR</type> type, which is a + special type provided by ECPG. The definition on an array of + type <type>VARCHAR</type> is converted into a + named <type>struct</> for every variable. A declaration like: +<programlisting> +VARCHAR var[180]; +</programlisting> + is converted into: +<programlisting> +struct varchar_var { int len; char arr[180]; } var; +</programlisting> + The member <structfield>arr</structfield> hosts the string + including a terminating zero byte. Thus, to store a string in + a <type>VARCHAR</type> host variable, the host variable has to be + declared with the length including the zero byte terminator. The + member <structfield>len</structfield> holds the length of the + string stored in the <structfield>arr</structfield> without the + terminating zero byte. When a host variable is used as input for + a query, if <literal>strlen(arr)</literal> + and <structfield>len</structfield> are different, the shorter one + is used. + </para> + + <para> + Two or more <type>VARCHAR</type> host variables cannot be defined + in single line statement. The following code will confuse + the <command>ecpg</command> preprocessor: +<programlisting> +VARCHAR v1[128], v2[128]; /* WRONG */ +</programlisting> + Two variables should be defined in separate statements like this: +<programlisting> +VARCHAR v1[128]; +VARCHAR v2[128]; +</programlisting> + </para> + + <para> + <type>VARCHAR</type> can be written in upper or lower case, but + not in mixed case. + </para> + + <para> + <type>char</type> and <type>VARCHAR</type> host variables can + also hold values of other SQL types, which will be stored in + their string forms. + </para> + </sect3> + + <sect3 id="ecpg-special-types"> + <title>Accessing special data types</title> + + <para> + ECPG contains some special types that help you to interact easily + with some special data types from the PostgreSQL server. In + particular, it has implemented support for the + <type>numeric</>, <type>decimal</type>, <type>date</>, <type>timestamp</>, + and <type>interval</> types. These data types cannot usefully be + mapped to primitive host variable types (such + as <type>int</>, <type>long long int</type>, + or <type>char[]</type>), because they have a complex internal + structure. Applications deal with these types by declaring host + variables in special types and accessing them using functions in + the pgtypes library. The pgtypes library, described in detail + in <xref linkend="ecpg-pgtypes"> contains basic functions to deal + with those types, such that you do not need to send a query to + the SQL server just for adding an interval to a timestamp for + example. + </para> + + <para> + The follow subsections describe these special data types. For + more details about pgtypes library functions, + see <xref linkend="ecpg-pgtypes">. + </para> + + <sect4> + <title id="ecpg-type-timestamp-date">timestamp, date</title> + + <para> + Here is a pattern for handling <type>timestamp</type> variables + in the ECPG host application. + </para> + + <para> + First, the program has to include the header file for the + <type>timestamp</type> type: +<programlisting> +#include <pgtypes_timestamp.h> +</programlisting> + </para> + + <para> + Next, declare a host variable as type <type>timestamp</type> in + the declare section: +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; +timestamp ts; +EXEC SQL END DECLARE SECTION; +</programlisting> + </para> + + <para> + And after reading a value into the host variable, process it + using pgtypes library functions. In following example, the + <type>timestamp</type> value is converted into text (ASCII) form + with the <function>PGTYPEStimestamp_to_asc()</function> + function: +<programlisting> +EXEC SQL SELECT now()::timestamp INTO :ts; + +printf("ts = %s\n", PGTYPEStimestamp_to_asc(ts)); +</programlisting> + This example will show some result like following: +<screen> +ts = 2010-06-27 18:03:56.949343 +</screen> + </para> + + <para> + In addition, the DATE type can be handled in the same way. The + program has to include pg_types_date.h, declare a host variable + as the date type and convert a DATE value into a text form using + PGTYPESdate_to_asc() function. For more details about the + pgtypes library functions, see <xref linkend="ecpg-pgtypes">. + </para> + </sect4> + + <sect4 id="ecpg-type-interval"> + <title>interval</title> + + <para> + The handling of the <type>interval</type> type is also similar + to the <type>timestamp</type> and <type>date</type> types. It + is required, however, to allocate memory for + an <type>interval</type> type value explicitly. In other words, + the memory space for the variable has to be allocated in the + heap memory, not in the stack memory. + </para> + + <para> + Here is an example program: +<programlisting> +#include <stdio.h> +#include <stdlib.h> +#include <pgtypes_interval.h> + +int +main(void) +{ +EXEC SQL BEGIN DECLARE SECTION; + interval *in; +EXEC SQL END DECLARE SECTION; + + EXEC SQL CONNECT TO testdb; + + in = PGTYPESinterval_new(); + EXEC SQL SELECT '1 min'::interval INTO :in; + printf("interval = %s\n", PGTYPESinterval_to_asc(in)); + PGTYPESinterval_free(in); + + EXEC SQL COMMIT; + EXEC SQL DISCONNECT ALL; + return 0; +} +</programlisting> + </para> + </sect4> + + <sect4 id="ecpg-type-numeric-decimal"> + <title>numeric, decimal</title> + + <para> + The handling of the <type>numeric</type> + and <type>decimal</type> types is similar to the + <type>interval</type> type: It requires defining a pointer, + allocating some memory space on the heap, and accessing the + variable using the pgtypes library functions. For more details + about the pgtypes library functions, + see <xref linkend="ecpg-pgtypes">. + </para> + + <para> + No functions are provided specifically for + the <type>decimal</type> type. An application has to convert it + to a <type>numeric</type> variable using a pgtypes library + function to do further processing. + </para> + + <para> + Here is an example program handling <type>numeric</type> + and <type>decimal</type> type variables. +<programlisting> +#include <stdio.h> +#include <stdlib.h> +#include <pgtypes_numeric.h> + +EXEC SQL WHENEVER SQLERROR STOP; + +int +main(void) +{ +EXEC SQL BEGIN DECLARE SECTION; + numeric *num; + numeric *num2; + decimal *dec; +EXEC SQL END DECLARE SECTION; + + EXEC SQL CONNECT TO testdb; + + num = PGTYPESnumeric_new(); + dec = PGTYPESdecimal_new(); + + EXEC SQL SELECT 12.345::numeric(4,2), 23.456::decimal(4,2) INTO :num, :dec; + + printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 0)); + printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 1)); + printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 2)); + + /* Convert decimal to numeric to show a decimal value. */ + num2 = PGTYPESnumeric_new(); + PGTYPESnumeric_from_decimal(dec, num2); + + printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 0)); + printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 1)); + printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 2)); + + PGTYPESnumeric_free(num2); + PGTYPESdecimal_free(dec); + PGTYPESnumeric_free(num); + + EXEC SQL COMMIT; + EXEC SQL DISCONNECT ALL; + return 0; +} +</programlisting> + </para> + </sect4> + </sect3> + + <sect3 id="ecpg-variables-nonprimitive-c"> + <title>Host variables with nonprimitive types</title> + + <para> + As a host variable you can also use arrays, typedefs, structs, and + pointers. + </para> + + <sect4 id="ecpg-variables-arrays"> + <title>Arrays</title> + + <para> + There are two use cases for arrays as host variables. The first + is a way to store some text string in <type>char[]</type> + or <type>VARCHAR[]</type>, as + explained <xref linkend="ecpg-char">. The second use case is to + retreive multiple rows from a query result without using a + cursor. Without an array, to process a query result consisting + of multiple rows, it is required to use a cursor and + the <command>FETCH</command> command. But with array host + variables, multiple rows can be received at once. The length of + the array has to be defined to be able to accomodate all rows, + otherwise a buffer overflow will likely occur. + </para> + + <para> + Following example scans the <literal>pg_database</literal> + system table and shows all OIDs and names of the available + databases: +<programlisting> +int +main(void) +{ +EXEC SQL BEGIN DECLARE SECTION; + int dbid[8]; + char dbname[8][16]; + int i; +EXEC SQL END DECLARE SECTION; + + memset(dbname, 0, sizeof(char)* 16 * 8); + memset(dbid, 0, sizeof(int) * 8); + + EXEC SQL CONNECT TO testdb; + + /* Retrieve multiple rows into arrays at once. */ + EXEC SQL SELECT oid,datname INTO :dbid, :dbname FROM pg_database; + + for (i = 0; i < 8; i++) + printf("oid=%d, dbname=%s\n", dbid[i], dbname[i]); + + EXEC SQL COMMIT; + EXEC SQL DISCONNECT ALL; + return 0; +} +</programlisting> + + This example shows following result. (The exact values depend on + local circumstances.) +<screen> +oid=1, dbname=template1 +oid=11510, dbname=template0 +oid=11511, dbname=postgres +oid=313780, dbname=testdb +oid=0, dbname= +oid=0, dbname= +oid=0, dbname= +</screen> + </para> + </sect4> + + <sect4 id="ecpg-variables-struct"> + <title>Structures</title> + + <para> + A structure whose member names match the column names of a query + result, can be used to retrieve multiple columns at once. The + structure enables handling multiple column values in a single + host variable. + </para> + + <para> + The following example retrieves OIDs, names, and sizes of the + avilable databases from the <literal>pg_database</literal> + system table and using + the <function>pg_database_size()</function> function. In this + example, a structure variable <varname>dbinfo_t</varname> with + members whose names match each column in + the <literal>SELECT</literal> result is used to retrieve one + result row without putting multiple host variables in + the <literal>FETCH</literal> statement. +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; + typedef struct + { + int oid; + char datname[65]; + long long int size; + } dbinfo_t; + + dbinfo_t dbval; +EXEC SQL END DECLARE SECTION; + + memset(&dbval, 0, sizeof(dbinfo_t)); + + EXEC SQL DECLARE cur1 CURSOR FOR SELECT oid, datname, pg_database_size(oid) AS size FROM pg_database; + EXEC SQL OPEN cur1; + + /* when end of result set reached, break out of while loop */ + EXEC SQL WHENEVER NOT FOUND DO BREAK; + + while (1) + { + /* Fetch multiple columns into one structure. */ + EXEC SQL FETCH FROM cur1 INTO :dbval; + + /* Print members of the structure. */ + printf("oid=%d, datname=%s, size=%lld\n", dbval.oid, dbval.datname, dbval.size); + } + + EXEC SQL CLOSE cur1; +</programlisting> + </para> + + <para> + This example shows following result. (The exact values depend on + local circumstances.) +<screen> +oid=1, datname=template1, size=4324580 +oid=11510, datname=template0, size=4243460 +oid=11511, datname=postgres, size=4324580 +oid=313780, datname=testdb, size=8183012 +</screen> + </para> + + <para> + Structure host variables <quote>absorb</quote> as many columns + as the structure as fields. Additional columns can be assigned + to other host variables. For example, the above program could + also be restructured like this, with the <varname>size</varname> + variable outside the structure: +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; + typedef struct + { + int oid; + char datname[65]; + } dbinfo_t; + + dbinfo_t dbval; + long long int size; +EXEC SQL END DECLARE SECTION; + + memset(&dbval, 0, sizeof(dbinfo_t)); + + EXEC SQL DECLARE cur1 CURSOR FOR SELECT oid, datname, pg_database_size(oid) AS size FROM pg_database; + EXEC SQL OPEN cur1; + + /* when end of result set reached, break out of while loop */ + EXEC SQL WHENEVER NOT FOUND DO BREAK; + + while (1) + { + /* Fetch multiple columns into one structure. */ + EXEC SQL FETCH FROM cur1 INTO :dbval, :size; + + /* Print members of the structure. */ + printf("oid=%d, datname=%s, size=%lld\n", dbval.oid, dbval.datname, size); + } + + EXEC SQL CLOSE cur1; +</programlisting> + </para> + </sect4> + + <sect4> + <title>Typedefs</title> + + <para> + Use the <literal>typedef</literal> keyword to map new types to already + existing types. +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; + typedef char mychartype[40]; + typedef long serial_t; +EXEC SQL END DECLARE SECTION; +</programlisting> + Note that you could also use: +<programlisting> +EXEC SQL TYPE serial_t IS long; +</programlisting> + This declaration does not need to be part of a declare section. + </para> + </sect4> + + <sect4> + <title>Pointers</title> + + <para> + You can declare pointers to the most common types. Note however + that you cannot use pointers as target variables of queries + without auto-allocation. See <xref linkend="ecpg-descriptors"> + for more information on auto-allocation. + </para> + + <para> +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; + int *intp; + char **charp; +EXEC SQL END DECLARE SECTION; +</programlisting> + </para> + </sect4> + </sect3> + </sect2> + + <sect2 id="ecpg-variables-nonprimitive-sql"> + <title>Handling nonprimitive SQL data types</title> + + <para> + This section contains information on how to handle nonscalar and + user-defined SQL-level data types in ECPG applications. Note that + this is distinct from the handling of host variables of + nonprimitive types, described in the previous section. + </para> + + <sect3> + <title>Arrays</title> + + <para> + SQL-level arrays are not directly supported in ECPG. It is not + possible to simply map an SQL array into a C array host variable. + This will result in undefined behavior. Some workarounds exist, + however. + </para> + + <para> + If a query accesses <emphasis>elements</emphasis> of an array + separately, then this avoids the use of arrays in ECPG. Then, a + host variable with a type that can be mapped to the element type + should be used. For example, if a column type is array of + <type>integer</type>, a host variable of type <type>int</type> + can be used. Also if the element type is <type>varchar</type> + or <type>text</type>, a host variable of type <type>char[]</type> + or <type>VARCHAR[]</type> can be used. + </para> + + <para> + Here is an example. Assume the following table: +<programlisting> +CREATE TABLE t3 ( + ii integer[] +); + +testdb=> SELECT * FROM t3; + ii +------------- + {1,2,3,4,5} +(1 row) +</programlisting> + + The following example program retrieves the 4th element of the + array and stores it into a host variable of + type <type>int</type>: +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; +int ii; +EXEC SQL END DECLARE SECTION; + +EXEC SQL DECLARE cur1 CURSOR FOR SELECT ii[4] FROM t3; +EXEC SQL OPEN cur1; + +EXEC SQL WHENEVER NOT FOUND DO BREAK; + +while (1) +{ + EXEC SQL FETCH FROM cur1 INTO :ii ; + printf("ii=%d\n", ii); +} + +EXEC SQL CLOSE cur1; +</programlisting> + + This example shows the following result: +<screen> +ii=4 +</screen> + </para> + + <para> + To map multiple array elements to the multiple elements in an + array type host variables each element of array column and each + element of the host variable array have to be managed separately, + for example: +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; +int ii_a[8]; +EXEC SQL END DECLARE SECTION; + +EXEC SQL DECLARE cur1 CURSOR FOR SELECT ii[1], ii[2], ii[3], ii[4] FROM t3; +EXEC SQL OPEN cur1; + +EXEC SQL WHENEVER NOT FOUND DO BREAK; + +while (1) +{ + EXEC SQL FETCH FROM cur1 INTO :ii_a[0], :ii_a[1], :ii_a[2], :ii_a[3]; + ... +} +</programlisting> + </para> + + <para> + Note again that +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; +int ii_a[8]; +EXEC SQL END DECLARE SECTION; + +EXEC SQL DECLARE cur1 CURSOR FOR SELECT ii FROM t3; +EXEC SQL OPEN cur1; + +EXEC SQL WHENEVER NOT FOUND DO BREAK; + +while (1) +{ + /* WRONG */ + EXEC SQL FETCH FROM cur1 INTO :ii_a; + ... +} +</programlisting> + would not work correctly in this case, because you cannot map an + array type column to an array host variable directly. + </para> + + <para> + Another workaround is to store arrays in their external string + representation in host variables of type <type>char[]</type> + or <type>VARCHAR[]</type>. For more details about this + representation, see <xref linkend="arrays-input">. Note that + this means that the array cannot be accessed naturally as an + array in the host program (without further processing that parses + the text representation). + </para> + </sect3> + + <sect3> + <title>Composite types</title> + + <para> + Composite types are not directly supported in ECPG, but an easy workaround is possible. + The + available workarounds are similar to the ones described for + arrays above: Either access each attribute separately or use the + external string representation. + </para> + + <para> + For the following examples, assume the following type and table: +<programlisting> +CREATE TYPE comp_t AS (intval integer, textval varchar(32)); +CREATE TABLE t4 (compval comp_t); +INSERT INTO t4 VALUES ( (256, 'PostgreSQL') ); +</programlisting> + + The most obvious solution is to access each attribute separately. + The following program retrieves data from the example table by + selecting each attribute of the type <type>comp_t</type> + separately: +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; +int intval; +varchar textval[33]; +EXEC SQL END DECLARE SECTION; + +/* Put each element of the composite type column in the SELECT list. */ +EXEC SQL DECLARE cur1 CURSOR FOR SELECT (compval).intval, (compval).textval FROM t4; +EXEC SQL OPEN cur1; + +EXEC SQL WHENEVER NOT FOUND DO BREAK; + +while (1) +{ + /* Fetch each element of the composite type column into host variables. */ + EXEC SQL FETCH FROM cur1 INTO :intval, :textval; + + printf("intval=%d, textval=%s\n", intval, textval.arr); +} + +EXEC SQL CLOSE cur1; +</programlisting> + </para> + + <para> + To enhance this example, the host variables to store values in + the <command>FETCH</command> command can be gathered into one + structure. For more details about the host variable in the + structure form, see <xref linkend="ecpg-variables-struct">. + To switch to the structure, the example can be modified as below. + The two host variables, <varname>intval</varname> + and <varname>textval</varname>, become members of + the <structname>comp_t</structname> structure, and the structure + is specified on the <command>FETCH</command> command. +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; +typedef struct +{ + int intval; + varchar textval[33]; +} comp_t; + +comp_t compval; +EXEC SQL END DECLARE SECTION; + +/* Put each element of the composite type column in the SELECT list. */ +EXEC SQL DECLARE cur1 CURSOR FOR SELECT (compval).intval, (compval).textval FROM t4; +EXEC SQL OPEN cur1; + +EXEC SQL WHENEVER NOT FOUND DO BREAK; + +while (1) +{ + /* Put all values in the SELECT list into one structure. */ + EXEC SQL FETCH FROM cur1 INTO :compval; + + printf("intval=%d, textval=%s\n", compval.intval, compval.textval.arr); +} + +EXEC SQL CLOSE cur1; +</programlisting> + + Although a structure is used in the <command>FETCH</command> + command, the attribute names in the <command>SELECT</command> + clause are specified one by one. This can be enhanced by using + a <literal>*</literal> to ask for all attributes of the composite + type value. +<programlisting> +... +EXEC SQL DECLARE cur1 CURSOR FOR SELECT (compval).* FROM t4; +EXEC SQL OPEN cur1; + +EXEC SQL WHENEVER NOT FOUND DO BREAK; + +while (1) +{ + /* Put all values in the SELECT list into one structure. */ + EXEC SQL FETCH FROM cur1 INTO :compval; + + printf("intval=%d, textval=%s\n", compval.intval, compval.textval.arr); +} +... +</programlisting> + This way, composite types can be mapped into structures almost + seamlessly, even though ECPG does not understand the composite + type itself. + </para> + + <para> + Finally, it is also possible to store composite type values in + their external string representation in host variables of + type <type>char[]</type> or <type>VARCHAR[]</type>. But that + way, it is not easily possible to access the fields of the value + from the host program. + </para> + </sect3> + + <sect3> + <title>User-defined base types</title> + + <para> + New user-defined base types are not directly supported by ECPG. + You can use the external string representation and host variables + of type <type>char[]</type> or <type>VARCHAR[]</type>, and this + solution is indeed appropriate and sufficient for many types. + </para> + + <para> + Here is an example using the data type <type>complex</type> from + the example in <xref linkend="xtypes">. The external string + representation of that type is <literal>(%lf,%lf)</literal>, + which is defined in the + functions <function>complex_in()</function> + and <function>complex_out()</function> functions + in <xref linkend="xtypes">. The following example inserts the + complex type values <literal>(1,1)</literal> + and <literal>(3,3)</literal> into the + columns <literal>a</literal> and <literal>b</literal>, and select + them from the table after that. + +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; + varchar a[64]; + varchar b[64]; +EXEC SQL END DECLARE SECTION; + + EXEC SQL INSERT INTO test_complex VALUES ('(1,1)', '(3,3)'); + + EXEC SQL DECLARE cur1 CURSOR FOR SELECT a, b FROM test_complex; + EXEC SQL OPEN cur1; + + EXEC SQL WHENEVER NOT FOUND DO BREAK; + + while (1) + { + EXEC SQL FETCH FROM cur1 INTO :a, :b; + printf("a=%s, b=%s\n", a.arr, b.arr); + } + + EXEC SQL CLOSE cur1; +</programlisting> + + This example shows following result: +<screen> +a=(1,1), b=(3,3) +</screen> + </para> + + <para> + Another workaround is avoiding the direct use of the user-defined + types in ECPG and instead create a function or cast that converts + between the user-defined type and a primitive type that ECPG can + handle. Note, however, that type casts, especially implicit + ones, should be introduced into the type system very carefully. + </para> + + <para> + For example, +<programlisting> +CREATE FUNCTION create_complex(r double, i double) RETURNS complex +LANGUAGE SQL +IMMUTABLE +AS $$ SELECT $1 * complex '(1,0')' + $2 * complex '(0,1)' $$; +</programlisting> + After this definition, the following +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; +double a, b, c, d; +EXEC SQL END DECLARE SECTION; + +a = 1; +b = 2; +c = 3; +d = 4; + +EXEC SQL INSERT INTO test_complex VALUES (create_complex(:a, :b), create_complex(:c, :d)); +</programlisting> + has the same effect as +<programlisting> +EXEC SQL INSERT INTO test_complex VALUES ('(1,2)', '(3,4)'); +</programlisting> + </para> + </sect3> + </sect2> + + <sect2 id="ecpg-indicators"> <title>Indicators</title> <para> @@ -668,6 +1745,16 @@ EXEC SQL SELECT b INTO :val :val_ind FROM test1; positive, it means that the value is not null, but it was truncated when it was stored in the host variable. </para> + + <para> + If the argument <literal>-r no_indicator</literal> is passed to + the preprocessor <command>ecpg</command>, it works in + <quote>no-indicator</quote> mode. In no-indicator mode, if no + indicator variable is specified, null values are signaled (on + input and output) for character string types as empty string and + for integer types as the lowest possible value for type (for + example, <symbol>INT_MIN</symbol> for <type>int</type>). + </para> </sect2> </sect1> @@ -684,9 +1771,12 @@ EXEC SQL SELECT b INTO :val :val_ind FROM test1; provide in a string variable. </para> - <para> - The simplest way to execute an arbitrary SQL statement is to use - the command <command>EXECUTE IMMEDIATE</command>. For example: + <sect2 id="ecpg-dynamic-without-result"> + <title>Executing statements without a result set</title> + + <para> + The simplest way to execute an arbitrary SQL statement is to use + the command <command>EXECUTE IMMEDIATE</command>. For example: <programlisting> EXEC SQL BEGIN DECLARE SECTION; const char *stmt = "CREATE TABLE test1 (...);"; @@ -694,18 +1784,26 @@ EXEC SQL END DECLARE SECTION; EXEC SQL EXECUTE IMMEDIATE :stmt; </programlisting> - You cannot execute statements that retrieve data (e.g., - <command>SELECT</command>) this way. - </para> + <command>EXECUTE IMMEDIATE</command> can be used for SQL + statements that do not return a result set (e.g., + DDL, <command>INSERT</command>, <command>UPDATE</command>, + <command>DELETE</command>). You cannot execute statements that + retrieve data (e.g., <command>SELECT</command>) this way. The + next section describes how to do that. + </para> + </sect2> - <para> - A more powerful way to execute arbitrary SQL statements is to - prepare them once and execute the prepared statement as often as - you like. It is also possible to prepare a generalized version of - a statement and then execute specific versions of it by - substituting parameters. When preparing the statement, write - question marks where you want to substitute parameters later. For - example: + <sect2 id="ecpg-dynamic-input"> + <title>Executing a statement with input parameters</title> + + <para> + A more powerful way to execute arbitrary SQL statements is to + prepare them once and execute the prepared statement as often as + you like. It is also possible to prepare a generalized version of + a statement and then execute specific versions of it by + substituting parameters. When preparing the statement, write + question marks where you want to substitute parameters later. For + example: <programlisting> EXEC SQL BEGIN DECLARE SECTION; const char *stmt = "INSERT INTO test1 VALUES(?, ?);"; @@ -715,35 +1813,79 @@ EXEC SQL PREPARE mystmt FROM :stmt; ... EXEC SQL EXECUTE mystmt USING 42, 'foobar'; </programlisting> - If the statement you are executing returns values, then add an - <literal>INTO</literal> clause: + </para> + + <para> + When you don't need the prepared statement anymore, you should + deallocate it: +<programlisting> +EXEC SQL DEALLOCATE PREPARE <replaceable>name</replaceable>; +</programlisting> + </para> + </sect2> + + <sect2 id="ecpg-dynamic-with-result"> + <title>Executing a statement with a result set</title> + + <para> + To execute an SQL statement with a single result row, + <command>EXECUTE</command> can be used. To save the result, add + an <literal>INTO</literal> clause. <programlisting><![CDATA[ EXEC SQL BEGIN DECLARE SECTION; const char *stmt = "SELECT a, b, c FROM test1 WHERE a > ?"; int v1, v2; -VARCHAR v3; +VARCHAR v3[50]; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE mystmt FROM :stmt; ... -EXEC SQL EXECUTE mystmt INTO v1, v2, v3 USING 37; +EXEC SQL EXECUTE mystmt INTO :v1, :v2, :v3 USING 37; ]]> </programlisting> - An <command>EXECUTE</command> command can have an - <literal>INTO</literal> clause, a <literal>USING</literal> clause, - both, or neither. - </para> + An <command>EXECUTE</command> command can have an + <literal>INTO</literal> clause, a <literal>USING</literal> clause, + both, or neither. + </para> - <para> - When you don't need the prepared statement anymore, you should - deallocate it: + <para> + If a query is expected to return more than one result row, a + cursor should be used, as in the following example. + (See <xref linkend="ecpg-cursors"> for more details about the + cursor.) <programlisting> -EXEC SQL DEALLOCATE PREPARE <replaceable>name</replaceable>; +EXEC SQL BEGIN DECLARE SECTION; +char dbaname[128]; +char datname[128]; +char *stmt = "SELECT u.usename as dbaname, d.datname " + " FROM pg_database d, pg_user u " + " WHERE d.datdba = u.usesysid"; +EXEC SQL END DECLARE SECTION; + +EXEC SQL CONNECT TO testdb AS con1 USER testuser; + +EXEC SQL PREPARE stmt1 FROM :stmt; + +EXEC SQL DECLARE cursor1 CURSOR FOR stmt1; +EXEC SQL OPEN cursor1; + +EXEC SQL WHENEVER NOT FOUND DO BREAK; + +while (1) +{ + EXEC SQL FETCH cursor1 INTO :dbaname,:datname; + printf("dbaname=%s, datname=%s\n", dbaname, datname); +} + +EXEC SQL CLOSE cursor1; + +EXEC SQL COMMIT; +EXEC SQL DISCONNECT ALL; </programlisting> - </para> + </para> + </sect2> </sect1> - <sect1 id="ecpg-pgtypes"> <title>pgtypes library</title> @@ -771,7 +1913,7 @@ free(out); </programlisting> </para> - <sect2> + <sect2 id="ecpg-pgtypes-numeric"> <title>The numeric type</title> <para> The numeric type offers to do calculations with arbitrary precision. See @@ -1094,7 +2236,7 @@ int PGTYPESnumeric_from_decimal(decimal *src, numeric *dst); </para> </sect2> - <sect2> + <sect2 id="ecpg-pgtypes-date"> <title>The date type</title> <para> The date type in C enables your programs to deal with data of the SQL type @@ -1570,8 +2712,8 @@ int PGTYPESdate_defmt_asc(date *d, char *fmt, char *str); </variablelist> </para> </sect2> - - <sect2> + + <sect2 id="ecpg-pgtypes-timestamp"> <title>The timestamp type</title> <para> The timestamp type in C enables your programs to deal with data of the SQL @@ -2101,7 +3243,7 @@ int PGTYPEStimestamp_sub_interval(timestamp *tin, interval *span, timestamp *tou </para> </sect2> - <sect2> + <sect2 id="ecpg-pgtypes-interval"> <title>The interval type</title> <para> The interval type in C enables your programs to deal with data of the SQL @@ -2188,7 +3330,7 @@ int PGTYPESinterval_copy(interval *intvlsrc, interval *intvldest); </para> </sect2> - <sect2> + <sect2 id="ecpg-pgtypes-decimal"> <title>The decimal type</title> <para> The decimal type is similar to the numeric type. However it is limited to @@ -2231,8 +3373,8 @@ void PGTYPESdecimal_free(decimal *var); </variablelist> </para> </sect2> - - <sect2> + + <sect2 id="ecpg-pgtypes-errno"> <title>errno values of pgtypeslib</title> <para> <variablelist> @@ -2281,7 +3423,8 @@ void PGTYPESdecimal_free(decimal *var); <term><literal>PGTYPES_DATE_BAD_DATE</literal></term> <listitem> <para> - + An invalid date string was passed to + the <function>PGTYPESdate_from_asc</function> function. </para> </listitem> </varlistentry> @@ -2290,7 +3433,8 @@ void PGTYPESdecimal_free(decimal *var); <term><literal>PGTYPES_DATE_ERR_EARGS</literal></term> <listitem> <para> - + Invalid arguments were passed to the + <function>PGTYPESdate_defmt_asc</function> function. </para> </listitem> </varlistentry> @@ -2299,7 +3443,8 @@ void PGTYPESdecimal_free(decimal *var); <term><literal>PGTYPES_DATE_ERR_ENOSHORTDATE</literal></term> <listitem> <para> - + An invalid token in the input string was found by the + <function>PGTYPESdate_defmt_asc</function> function. </para> </listitem> </varlistentry> @@ -2308,7 +3453,10 @@ void PGTYPESdecimal_free(decimal *var); <term><literal>PGTYPES_INTVL_BAD_INTERVAL</literal></term> <listitem> <para> - + An invalid interval string was passed to the + <function>PGTYPESinterval_from_asc</function> function, or an + invalid interval value was passed to the + <function>PGTYPESinterval_to_asc</function> function. </para> </listitem> </varlistentry> @@ -2317,7 +3465,8 @@ void PGTYPESdecimal_free(decimal *var); <term><literal>PGTYPES_DATE_ERR_ENOTDMY</literal></term> <listitem> <para> - + There was a mismatch in the day/month/year assignment in the + <function>PGTYPESdate_defmt_asc</function> function. </para> </listitem> </varlistentry> @@ -2326,7 +3475,8 @@ void PGTYPESdecimal_free(decimal *var); <term><literal>PGTYPES_DATE_BAD_DAY</literal></term> <listitem> <para> - + An invalid day of the month value was found by + the <function>PGTYPESdate_defmt_asc</function> function. </para> </listitem> </varlistentry> @@ -2335,7 +3485,8 @@ void PGTYPESdecimal_free(decimal *var); <term><literal>PGTYPES_DATE_BAD_MONTH</literal></term> <listitem> <para> - + An invalid month value was found by + the <function>PGTYPESdate_defmt_asc</function> function. </para> </listitem> </varlistentry> @@ -2344,7 +3495,20 @@ void PGTYPESdecimal_free(decimal *var); <term><literal>PGTYPES_TS_BAD_TIMESTAMP</literal></term> <listitem> <para> - + An invalid timestamp string pass passed to + the <function>PGTYPEStimestamp_from_asc</function> function, + or an invalid timestamp value was passed to + the <function>PGTYPEStimestamp_to_asc</function> function. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>PGTYPES_TS_ERR_EINFTIME</literal></term> + <listitem> + <para> + An infinite timestamp value was encountered in a context that + cannot handle it. </para> </listitem> </varlistentry> @@ -2352,7 +3516,7 @@ void PGTYPESdecimal_free(decimal *var); </para> </sect2> - <sect2> + <sect2 id="ecpg-pgtypes-constants"> <title>Special constants of pgtypeslib</title> <para> <variablelist> @@ -2394,7 +3558,7 @@ void PGTYPESdecimal_free(decimal *var); </para> <sect2 id="ecpg-named-descriptors"> - <title>Named SQL Descriptor Areas</title> + <title>Named SQL descriptor areas</title> <para> A named SQL descriptor area consists of a header, which contains @@ -2430,7 +3594,7 @@ EXEC SQL FETCH NEXT FROM mycursor INTO SQL DESCRIPTOR mydesc; <para> For not yet executed prepared queries, the <command>DESCRIBE</command> statement can be used to get the metadata of the result set: -<programlisting> +<programlisting> EXEC SQL BEGIN DECLARE SECTION; char *sql_stmt = "SELECT * FROM table1"; EXEC SQL END DECLARE SECTION; @@ -2445,7 +3609,7 @@ EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc; so using <literal>DESCRIPTOR</literal> and <literal>SQL DESCRIPTOR</literal> produced named SQL Descriptor Areas. Now it is mandatory, omitting the <literal>SQL</literal> keyword produces SQLDA Descriptor Areas, - see <xref linkend="ecpg-sqlda-descriptors">. + see <xref linkend="ecpg-sqlda-descriptors">. </para> <para> @@ -2499,7 +3663,13 @@ EXEC SQL GET DESCRIPTOR <replaceable>name</replaceable> VALUE <replaceable>num</ <term><literal>DATETIME_INTERVAL_CODE</literal> (integer)</term> <listitem> <para> - ? + When <literal>TYPE</literal> is <literal>9</literal>, + <literal>DATETIME_INTERVAL_CODE</literal> will have a value of + <literal>1</literal> for <literal>DATE</literal>, + <literal>2</literal> for <literal>TIME</literal>, + <literal>3</literal> for <literal>TIMESTAMP</literal>, + <literal>4</literal> for <literal>TIME WITH TIME ZONE</literal>, or + <literal>5</literal> for <literal>TIMESTAMP WITH TIME ZONE</literal>. </para> </listitem> </varlistentry> @@ -2646,7 +3816,7 @@ EXEC SQL GET DESCRIPTOR mydesc VALUE 1 :id = DATA; </sect2> <sect2 id="ecpg-sqlda-descriptors"> - <title>SQLDA Descriptor Areas</title> + <title>SQLDA descriptor areas</title> <para> An SQLDA Descriptor Area is a C language structure which can be also used @@ -2668,56 +3838,80 @@ EXEC SQL DESCRIBE prepared_statement INTO mysqlda; </programlisting> </para> - <para> - The structure of SQLDA is: -<programlisting> -#define NAMEDATALEN 64 + <procedure> + <para> + The general flow of a program that uses SQLDA is: + </para> + <step><simpara>Prepare a query, and declare a cursor for it.</simpara></step> + <step><simpara>Declare an SQLDA for the result rows.</simpara></step> + <step><simpara>Declare an SQLDA for the input parameters, and initialize them (memory allocation, parameter settings).</simpara></step> + <step><simpara>Open a cursor with the input SQLDA.</simpara></step> + <step><simpara>Fetch rows from the cursor, and store them into an output SQLDA.</simpara></step> + <step><simpara>Read values from the output SQLDA into the host variables (with conversion if necessary).</simpara></step> + <step><simpara>Close the cursor.</simpara></step> + <step><simpara>Free the memory area allocated for the input SQLDA.</simpara></step> + </procedure> + + <sect3> + <title>SQLDA data structure</title> -struct sqlname -{ - short length; - char data[NAMEDATALEN]; -}; + <para> + SQLDA uses three data structure + types: <type>sqlda_t</type>, <type>sqlvar_t</type>, + and <type>struct sqlname</type>. + </para> -struct sqlvar_struct -{ - short sqltype; - short sqllen; - char *sqldata; - short *sqlind; - struct sqlname sqlname; -}; + <tip> + <para> + PostgreSQL's SQLDA has a similar data structure to the one in + IBM DB2 Universal Database, so some technical information on + DB2's SQLDA could help understanding PostgreSQL's one better. + </para> + </tip> + + <sect4 id="ecpg-sqlda-sqlda"> + <title>sqlda_t structure</title> + + <para> + The structure type <type>sqlda_t</type> is the type of the + actual SQLDA. It holds one record. And two or + more <type>sqlda_t</type> structures can be connected in a + linked list with the pointer in + the <structfield>desc_next</structfield> field, thus + representing an ordered collection of rows. So, when two or + more rows are fetched, the application can read them by + following the <structfield>desc_next</structfield> pointer in + each <type>sqlda_t</type> node. + </para> + <para> + The definition of <type>sqlda_t</type> is: +<programlisting> struct sqlda_struct { - char sqldaid[8]; - long sqldabc; - short sqln; - short sqld; - struct sqlda_struct *desc_next; - struct sqlvar_struct sqlvar[1]; + char sqldaid[8]; + long sqldabc; + short sqln; + short sqld; + struct sqlda_struct *desc_next; + struct sqlvar_struct sqlvar[1]; }; -typedef struct sqlvar_struct sqlvar_t; -typedef struct sqlda_struct sqlda_t; +typedef struct sqlda_struct sqlda_t; </programlisting> - </para> - <para> - The allocated data for an SQLDA structure is variable as it depends on the - number of fields in a result set and also depends on the length of the string - data values in a record. The individual fields of the <literal>SQLDA</literal> - structure are: + The meaning of the fields is: <variablelist> <varlistentry> <term><literal>sqldaid</></term> <listitem> <para> - It contains the "<literal>SQLDA </literal>" literal string. + It contains the literal string <literal>"SQLDA "</literal>. </para> </listitem> </varlistentry> + <varlistentry> <term><literal>sqldabc</></term> <listitem> @@ -2726,6 +3920,7 @@ typedef struct sqlda_struct sqlda_t; </para> </listitem> </varlistentry> + <varlistentry> <term><literal>sqln</></term> <listitem> @@ -2740,6 +3935,7 @@ typedef struct sqlda_struct sqlda_t; </para> </listitem> </varlistentry> + <varlistentry> <term><literal>sqld</></term> <listitem> @@ -2748,6 +3944,7 @@ typedef struct sqlda_struct sqlda_t; </para> </listitem> </varlistentry> + <varlistentry> <term><literal>desc_next</></term> <listitem> @@ -2761,15 +3958,43 @@ typedef struct sqlda_struct sqlda_t; <term><literal>sqlvar</></term> <listitem> <para> - This is the array of the fields in the result set. The fields are: + This is the array of the columns in the result set. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </sect4> - <variablelist> + <sect4 id="ecpg-sqlda-sqlvar"> + <title>sqlvar_t structure</title> + <para> + The structure type <type>sqlvar_t</type> holds a column value + and metadata such as type and length. The definition of the type + is: + +<programlisting> +struct sqlvar_struct +{ + short sqltype; + short sqllen; + char *sqldata; + short *sqlind; + struct sqlname sqlname; +}; + +typedef struct sqlvar_struct sqlvar_t; +</programlisting> + + The meaning of the fields is: + + <variablelist> <varlistentry> <term><literal>sqltype</></term> <listitem> <para> - It contains the type identifier of the field. For values, + Contains the type identifier of the field. For values, see <literal>enum ECPGttype</literal> in <literal>ecpgtype.h</literal>. </para> </listitem> @@ -2779,7 +4004,7 @@ typedef struct sqlda_struct sqlda_t; <term><literal>sqllen</></term> <listitem> <para> - It contains the binary length of the field. E.g. 4 bytes for <type>ECPGt_int</type>. + Contains the binary length of the field. e.g. 4 bytes for <type>ECPGt_int</type>. </para> </listitem> </varlistentry> @@ -2788,7 +4013,8 @@ typedef struct sqlda_struct sqlda_t; <term><literal>sqldata</></term> <listitem> <para> - <literal>(char *)sqldata</literal> points to the data. + Points to the data. The format of the data is described + in <xref linkend="ecpg-variables-type-mapping">. </para> </listitem> </varlistentry> @@ -2797,8 +4023,8 @@ typedef struct sqlda_struct sqlda_t; <term><literal>sqlind</></term> <listitem> <para> - <literal>(char *)sqlind</literal> points to the NULL indicator for data. - 0 means NOT NULL, -1 means NULL. + Points to the null indicator. 0 means not null, -1 means + null. </para> </listitem> </varlistentry> @@ -2807,22 +4033,37 @@ typedef struct sqlda_struct sqlda_t; <term><literal>sqlname</></term> <listitem> <para> - <literal>struct sqlname sqlname</literal> contains the name of the field - in a structure: + The the name of the field. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </sect4> + + <sect4 id="ecpg-sqlda-sqlname"> + <title>struct sqlname structure</title> + + <para> + A <type>struct sqlname</type> structure holds a column name. It + is used as a member of the <type>sqlvar_t</type> structure. The + definition of the structure is: <programlisting> +#define NAMEDATALEN 64 + struct sqlname { short length; char data[NAMEDATALEN]; }; </programlisting> - + The meaning of the fields is: <variablelist> <varlistentry> <term><literal>length</></term> <listitem> <para> - <literal>sqlname.length</literal> contains the length of the field name. + Contains the length of the field name. </para> </listitem> </varlistentry> @@ -2830,28 +4071,3756 @@ struct sqlname <term><literal>data</></term> <listitem> <para> - <literal>sqlname.data</literal> contains the actual field name. + Contains the actual field name. </para> </listitem> </varlistentry> </variablelist> + </para> + </sect4> + </sect3> + + <sect3 id="ecpg-sqlda-output"> + <title>Retreiving a result set using an SQLDA</title> + + <procedure> + <para> + The general steps to retrieve a query result set through an + SQLDA are: + </para> + <step><simpara>Declare an <type>sqlda_t</type> structure to receive the result set.</simpara></step> + <step><simpara>Execute <command>FETCH</>/<command>EXECUTE</>/<command>DESCRIBE</> commands to process a query specifying the declared SQLDA.</simpara></step> + <step><simpara>Check the number of records in the result set by looking at <structfield>sqln</>, a member of the <type>sqlda_t</type> structure.</simpara></step> + <step><simpara>Get the values of each column from <literal>sqlvar[0]</>, <literal>sqlvar[1]</>, etc., members of the <type>sqlda_t</type> structure.</simpara></step> + <step><simpara>Go to next row (<type>sqlda_t</type> structure) by following the <structfield>desc_next</> pointer, a member of the <type>sqlda_t</type> structure.</simpara></step> + <step><simpara>Repeat above as you need.</simpara></step> + </procedure> + + <para> + Here is an example retrieving a result set through an SQLDA. + </para> + + <para> + First, declare a <type>sqlda_t</type> structure to receive the result set. +<programlisting> +sqlda_t *sqlda1; +</programlisting> + </para> + + <para> + Next, specify the SQLDA in a command. This is + a <command>FETCH</> command example. +<programlisting> +EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1; +</programlisting> + </para> + + <para> + Run a loop following the linked list to retrieve the rows. +<programlisting> +sqlda_t *cur_sqlda; + +for (cur_sqlda = sqlda1; + cur_sqlda != NULL; + cur_sqlda = cur_sqlda->desc_next) +{ + ... +} +</programlisting> + </para> + + <para> + Inside the loop, run another loop to retrieve each column data + (<type>sqlvar_t</type> structure) of the row. +<programlisting> +for (i = 0; i < cur_sqlda->sqld; i++) +{ + sqlvar_t v = cur_sqlda->sqlvar[i]; + char *sqldata = v.sqldata; + short sqllen = v.sqllen; + ... +} +</programlisting> + </para> + + <para> + To get a column value, check the <structfield>sqltype</> value, + a member of the <type>sqlvar_t</type> structure. Then, switch + to an appropriate way, depending on the column type, to copy + data from the <structfield>sqlvar</> field to a host variable. +<programlisting> +char var_buf[1024]; + +switch (v.sqltype) +{ + case ECPGt_char: + memset(&var_buf, 0, sizeof(var_buf)); + memcpy(&var_buf, sqldata, (sizeof(var_buf) <= sqllen ? sizeof(var_buf) - 1 : sqllen)); + break; + + case ECPGt_int: /* integer */ + memcpy(&intval, sqldata, sqllen); + snprintf(var_buf, sizeof(var_buf), "%d", intval); + break; + + ... +} +</programlisting> + </para> + </sect3> + + <sect3 id="ecpg-sqlda-input"> + <title>Passing query parameters using an SQLDA</title> + + <procedure> + <para> + The general steps to use an SQLDA to pass input + parameters to a prepared query are: + </para> + <step><simpara>Create a prepared query (prepared statement)</simpara></step> + <step><simpara>Declare a sqlda_t structure as an input SQLDA.</simpara></step> + <step><simpara>Allocate memory area (as sqlda_t structure) for the input SQLDA.</simpara></step> + <step><simpara>Set (copy) input values in the allocated memory.</simpara></step> + <step><simpara>Open a cursor with specifying the input SQLDA.</simpara></step> + </procedure> + + <para> + Here is an example. + </para> + + <para> + First, create a prepared statement. +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; +char query[1024] = "SELECT d.oid, * FROM pg_database d, pg_stat_database s WHERE d.oid = s.datid AND (d.datname = ? OR d.oid = ?)"; +EXEC SQL END DECLARE SECTION; + +EXEC SQL PREPARE stmt1 FROM :query; +</programlisting> + </para> + + <para> + Next, allocate memory for an SQLDA, and set the number of input + parameters in <structfield>sqln</>, a member variable of + the <type>sqlda_t</type> structure. When two or more input + parameters are required for the prepared query, the application + has to allocate additional memory space which is calculated by + (nr. of params - 1) * sizeof(sqlvar_t). The example shown here + allocates memory space for two input parameters. +<programlisting> +sqlda_t *sqlda2; + +sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t)); +memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t)); + +sqlda2->sqln = 2; /* number of input variables */ +</programlisting> + </para> + + <para> + After memory allocation, store the parameter values into the + <literal>sqlvar[]</literal> array. (This is same array used for + retrieving column values when the SQLDA is receiving a result + set.) In this example, the input parameters + are <literal>"postgres"</literal>, having a string type, + and <literal>1</literal>, having an integer type. +<programlisting> +sqlda2->sqlvar[0].sqltype = ECPGt_char; +sqlda2->sqlvar[0].sqldata = "postgres"; +sqlda2->sqlvar[0].sqllen = 8; + +int intval = 1; +sqlda2->sqlvar[1].sqltype = ECPGt_int; +sqlda2->sqlvar[1].sqldata = (char *) &intval; +sqlda2->sqlvar[1].sqllen = sizeof(intval); +</programlisting> + </para> + + <para> + By opening a cursor and specifying the SQLDA that was set up + beforehand, the input parameters are passed to the prepared + statement. +<programlisting> +EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2; +</programlisting> + </para> + + <para> + Finally, after using input SQLDAs, the allocated memory space + must be freed explicitly, unlike SQLDAs used for receiving query + results. +<programlisting> +free(sqlda2); +</programlisting> + </para> + </sect3> + + <sect3 id="ecpg-sqlda-example"> + <title>A sample application using SQLDA</title> + + <para> + Here is an example program, which describes how to fetch access + statistics of the databases, specified by the input parameters, + from the system catalogs. + </para> + + <para> + This application joins two system tables, pg_database and + pg_stat_database on the database oid, and also fetches and shows + the database statistics which are retreived by two input + parameters (a database "postgres", and oid "1"). + </para> + + <para> + First, declare an SQLDA for input and an SQLDA for output. +<programlisting> +EXEC SQL include sqlda.h; + +sqlda_t *sqlda1; /* an output descriptor */ +sqlda_t *sqlda2; /* an input descriptor */ +</programlisting> + </para> + + <para> + Next, connect to the database, prepare a statement, and declare a + cursor for the prepared statement. +<programlisting> +int +main(void) +{ + EXEC SQL BEGIN DECLARE SECTION; + char query[1024] = "SELECT d.oid,* FROM pg_database d, pg_stat_database s WHERE d.oid=s.datid AND ( d.datname=? OR d.oid=? )"; + EXEC SQL END DECLARE SECTION; + + EXEC SQL CONNECT TO testdb AS con1 USER testuser; + + EXEC SQL PREPARE stmt1 FROM :query; + EXEC SQL DECLARE cur1 CURSOR FOR stmt1; +</programlisting> + </para> + + <para> + Next, put some values in the input SQLDA for the input + parameters. Allocate memory for the input SQLDA, and set the + number of input parameters to <literal>sqln</literal>. Store + type, value, and value length into <literal>sqltype</literal>, + <literal>sqldata</literal>, and <literal>sqllen</literal> in the + <literal>sqlvar</literal> structure. + +<programlisting> + /* Create SQLDA structure for input parameters. */ + sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t)); + memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t)); + sqlda2->sqln = 2; /* number of input variables */ + + sqlda2->sqlvar[0].sqltype = ECPGt_char; + sqlda2->sqlvar[0].sqldata = "postgres"; + sqlda2->sqlvar[0].sqllen = 8; + + intval = 1; + sqlda2->sqlvar[1].sqltype = ECPGt_int; + sqlda2->sqlvar[1].sqldata = (char *)&intval; + sqlda2->sqlvar[1].sqllen = sizeof(intval); +</programlisting> + </para> + + <para> + After setting up the input SQLDA, open a cursor with the input + SQLDA. + +<programlisting> + /* Open a cursor with input parameters. */ + EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2; +</programlisting> + </para> + + <para> + Fetch rows into the output SQLDA from the opened cursor. + (Generally, you have to call <command>FETCH</command> repeatedly + in the loop, to fetch all rows in the result set.) +<programlisting> + while (1) + { + sqlda_t *cur_sqlda; + + /* Assign descriptor to the cursor */ + EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1; +</programlisting> + </para> + + <para> + Next, retrieve the fetched records from the SQLDA, by following + the linked list of the <type>sqlda_t</type> structure. +<programlisting> + for (cur_sqlda = sqlda1 ; + cur_sqlda != NULL ; + cur_sqlda = cur_sqlda->desc_next) + { + ... +</programlisting> + </para> + + <para> + Read each columns in the first record. The number of columns is + stored in <structfield>sqld</>, the actual data of the first + column is stored in <literal>sqlvar[0]</>, both members of + the <type>sqlda_t</type> structure. + +<programlisting> + /* Print every column in a row. */ + for (i = 0; i < sqlda1->sqld; i++) + { + sqlvar_t v = sqlda1->sqlvar[i]; + char *sqldata = v.sqldata; + short sqllen = v.sqllen; + + strncpy(name_buf, v.sqlname.data, v.sqlname.length); + name_buf[v.sqlname.length] = '\0'; +</programlisting> + </para> + + <para> + Now, the column data is stored in the variable <varname>v</>. + Copy every datum into host variables, looking + at <literal>v.sqltype</> for the type of the column. +<programlisting> + switch (v.sqltype) { + int intval; + double doubleval; + unsigned long long int longlongval; + + case ECPGt_char: + memset(&var_buf, 0, sizeof(var_buf)); + memcpy(&var_buf, sqldata, (sizeof(var_buf) <= sqllen ? sizeof(var_buf)-1 : sqllen)); + break; + + case ECPGt_int: /* integer */ + memcpy(&intval, sqldata, sqllen); + snprintf(var_buf, sizeof(var_buf), "%d", intval); + break; + + ... + + default: + ... + } + + printf("%s = %s (type: %d)\n", name_buf, var_buf, v.sqltype); + } +</programlisting> + </para> + + <para> + Close the cursor after processing all of records, and disconnect + from the database. +<programlisting> + EXEC SQL CLOSE cur1; + EXEC SQL COMMIT; + + EXEC SQL DISCONNECT ALL; +</programlisting> + </para> + + <para> + The whole program is shown + in <xref linkend="ecpg-sqlda-example-example">. + </para> + + <example id="ecpg-sqlda-example-example"> + <title>Example SQLDA program</title> +<programlisting> +#include <stdlib.h> +#include <string.h> +#include <stdlib.h> +#include <stdio.h> +#include <unistd.h> + +EXEC SQL include sqlda.h; + +sqlda_t *sqlda1; /* descriptor for output */ +sqlda_t *sqlda2; /* descriptor for input */ + +EXEC SQL WHENEVER NOT FOUND DO BREAK; +EXEC SQL WHENEVER SQLERROR STOP; + +int +main(void) +{ + EXEC SQL BEGIN DECLARE SECTION; + char query[1024] = "SELECT d.oid,* FROM pg_database d, pg_stat_database s WHERE d.oid=s.datid AND ( d.datname=? OR d.oid=? )"; + + int intval; + unsigned long long int longlongval; + EXEC SQL END DECLARE SECTION; + + EXEC SQL CONNECT TO uptimedb AS con1 USER uptime; + + EXEC SQL PREPARE stmt1 FROM :query; + EXEC SQL DECLARE cur1 CURSOR FOR stmt1; + + /* Create a SQLDA structure for an input parameter */ + sqlda2 = (sqlda_t *)malloc(sizeof(sqlda_t) + sizeof(sqlvar_t)); + memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t)); + sqlda2->sqln = 2; /* a number of input variables */ + + sqlda2->sqlvar[0].sqltype = ECPGt_char; + sqlda2->sqlvar[0].sqldata = "postgres"; + sqlda2->sqlvar[0].sqllen = 8; + + intval = 1; + sqlda2->sqlvar[1].sqltype = ECPGt_int; + sqlda2->sqlvar[1].sqldata = (char *) &intval; + sqlda2->sqlvar[1].sqllen = sizeof(intval); + + /* Open a cursor with input parameters. */ + EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2; + + while (1) + { + sqlda_t *cur_sqlda; + + /* Assign descriptor to the cursor */ + EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1; + + for (cur_sqlda = sqlda1 ; + cur_sqlda != NULL ; + cur_sqlda = cur_sqlda->desc_next) + { + int i; + char name_buf[1024]; + char var_buf[1024]; + + /* Print every column in a row. */ + for (i=0 ; i<cur_sqlda->sqld ; i++) + { + sqlvar_t v = cur_sqlda->sqlvar[i]; + char *sqldata = v.sqldata; + short sqllen = v.sqllen; + + strncpy(name_buf, v.sqlname.data, v.sqlname.length); + name_buf[v.sqlname.length] = '\0'; + + switch (v.sqltype) + { + case ECPGt_char: + memset(&var_buf, 0, sizeof(var_buf)); + memcpy(&var_buf, sqldata, (sizeof(var_buf)<=sqllen ? sizeof(var_buf)-1 : sqllen) ); + break; + + case ECPGt_int: /* integer */ + memcpy(&intval, sqldata, sqllen); + snprintf(var_buf, sizeof(var_buf), "%d", intval); + break; + + case ECPGt_long_long: /* bigint */ + memcpy(&longlongval, sqldata, sqllen); + snprintf(var_buf, sizeof(var_buf), "%lld", longlongval); + break; + + default: + { + int i; + memset(var_buf, 0, sizeof(var_buf)); + for (i = 0; i < sqllen; i++) + { + char tmpbuf[16]; + snprintf(tmpbuf, sizeof(tmpbuf), "%02x ", (unsigned char) sqldata[i]); + strncat(var_buf, tmpbuf, sizeof(var_buf)); + } + } + break; + } + + printf("%s = %s (type: %d)\n", name_buf, var_buf, v.sqltype); + } + + printf("\n"); + } + } + + EXEC SQL CLOSE cur1; + EXEC SQL COMMIT; + + EXEC SQL DISCONNECT ALL; + + return 0; +} +</programlisting> + + <para> + The output of this example should look something like the + following (some numbers will vary). + </para> + +<screen> +oid = 1 (type: 1) +datname = template1 (type: 1) +datdba = 10 (type: 1) +encoding = 0 (type: 5) +datistemplate = t (type: 1) +datallowconn = t (type: 1) +datconnlimit = -1 (type: 5) +datlastsysoid = 11510 (type: 1) +datfrozenxid = 379 (type: 1) +dattablespace = 1663 (type: 1) +datconfig = (type: 1) +datacl = {=c/uptime,uptime=CTc/uptime} (type: 1) +datid = 1 (type: 1) +datname = template1 (type: 1) +numbackends = 0 (type: 5) +xact_commit = 113606 (type: 9) +xact_rollback = 0 (type: 9) +blks_read = 130 (type: 9) +blks_hit = 7341714 (type: 9) +tup_returned = 38262679 (type: 9) +tup_fetched = 1836281 (type: 9) +tup_inserted = 0 (type: 9) +tup_updated = 0 (type: 9) +tup_deleted = 0 (type: 9) + +oid = 11511 (type: 1) +datname = postgres (type: 1) +datdba = 10 (type: 1) +encoding = 0 (type: 5) +datistemplate = f (type: 1) +datallowconn = t (type: 1) +datconnlimit = -1 (type: 5) +datlastsysoid = 11510 (type: 1) +datfrozenxid = 379 (type: 1) +dattablespace = 1663 (type: 1) +datconfig = (type: 1) +datacl = (type: 1) +datid = 11511 (type: 1) +datname = postgres (type: 1) +numbackends = 0 (type: 5) +xact_commit = 221069 (type: 9) +xact_rollback = 18 (type: 9) +blks_read = 1176 (type: 9) +blks_hit = 13943750 (type: 9) +tup_returned = 77410091 (type: 9) +tup_fetched = 3253694 (type: 9) +tup_inserted = 0 (type: 9) +tup_updated = 0 (type: 9) +tup_deleted = 0 (type: 9) +</screen> + </example> + </sect3> + </sect2> + </sect1> + + <sect1 id="ecpg-errors"> + <title>Error Handling</title> + + <para> + This section describes how you can handle exceptional conditions + and warnings in an embedded SQL program. There are two + nonexclusive facilities for this. + + <itemizedlist> + <listitem> + <simpara> + Callbacks can be configured to handle warning and error + conditions using the <literal>WHENEVER</literal> command. + </simpara> + </listitem> + + <listitem> + <simpara> + Detailed information about the error or warning can be obtained + from the <varname>sqlca</varname> variable. + </simpara> + </listitem> + </itemizedlist> + </para> + + <sect2 id="ecpg-whenever"> + <title>Setting callbacks</title> + + <para> + One simple method to catch errors and warnings is to set a + specific action to be executed whenever a particular condition + occurs. In general: +<programlisting> +EXEC SQL WHENEVER <replaceable>condition</replaceable> <replaceable>action</replaceable>; +</programlisting> + </para> + + <para> + <replaceable>condition</replaceable> can be one of the following: + + <variablelist> + <varlistentry> + <term><literal>SQLERROR</literal></term> + <listitem> + <para> + The specified action is called whenever an error occurs during + the execution of an SQL statement. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>SQLWARNING</literal></term> + <listitem> + <para> + The specified action is called whenever a warning occurs + during the execution of an SQL statement. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>NOT FOUND</literal></term> + <listitem> + <para> + The specified action is called whenever an SQL statement + retrieves or affects zero rows. (This condition is not an + error, but you might be interested in handling it specially.) + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + + <para> + <replaceable>action</replaceable> can be one of the following: + + <variablelist> + <varlistentry> + <term><literal>CONTINUE</literal></term> + <listitem> + <para> + This effectively means that the condition is ignored. This is + the default. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>GOTO <replaceable>label</replaceable></literal></term> + <term><literal>GO TO <replaceable>label</replaceable></literal></term> + <listitem> + <para> + Jump to the specified label (using a C <literal>goto</literal> + statement). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>SQLPRINT</literal></term> + <listitem> + <para> + Print a message to standard error. This is useful for simple + programs or during prototyping. The details of the message + cannot be configured. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>STOP</literal></term> + <listitem> + <para> + Call <literal>exit(1)</literal>, which will terminate the + program. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>DO BREAK</literal></term> + <listitem> + <para> + Execute the C statement <literal>break</literal>. This should + only be used in loops or <literal>switch</literal> statements. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>CALL <replaceable>name</replaceable> (<replaceable>args</replaceable>)</literal></term> + <term><literal>DO <replaceable>name</replaceable> (<replaceable>args</replaceable>)</literal></term> + <listitem> + <para> + Call the specified C functions with the specified arguments. + </para> + </listitem> + </varlistentry> + </variablelist> + + The SQL standard only provides for the actions + <literal>CONTINUE</literal> and <literal>GOTO</literal> (and + <literal>GO TO</literal>). + </para> + + <para> + Here is an example that you might want to use in a simple program. + It prints a simple message when a warning occurs and aborts the + program when an error happens: +<programlisting> +EXEC SQL WHENEVER SQLWARNING SQLPRINT; +EXEC SQL WHENEVER SQLERROR STOP; +</programlisting> + </para> + + <para> + The statement <literal>EXEC SQL WHENEVER</literal> is a directive + of the SQL preprocessor, not a C statement. The error or warning + actions that it sets apply to all embedded SQL statements that + appear below the point where the handler is set, unless a + different action was set for the same condition between the first + <literal>EXEC SQL WHENEVER</literal> and the SQL statement causing + the condition, regardless of the flow of control in the C program. + So neither of the two following C program excerpts will have the + desired effect: +<programlisting> +/* + * WRONG + */ +int main(int argc, char *argv[]) +{ + ... + if (verbose) { + EXEC SQL WHENEVER SQLWARNING SQLPRINT; + } + ... + EXEC SQL SELECT ...; + ... +} +</programlisting> + +<programlisting> +/* + * WRONG + */ +int main(int argc, char *argv[]) +{ + ... + set_error_handler(); + ... + EXEC SQL SELECT ...; + ... +} + +static void set_error_handler(void) +{ + EXEC SQL WHENEVER SQLERROR STOP; +} +</programlisting> + </para> + </sect2> + + <sect2 id="ecpg-sqlca"> + <title>sqlca</title> + + <para> + For more powerful error handling, the embedded SQL interface + provides a global variable with the name <varname>sqlca</varname> + (SQL communication area) + that has the following structure: +<programlisting> +struct +{ + char sqlcaid[8]; + long sqlabc; + long sqlcode; + struct + { + int sqlerrml; + char sqlerrmc[SQLERRMC_LEN]; + } sqlerrm; + char sqlerrp[8]; + long sqlerrd[6]; + char sqlwarn[8]; + char sqlstate[5]; +} sqlca; +</programlisting> + (In a multithreaded program, every thread automatically gets its + own copy of <varname>sqlca</varname>. This works similarly to the + handling of the standard C global variable + <varname>errno</varname>.) + </para> + + <para> + <varname>sqlca</varname> covers both warnings and errors. If + multiple warnings or errors occur during the execution of a + statement, then <varname>sqlca</varname> will only contain + information about the last one. + </para> + + <para> + If no error occurred in the last <acronym>SQL</acronym> statement, + <literal>sqlca.sqlcode</literal> will be 0 and + <literal>sqlca.sqlstate</literal> will be + <literal>"00000"</literal>. If a warning or error occurred, then + <literal>sqlca.sqlcode</literal> will be negative and + <literal>sqlca.sqlstate</literal> will be different from + <literal>"00000"</literal>. A positive + <literal>sqlca.sqlcode</literal> indicates a harmless condition, + such as that the last query returned zero rows. + <literal>sqlcode</literal> and <literal>sqlstate</literal> are two + different error code schemes; details appear below. + </para> + + <para> + If the last SQL statement was successful, then + <literal>sqlca.sqlerrd[1]</literal> contains the OID of the + processed row, if applicable, and + <literal>sqlca.sqlerrd[2]</literal> contains the number of + processed or returned rows, if applicable to the command. + </para> + + <para> + In case of an error or warning, + <literal>sqlca.sqlerrm.sqlerrmc</literal> will contain a string + that describes the error. The field + <literal>sqlca.sqlerrm.sqlerrml</literal> contains the length of + the error message that is stored in + <literal>sqlca.sqlerrm.sqlerrmc</literal> (the result of + <function>strlen()</function>, not really interesting for a C + programmer). Note that some messages are too long to fit in the + fixed-size <literal>sqlerrmc</literal> array; they will be truncated. + </para> + + <para> + In case of a warning, <literal>sqlca.sqlwarn[2]</literal> is set + to <literal>W</literal>. (In all other cases, it is set to + something different from <literal>W</literal>.) If + <literal>sqlca.sqlwarn[1]</literal> is set to + <literal>W</literal>, then a value was truncated when it was + stored in a host variable. <literal>sqlca.sqlwarn[0]</literal> is + set to <literal>W</literal> if any of the other elements are set + to indicate a warning. + </para> + + <para> + The fields <structfield>sqlcaid</structfield>, + <structfield>sqlcabc</structfield>, + <structfield>sqlerrp</structfield>, and the remaining elements of + <structfield>sqlerrd</structfield> and + <structfield>sqlwarn</structfield> currently contain no useful + information. + </para> + + <para> + The structure <varname>sqlca</varname> is not defined in the SQL + standard, but is implemented in several other SQL database + systems. The definitions are similar at the core, but if you want + to write portable applications, then you should investigate the + different implementations carefully. + </para> + + <para> + Here is one example that combines the use of <literal>WHENEVER</> + and <varname>sqlca</varname>, printing out the contents + of <varname>sqlca</varname> when an error occurs. This is perhaps + useful for debugging or prototyping applications, before + installing a more <quote>user-friendly</quote> error handler. + +<programlisting> +EXEC SQL WHENEVER SQLERROR CALL print_sqlca(); + +void +print_sqlca() +{ + fprintf(stderr, "==== sqlca ====\n"); + fprintf(stderr, "sqlcode: %ld\n", sqlca.sqlcode); + fprintf(stderr, "sqlerrm.sqlerrml: %d\n", sqlca.sqlerrm.sqlerrml); + fprintf(stderr, "sqlerrm.sqlerrmc: %s\n", sqlca.sqlerrm.sqlerrmc); + fprintf(stderr, "sqlerrd: %ld %ld %ld %ld %ld %ld\n", sqlca.sqlerrd[0],sqlca.sqlerrd[1],sqlca.sqlerrd[2], + sqlca.sqlerrd[3],sqlca.sqlerrd[4],sqlca.sqlerrd[5]); + fprintf(stderr, "sqlwarn: %d %d %d %d %d %d %d %d\n", sqlca.sqlwarn[0], sqlca.sqlwarn[1], sqlca.sqlwarn[2], + sqlca.sqlwarn[3], sqlca.sqlwarn[4], sqlca.sqlwarn[5], + sqlca.sqlwarn[6], sqlca.sqlwarn[7]); + fprintf(stderr, "sqlstate: %5s\n", sqlca.sqlstate); + fprintf(stderr, "===============\n"); +} +</programlisting> + + The result could look as follows (here an error due to a + misspelled table name): + +<screen> +==== sqlca ==== +sqlcode: -400 +sqlerrm.sqlerrml: 49 +sqlerrm.sqlerrmc: relation "pg_databasep" does not exist on line 38 +sqlerrd: 0 0 0 0 0 0 +sqlwarn: 0 0 0 0 0 0 0 0 +sqlstate: 42P01 +=============== +</screen> + </para> + </sect2> + + <sect2 id="ecpg-sqlstate-sqlcode"> + <title><literal>SQLSTATE</literal> vs <literal>SQLCODE</literal></title> + + <para> + The fields <literal>sqlca.sqlstate</literal> and + <literal>sqlca.sqlcode</literal> are two different schemes that + provide error codes. Both are derived from the SQL standard, but + <literal>SQLCODE</literal> has been marked deprecated in the SQL-92 + edition of the standard and has been dropped in later editions. + Therefore, new applications are strongly encouraged to use + <literal>SQLSTATE</literal>. + </para> + + <para> + <literal>SQLSTATE</literal> is a five-character array. The five + characters contain digits or upper-case letters that represent + codes of various error and warning conditions. + <literal>SQLSTATE</literal> has a hierarchical scheme: the first + two characters indicate the general class of the condition, the + last three characters indicate a subclass of the general + condition. A successful state is indicated by the code + <literal>00000</literal>. The <literal>SQLSTATE</literal> codes are for + the most part defined in the SQL standard. The + <productname>PostgreSQL</productname> server natively supports + <literal>SQLSTATE</literal> error codes; therefore a high degree + of consistency can be achieved by using this error code scheme + throughout all applications. For further information see + <xref linkend="errcodes-appendix">. + </para> + + <para> + <literal>SQLCODE</literal>, the deprecated error code scheme, is a + simple integer. A value of 0 indicates success, a positive value + indicates success with additional information, a negative value + indicates an error. The SQL standard only defines the positive + value +100, which indicates that the last command returned or + affected zero rows, and no specific negative values. Therefore, + this scheme can only achieve poor portability and does not have a + hierarchical code assignment. Historically, the embedded SQL + processor for <productname>PostgreSQL</productname> has assigned + some specific <literal>SQLCODE</literal> values for its use, which + are listed below with their numeric value and their symbolic name. + Remember that these are not portable to other SQL implementations. + To simplify the porting of applications to the + <literal>SQLSTATE</literal> scheme, the corresponding + <literal>SQLSTATE</literal> is also listed. There is, however, no + one-to-one or one-to-many mapping between the two schemes (indeed + it is many-to-many), so you should consult the global + <literal>SQLSTATE</literal> listing in <xref linkend="errcodes-appendix"> + in each case. + </para> + + <para> + These are the assigned <literal>SQLCODE</literal> values: + + <variablelist> + <varlistentry> + <term>0 (<symbol>ECPG_NO_ERROR</symbol>)</term> + <listitem> + <para> + Indicates no error. (SQLSTATE 00000) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>100 (<symbol>ECPG_NOT_FOUND</symbol>)</term> + <listitem> + <para> + This is a harmless condition indicating that the last command + retrieved or processed zero rows, or that you are at the end of + the cursor. (SQLSTATE 02000) + </para> + + <para> + When processing a cursor in a loop, you could use this code as + a way to detect when to abort the loop, like this: +<programlisting> +while (1) +{ + EXEC SQL FETCH ... ; + if (sqlca.sqlcode == ECPG_NOT_FOUND) + break; +} +</programlisting> + But <literal>WHENEVER NOT FOUND DO BREAK</literal> effectively + does this internally, so there is usually no advantage in + writing this out explicitly. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-12 (<symbol>ECPG_OUT_OF_MEMORY</symbol>)</term> + <listitem> + <para> + Indicates that your virtual memory is exhausted. The numeric + value is defined as <literal>-ENOMEM</literal>. (SQLSTATE + YE001) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-200 (<symbol>ECPG_UNSUPPORTED</symbol>)</term> + <listitem> + <para> + 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. (SQLSTATE YE002) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-201 (<symbol>ECPG_TOO_MANY_ARGUMENTS</symbol>)</term> + <listitem> + <para> + This means that the command specified more host variables than + the command expected. (SQLSTATE 07001 or 07002) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-202 (<symbol>ECPG_TOO_FEW_ARGUMENTS</symbol>)</term> + <listitem> + <para> + This means that the command specified fewer host variables than + the command expected. (SQLSTATE 07001 or 07002) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-203 (<symbol>ECPG_TOO_MANY_MATCHES</symbol>)</term> + <listitem> + <para> + This means a query has returned multiple rows but the statement + was only prepared to store one result row (for example, because + the specified variables are not arrays). (SQLSTATE 21000) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-204 (<symbol>ECPG_INT_FORMAT</symbol>)</term> + <listitem> + <para> + The host variable is of type <type>int</type> and the datum in + the database is of a different type and contains a value that + cannot be interpreted as an <type>int</type>. The library uses + <function>strtol()</function> for this conversion. (SQLSTATE + 42804) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-205 (<symbol>ECPG_UINT_FORMAT</symbol>)</term> + <listitem> + <para> + The host variable is of type <type>unsigned int</type> and the + datum in the database is of a different type and contains a + value that cannot be interpreted as an <type>unsigned + int</type>. The library uses <function>strtoul()</function> + for this conversion. (SQLSTATE 42804) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-206 (<symbol>ECPG_FLOAT_FORMAT</symbol>)</term> + <listitem> + <para> + The host variable is of type <type>float</type> and the datum + in the database is of another type and contains a value that + cannot be interpreted as a <type>float</type>. The library + uses <function>strtod()</function> for this conversion. + (SQLSTATE 42804) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-207 (<symbol>ECPG_NUMERIC_FORMAT</symbol>)</term> + <listitem> + <para> + The host variable is of type <type>numeric</type> and the datum + in the database is of another type and contains a value that + cannot be interpreted as a <type>numeric</type> value. + (SQLSTATE 42804) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-208 (<symbol>ECPG_INTERVAL_FORMAT</symbol>)</term> + <listitem> + <para> + The host variable is of type <type>interval</type> and the datum + in the database is of another type and contains a value that + cannot be interpreted as an <type>interval</type> value. + (SQLSTATE 42804) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-209 (<symbol>ECPG_DATE_FORMAT</symbol>)</term> + <listitem> + <para> + The host variable is of type <type>date</type> and the datum in + the database is of another type and contains a value that + cannot be interpreted as a <type>date</type> value. + (SQLSTATE 42804) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-210 (<symbol>ECPG_TIMESTAMP_FORMAT</symbol>)</term> + <listitem> + <para> + The host variable is of type <type>timestamp</type> and the + datum in the database is of another type and contains a value + that cannot be interpreted as a <type>timestamp</type> value. + (SQLSTATE 42804) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-211 (<symbol>ECPG_CONVERT_BOOL</symbol>)</term> + <listitem> + <para> + This means the host variable is of type <type>bool</type> and + the datum in the database is neither <literal>'t'</> nor + <literal>'f'</>. (SQLSTATE 42804) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-212 (<symbol>ECPG_EMPTY</symbol>)</term> + <listitem> + <para> + The statement sent to the <productname>PostgreSQL</productname> + server was empty. (This cannot normally happen in an embedded + SQL program, so it might point to an internal error.) (SQLSTATE + YE002) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-213 (<symbol>ECPG_MISSING_INDICATOR</symbol>)</term> + <listitem> + <para> + A null value was returned and no null indicator variable was + supplied. (SQLSTATE 22002) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-214 (<symbol>ECPG_NO_ARRAY</symbol>)</term> + <listitem> + <para> + An ordinary variable was used in a place that requires an + array. (SQLSTATE 42804) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-215 (<symbol>ECPG_DATA_NOT_ARRAY</symbol>)</term> + <listitem> + <para> + The database returned an ordinary variable in a place that + requires array value. (SQLSTATE 42804) + </para> + </listitem> + </varlistentry> + +<![IGNORE[ + <!-- disabled by #if 0 in ecpglib --> + <varlistentry> + <term>-216 (<symbol>ECPG_ARRAY_INSERT</symbol>)</term> + <listitem> + <para> + The value could not be inserted into the array. (SQLSTATE + 42804) + </para> + </listitem> + </varlistentry> +]]> + + <varlistentry> + <term>-220 (<symbol>ECPG_NO_CONN</symbol>)</term> + <listitem> + <para> + The program tried to access a connection that does not exist. + (SQLSTATE 08003) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-221 (<symbol>ECPG_NOT_CONN</symbol>)</term> + <listitem> + <para> + The program tried to access a connection that does exist but is + not open. (This is an internal error.) (SQLSTATE YE002) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-230 (<symbol>ECPG_INVALID_STMT</symbol>)</term> + <listitem> + <para> + The statement you are trying to use has not been prepared. + (SQLSTATE 26000) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-239 (<symbol>ECPG_INFORMIX_DUPLICATE_KEY</symbol>)</term> + <listitem> + <para> + Duplicate key error, violation of unique constraint (Informix + compatibility mode). (SQLSTATE 23505) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-240 (<symbol>ECPG_UNKNOWN_DESCRIPTOR</symbol>)</term> + <listitem> + <para> + The descriptor specified was not found. The statement you are + trying to use has not been prepared. (SQLSTATE 33000) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-241 (<symbol>ECPG_INVALID_DESCRIPTOR_INDEX</symbol>)</term> + <listitem> + <para> + The descriptor index specified was out of range. (SQLSTATE + 07009) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-242 (<symbol>ECPG_UNKNOWN_DESCRIPTOR_ITEM</symbol>)</term> + <listitem> + <para> + An invalid descriptor item was requested. (This is an internal + error.) (SQLSTATE YE002) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-243 (<symbol>ECPG_VAR_NOT_NUMERIC</symbol>)</term> + <listitem> + <para> + During the execution of a dynamic statement, the database + returned a numeric value and the host variable was not numeric. + (SQLSTATE 07006) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-244 (<symbol>ECPG_VAR_NOT_CHAR</symbol>)</term> + <listitem> + <para> + During the execution of a dynamic statement, the database + returned a non-numeric value and the host variable was numeric. + (SQLSTATE 07006) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-284 (<symbol>ECPG_INFORMIX_SUBSELECT_NOT_ONE</symbol>)</term> + <listitem> + <para> + A result of the subquery is not single row (Informix + compatibility mode). (SQLSTATE 21000) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-400 (<symbol>ECPG_PGSQL</symbol>)</term> + <listitem> + <para> + Some error caused by the <productname>PostgreSQL</productname> + server. The message contains the error message from the + <productname>PostgreSQL</productname> server. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-401 (<symbol>ECPG_TRANS</symbol>)</term> + <listitem> + <para> + The <productname>PostgreSQL</productname> server signaled that + we cannot start, commit, or rollback the transaction. + (SQLSTATE 08007) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-402 (<symbol>ECPG_CONNECT</symbol>)</term> + <listitem> + <para> + The connection attempt to the database did not succeed. + (SQLSTATE 08001) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-403 (<symbol>ECPG_DUPLICATE_KEY</symbol>)</term> + <listitem> + <para> + Duplicate key error, violation of unique constraint. (SQLSTATE + 23505) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-404 (<symbol>ECPG_SUBSELECT_NOT_ONE</symbol>)</term> + <listitem> + <para> + A result for the subquery is not single row. (SQLSTATE 21000) + </para> + </listitem> + </varlistentry> + +<![IGNORE[ + <!-- currently not used by the code --> + <varlistentry> + <term>-600 (<symbol>ECPG_WARNING_UNRECOGNIZED</symbol>)</term> + <listitem> + <para> + An unrecognized warning was received from the server. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-601 (<symbol>ECPG_WARNING_QUERY_IGNORED</symbol>)</term> + <listitem> + <para> + Current transaction is aborted. Queries are ignored until the + end of the transaction block. + </para> + </listitem> + </varlistentry> +]]> + + <varlistentry> + <term>-602 (<symbol>ECPG_WARNING_UNKNOWN_PORTAL</symbol>)</term> + <listitem> + <para> + An invalid cursor name was specified. (SQLSTATE 34000) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-603 (<symbol>ECPG_WARNING_IN_TRANSACTION</symbol>)</term> + <listitem> + <para> + Transaction is in progress. (SQLSTATE 25001) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-604 (<symbol>ECPG_WARNING_NO_TRANSACTION</symbol>)</term> + <listitem> + <para> + There is no active (in-progress) transaction. (SQLSTATE 25P01) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>-605 (<symbol>ECPG_WARNING_PORTAL_EXISTS</symbol>)</term> + <listitem> + <para> + An existing cursor name was specified. (SQLSTATE 42P03) + </para> + </listitem> + </varlistentry> + + </variablelist> + </para> + </sect2> + </sect1> + + <sect1 id="ecpg-preproc"> + <title>Preprocessor directives</title> + + <para> + Several preprocessor directives are available that modify how + the <command>ecpg</command> preprocessor parses and processes a + file. + </para> + + <sect2 id="ecpg-include"> + <title>Including files</title> + + <para> + To include an external file into your embedded SQL program, use: +<programlisting> +EXEC SQL INCLUDE <replaceable>filename</replaceable>; +EXEC SQL INCLUDE <<replaceable>filename</replaceable>>; +EXEC SQL INCLUDE "<replaceable>filename</replaceable>"; +</programlisting> + The embedded SQL preprocessor will look for a file named + <literal><replaceable>filename</replaceable>.h</literal>, + preprocess it, and include it in the resulting C output. Thus, + embedded SQL statements in the included file are handled correctly. + </para> + + <para> + The <command>ecpg</command> preprocessor will search a file at + several directories in following order: + + <itemizedlist> + <listitem><simpara>current directory</simpara></listitem> + <listitem><simpara><filename>/usr/local/include</filename></simpara></listitem> + <listitem><simpara>PostgreSQL include directory, defined at build time (e.g., <filename>/usr/local/pgsql/include</filename>)</simpara></listitem> + <listitem><simpara><filename>/usr/include</filename></simpara></listitem> + </itemizedlist> + + But when <literal>EXEC SQL INCLUDE + "<replaceable>filename</replaceable>"</literal> is used, only the + current directory is searched. + </para> + + <para> + In each directory, the preprocessor will first look for the file + name as given, and if not found will append <literal>.h</literal> + to the file name and try again (unless the specified file name + already has that suffix). + </para> + + <para> + Note that <command>EXEC SQL INCLUDE</command> is <emphasis>not</emphasis> the same as: +<programlisting> +#include <<replaceable>filename</replaceable>.h> +</programlisting> + because this file would not be subject to SQL command preprocessing. + Naturally, you can continue to use the C + <literal>#include</literal> directive to include other header + files. + </para> + + <note> + <para> + The include file name is case-sensitive, even though the rest of + the <literal>EXEC SQL INCLUDE</literal> command follows the normal + SQL case-sensitivity rules. + </para> + </note> + </sect2> + + <sect2 id="ecpg-define"> + <title>The define and undef directives</title> + <para> + Similar to the directive <literal>#define</literal> that is known from C, + embedded SQL has a similar concept: +<programlisting> +EXEC SQL DEFINE <replaceable>name</>; +EXEC SQL DEFINE <replaceable>name</> <replaceable>value</>; +</programlisting> + So you can define a name: +<programlisting> +EXEC SQL DEFINE HAVE_FEATURE; +</programlisting> + And you can also define constants: +<programlisting> +EXEC SQL DEFINE MYNUMBER 12; +EXEC SQL DEFINE MYSTRING 'abc'; +</programlisting> + Use <literal>undef</> to remove a previous definition: +<programlisting> +EXEC SQL UNDEF MYNUMBER; +</programlisting> + </para> + + <para> + Of course you can continue to use the C versions <literal>#define</literal> + and <literal>#undef</literal> in your embedded SQL program. The difference + is where your defined values get evaluated. If you use <literal>EXEC SQL + DEFINE</> then the <command>ecpg</> preprocessor evaluates the defines and substitutes + the values. For example if you write: +<programlisting> +EXEC SQL DEFINE MYNUMBER 12; +... +EXEC SQL UPDATE Tbl SET col = MYNUMBER; +</programlisting> + then <command>ecpg</> will already do the substitution and your C compiler will never + see any name or identifier <literal>MYNUMBER</>. Note that you cannot use + <literal>#define</literal> for a constant that you are going to use in an + embedded SQL query because in this case the embedded SQL precompiler is not + able to see this declaration. + </para> + </sect2> + + <sect2 id="ecpg-ifdef"> + <title>ifdef, ifndef, else, elif, and endif directives</title> + <para> + You can use the following directives to compile code sections conditionally: + + <variablelist> + <varlistentry> + <term><literal>EXEC SQL ifdef <replaceable>name</>;</literal></term> + <listitem> + <para> + Checks a <replaceable>name</> and processes subsequent lines if + <replaceable>name</> has been created with <literal>EXEC SQL define + <replaceable>name</></literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>EXEC SQL ifndef <replaceable>name</>;</literal></term> + <listitem> + <para> + Checks a <replaceable>name</> and processes subsequent lines if + <replaceable>name</> has <emphasis>not</emphasis> been created with + <literal>EXEC SQL define <replaceable>name</></literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>EXEC SQL else;</literal></term> + <listitem> + <para> + Starts processing an alternative section to a section introduced by + either <literal>EXEC SQL ifdef <replaceable>name</></literal> or + <literal>EXEC SQL ifndef <replaceable>name</></literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>EXEC SQL elif <replaceable>name</>;</literal></term> + <listitem> + <para> + Checks <replaceable>name</> and starts an alternative section if + <replaceable>name</> has been created with <literal>EXEC SQL define + <replaceable>name</></literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>EXEC SQL endif;</literal></term> + <listitem> + <para> + Ends an alternative section. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + + <para> + Example: +<programlisting> +EXEC SQL ifndef TZVAR; +EXEC SQL SET TIMEZONE TO 'GMT'; +EXEC SQL elif TZNAME; +EXEC SQL SET TIMEZONE TO TZNAME; +EXEC SQL else; +EXEC SQL SET TIMEZONE TO TZVAR; +EXEC SQL endif; +</programlisting> + </para> + + </sect2> + </sect1> + + <sect1 id="ecpg-process"> + <title>Processing Embedded SQL Programs</title> + + <para> + 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 <acronym>SQL</acronym> + <acronym>C</acronym> preprocessor, which converts the + <acronym>SQL</acronym> 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 <acronym>SQL</acronym> command using + the <application>libpq</application> interface, and put the result + in the arguments specified for output. + </para> + + <para> + The preprocessor program is called <filename>ecpg</filename> and is + included in a normal <productname>PostgreSQL</> installation. + Embedded SQL programs are typically named with an extension + <filename>.pgc</filename>. If you have a program file called + <filename>prog1.pgc</filename>, you can preprocess it by simply + calling: +<programlisting> +ecpg prog1.pgc +</programlisting> + This will create a file called <filename>prog1.c</filename>. If + your input files do not follow the suggested naming pattern, you + can specify the output file explicitly using the + <option>-o</option> option. + </para> + + <para> + The preprocessed file can be compiled normally, for example: +<programlisting> +cc -c prog1.c +</programlisting> + The generated C source files include header files from the + <productname>PostgreSQL</> installation, so if you installed + <productname>PostgreSQL</> in a location that is not searched by + default, you have to add an option such as + <literal>-I/usr/local/pgsql/include</literal> to the compilation + command line. + </para> + + <para> + To link an embedded SQL program, you need to include the + <filename>libecpg</filename> library, like so: +<programlisting> +cc -o myprog prog1.o prog2.o ... -lecpg +</programlisting> + Again, you might have to add an option like + <literal>-L/usr/local/pgsql/lib</literal> to that command line. + </para> + + <para> + If you manage the build process of a larger project using + <application>make</application>, it might be convenient to include + the following implicit rule to your makefiles: +<programlisting> +ECPG = ecpg + +%.c: %.pgc + $(ECPG) $< +</programlisting> + </para> + + <para> + The complete syntax of the <command>ecpg</command> command is + detailed in <xref linkend="app-ecpg">. + </para> + + <para> + The <application>ecpg</application> library is thread-safe by + default. However, you might need to use some threading + command-line options to compile your client code. + </para> + </sect1> + + <sect1 id="ecpg-library"> + <title>Library Functions</title> + + <para> + The <filename>libecpg</filename> library primarily contains + <quote>hidden</quote> 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. + </para> + + <itemizedlist> + <listitem> + <para> + <function>ECPGdebug(int <replaceable>on</replaceable>, FILE + *<replaceable>stream</replaceable>)</function> turns on debug + logging if called with the first argument non-zero. Debug logging + is done on <replaceable>stream</replaceable>. The log contains + all <acronym>SQL</acronym> statements with all the input + variables inserted, and the results from the + <productname>PostgreSQL</productname> server. This can be very + useful when searching for errors in your <acronym>SQL</acronym> + statements. + </para> + <note> + <para> + On Windows, if the <application>ecpg</> libraries and an application are + compiled with different flags, this function call will crash the + application because the internal representation of the + <literal>FILE</> pointers differ. Specifically, + multithreaded/single-threaded, release/debug, and static/dynamic + flags should be the same for the library and all applications using + that library. + </para> + </note> + </listitem> + + <listitem> + <para> + <function>ECPGget_PGconn(const char *<replaceable>connection_name</replaceable>) + </function> returns the library database connection handle identified by the given name. + If <replaceable>connection_name</replaceable> is set to <literal>NULL</literal>, the current + connection handle is returned. If no connection handle can be identified, the function returns + <literal>NULL</literal>. The returned connection handle can be used to call any other functions + from <application>libpq</application>, if necessary. + </para> + <note> + <para> + It is a bad idea to manipulate database connection handles made from <application>ecpg</application> directly + with <application>libpq</application> routines. + </para> + </note> + </listitem> + + <listitem> + <para> + <function>ECPGtransactionStatus(const char *<replaceable>connection_name</replaceable>)</function> + returns the current transaction status of the given connection identified by <replaceable>connection_name</replaceable>. + See <xref linkend="libpq-status"> and libpq's <function>PQtransactionStatus()</function> for details about the returned status codes. + </para> + </listitem> + + <listitem> + <para> + <function>ECPGstatus(int <replaceable>lineno</replaceable>, + const char* <replaceable>connection_name</replaceable>)</function> + returns true if you are connected to a database and false if not. + <replaceable>connection_name</replaceable> can be <literal>NULL</> + if a single connection is being used. + </para> + </listitem> + </itemizedlist> + </sect1> + + <sect1 id="ecpg-lo"> + <title>Large Objects</title> + + <para> + Large objects are not directly supported by ECPG, but ECPG + application can manipulate large objects through the libpq large + object functions, obtaining the necessary <type>PGconn</type> + object by calling the <function>ECPGget_PGconn()</function> + function. (However, use of + the <function>ECPGget_PGconn()</function> function and touching + <type>PGconn</type> objects directly should be done very carefully + and ideally not mixed with other ECPG database access calls.) + </para> + + <para> + For more details about the <function>ECPGget_PGconn()</function>, see + <xref linkend="ecpg-library">. For information about the large + object function interface, see <xref linkend="largeObjects">. + </para> + + <para> + Large object functions have to be called in a transaction block, so + when autocommit is off, <command>BEGIN</command> commands have to + be isssued explicitly. + </para> + + <para> + <xref linkend="ecpg-lo-example"> shows an example program that + illustrates how to create, write, and read a large object in an + ECPG application. + </para> + + <example id="ecpg-lo-example"> + <title>ECPG program accessing large objects</title> +<programlisting><![CDATA[ +#include <stdio.h> +#include <stdlib.h> +#include <libpq-fe.h> +#include <libpq/libpq-fs.h> + +EXEC SQL WHENEVER SQLERROR STOP; + +int +main(void) +{ + PGconn *conn; + Oid loid; + int fd; + char buf[256]; + int buflen = 256; + char buf2[256]; + int rc; + + memset(buf, 1, buflen); + + EXEC SQL CONNECT TO testdb AS con1; + + conn = ECPGget_PGconn("con1"); + printf("conn = %p\n", conn); + + /* create */ + loid = lo_create(conn, 0); + if (loid < 0) + printf("lo_create() failed: %s", PQerrorMessage(conn)); + + printf("loid = %d\n", loid); + + /* write test */ + fd = lo_open(conn, loid, INV_READ|INV_WRITE); + if (fd < 0) + printf("lo_open() failed: %s", PQerrorMessage(conn)); + + printf("fd = %d\n", fd); + + rc = lo_write(conn, fd, buf, buflen); + if (rc < 0) + printf("lo_write() failed\n"); + + rc = lo_close(conn, fd); + if (rc < 0) + printf("lo_close() failed: %s", PQerrorMessage(conn)); + + /* read test */ + fd = lo_open(conn, loid, INV_READ); + if (fd < 0) + printf("lo_open() failed: %s", PQerrorMessage(conn)); + + printf("fd = %d\n", fd); + + rc = lo_read(conn, fd, buf2, buflen); + if (rc < 0) + printf("lo_read() failed\n"); + + rc = lo_close(conn, fd); + if (rc < 0) + printf("lo_close() failed: %s", PQerrorMessage(conn)); + + /* check */ + rc = memcmp(buf, buf2, buflen); + printf("memcmp() = %d\n", rc); + + /* cleanup */ + rc = lo_unlink(conn, loid); + if (rc < 0) + printf("lo_unlink() failed: %s", PQerrorMessage(conn)); + + EXEC SQL COMMIT; + EXEC SQL DISCONNECT ALL; + return 0; +} +]]></programlisting> + </example> + </sect1> + + <sect1 id="ecpg-cpp"> + <title><acronym>C++</acronym> Applications</title> + + <para> + ECPG has some limited support for C++ applications. This section + describes some caveats. + </para> + + <para> + The <command>ecpg</command> preprocessor takes an input file + written in C (or something like C) and embedded SQL commands, + converts the embedded SQL commands into C language chunks, and + finally generates a <filename>.c</filename> file. The header file + declarations of the library functions used by the C language chunks + that <command>ecpg</command> generates are wrapped + in <literal>extern "C" { ... }</literal> blocks when used under + C++, so they should work seamlessly in C++. + </para> + + <para> + In general, however, the <command>ecpg</command> preprocessor only + understands C; it does not handle the special syntax and reserved + words of the C++ language. So, some embedded SQL code written in + C++ application code that uses complicated features specific to C++ + might fail to be preprocessed correctly or might not work as + expected. + </para> + + <para> + A safe way to use the embedded SQL code in a C++ application is + hiding the ECPG calls in a C module, which the C++ application code + calls into to access the database, and linking that together with + the rest of the C++ code. See <xref linkend="ecpg-cpp-and-c"> + about that. + </para> + + <sect2 id="ecpg-cpp-scope"> + <title>Scope for host variables</title> + + <para> + The <command>ecpg</command> preprocessor understands the scope of + variables in C. In the C language, this is rather simple because + the scopes of variables is based on their code blocks. In C++, + however, the class member variables are referenced in a different + code block from the declared position, so + the <command>ecpg</command> preprocessor will not understand the + scope of the class member variables. + </para> + + <para> + For example, in the following case, the <command>ecpg</command> + preprocessor cannot find any declaration for the + variable <literal>dbname</literal> in the <literal>test</literal> + method, so an error will occur. + +<programlisting> +class TestCpp +{ + EXEC SQL BEGIN DECLARE SECTION; + char dbname[1024]; + EXEC SQL END DECLARE SECTION; + + public: + TestCpp(); + void test(); + ~TestCpp(); +}; + +TestCpp::TestCpp() +{ + EXEC SQL CONNECT TO testdb1; +} + +void Test::test() +{ + EXEC SQL SELECT current_database() INTO :dbname; + printf("current_database = %s\n", dbname); +} + +TestCpp::~TestCpp() +{ + EXEC SQL DISCONNECT ALL; +} +</programlisting> + + This code will result in an error like this: +<screen> +<userinput>ecpg test_cpp.pgc</userinput> +test_cpp.pgc:28: ERROR: variable "dbname" is not declared +</screen> + </para> + + <para> + To avoid this scope issue, the <literal>test</literal> method + could be modified to use a local variable as intermediate storage. + But this approach is only a poor workaround, because it uglifies + the code and reduces performance. + +<programlisting> +void TestCpp::test() +{ + EXEC SQL BEGIN DECLARE SECTION; + char tmp[1024]; + EXEC SQL END DECLARE SECTION; + + EXEC SQL SELECT current_database() INTO :tmp; + strlcpy(dbname, tmp, sizeof(tmp)); + + printf("current_database = %s\n", dbname); +} +</programlisting> + </para> + </sect2> + + <sect2 id="ecpg-cpp-and-c"> + <title>C++ application development with external C module</title> + + <para> + If you understand these technical limitations of + the <command>ecpg</command> preprocessor in C++, you might come to + the conclusion that linking C objects and C++ objects at the link + stage to enable C++ applications to use ECPG features could be + better than writing some embedded SQL commands in C++ code + directly. This section describes a way to separate some embedded + SQL commands from C++ application code with a simple example. In + this example, the application is implemented in C++, while C and + ECPG is used to connect to the PostgreSQL server. + </para> + + <para> + Three kinds of files have to be created: a C file + (<filename>*.pgc</filename>), a header file, and a C++ file: + + <variablelist> + <varlistentry> + <term><filename>test_mod.pgc</filename></term> + <listitem> + <para> + A sub-routine module to execute SQL commands embedded in C. + It is going to be converted + into <filename>test_mod.c</filename> by the preprocessor. + +<programlisting> +#include "test_mod.h" +#include <stdio.h> + +void +db_connect() +{ + EXEC SQL CONNECT TO testdb1; +} + +void +db_test() +{ + EXEC SQL BEGIN DECLARE SECTION; + char dbname[1024]; + EXEC SQL END DECLARE SECTION; + + EXEC SQL SELECT current_database() INTO :dbname; + printf("current_database = %s\n", dbname); +} + +void +db_disconnect() +{ + EXEC SQL DISCONNECT ALL; +} +</programlisting> + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><filename>test_mod.h</filename></term> + <listitem> + <para> + A header file with declarations of the functions in the C + module (<filename>test_mod.pgc</filename>). It is included by + <filename>test_cpp.cpp</filename>. This file has to have an + <literal>extern "C"</literal> block around the declarations, + because it will be linked from the C++ module. + +<programlisting> +#ifdef __cplusplus +extern "C" { +#endif + +void db_connect(); +void db_test(); +void db_disconnect(); + +#ifdef __cplusplus +} +#endif +</programlisting> + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><filename>test_cpp.cpp</filename></term> + <listitem> + <para> + The main code for the application, including + the <function>main</function> routine, and in this example a + C++ class. + +<programlisting> +#include "test_mod.h" + +class TestCpp +{ + public: + TestCpp(); + void test(); + ~TestCpp(); +}; + +TestCpp::TestCpp() +{ + db_connect(); +} + +void +TestCpp::test() +{ + db_test(); +} + +TestCpp::~TestCpp() +{ + db_disconnect(); +} + +int +main(void) +{ + TestCpp *t = new TestCpp(); + + t->test(); + return 0; +} +</programlisting> + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + + <para> + To build the application, proceed as follows. Convert + <filename>test_mod.pgc</> into <filename>test_mod.c</> by + running <command>ecpg</command>, and generate + <filename>test_mod.o</> by compiling + <filename>test_mod.c</> with the C compiler: +<programlisting> +ecpg -o test_mod.c test_mod.pgc +cc -c test_mod.c -o test_mod.o +</programlisting> + </para> + + <para> + Next, generate <filename>test_cpp.o</> by compiling + <filename>test_cpp.cpp</> with the C++ compiler:. +<programlisting> +c++ -c test_cpp.cpp -o test_cpp.o +</programlisting> + </para> + + <para> + Finally, link these object files, <filename>test_cpp.o</> + and <filename>test_mod.o</>, into one executable, using the C++ + compiler driver: +<programlisting> +c++ test_cpp.o test_mod.o -lecpg -o test_cpp +</programlisting> + </para> + </sect2> + </sect1> + + <sect1 id="ecpg-sql-commands"> + <title>Embedded SQL Commands</title> + + <para> + This section describes all SQL commands that are specific to + embedded SQL. Also refer to the SQL commands listed + in <xref linkend="sql-commands">, which can also be used in + embedded SQL, unless stated otherwise. + </para> + + <refentry id="ecpg-sql-allocate-descriptor"> + <refnamediv> + <refname>ALLOCATE DESCRIPTOR</refname> + <refpurpose>allocate an SQL descriptor area</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +ALLOCATE DESCRIPTOR <replaceable class="PARAMETER">name</replaceable> +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>ALLOCATE DESCRIPTOR</command> allocates a new named SQL + descriptor area, which can be used to exchange data between the + PostgreSQL server and the host program. + </para> + + <para> + Descriptor areas should be freed after use using + the <command>DEALLOCATE DESCRIPTOR</command> command. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="PARAMETER">name</replaceable></term> + <listitem> + <para> + A name of SQL descriptor, case sensitive. This can be an SQL + identifier or a host variable. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Examples</title> + +<programlisting> +EXEC SQL ALLOCATE DESCRIPTOR mydesc; +</programlisting> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>ALLOCATE DESCRIPTOR</command> is specified in the SQL + standard. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="ecpg-sql-deallocate-descriptor"></member> + <member><xref linkend="ecpg-sql-get-descriptor"></member> + <member><xref linkend="ecpg-sql-set-descriptor"></member> + </simplelist> + </refsect1> + </refentry> + + <refentry id="ecpg-sql-connect"> + <refnamediv> + <refname>CONNECT</refname> + <refpurpose>establish a database connection</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +CONNECT TO <replaceable>connection_target</replaceable> [ AS <replaceable>connection_name</replaceable> ] [ USER <replaceable>connection_user_name</replaceable> ] +CONNECT TO DEFAULT +CONNECT <replaceable>connection_user_name</replaceable> +DATABASE <replaceable>connection_target</replaceable> +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + The <command>CONNECT</command> command establishes a connection + between the client and the PostgreSQL server. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="PARAMETER">connection_target</replaceable></term> + <listitem> + <para> + <replaceable class="PARAMETER">connection_target</replaceable> + specifies the target server of the connection on one of + several forms. + + <variablelist> + <varlistentry> + <term>[ <replaceable>database_name</replaceable> ] [ <literal>@</literal><replaceable>host</replaceable> ] [ <literal>:</literal><replaceable>port</replaceable> ]</term> + <listitem> + <para> + Connect over TCP/IP + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>unix:postgresql://</literal><replaceable>host</replaceable> [ <literal>:</literal><replaceable>port</replaceable> ] <literal>/</literal> [ <replaceable>database_name</replaceable> ] [ <literal>?</literal><replaceable>connection_option</replaceable> ]</term> + <listitem> + <para> + Connect over Unix-domain sockets + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>tcp:postgresql://</literal><replaceable>host</replaceable> [ <literal>:</literal><replaceable>port</replaceable> ] <literal>/</literal> [ <replaceable>database_name</replaceable> ] [ <literal>?</literal><replaceable>connection_option</replaceable> ]</term> + <listitem> + <para> + Connect over TCP/IP + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>SQL string constant</term> + <listitem> + <para> + containing a value in one of the above forms </para> </listitem> </varlistentry> + <varlistentry> + <term>host variable</term> + <listitem> + <para> + host variable of type <type>char[]</type> + or <type>VARCHAR[]</type> containing a value in one of the + above forms + </para> + </listitem> + </varlistentry> </variablelist> </para> </listitem> </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">connection_object</replaceable></term> + <listitem> + <para> + An optional identifier for the connection, so that it can be + referred to in other commands. This can be an SQL identifier + or a host variable. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">connection_user</replaceable></term> + <listitem> + <para> + The user name for the database connection. + </para> + + <para> + This parameter can also specify user name and password, using one the forms + <literal><replaceable>user_name</replaceable>/<replaceable>password</replaceable></literal>, + <literal><replaceable>user_name</replaceable> IDENTIFIED BY <replaceable>password</replaceable></literal>, or + <literal><replaceable>user_name</replaceable> USING <replaceable>password</replaceable></literal>. + </para> + + <para> + User name and password can be SQL identifiers, string + constants, or host variables. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>DEFAULT</literal></term> + <listitem> + <para> + Use all default connection parameters, as defined by libpq. + </para> + </listitem> + </varlistentry> </variablelist> - </para> + </refsect1> - </sect2> + <refsect1> + <title>Examples</title> + + <para> + Here a several variants for specifying connection parameters: +<programlisting> +EXEC SQL CONNECT TO "connectdb" AS main; +EXEC SQL CONNECT TO "connectdb" AS second; +EXEC SQL CONNECT TO "unix:postgresql://200.46.204.71/connectdb" AS main USER connectuser; +EXEC SQL CONNECT TO "unix:postgresql://localhost/connectdb" AS main USER connectuser; +EXEC SQL CONNECT TO 'connectdb' AS main; +EXEC SQL CONNECT TO 'unix:postgresql://localhost/connectdb' AS main USER :user; +EXEC SQL CONNECT TO :db AS :id; +EXEC SQL CONNECT TO :db USER connectuser USING :pw; +EXEC SQL CONNECT TO @localhost AS main USER connectdb; +EXEC SQL CONNECT TO REGRESSDB1 as main; +EXEC SQL CONNECT TO AS main USER connectdb; +EXEC SQL CONNECT TO connectdb AS :id; +EXEC SQL CONNECT TO connectdb AS main USER connectuser/connectdb; +EXEC SQL CONNECT TO connectdb AS main; +EXEC SQL CONNECT TO connectdb@localhost AS main; +EXEC SQL CONNECT TO tcp:postgresql://localhost/ USER connectdb; +EXEC SQL CONNECT TO tcp:postgresql://localhost/connectdb USER connectuser IDENTIFIED BY connectpw; +EXEC SQL CONNECT TO tcp:postgresql://localhost:20/connectdb USER connectuser IDENTIFIED BY connectpw; +EXEC SQL CONNECT TO unix:postgresql://localhost/ AS main USER connectdb; +EXEC SQL CONNECT TO unix:postgresql://localhost/connectdb AS main USER connectuser; +EXEC SQL CONNECT TO unix:postgresql://localhost/connectdb USER connectuser IDENTIFIED BY "connectpw"; +EXEC SQL CONNECT TO unix:postgresql://localhost/connectdb USER connectuser USING "connectpw"; +EXEC SQL CONNECT TO unix:postgresql://localhost/connectdb?connect_timeout=14 USER connectuser; +</programlisting> + </para> + + <para> + Here is an example program that illustrates the use of host + variables to specify connection parameters: +<programlisting> +int +main(void) +{ +EXEC SQL BEGIN DECLARE SECTION; + char *dbname = "testdb"; /* database name */ + char *user = "testuser"; /* connection user name */ + char *connection = "tcp:postgresql://localhost:5432/testdb"; + /* connection string */ + char ver[256]; /* buffer to store the version string */ +EXEC SQL END DECLARE SECTION; + + ECPGdebug(1, stderr); + + EXEC SQL CONNECT TO :dbname USER :user; + EXEC SQL SELECT version() INTO :ver; + EXEC SQL DISCONNECT; + + printf("version: %s\n", ver); + + EXEC SQL CONNECT TO :connection USER :user; + EXEC SQL SELECT version() INTO :ver; + EXEC SQL DISCONNECT; + + printf("version: %s\n", ver); + + return 0; +} +</programlisting> + </para> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>CONNECT</command> is specified in the SQL standard, but + the format of the connection parameters is + implementation-specific. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="ecpg-sql-disconnect"></member> + <member><xref linkend="ecpg-sql-set-connection"></member> + </simplelist> + </refsect1> + </refentry> + + <refentry id="ecpg-sql-deallocate-descriptor"> + <refnamediv> + <refname>DEALLOCATE DESCRIPTOR</refname> + <refpurpose>deallocate an SQL descriptor area</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +DEALLOCATE DESCRIPTOR <replaceable class="PARAMETER">name</replaceable> +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>DEALLOCATE DESCRIPTOR</command> deallocates a named SQL + descriptor area. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="PARAMETER">name</replaceable></term> + <listitem> + <para> + The name of the descriptor which is going to be deallocated. + It is case sensitive. This can be an SQL identifier or a host + variable. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Examples</title> + +<programlisting> +EXEC SQL DEALLOCATE DESCRIPTOR mydesc; +</programlisting> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>DEALLOCATE DESCRIPTOR</command> is specified in the SQL + standard. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="ecpg-sql-allocate-descriptor"></member> + <member><xref linkend="ecpg-sql-get-descriptor"></member> + <member><xref linkend="ecpg-sql-set-descriptor"></member> + </simplelist> + </refsect1> + </refentry> + + <refentry id="ecpg-sql-declare"> + <refnamediv> + <refname>DECLARE</refname> + <refpurpose>define a cursor</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +DECLARE <replaceable class="PARAMETER">cursor_name</replaceable> [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="PARAMETER">prepared_name</replaceable> +DECLARE <replaceable class="PARAMETER">cursor_name</replaceable> [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="PARAMETER">query</replaceable> +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>DECLARE</command> declares a cursor for iterating over + the result set of a prepared statement. This command has + slightly different semantics from the direct SQL + command <command>DECLARE</command>: Whereas the latter executes a + query and prepares the result set for retrieval, this embedded + SQL command merely declares a name as a <quote>loop + variable</quote> for iterating over the result set of a query; + the actual execution happens when the cursor is opened with + the <command>OPEN</command> command. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + <variablelist> + + <varlistentry> + <term><replaceable class="PARAMETER">cursor_name</replaceable></term> + <listitem> + <para> + A cursor name, case sensitive. This can be an SQL identifier + or a host variable. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">prepared_name</replaceable></term> + <listitem> + <para> + The name of a prepared query, either as an SQL identfier or a + host variable. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">query</replaceable></term> + <listitem> + <para> + A <xref linkend="sql-select"> or + <xref linkend="sql-values"> command which will provide the + rows to be returned by the cursor. + </para> + </listitem> + </varlistentry> + </variablelist> + + <para> + For the meaning of the cursor options, + see <xref linkend="sql-declare">. + </para> + </refsect1> + + <refsect1> + <title>Examples</title> + + <para> + Examples declaring a cursor for a query: +<programlisting> +EXEC SQL DECLARE C CURSOR FOR SELECT * FROM My_Table; +EXEC SQL DECLARE C CURSOR FOR SELECT Item1 FROM T; +EXEC SQL DECLARE cur1 CURSOR FOR SELECT version(); +</programlisting> + </para> + + <para> + An example declaring a cursor for a prepared statement: +<programlisting> +EXEC SQL PREPARE stmt1 AS SELECT version(); +EXEC SQL DECLARE cur1 CURSOR FOR stmt1; +</programlisting> + </para> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>DECLARE</command> is specified in the SQL standard. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="ecpg-sql-open"></member> + <member><xref linkend="sql-close"></member> + <member><xref linkend="sql-declare"></member> + </simplelist> + </refsect1> + </refentry> + + <refentry id="ecpg-sql-describe"> + <refnamediv> + <refname>DESCRIBE</refname> + <refpurpose>obtain information about a prepared statement or result set</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +DESCRIBE [ OUTPUT ] <replaceable class="PARAMETER">prepared_name</replaceable> USING [ SQL ] DESCRIPTOR <replaceable class="PARAMETER">descriptor_name</replaceable> +DESCRIBE [ OUTPUT ] <replaceable class="PARAMETER">prepared_name</replaceable> INTO [ SQL ] DESCRIPTOR <replaceable class="PARAMETER">descriptor_name</replaceable> +DESCRIBE [ OUTPUT ] <replaceable class="PARAMETER">prepared_name</replaceable> INTO <replaceable class="PARAMETER">sqlda_name</replaceable> +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>DECLARE</command> retrieves metadata information about + the result columns contained in a prepared statement, without + actually fetching a row. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="PARAMETER">prepared_name</replaceable></term> + <listitem> + <para> + The name of a prepared statement. This can be an SQL + identifier or a host variable. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">descriptor_name</replaceable></term> + <listitem> + <para> + A descriptor name. It is case sensitive. It can be an SQL + identifier or a host variable. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">sqlda_name</replaceable></term> + <listitem> + <para> + The name of an SQLDA variable. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Examples</title> + +<programlisting> +EXEC SQL ALLOCATE DESCRIPTOR mydesc; +EXEC SQL PREPARE stmt1 FROM :sql_stmt; +EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc; +EXEC SQL GET DESCRIPTOR mydesc VALUE 1 :charvar = NAME; +EXEC SQL DEALLOCATE DESCRIPTOR mydesc; +</programlisting> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>DESCRIBE</command> is specified in the SQL standard. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="ecpg-sql-allocate-descriptor"></member> + <member><xref linkend="ecpg-sql-get-descriptor"></member> + </simplelist> + </refsect1> + </refentry> + + <refentry id="ecpg-sql-disconnect"> + <refnamediv> + <refname>DISCONNECT</refname> + <refpurpose>terminate a database connection</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +DISCONNECT <replaceable class="PARAMETER">connection_name</replaceable> +DISCONNECT [ CURRENT ] +DISCONNECT DEFAULT +DISCONNECT ALL +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>DISCONNECT</command> closes a connection (or all + connections) to the database. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="PARAMETER">connection_name</replaceable></term> + <listitem> + <para> + A database connection name established by + the <command>CONNECT</command> command. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>CURRENT</literal></term> + <listitem> + <para> + Close the <quote>current</quote> connection, which is either + the most recently opened connection, or the connection set by + the <command>SET CONNECTION</command> command. This is also + the default if no argument is given to + the <command>DISCONNECT</command> command. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>DEFAULT</literal></term> + <listitem> + <para> + Close the default connection. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ALL</literal></term> + <listitem> + <para> + Close all open connections. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Examples</title> + +<programlisting> +int +main(void) +{ + EXEC SQL CONNECT TO testdb AS DEFAULT USER testuser; + EXEC SQL CONNECT TO testdb AS con1 USER testuser; + EXEC SQL CONNECT TO testdb AS con2 USER testuser; + EXEC SQL CONNECT TO testdb AS con3 USER testuser; + + EXEC SQL DISCONNECT CURRENT; /* close con3 */ + EXEC SQL DISCONNECT DEFAULT; /* close DEFAULT */ + EXEC SQL DISCONNECT ALL; /* close con2 and con1 */ + + return 0; +} +</programlisting> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>DISCONNECT</command> is specified in the SQL standard. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="ecpg-sql-connect"></member> + <member><xref linkend="ecpg-sql-set-connection"></member> + </simplelist> + </refsect1> + </refentry> + + <refentry id="ecpg-sql-execute-immediate"> + <refnamediv> + <refname>EXECUTE IMMEDIATE</refname> + <refpurpose>dynamically prepare and execute a statement</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +EXECUTE IMMEDIATE <replaceable class="PARAMETER">string</replaceable> +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>EXECUTE IMMEDIATE</command> immediately prepares and + executes a dynamically specified SQL statement, without + retrieving result rows. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="PARAMETER">string</replaceable></term> + <listitem> + <para> + A literal C string or a host variable containing the SQL + statement to be executed. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Examples</title> + + <para> + Here is an example that executes an <command>INSERT</command> + statement using <command>EXECUTE IMMEDIATE</command> and a host + variable named <varname>command</varname>: +<programlisting> +sprintf(command, "INSERT INTO test (name, amount, letter) VALUES ('db: ''r1''', 1, 'f')"); +EXEC SQL EXECUTE IMMEDIATE :command; +</programlisting> + </para> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>EXECUTE IMMEDIATE</command> is specified in the SQL standard. + </para> + </refsect1> + </refentry> + + <refentry id="ecpg-sql-get-descriptor"> + <refnamediv> + <refname>GET DESCRIPTOR</refname> + <refpurpose>get information from an SQL descriptor area</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +GET DESCRIPTOR <replaceable class="PARAMETER">descriptor_name</replaceable> <replaceable class="PARAMETER">:cvariable</replaceable> = <replaceable class="PARAMETER">descriptor_header_item</replaceable> [, ... ] +GET DESCRIPTOR <replaceable class="PARAMETER">descriptor_name</replaceable> VALUE <replaceable class="PARAMETER">column_number</replaceable> <replaceable class="PARAMETER">:cvariable</replaceable> = <replaceable class="PARAMETER">descriptor_item</replaceable> [, ... ] +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>GET DESCRIPTOR</command> retrieves information about a + query result set from an SQL descriptor area and stores it into + host variables. A descriptor area is typically populated + using <command>FETCH</command> or <command>SELECT</command> + before using this command to transfer the information into host + language variables. + </para> + + <para> + This command has two forms: The first form retrieves + descriptor <quote>header</quote> items, which apply to the result + set in its entirety. One example is the row count. The second + form, which requires the column number as additional parameter, + retrieves information about a particular column. Examples are + the column name and the actual column value. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="PARAMETER">descriptor_name</replaceable></term> + <listitem> + <para> + A descriptor name. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">descriptor_header_item</replaceable></term> + <listitem> + <para> + A token identifying which header information item to retrieve. + Only <literal>COUNT</literal>, to get the number of columns in the + result set, is currently supported. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">column_number</replaceable></term> + <listitem> + <para> + The number of the column about which information is to be + retrieved. The count starts at 1. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">descriptor_item</replaceable></term> + <listitem> + <para> + A token identifying which item of information about a column + to retrieve. See <xref linkend="ecpg-named-descriptors"> for + a list of supported items. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">cvariable</replaceable></term> + <listitem> + <para> + A host variable that will receive the data retrieved from the + descriptor area. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Examples</title> + + <para> + An example to retrieve the number of columns in a result set: +<programlisting> +EXEC SQL GET DESCRIPTOR d :d_count = COUNT; +</programlisting> + </para> + + <para> + An example to retrieve a data length in the first column: +<programlisting> +EXEC SQL GET DESCRIPTOR d VALUE 1 :d_returned_octet_length = RETURNED_OCTET_LENGTH; +</programlisting> + </para> + + <para> + An example to retrieve the data body of the second column as a + string: +<programlisting> +EXEC SQL GET DESCRIPTOR d VALUE 2 :d_data = DATA; +</programlisting> + </para> + + <para> + Here is an example for a whole procedure of + executing <literal>SELECT current_database();</> and showing the number of + columns, the column data length, and the column data: +<programlisting> +int +main(void) +{ +EXEC SQL BEGIN DECLARE SECTION; + int d_count; + char d_data[1024]; + int d_returned_octet_length; +EXEC SQL END DECLARE SECTION; + + EXEC SQL CONNECT TO testdb AS con1 USER testuser; + EXEC SQL ALLOCATE DESCRIPTOR d; + + /* Declare, open a cursor, and assign a descriptor to the cursor */ + EXEC SQL DECLARE cur CURSOR FOR SELECT current_database(); + EXEC SQL OPEN cur; + EXEC SQL FETCH NEXT FROM cur INTO SQL DESCRIPTOR d; + + /* Get a number of total columns */ + EXEC SQL GET DESCRIPTOR d :d_count = COUNT; + printf("d_count = %d\n", d_count); + + /* Get length of a returned column */ + EXEC SQL GET DESCRIPTOR d VALUE 1 :d_returned_octet_length = RETURNED_OCTET_LENGTH; + printf("d_returned_octet_length = %d\n", d_returned_octet_length); + + /* Fetch the returned column as a string */ + EXEC SQL GET DESCRIPTOR d VALUE 1 :d_data = DATA; + printf("d_data = %s\n", d_data); + + /* Closing */ + EXEC SQL CLOSE cur; + EXEC SQL COMMIT; + + EXEC SQL DEALLOCATE DESCRIPTOR d; + EXEC SQL DISCONNECT ALL; + + return 0; +} +</programlisting> + When the example is executed, the result will look like this: +<screen> +d_count = 1 +d_returned_octet_length = 6 +d_data = testdb +</screen> + </para> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>GET DESCRIPTOR</command> is specified in the SQL standard. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="ecpg-sql-allocate-descriptor"></member> + <member><xref linkend="ecpg-sql-set-descriptor"></member> + </simplelist> + </refsect1> + </refentry> + + <refentry id="ecpg-sql-open"> + <refnamediv> + <refname>OPEN</refname> + <refpurpose>open a dynamic cursor</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +OPEN <replaceable class="PARAMETER">cursor_name</replaceable> +OPEN <replaceable class="PARAMETER">cursor_name</replaceable> USING <replaceable class="PARAMETER">value</replaceable> [, ... ] +OPEN <replaceable class="PARAMETER">cursor_name</replaceable> USING SQL DESCRIPTOR <replaceable class="PARAMETER">descriptor_name</replaceable> +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>OPEN</command> opens a cursor and optionally binds + actual values to the placeholders in the cursor's declaration. + The cursor must previously have been declared with + the <command>DECLARE</command> command. The execution + of <command>OPEN</command> causes the query to start executing on + the server. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="PARAMETER">cursor_name</replaceable></term> + <listitem> + <para> + The name of the cursor to be opened. This can be an SQL + identifier or a host variable. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">value</replaceable></term> + <listitem> + <para> + A value to be bound to a placeholder in the cursor. This can + be an SQL constant, a host variable, or a host variable with + indicator. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">descriptor_name</replaceable></term> + <listitem> + <para> + The name of a descriptor containing values to be bound to the + placeholders in the cursor. This can be an SQL identifier or + a host variable. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Examples</title> + +<programlisting> +EXEC SQL OPEN a; +EXEC SQL OPEN d USING 1, 'test'; +EXEC SQL OPEN c1 USING SQL DESCRIPTOR mydesc; +EXEC SQL OPEN :curname1; +</programlisting> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>OPEN</command> is specified in the SQL standard. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="ecpg-sql-declare"></member> + <member><xref linkend="sql-close"></member> + </simplelist> + </refsect1> + </refentry> + + <refentry id="ecpg-sql-prepare"> + <refnamediv> + <refname>PREPARE</refname> + <refpurpose>prepare a statement for execution</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +PREPARE <replaceable class="PARAMETER">name</replaceable> FROM <replaceable class="PARAMETER">string</replaceable> +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>PREPARE</command> prepares a statement dynamically + specified as a string for execution. This is different from the + direct SQL statement <xref linkend="sql-prepare">, which can also + be used in embedded programs. The <xref linkend="sql-execute"> + command is used to execute either kind of prepared statement. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="PARAMETER">prepared_name</replaceable></term> + <listitem> + <para> + An identifier for the prepared query. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">string</replaceable></term> + <listitem> + <para> + A literal C string or a host variable containing a preparable + statement, one of the SELECT, INSERT, UPDATE, or + DELETE. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Examples</title> +<programlisting> +char *stmt = "SELECT * FROM test1 WHERE a = ? AND b = ?"; + +EXEC SQL ALLOCATE DESCRIPTOR outdesc; +EXEC SQL PREPARE foo FROM :stmt; + +EXEC SQL EXECUTE foo USING SQL DESCRIPTOR indesc INTO SQL DESCRIPTOR outdesc; +</programlisting> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>PREPARE</command> is specified in the SQL standard. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="sql-execute"></member> + </simplelist> + </refsect1> + </refentry> + + <refentry id="ecpg-sql-set-autocommit"> + <refnamediv> + <refname>SET AUTOCOMMIT</refname> + <refpurpose>set the autocommit behavior of the current session</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +SET AUTOCOMMIT { = | TO } { ON | OFF } +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>SET AUTOCOMMIT</command> sets the autocommit behavior of + the current database session. By default, embedded SQL programs + are <emphasis>not</emphasis> in autocommit mode, + so <command>COMMIT</command> needs to be issued explicitly when + desired. This command can change the session to autocommit mode, + where each individual statement is committed implicitly. + </para> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>SET AUTOCOMMIT</command> is an extension of PostgreSQL ECPG. + </para> + </refsect1> + </refentry> + + <refentry id="ecpg-sql-set-connection"> + <refnamediv> + <refname>SET CONNECTION</refname> + <refpurpose>select a database connection</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +SET CONNECTION [ TO | = ] <replaceable class="PARAMETER">connection_name</replaceable> +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>SET CONNECTION</command> sets the <quote>current</quote> + database connection, which is the one that all commands use + unless overridden. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="PARAMETER">connection_name</replaceable></term> + <listitem> + <para> + A database connection name established by + the <command>CONNECT</command> command. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>DEFAULT</literal></term> + <listitem> + <para> + Set the connection to the default connection. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Examples</title> + +<programlisting> +EXEC SQL SET CONNECTION TO con2; +EXEC SQL SET CONNECTION = con1; +</programlisting> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>SET CONNECTION</command> is specified in the SQL standard. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="ecpg-sql-connect"></member> + <member><xref linkend="ecpg-sql-disconnect"></member> + </simplelist> + </refsect1> + </refentry> + + <refentry id="ecpg-sql-set-descriptor"> + <refnamediv> + <refname>SET DESCRIPTOR</refname> + <refpurpose>set information in an SQL descriptor area</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +SET DESCRIPTOR <replaceable class="PARAMETER">descriptor_name</replaceable> <replaceable class="PARAMETER">descriptor_header_item</replaceable> = <replaceable>value</replaceable> [, ... ] +SET DESCRIPTOR <replaceable class="PARAMETER">descriptor_name</replaceable> VALUE <replaceable class="PARAMETER">number</replaceable> <replaceable class="PARAMETER">descriptor_item</replaceable> = <replaceable>value</replaceable> [, ...] +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>SET DESCRIPTOR</command> populates an SQL descriptor + area with values. The descriptor area is then typically used to + bind parameters in a prepared query execution. + </para> + + <para> + This command has two forms: The first form applies to the + descriptor <quote>header</quote>, which is independent of a + particular datum. The second form assigns values to particular + datums, identified by number. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="PARAMETER">descriptor_name</replaceable></term> + <listitem> + <para> + A descriptor name. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">descriptor_header_item</replaceable></term> + <listitem> + <para> + A token identifying which header information item to set. + Only <literal>COUNT</literal>, to set the number of descriptor + items, is currently supported. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">number</replaceable></term> + <listitem> + <para> + The number of the descriptor item to set. The count starts at + 1. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">descriptor_item</replaceable></term> + <listitem> + <para> + A token identifiying which item of information to set in the + descriptor. See <xref linkend="ecpg-named-descriptors"> for a + list of supported items. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">value</replaceable></term> + <listitem> + <para> + A value to store into the descriptor item. This can be an SQL + constant or a host variable. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Examples</title> +<programlisting> +EXEC SQL SET DESCRIPTOR indesc COUNT = 1; +EXEC SQL SET DESCRIPTOR indesc VALUE 1 DATA = 2; +EXEC SQL SET DESCRIPTOR indesc VALUE 1 DATA = :val1; +EXEC SQL SET DESCRIPTOR indesc VALUE 2 INDICATOR = :val1, DATA = 'some string'; +EXEC SQL SET DESCRIPTOR indesc VALUE 2 INDICATOR = :val2null, DATA = :val2; +</programlisting> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>SET DESCRIPTOR</command> is specified in the SQL standard. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="ecpg-sql-allocate-descriptor"></member> + <member><xref linkend="ecpg-sql-get-descriptor"></member> + </simplelist> + </refsect1> + </refentry> + + <refentry id="ecpg-sql-type"> + <refnamediv> + <refname>TYPE</refname> + <refpurpose>define a new data type</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +TYPE <replaceable class="PARAMETER">type_name</replaceable> IS <replaceable class="PARAMETER">ctype</replaceable> +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + The <command>TYPE</command> command defines a new C type. It is + equivalent to putting a <literal>typedef</literal> into a declare + section. + </para> + + <para> + This command is only recognized when <command>ecpg</command> is + run with the <option>-c</option> option. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="PARAMETER">type_name</replaceable></term> + <listitem> + <para> + The name for the new type. It must be a valid C type name. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">ctype</replaceable></term> + <listitem> + <para> + A C type specification. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Examples</title> + +<programlisting> +EXEC SQL TYPE customer IS + struct + { + varchar name[50]; + int phone; + }; + +EXEC SQL TYPE cust_ind IS + struct ind + { + short name_ind; + short phone_ind; + }; + +EXEC SQL TYPE c IS char reference; +EXEC SQL TYPE ind IS union { int integer; short smallint; }; +EXEC SQL TYPE intarray IS int[AMOUNT]; +EXEC SQL TYPE str IS varchar[BUFFERSIZ]; +EXEC SQL TYPE string IS char[11]; +</programlisting> + + <para> + Here is an example program that uses <command>EXEC SQL + TYPE</command>: +<programlisting> +EXEC SQL WHENEVER SQLERROR SQLPRINT; + +EXEC SQL TYPE tt IS + struct + { + varchar v[256]; + int i; + }; + +EXEC SQL TYPE tt_ind IS + struct ind { + short v_ind; + short i_ind; + }; + +int +main(void) +{ +EXEC SQL BEGIN DECLARE SECTION; + tt t; + tt_ind t_ind; +EXEC SQL END DECLARE SECTION; + + EXEC SQL CONNECT TO testdb AS con1; + + EXEC SQL SELECT current_database(), 256 INTO :t:t_ind LIMIT 1; + + printf("t.v = %s\n", t.v.arr); + printf("t.i = %d\n", t.i); + + printf("t_ind.v_ind = %d\n", t_ind.v_ind); + printf("t_ind.i_ind = %d\n", t_ind.i_ind); + + EXEC SQL DISCONNECT con1; + + return 0; +} +</programlisting> + + The output from this program looks like this: +<screen> +t.v = testdb +t.i = 256 +t_ind.v_ind = 0 +t_ind.i_ind = 0 +</screen> + </para> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + The <command>TYPE</command> command is a PostgreSQL extension. + </para> + </refsect1> + </refentry> + + <refentry id="ecpg-sql-var"> + <refnamediv> + <refname>VAR</refname> + <refpurpose>define a variable</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +VAR <replaceable>varname</replaceable> IS <replaceable>ctype</replaceable> +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + The <command>VAR</command> command defines a host variable. It + is equivalent to an ordinary C variable definition inside a + declare section. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="PARAMETER">varname</replaceable></term> + <listitem> + <para> + A C variable name. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">ctype</replaceable></term> + <listitem> + <para> + A C type specification. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Examples</title> + +<programlisting> +EXEC SQL VAR vc IS VARCHAR[10]; +EXEC SQL VAR boolvar IS bool; +</programlisting> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + The <command>VAR</command> command is a PostgreSQL extension. + </para> + </refsect1> + </refentry> + + <refentry id="ecpg-sql-whenever"> + <refnamediv> + <refname>WHENEVER</refname> + <refpurpose>specify the action to be taken when an SQL statement causes a specific class condition to be raised</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +WHENEVER { NOT FOUND | SQLERROR | SQLWARNING } <replaceable class="PARAMETER">action</replaceable> +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + Define a behavior which is called on the special cases (Rows not + found, SQL warnings or errors) in the result of SQL execution. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <para> + See <xref linkend="ecpg-whenever"> for a description of the + parameters. + </para> + </refsect1> + + <refsect1> + <title>Examples</title> + +<programlisting> +EXEC SQL WHENEVER NOT FOUND CONTINUE; +EXEC SQL WHENEVER NOT FOUND DO BREAK; +EXEC SQL WHENEVER SQLWARNING SQLPRINT; +EXEC SQL WHENEVER SQLWARNING DO warn(); +EXEC SQL WHENEVER SQLERROR sqlprint; +EXEC SQL WHENEVER SQLERROR CALL print2(); +EXEC SQL WHENEVER SQLERROR DO handle_error("select"); +EXEC SQL WHENEVER SQLERROR DO sqlnotice(NULL, NONO); +EXEC SQL WHENEVER SQLERROR DO sqlprint(); +EXEC SQL WHENEVER SQLERROR GOTO error_label; +EXEC SQL WHENEVER SQLERROR STOP; +</programlisting> + <para> + A typical application is the use of <literal>WHENEVER NOT FOUND + BREAK</literal> to handle looping through result sets: +<programlisting> +int +main(void) +{ + EXEC SQL CONNECT TO testdb AS con1; + EXEC SQL ALLOCATE DESCRIPTOR d; + EXEC SQL DECLARE cur CURSOR FOR SELECT current_database(), 'hoge', 256; + EXEC SQL OPEN cur; + + /* when end of result set reached, break out of while loop */ + EXEC SQL WHENEVER NOT FOUND DO BREAK; + + while (1) + { + EXEC SQL FETCH NEXT FROM cur INTO SQL DESCRIPTOR d; + ... + } + + EXEC SQL CLOSE cur; + EXEC SQL COMMIT; + + EXEC SQL DEALLOCATE DESCRIPTOR d; + EXEC SQL DISCONNECT ALL; + + return 0; +} +</programlisting> + </para> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>WHENEVER</command> is specified in the SQL standard, but + most of the actions are PostgreSQL extensions. + </para> + </refsect1> + </refentry> </sect1> <sect1 id="ecpg-informix-compat"> - <title><productname>Informix</productname> compatibility mode</title> + <title><productname>Informix</productname> Compatibility Mode</title> <para> <command>ecpg</command> can be run in a so-called <firstterm>Informix compatibility mode</>. If this mode is active, it tries to behave as if it were the <productname>Informix</productname> @@ -2866,6 +7835,17 @@ $INSERT INTO test(i, j) VALUES (7, :j); $COMMIT; </programlisting> </para> + + <note> + <para> + There must not be any white space between the <literal>$</literal> + and a following preprocessor directive, that is, + <literal>include</literal>, <literal>define</literal>, <literal>ifdef</literal>, + etc. Otherwise, the preprocessor will parse the token as a host + variable. + </para> + </note> + <para> There are two compatibility modes: <literal>INFORMIX</>, <literal>INFORMIX_SE</> </para> @@ -2893,7 +7873,7 @@ $COMMIT; find support in ECPG for that either. </para> - <sect2> + <sect2 id="ecpg-informix-types"> <title>Additional types</title> <para> The Informix-special "string" pseudo-type for storing right-trimmed character string data is now @@ -2909,7 +7889,7 @@ EXEC SQL FETCH MYCUR INTO :userid; </para> </sect2> - <sect2> + <sect2 id="ecpg-informix-statements"> <title>Additional/missing embedded SQL statements</title> <para> <variablelist> @@ -2944,51 +7924,50 @@ EXEC SQL CLOSE DATABASE; <listitem> <para> <literal>FREE statement_name</> is a synonym for <literal>DEALLOCATE PREPARE statement_name</>. - </para> - </listitem> + </para> + </listitem> </varlistentry> </variablelist> </para> </sect2> - <sect2> - <title>Informix-compatible SQLDA Descriptor Areas</title> + <sect2 id="ecpg-informix-sqlda"> + <title>Informix-compatible SQLDA descriptor areas</title> <para> Informix-compatible mode supports a different structure than the one described in <xref linkend="ecpg-sqlda-descriptors">. See below: <programlisting> struct sqlvar_compat { - short sqltype; - int sqllen; - char *sqldata; - short *sqlind; - char *sqlname; - char *sqlformat; - short sqlitype; - short sqlilen; - char *sqlidata; - int sqlxid; - char *sqltypename; - short sqltypelen; - short sqlownerlen; - short sqlsourcetype; - char *sqlownername; - int sqlsourceid; - - char *sqlilongdata; - int sqlflags; - void *sqlreserved; + short sqltype; + int sqllen; + char *sqldata; + short *sqlind; + char *sqlname; + char *sqlformat; + short sqlitype; + short sqlilen; + char *sqlidata; + int sqlxid; + char *sqltypename; + short sqltypelen; + short sqlownerlen; + short sqlsourcetype; + char *sqlownername; + int sqlsourceid; + char *sqlilongdata; + int sqlflags; + void *sqlreserved; }; struct sqlda_compat { - short sqld; - struct sqlvar_compat *sqlvar; - char desc_name[19]; - short desc_occ; - struct sqlda_compat *desc_next; - void *reserved; + short sqld; + struct sqlvar_compat *sqlvar; + char desc_name[19]; + short desc_occ; + struct sqlda_compat *desc_next; + void *reserved; }; typedef struct sqlvar_compat sqlvar_t; @@ -2998,7 +7977,7 @@ typedef struct sqlda_compat sqlda_t; <para> The global properties are: - <variablelist> + <variablelist> <varlistentry> <term><literal>sqld</></term> @@ -3010,7 +7989,7 @@ typedef struct sqlda_compat sqlda_t; </varlistentry> <varlistentry> - <term><literal>sqlvar</></term> + <term><literal>sqlvar</></term> <listitem> <para> Pointer to the per-field properties. @@ -3229,7 +8208,7 @@ EXEC SQL INCLUDE sqlda.h; </para> </sect2> - <sect2> + <sect2 id="ecpg-informix-functions"> <title>Additional functions</title> <para> <variablelist> @@ -3245,7 +8224,7 @@ int decadd(decimal *arg1, decimal *arg2, decimal *sum); (<literal>arg1</>), a pointer to the second operand of type decimal (<literal>arg2</>) and a pointer to a value of type decimal that will contain the sum (<literal>sum</>). On success, the function returns 0. - <symbol>ECPG_INFORMIX_NUM_OVERFLOW</> is returned in case of overflow and + <symbol>ECPG_INFORMIX_NUM_OVERFLOW</> is returned in case of overflow and <symbol>ECPG_INFORMIX_NUM_UNDERFLOW</> in case of underflow. -1 is returned for other failures and <varname>errno</> is set to the respective <varname>errno</> number of the pgtypeslib. @@ -4287,7 +9266,7 @@ risnull(CINTTYPE, (char *) &i); </para> </sect2> - <sect2> + <sect2 id="ecpg-informix-constants"> <title>Additional constants</title> <para> Note that all constants here describe errors and all of them are defined @@ -4450,1102 +9429,6 @@ risnull(CINTTYPE, (char *) &i); </sect2> </sect1> - <sect1 id="ecpg-errors"> - <title>Error Handling</title> - - <para> - This section describes how you can handle exceptional conditions - and warnings in an embedded SQL program. There are several - nonexclusive facilities for this. - </para> - - <sect2> - <title>Setting Callbacks</title> - - <para> - One simple method to catch errors and warnings is to set a - specific action to be executed whenever a particular condition - occurs. In general: -<programlisting> -EXEC SQL WHENEVER <replaceable>condition</replaceable> <replaceable>action</replaceable>; -</programlisting> - </para> - - <para> - <replaceable>condition</replaceable> can be one of the following: - - <variablelist> - <varlistentry> - <term><literal>SQLERROR</literal></term> - <listitem> - <para> - The specified action is called whenever an error occurs during - the execution of an SQL statement. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>SQLWARNING</literal></term> - <listitem> - <para> - The specified action is called whenever a warning occurs - during the execution of an SQL statement. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>NOT FOUND</literal></term> - <listitem> - <para> - The specified action is called whenever an SQL statement - retrieves or affects zero rows. (This condition is not an - error, but you might be interested in handling it specially.) - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - - <para> - <replaceable>action</replaceable> can be one of the following: - - <variablelist> - <varlistentry> - <term><literal>CONTINUE</literal></term> - <listitem> - <para> - This effectively means that the condition is ignored. This is - the default. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>GOTO <replaceable>label</replaceable></literal></term> - <term><literal>GO TO <replaceable>label</replaceable></literal></term> - <listitem> - <para> - Jump to the specified label (using a C <literal>goto</literal> - statement). - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>SQLPRINT</literal></term> - <listitem> - <para> - Print a message to standard error. This is useful for simple - programs or during prototyping. The details of the message - cannot be configured. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>STOP</literal></term> - <listitem> - <para> - Call <literal>exit(1)</literal>, which will terminate the - program. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>DO BREAK</literal></term> - <listitem> - <para> - Execute the C statement <literal>break</literal>. This should - only be used in loops or <literal>switch</literal> statements. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>CALL <replaceable>name</replaceable> (<replaceable>args</replaceable>)</literal></term> - <term><literal>DO <replaceable>name</replaceable> (<replaceable>args</replaceable>)</literal></term> - <listitem> - <para> - Call the specified C functions with the specified arguments. - </para> - </listitem> - </varlistentry> - </variablelist> - - The SQL standard only provides for the actions - <literal>CONTINUE</literal> and <literal>GOTO</literal> (and - <literal>GO TO</literal>). - </para> - - <para> - Here is an example that you might want to use in a simple program. - It prints a simple message when a warning occurs and aborts the - program when an error happens: -<programlisting> -EXEC SQL WHENEVER SQLWARNING SQLPRINT; -EXEC SQL WHENEVER SQLERROR STOP; -</programlisting> - </para> - - <para> - The statement <literal>EXEC SQL WHENEVER</literal> is a directive - of the SQL preprocessor, not a C statement. The error or warning - actions that it sets apply to all embedded SQL statements that - appear below the point where the handler is set, unless a - different action was set for the same condition between the first - <literal>EXEC SQL WHENEVER</literal> and the SQL statement causing - the condition, regardless of the flow of control in the C program. - So neither of the two following C program excerpts will have the - desired effect: -<programlisting> -/* - * WRONG - */ -int main(int argc, char *argv[]) -{ - ... - if (verbose) { - EXEC SQL WHENEVER SQLWARNING SQLPRINT; - } - ... - EXEC SQL SELECT ...; - ... -} -</programlisting> - -<programlisting> -/* - * WRONG - */ -int main(int argc, char *argv[]) -{ - ... - set_error_handler(); - ... - EXEC SQL SELECT ...; - ... -} - -static void set_error_handler(void) -{ - EXEC SQL WHENEVER SQLERROR STOP; -} -</programlisting> - </para> - </sect2> - - <sect2> - <title>sqlca</title> - - <para> - For more powerful error handling, the embedded SQL interface - provides a global variable with the name <varname>sqlca</varname> - that has the following structure: -<programlisting> -struct -{ - char sqlcaid[8]; - long sqlabc; - long sqlcode; - struct - { - int sqlerrml; - char sqlerrmc[SQLERRMC_LEN]; - } sqlerrm; - char sqlerrp[8]; - long sqlerrd[6]; - char sqlwarn[8]; - char sqlstate[5]; -} sqlca; -</programlisting> - (In a multithreaded program, every thread automatically gets its - own copy of <varname>sqlca</varname>. This works similarly to the - handling of the standard C global variable - <varname>errno</varname>.) - </para> - - <para> - <varname>sqlca</varname> covers both warnings and errors. If - multiple warnings or errors occur during the execution of a - statement, then <varname>sqlca</varname> will only contain - information about the last one. - </para> - - <para> - If no error occurred in the last <acronym>SQL</acronym> statement, - <literal>sqlca.sqlcode</literal> will be 0 and - <literal>sqlca.sqlstate</literal> will be - <literal>"00000"</literal>. If a warning or error occurred, then - <literal>sqlca.sqlcode</literal> will be negative and - <literal>sqlca.sqlstate</literal> will be different from - <literal>"00000"</literal>. A positive - <literal>sqlca.sqlcode</literal> indicates a harmless condition, - such as that the last query returned zero rows. - <literal>sqlcode</literal> and <literal>sqlstate</literal> are two - different error code schemes; details appear below. - </para> - - <para> - If the last SQL statement was successful, then - <literal>sqlca.sqlerrd[1]</literal> contains the OID of the - processed row, if applicable, and - <literal>sqlca.sqlerrd[2]</literal> contains the number of - processed or returned rows, if applicable to the command. - </para> - - <para> - In case of an error or warning, - <literal>sqlca.sqlerrm.sqlerrmc</literal> will contain a string - that describes the error. The field - <literal>sqlca.sqlerrm.sqlerrml</literal> contains the length of - the error message that is stored in - <literal>sqlca.sqlerrm.sqlerrmc</literal> (the result of - <function>strlen()</function>, not really interesting for a C - programmer). Note that some messages are too long to fit in the - fixed-size <literal>sqlerrmc</literal> array; they will be truncated. - </para> - - <para> - In case of a warning, <literal>sqlca.sqlwarn[2]</literal> is set - to <literal>W</literal>. (In all other cases, it is set to - something different from <literal>W</literal>.) If - <literal>sqlca.sqlwarn[1]</literal> is set to - <literal>W</literal>, then a value was truncated when it was - stored in a host variable. <literal>sqlca.sqlwarn[0]</literal> is - set to <literal>W</literal> if any of the other elements are set - to indicate a warning. - </para> - - <para> - The fields <structfield>sqlcaid</structfield>, - <structfield>sqlcabc</structfield>, - <structfield>sqlerrp</structfield>, and the remaining elements of - <structfield>sqlerrd</structfield> and - <structfield>sqlwarn</structfield> currently contain no useful - information. - </para> - - <para> - The structure <varname>sqlca</varname> is not defined in the SQL - standard, but is implemented in several other SQL database - systems. The definitions are similar at the core, but if you want - to write portable applications, then you should investigate the - different implementations carefully. - </para> - </sect2> - - <sect2> - <title><literal>SQLSTATE</literal> vs <literal>SQLCODE</literal></title> - - <para> - The fields <literal>sqlca.sqlstate</literal> and - <literal>sqlca.sqlcode</literal> are two different schemes that - provide error codes. Both are derived from the SQL standard, but - <literal>SQLCODE</literal> has been marked deprecated in the SQL-92 - edition of the standard and has been dropped in later editions. - Therefore, new applications are strongly encouraged to use - <literal>SQLSTATE</literal>. - </para> - - <para> - <literal>SQLSTATE</literal> is a five-character array. The five - characters contain digits or upper-case letters that represent - codes of various error and warning conditions. - <literal>SQLSTATE</literal> has a hierarchical scheme: the first - two characters indicate the general class of the condition, the - last three characters indicate a subclass of the general - condition. A successful state is indicated by the code - <literal>00000</literal>. The <literal>SQLSTATE</literal> codes are for - the most part defined in the SQL standard. The - <productname>PostgreSQL</productname> server natively supports - <literal>SQLSTATE</literal> error codes; therefore a high degree - of consistency can be achieved by using this error code scheme - throughout all applications. For further information see - <xref linkend="errcodes-appendix">. - </para> - - <para> - <literal>SQLCODE</literal>, the deprecated error code scheme, is a - simple integer. A value of 0 indicates success, a positive value - indicates success with additional information, a negative value - indicates an error. The SQL standard only defines the positive - value +100, which indicates that the last command returned or - affected zero rows, and no specific negative values. Therefore, - this scheme can only achieve poor portability and does not have a - hierarchical code assignment. Historically, the embedded SQL - processor for <productname>PostgreSQL</productname> has assigned - some specific <literal>SQLCODE</literal> values for its use, which - are listed below with their numeric value and their symbolic name. - Remember that these are not portable to other SQL implementations. - To simplify the porting of applications to the - <literal>SQLSTATE</literal> scheme, the corresponding - <literal>SQLSTATE</literal> is also listed. There is, however, no - one-to-one or one-to-many mapping between the two schemes (indeed - it is many-to-many), so you should consult the global - <literal>SQLSTATE</literal> listing in <xref linkend="errcodes-appendix"> - in each case. - </para> - - <para> - These are the assigned <literal>SQLCODE</literal> values: - - <variablelist> - <varlistentry> - <term>0 (<symbol>ECPG_NO_ERROR</symbol>)</term> - <listitem> - <para> - Indicates no error. (SQLSTATE 00000) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>100 (<symbol>ECPG_NOT_FOUND</symbol>)</term> - <listitem> - <para> - This is a harmless condition indicating that the last command - retrieved or processed zero rows, or that you are at the end of - the cursor. (SQLSTATE 02000) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-12 (<symbol>ECPG_OUT_OF_MEMORY</symbol>)</term> - <listitem> - <para> - Indicates that your virtual memory is exhausted. The numeric - value is defined as <literal>-ENOMEM</literal>. (SQLSTATE - YE001) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-200 (<symbol>ECPG_UNSUPPORTED</symbol>)</term> - <listitem> - <para> - 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. (SQLSTATE YE002) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-201 (<symbol>ECPG_TOO_MANY_ARGUMENTS</symbol>)</term> - <listitem> - <para> - This means that the command specified more host variables than - the command expected. (SQLSTATE 07001 or 07002) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-202 (<symbol>ECPG_TOO_FEW_ARGUMENTS</symbol>)</term> - <listitem> - <para> - This means that the command specified fewer host variables than - the command expected. (SQLSTATE 07001 or 07002) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-203 (<symbol>ECPG_TOO_MANY_MATCHES</symbol>)</term> - <listitem> - <para> - This means a query has returned multiple rows but the statement - was only prepared to store one result row (for example, because - the specified variables are not arrays). (SQLSTATE 21000) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-204 (<symbol>ECPG_INT_FORMAT</symbol>)</term> - <listitem> - <para> - The host variable is of type <type>int</type> and the datum in - the database is of a different type and contains a value that - cannot be interpreted as an <type>int</type>. The library uses - <function>strtol()</function> for this conversion. (SQLSTATE - 42804) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-205 (<symbol>ECPG_UINT_FORMAT</symbol>)</term> - <listitem> - <para> - The host variable is of type <type>unsigned int</type> and the - datum in the database is of a different type and contains a - value that cannot be interpreted as an <type>unsigned - int</type>. The library uses <function>strtoul()</function> - for this conversion. (SQLSTATE 42804) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-206 (<symbol>ECPG_FLOAT_FORMAT</symbol>)</term> - <listitem> - <para> - The host variable is of type <type>float</type> and the datum - in the database is of another type and contains a value that - cannot be interpreted as a <type>float</type>. The library - uses <function>strtod()</function> for this conversion. - (SQLSTATE 42804) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-207 (<symbol>ECPG_NUMERIC_FORMAT</symbol>)</term> - <listitem> - <para> - The host variable is of type <type>numeric</type> and the datum - in the database is of another type and contains a value that - cannot be interpreted as a <type>numeric</type> value. - (SQLSTATE 42804) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-208 (<symbol>ECPG_INTERVAL_FORMAT</symbol>)</term> - <listitem> - <para> - The host variable is of type <type>interval</type> and the datum - in the database is of another type and contains a value that - cannot be interpreted as an <type>interval</type> value. - (SQLSTATE 42804) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-209 (<symbol>ECPG_DATE_FORMAT</symbol>)</term> - <listitem> - <para> - The host variable is of type <type>date</type> and the datum in - the database is of another type and contains a value that - cannot be interpreted as a <type>date</type> value. - (SQLSTATE 42804) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-210 (<symbol>ECPG_TIMESTAMP_FORMAT</symbol>)</term> - <listitem> - <para> - The host variable is of type <type>timestamp</type> and the - datum in the database is of another type and contains a value - that cannot be interpreted as a <type>timestamp</type> value. - (SQLSTATE 42804) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-211 (<symbol>ECPG_CONVERT_BOOL</symbol>)</term> - <listitem> - <para> - This means the host variable is of type <type>bool</type> and - the datum in the database is neither <literal>'t'</> nor - <literal>'f'</>. (SQLSTATE 42804) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-212 (<symbol>ECPG_EMPTY</symbol>)</term> - <listitem> - <para> - The statement sent to the <productname>PostgreSQL</productname> - server was empty. (This cannot normally happen in an embedded - SQL program, so it might point to an internal error.) (SQLSTATE - YE002) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-213 (<symbol>ECPG_MISSING_INDICATOR</symbol>)</term> - <listitem> - <para> - A null value was returned and no null indicator variable was - supplied. (SQLSTATE 22002) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-214 (<symbol>ECPG_NO_ARRAY</symbol>)</term> - <listitem> - <para> - An ordinary variable was used in a place that requires an - array. (SQLSTATE 42804) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-215 (<symbol>ECPG_DATA_NOT_ARRAY</symbol>)</term> - <listitem> - <para> - The database returned an ordinary variable in a place that - requires array value. (SQLSTATE 42804) - </para> - </listitem> - </varlistentry> - -<![IGNORE[ - <!-- disabled by #if 0 in ecpglib --> - <varlistentry> - <term>-216 (<symbol>ECPG_ARRAY_INSERT</symbol>)</term> - <listitem> - <para> - The value could not be inserted into the array. (SQLSTATE - 42804) - </para> - </listitem> - </varlistentry> -]]> - - <varlistentry> - <term>-220 (<symbol>ECPG_NO_CONN</symbol>)</term> - <listitem> - <para> - The program tried to access a connection that does not exist. - (SQLSTATE 08003) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-221 (<symbol>ECPG_NOT_CONN</symbol>)</term> - <listitem> - <para> - The program tried to access a connection that does exist but is - not open. (This is an internal error.) (SQLSTATE YE002) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-230 (<symbol>ECPG_INVALID_STMT</symbol>)</term> - <listitem> - <para> - The statement you are trying to use has not been prepared. - (SQLSTATE 26000) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-239 (<symbol>ECPG_INFORMIX_DUPLICATE_KEY</symbol>)</term> - <listitem> - <para> - Duplicate key error, violation of unique constraint (Informix - compatibility mode). (SQLSTATE 23505) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-240 (<symbol>ECPG_UNKNOWN_DESCRIPTOR</symbol>)</term> - <listitem> - <para> - The descriptor specified was not found. The statement you are - trying to use has not been prepared. (SQLSTATE 33000) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-241 (<symbol>ECPG_INVALID_DESCRIPTOR_INDEX</symbol>)</term> - <listitem> - <para> - The descriptor index specified was out of range. (SQLSTATE - 07009) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-242 (<symbol>ECPG_UNKNOWN_DESCRIPTOR_ITEM</symbol>)</term> - <listitem> - <para> - An invalid descriptor item was requested. (This is an internal - error.) (SQLSTATE YE002) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-243 (<symbol>ECPG_VAR_NOT_NUMERIC</symbol>)</term> - <listitem> - <para> - During the execution of a dynamic statement, the database - returned a numeric value and the host variable was not numeric. - (SQLSTATE 07006) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-244 (<symbol>ECPG_VAR_NOT_CHAR</symbol>)</term> - <listitem> - <para> - During the execution of a dynamic statement, the database - returned a non-numeric value and the host variable was numeric. - (SQLSTATE 07006) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-284 (<symbol>ECPG_INFORMIX_SUBSELECT_NOT_ONE</symbol>)</term> - <listitem> - <para> - A result of the subquery is not single row (Informix - compatibility mode). (SQLSTATE 21000) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-400 (<symbol>ECPG_PGSQL</symbol>)</term> - <listitem> - <para> - Some error caused by the <productname>PostgreSQL</productname> - server. The message contains the error message from the - <productname>PostgreSQL</productname> server. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-401 (<symbol>ECPG_TRANS</symbol>)</term> - <listitem> - <para> - The <productname>PostgreSQL</productname> server signaled that - we cannot start, commit, or rollback the transaction. - (SQLSTATE 08007) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-402 (<symbol>ECPG_CONNECT</symbol>)</term> - <listitem> - <para> - The connection attempt to the database did not succeed. - (SQLSTATE 08001) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-403 (<symbol>ECPG_DUPLICATE_KEY</symbol>)</term> - <listitem> - <para> - Duplicate key error, violation of unique constraint. (SQLSTATE - 23505) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-404 (<symbol>ECPG_SUBSELECT_NOT_ONE</symbol>)</term> - <listitem> - <para> - A result for the subquery is not single row. (SQLSTATE 21000) - </para> - </listitem> - </varlistentry> - -<![IGNORE[ - <!-- currently not used by the code --> - <varlistentry> - <term>-600 (<symbol>ECPG_WARNING_UNRECOGNIZED</symbol>)</term> - <listitem> - <para> - An unrecognized warning was received from the server. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-601 (<symbol>ECPG_WARNING_QUERY_IGNORED</symbol>)</term> - <listitem> - <para> - Current transaction is aborted. Queries are ignored until the - end of the transaction block. - </para> - </listitem> - </varlistentry> -]]> - - <varlistentry> - <term>-602 (<symbol>ECPG_WARNING_UNKNOWN_PORTAL</symbol>)</term> - <listitem> - <para> - An invalid cursor name was specified. (SQLSTATE 34000) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-603 (<symbol>ECPG_WARNING_IN_TRANSACTION</symbol>)</term> - <listitem> - <para> - Transaction is in progress. (SQLSTATE 25001) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-604 (<symbol>ECPG_WARNING_NO_TRANSACTION</symbol>)</term> - <listitem> - <para> - There is no active (in-progress) transaction. (SQLSTATE 25P01) - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>-605 (<symbol>ECPG_WARNING_PORTAL_EXISTS</symbol>)</term> - <listitem> - <para> - An existing cursor name was specified. (SQLSTATE 42P03) - </para> - </listitem> - </varlistentry> - - </variablelist> - </para> - </sect2> - </sect1> - - <sect1 id="ecpg-preproc"> - <title>Preprocessor directives</title> - - <sect2> - <title>Including files</title> - - <para> - To include an external file into your embedded SQL program, use: -<programlisting> -EXEC SQL INCLUDE <replaceable>filename</replaceable>; -</programlisting> - The embedded SQL preprocessor will look for a file named - <literal><replaceable>filename</replaceable>.h</literal>, - preprocess it, and include it in the resulting C output. Thus, - embedded SQL statements in the included file are handled correctly. - </para> - - <para> - Note that this is <emphasis>not</emphasis> the same as: -<programlisting> -#include <<replaceable>filename</replaceable>.h> -</programlisting> - because this file would not be subject to SQL command preprocessing. - Naturally, you can continue to use the C - <literal>#include</literal> directive to include other header - files. - </para> - - <note> - <para> - The include file name is case-sensitive, even though the rest of - the <literal>EXEC SQL INCLUDE</literal> command follows the normal - SQL case-sensitivity rules. - </para> - </note> - </sect2> - - <sect2> - <title>The #define and #undef directives</title> - <para> - Similar to the directive <literal>#define</literal> that is known from C, - embedded SQL has a similar concept: -<programlisting> -EXEC SQL DEFINE <replaceable>name</>; -EXEC SQL DEFINE <replaceable>name</> <replaceable>value</>; -</programlisting> - So you can define a name: -<programlisting> -EXEC SQL DEFINE HAVE_FEATURE; -</programlisting> - And you can also define constants: -<programlisting> -EXEC SQL DEFINE MYNUMBER 12; -EXEC SQL DEFINE MYSTRING 'abc'; -</programlisting> - Use <literal>undef</> to remove a previous definition: -<programlisting> -EXEC SQL UNDEF MYNUMBER; -</programlisting> - </para> - - <para> - Of course you can continue to use the C versions <literal>#define</literal> - and <literal>#undef</literal> in your embedded SQL program. The difference - is where your defined values get evaluated. If you use <literal>EXEC SQL - DEFINE</> then the <command>ecpg</> preprocessor evaluates the defines and substitutes - the values. For example if you write: -<programlisting> -EXEC SQL DEFINE MYNUMBER 12; -... -EXEC SQL UPDATE Tbl SET col = MYNUMBER; -</programlisting> - then <command>ecpg</> will already do the substitution and your C compiler will never - see any name or identifier <literal>MYNUMBER</>. Note that you cannot use - <literal>#define</literal> for a constant that you are going to use in an - embedded SQL query because in this case the embedded SQL precompiler is not - able to see this declaration. - </para> - </sect2> - - <sect2> - <title>ifdef, ifndef, else, elif, and endif directives</title> - <para> - You can use the following directives to compile code sections conditionally: - - <variablelist> - <varlistentry> - <term><literal>EXEC SQL ifdef <replaceable>name</>;</literal></term> - <listitem> - <para> - Checks a <replaceable>name</> and processes subsequent lines if - <replaceable>name</> has been created with <literal>EXEC SQL define - <replaceable>name</></literal>. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>EXEC SQL ifndef <replaceable>name</>;</literal></term> - <listitem> - <para> - Checks a <replaceable>name</> and processes subsequent lines if - <replaceable>name</> has <emphasis>not</emphasis> been created with - <literal>EXEC SQL define <replaceable>name</></literal>. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>EXEC SQL else;</literal></term> - <listitem> - <para> - Starts processing an alternative section to a section introduced by - either <literal>EXEC SQL ifdef <replaceable>name</></literal> or - <literal>EXEC SQL ifndef <replaceable>name</></literal>. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>EXEC SQL elif <replaceable>name</>;</literal></term> - <listitem> - <para> - Checks <replaceable>name</> and starts an alternative section if - <replaceable>name</> has been created with <literal>EXEC SQL define - <replaceable>name</></literal>. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>EXEC SQL endif;</literal></term> - <listitem> - <para> - Ends an alternative section. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - - <para> - Example: -<programlisting> -EXEC SQL ifndef TZVAR; -EXEC SQL SET TIMEZONE TO 'GMT'; -EXEC SQL elif TZNAME; -EXEC SQL SET TIMEZONE TO TZNAME; -EXEC SQL else; -EXEC SQL SET TIMEZONE TO TZVAR; -EXEC SQL endif; -</programlisting> - </para> - - </sect2> - </sect1> - - <sect1 id="ecpg-process"> - <title>Processing Embedded SQL Programs</title> - - <para> - 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 <acronym>SQL</acronym> - <acronym>C</acronym> preprocessor, which converts the - <acronym>SQL</acronym> 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 <acronym>SQL</acronym> command using - the <application>libpq</application> interface, and put the result - in the arguments specified for output. - </para> - - <para> - The preprocessor program is called <filename>ecpg</filename> and is - included in a normal <productname>PostgreSQL</> installation. - Embedded SQL programs are typically named with an extension - <filename>.pgc</filename>. If you have a program file called - <filename>prog1.pgc</filename>, you can preprocess it by simply - calling: -<programlisting> -ecpg prog1.pgc -</programlisting> - This will create a file called <filename>prog1.c</filename>. If - your input files do not follow the suggested naming pattern, you - can specify the output file explicitly using the - <option>-o</option> option. - </para> - - <para> - The preprocessed file can be compiled normally, for example: -<programlisting> -cc -c prog1.c -</programlisting> - The generated C source files include header files from the - <productname>PostgreSQL</> installation, so if you installed - <productname>PostgreSQL</> in a location that is not searched by - default, you have to add an option such as - <literal>-I/usr/local/pgsql/include</literal> to the compilation - command line. - </para> - - <para> - To link an embedded SQL program, you need to include the - <filename>libecpg</filename> library, like so: -<programlisting> -cc -o myprog prog1.o prog2.o ... -lecpg -</programlisting> - Again, you might have to add an option like - <literal>-L/usr/local/pgsql/lib</literal> to that command line. - </para> - - <para> - If you manage the build process of a larger project using - <application>make</application>, it might be convenient to include - the following implicit rule to your makefiles: -<programlisting> -ECPG = ecpg - -%.c: %.pgc - $(ECPG) $< -</programlisting> - </para> - - <para> - The complete syntax of the <command>ecpg</command> command is - detailed in <xref linkend="app-ecpg">. - </para> - - <para> - The <application>ecpg</application> library is thread-safe by - default. However, you might need to use some threading - command-line options to compile your client code. - </para> - </sect1> - - <sect1 id="ecpg-library"> - <title>Library Functions</title> - - <para> - The <filename>libecpg</filename> library primarily contains - <quote>hidden</quote> 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. - </para> - - <itemizedlist> - <listitem> - <para> - <function>ECPGdebug(int <replaceable>on</replaceable>, FILE - *<replaceable>stream</replaceable>)</function> turns on debug - logging if called with the first argument non-zero. Debug logging - is done on <replaceable>stream</replaceable>. The log contains - all <acronym>SQL</acronym> statements with all the input - variables inserted, and the results from the - <productname>PostgreSQL</productname> server. This can be very - useful when searching for errors in your <acronym>SQL</acronym> - statements. - </para> - <note> - <para> - On Windows, if the <application>ecpg</> libraries and an application are - compiled with different flags, this function call will crash the - application because the internal representation of the - <literal>FILE</> pointers differ. Specifically, - multithreaded/single-threaded, release/debug, and static/dynamic - flags should be the same for the library and all applications using - that library. - </para> - </note> - </listitem> - - <listitem> - <para> - <function>ECPGget_PGconn(const char *<replaceable>connection_name</replaceable>) - </function> returns the library database connection handle identified by the given name. - If <replaceable>connection_name</replaceable> is set to <literal>NULL</literal>, the current - connection handle is returned. If no connection handle can be identified, the function returns - <literal>NULL</literal>. The returned connection handle can be used to call any other functions - from <application>libpq</application>, if necessary. - </para> - <note> - <para> - It is a bad idea to manipulate database connection handles made from <application>ecpg</application> directly - with <application>libpq</application> routines. - </para> - </note> - </listitem> - - <listitem> - <para> - <function>ECPGtransactionStatus(const char *<replaceable>connection_name</replaceable>)</function> - returns the current transaction status of the given connection identified by <replaceable>connection_name</replaceable>. - See <xref linkend="libpq-status"> and libpq's <function>PQtransactionStatus()</function> for details about the returned status codes. - </para> - </listitem> - - <listitem> - <para> - <function>ECPGstatus(int <replaceable>lineno</replaceable>, - const char* <replaceable>connection_name</replaceable>)</function> - returns true if you are connected to a database and false if not. - <replaceable>connection_name</replaceable> can be <literal>NULL</> - if a single connection is being used. - </para> - </listitem> - </itemizedlist> - </sect1> - <sect1 id="ecpg-develop"> <title>Internals</title> diff --git a/doc/src/sgml/stylesheet-man.xsl b/doc/src/sgml/stylesheet-man.xsl index 81e875614a8..8c614ca754d 100644 --- a/doc/src/sgml/stylesheet-man.xsl +++ b/doc/src/sgml/stylesheet-man.xsl @@ -90,6 +90,50 @@ </xsl:template> +<!-- For refentries we don't man to generate a man page for, leave out + manvolnum, let it default to 0, and skip writing out man files + with section 0. --> + +<!-- overridden from common/refentry.xsl --> +<xsl:template name="get.refentry.section"> + <xsl:choose> + <xsl:when test="refmeta/manvolnum"> + <xsl:value-of select="refmeta/manvolnum"/> + </xsl:when> + <xsl:otherwise> + <xsl:text>0</xsl:text> + </xsl:otherwise> + </xsl:choose> +</xsl:template> + +<!-- overridden from manpages/other.xsl --> + <xsl:template name="write.man.file"> + <xsl:param name="name"/> + <xsl:param name="section"/> + <xsl:param name="lang"/> + <xsl:param name="content"/> + <xsl:param name="filename"> + <xsl:call-template name="make.adjusted.man.filename"> + <xsl:with-param name="name" select="$name"/> + <xsl:with-param name="section" select="$section"/> + <xsl:with-param name="lang" select="$lang"/> + </xsl:call-template> + </xsl:param> + <xsl:if test="$section != 0"> + <xsl:call-template name="write.text.chunk"> + <xsl:with-param name="filename" select="$filename"/> + <xsl:with-param name="suppress-context-node-name" select="1"/> + <xsl:with-param name="quiet" select="$man.output.quietly"/> + <xsl:with-param + name="message-prolog" + >Note: </xsl:with-param> + <xsl:with-param name="encoding" select="$man.output.encoding"/> + <xsl:with-param name="content" select="$content"/> + </xsl:call-template> + </xsl:if> + </xsl:template> + + <!-- Overridden template as workaround for this problem: <https://sourceforge.net/tracker/?func=detail&aid=2831602&group_id=21935&atid=373747> --> |