diff options
Diffstat (limited to 'doc/src/sgml/ecpg.sgml')
| -rw-r--r-- | doc/src/sgml/ecpg.sgml | 10046 |
1 files changed, 10046 insertions, 0 deletions
diff --git a/doc/src/sgml/ecpg.sgml b/doc/src/sgml/ecpg.sgml new file mode 100644 index 0000000..e406868 --- /dev/null +++ b/doc/src/sgml/ecpg.sgml @@ -0,0 +1,10046 @@ +<!-- doc/src/sgml/ecpg.sgml --> + +<chapter id="ecpg"> + <title><application>ECPG</application> — Embedded <acronym>SQL</acronym> in C</title> + + <indexterm zone="ecpg"><primary>embedded SQL</primary><secondary>in C</secondary></indexterm> + <indexterm zone="ecpg"><primary>C</primary></indexterm> + <indexterm zone="ecpg"><primary>ECPG</primary></indexterm> + + <para> + This chapter describes the embedded <acronym>SQL</acronym> package + for <productname>PostgreSQL</productname>. It was written by + 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. + </para> + + <para> + This documentation is quite incomplete. But since this + interface is standardized, additional information can be found in + many resources about SQL. + </para> + + <sect1 id="ecpg-concept"> + <title>The Concept</title> + + <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 (<filename>*.pgc</filename>) + is first passed through the embedded SQL preprocessor, which converts it + 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> + Embedded <acronym>SQL</acronym> has advantages over other methods + for handling <acronym>SQL</acronym> commands from C code. First, it + takes care of the tedious passing of information to and from + variables in your <acronym>C</acronym> program. Second, the SQL + code in the program is checked at build time for syntactical + correctness. Third, embedded <acronym>SQL</acronym> in C is + specified in the <acronym>SQL</acronym> standard and supported by + many other <acronym>SQL</acronym> database systems. The + <productname>PostgreSQL</productname> implementation is designed to match this + standard as much as possible, and it is usually possible to port + embedded <acronym>SQL</acronym> programs written for other SQL + databases to <productname>PostgreSQL</productname> with relative + ease. + </para> + + <para> + As already stated, programs written for the embedded + <acronym>SQL</acronym> interface are normal C programs with special + code inserted to perform database-related actions. This special + code always has the form: +<programlisting> +EXEC SQL ...; +</programlisting> + These statements syntactically take the place of a C statement. + Depending on the particular statement, they can appear at the + global level or within a function. + </para> + + <para> + Embedded + <acronym>SQL</acronym> statements follow the case-sensitivity rules of + normal <acronym>SQL</acronym> code, and not those of C. Also they allow nested + C-style comments as per the SQL standard. The C part of the + program, however, follows the C standard of not accepting nested comments. + Embedded <acronym>SQL</acronym> statements likewise use SQL rules, not + C rules, for parsing quoted strings and identifiers. + (See <xref linkend="sql-syntax-strings"/> and + <xref linkend="sql-syntax-identifiers"/> respectively. Note that + ECPG assumes that <varname>standard_conforming_strings</varname> + is <literal>on</literal>.) + Of course, the C part of the program follows C quoting rules. + </para> + + <para> + The following sections explain all the embedded SQL statements. + </para> + </sect1> + + <sect1 id="ecpg-connect"> + <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: +<programlisting> +EXEC SQL CONNECT TO <replaceable>target</replaceable> <optional>AS <replaceable>connection-name</replaceable></optional> <optional>USER <replaceable>user-name</replaceable></optional>; +</programlisting> + The <replaceable>target</replaceable> can be specified in the + following ways: + + <itemizedlist> + <listitem> + <simpara> + <literal><replaceable>dbname</replaceable><optional>@<replaceable>hostname</replaceable></optional><optional>:<replaceable>port</replaceable></optional></literal> + </simpara> + </listitem> + + <listitem> + <simpara> + <literal>tcp:postgresql://<replaceable>hostname</replaceable><optional>:<replaceable>port</replaceable></optional><optional>/<replaceable>dbname</replaceable></optional><optional>?<replaceable>options</replaceable></optional></literal> + </simpara> + </listitem> + + <listitem> + <simpara> + <literal>unix:postgresql://localhost<optional>:<replaceable>port</replaceable></optional><optional>/<replaceable>dbname</replaceable></optional><optional>?<replaceable>options</replaceable></optional></literal> + </simpara> + </listitem> + + <listitem> + <simpara> + an SQL string literal containing one of the above forms + </simpara> + </listitem> + + <listitem> + <simpara> + a reference to a character variable containing one of the above forms (see examples) + </simpara> + </listitem> + + <listitem> + <simpara> + <literal>DEFAULT</literal> + </simpara> + </listitem> + </itemizedlist> + + The connection target <literal>DEFAULT</literal> initiates a connection + to the default database under the default user name. No separate + user name or connection name can be specified in that case. + </para> + + <para> + If you specify the connection target directly (that is, not as a string + literal or variable reference), then the components of the target are + passed through normal SQL parsing; this means that, for example, + the <replaceable>hostname</replaceable> must look like one or more SQL + identifiers separated by dots, and those identifiers will be + case-folded unless double-quoted. Values of + any <replaceable>options</replaceable> must be SQL identifiers, + integers, or variable references. Of course, you can put nearly + anything into an SQL identifier by double-quoting it. + In practice, it is probably less error-prone to use a (single-quoted) + string literal or a variable reference than to write the connection + target directly. + </para> + + <para> + There are also different ways to specify the user name: + + <itemizedlist> + <listitem> + <simpara> + <literal><replaceable>username</replaceable></literal> + </simpara> + </listitem> + + <listitem> + <simpara> + <literal><replaceable>username</replaceable>/<replaceable>password</replaceable></literal> + </simpara> + </listitem> + + <listitem> + <simpara> + <literal><replaceable>username</replaceable> IDENTIFIED BY <replaceable>password</replaceable></literal> + </simpara> + </listitem> + + <listitem> + <simpara> + <literal><replaceable>username</replaceable> USING <replaceable>password</replaceable></literal> + </simpara> + </listitem> + </itemizedlist> + + As above, the parameters <replaceable>username</replaceable> and + <replaceable>password</replaceable> can be an SQL identifier, an + SQL string literal, or a reference to a character variable. + </para> + + <para> + If the connection target includes any <replaceable>options</replaceable>, + those consist of + <literal><replaceable>keyword</replaceable>=<replaceable>value</replaceable></literal> + specifications separated by ampersands (<literal>&</literal>). + The allowed key words are the same ones recognized + by <application>libpq</application> (see + <xref linkend="libpq-paramkeywords"/>). Spaces are ignored before + any <replaceable>keyword</replaceable> or <replaceable>value</replaceable>, + though not within or after one. Note that there is no way to + write <literal>&</literal> within a <replaceable>value</replaceable>. + </para> + + <para> + Notice that when specifying a socket connection + (with the <literal>unix:</literal> prefix), the host name must be + exactly <literal>localhost</literal>. To select a non-default + socket directory, write the directory's pathname as the value of + a <varname>host</varname> option in + the <replaceable>options</replaceable> part of the target. + </para> + + <para> + The <replaceable>connection-name</replaceable> is used to handle + multiple connections in one program. It can be omitted if a + program uses only one connection. The most recently opened + connection becomes the current connection, which is used by default + when an SQL statement is to be executed (see later in this + chapter). + </para> + + <para> + Here are some examples of <command>CONNECT</command> statements: +<programlisting> +EXEC SQL CONNECT TO mydb@sql.mydomain.com; + +EXEC SQL CONNECT TO tcp:postgresql://sql.mydomain.com/mydb AS myconnection USER john; + +EXEC SQL BEGIN DECLARE SECTION; +const char *target = "mydb@sql.mydomain.com"; +const char *user = "john"; +const char *passwd = "secret"; +EXEC SQL END DECLARE SECTION; + ... +EXEC SQL CONNECT TO :target USER :user USING :passwd; +/* or EXEC SQL CONNECT TO :target USER :user/:passwd; */ +</programlisting> + The last example makes use of the feature referred to above as + character variable references. You will see in later sections how C + variables can be used in SQL statements when you prefix them with a + colon. + </para> + + <para> + Be advised that the format of the connection target is not + specified in the SQL standard. So if you want to develop portable + applications, you might want to use something based on the last + example above to encapsulate the connection target string + somewhere. + </para> + + <para> + If untrusted users have access to a database that has not adopted a + <link linkend="ddl-schemas-patterns">secure schema usage pattern</link>, + begin each session by removing publicly-writable schemas + from <varname>search_path</varname>. For example, + add <literal>options=-c search_path=</literal> + to <literal><replaceable>options</replaceable></literal>, or + issue <literal>EXEC SQL SELECT pg_catalog.set_config('search_path', '', + false);</literal> after connecting. This consideration is not specific to + ECPG; it applies to every interface for executing arbitrary SQL commands. + </para> + </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 + three 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. + </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. + </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 SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + EXEC SQL CONNECT TO testdb2 AS con2 USER testuser; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + EXEC SQL CONNECT TO testdb3 AS con3 USER testuser; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + + /* 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> + + <para> + The third option is to declare an SQL identifier linked to + the connection, for example: +<programlisting> +EXEC SQL AT <replaceable>connection-name</replaceable> DECLARE <replaceable>statement-name</replaceable> STATEMENT; +EXEC SQL PREPARE <replaceable>statement-name</replaceable> FROM :<replaceable>dyn-string</replaceable>; +</programlisting> + Once you link an SQL identifier to a connection, you execute dynamic SQL + without an AT clause. Note that this option behaves like preprocessor + directives, therefore the link is enabled only in the file. + </para> + <para> + Here is an example program using this option: +<programlisting><![CDATA[ +#include <stdio.h> + +EXEC SQL BEGIN DECLARE SECTION; +char dbname[128]; +char *dyn_sql = "SELECT current_database()"; +EXEC SQL END DECLARE SECTION; + +int main(){ + EXEC SQL CONNECT TO postgres AS con1; + EXEC SQL CONNECT TO testdb AS con2; + EXEC SQL AT con1 DECLARE stmt STATEMENT; + EXEC SQL PREPARE stmt FROM :dyn_sql; + EXEC SQL EXECUTE stmt INTO :dbname; + printf("%s\n", dbname); + + EXEC SQL DISCONNECT ALL; + return 0; +} +]]></programlisting> + + This example would produce this output, even if the default connection is testdb: +<screen> +postgres +</screen> + </para> + </sect2> + + <sect2 id="ecpg-disconnect"> + <title>Closing a Connection</title> + + <para> + To close a connection, use the following statement: +<programlisting> +EXEC SQL DISCONNECT <optional><replaceable>connection</replaceable></optional>; +</programlisting> + The <replaceable>connection</replaceable> can be specified + in the following ways: + + <itemizedlist> + <listitem> + <simpara> + <literal><replaceable>connection-name</replaceable></literal> + </simpara> + </listitem> + + <listitem> + <simpara> + <literal>CURRENT</literal> + </simpara> + </listitem> + + <listitem> + <simpara> + <literal>ALL</literal> + </simpara> + </listitem> + </itemizedlist> + + If no connection name is specified, the current connection is + closed. + </para> + + <para> + It is good style that an application always explicitly disconnect + from every connection it opened. + </para> + </sect2> + + </sect1> + + <sect1 id="ecpg-commands"> + <title>Running SQL Commands</title> + + <para> + Any SQL command can be run from within an embedded SQL application. + Below are some examples of how to do that. + </para> + + <sect2 id="ecpg-executing"> + <title>Executing SQL Statements</title> + + <para> + Creating a table: +<programlisting> +EXEC SQL CREATE TABLE foo (number integer, ascii char(16)); +EXEC SQL CREATE UNIQUE INDEX num1 ON foo(number); +EXEC SQL COMMIT; +</programlisting> + </para> + + <para> + Inserting rows: +<programlisting> +EXEC SQL INSERT INTO foo (number, ascii) VALUES (9999, 'doodad'); +EXEC SQL COMMIT; +</programlisting> + </para> + + <para> + Deleting rows: +<programlisting> +EXEC SQL DELETE FROM foo WHERE number = 9999; +EXEC SQL COMMIT; +</programlisting> + </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'; +</programlisting> + </para> + + <para> + Also, a configuration parameter can be retrieved 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 + SELECT number, ascii FROM foo + ORDER BY ascii; +EXEC SQL OPEN foo_bar; +EXEC SQL FETCH foo_bar INTO :FooBar, DooDad; +... +EXEC SQL CLOSE foo_bar; +EXEC SQL COMMIT; +</programlisting> + </para> + + <para> + For more details about declaring a cursor, see <xref + linkend="ecpg-sql-declare"/>; for more details about fetching rows from a + cursor, see <xref linkend="sql-fetch"/>. + </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>psql</application>'s default 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> + + <para> + The following transaction management commands are available: + + <variablelist> + <varlistentry> + <term><literal>EXEC SQL COMMIT</literal></term> + <listitem> + <para> + Commit an in-progress transaction. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>EXEC SQL ROLLBACK</literal></term> + <listitem> + <para> + Roll back an in-progress transaction. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>EXEC SQL PREPARE TRANSACTION </literal><replaceable class="parameter">transaction_id</replaceable></term> + <listitem> + <para> + Prepare the current transaction for two-phase commit. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>EXEC SQL COMMIT PREPARED </literal><replaceable class="parameter">transaction_id</replaceable></term> + <listitem> + <para> + Commit a transaction that is in prepared state. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>EXEC SQL ROLLBACK PREPARED </literal><replaceable class="parameter">transaction_id</replaceable></term> + <listitem> + <para> + Roll back a transaction that is in prepared state. + </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>EXEC SQL 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 PREPARE stmt1 FROM "SELECT oid, datname FROM pg_database WHERE oid = ?"; +</programlisting> + </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> + If a statement returns 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 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> + </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"> + <title>Using Host Variables</title> + + <para> + In <xref linkend="ecpg-commands"/> you saw how you can execute SQL + statements from an embedded SQL program. Some of those statements + only used fixed values and did not provide a way to insert + user-supplied values into statements or have the program process + the values returned by the query. Those kinds of statements are + not really useful in real applications. This section explains in + detail how you can pass data between your C program and the + embedded SQL statements using a simple mechanism called + <firstterm>host variables</firstterm>. In an embedded SQL program we + consider the SQL statements to be <firstterm>guests</firstterm> in the C + program code which is the <firstterm>host language</firstterm>. Therefore + the variables of the C program are called <firstterm>host + variables</firstterm>. + </para> + + <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> + Passing data between the C program and the SQL statements is + particularly simple in embedded SQL. Instead of having the + program paste the data into the statement, which entails various + complications, such as properly quoting the value, you can simply + write the name of a C variable into the SQL statement, prefixed by + a colon. For example: +<programlisting> +EXEC SQL INSERT INTO sometable VALUES (:v1, 'foo', :v2); +</programlisting> + This statement refers to two C variables named + <varname>v1</varname> and <varname>v2</varname> and also uses a + regular SQL string literal, to illustrate that you are not + restricted to use one kind of data or the other. + </para> + + <para> + This style of inserting C variables in SQL statements works + anywhere a value expression is expected in an SQL statement. + </para> + </sect2> + + <sect2 id="ecpg-declare-sections"> + <title>Declare Sections</title> + + <para> + To pass data from the program to the database, for example as + parameters in a query, or to pass data from the database back to + the program, the C variables that are intended to contain this + data need to be declared in specially marked sections, so the + embedded SQL preprocessor is made aware of them. + </para> + + <para> + This section starts with: +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; +</programlisting> + and ends with: +<programlisting> +EXEC SQL END DECLARE SECTION; +</programlisting> + Between those lines, there must be normal C variable declarations, + such as: +<programlisting> +int x = 4; +char foo[16], bar[16]; +</programlisting> + As you can see, you can optionally assign an initial value to the variable. + The variable's scope is determined by the location of its declaring + section within the program. + You can also declare variables with the following syntax which implicitly + creates a declare section: +<programlisting> +EXEC SQL int i = 4; +</programlisting> + You can have as many declare sections in a program as you like. + </para> + + <para> + The declarations are also echoed to the output file as normal C + variables, so there's no need to declare them again. Variables + that are not intended to be used in SQL commands can be declared + normally outside these special sections. + </para> + + <para> + The definition of a structure or union also must be listed inside + a <literal>DECLARE</literal> section. Otherwise the preprocessor cannot + handle these types since it does not know the definition. + </para> + </sect2> + + <sect2 id="ecpg-retrieving"> + <title>Retrieving Query Results</title> + + <para> + Now you should be able to pass data generated by your program into + an SQL command. But how do you retrieve the results of a query? + For that purpose, embedded SQL provides special variants of the + usual commands <command>SELECT</command> and + <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> + Here is an example: +<programlisting> +/* + * assume this table: + * CREATE TABLE test1 (a int, b varchar(50)); + */ + +EXEC SQL BEGIN DECLARE SECTION; +int v1; +VARCHAR v2; +EXEC SQL END DECLARE SECTION; + + ... + +EXEC SQL SELECT a, b INTO :v1, :v2 FROM test; +</programlisting> + So the <literal>INTO</literal> clause appears between the select + list and the <literal>FROM</literal> clause. The number of + elements in the select list and the list after + <literal>INTO</literal> (also called the target list) must be + equal. + </para> + + <para> + Here is an example using the command <command>FETCH</command>: +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; +int v1; +VARCHAR v2; +EXEC SQL END DECLARE SECTION; + + ... + +EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test; + + ... + +do +{ + ... + EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2; + ... +} while (...); +</programlisting> + Here the <literal>INTO</literal> clause appears after all the + normal clauses. + </para> + + </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>smallserial</type></entry> + <entry><type>short</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</replaceable>)</type>, <type>varchar(<replaceable>n</replaceable>)</type>, <type>text</type></entry> + <entry><type>char[<replaceable>n</replaceable>+1]</type>, <type>VARCHAR[<replaceable>n</replaceable>+1]</type></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> + + <row> + <entry><type>bytea</type></entry> + <entry><type>char *</type>, <type>bytea[<replaceable>n</replaceable>]</type></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</type> 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> + <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>, <type>decimal</type>, <type>date</type>, <type>timestamp</type>, + and <type>interval</type> types. These data types cannot usefully be + mapped to primitive host variable types (such + as <type>int</type>, <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 time stamp 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>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 <filename>pgtypes_date.h</filename>, declare a host variable + as the date type and convert a DATE value into a text form using + <function>PGTYPESdate_to_asc()</function> 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; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + + 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; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + + 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> + + <sect4> + <title>bytea</title> + + <para> + The handling of the <type>bytea</type> type is similar to + that of <type>VARCHAR</type>. The definition on an array of type + <type>bytea</type> is converted into a named struct for every + variable. A declaration like: +<programlisting> +bytea var[180]; +</programlisting> + is converted into: +<programlisting> +struct bytea_var { int len; char arr[180]; } var; +</programlisting> + The member <structfield>arr</structfield> hosts binary format + data. It can also handle <literal>'\0'</literal> as part of + data, unlike <type>VARCHAR</type>. + The data is converted from/to hex format and sent/received by + ecpglib. + </para> + + <note> + <para> + <type>bytea</type> variable can be used only when + <xref linkend="guc-bytea-output"/> is set to <literal>hex</literal>. + </para> + </note> + </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 in <xref linkend="ecpg-char"/>. The second use case is to + retrieve 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 accommodate 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; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + + /* 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 + available 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> + Multi-dimensional SQL-level arrays are not directly supported in ECPG. + One-dimensional SQL-level arrays can be mapped into C array host + variables and vice-versa. However, when creating a statement ecpg does + not know the types of the columns, so that it cannot check if a C array + is input into a corresponding SQL-level array. When processing the + output of an SQL statement, ecpg has the necessary information and thus + checks if both are arrays. + </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>(%f,%f)</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> + The examples above do not handle null values. In fact, the + retrieval examples will raise an error if they fetch a null value + from the database. To be able to pass null values to the database + or retrieve null values from the database, you need to append a + second host variable specification to each host variable that + contains data. This second host variable is called the + <firstterm>indicator</firstterm> and contains a flag that tells + whether the datum is null, in which case the value of the real + host variable is ignored. Here is an example that handles the + retrieval of null values correctly: +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; +VARCHAR val; +int val_ind; +EXEC SQL END DECLARE SECTION: + + ... + +EXEC SQL SELECT b INTO :val :val_ind FROM test1; +</programlisting> + The indicator variable <varname>val_ind</varname> will be zero if + the value was not null, and it will be negative if the value was + null. (See <xref linkend="ecpg-oracle-compat"/> to enable + Oracle-specific behavior.) + </para> + + <para> + The indicator has another function: if the indicator value is + 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> + + <sect1 id="ecpg-dynamic"> + <title>Dynamic SQL</title> + + <para> + In many cases, the particular SQL statements that an application + has to execute are known at the time the application is written. + In some cases, however, the SQL statements are composed at run time + or provided by an external source. In these cases you cannot embed + the SQL statements directly into the C source code, but there is a + facility that allows you to call arbitrary SQL statements that you + provide in a string variable. + </para> + + <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 (...);"; +EXEC SQL END DECLARE SECTION; + +EXEC SQL EXECUTE IMMEDIATE :stmt; +</programlisting> + <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> + + <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(?, ?);"; +EXEC SQL END DECLARE SECTION; + +EXEC SQL PREPARE mystmt FROM :stmt; + ... +EXEC SQL EXECUTE mystmt USING 42, 'foobar'; +</programlisting> + </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[50]; +EXEC SQL END DECLARE SECTION; + +EXEC SQL PREPARE mystmt FROM :stmt; + ... +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> + + <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 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 SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + +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> + </sect2> + </sect1> + + <sect1 id="ecpg-pgtypes"> + <title>pgtypes Library</title> + + <para> + The pgtypes library maps <productname>PostgreSQL</productname> database + types to C equivalents that can be used in C programs. It also offers + functions to do basic calculations with those types within C, i.e., without + the help of the <productname>PostgreSQL</productname> server. See the + following example: +<programlisting><![CDATA[ +EXEC SQL BEGIN DECLARE SECTION; + date date1; + timestamp ts1, tsout; + interval iv1; + char *out; +EXEC SQL END DECLARE SECTION; + +PGTYPESdate_today(&date1); +EXEC SQL SELECT started, duration INTO :ts1, :iv1 FROM datetbl WHERE d=:date1; +PGTYPEStimestamp_add_interval(&ts1, &iv1, &tsout); +out = PGTYPEStimestamp_to_asc(&tsout); +printf("Started + duration: %s\n", out); +PGTYPESchar_free(out); +]]> +</programlisting> + </para> + + <sect2 id="ecpg-pgtypes-cstrings"> + <title>Character Strings</title> + <para> + Some functions such as <function>PGTYPESnumeric_to_asc</function> return + a pointer to a freshly allocated character string. These results should be + freed with <function>PGTYPESchar_free</function> instead of + <function>free</function>. (This is important only on Windows, where + memory allocation and release sometimes need to be done by the same + library.) + </para> + </sect2> + + <sect2 id="ecpg-pgtypes-numeric"> + <title>The numeric Type</title> + <para> + The numeric type offers to do calculations with arbitrary precision. See + <xref linkend="datatype-numeric"/> for the equivalent type in the + <productname>PostgreSQL</productname> server. Because of the arbitrary precision this + variable needs to be able to expand and shrink dynamically. That's why you + can only create numeric variables on the heap, by means of the + <function>PGTYPESnumeric_new</function> and <function>PGTYPESnumeric_free</function> + functions. The decimal type, which is similar but limited in precision, + can be created on the stack as well as on the heap. + </para> + <para> + The following functions can be used to work with the numeric type: + <variablelist> + <varlistentry> + <term><function>PGTYPESnumeric_new</function></term> + <listitem> + <para> + Request a pointer to a newly allocated numeric variable. +<synopsis> +numeric *PGTYPESnumeric_new(void); +</synopsis> + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>PGTYPESnumeric_free</function></term> + <listitem> + <para> + Free a numeric type, release all of its memory. +<synopsis> +void PGTYPESnumeric_free(numeric *var); +</synopsis> + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>PGTYPESnumeric_from_asc</function></term> + <listitem> + <para> + Parse a numeric type from its string notation. +<synopsis> +numeric *PGTYPESnumeric_from_asc(char *str, char **endptr); +</synopsis> + Valid formats are for example: + <literal>-2</literal>, + <literal>.794</literal>, + <literal>+3.44</literal>, + <literal>592.49E07</literal> or + <literal>-32.84e-4</literal>. + If the value could be parsed successfully, a valid pointer is returned, + else the NULL pointer. At the moment ECPG always parses the complete + string and so it currently does not support to store the address of the + first invalid character in <literal>*endptr</literal>. You can safely + set <literal>endptr</literal> to NULL. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>PGTYPESnumeric_to_asc</function></term> + <listitem> + <para> + Returns a pointer to a string allocated by <function>malloc</function> that contains the string + representation of the numeric type <literal>num</literal>. +<synopsis> +char *PGTYPESnumeric_to_asc(numeric *num, int dscale); +</synopsis> + The numeric value will be printed with <literal>dscale</literal> decimal + digits, with rounding applied if necessary. + The result must be freed with <function>PGTYPESchar_free()</function>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>PGTYPESnumeric_add</function></term> + <listitem> + <para> + Add two numeric variables into a third one. +<synopsis> +int PGTYPESnumeric_add(numeric *var1, numeric *var2, numeric *result); +</synopsis> + The function adds the variables <literal>var1</literal> and + <literal>var2</literal> into the result variable + <literal>result</literal>. + The function returns 0 on success and -1 in case of error. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>PGTYPESnumeric_sub</function></term> + <listitem> + <para> + Subtract two numeric variables and return the result in a third one. +<synopsis> +int PGTYPESnumeric_sub(numeric *var1, numeric *var2, numeric *result); +</synopsis> + The function subtracts the variable <literal>var2</literal> from + the variable <literal>var1</literal>. The result of the operation is + stored in the variable <literal>result</literal>. + The function returns 0 on success and -1 in case of error. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>PGTYPESnumeric_mul</function></term> + <listitem> + <para> + Multiply two numeric variables and return the result in a third one. +<synopsis> +int PGTYPESnumeric_mul(numeric *var1, numeric *var2, numeric *result); +</synopsis> + The function multiplies the variables <literal>var1</literal> and + <literal>var2</literal>. The result of the operation is stored in the + variable <literal>result</literal>. + The function returns 0 on success and -1 in case of error. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>PGTYPESnumeric_div</function></term> + <listitem> + <para> + Divide two numeric variables and return the result in a third one. +<synopsis> +int PGTYPESnumeric_div(numeric *var1, numeric *var2, numeric *result); +</synopsis> + The function divides the variables <literal>var1</literal> by + <literal>var2</literal>. The result of the operation is stored in the + variable <literal>result</literal>. + The function returns 0 on success and -1 in case of error. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>PGTYPESnumeric_cmp</function></term> + <listitem> + <para> + Compare two numeric variables. +<synopsis> +int PGTYPESnumeric_cmp(numeric *var1, numeric *var2) +</synopsis> + This function compares two numeric variables. In case of error, + <literal>INT_MAX</literal> is returned. On success, the function + returns one of three possible results: + <itemizedlist> + <listitem> + <para> + 1, if <literal>var1</literal> is bigger than <literal>var2</literal> + </para> + </listitem> + <listitem> + <para> + -1, if <literal>var1</literal> is smaller than <literal>var2</literal> + </para> + </listitem> + <listitem> + <para> + 0, if <literal>var1</literal> and <literal>var2</literal> are equal + </para> + </listitem> + </itemizedlist> + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>PGTYPESnumeric_from_int</function></term> + <listitem> + <para> + Convert an int variable to a numeric variable. +<synopsis> +int PGTYPESnumeric_from_int(signed int int_val, numeric *var); +</synopsis> + This function accepts a variable of type signed int and stores it + in the numeric variable <literal>var</literal>. Upon success, 0 is returned and + -1 in case of a failure. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>PGTYPESnumeric_from_long</function></term> + <listitem> + <para> + Convert a long int variable to a numeric variable. +<synopsis> +int PGTYPESnumeric_from_long(signed long int long_val, numeric *var); +</synopsis> + This function accepts a variable of type signed long int and stores it + in the numeric variable <literal>var</literal>. Upon success, 0 is returned and + -1 in case of a failure. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>PGTYPESnumeric_copy</function></term> + <listitem> + <para> + Copy over one numeric variable into another one. +<synopsis> +int PGTYPESnumeric_copy(numeric *src, numeric *dst); +</synopsis> + This function copies over the value of the variable that + <literal>src</literal> points to into the variable that <literal>dst</literal> + points to. It returns 0 on success and -1 if an error occurs. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>PGTYPESnumeric_from_double</function></term> + <listitem> + <para> + Convert a variable of type double to a numeric. +<synopsis> +int PGTYPESnumeric_from_double(double d, numeric *dst); +</synopsis> + This function accepts a variable of type double and stores the result + in the variable that <literal>dst</literal> points to. It returns 0 on success + and -1 if an error occurs. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>PGTYPESnumeric_to_double</function></term> + <listitem> + <para> + Convert a variable of type numeric to double. +<synopsis> +int PGTYPESnumeric_to_double(numeric *nv, double *dp) +</synopsis> + The function converts the numeric value from the variable that + <literal>nv</literal> points to into the double variable that <literal>dp</literal> points + to. It returns 0 on success and -1 if an error occurs, including + overflow. On overflow, the global variable <literal>errno</literal> will be set + to <literal>PGTYPES_NUM_OVERFLOW</literal> additionally. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>PGTYPESnumeric_to_int</function></term> + <listitem> + <para> + Convert a variable of type numeric to int. +<synopsis> +int PGTYPESnumeric_to_int(numeric *nv, int *ip); +</synopsis> + The function converts the numeric value from the variable that + <literal>nv</literal> points to into the integer variable that <literal>ip</literal> + points to. It returns 0 on success and -1 if an error occurs, including + overflow. On overflow, the global variable <literal>errno</literal> will be set + to <literal>PGTYPES_NUM_OVERFLOW</literal> additionally. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>PGTYPESnumeric_to_long</function></term> + <listitem> + <para> + Convert a variable of type numeric to long. +<synopsis> +int PGTYPESnumeric_to_long(numeric *nv, long *lp); +</synopsis> + The function converts the numeric value from the variable that + <literal>nv</literal> points to into the long integer variable that + <literal>lp</literal> points to. It returns 0 on success and -1 if an error + occurs, including overflow. On overflow, the global variable + <literal>errno</literal> will be set to <literal>PGTYPES_NUM_OVERFLOW</literal> + additionally. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>PGTYPESnumeric_to_decimal</function></term> + <listitem> + <para> + Convert a variable of type numeric to decimal. +<synopsis> +int PGTYPESnumeric_to_decimal(numeric *src, decimal *dst); +</synopsis> + The function converts the numeric value from the variable that + <literal>src</literal> points to into the decimal variable that + <literal>dst</literal> points to. It returns 0 on success and -1 if an error + occurs, including overflow. On overflow, the global variable + <literal>errno</literal> will be set to <literal>PGTYPES_NUM_OVERFLOW</literal> + additionally. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>PGTYPESnumeric_from_decimal</function></term> + <listitem> + <para> + Convert a variable of type decimal to numeric. +<synopsis> +int PGTYPESnumeric_from_decimal(decimal *src, numeric *dst); +</synopsis> + The function converts the decimal value from the variable that + <literal>src</literal> points to into the numeric variable that + <literal>dst</literal> points to. It returns 0 on success and -1 if an error + occurs. Since the decimal type is implemented as a limited version of + the numeric type, overflow cannot occur with this conversion. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </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 + date. See <xref linkend="datatype-datetime"/> for the equivalent type in the + <productname>PostgreSQL</productname> server. + </para> + <para> + The following functions can be used to work with the date type: + <variablelist> + <varlistentry id="pgtypesdatefromtimestamp"> + <term><function>PGTYPESdate_from_timestamp</function></term> + <listitem> + <para> + Extract the date part from a timestamp. +<synopsis> +date PGTYPESdate_from_timestamp(timestamp dt); +</synopsis> + The function receives a timestamp as its only argument and returns the + extracted date part from this timestamp. + </para> + </listitem> + </varlistentry> + + <varlistentry id="pgtypesdatefromasc"> + <term><function>PGTYPESdate_from_asc</function></term> + <listitem> + <para> + Parse a date from its textual representation. +<synopsis> +date PGTYPESdate_from_asc(char *str, char **endptr); +</synopsis> + The function receives a C char* string <literal>str</literal> and a pointer to + a C char* string <literal>endptr</literal>. At the moment ECPG always parses + the complete string and so it currently does not support to store the + address of the first invalid character in <literal>*endptr</literal>. + You can safely set <literal>endptr</literal> to NULL. + </para> + <para> + Note that the function always assumes MDY-formatted dates and there is + currently no variable to change that within ECPG. + </para> + <para> + <xref linkend="ecpg-pgtypesdate-from-asc-table"/> shows the allowed input formats. + </para> + <table id="ecpg-pgtypesdate-from-asc-table"> + <title>Valid Input Formats for <function>PGTYPESdate_from_asc</function></title> + <tgroup cols="2"> + <thead> + <row> + <entry>Input</entry> + <entry>Result</entry> + </row> + </thead> + <tbody> + <row> + <entry><literal>January 8, 1999</literal></entry> + <entry><literal>January 8, 1999</literal></entry> + </row> + <row> + <entry><literal>1999-01-08</literal></entry> + <entry><literal>January 8, 1999</literal></entry> + </row> + <row> + <entry><literal>1/8/1999</literal></entry> + <entry><literal>January 8, 1999</literal></entry> + </row> + <row> + <entry><literal>1/18/1999</literal></entry> + <entry><literal>January 18, 1999</literal></entry> + </row> + <row> + <entry><literal>01/02/03</literal></entry> + <entry><literal>February 1, 2003</literal></entry> + </row> + <row> + <entry><literal>1999-Jan-08</literal></entry> + <entry><literal>January 8, 1999</literal></entry> + </row> + <row> + <entry><literal>Jan-08-1999</literal></entry> + <entry><literal>January 8, 1999</literal></entry> + </row> + <row> + <entry><literal>08-Jan-1999</literal></entry> + <entry><literal>January 8, 1999</literal></entry> + </row> + <row> + <entry><literal>99-Jan-08</literal></entry> + <entry><literal>January 8, 1999</literal></entry> + </row> + <row> + <entry><literal>08-Jan-99</literal></entry> + <entry><literal>January 8, 1999</literal></entry> + </row> + <row> + <entry><literal>08-Jan-06</literal></entry> + <entry><literal>January 8, 2006</literal></entry> + </row> + <row> + <entry><literal>Jan-08-99</literal></entry> + <entry><literal>January 8, 1999</literal></entry> + </row> + <row> + <entry><literal>19990108</literal></entry> + <entry><literal>ISO 8601; January 8, 1999</literal></entry> + </row> + <row> + <entry><literal>990108</literal></entry> + <entry><literal>ISO 8601; January 8, 1999</literal></entry> + </row> + <row> + <entry><literal>1999.008</literal></entry> + <entry><literal>year and day of year</literal></entry> + </row> + <row> + <entry><literal>J2451187</literal></entry> + <entry><literal>Julian day</literal></entry> + </row> + <row> + <entry><literal>January 8, 99 BC</literal></entry> + <entry><literal>year 99 before the Common Era</literal></entry> + </row> + </tbody> + </tgroup> + </table> + </listitem> + </varlistentry> + + <varlistentry id="pgtypesdatetoasc"> + <term><function>PGTYPESdate_to_asc</function></term> + <listitem> + <para> + Return the textual representation of a date variable. +<synopsis> +char *PGTYPESdate_to_asc(date dDate); +</synopsis> + The function receives the date <literal>dDate</literal> as its only parameter. + It will output the date in the form <literal>1999-01-18</literal>, i.e., in the + <literal>YYYY-MM-DD</literal> format. + The result must be freed with <function>PGTYPESchar_free()</function>. + </para> + </listitem> + </varlistentry> + + <varlistentry id="pgtypesdatejulmdy"> + <term><function>PGTYPESdate_julmdy</function></term> + <listitem> + <para> + Extract the values for the day, the month and the year from a variable + of type date. +<synopsis> +void PGTYPESdate_julmdy(date d, int *mdy); +</synopsis> + <!-- almost same description as for rjulmdy() --> + The function receives the date <literal>d</literal> and a pointer to an array + of 3 integer values <literal>mdy</literal>. The variable name indicates + the sequential order: <literal>mdy[0]</literal> will be set to contain the + number of the month, <literal>mdy[1]</literal> will be set to the value of the + day and <literal>mdy[2]</literal> will contain the year. + </para> + </listitem> + </varlistentry> + + <varlistentry id="pgtypesdatemdyjul"> + <term><function>PGTYPESdate_mdyjul</function></term> + <listitem> + <para> + Create a date value from an array of 3 integers that specify the + day, the month and the year of the date. +<synopsis> +void PGTYPESdate_mdyjul(int *mdy, date *jdate); +</synopsis> + The function receives the array of the 3 integers (<literal>mdy</literal>) as + its first argument and as its second argument a pointer to a variable + of type date that should hold the result of the operation. + </para> + </listitem> + </varlistentry> + + <varlistentry id="pgtypesdatedayofweek"> + <term><function>PGTYPESdate_dayofweek</function></term> + <listitem> + <para> + Return a number representing the day of the week for a date value. +<synopsis> +int PGTYPESdate_dayofweek(date d); +</synopsis> + The function receives the date variable <literal>d</literal> as its only + argument and returns an integer that indicates the day of the week for + this date. + <itemizedlist> + <listitem> + <para> + 0 - Sunday + </para> + </listitem> + <listitem> + <para> + 1 - Monday + </para> + </listitem> + <listitem> + <para> + 2 - Tuesday + </para> + </listitem> + <listitem> + <para> + 3 - Wednesday + </para> + </listitem> + <listitem> + <para> + 4 - Thursday + </para> + </listitem> + <listitem> + <para> + 5 - Friday + </para> + </listitem> + <listitem> + <para> + 6 - Saturday + </para> + </listitem> + </itemizedlist> + </para> + </listitem> + </varlistentry> + + <varlistentry id="pgtypesdatetoday"> + <term><function>PGTYPESdate_today</function></term> + <listitem> + <para> + Get the current date. +<synopsis> +void PGTYPESdate_today(date *d); +</synopsis> + The function receives a pointer to a date variable (<literal>d</literal>) + that it sets to the current date. + </para> + </listitem> + </varlistentry> + + <varlistentry id="pgtypesdatefmtasc"> + <term><function>PGTYPESdate_fmt_asc</function></term> + <listitem> + <para> + Convert a variable of type date to its textual representation using a + format mask. +<synopsis> +int PGTYPESdate_fmt_asc(date dDate, char *fmtstring, char *outbuf); +</synopsis> + The function receives the date to convert (<literal>dDate</literal>), the + format mask (<literal>fmtstring</literal>) and the string that will hold the + textual representation of the date (<literal>outbuf</literal>). + </para> + <para> + On success, 0 is returned and a negative value if an error occurred. + </para> + <para> + The following literals are the field specifiers you can use: + <itemizedlist> + <listitem> + <para> + <literal>dd</literal> - The number of the day of the month. + </para> + </listitem> + <listitem> + <para> + <literal>mm</literal> - The number of the month of the year. + </para> + </listitem> + <listitem> + <para> + <literal>yy</literal> - The number of the year as a two digit number. + </para> + </listitem> + <listitem> + <para> + <literal>yyyy</literal> - The number of the year as a four digit number. + </para> + </listitem> + <listitem> + <para> + <literal>ddd</literal> - The name of the day (abbreviated). + </para> + </listitem> + <listitem> + <para> + <literal>mmm</literal> - The name of the month (abbreviated). + </para> + </listitem> + </itemizedlist> + All other characters are copied 1:1 to the output string. + </para> + <para> + <xref linkend="ecpg-pgtypesdate-fmt-asc-example-table"/> indicates a few possible formats. This will give + you an idea of how to use this function. All output lines are based on + the same date: November 23, 1959. + </para> + <table id="ecpg-pgtypesdate-fmt-asc-example-table"> + <title>Valid Input Formats for <function>PGTYPESdate_fmt_asc</function></title> + <tgroup cols="2"> + <thead> + <row> + <entry>Format</entry> + <entry>Result</entry> + </row> + </thead> + <tbody> + <row> + <entry><literal>mmddyy</literal></entry> + <entry><literal>112359</literal></entry> + </row> + <row> + <entry><literal>ddmmyy</literal></entry> + <entry><literal>231159</literal></entry> + </row> + <row> + <entry><literal>yymmdd</literal></entry> + <entry><literal>591123</literal></entry> + </row> + <row> + <entry><literal>yy/mm/dd</literal></entry> + <entry><literal>59/11/23</literal></entry> + </row> + <row> + <entry><literal>yy mm dd</literal></entry> + <entry><literal>59 11 23</literal></entry> + </row> + <row> + <entry><literal>yy.mm.dd</literal></entry> + <entry><literal>59.11.23</literal></entry> + </row> + <row> + <entry><literal>.mm.yyyy.dd.</literal></entry> + <entry><literal>.11.1959.23.</literal></entry> + </row> + <row> + <entry><literal>mmm. dd, yyyy</literal></entry> + <entry><literal>Nov. 23, 1959</literal></entry> + </row> + <row> + <entry><literal>mmm dd yyyy</literal></entry> + <entry><literal>Nov 23 1959</literal></entry> + </row> + <row> + <entry><literal>yyyy dd mm</literal></entry> + <entry><literal>1959 23 11</literal></entry> + </row> + <row> + <entry><literal>ddd, mmm. dd, yyyy</literal></entry> + <entry><literal>Mon, Nov. 23, 1959</literal></entry> + </row> + <row> + <entry><literal>(ddd) mmm. dd, yyyy</literal></entry> + <entry><literal>(Mon) Nov. 23, 1959</literal></entry> + </row> + </tbody> + </tgroup> + </table> + </listitem> + </varlistentry> + + <varlistentry id="pgtypesdatedefmtasc"> + <term><function>PGTYPESdate_defmt_asc</function></term> + <listitem> + <para> + Use a format mask to convert a C <type>char*</type> string to a value of type + date. +<synopsis> +int PGTYPESdate_defmt_asc(date *d, char *fmt, char *str); +</synopsis> + <!-- same description as rdefmtdate --> + The function receives a pointer to the date value that should hold the + result of the operation (<literal>d</literal>), the format mask to use for + parsing the date (<literal>fmt</literal>) and the C char* string containing + the textual representation of the date (<literal>str</literal>). The textual + representation is expected to match the format mask. However you do not + need to have a 1:1 mapping of the string to the format mask. The + function only analyzes the sequential order and looks for the literals + <literal>yy</literal> or <literal>yyyy</literal> that indicate the + position of the year, <literal>mm</literal> to indicate the position of + the month and <literal>dd</literal> to indicate the position of the + day. + </para> + <para> + <xref linkend="ecpg-rdefmtdate-example-table"/> indicates a few possible formats. This will give + you an idea of how to use this function. + </para> + <table id="ecpg-rdefmtdate-example-table"> + <title>Valid Input Formats for <function>rdefmtdate</function></title> + <tgroup cols="3"> + <thead> + <row> + <entry>Format</entry> + <entry>String</entry> + <entry>Result</entry> + </row> + </thead> + <tbody> + <row> + <entry><literal>ddmmyy</literal></entry> + <entry><literal>21-2-54</literal></entry> + <entry><literal>1954-02-21</literal></entry> + </row> + <row> + <entry><literal>ddmmyy</literal></entry> + <entry><literal>2-12-54</literal></entry> + <entry><literal>1954-12-02</literal></entry> + </row> + <row> + <entry><literal>ddmmyy</literal></entry> + <entry><literal>20111954</literal></entry> + <entry><literal>1954-11-20</literal></entry> + </row> + <row> + <entry><literal>ddmmyy</literal></entry> + <entry><literal>130464</literal></entry> + <entry><literal>1964-04-13</literal></entry> + </row> + <row> + <entry><literal>mmm.dd.yyyy</literal></entry> + <entry><literal>MAR-12-1967</literal></entry> + <entry><literal>1967-03-12</literal></entry> + </row> + <row> + <entry><literal>yy/mm/dd</literal></entry> + <entry><literal>1954, February 3rd</literal></entry> + <entry><literal>1954-02-03</literal></entry> + </row> + <row> + <entry><literal>mmm.dd.yyyy</literal></entry> + <entry><literal>041269</literal></entry> + <entry><literal>1969-04-12</literal></entry> + </row> + <row> + <entry><literal>yy/mm/dd</literal></entry> + <entry><literal>In the year 2525, in the month of July, mankind will be alive on the 28th day</literal></entry> + <entry><literal>2525-07-28</literal></entry> + </row> + <row> + <entry><literal>dd-mm-yy</literal></entry> + <entry><literal>I said on the 28th of July in the year 2525</literal></entry> + <entry><literal>2525-07-28</literal></entry> + </row> + <row> + <entry><literal>mmm.dd.yyyy</literal></entry> + <entry><literal>9/14/58</literal></entry> + <entry><literal>1958-09-14</literal></entry> + </row> + <row> + <entry><literal>yy/mm/dd</literal></entry> + <entry><literal>47/03/29</literal></entry> + <entry><literal>1947-03-29</literal></entry> + </row> + <row> + <entry><literal>mmm.dd.yyyy</literal></entry> + <entry><literal>oct 28 1975</literal></entry> + <entry><literal>1975-10-28</literal></entry> + </row> + <row> + <entry><literal>mmddyy</literal></entry> + <entry><literal>Nov 14th, 1985</literal></entry> + <entry><literal>1985-11-14</literal></entry> + </row> + </tbody> + </tgroup> + </table> + </listitem> + </varlistentry> + </variablelist> + </para> + </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 + type timestamp. See <xref linkend="datatype-datetime"/> for the equivalent + type in the <productname>PostgreSQL</productname> server. + </para> + <para> + The following functions can be used to work with the timestamp type: + <variablelist> + <varlistentry id="pgtypestimestampfromasc"> + <term><function>PGTYPEStimestamp_from_asc</function></term> + <listitem> + <para> + Parse a timestamp from its textual representation into a timestamp + variable. +<synopsis> +timestamp PGTYPEStimestamp_from_asc(char *str, char **endptr); +</synopsis> + The function receives the string to parse (<literal>str</literal>) and a + pointer to a C char* (<literal>endptr</literal>). + At the moment ECPG always parses + the complete string and so it currently does not support to store the + address of the first invalid character in <literal>*endptr</literal>. + You can safely set <literal>endptr</literal> to NULL. + </para> + <para> + The function returns the parsed timestamp on success. On error, + <literal>PGTYPESInvalidTimestamp</literal> is returned and <varname>errno</varname> is + set to <literal>PGTYPES_TS_BAD_TIMESTAMP</literal>. See <xref linkend="pgtypesinvalidtimestamp"/> for important notes on this value. + </para> + <para> + In general, the input string can contain any combination of an allowed + date specification, a whitespace character and an allowed time + specification. Note that time zones are not supported by ECPG. It can + parse them but does not apply any calculation as the + <productname>PostgreSQL</productname> server does for example. Timezone + specifiers are silently discarded. + </para> + <para> + <xref linkend="ecpg-pgtypestimestamp-from-asc-example-table"/> contains a few examples for input strings. + </para> + <table id="ecpg-pgtypestimestamp-from-asc-example-table"> + <title>Valid Input Formats for <function>PGTYPEStimestamp_from_asc</function></title> + <tgroup cols="2"> + <thead> + <row> + <entry>Input</entry> + <entry>Result</entry> + </row> + </thead> + <tbody> + <row> + <entry><literal>1999-01-08 04:05:06</literal></entry> + <entry><literal>1999-01-08 04:05:06</literal></entry> + </row> + <row> + <entry><literal>January 8 04:05:06 1999 PST</literal></entry> + <entry><literal>1999-01-08 04:05:06</literal></entry> + </row> + <row> + <entry><literal>1999-Jan-08 04:05:06.789-8</literal></entry> + <entry><literal>1999-01-08 04:05:06.789 (time zone specifier ignored)</literal></entry> + </row> + <row> + <entry><literal>J2451187 04:05-08:00</literal></entry> + <entry><literal>1999-01-08 04:05:00 (time zone specifier ignored)</literal></entry> + </row> + </tbody> + </tgroup> + </table> + </listitem> + </varlistentry> + + <varlistentry id="pgtypestimestamptoasc"> + <term><function>PGTYPEStimestamp_to_asc</function></term> + <listitem> + <para> + Converts a date to a C char* string. +<synopsis> +char *PGTYPEStimestamp_to_asc(timestamp tstamp); +</synopsis> + The function receives the timestamp <literal>tstamp</literal> as + its only argument and returns an allocated string that contains the + textual representation of the timestamp. + The result must be freed with <function>PGTYPESchar_free()</function>. + </para> + </listitem> + </varlistentry> + + <varlistentry id="pgtypestimestampcurrent"> + <term><function>PGTYPEStimestamp_current</function></term> + <listitem> + <para> + Retrieve the current timestamp. +<synopsis> +void PGTYPEStimestamp_current(timestamp *ts); +</synopsis> + The function retrieves the current timestamp and saves it into the + timestamp variable that <literal>ts</literal> points to. + </para> + </listitem> + </varlistentry> + + <varlistentry id="pgtypestimestampfmtasc"> + <term><function>PGTYPEStimestamp_fmt_asc</function></term> + <listitem> + <para> + Convert a timestamp variable to a C char* using a format mask. +<synopsis> +int PGTYPEStimestamp_fmt_asc(timestamp *ts, char *output, int str_len, char *fmtstr); +</synopsis> + The function receives a pointer to the timestamp to convert as its + first argument (<literal>ts</literal>), a pointer to the output buffer + (<literal>output</literal>), the maximal length that has been allocated for + the output buffer (<literal>str_len</literal>) and the format mask to + use for the conversion (<literal>fmtstr</literal>). + </para> + <para> + Upon success, the function returns 0 and a negative value if an + error occurred. + </para> + <para> + You can use the following format specifiers for the format mask. The + format specifiers are the same ones that are used in the + <function>strftime</function> function in <productname>libc</productname>. Any + non-format specifier will be copied into the output buffer. + <!-- This is from the FreeBSD man page: + http://www.freebsd.org/cgi/man.cgi?query=strftime&apropos=0&sektion=3&manpath=FreeBSD+7.0-current&format=html + --> + <itemizedlist> + <listitem> + <para> + <literal>%A</literal> - is replaced by national representation of + the full weekday name. + </para> + </listitem> + <listitem> + <para> + <literal>%a</literal> - is replaced by national representation of + the abbreviated weekday name. + </para> + </listitem> + <listitem> + <para> + <literal>%B</literal> - is replaced by national representation of + the full month name. + </para> + </listitem> + <listitem> + <para> + <literal>%b</literal> - is replaced by national representation of + the abbreviated month name. + </para> + </listitem> + <listitem> + <para> + <literal>%C</literal> - is replaced by (year / 100) as decimal + number; single digits are preceded by a zero. + </para> + </listitem> + <listitem> + <para> + <literal>%c</literal> - is replaced by national representation of + time and date. + </para> + </listitem> + <listitem> + <para> + <literal>%D</literal> - is equivalent to + <literal>%m/%d/%y</literal>. + </para> + </listitem> + <listitem> + <para> + <literal>%d</literal> - is replaced by the day of the month as a + decimal number (01–31). + </para> + </listitem> + <listitem> + <para> + <literal>%E*</literal> <literal>%O*</literal> - POSIX locale + extensions. The sequences + <literal>%Ec</literal> + <literal>%EC</literal> + <literal>%Ex</literal> + <literal>%EX</literal> + <literal>%Ey</literal> + <literal>%EY</literal> + <literal>%Od</literal> + <literal>%Oe</literal> + <literal>%OH</literal> + <literal>%OI</literal> + <literal>%Om</literal> + <literal>%OM</literal> + <literal>%OS</literal> + <literal>%Ou</literal> + <literal>%OU</literal> + <literal>%OV</literal> + <literal>%Ow</literal> + <literal>%OW</literal> + <literal>%Oy</literal> + are supposed to provide alternative representations. + </para> + <para> + Additionally <literal>%OB</literal> implemented to represent + alternative months names (used standalone, without day mentioned). + </para> + </listitem> + <listitem> + <para> + <literal>%e</literal> - is replaced by the day of month as a decimal + number (1–31); single digits are preceded by a blank. + </para> + </listitem> + <listitem> + <para> + <literal>%F</literal> - is equivalent to <literal>%Y-%m-%d</literal>. + </para> + </listitem> + <listitem> + <para> + <literal>%G</literal> - is replaced by a year as a decimal number + with century. This year is the one that contains the greater part of + the week (Monday as the first day of the week). + </para> + </listitem> + <listitem> + <para> + <literal>%g</literal> - is replaced by the same year as in + <literal>%G</literal>, but as a decimal number without century + (00–99). + </para> + </listitem> + <listitem> + <para> + <literal>%H</literal> - is replaced by the hour (24-hour clock) as a + decimal number (00–23). + </para> + </listitem> + <listitem> + <para> + <literal>%h</literal> - the same as <literal>%b</literal>. + </para> + </listitem> + <listitem> + <para> + <literal>%I</literal> - is replaced by the hour (12-hour clock) as a + decimal number (01–12). + </para> + </listitem> + <listitem> + <para> + <literal>%j</literal> - is replaced by the day of the year as a + decimal number (001–366). + </para> + </listitem> + <listitem> + <para> + <literal>%k</literal> - is replaced by the hour (24-hour clock) as a + decimal number (0–23); single digits are preceded by a blank. + </para> + </listitem> + <listitem> + <para> + <literal>%l</literal> - is replaced by the hour (12-hour clock) as a + decimal number (1–12); single digits are preceded by a blank. + </para> + </listitem> + <listitem> + <para> + <literal>%M</literal> - is replaced by the minute as a decimal + number (00–59). + </para> + </listitem> + <listitem> + <para> + <literal>%m</literal> - is replaced by the month as a decimal number + (01–12). + </para> + </listitem> + <listitem> + <para> + <literal>%n</literal> - is replaced by a newline. + </para> + </listitem> + <listitem> + <para> + <literal>%O*</literal> - the same as <literal>%E*</literal>. + </para> + </listitem> + <listitem> + <para> + <literal>%p</literal> - is replaced by national representation of + either <quote>ante meridiem</quote> or <quote>post meridiem</quote> as appropriate. + </para> + </listitem> + <listitem> + <para> + <literal>%R</literal> - is equivalent to <literal>%H:%M</literal>. + </para> + </listitem> + <listitem> + <para> + <literal>%r</literal> - is equivalent to <literal>%I:%M:%S + %p</literal>. + </para> + </listitem> + <listitem> + <para> + <literal>%S</literal> - is replaced by the second as a decimal + number (00–60). + </para> + </listitem> + <listitem> + <para> + <literal>%s</literal> - is replaced by the number of seconds since + the Epoch, UTC. + </para> + </listitem> + <listitem> + <para> + <literal>%T</literal> - is equivalent to <literal>%H:%M:%S</literal> + </para> + </listitem> + <listitem> + <para> + <literal>%t</literal> - is replaced by a tab. + </para> + </listitem> + <listitem> + <para> + <literal>%U</literal> - is replaced by the week number of the year + (Sunday as the first day of the week) as a decimal number (00–53). + </para> + </listitem> + <listitem> + <para> + <literal>%u</literal> - is replaced by the weekday (Monday as the + first day of the week) as a decimal number (1–7). + </para> + </listitem> + <listitem> + <para> + <literal>%V</literal> - is replaced by the week number of the year + (Monday as the first day of the week) as a decimal number (01–53). + If the week containing January 1 has four or more days in the new + year, then it is week 1; otherwise it is the last week of the + previous year, and the next week is week 1. + </para> + </listitem> + <listitem> + <para> + <literal>%v</literal> - is equivalent to + <literal>%e-%b-%Y</literal>. + </para> + </listitem> + <listitem> + <para> + <literal>%W</literal> - is replaced by the week number of the year + (Monday as the first day of the week) as a decimal number (00–53). + </para> + </listitem> + <listitem> + <para> + <literal>%w</literal> - is replaced by the weekday (Sunday as the + first day of the week) as a decimal number (0–6). + </para> + </listitem> + <listitem> + <para> + <literal>%X</literal> - is replaced by national representation of + the time. + </para> + </listitem> + <listitem> + <para> + <literal>%x</literal> - is replaced by national representation of + the date. + </para> + </listitem> + <listitem> + <para> + <literal>%Y</literal> - is replaced by the year with century as a + decimal number. + </para> + </listitem> + <listitem> + <para> + <literal>%y</literal> - is replaced by the year without century as a + decimal number (00–99). + </para> + </listitem> + <listitem> + <para> + <literal>%Z</literal> - is replaced by the time zone name. + </para> + </listitem> + <listitem> + <para> + <literal>%z</literal> - is replaced by the time zone offset from + UTC; a leading plus sign stands for east of UTC, a minus sign for + west of UTC, hours and minutes follow with two digits each and no + delimiter between them (common form for <ulink url="https://tools.ietf.org/html/rfc822">RFC 822</ulink> date headers). + </para> + </listitem> + <listitem> + <para> + <literal>%+</literal> - is replaced by national representation of + the date and time. + </para> + </listitem> + <listitem> + <para> + <literal>%-*</literal> - GNU libc extension. Do not do any padding + when performing numerical outputs. + </para> + </listitem> + <listitem> + <para> + $_* - GNU libc extension. Explicitly specify space for padding. + </para> + </listitem> + <listitem> + <para> + <literal>%0*</literal> - GNU libc extension. Explicitly specify zero + for padding. + </para> + </listitem> + <listitem> + <para> + <literal>%%</literal> - is replaced by <literal>%</literal>. + </para> + </listitem> + </itemizedlist> + </para> + </listitem> + </varlistentry> + + <varlistentry id="pgtypestimestampsub"> + <term><function>PGTYPEStimestamp_sub</function></term> + <listitem> + <para> + Subtract one timestamp from another one and save the result in a + variable of type interval. +<synopsis> +int PGTYPEStimestamp_sub(timestamp *ts1, timestamp *ts2, interval *iv); +</synopsis> + The function will subtract the timestamp variable that <literal>ts2</literal> + points to from the timestamp variable that <literal>ts1</literal> points to + and will store the result in the interval variable that <literal>iv</literal> + points to. + </para> + <para> + Upon success, the function returns 0 and a negative value if an + error occurred. + </para> + </listitem> + </varlistentry> + + <varlistentry id="pgtypestimestampdefmtasc"> + <term><function>PGTYPEStimestamp_defmt_asc</function></term> + <listitem> + <para> + Parse a timestamp value from its textual representation using a + formatting mask. +<synopsis> +int PGTYPEStimestamp_defmt_asc(char *str, char *fmt, timestamp *d); +</synopsis> + The function receives the textual representation of a timestamp in the + variable <literal>str</literal> as well as the formatting mask to use in the + variable <literal>fmt</literal>. The result will be stored in the variable + that <literal>d</literal> points to. + </para> + <para> + If the formatting mask <literal>fmt</literal> is NULL, the function will fall + back to the default formatting mask which is <literal>%Y-%m-%d + %H:%M:%S</literal>. + </para> + <para> + This is the reverse function to <xref + linkend="pgtypestimestampfmtasc"/>. See the documentation there in + order to find out about the possible formatting mask entries. + </para> + </listitem> + </varlistentry> + + <varlistentry id="pgtypestimestampaddinterval"> + <term><function>PGTYPEStimestamp_add_interval</function></term> + <listitem> + <para> + Add an interval variable to a timestamp variable. +<synopsis> +int PGTYPEStimestamp_add_interval(timestamp *tin, interval *span, timestamp *tout); +</synopsis> + The function receives a pointer to a timestamp variable <literal>tin</literal> + and a pointer to an interval variable <literal>span</literal>. It adds the + interval to the timestamp and saves the resulting timestamp in the + variable that <literal>tout</literal> points to. + </para> + <para> + Upon success, the function returns 0 and a negative value if an + error occurred. + </para> + </listitem> + </varlistentry> + + <varlistentry id="pgtypestimestampsubinterval"> + <term><function>PGTYPEStimestamp_sub_interval</function></term> + <listitem> + <para> + Subtract an interval variable from a timestamp variable. +<synopsis> +int PGTYPEStimestamp_sub_interval(timestamp *tin, interval *span, timestamp *tout); +</synopsis> + The function subtracts the interval variable that <literal>span</literal> + points to from the timestamp variable that <literal>tin</literal> points to + and saves the result into the variable that <literal>tout</literal> points + to. + </para> + <para> + Upon success, the function returns 0 and a negative value if an + error occurred. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </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 + type interval. See <xref linkend="datatype-datetime"/> for the equivalent + type in the <productname>PostgreSQL</productname> server. + </para> + <para> + The following functions can be used to work with the interval type: + <variablelist> + + <varlistentry id="pgtypesintervalnew"> + <term><function>PGTYPESinterval_new</function></term> + <listitem> + <para> + Return a pointer to a newly allocated interval variable. +<synopsis> +interval *PGTYPESinterval_new(void); +</synopsis> + </para> + </listitem> + </varlistentry> + + <varlistentry id="pgtypesintervalfree"> + <term><function>PGTYPESinterval_free</function></term> + <listitem> + <para> + Release the memory of a previously allocated interval variable. +<synopsis> +void PGTYPESinterval_free(interval *intvl); +</synopsis> + </para> + </listitem> + </varlistentry> + + <varlistentry id="pgtypesintervalfromasc"> + <term><function>PGTYPESinterval_from_asc</function></term> + <listitem> + <para> + Parse an interval from its textual representation. +<synopsis> +interval *PGTYPESinterval_from_asc(char *str, char **endptr); +</synopsis> + The function parses the input string <literal>str</literal> and returns a + pointer to an allocated interval variable. + At the moment ECPG always parses + the complete string and so it currently does not support to store the + address of the first invalid character in <literal>*endptr</literal>. + You can safely set <literal>endptr</literal> to NULL. + </para> + </listitem> + </varlistentry> + + <varlistentry id="pgtypesintervaltoasc"> + <term><function>PGTYPESinterval_to_asc</function></term> + <listitem> + <para> + Convert a variable of type interval to its textual representation. +<synopsis> +char *PGTYPESinterval_to_asc(interval *span); +</synopsis> + The function converts the interval variable that <literal>span</literal> + points to into a C char*. The output looks like this example: + <literal>@ 1 day 12 hours 59 mins 10 secs</literal>. + The result must be freed with <function>PGTYPESchar_free()</function>. + </para> + </listitem> + </varlistentry> + + <varlistentry id="pgtypesintervalcopy"> + <term><function>PGTYPESinterval_copy</function></term> + <listitem> + <para> + Copy a variable of type interval. +<synopsis> +int PGTYPESinterval_copy(interval *intvlsrc, interval *intvldest); +</synopsis> + The function copies the interval variable that <literal>intvlsrc</literal> + points to into the variable that <literal>intvldest</literal> points to. Note + that you need to allocate the memory for the destination variable + before. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </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 + a maximum precision of 30 significant digits. In contrast to the numeric + type which can be created on the heap only, the decimal type can be + created either on the stack or on the heap (by means of the functions + <function>PGTYPESdecimal_new</function> and + <function>PGTYPESdecimal_free</function>). + There are a lot of other functions that deal with the decimal type in the + <productname>Informix</productname> compatibility mode described in <xref + linkend="ecpg-informix-compat"/>. + </para> + <para> + The following functions can be used to work with the decimal type and are + not only contained in the <literal>libcompat</literal> library. + <variablelist> + <varlistentry> + <term><function>PGTYPESdecimal_new</function></term> + <listitem> + <para> + Request a pointer to a newly allocated decimal variable. +<synopsis> +decimal *PGTYPESdecimal_new(void); +</synopsis> + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>PGTYPESdecimal_free</function></term> + <listitem> + <para> + Free a decimal type, release all of its memory. +<synopsis> +void PGTYPESdecimal_free(decimal *var); +</synopsis> + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </sect2> + + <sect2 id="ecpg-pgtypes-errno"> + <title>errno Values of pgtypeslib</title> + <para> + <variablelist> + <varlistentry> + <term><literal>PGTYPES_NUM_BAD_NUMERIC</literal></term> + <listitem> + <para> + An argument should contain a numeric variable (or point to a numeric + variable) but in fact its in-memory representation was invalid. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>PGTYPES_NUM_OVERFLOW</literal></term> + <listitem> + <para> + An overflow occurred. Since the numeric type can deal with almost + arbitrary precision, converting a numeric variable into other types + might cause overflow. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>PGTYPES_NUM_UNDERFLOW</literal></term> + <listitem> + <para> + An underflow occurred. Since the numeric type can deal with almost + arbitrary precision, converting a numeric variable into other types + might cause underflow. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>PGTYPES_NUM_DIVIDE_ZERO</literal></term> + <listitem> + <para> + A division by zero has been attempted. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <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> + + <varlistentry> + <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> + + <varlistentry> + <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> + + <varlistentry> + <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> + + <varlistentry> + <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> + + <varlistentry> + <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> + + <varlistentry> + <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> + + <varlistentry> + <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> + </variablelist> + </para> + </sect2> + + <sect2 id="ecpg-pgtypes-constants"> + <title>Special Constants of pgtypeslib</title> + <para> + <variablelist> + <varlistentry id="pgtypesinvalidtimestamp"> + <term><literal>PGTYPESInvalidTimestamp</literal></term> + <listitem> + <para> + A value of type timestamp representing an invalid time stamp. This is + returned by the function <function>PGTYPEStimestamp_from_asc</function> on + parse error. + Note that due to the internal representation of the <type>timestamp</type> data type, + <literal>PGTYPESInvalidTimestamp</literal> is also a valid timestamp at + the same time. It is set to <literal>1899-12-31 23:59:59</literal>. In order + to detect errors, make sure that your application does not only test + for <literal>PGTYPESInvalidTimestamp</literal> but also for + <literal>errno != 0</literal> after each call to + <function>PGTYPEStimestamp_from_asc</function>. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </sect2> + </sect1> + + <sect1 id="ecpg-descriptors"> + <title>Using Descriptor Areas</title> + + <para> + An SQL descriptor area is a more sophisticated method for processing + the result of a <command>SELECT</command>, <command>FETCH</command> or + a <command>DESCRIBE</command> statement. An SQL descriptor area groups + the data of one row of data together with metadata items into one + data structure. The metadata is particularly useful when executing + dynamic SQL statements, where the nature of the result columns might + not be known ahead of time. PostgreSQL provides two ways to use + Descriptor Areas: the named SQL Descriptor Areas and the C-structure + SQLDAs. + </para> + + <sect2 id="ecpg-named-descriptors"> + <title>Named SQL Descriptor Areas</title> + + <para> + A named SQL descriptor area consists of a header, which contains + information concerning the entire descriptor, and one or more item + descriptor areas, which basically each describe one column in the + result row. + </para> + + <para> + Before you can use an SQL descriptor area, you need to allocate one: +<programlisting> +EXEC SQL ALLOCATE DESCRIPTOR <replaceable>identifier</replaceable>; +</programlisting> + The identifier serves as the <quote>variable name</quote> of the + descriptor area. <!-- The scope of the allocated descriptor is WHAT?. --> + When you don't need the descriptor anymore, you should deallocate + it: +<programlisting> +EXEC SQL DEALLOCATE DESCRIPTOR <replaceable>identifier</replaceable>; +</programlisting> + </para> + + <para> + To use a descriptor area, specify it as the storage target in an + <literal>INTO</literal> clause, instead of listing host variables: +<programlisting> +EXEC SQL FETCH NEXT FROM mycursor INTO SQL DESCRIPTOR mydesc; +</programlisting> + If the result set is empty, the Descriptor Area will still contain + the metadata from the query, i.e., the field names. + </para> + + <para> + For not yet executed prepared queries, the <command>DESCRIBE</command> + statement can be used to get the metadata of the result set: +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; +char *sql_stmt = "SELECT * FROM table1"; +EXEC SQL END DECLARE SECTION; + +EXEC SQL PREPARE stmt1 FROM :sql_stmt; +EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc; +</programlisting> + </para> + + <para> + Before PostgreSQL 9.0, the <literal>SQL</literal> keyword was optional, + 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"/>. + </para> + + <para> + In <command>DESCRIBE</command> and <command>FETCH</command> statements, + the <literal>INTO</literal> and <literal>USING</literal> keywords can be + used to similarly: they produce the result set and the metadata in a + Descriptor Area. + </para> + + <para> + Now how do you get the data out of the descriptor area? You can + think of the descriptor area as a structure with named fields. To + retrieve the value of a field from the header and store it into a + host variable, use the following command: +<programlisting> +EXEC SQL GET DESCRIPTOR <replaceable>name</replaceable> :<replaceable>hostvar</replaceable> = <replaceable>field</replaceable>; +</programlisting> + Currently, there is only one header field defined: + <replaceable>COUNT</replaceable>, which tells how many item + descriptor areas exist (that is, how many columns are contained in + the result). The host variable needs to be of an integer type. To + get a field from the item descriptor area, use the following + command: +<programlisting> +EXEC SQL GET DESCRIPTOR <replaceable>name</replaceable> VALUE <replaceable>num</replaceable> :<replaceable>hostvar</replaceable> = <replaceable>field</replaceable>; +</programlisting> + <replaceable>num</replaceable> can be a literal integer or a host + variable containing an integer. Possible fields are: + + <variablelist> + <varlistentry> + <term><literal>CARDINALITY</literal> (integer)</term> + <listitem> + <para> + number of rows in the result set + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>DATA</literal></term> + <listitem> + <para> + actual data item (therefore, the data type of this field + depends on the query) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <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> + + <varlistentry> + <term><literal>DATETIME_INTERVAL_PRECISION</literal> (integer)</term> + <listitem> + <para> + not implemented + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>INDICATOR</literal> (integer)</term> + <listitem> + <para> + the indicator (indicating a null value or a value truncation) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>KEY_MEMBER</literal> (integer)</term> + <listitem> + <para> + not implemented + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>LENGTH</literal> (integer)</term> + <listitem> + <para> + length of the datum in characters + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>NAME</literal> (string)</term> + <listitem> + <para> + name of the column + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>NULLABLE</literal> (integer)</term> + <listitem> + <para> + not implemented + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>OCTET_LENGTH</literal> (integer)</term> + <listitem> + <para> + length of the character representation of the datum in bytes + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>PRECISION</literal> (integer)</term> + <listitem> + <para> + precision (for type <type>numeric</type>) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>RETURNED_LENGTH</literal> (integer)</term> + <listitem> + <para> + length of the datum in characters + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>RETURNED_OCTET_LENGTH</literal> (integer)</term> + <listitem> + <para> + length of the character representation of the datum in bytes + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>SCALE</literal> (integer)</term> + <listitem> + <para> + scale (for type <type>numeric</type>) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>TYPE</literal> (integer)</term> + <listitem> + <para> + numeric code of the data type of the column + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + + <para> + In <command>EXECUTE</command>, <command>DECLARE</command> and <command>OPEN</command> + statements, the effect of the <literal>INTO</literal> and <literal>USING</literal> + keywords are different. A Descriptor Area can also be manually built to + provide the input parameters for a query or a cursor and + <literal>USING SQL DESCRIPTOR <replaceable>name</replaceable></literal> + is the way to pass the input parameters into a parameterized query. The statement + to build a named SQL Descriptor Area is below: +<programlisting> +EXEC SQL SET DESCRIPTOR <replaceable>name</replaceable> VALUE <replaceable>num</replaceable> <replaceable>field</replaceable> = :<replaceable>hostvar</replaceable>; +</programlisting> + </para> + + <para> + PostgreSQL supports retrieving more that one record in one <command>FETCH</command> + statement and storing the data in host variables in this case assumes that the + variable is an array. E.g.: +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; +int id[5]; +EXEC SQL END DECLARE SECTION; + +EXEC SQL FETCH 5 FROM mycursor INTO SQL DESCRIPTOR mydesc; + +EXEC SQL GET DESCRIPTOR mydesc VALUE 1 :id = DATA; +</programlisting> + + </para> + + </sect2> + + <sect2 id="ecpg-sqlda-descriptors"> + <title>SQLDA Descriptor Areas</title> + + <para> + An SQLDA Descriptor Area is a C language structure which can be also used + to get the result set and the metadata of a query. One structure stores one + record from the result set. +<programlisting> +EXEC SQL include sqlda.h; +sqlda_t *mysqlda; + +EXEC SQL FETCH 3 FROM mycursor INTO DESCRIPTOR mysqlda; +</programlisting> + Note that the <literal>SQL</literal> keyword is omitted. The paragraphs about + the use cases of the <literal>INTO</literal> and <literal>USING</literal> + keywords in <xref linkend="ecpg-named-descriptors"/> also apply here with an addition. + In a <command>DESCRIBE</command> statement the <literal>DESCRIPTOR</literal> + keyword can be completely omitted if the <literal>INTO</literal> keyword is used: +<programlisting> +EXEC SQL DESCRIBE prepared_statement INTO mysqlda; +</programlisting> + </para> + + <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> + + <para> + SQLDA uses three data structure + types: <type>sqlda_t</type>, <type>sqlvar_t</type>, + and <type>struct sqlname</type>. + </para> + + <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]; +}; + +typedef struct sqlda_struct sqlda_t; +</programlisting> + + The meaning of the fields is: + + <variablelist> + <varlistentry> + <term><literal>sqldaid</literal></term> + <listitem> + <para> + It contains the literal string <literal>"SQLDA "</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>sqldabc</literal></term> + <listitem> + <para> + It contains the size of the allocated space in bytes. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>sqln</literal></term> + <listitem> + <para> + It contains the number of input parameters for a parameterized query in + case it's passed into <command>OPEN</command>, <command>DECLARE</command> or + <command>EXECUTE</command> statements using the <literal>USING</literal> + keyword. In case it's used as output of <command>SELECT</command>, + <command>EXECUTE</command> or <command>FETCH</command> statements, + its value is the same as <literal>sqld</literal> + statement + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>sqld</literal></term> + <listitem> + <para> + It contains the number of fields in a result set. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>desc_next</literal></term> + <listitem> + <para> + If the query returns more than one record, multiple linked + SQLDA structures are returned, and <literal>desc_next</literal> holds + a pointer to the next entry in the list. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term><literal>sqlvar</literal></term> + <listitem> + <para> + This is the array of the columns in the result set. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </sect4> + + <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</literal></term> + <listitem> + <para> + Contains the type identifier of the field. For values, + see <literal>enum ECPGttype</literal> in <literal>ecpgtype.h</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>sqllen</literal></term> + <listitem> + <para> + Contains the binary length of the field. e.g., 4 bytes for <type>ECPGt_int</type>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>sqldata</literal></term> + <listitem> + <para> + Points to the data. The format of the data is described + in <xref linkend="ecpg-variables-type-mapping"/>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>sqlind</literal></term> + <listitem> + <para> + Points to the null indicator. 0 means not null, -1 means + null. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>sqlname</literal></term> + <listitem> + <para> + 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</literal></term> + <listitem> + <para> + Contains the length of the field name. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term><literal>data</literal></term> + <listitem> + <para> + Contains the actual field name. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </sect4> + </sect3> + + <sect3 id="ecpg-sqlda-output"> + <title>Retrieving 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>/<command>EXECUTE</command>/<command>DESCRIBE</command> 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</structfield>, a member of the <type>sqlda_t</type> structure.</simpara></step> + <step><simpara>Get the values of each column from <literal>sqlvar[0]</literal>, <literal>sqlvar[1]</literal>, 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</structfield> 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> 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</structfield> 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</structfield> 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 an 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</structfield>, 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 retrieved by two input + parameters (a database <literal>postgres</literal>, and OID <literal>1</literal>). + </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 SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + + 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</structfield>, the actual data of the first + column is stored in <literal>sqlvar[0]</literal>, 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</varname>. + Copy every datum into host variables, looking + at <literal>v.sqltype</literal> 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 SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + + EXEC SQL PREPARE stmt1 FROM :query; + EXEC SQL DECLARE cur1 CURSOR FOR stmt1; + + /* Create an 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) +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) +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>DO CONTINUE</literal></term> + <listitem> + <para> + Execute the C statement <literal>continue</literal>. This should + only be used in loops statements. if executed, will cause the flow + of control to return to the top of the loop. + </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. (This + use is different from the meaning of <literal>CALL</literal> + and <literal>DO</literal> in the normal PostgreSQL grammar.) + </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>sqlabc</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</literal> + 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'</literal> nor + <literal>'f'</literal>. (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> + + <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> + + <!-- 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</replaceable>; +EXEC SQL DEFINE <replaceable>name</replaceable> <replaceable>value</replaceable>; +</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</literal> 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</literal> then the <command>ecpg</command> 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</command> will already do the substitution and your C compiler will never + see any name or identifier <literal>MYNUMBER</literal>. 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, elif, else, 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</replaceable>;</literal></term> + <listitem> + <para> + Checks a <replaceable>name</replaceable> and processes subsequent lines if + <replaceable>name</replaceable> has been defined via <literal>EXEC SQL define + <replaceable>name</replaceable></literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>EXEC SQL ifndef <replaceable>name</replaceable>;</literal></term> + <listitem> + <para> + Checks a <replaceable>name</replaceable> and processes subsequent lines if + <replaceable>name</replaceable> has <emphasis>not</emphasis> been defined via + <literal>EXEC SQL define <replaceable>name</replaceable></literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>EXEC SQL elif <replaceable>name</replaceable>;</literal></term> + <listitem> + <para> + Begins an optional alternative section after an + <literal>EXEC SQL ifdef <replaceable>name</replaceable></literal> or + <literal>EXEC SQL ifndef <replaceable>name</replaceable></literal> + directive. Any number of <literal>elif</literal> sections can appear. + Lines following an <literal>elif</literal> will be processed + if <replaceable>name</replaceable> has been + defined <emphasis>and</emphasis> no previous section of the same + <literal>ifdef</literal>/<literal>ifndef</literal>...<literal>endif</literal> + construct has been processed. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>EXEC SQL else;</literal></term> + <listitem> + <para> + Begins an optional, final alternative section after an + <literal>EXEC SQL ifdef <replaceable>name</replaceable></literal> or + <literal>EXEC SQL ifndef <replaceable>name</replaceable></literal> + directive. Subsequent lines will be processed if no previous section + of the same + <literal>ifdef</literal>/<literal>ifndef</literal>...<literal>endif</literal> + construct has been processed. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>EXEC SQL endif;</literal></term> + <listitem> + <para> + Ends an + <literal>ifdef</literal>/<literal>ifndef</literal>...<literal>endif</literal> + construct. Subsequent lines are processed normally. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + + <para> + <literal>ifdef</literal>/<literal>ifndef</literal>...<literal>endif</literal> + constructs can be nested, up to 127 levels deep. + </para> + + <para> + This example will compile exactly one of the three <literal>SET + TIMEZONE</literal> commands: +<programlisting> +EXEC SQL ifdef TZVAR; +EXEC SQL SET TIMEZONE TO TZVAR; +EXEC SQL elif TZNAME; +EXEC SQL SET TIMEZONE TO TZNAME; +EXEC SQL else; +EXEC SQL SET TIMEZONE TO 'GMT'; +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</productname> 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</productname> installation, so if you installed + <productname>PostgreSQL</productname> 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> + You can + use <command>pg_config</command><indexterm><primary>pg_config</primary><secondary sortas="ecpg">with + ecpg</secondary></indexterm> + or <command>pkg-config</command><indexterm><primary>pkg-config</primary><secondary sortas="ecpg">with + ecpg</secondary></indexterm> with package name <literal>libecpg</literal> to + get the paths for your installation. + </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</application> libraries and an application are + compiled with different flags, this function call will crash the + application because the internal representation of the + <literal>FILE</literal> 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 <xref linkend="libpq-PQtransactionStatus"/> 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</literal> + 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 issued 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; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + + 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; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; +} + +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; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; +} + +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</filename> into <filename>test_mod.c</filename> by + running <command>ecpg</command>, and generate + <filename>test_mod.o</filename> by compiling + <filename>test_mod.c</filename> 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</filename> by compiling + <filename>test_cpp.cpp</filename> 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</filename> + and <filename>test_mod.o</filename>, 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</replaceable> ] +CONNECT TO DEFAULT +CONNECT <replaceable>connection_user</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_name</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> + </refsect1> + + <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 pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + EXEC SQL SELECT version() INTO :ver; + EXEC SQL DISCONNECT; + + printf("version: %s\n", ver); + + EXEC SQL CONNECT TO :connection USER :user; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + 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 ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">prepared_name</replaceable> +DECLARE <replaceable class="parameter">cursor_name</replaceable> [ BINARY ] [ ASENSITIVE | 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 identifier 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-declare-statement"> + <refnamediv> + <refname>DECLARE STATEMENT</refname> + <refpurpose>declare SQL statement identifier</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +EXEC SQL [ AT <replaceable class="parameter">connection_name</replaceable> ] DECLARE <replaceable class="parameter">statement_name</replaceable> STATEMENT +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>DECLARE STATEMENT</command> declares an SQL statement identifier. + SQL statement identifier can be associated with the connection. + When the identifier is used by dynamic SQL statements, the statements + are executed using the associated connection. + The namespace of the declaration is the precompile unit, and multiple + declarations to the same SQL statement identifier are not allowed. + Note that if the precompiler runs in Informix compatibility mode and + some SQL statement is declared, "database" can not be used as a cursor + name. + </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> + <para> + AT clause can be omitted, but such statement has no meaning. + </para> + </listitem> + </varlistentry> + </variablelist> + + <variablelist> + <varlistentry> + <term><replaceable class="parameter">statement_name</replaceable></term> + <listitem> + <para> + The name of an SQL statement identifier, either as an SQL identifier or a host variable. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Notes</title> + <para> + This association is valid only if the declaration is physically placed on top of a dynamic statement. + </para> + </refsect1> + + <refsect1> + <title>Examples</title> + +<programlisting> +EXEC SQL CONNECT TO postgres AS con1; +EXEC SQL AT con1 DECLARE sql_stmt STATEMENT; +EXEC SQL DECLARE cursor_name CURSOR FOR sql_stmt; +EXEC SQL PREPARE sql_stmt FROM :dyn_string; +EXEC SQL OPEN cursor_name; +EXEC SQL FETCH cursor_name INTO :column1; +EXEC SQL CLOSE cursor_name; +</programlisting> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>DECLARE STATEMENT</command> is an extension of the SQL standard, + but can be used in famous DBMSs. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="ecpg-sql-connect"/></member> + <member><xref linkend="ecpg-sql-declare"/></member> + <member><xref linkend="ecpg-sql-open"/></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>DESCRIBE</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 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>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 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 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 string or a host variable containing the SQL + statement to be executed. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Notes</title> + + <para> + In typical usage, the <replaceable>string</replaceable> is a host + variable reference to a string containing a dynamically-constructed + SQL statement. The case of a literal string is not very useful; + you might as well just write the SQL statement directly, without + the extra typing of <command>EXECUTE IMMEDIATE</command>. + </para> + + <para> + If you do use a literal string, keep in mind that any double quotes + you might wish to include in the SQL statement must be written as + octal escapes (<literal>\042</literal>) not the usual C + idiom <literal>\"</literal>. This is because the string is inside + an <literal>EXEC SQL</literal> section, so the ECPG lexer parses it + according to SQL rules not C rules. Any embedded backslashes will + later be handled according to C rules; but <literal>\"</literal> + causes an immediate syntax error because it is seen as ending the + literal. + </para> + </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();</literal> 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 SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + 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">prepared_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 string or a host variable containing a preparable + SQL statement, one of SELECT, INSERT, UPDATE, or DELETE. + Use question marks (<literal>?</literal>) for parameter values + to be supplied at execution. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Notes</title> + + <para> + In typical usage, the <replaceable>string</replaceable> is a host + variable reference to a string containing a dynamically-constructed + SQL statement. The case of a literal string is not very useful; + you might as well just write a direct SQL <command>PREPARE</command> + statement. + </para> + + <para> + If you do use a literal string, keep in mind that any double quotes + you might wish to include in the SQL statement must be written as + octal escapes (<literal>\042</literal>) not the usual C + idiom <literal>\"</literal>. This is because the string is inside + an <literal>EXEC SQL</literal> section, so the ECPG lexer parses it + according to SQL rules not C rules. Any embedded backslashes will + later be handled according to C rules; but <literal>\"</literal> + causes an immediate syntax error because it is seen as ending the + literal. + </para> + </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>CURRENT</literal></term> + <listitem> + <para> + Set the connection to the current connection (thus, nothing happens). + </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 identifying 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 pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + + 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 assigns a new C data type + to a host variable. The host variable must be previously + declared in 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 begin declare section; +short a; +exec sql end declare section; +EXEC SQL VAR a IS int; +</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 NOT FOUND DO CONTINUE; +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 SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + 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> + <para> + <command>ecpg</command> can be run in a so-called <firstterm>Informix compatibility mode</firstterm>. If + this mode is active, it tries to behave as if it were the <productname>Informix</productname> + precompiler for <productname>Informix</productname> E/SQL. Generally spoken this will allow you to use + the dollar sign instead of the <literal>EXEC SQL</literal> primitive to introduce + embedded SQL commands: +<programlisting> +$int j = 3; +$CONNECT TO :dbname; +$CREATE TABLE test(i INT PRIMARY KEY, j INT); +$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>, <literal>INFORMIX_SE</literal> + </para> + <para> + When linking programs that use this compatibility mode, remember to link + against <literal>libcompat</literal> that is shipped with ECPG. + </para> + <para> + Besides the previously explained syntactic sugar, the <productname>Informix</productname> compatibility + mode ports some functions for input, output and transformation of data as + well as embedded SQL statements known from E/SQL to ECPG. + </para> + <para> + <productname>Informix</productname> compatibility mode is closely connected to the pgtypeslib library + of ECPG. pgtypeslib maps SQL data types to data types within the C host + program and most of the additional functions of the <productname>Informix</productname> compatibility + mode allow you to operate on those C host program types. Note however that + the extent of the compatibility is limited. It does not try to copy <productname>Informix</productname> + behavior; it allows you to do more or less the same operations and gives + you functions that have the same name and the same basic behavior but it is + no drop-in replacement if you are using <productname>Informix</productname> at the moment. Moreover, + some of the data types are different. For example, + <productname>PostgreSQL</productname>'s datetime and interval types do not + know about ranges like for example <literal>YEAR TO MINUTE</literal> so you won't + find support in ECPG for that either. + </para> + + <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 + supported in Informix-mode without using <literal>typedef</literal>. In fact, in Informix-mode, + ECPG refuses to process source files that contain <literal>typedef sometype string;</literal> +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; +string userid; /* this variable will contain trimmed data */ +EXEC SQL END DECLARE SECTION; + +EXEC SQL FETCH MYCUR INTO :userid; +</programlisting> + </para> + </sect2> + + <sect2 id="ecpg-informix-statements"> + <title>Additional/Missing Embedded SQL Statements</title> + <para> + <variablelist> + <varlistentry> + <term><literal>CLOSE DATABASE</literal></term> + <listitem> + <para> + This statement closes the current connection. In fact, this is a + synonym for ECPG's <literal>DISCONNECT CURRENT</literal>: +<programlisting> +$CLOSE DATABASE; /* close the current connection */ +EXEC SQL CLOSE DATABASE; +</programlisting> + </para> + </listitem> + </varlistentry> + <varlistentry> + <term><literal>FREE cursor_name</literal></term> + <listitem> + <para> + Due to differences in how ECPG works compared to Informix's ESQL/C (namely, which steps + are purely grammar transformations and which steps rely on the underlying run-time library) + there is no <literal>FREE cursor_name</literal> statement in ECPG. This is because in ECPG, + <literal>DECLARE CURSOR</literal> doesn't translate to a function call into + the run-time library that uses to the cursor name. This means that there's no run-time + bookkeeping of SQL cursors in the ECPG run-time library, only in the PostgreSQL server. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term><literal>FREE statement_name</literal></term> + <listitem> + <para> + <literal>FREE statement_name</literal> is a synonym for <literal>DEALLOCATE PREPARE statement_name</literal>. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </sect2> + + <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; +}; + +struct sqlda_compat +{ + 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; +typedef struct sqlda_compat sqlda_t; +</programlisting> + </para> + + <para> + The global properties are: + <variablelist> + + <varlistentry> + <term><literal>sqld</literal></term> + <listitem> + <para> + The number of fields in the <literal>SQLDA</literal> descriptor. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>sqlvar</literal></term> + <listitem> + <para> + Pointer to the per-field properties. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>desc_name</literal></term> + <listitem> + <para> + Unused, filled with zero-bytes. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>desc_occ</literal></term> + <listitem> + <para> + Size of the allocated structure. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>desc_next</literal></term> + <listitem> + <para> + Pointer to the next SQLDA structure if the result set contains more than one record. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>reserved</literal></term> + <listitem> + <para> + Unused pointer, contains NULL. Kept for Informix-compatibility. + </para> + </listitem> + </varlistentry> + + </variablelist> + + The per-field properties are below, they are stored in the <literal>sqlvar</literal> array: + + <variablelist> + + <varlistentry> + <term><literal>sqltype</literal></term> + <listitem> + <para> + Type of the field. Constants are in <literal>sqltypes.h</literal> + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>sqllen</literal></term> + <listitem> + <para> + Length of the field data. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>sqldata</literal></term> + <listitem> + <para> + Pointer to the field data. The pointer is of <literal>char *</literal> type, + the data pointed by it is in a binary format. Example: +<programlisting> +int intval; + +switch (sqldata->sqlvar[i].sqltype) +{ + case SQLINTEGER: + intval = *(int *)sqldata->sqlvar[i].sqldata; + break; + ... +} +</programlisting> + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>sqlind</literal></term> + <listitem> + <para> + Pointer to the NULL indicator. If returned by DESCRIBE or FETCH then it's always a valid pointer. + If used as input for <literal>EXECUTE ... USING sqlda;</literal> then NULL-pointer value means + that the value for this field is non-NULL. Otherwise a valid pointer and <literal>sqlitype</literal> + has to be properly set. Example: +<programlisting> +if (*(int2 *)sqldata->sqlvar[i].sqlind != 0) + printf("value is NULL\n"); +</programlisting> + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>sqlname</literal></term> + <listitem> + <para> + Name of the field. 0-terminated string. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>sqlformat</literal></term> + <listitem> + <para> + Reserved in Informix, value of <xref linkend="libpq-PQfformat"/> for the field. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>sqlitype</literal></term> + <listitem> + <para> + Type of the NULL indicator data. It's always SQLSMINT when returning data from the server. + When the <literal>SQLDA</literal> is used for a parameterized query, the data is treated + according to the set type. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>sqlilen</literal></term> + <listitem> + <para> + Length of the NULL indicator data. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>sqlxid</literal></term> + <listitem> + <para> + Extended type of the field, result of <xref linkend="libpq-PQftype"/>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>sqltypename</literal></term> + <term><literal>sqltypelen</literal></term> + <term><literal>sqlownerlen</literal></term> + <term><literal>sqlsourcetype</literal></term> + <term><literal>sqlownername</literal></term> + <term><literal>sqlsourceid</literal></term> + <term><literal>sqlflags</literal></term> + <term><literal>sqlreserved</literal></term> + <listitem> + <para> + Unused. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>sqlilongdata</literal></term> + <listitem> + <para> + It equals to <literal>sqldata</literal> if <literal>sqllen</literal> is larger than 32kB. + </para> + </listitem> + </varlistentry> + + </variablelist> + + Example: +<programlisting> +EXEC SQL INCLUDE sqlda.h; + + sqlda_t *sqlda; /* This doesn't need to be under embedded DECLARE SECTION */ + + EXEC SQL BEGIN DECLARE SECTION; + char *prep_stmt = "select * from table1"; + int i; + EXEC SQL END DECLARE SECTION; + + ... + + EXEC SQL PREPARE mystmt FROM :prep_stmt; + + EXEC SQL DESCRIBE mystmt INTO sqlda; + + printf("# of fields: %d\n", sqlda->sqld); + for (i = 0; i < sqlda->sqld; i++) + printf("field %d: \"%s\"\n", sqlda->sqlvar[i]->sqlname); + + EXEC SQL DECLARE mycursor CURSOR FOR mystmt; + EXEC SQL OPEN mycursor; + EXEC SQL WHENEVER NOT FOUND GOTO out; + + while (1) + { + EXEC SQL FETCH mycursor USING sqlda; + } + + EXEC SQL CLOSE mycursor; + + free(sqlda); /* The main structure is all to be free(), + * sqlda and sqlda->sqlvar is in one allocated area */ +</programlisting> + For more information, see the <literal>sqlda.h</literal> header and the + <literal>src/interfaces/ecpg/test/compat_informix/sqlda.pgc</literal> regression test. + </para> + </sect2> + + <sect2 id="ecpg-informix-functions"> + <title>Additional Functions</title> + <para> + <variablelist> + <varlistentry> + <term><function>decadd</function></term> + <listitem> + <para> + Add two decimal type values. +<synopsis> +int decadd(decimal *arg1, decimal *arg2, decimal *sum); +</synopsis> + The function receives a pointer to the first operand of type decimal + (<literal>arg1</literal>), a pointer to the second operand of type decimal + (<literal>arg2</literal>) and a pointer to a value of type decimal that will + contain the sum (<literal>sum</literal>). On success, the function returns 0. + <symbol>ECPG_INFORMIX_NUM_OVERFLOW</symbol> is returned in case of overflow and + <symbol>ECPG_INFORMIX_NUM_UNDERFLOW</symbol> in case of underflow. -1 is returned for + other failures and <varname>errno</varname> is set to the respective <varname>errno</varname> number of the + pgtypeslib. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>deccmp</function></term> + <listitem> + <para> + Compare two variables of type decimal. +<synopsis> +int deccmp(decimal *arg1, decimal *arg2); +</synopsis> + The function receives a pointer to the first decimal value + (<literal>arg1</literal>), a pointer to the second decimal value + (<literal>arg2</literal>) and returns an integer value that indicates which is + the bigger value. + <itemizedlist> + <listitem> + <para> + 1, if the value that <literal>arg1</literal> points to is bigger than the + value that <literal>var2</literal> points to + </para> + </listitem> + <listitem> + <para> + -1, if the value that <literal>arg1</literal> points to is smaller than the + value that <literal>arg2</literal> points to </para> + </listitem> + <listitem> + <para> + 0, if the value that <literal>arg1</literal> points to and the value that + <literal>arg2</literal> points to are equal + </para> + </listitem> + </itemizedlist> + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>deccopy</function></term> + <listitem> + <para> + Copy a decimal value. +<synopsis> +void deccopy(decimal *src, decimal *target); +</synopsis> + The function receives a pointer to the decimal value that should be + copied as the first argument (<literal>src</literal>) and a pointer to the + target structure of type decimal (<literal>target</literal>) as the second + argument. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>deccvasc</function></term> + <listitem> + <para> + Convert a value from its ASCII representation into a decimal type. +<synopsis> +int deccvasc(char *cp, int len, decimal *np); +</synopsis> + The function receives a pointer to string that contains the string + representation of the number to be converted (<literal>cp</literal>) as well + as its length <literal>len</literal>. <literal>np</literal> is a pointer to the + decimal value that saves the result of the operation. + </para> + <para> + Valid formats are for example: + <literal>-2</literal>, + <literal>.794</literal>, + <literal>+3.44</literal>, + <literal>592.49E07</literal> or + <literal>-32.84e-4</literal>. + </para> + <para> + The function returns 0 on success. If overflow or underflow occurred, + <literal>ECPG_INFORMIX_NUM_OVERFLOW</literal> or + <literal>ECPG_INFORMIX_NUM_UNDERFLOW</literal> is returned. If the ASCII + representation could not be parsed, + <literal>ECPG_INFORMIX_BAD_NUMERIC</literal> is returned or + <literal>ECPG_INFORMIX_BAD_EXPONENT</literal> if this problem occurred while + parsing the exponent. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>deccvdbl</function></term> + <listitem> + <para> + Convert a value of type double to a value of type decimal. +<synopsis> +int deccvdbl(double dbl, decimal *np); +</synopsis> + The function receives the variable of type double that should be + converted as its first argument (<literal>dbl</literal>). As the second + argument (<literal>np</literal>), the function receives a pointer to the + decimal variable that should hold the result of the operation. + </para> + <para> + The function returns 0 on success and a negative value if the + conversion failed. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>deccvint</function></term> + <listitem> + <para> + Convert a value of type int to a value of type decimal. +<synopsis> +int deccvint(int in, decimal *np); +</synopsis> + The function receives the variable of type int that should be + converted as its first argument (<literal>in</literal>). As the second + argument (<literal>np</literal>), the function receives a pointer to the + decimal variable that should hold the result of the operation. + </para> + <para> + The function returns 0 on success and a negative value if the + conversion failed. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>deccvlong</function></term> + <listitem> + <para> + Convert a value of type long to a value of type decimal. +<synopsis> +int deccvlong(long lng, decimal *np); +</synopsis> + The function receives the variable of type long that should be + converted as its first argument (<literal>lng</literal>). As the second + argument (<literal>np</literal>), the function receives a pointer to the + decimal variable that should hold the result of the operation. + </para> + <para> + The function returns 0 on success and a negative value if the + conversion failed. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>decdiv</function></term> + <listitem> + <para> + Divide two variables of type decimal. +<synopsis> +int decdiv(decimal *n1, decimal *n2, decimal *result); +</synopsis> + The function receives pointers to the variables that are the first + (<literal>n1</literal>) and the second (<literal>n2</literal>) operands and + calculates <literal>n1</literal>/<literal>n2</literal>. <literal>result</literal> is a + pointer to the variable that should hold the result of the operation. + </para> + <para> + On success, 0 is returned and a negative value if the division fails. + If overflow or underflow occurred, the function returns + <literal>ECPG_INFORMIX_NUM_OVERFLOW</literal> or + <literal>ECPG_INFORMIX_NUM_UNDERFLOW</literal> respectively. If an attempt to + divide by zero is observed, the function returns + <literal>ECPG_INFORMIX_DIVIDE_ZERO</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>decmul</function></term> + <listitem> + <para> + Multiply two decimal values. +<synopsis> +int decmul(decimal *n1, decimal *n2, decimal *result); +</synopsis> + The function receives pointers to the variables that are the first + (<literal>n1</literal>) and the second (<literal>n2</literal>) operands and + calculates <literal>n1</literal>*<literal>n2</literal>. <literal>result</literal> is a + pointer to the variable that should hold the result of the operation. + </para> + <para> + On success, 0 is returned and a negative value if the multiplication + fails. If overflow or underflow occurred, the function returns + <literal>ECPG_INFORMIX_NUM_OVERFLOW</literal> or + <literal>ECPG_INFORMIX_NUM_UNDERFLOW</literal> respectively. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>decsub</function></term> + <listitem> + <para> + Subtract one decimal value from another. +<synopsis> +int decsub(decimal *n1, decimal *n2, decimal *result); +</synopsis> + The function receives pointers to the variables that are the first + (<literal>n1</literal>) and the second (<literal>n2</literal>) operands and + calculates <literal>n1</literal>-<literal>n2</literal>. <literal>result</literal> is a + pointer to the variable that should hold the result of the operation. + </para> + <para> + On success, 0 is returned and a negative value if the subtraction + fails. If overflow or underflow occurred, the function returns + <literal>ECPG_INFORMIX_NUM_OVERFLOW</literal> or + <literal>ECPG_INFORMIX_NUM_UNDERFLOW</literal> respectively. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>dectoasc</function></term> + <listitem> + <para> + Convert a variable of type decimal to its ASCII representation in a C + char* string. +<synopsis> +int dectoasc(decimal *np, char *cp, int len, int right) +</synopsis> + The function receives a pointer to a variable of type decimal + (<literal>np</literal>) that it converts to its textual representation. + <literal>cp</literal> is the buffer that should hold the result of the + operation. The parameter <literal>right</literal> specifies, how many digits + right of the decimal point should be included in the output. The result + will be rounded to this number of decimal digits. Setting + <literal>right</literal> to -1 indicates that all available decimal digits + should be included in the output. If the length of the output buffer, + which is indicated by <literal>len</literal> is not sufficient to hold the + textual representation including the trailing zero byte, only a + single <literal>*</literal> character is stored in the result and -1 is + returned. + </para> + <para> + The function returns either -1 if the buffer <literal>cp</literal> was too + small or <literal>ECPG_INFORMIX_OUT_OF_MEMORY</literal> if memory was + exhausted. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>dectodbl</function></term> + <listitem> + <para> + Convert a variable of type decimal to a double. +<synopsis> +int dectodbl(decimal *np, double *dblp); +</synopsis> + The function receives a pointer to the decimal value to convert + (<literal>np</literal>) and a pointer to the double variable that + should hold the result of the operation (<literal>dblp</literal>). + </para> + <para> + On success, 0 is returned and a negative value if the conversion + failed. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>dectoint</function></term> + <listitem> + <para> + Convert a variable to type decimal to an integer. +<synopsis> +int dectoint(decimal *np, int *ip); +</synopsis> + The function receives a pointer to the decimal value to convert + (<literal>np</literal>) and a pointer to the integer variable that + should hold the result of the operation (<literal>ip</literal>). + </para> + <para> + On success, 0 is returned and a negative value if the conversion + failed. If an overflow occurred, <literal>ECPG_INFORMIX_NUM_OVERFLOW</literal> + is returned. + </para> + <para> + Note that the ECPG implementation differs from the <productname>Informix</productname> + implementation. <productname>Informix</productname> limits an integer to the range from -32767 to + 32767, while the limits in the ECPG implementation depend on the + architecture (<literal>INT_MIN .. INT_MAX</literal>). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>dectolong</function></term> + <listitem> + <para> + Convert a variable to type decimal to a long integer. +<synopsis> +int dectolong(decimal *np, long *lngp); +</synopsis> + The function receives a pointer to the decimal value to convert + (<literal>np</literal>) and a pointer to the long variable that + should hold the result of the operation (<literal>lngp</literal>). + </para> + <para> + On success, 0 is returned and a negative value if the conversion + failed. If an overflow occurred, <literal>ECPG_INFORMIX_NUM_OVERFLOW</literal> + is returned. + </para> + <para> + Note that the ECPG implementation differs from the <productname>Informix</productname> + implementation. <productname>Informix</productname> limits a long integer to the range from + -2,147,483,647 to 2,147,483,647, while the limits in the ECPG + implementation depend on the architecture (<literal>-LONG_MAX .. + LONG_MAX</literal>). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>rdatestr</function></term> + <listitem> + <para> + Converts a date to a C char* string. +<synopsis> +int rdatestr(date d, char *str); +</synopsis> + The function receives two arguments, the first one is the date to + convert (<literal>d</literal>) and the second one is a pointer to the target + string. The output format is always <literal>yyyy-mm-dd</literal>, so you need + to allocate at least 11 bytes (including the zero-byte terminator) for the + string. + </para> + <para> + The function returns 0 on success and a negative value in case of + error. + </para> + <para> + Note that ECPG's implementation differs from the <productname>Informix</productname> + implementation. In <productname>Informix</productname> the format can be influenced by setting + environment variables. In ECPG however, you cannot change the output + format. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>rstrdate</function></term> + <listitem> + <para> + Parse the textual representation of a date. +<synopsis> +int rstrdate(char *str, date *d); +</synopsis> + The function receives the textual representation of the date to convert + (<literal>str</literal>) and a pointer to a variable of type date + (<literal>d</literal>). This function does not allow you to specify a format + mask. It uses the default format mask of <productname>Informix</productname> which is + <literal>mm/dd/yyyy</literal>. Internally, this function is implemented by + means of <function>rdefmtdate</function>. Therefore, <function>rstrdate</function> is + not faster and if you have the choice you should opt for + <function>rdefmtdate</function> which allows you to specify the format mask + explicitly. + </para> + <para> + The function returns the same values as <function>rdefmtdate</function>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>rtoday</function></term> + <listitem> + <para> + Get the current date. +<synopsis> +void rtoday(date *d); +</synopsis> + The function receives a pointer to a date variable (<literal>d</literal>) + that it sets to the current date. + </para> + <para> + Internally this function uses the <xref linkend="pgtypesdatetoday"/> + function. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>rjulmdy</function></term> + <listitem> + <para> + Extract the values for the day, the month and the year from a variable + of type date. +<synopsis> +int rjulmdy(date d, short mdy[3]); +</synopsis> + The function receives the date <literal>d</literal> and a pointer to an array + of 3 short integer values <literal>mdy</literal>. The variable name indicates + the sequential order: <literal>mdy[0]</literal> will be set to contain the + number of the month, <literal>mdy[1]</literal> will be set to the value of the + day and <literal>mdy[2]</literal> will contain the year. + </para> + <para> + The function always returns 0 at the moment. + </para> + <para> + Internally the function uses the <xref linkend="pgtypesdatejulmdy"/> + function. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>rdefmtdate</function></term> + <listitem> + <para> + Use a format mask to convert a character string to a value of type + date. +<synopsis> +int rdefmtdate(date *d, char *fmt, char *str); +</synopsis> + The function receives a pointer to the date value that should hold the + result of the operation (<literal>d</literal>), the format mask to use for + parsing the date (<literal>fmt</literal>) and the C char* string containing + the textual representation of the date (<literal>str</literal>). The textual + representation is expected to match the format mask. However you do not + need to have a 1:1 mapping of the string to the format mask. The + function only analyzes the sequential order and looks for the literals + <literal>yy</literal> or <literal>yyyy</literal> that indicate the + position of the year, <literal>mm</literal> to indicate the position of + the month and <literal>dd</literal> to indicate the position of the + day. + </para> + <para> + The function returns the following values: + <itemizedlist> + <listitem> + <para> + 0 - The function terminated successfully. + </para> + </listitem> + <listitem> + <para> + <literal>ECPG_INFORMIX_ENOSHORTDATE</literal> - The date does not contain + delimiters between day, month and year. In this case the input + string must be exactly 6 or 8 bytes long but isn't. + </para> + </listitem> + <listitem> + <para> + <literal>ECPG_INFORMIX_ENOTDMY</literal> - The format string did not + correctly indicate the sequential order of year, month and day. + </para> + </listitem> + <listitem> + <para> + <literal>ECPG_INFORMIX_BAD_DAY</literal> - The input string does not + contain a valid day. + </para> + </listitem> + <listitem> + <para> + <literal>ECPG_INFORMIX_BAD_MONTH</literal> - The input string does not + contain a valid month. + </para> + </listitem> + <listitem> + <para> + <literal>ECPG_INFORMIX_BAD_YEAR</literal> - The input string does not + contain a valid year. + </para> + </listitem> + </itemizedlist> + </para> + <para> + Internally this function is implemented to use the <xref + linkend="pgtypesdatedefmtasc"/> function. See the reference there for a + table of example input. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>rfmtdate</function></term> + <listitem> + <para> + Convert a variable of type date to its textual representation using a + format mask. +<synopsis> +int rfmtdate(date d, char *fmt, char *str); +</synopsis> + The function receives the date to convert (<literal>d</literal>), the format + mask (<literal>fmt</literal>) and the string that will hold the textual + representation of the date (<literal>str</literal>). + </para> + <para> + On success, 0 is returned and a negative value if an error occurred. + </para> + <para> + Internally this function uses the <xref linkend="pgtypesdatefmtasc"/> + function, see the reference there for examples. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>rmdyjul</function></term> + <listitem> + <para> + Create a date value from an array of 3 short integers that specify the + day, the month and the year of the date. +<synopsis> +int rmdyjul(short mdy[3], date *d); +</synopsis> + The function receives the array of the 3 short integers + (<literal>mdy</literal>) and a pointer to a variable of type date that should + hold the result of the operation. + </para> + <para> + Currently the function returns always 0. + </para> + <para> + Internally the function is implemented to use the function <xref + linkend="pgtypesdatemdyjul"/>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>rdayofweek</function></term> + <listitem> + <para> + Return a number representing the day of the week for a date value. +<synopsis> +int rdayofweek(date d); +</synopsis> + The function receives the date variable <literal>d</literal> as its only + argument and returns an integer that indicates the day of the week for + this date. + <itemizedlist> + <listitem> + <para> + 0 - Sunday + </para> + </listitem> + <listitem> + <para> + 1 - Monday + </para> + </listitem> + <listitem> + <para> + 2 - Tuesday + </para> + </listitem> + <listitem> + <para> + 3 - Wednesday + </para> + </listitem> + <listitem> + <para> + 4 - Thursday + </para> + </listitem> + <listitem> + <para> + 5 - Friday + </para> + </listitem> + <listitem> + <para> + 6 - Saturday + </para> + </listitem> + </itemizedlist> + </para> + <para> + Internally the function is implemented to use the function <xref + linkend="pgtypesdatedayofweek"/>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>dtcurrent</function></term> + <listitem> + <para> + Retrieve the current timestamp. +<synopsis> +void dtcurrent(timestamp *ts); +</synopsis> + The function retrieves the current timestamp and saves it into the + timestamp variable that <literal>ts</literal> points to. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>dtcvasc</function></term> + <listitem> + <para> + Parses a timestamp from its textual representation + into a timestamp variable. +<synopsis> +int dtcvasc(char *str, timestamp *ts); +</synopsis> + The function receives the string to parse (<literal>str</literal>) and a + pointer to the timestamp variable that should hold the result of the + operation (<literal>ts</literal>). + </para> + <para> + The function returns 0 on success and a negative value in case of + error. + </para> + <para> + Internally this function uses the <xref + linkend="pgtypestimestampfromasc"/> function. See the reference there + for a table with example inputs. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>dtcvfmtasc</function></term> + <listitem> + <para> + Parses a timestamp from its textual representation + using a format mask into a timestamp variable. +<synopsis> +dtcvfmtasc(char *inbuf, char *fmtstr, timestamp *dtvalue) +</synopsis> + The function receives the string to parse (<literal>inbuf</literal>), the + format mask to use (<literal>fmtstr</literal>) and a pointer to the timestamp + variable that should hold the result of the operation + (<literal>dtvalue</literal>). + </para> + <para> + This function is implemented by means of the <xref + linkend="pgtypestimestampdefmtasc"/> function. See the documentation + there for a list of format specifiers that can be used. + </para> + <para> + The function returns 0 on success and a negative value in case of + error. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>dtsub</function></term> + <listitem> + <para> + Subtract one timestamp from another and return a variable of type + interval. +<synopsis> +int dtsub(timestamp *ts1, timestamp *ts2, interval *iv); +</synopsis> + The function will subtract the timestamp variable that <literal>ts2</literal> + points to from the timestamp variable that <literal>ts1</literal> points to + and will store the result in the interval variable that <literal>iv</literal> + points to. + </para> + <para> + Upon success, the function returns 0 and a negative value if an + error occurred. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>dttoasc</function></term> + <listitem> + <para> + Convert a timestamp variable to a C char* string. +<synopsis> +int dttoasc(timestamp *ts, char *output); +</synopsis> + The function receives a pointer to the timestamp variable to convert + (<literal>ts</literal>) and the string that should hold the result of the + operation (<literal>output</literal>). It converts <literal>ts</literal> to its + textual representation according to the SQL standard, which is + be <literal>YYYY-MM-DD HH:MM:SS</literal>. + </para> + <para> + Upon success, the function returns 0 and a negative value if an + error occurred. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>dttofmtasc</function></term> + <listitem> + <para> + Convert a timestamp variable to a C char* using a format mask. +<synopsis> +int dttofmtasc(timestamp *ts, char *output, int str_len, char *fmtstr); +</synopsis> + The function receives a pointer to the timestamp to convert as its + first argument (<literal>ts</literal>), a pointer to the output buffer + (<literal>output</literal>), the maximal length that has been allocated for + the output buffer (<literal>str_len</literal>) and the format mask to + use for the conversion (<literal>fmtstr</literal>). + </para> + <para> + Upon success, the function returns 0 and a negative value if an + error occurred. + </para> + <para> + Internally, this function uses the <xref + linkend="pgtypestimestampfmtasc"/> function. See the reference there for + information on what format mask specifiers can be used. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>intoasc</function></term> + <listitem> + <para> + Convert an interval variable to a C char* string. +<synopsis> +int intoasc(interval *i, char *str); +</synopsis> + The function receives a pointer to the interval variable to convert + (<literal>i</literal>) and the string that should hold the result of the + operation (<literal>str</literal>). It converts <literal>i</literal> to its + textual representation according to the SQL standard, which is + be <literal>YYYY-MM-DD HH:MM:SS</literal>. + </para> + <para> + Upon success, the function returns 0 and a negative value if an + error occurred. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>rfmtlong</function></term> + <listitem> + <para> + Convert a long integer value to its textual representation using a + format mask. +<synopsis> +int rfmtlong(long lng_val, char *fmt, char *outbuf); +</synopsis> + The function receives the long value <literal>lng_val</literal>, the format + mask <literal>fmt</literal> and a pointer to the output buffer + <literal>outbuf</literal>. It converts the long value according to the format + mask to its textual representation. + </para> + <para> + The format mask can be composed of the following format specifying + characters: + <itemizedlist> + <listitem> + <para> + <literal>*</literal> (asterisk) - if this position would be blank + otherwise, fill it with an asterisk. + </para> + </listitem> + <listitem> + <para> + <literal>&</literal> (ampersand) - if this position would be + blank otherwise, fill it with a zero. + </para> + </listitem> + <listitem> + <para> + <literal>#</literal> - turn leading zeroes into blanks. + </para> + </listitem> + <listitem> + <para> + <literal><</literal> - left-justify the number in the string. + </para> + </listitem> + <listitem> + <para> + <literal>,</literal> (comma) - group numbers of four or more digits + into groups of three digits separated by a comma. + </para> + </listitem> + <listitem> + <para> + <literal>.</literal> (period) - this character separates the + whole-number part of the number from the fractional part. + </para> + </listitem> + <listitem> + <para> + <literal>-</literal> (minus) - the minus sign appears if the number + is a negative value. + </para> + </listitem> + <listitem> + <para> + <literal>+</literal> (plus) - the plus sign appears if the number is + a positive value. + </para> + </listitem> + <listitem> + <para> + <literal>(</literal> - this replaces the minus sign in front of the + negative number. The minus sign will not appear. + </para> + </listitem> + <listitem> + <para> + <literal>)</literal> - this character replaces the minus and is + printed behind the negative value. + </para> + </listitem> + <listitem> + <para> + <literal>$</literal> - the currency symbol. + </para> + </listitem> + </itemizedlist> + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>rupshift</function></term> + <listitem> + <para> + Convert a string to upper case. +<synopsis> +void rupshift(char *str); +</synopsis> + The function receives a pointer to the string and transforms every + lower case character to upper case. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>byleng</function></term> + <listitem> + <para> + Return the number of characters in a string without counting trailing + blanks. +<synopsis> +int byleng(char *str, int len); +</synopsis> + The function expects a fixed-length string as its first argument + (<literal>str</literal>) and its length as its second argument + (<literal>len</literal>). It returns the number of significant characters, + that is the length of the string without trailing blanks. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>ldchar</function></term> + <listitem> + <para> + Copy a fixed-length string into a null-terminated string. +<synopsis> +void ldchar(char *src, int len, char *dest); +</synopsis> + The function receives the fixed-length string to copy + (<literal>src</literal>), its length (<literal>len</literal>) and a pointer to the + destination memory (<literal>dest</literal>). Note that you need to reserve at + least <literal>len+1</literal> bytes for the string that <literal>dest</literal> + points to. The function copies at most <literal>len</literal> bytes to the new + location (less if the source string has trailing blanks) and adds the + null-terminator. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>rgetmsg</function></term> + <listitem> + <para> +<synopsis> +int rgetmsg(int msgnum, char *s, int maxsize); +</synopsis> + This function exists but is not implemented at the moment! + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>rtypalign</function></term> + <listitem> + <para> +<synopsis> +int rtypalign(int offset, int type); +</synopsis> + This function exists but is not implemented at the moment! + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>rtypmsize</function></term> + <listitem> + <para> +<synopsis> +int rtypmsize(int type, int len); +</synopsis> + This function exists but is not implemented at the moment! + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>rtypwidth</function></term> + <listitem> + <para> +<synopsis> +int rtypwidth(int sqltype, int sqllen); +</synopsis> + This function exists but is not implemented at the moment! + </para> + </listitem> + </varlistentry> + + <varlistentry id="rsetnull"> + <term><function>rsetnull</function></term> + <listitem> + <para> + Set a variable to NULL. +<synopsis> +int rsetnull(int t, char *ptr); +</synopsis> + The function receives an integer that indicates the type of the + variable and a pointer to the variable itself that is cast to a C + char* pointer. + </para> + <para> + The following types exist: + <itemizedlist> + <listitem> + <para> + <literal>CCHARTYPE</literal> - For a variable of type <type>char</type> or <type>char*</type> + </para> + </listitem> + <listitem> + <para> + <literal>CSHORTTYPE</literal> - For a variable of type <type>short int</type> + </para> + </listitem> + <listitem> + <para> + <literal>CINTTYPE</literal> - For a variable of type <type>int</type> + </para> + </listitem> + <listitem> + <para> + <literal>CBOOLTYPE</literal> - For a variable of type <type>boolean</type> + </para> + </listitem> + <listitem> + <para> + <literal>CFLOATTYPE</literal> - For a variable of type <type>float</type> + </para> + </listitem> + <listitem> + <para> + <literal>CLONGTYPE</literal> - For a variable of type <type>long</type> + </para> + </listitem> + <listitem> + <para> + <literal>CDOUBLETYPE</literal> - For a variable of type <type>double</type> + </para> + </listitem> + <listitem> + <para> + <literal>CDECIMALTYPE</literal> - For a variable of type <type>decimal</type> + </para> + </listitem> + <listitem> + <para> + <literal>CDATETYPE</literal> - For a variable of type <type>date</type> + </para> + </listitem> + <listitem> + <para> + <literal>CDTIMETYPE</literal> - For a variable of type <type>timestamp</type> + </para> + </listitem> + </itemizedlist> + </para> + + <para> + Here is an example of a call to this function: +<programlisting><![CDATA[ +$char c[] = "abc "; +$short s = 17; +$int i = -74874; + +rsetnull(CCHARTYPE, (char *) c); +rsetnull(CSHORTTYPE, (char *) &s); +rsetnull(CINTTYPE, (char *) &i); +]]> +</programlisting> + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>risnull</function></term> + <listitem> + <para> + Test if a variable is NULL. +<synopsis> +int risnull(int t, char *ptr); +</synopsis> + The function receives the type of the variable to test (<literal>t</literal>) + as well a pointer to this variable (<literal>ptr</literal>). Note that the + latter needs to be cast to a char*. See the function <xref + linkend="rsetnull"/> for a list of possible variable types. + </para> + <para> + Here is an example of how to use this function: +<programlisting><![CDATA[ +$char c[] = "abc "; +$short s = 17; +$int i = -74874; + +risnull(CCHARTYPE, (char *) c); +risnull(CSHORTTYPE, (char *) &s); +risnull(CINTTYPE, (char *) &i); +]]> +</programlisting> + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </sect2> + + <sect2 id="ecpg-informix-constants"> + <title>Additional Constants</title> + <para> + Note that all constants here describe errors and all of them are defined + to represent negative values. In the descriptions of the different + constants you can also find the value that the constants represent in the + current implementation. However you should not rely on this number. You can + however rely on the fact all of them are defined to represent negative + values. + <variablelist> + <varlistentry> + <term><literal>ECPG_INFORMIX_NUM_OVERFLOW</literal></term> + <listitem> + <para> + Functions return this value if an overflow occurred in a + calculation. Internally it is defined as -1200 (the <productname>Informix</productname> + definition). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ECPG_INFORMIX_NUM_UNDERFLOW</literal></term> + <listitem> + <para> + Functions return this value if an underflow occurred in a calculation. + Internally it is defined as -1201 (the <productname>Informix</productname> definition). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ECPG_INFORMIX_DIVIDE_ZERO</literal></term> + <listitem> + <para> + Functions return this value if an attempt to divide by zero is + observed. Internally it is defined as -1202 (the <productname>Informix</productname> definition). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ECPG_INFORMIX_BAD_YEAR</literal></term> + <listitem> + <para> + Functions return this value if a bad value for a year was found while + parsing a date. Internally it is defined as -1204 (the <productname>Informix</productname> + definition). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ECPG_INFORMIX_BAD_MONTH</literal></term> + <listitem> + <para> + Functions return this value if a bad value for a month was found while + parsing a date. Internally it is defined as -1205 (the <productname>Informix</productname> + definition). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ECPG_INFORMIX_BAD_DAY</literal></term> + <listitem> + <para> + Functions return this value if a bad value for a day was found while + parsing a date. Internally it is defined as -1206 (the <productname>Informix</productname> + definition). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ECPG_INFORMIX_ENOSHORTDATE</literal></term> + <listitem> + <para> + Functions return this value if a parsing routine needs a short date + representation but did not get the date string in the right length. + Internally it is defined as -1209 (the <productname>Informix</productname> definition). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ECPG_INFORMIX_DATE_CONVERT</literal></term> + <listitem> + <para> + Functions return this value if an error occurred during date + formatting. Internally it is defined as -1210 (the + <productname>Informix</productname> definition). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ECPG_INFORMIX_OUT_OF_MEMORY</literal></term> + <listitem> + <para> + Functions return this value if memory was exhausted during + their operation. Internally it is defined as -1211 (the + <productname>Informix</productname> definition). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ECPG_INFORMIX_ENOTDMY</literal></term> + <listitem> + <para> + Functions return this value if a parsing routine was supposed to get a + format mask (like <literal>mmddyy</literal>) but not all fields were listed + correctly. Internally it is defined as -1212 (the <productname>Informix</productname> definition). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ECPG_INFORMIX_BAD_NUMERIC</literal></term> + <listitem> + <para> + Functions return this value either if a parsing routine cannot parse + the textual representation for a numeric value because it contains + errors or if a routine cannot complete a calculation involving numeric + variables because at least one of the numeric variables is invalid. + Internally it is defined as -1213 (the <productname>Informix</productname> definition). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ECPG_INFORMIX_BAD_EXPONENT</literal></term> + <listitem> + <para> + Functions return this value if a parsing routine cannot parse + an exponent. Internally it is defined as -1216 (the + <productname>Informix</productname> definition). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ECPG_INFORMIX_BAD_DATE</literal></term> + <listitem> + <para> + Functions return this value if a parsing routine cannot parse + a date. Internally it is defined as -1218 (the + <productname>Informix</productname> definition). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ECPG_INFORMIX_EXTRA_CHARS</literal></term> + <listitem> + <para> + Functions return this value if a parsing routine is passed extra + characters it cannot parse. Internally it is defined as -1264 (the + <productname>Informix</productname> definition). + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </sect2> + </sect1> + + <sect1 id="ecpg-oracle-compat"> + <title><productname>Oracle</productname> Compatibility Mode</title> + <para> + <command>ecpg</command> can be run in a so-called <firstterm>Oracle + compatibility mode</firstterm>. If this mode is active, it tries to + behave as if it were Oracle <productname>Pro*C</productname>. + </para> + + <para> + Specifically, this mode changes <command>ecpg</command> in three ways: + + <itemizedlist> + <listitem> + <para> + Pad character arrays receiving character string types with + trailing spaces to the specified length + </para> + </listitem> + + <listitem> + <para> + Zero byte terminate these character arrays, and set the indicator + variable if truncation occurs + </para> + </listitem> + + <listitem> + <para> + Set the null indicator to <literal>-1</literal> when character + arrays receive empty character string types + </para> + </listitem> + </itemizedlist> + </para> + </sect1> + + <sect1 id="ecpg-develop"> + <title>Internals</title> + + <para> + This section explains how <application>ECPG</application> works + internally. This information can occasionally be useful to help + users understand how to use <application>ECPG</application>. + </para> + + <para> + The first four lines written by <command>ecpg</command> to the + output are fixed lines. Two are comments and two are include + lines necessary to interface to the library. Then the + preprocessor reads through the file and writes output. Normally + it just echoes everything to the output. + </para> + + <para> + When it sees an <command>EXEC SQL</command> statement, it + intervenes and changes it. The command starts with <command>EXEC + SQL</command> and ends with <command>;</command>. Everything in + between is treated as an <acronym>SQL</acronym> statement and + parsed for variable substitution. + </para> + + <para> + Variable substitution occurs when a symbol starts with a colon + (<literal>:</literal>). The variable with that name is looked up + among the variables that were previously declared within a + <literal>EXEC SQL DECLARE</literal> section. + </para> + + <para> + The most important function in the library is + <function>ECPGdo</function>, which takes care of executing most + commands. It takes a variable number of arguments. This can easily + add up to 50 or so arguments, and we hope this will not be a + problem on any platform. + </para> + + <para> + The arguments are: + + <variablelist> + <varlistentry> + <term>A line number</term> + <listitem> + <para> + This is the line number of the original line; used in error + messages only. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>A string</term> + <listitem> + <para> + This is the <acronym>SQL</acronym> command that is to be issued. + It is modified by the input variables, i.e., the variables that + where not known at compile time but are to be entered in the + command. Where the variables should go the string contains + <literal>?</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>Input variables</term> + <listitem> + <para> + Every input variable causes ten arguments to be created. (See below.) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><parameter>ECPGt_EOIT</parameter></term> + <listitem> + <para> + An <type>enum</type> telling that there are no more input + variables. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>Output variables</term> + <listitem> + <para> + Every output variable causes ten arguments to be created. + (See below.) These variables are filled by the function. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><parameter>ECPGt_EORT</parameter></term> + <listitem> + <para> + An <type>enum</type> telling that there are no more variables. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + + <para> + For every variable that is part of the <acronym>SQL</acronym> + command, the function gets ten arguments: + + <orderedlist> + <listitem> + <para> + The type as a special symbol. + </para> + </listitem> + + <listitem> + <para> + A pointer to the value or a pointer to the pointer. + </para> + </listitem> + + <listitem> + <para> + The size of the variable if it is a <type>char</type> or <type>varchar</type>. + </para> + </listitem> + + <listitem> + <para> + The number of elements in the array (for array fetches). + </para> + </listitem> + + <listitem> + <para> + The offset to the next element in the array (for array fetches). + </para> + </listitem> + + <listitem> + <para> + The type of the indicator variable as a special symbol. + </para> + </listitem> + + <listitem> + <para> + A pointer to the indicator variable. + </para> + </listitem> + + <listitem> + <para> + 0 + </para> + </listitem> + + <listitem> + <para> + The number of elements in the indicator array (for array fetches). + </para> + </listitem> + + <listitem> + <para> + The offset to the next element in the indicator array (for + array fetches). + </para> + </listitem> + </orderedlist> + </para> + + <para> + Note that not all SQL commands are treated in this way. For + instance, an open cursor statement like: +<programlisting> +EXEC SQL OPEN <replaceable>cursor</replaceable>; +</programlisting> + is not copied to the output. Instead, the cursor's + <command>DECLARE</command> command is used at the position of the <command>OPEN</command> command + because it indeed opens the cursor. + </para> + + <para> + Here is a complete example describing the output of the + preprocessor of a file <filename>foo.pgc</filename> (details might + change with each particular version of the preprocessor): +<programlisting> +EXEC SQL BEGIN DECLARE SECTION; +int index; +int result; +EXEC SQL END DECLARE SECTION; +... +EXEC SQL SELECT res INTO :result FROM mytable WHERE index = :index; +</programlisting> + is translated into: +<programlisting><![CDATA[ +/* Processed by ecpg (2.6.0) */ +/* These two include files are added by the preprocessor */ +#include <ecpgtype.h>; +#include <ecpglib.h>; + +/* exec sql begin declare section */ + +#line 1 "foo.pgc" + + int index; + int result; +/* exec sql end declare section */ +... +ECPGdo(__LINE__, NULL, "SELECT res FROM mytable WHERE index = ? ", + ECPGt_int,&(index),1L,1L,sizeof(int), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, + ECPGt_int,&(result),1L,1L,sizeof(int), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 147 "foo.pgc" +]]> +</programlisting> + (The indentation here is added for readability and not + something the preprocessor does.) + </para> + </sect1> +</chapter> |