path: root/doc/src/sgml/ecpg.sgml

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ecpg.sgml,v 1.43 2003/03/25 16:15:35 petere Exp $
-->

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

 <para>
  This chapter describes the embedded <acronym>SQL</acronym> package
  for <productname>PostgreSQL</productname>. It works with
  <acronym>C</acronym> and <acronym>C++</acronym>. It was written by
  Linus Tolke (<email>[email protected]</email>) and Michael Meskes
  (<email>[email protected]</email>).
 </para>

 <para>
  Admittedly, 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
   is first passed to the embedded SQL preprocessor, which converts it
   to an ordinary C program, and afterwards it can be processed by a C
   compilation tool chain.
  </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.  Secondly, embedded
   <acronym>SQL</acronym> in C is defined in the
   <acronym>SQL</acronym> standard and supported by many other
   <acronym>SQL</acronym> databases.  The <productname>PostgreSQL</>
   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 indicated, 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 may appear in the
   global context or within a function.  Embedded
   <acronym>SQL</acronym> statements follow the case-sensitivity rules
   of normal <acronym>SQL</acronym> code, and not those of C.
  </para>

  <para>
   The following sections explain all the embedded SQL statements.
  </para>
 </sect1>

 <sect1 id="ecpg-connect">
  <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</><optional>@<replaceable>hostname</></optional><optional>:<replaceable>port</></optional></literal>
     </simpara>
    </listitem>

    <listitem>
     <simpara>
      <literal>tcp:postgresql://<replaceable>hostname</><optional>:<replaceable>port</></optional><optional>/<replaceable>dbname</></optional><optional>?<replaceable>options</></optional></literal>
     </simpara>
    </listitem>

    <listitem>
     <simpara>
      <literal>unix:postgresql://<replaceable>hostname</><optional>:<replaceable>port</></optional><optional>/<replaceable>dbname</></optional><optional>?<replaceable>options</></optional></literal>
     </simpara>
    </listitem>
 
    <listitem>
     <simpara>
      <literal><replaceable>character variable</replaceable></literal>
     </simpara>
    </listitem>

    <listitem>
     <simpara>
      <literal><replaceable>character string</replaceable></literal>
     </simpara>
    </listitem>
 
    <listitem>
     <simpara>
      <literal>DEFAULT</literal>
     </simpara>
    </listitem>
   </itemizedlist>
  </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>
   The <replaceable>username</replaceable> and
   <replaceable>password</replaceable> may be an SQL name, a
   character variable, or a character string.
  </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.
  </para>
 </sect1>

 <sect1 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>DEFAULT</literal>
     </simpara>
    </listitem>

    <listitem>
     <simpara>
      <literal>CURRENT</literal>
     </simpara>
    </listitem>

    <listitem>
     <simpara>
      <literal>ALL</literal>
     </simpara>
    </listitem>
   </itemizedlist>
  </para>
 </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>

  <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>
   Singleton Select:
<programlisting>
EXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE ascii = 'doodad';
</programlisting>
  </para>

  <para>
   Select using Cursors:
<programlisting>
EXEC SQL DECLARE foo_bar CURSOR FOR
    SELECT number, ascii FROM foo
    ORDER BY ascii;
EXEC SQL FETCH foo_bar INTO :FooBar, DooDad;
...
EXEC SQL CLOSE foo_bar;
EXEC SQL COMMIT;
</programlisting>
  </para>

  <para>
   Updates:
<programlisting>
EXEC SQL UPDATE foo
    SET ascii = 'foobar'
    WHERE number = 9999;
EXEC SQL COMMIT;
</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 the next
   section.
  </para>

  <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 (as known from
   other interfaces) via the <option>-t</option> command-line option
   to <command>ecpg</command> (see below) or via the <literal>EXEC SQL
   SET AUTOCOMMIT TO ON</literal> statement. In autocommit mode, each
   command is automatically committed unless it is inside an explicit
   transaction block. This mode can be explicitly turned off using
   <literal>EXEC SQL SET AUTOCOMMIT TO OFF</literal>.
  </para>
 </sect1>

 <sect1 id="ecpg-variables">
  <title>Passing Data</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 a specially marked section, 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;
char  foo[16], bar[16];
</programlisting>
  </para>

  <para>
   The declarations are also echoed to the output file as a normal C
   variables, so there's no need to declare them again.  Variables
   that are not intended to be used with SQL commands can be declared
   normally outside these special sections.
  </para>

  <para>
   The definition of a structure or union also must be listed inside a
   <literal>DECLARE</> section. Otherwise the preprocessor cannot
   handle these types since it does not know the definition.
  </para>

  <para>
   The special types <type>VARCHAR</type> and <type>VARCHAR2</type>
   are converted into a named <type>struct</> for every variable. A
   declaration like
<programlisting>
VARCHAR var[180];
</programlisting>
   is converted into
<programlisting>
struct varchar_var { int len; char arr[180]; } var;
</programlisting>
   This structure is suitable for interfacing with SQL datums of type
   <type>varchar</type>.
  </para>

  <para>
   To use a properly declared C variable in an SQL statement, write
   <literal>:<replaceable>varname</></literal> where an expression is
   expected.  See the previous section for some examples.
  </para>
 </sect1>

 <sect1 id="ecpg-errors">
  <title>Error Handling</title>

  <para>
   The embedded SQL interface provides a simplistic and a complex way
   to handle exceptional conditions in a program.  The first method
   causes a message to printed automatically when a certain condition
   occurs.  For example:
<programlisting>
EXEC SQL WHENEVER sqlerror sqlprint;
</programlisting>
   or
<programlisting>
EXEC SQL WHENEVER not found sqlprint;
</programlisting>
   This error handling remains enabled throughout the entire program.
  </para>

  <note>
   <para>
    This is <emphasis>not</emphasis> an exhaustive example of usage
    for the <command>EXEC SQL WHENEVER</command> statement.  Further
    examples of usage may be found in SQL manuals (e.g.,
    <citetitle>The LAN TIMES Guide to SQL</> by Groff and Weinberg).
   </para>
  </note>

  <para>
   For a more powerful error handling, the embedded SQL interface
   provides a <type>struct</> and a variable with the name
   <varname>sqlca</varname> as follows:
<programlisting>
struct sqlca
{
    char sqlcaid[8];
    long sqlabc;
    long sqlcode;
    struct
    {
        int sqlerrml;
        char sqlerrmc[70];
    } sqlerrm;
    char sqlerrp[8];

    long sqlerrd[6];
    /* 0: empty                                         */
    /* 1: OID of processed row if applicable            */
    /* 2: number of rows processed in an INSERT, UPDATE */
    /*    or DELETE statement                           */
    /* 3: empty                                         */
    /* 4: empty                                         */
    /* 5: empty                                         */

    char sqlwarn[8];
    /* 0: set to 'W' if at least one other is 'W'       */
    /* 1: if 'W' at least one character string          */
    /*    value was truncated when it was               */
    /*    stored into a host variable                   */
    /* 2: empty                                         */
    /* 3: empty                                         */
    /* 4: empty                                         */
    /* 5: empty                                         */
    /* 6: empty                                         */
    /* 7: empty                                         */

    char sqlext[8];
} sqlca;
</programlisting>
   (Many of the empty fields may be used in a future release.)
  </para>

  <para>
   If no error occurred in the last <acronym>SQL</acronym> statement,
   <literal>sqlca.sqlcode</literal> will be 0
   (<symbol>ECPG_NO_ERROR</>). If <literal>sqlca.sqlcode</literal> is
   less than zero, this is a serious error, like the database
   definition does not match the query. If it is greater than zero, it
   is a normal error like the table did not contain the requested row.
  </para>

  <para>
   <literal>sqlca.sqlerrm.sqlerrmc</literal> will contain a string
   that describes the error. The string ends with the line number in
   the source file.
  </para>

  <para>
   These are the errors that can occur:

   <variablelist>
    <varlistentry>
     <term><computeroutput>-12: Out of memory in line %d.</computeroutput></term>
     <listitem>
      <para>
       Should not normally occur. This indicates your virtual memory
       is exhausted.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-200 (ECPG_UNSUPPORTED): Unsupported type %s on line %d.</computeroutput></term>
     <listitem>
      <para>
       Should not normally occur. This indicates the preprocessor has
       generated something that the library does not know about.
       Perhaps you are running incompatible versions of the
       preprocessor and the library.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-201 (ECPG_TOO_MANY_ARGUMENTS): Too many arguments line %d.</computeroutput></term>
     <listitem>
      <para>
       This means that the server has returned more arguments than we
       have matching variables.  Perhaps you have forgotten a couple
       of the host variables in the <command>INTO
       :var1, :var2</command> list.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-202 (ECPG_TOO_FEW_ARGUMENTS): Too few arguments line %d.</computeroutput></term>
     <listitem>
      <para>
       This means that the server has returned fewer arguments than we
       have host variables. Perhaps you have too many host variables
       in the <command>INTO :var1,:var2</command> list.  </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-203 (ECPG_TOO_MANY_MATCHES): Too many matches line %d.</computeroutput></term>
     <listitem>
      <para>
       This means the query has returned multiple rows but the
       variables specified are not arrays. The
       <command>SELECT</command> command was not unique.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-204 (ECPG_INT_FORMAT): Not correctly formatted int type: %s line %d.</computeroutput></term>
     <listitem>
      <para>
       This means the host variable is of type <type>int</type> and
       the field in the <productname>PostgreSQL</productname> database
       is of another type and contains a value that cannot be
       interpreted as an <type>int</type>. The library uses
       <function>strtol()</function> for this conversion.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-205 (ECPG_UINT_FORMAT): Not correctly formatted unsigned type: %s line %d.</computeroutput></term>
     <listitem>
      <para>
       This means the host variable is of type <type>unsigned
       int</type> and the field in the
       <productname>PostgreSQL</productname> database is of another
       type and contains a value that cannot be interpreted as an
       <type>unsigned int</type>. The library uses
       <function>strtoul()</function> for this conversion.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-206 (ECPG_FLOAT_FORMAT): Not correctly formatted floating-point type: %s line %d.</computeroutput></term>
     <listitem>
      <para>
       This means the host variable is of type <type>float</type> and
       the field in the <productname>PostgreSQL</productname> 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.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-207 (ECPG_CONVERT_BOOL): Unable to convert %s to bool on line %d.</computeroutput></term>
     <listitem>
      <para>
       This means the host variable is of type <type>bool</type> and
       the field in the <productname>PostgreSQL</productname> database
       is neither <literal>'t'</> nor <literal>'f'</>.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-208 (ECPG_EMPTY): Empty query line %d.</computeroutput></term>
     <listitem>
      <para>
       The query was empty.  (This cannot normally happen in an
       embedded SQL program, so it may point to an internal error.)
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-209 (ECPG_MISSING_INDICATOR): NULL value without indicator in line %d.</computeroutput></term>
     <listitem>
      <para>
       A null value was returned and no null indicator variable was
       supplied.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-210 (ECPG_NO_ARRAY): Variable is not an array in line %d.</computeroutput></term>
     <listitem>
      <para>
       An ordinary variable was used in a place that requires an
       array.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-211 (ECPG_DATA_NOT_ARRAY): Data read from backend is not an array in line %d.</computeroutput></term>
     <listitem>
      <para>
       The database returned an ordinary variable in a place that
       requires array value.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-220 (ECPG_NO_CONN): No such connection %s in line %d.</computeroutput></term>
     <listitem>
      <para>
       The program tried to access a connection that does not exist.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-221 (ECPG_NOT_CONN): Not connected in line %d.</computeroutput></term>
     <listitem>
      <para>
       The program tried to access a connection that does exist but is
       not open.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-230 (ECPG_INVALID_STMT): Invalid statement name %s in line %d.</computeroutput></term>
     <listitem>
      <para>
       The statement you are trying to use has not been prepared.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-240 (ECPG_UNKNOWN_DESCRIPTOR): Descriptor %s not found in line %d.</computeroutput></term>
     <listitem>
      <para>
       The descriptor specified was not found. The statement you are
       trying to use has not been prepared.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-241 (ECPG_INVALID_DESCRIPTOR_INDEX): Descriptor index out of range in line %d.</computeroutput></term>
     <listitem>
      <para>
       The descriptor index specified was out of range.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-242 (ECPG_UNKNOWN_DESCRIPTOR_ITEM): Unknown descriptor item %s in line %d.</computeroutput></term>
     <listitem>
      <para>
       The descriptor specified was not found. The statement you are trying to use has not been prepared.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-243 (ECPG_VAR_NOT_NUMERIC): Variable is not a numeric type in line %d.</computeroutput></term>
     <listitem>
      <para>
       The database returned a numeric value and the variable was not
       numeric.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-244 (ECPG_VAR_NOT_CHAR): Variable is not a character type in line %d.</computeroutput></term>
     <listitem>
      <para>
       The database returned a non-numeric value and the variable was
       numeric.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-400 (ECPG_PGSQL): '%s' in line %d.</computeroutput></term>
     <listitem>
      <para>
       Some <productname>PostgreSQL</productname> error. The message
       contains the error message from the
       <productname>PostgreSQL</productname> server.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-401 (ECPG_TRANS): Error in transaction processing line %d.</computeroutput></term>
     <listitem>
      <para>
       The <productname>PostgreSQL</productname> server signaled that we cannot
       start, commit, or rollback the transaction.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>-402 (ECPG_CONNECT): Could not connect to database %s in line %d.</computeroutput></term>
     <listitem>
      <para>
       The connection attempt to the database did not work.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><computeroutput>100 (ECPG_NOT_FOUND): Data not found line %d.</computeroutput></term>
     <listitem>
      <para>
       This is a <quote>normal</quote> error that tells you that what
       you are querying cannot be found or you are at the end of the
       cursor.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </para>
 </sect1>

 <sect1 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>;
</programlisting>
   The embedded SQL preprocessor will look for a file named
   <literal><replaceable>filename</replaceable>.h</literal>,
   preprocess it, and include it in the resulting C output.  Thus,
   embedded SQL statements in the included file are handled correctly.
  </para>

  <para>
   Note that this is <emphasis>not</emphasis> the same as
<programlisting>
#include &lt;<replaceable>filename</replaceable>.h&gt;
</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>
 </sect1>

 <sect1 id="ecpg-process">
  <title>Processing Embedded SQL Programs</title>

  <para>
   Now that you have an idea how to form embedded SQL C programs, you
   probably want to know how to compile them.  Before compiling you
   run the file through the embedded <acronym>SQL</acronym>
   <acronym>C</acronym> preprocessor, which converts the
   <acronym>SQL</acronym> statements you used to special function
   calls.  After compiling, you must link with a special library that
   contains the needed functions. These functions fetch information
   from the arguments, perform the <acronym>SQL</acronym> command using
   the <application>libpq</application> interface, and put the result
   in the arguments specified for output.
  </para>

  <para>
   The preprocessor program is called <filename>ecpg</filename> and is
   included in a normal <productname>PostgreSQL</> installation.
   Embedded SQL programs are typically named with an extension
   <filename>.pgc</filename>.  If you have a program file called
   <filename>prog1.pgc</filename>, you can preprocess it by simply
   calling
<programlisting>
ecpg prog1.pgc
</programlisting>
   This will create a file called <filename>prog1.c</filename>.  If
   your input files do not follow the suggested naming pattern, you
   can specify the output file explicitly using the
   <option>-o</option> option.
  </para>

  <para>
   The preprocessed file can be compiled normally, for example:
<programlisting>
cc -c prog1.c
</programlisting>
   The generated C source files include headers files from the
   <productname>PostgreSQL</> installation, so if you installed
   <productname>PostgreSQL</> in a location that is not searched by
   default, you have to add an option such as
   <literal>-I/usr/local/pgsql/include</literal> to the compilation
   command line.
  </para>

  <para>
   To link an embedded SQL program, you need to include the
   <filename>libecpg</filename> library, like so:
<programlisting>
cc -o myprog prog1.o prog2.o ... -lecpg
</programlisting>
   Again, you might have to add an option like
   <literal>-L/usr/local/pgsql/lib</literal> to that command line.
  </para>

  <para>
   If you manage the build process of a larger project using
   <application>make</application>, it may 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>
 </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>
   </listitem>

   <listitem>
    <para>
     <function>ECPGstatus()</function> returns true if you
     are connected to a database and false if not.
    </para>
   </listitem>
  </itemizedlist>
 </sect1>

 <sect1 id="ecpg-develop">
  <title>Internals</title>

  <para>
   This section explain 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</> 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</></term>
      <listitem>
       <para>
        An <type>enum</> 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</></term>
       <listitem>
       <para>
        An <type>enum</> 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 is used because it opens the cursor as
    well.
   </para>

   <para>
    Here is a complete example describing the output of the
    preprocessor of a file <filename>foo.pgc</filename> (details may
    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>
/* Processed by ecpg (2.6.0) */
/* These two include files are added by the preprocessor */
#include &lt;ecpgtype.h&gt;;
#include &lt;ecpglib.h&gt;;

/* 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,&amp;(index),1L,1L,sizeof(int),
        ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT,
        ECPGt_int,&amp;(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>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->