ecpg.sgml 56.5 KB
Newer Older
1
<!--
2
$PostgreSQL: pgsql/doc/src/sgml/ecpg.sgml,v 1.61 2005/01/07 05:43:28 momjian Exp $
3 4
-->

5 6 7 8
<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>
Peter Eisentraut's avatar
Peter Eisentraut committed
9 10
 <indexterm zone="ecpg"><primary>C</primary></indexterm>
 <indexterm zone="ecpg"><primary>ECPG</primary></indexterm>
11 12 13

 <para>
  This chapter describes the embedded <acronym>SQL</acronym> package
14
  for <productname>PostgreSQL</productname>. It was written by
15
  Linus Tolke (<email>linus@epact.se</email>) and Michael Meskes
16 17 18
  (<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. 
19 20 21
 </para>

 <para>
22
  This documentation is quite incomplete.  But since this
23 24 25 26 27 28
  interface is standardized, additional information can be found in
  many resources about SQL.
 </para>

 <sect1 id="ecpg-concept">
  <title>The Concept</title>
29

Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
30
  <para>
31 32 33
   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
34
   is first passed through the embedded SQL preprocessor, which converts it
35
   to an ordinary C program, and afterwards it can be processed by a C
36
   compiler.
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
37
  </para>
38

39 40 41 42
  <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
43 44 45 46 47 48 49 50 51 52
   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</> 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.
53
  </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
54

55
  <para>
56
   As already stated, programs written for the embedded
Bruce Momjian's avatar
Bruce Momjian committed
57 58 59
   <acronym>SQL</acronym> interface are normal C programs with special
   code inserted to perform database-related actions.  This special
   code always has the form
60 61 62 63
<programlisting>
EXEC SQL ...;
</programlisting>
   These statements syntactically take the place of a C statement.
64 65
   Depending on the particular statement, they may appear at the
   global level or within a function.  Embedded
Bruce Momjian's avatar
Bruce Momjian committed
66 67
   <acronym>SQL</acronym> statements follow the case-sensitivity rules
   of normal <acronym>SQL</acronym> code, and not those of C.
68
  </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
69

70 71 72 73
  <para>
   The following sections explain all the embedded SQL statements.
  </para>
 </sect1>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
74

75 76
 <sect1 id="ecpg-connect">
  <title>Connecting to the Database Server</title>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
77

78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106
  <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>
107
      an SQL string literal containing one of the above forms
108 109 110 111 112
     </simpara>
    </listitem>

    <listitem>
     <simpara>
113
      a reference to a character variable containing one of the above forms (see examples)
114 115 116 117 118 119 120 121 122
     </simpara>
    </listitem>
 
    <listitem>
     <simpara>
      <literal>DEFAULT</literal>
     </simpara>
    </listitem>
   </itemizedlist>
123 124 125 126 127 128 129 130 131 132

   If you specify the connection target literally (that is, not
   through a variable reference) and you don't quote the value, then
   the case-insensitivity rules of normal SQL are applied.  In that
   case you can also double-quote the individual parameters separately
   as needed.  In practice, it is probably less error-prone to use a
   (single-quoted) string literal or a variable reference.  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 may be specified in that case.
133
  </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
134

135 136 137 138 139 140
  <para>
   There are also different ways to specify the user name:

   <itemizedlist>
    <listitem>
     <simpara>
141
      <literal><replaceable>username</replaceable></literal>
142 143 144 145 146
     </simpara>
    </listitem>

    <listitem>
     <simpara>
147
      <literal><replaceable>username</replaceable>/<replaceable>password</replaceable></literal>
148 149 150 151 152
     </simpara>
    </listitem>

    <listitem>
     <simpara>
153
      <literal><replaceable>username</replaceable> IDENTIFIED BY <replaceable>password</replaceable></literal>
154 155 156 157 158
     </simpara>
    </listitem>

    <listitem>
     <simpara>
159
      <literal><replaceable>username</replaceable> USING <replaceable>password</replaceable></literal>
160 161 162
     </simpara>
    </listitem>
   </itemizedlist>
163 164 165 166

   As above, the parameters <replaceable>username</replaceable> and
   <replaceable>password</replaceable> may be an SQL identifier, an
   SQL string literal, or a reference to a character variable.
167
  </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
168

169 170 171
  <para>
   The <replaceable>connection-name</replaceable> is used to handle
   multiple connections in one program.  It can be omitted if a
172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203
   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 'unix: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";
EXEC SQL END DECLARE SECTION;
 ...
EXEC SQL CONNECT TO :target USER :user;
</programlisting>
   The last form makes use of the variant referred to above as
   character variable reference.  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.
204 205
  </para>
 </sect1>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
206

207 208
 <sect1 id="ecpg-disconnect">
  <title>Closing a Connection</title>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
209

210 211 212
  <para>
   To close a connection, use the following statement:
<programlisting>
213
EXEC SQL DISCONNECT <optional><replaceable>connection</replaceable></optional>;
214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242
</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>
243 244 245 246 247 248 249 250

   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.
251 252
  </para>
 </sect1>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
253

254 255
 <sect1 id="ecpg-commands">
  <title>Running SQL Commands</title>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
256

257 258 259 260
  <para>
   Any SQL command can be run from within an embedded SQL application.
   Below are some examples of how to do that.
  </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
261

262 263 264 265 266 267 268 269
  <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>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
270

271 272 273 274 275 276 277
  <para>
   Inserting rows:
<programlisting>
EXEC SQL INSERT INTO foo (number, ascii) VALUES (9999, 'doodad');
EXEC SQL COMMIT;
</programlisting>
  </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
278

279 280 281 282 283 284 285
  <para>
   Deleting rows:
<programlisting>
EXEC SQL DELETE FROM foo WHERE number = 9999;
EXEC SQL COMMIT;
</programlisting>
  </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
286

287
  <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
288
   Single-row select:
289 290 291 292
<programlisting>
EXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE ascii = 'doodad';
</programlisting>
  </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
293

294
  <para>
295
   Select using cursors:
296 297 298 299
<programlisting>
EXEC SQL DECLARE foo_bar CURSOR FOR
    SELECT number, ascii FROM foo
    ORDER BY ascii;
300
EXEC SQL OPEN foo_bar;
301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316
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>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
317

318 319 320 321
  <para>
   The tokens of the form
   <literal>:<replaceable>something</replaceable></literal> are
   <firstterm>host variables</firstterm>, that is, they refer to
322 323
   variables in the C program.  They are explained in <xref
   linkend="ecpg-variables">.
324
  </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
325

326 327 328
  <para>
   In the default mode, statements are committed only when
   <command>EXEC SQL COMMIT</command> is issued. The embedded SQL
329 330 331
   interface also supports autocommit of transactions (similar to
   <application>libpq</> behavior) via the <option>-t</option> command-line
   option to <command>ecpg</command> (see below) or via the <literal>EXEC SQL
332
   SET AUTOCOMMIT TO ON</literal> statement. In autocommit mode, each
333
   command is automatically committed unless it is inside an explicit
334 335 336 337 338
   transaction block. This mode can be explicitly turned off using
   <literal>EXEC SQL SET AUTOCOMMIT TO OFF</literal>.
  </para>
 </sect1>

339 340
 <sect1 id="ecpg-set-connection">
  <title>Choosing a Connection</title>
341 342

  <para>
343 344 345 346
   The SQL statements shown in the previous section are executed on
   the current connection, that is, the most recently opened one.  If
   an application needs to manage multiple connections, then there are
   two ways to handle this.
347 348 349
  </para>

  <para>
350 351 352 353 354 355 356
   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.
357 358 359 360 361 362 363
      </para>

      <para>
      If your application uses multiple threads of execution, they cannot share a
      connection concurrently. You must either explicitly control access to the connection
      (using mutexes) or use a connection for each thread. If each thread uses its own connection,
      you will need to use the AT clause to specify which connection the thread will use.
364 365 366 367 368 369
  </para>

  <para>
   The second option is to execute a statement to switch the current
   connection.  That statement is:
<programlisting>
Peter Eisentraut's avatar
Peter Eisentraut committed
370
EXEC SQL SET CONNECTION <replaceable>connection-name</replaceable>;
371 372
</programlisting>
   This option is particularly convenient if many statements are to be
373
   executed on the same connection.  It is not thread-aware.
374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402
  </para>
 </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>.
  </para>

  <sect2>
   <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>
Peter Eisentraut's avatar
Peter Eisentraut committed
403
EXEC SQL INSERT INTO sometable VALUES (:v1, 'foo', :v2);
404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431
</programlisting>
    This statements 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.  In
    the SQL environment we call the references to C variables
    <firstterm>host variables</firstterm>.
   </para>
  </sect2>

  <sect2>
   <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
432
<programlisting>
433
EXEC SQL BEGIN DECLARE SECTION;
434
</programlisting>
435
    and ends with
436
<programlisting>
437
EXEC SQL END DECLARE SECTION;
438
</programlisting>
439 440
    Between those lines, there must be normal C variable declarations,
    such as
441 442 443 444
<programlisting>
int   x;
char  foo[16], bar[16];
</programlisting>
445 446 447 448 449 450
    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 a normal C
    variables, so there's no need to declare them again.  Variables
451
    that are not intended to be used in SQL commands can be declared
452 453 454 455 456 457 458 459 460 461
    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>
462 463
    The special type <type>VARCHAR</type> 
    is converted into a named <type>struct</> for every variable. A
464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555
    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>
  </sect2>

  <sect2>
   <title><command>SELECT INTO</command> and <command>FETCH INTO</command></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.
   </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>

   <para>
    Both of these methods only allow retrieving one row at a time.  If
    you need to process result sets that potentially contain more than
    one row, you need to use a cursor, as shown in the second example.
   </para>
  </sect2>

  <sect2>
   <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
556
    whether the datum is null, in which case the value of the real
557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592
    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.
   </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>
  </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.
593
  </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
594

595
  <para>
596 597 598 599 600 601 602
   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;

Peter Eisentraut's avatar
Peter Eisentraut committed
603
EXEC SQL EXECUTE IMMEDIATE :stmt;
604 605 606
</programlisting>
   You may not execute statements that retrieve data (e.g.,
   <command>SELECT</command>) this way.
607
  </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
608

609
  <para>
610 611 612 613 614 615 616 617 618 619 620 621
   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;

Peter Eisentraut's avatar
Peter Eisentraut committed
622
EXEC SQL PREPARE mystmt FROM :stmt;
623
 ...
Peter Eisentraut's avatar
Peter Eisentraut committed
624
EXEC SQL EXECUTE mystmt USING 42, 'foobar';
625 626 627 628 629 630 631 632 633 634
</programlisting>
   If the statement you are executing returns values, then add an
   <literal>INTO</literal> clause:
<programlisting>
EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "SELECT a, b, c FROM test1 WHERE a > ?";
int v1, v2;
VARCHAR v3;
EXEC SQL END DECLARE SECTION;

Peter Eisentraut's avatar
Peter Eisentraut committed
635
EXEC SQL PREPARE mystmt FROM :stmt;
636
 ...
Peter Eisentraut's avatar
Peter Eisentraut committed
637
EXEC SQL EXECUTE mystmt INTO v1, v2, v3 USING 37;
638 639 640 641
</programlisting>
   An <command>EXECUTE</command> command may have an
   <literal>INTO</literal> clause, a <literal>USING</literal> clause,
   both, or neither.
642
  </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
643

644
  <para>
645 646
   When you don't need the prepared statement anymore, you should
   deallocate it:
647
<programlisting>
648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676
EXEC SQL DEALLOCATE PREPARE <replaceable>name</replaceable>;
</programlisting>
  </para>
 </sect1>

 <sect1 id="ecpg-descriptors">
  <title>Using SQL Descriptor Areas</title>

  <para>
   An SQL descriptor area is a more sophisticated method for
   processing the result of a <command>SELECT</command> or
   <command>FETCH</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 may
   not be known ahead of time.
  </para>

  <para>
   An 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>;
677
</programlisting>
678
   The identifier serves as the <quote>variable name</quote> of the
679
   descriptor area.  <remark>The scope of the allocated descriptor is WHAT?.</remark>
680 681
   When you don't need the descriptor anymore, you should deallocate
   it:
682
<programlisting>
683
EXEC SQL DEALLOCATE DESCRIPTOR <replaceable>identifier</replaceable>;
684 685
</programlisting>
  </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
686

687
  <para>
688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716
   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 DESCRIPTOR mydesc;
</programlisting>
  </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>
717
     <term><literal>CARDINALITY</literal> (integer)</term>
718 719 720 721 722 723 724 725
     <listitem>
      <para>
       number of rows in the result set
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
726
     <term><literal>DATA</literal></term>
727 728 729 730 731 732 733 734 735
     <listitem>
      <para>
       actual data item (therefore, the data type of this field
       depends on the query)
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
736
     <term><literal>DATETIME_INTERVAL_CODE</literal> (integer)</term>
737 738 739 740 741 742 743 744
     <listitem>
      <para>
       ?
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
745
     <term><literal>DATETIME_INTERVAL_PRECISION</literal> (integer)</term>
746 747 748 749 750 751 752 753
     <listitem>
      <para>
       not implemented
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
754
     <term><literal>INDICATOR</literal> (integer)</term>
755 756 757 758 759 760 761 762
     <listitem>
      <para>
       the indicator (indicating a null value or a value truncation)
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
763
     <term><literal>KEY_MEMBER</literal> (integer)</term>
764 765 766 767 768 769 770 771
     <listitem>
      <para>
       not implemented
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
772
     <term><literal>LENGTH</literal> (integer)</term>
773 774 775 776 777 778 779 780
     <listitem>
      <para>
       length of the datum in characters
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
781
     <term><literal>NAME</literal> (string)</term>
782 783 784 785 786 787 788 789
     <listitem>
      <para>
       name of the column
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
790
     <term><literal>NULLABLE</literal> (integer)</term>
791 792 793 794 795 796 797 798
     <listitem>
      <para>
       not implemented
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
799
     <term><literal>OCTET_LENGTH</literal> (integer)</term>
800 801 802 803 804 805 806 807
     <listitem>
      <para>
       length of the character representation of the datum in bytes
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
808
     <term><literal>PRECISION</literal> (integer)</term>
809 810 811 812 813 814 815 816
     <listitem>
      <para>
       precision (for type <type>numeric</type>)
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
817
     <term><literal>RETURNED_LENGTH</literal> (integer)</term>
818 819 820 821 822 823 824 825
     <listitem>
      <para>
       length of the datum in characters
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
826
     <term><literal>RETURNED_OCTET_LENGTH</literal> (integer)</term>
827 828 829 830 831 832 833 834
     <listitem>
      <para>
       length of the character representation of the datum in bytes
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
835
     <term><literal>SCALE</literal> (integer)</term>
836 837 838 839 840 841 842 843
     <listitem>
      <para>
       scale (for type <type>numeric</type>)
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
844
     <term><literal>TYPE</literal> (integer)</term>
845 846 847 848 849 850 851
     <listitem>
      <para>
       numeric code of the data type of the column
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
852 853
  </para>
 </sect1>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
854

855 856
 <sect1 id="ecpg-errors">
  <title>Error Handling</title>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
857

858
  <para>
859 860 861 862 863 864 865 866 867 868 869 870
   This section describes how you can handle exceptional conditions
   and warnings in an embedded SQL program.  There are several
   nonexclusive facilities for this.
  </para>

  <sect2>
   <title>Setting Callbacks</title>

   <para>
    One simple method to catch errors and warnings is to set a
    specific action to be executed whenever a particular condition
    occurs.  In general:
871
<programlisting>
872
EXEC SQL WHENEVER <replaceable>condition</replaceable> <replaceable>action</replaceable>;
873
</programlisting>
874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988
   </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>BREAK</literal></term>
      <listitem>
       <para>
        Execute the C statement <literal>break</literal>.  This should
        only be used in loops or <literal>switch</literal> statements.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>CALL <replaceable>name</replaceable> (<replaceable>args</replaceable>)</literal></term>
      <term><literal>DO <replaceable>name</replaceable> (<replaceable>args</replaceable>)</literal></term>
      <listitem>
       <para>
        Call the specified C functions with the specified arguments.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>

    The SQL standard only provides for the actions
    <literal>CONTINUE</literal> and <literal>GOTO</literal> (and
    <literal>GO TO</literal>).
   </para>

   <para>
    Here is an example that you might want to use in a simple program.
    It prints a simple message when a warning occurs and aborts the
    program when an error happens.
989
<programlisting>
990 991
EXEC SQL WHENEVER SQLWARNING SQLPRINT;
EXEC SQL WHENEVER SQLERROR STOP;
992
</programlisting>
993
   </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
994

995
   <para>
996 997 998 999 1000 1001 1002
    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.
1003
    So neither of the two following C program excerpts will have the
1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038
    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>
1039
   </para>
1040
  </sect2>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1041

1042 1043 1044 1045
  <sect2>
   <title>sqlca</title>

   <para>
1046
    For more powerful error handling, the embedded SQL interface
1047 1048
    provides a global variable with the name <varname>sqlca</varname>
    that has the following structure:
1049
<programlisting>
1050
struct
1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062
{
    char sqlcaid[8];
    long sqlabc;
    long sqlcode;
    struct
    {
        int sqlerrml;
        char sqlerrmc[70];
    } sqlerrm;
    char sqlerrp[8];
    long sqlerrd[6];
    char sqlwarn[8];
1063
    char sqlstate[5];
1064 1065
} sqlca;
</programlisting>
1066
    (In a multithreaded program, every thread automatically gets its
1067
    own copy of <varname>sqlca</varname>.  This works similarly to the
1068 1069 1070
    handling of the standard C global variable
    <varname>errno</varname>.)
   </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1071

1072 1073 1074 1075 1076 1077
   <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>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1078

1079 1080 1081 1082
   <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
1083
    <literal>"00000"</literal>.  If a warning or error occurred, then
1084 1085 1086 1087 1088 1089 1090 1091
    <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>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1092

1093 1094 1095 1096 1097 1098 1099
   <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>
1100

1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134
   <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).
   </para>

   <para>
    In case of a warning, <literal>sqlca.sqlwarn[2]</literal> is set
    to <literal>W</literal>.  (In all other cases, it is set to
    something different from <literal>W</literal>.)  If
    <literal>sqlca.sqlwarn[1]</literal> is set to
    <literal>W</literal>, then a value was truncated when it was
    stored in a host variable.  <literal>sqlca.sqlwarn[0]</literal> is
    set to <literal>W</literal> if any of the other elements are set
    to indicate a warning.
   </para>

   <para>
    The fields <structfield>sqlcaid</structfield>,
    <structfield>sqlcabc</structfield>,
    <structfield>sqlerrp</structfield>, and the remaining elements of
    <structfield>sqlerrd</structfield> and
    <structfield>sqlwarn</structfield> currently contain no useful
    information.
   </para>

   <para>
    The structure <varname>sqlca</varname> is not defined in the SQL
    standard, but is implemented in several other SQL database
1135
    systems.  The definitions are similar at the core, but if you want
1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161
    to write portable applications, then you should investigate the
    different implementations carefully.
   </para>
  </sect2>

  <sect2>
   <title><literal>SQLSTATE</literal> vs <literal>SQLCODE</literal></title>

   <para>
    The fields <literal>sqlca.sqlstate</literal> and
    <literal>sqlca.sqlcode</literal> are two different schemes that
    provide error codes.  Both are specified in the SQL standard, but
    <literal>SQLCODE</literal> has been marked deprecated in the 1992
    edition of the standard and has been dropped in the 1999 edition.
    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
1162 1163
    <literal>00000</literal>.  The <literal>SQLSTATE</literal> codes are for
    the most part defined in the SQL standard.  The
1164 1165 1166
    <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
1167 1168
    throughout all applications.  For further information see
    <xref linkend="errcodes-appendix">.
1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179
   </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
1180 1181 1182 1183 1184
    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
1185 1186 1187 1188
    <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
1189 1190
    <literal>SQLSTATE</literal> listing in <xref linkend="errcodes-appendix">
    in each case.
1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202
   </para>

   <para>
    These are the assigned <literal>SQLCODE</literal> values:

    <variablelist>
     <varlistentry>
      <term>-12 (<symbol>ECPG_OUT_OF_MEMORY</symbol>)</term>
      <listitem>
       <para>
        Indicates that your virtual memory is exhausted. (SQLSTATE
        YE001)
1203 1204 1205 1206 1207
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1208
     <term>-200 (<symbol>ECPG_UNSUPPORTED</symbol>)</term>
1209 1210
     <listitem>
      <para>
1211 1212 1213 1214
       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)
1215 1216 1217 1218 1219
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1220
     <term>-201 (<symbol>ECPG_TOO_MANY_ARGUMENTS</symbol>)</term>
1221 1222
     <listitem>
      <para>
1223 1224
       This means that the command specified more host variables than
       the command expected.  (SQLSTATE 07001 or 07002)
1225 1226 1227 1228 1229
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1230
     <term>-202 (<symbol>ECPG_TOO_FEW_ARGUMENTS</symbol>)</term>
1231 1232
     <listitem>
      <para>
1233 1234 1235
       This means that the command specified fewer host variables than
       the command expected.  (SQLSTATE 07001 or 07002)
      </para>
1236 1237 1238 1239
     </listitem>
    </varlistentry>

    <varlistentry>
1240
     <term>-203 (<symbol>ECPG_TOO_MANY_MATCHES</symbol>)</term>
1241 1242
     <listitem>
      <para>
1243 1244 1245
       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)
1246 1247 1248 1249 1250
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1251
     <term>-204 (<symbol>ECPG_INT_FORMAT</symbol>)</term>
1252 1253
     <listitem>
      <para>
1254 1255 1256 1257 1258
       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)
1259 1260 1261 1262 1263
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1264
     <term>-205 (<symbol>ECPG_UINT_FORMAT</symbol>)</term>
1265 1266
     <listitem>
      <para>
1267 1268 1269 1270 1271
       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)
1272 1273 1274 1275 1276
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1277
     <term>-206 (<symbol>ECPG_FLOAT_FORMAT</symbol>)</term>
1278 1279
     <listitem>
      <para>
1280 1281 1282 1283 1284
       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)
1285 1286 1287 1288 1289
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1290
     <term>-207 (<symbol>ECPG_CONVERT_BOOL</symbol>)</term>
1291 1292 1293
     <listitem>
      <para>
       This means the host variable is of type <type>bool</type> and
1294 1295
       the datum in the database is neither <literal>'t'</> nor
       <literal>'f'</>.  (SQLSTATE 42804)
1296 1297 1298 1299 1300
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1301
     <term>-208 (<symbol>ECPG_EMPTY</symbol>)</term>
1302 1303
     <listitem>
      <para>
1304 1305 1306 1307
       The statement sent to the <productname>PostgreSQL</productname>
       server was empty.  (This cannot normally happen in an embedded
       SQL program, so it may point to an internal error.)  (SQLSTATE
       YE002)
1308 1309 1310 1311 1312
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1313
     <term>-209 (<symbol>ECPG_MISSING_INDICATOR</symbol>)</term>
1314 1315 1316
     <listitem>
      <para>
       A null value was returned and no null indicator variable was
1317
       supplied.  (SQLSTATE 22002)
1318 1319 1320 1321 1322
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1323
     <term>-210 (<symbol>ECPG_NO_ARRAY</symbol>)</term>
1324 1325 1326
     <listitem>
      <para>
       An ordinary variable was used in a place that requires an
1327
       array.  (SQLSTATE 42804)
1328 1329 1330 1331 1332
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1333
     <term>-211 (<symbol>ECPG_DATA_NOT_ARRAY</symbol>)</term>
1334 1335 1336
     <listitem>
      <para>
       The database returned an ordinary variable in a place that
1337
       requires array value.  (SQLSTATE 42804)
1338 1339 1340 1341 1342
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1343
     <term>-220 (<symbol>ECPG_NO_CONN</symbol>)</term>
1344 1345 1346
     <listitem>
      <para>
       The program tried to access a connection that does not exist.
1347
       (SQLSTATE 08003)
1348 1349 1350 1351 1352
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1353
     <term>-221 (<symbol>ECPG_NOT_CONN</symbol>)</term>
1354 1355 1356
     <listitem>
      <para>
       The program tried to access a connection that does exist but is
1357
       not open.  (This is an internal error.)  (SQLSTATE YE002)
1358 1359 1360 1361 1362
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1363
     <term>-230 (<symbol>ECPG_INVALID_STMT</symbol>)</term>
1364 1365 1366
     <listitem>
      <para>
       The statement you are trying to use has not been prepared.
1367
       (SQLSTATE 26000)
1368 1369 1370 1371 1372
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1373
     <term>-240 (<symbol>ECPG_UNKNOWN_DESCRIPTOR</symbol>)</term>
1374 1375
     <listitem>
      <para>
1376 1377
       The descriptor specified was not found.  The statement you are
       trying to use has not been prepared.  (SQLSTATE 33000)
1378 1379 1380 1381 1382
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1383
     <term>-241 (<symbol>ECPG_INVALID_DESCRIPTOR_INDEX</symbol>)</term>
1384 1385
     <listitem>
      <para>
1386 1387
       The descriptor index specified was out of range.  (SQLSTATE
       07009)
1388 1389 1390 1391 1392
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1393
     <term>-242 (<symbol>ECPG_UNKNOWN_DESCRIPTOR_ITEM</symbol>)</term>
1394 1395
     <listitem>
      <para>
1396 1397
       An invalid descriptor item was requested.  (This is an internal
       error.)  (SQLSTATE YE002)
1398 1399 1400 1401 1402
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1403
     <term>-243 (<symbol>ECPG_VAR_NOT_NUMERIC</symbol>)</term>
1404 1405
     <listitem>
      <para>
1406 1407 1408
       During the execution of a dynamic statement, the database
       returned a numeric value and the host variable was not numeric.
       (SQLSTATE 07006)
1409 1410 1411 1412 1413
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1414
     <term>-244 (<symbol>ECPG_VAR_NOT_CHAR</symbol>)</term>
1415 1416
     <listitem>
      <para>
1417 1418 1419
       During the execution of a dynamic statement, the database
       returned a non-numeric value and the host variable was numeric.
       (SQLSTATE 07006)
1420 1421 1422 1423 1424
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1425
     <term>-400 (<symbol>ECPG_PGSQL</symbol>)</term>
1426 1427
     <listitem>
      <para>
1428 1429
       Some error caused by the <productname>PostgreSQL</productname>
       server.  The message contains the error message from the
1430
       <productname>PostgreSQL</productname> server.
1431 1432 1433 1434 1435
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1436
     <term>-401 (<symbol>ECPG_TRANS</symbol>)</term>
1437 1438
     <listitem>
      <para>
1439 1440 1441
       The <productname>PostgreSQL</productname> server signaled that
       we cannot start, commit, or rollback the transaction.
       (SQLSTATE 08007)
1442 1443 1444 1445 1446
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1447
     <term>-402 (<symbol>ECPG_CONNECT</symbol>)</term>
1448 1449
     <listitem>
      <para>
1450 1451
       The connection attempt to the database did not succeed.
       (SQLSTATE 08001)
1452 1453 1454 1455 1456
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1457
     <term>100 (<symbol>ECPG_NOT_FOUND</symbol>)</term>
1458 1459
     <listitem>
      <para>
1460 1461 1462
       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)
1463 1464 1465 1466 1467
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </para>
1468
  </sect2>
1469
 </sect1>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1470

1471 1472
 <sect1 id="ecpg-include">
  <title>Including Files</title>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1473

1474 1475 1476 1477 1478 1479 1480 1481 1482 1483
  <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>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1484

1485 1486 1487 1488 1489
  <para>
   Note that this is <emphasis>not</emphasis> the same as
<programlisting>
#include &lt;<replaceable>filename</replaceable>.h&gt;
</programlisting>
1490
   because this file would not be subject to SQL command preprocessing.
1491 1492 1493 1494
   Naturally, you can continue to use the C
   <literal>#include</literal> directive to include other header
   files.
  </para>
1495

1496 1497 1498 1499 1500 1501 1502 1503
  <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>
1504

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

1508 1509 1510 1511 1512 1513 1514 1515
  <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
1516
   from the arguments, perform the <acronym>SQL</acronym> command using
1517 1518 1519
   the <application>libpq</application> interface, and put the result
   in the arguments specified for output.
  </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1520

1521 1522
  <para>
   The preprocessor program is called <filename>ecpg</filename> and is
Bruce Momjian's avatar
Bruce Momjian committed
1523 1524
   included in a normal <productname>PostgreSQL</> installation.
   Embedded SQL programs are typically named with an extension
1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535
   <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>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1536

1537
  <para>
1538
   The preprocessed file can be compiled normally, for example:
1539 1540 1541
<programlisting>
cc -c prog1.c
</programlisting>
1542
   The generated C source files include header files from the
Bruce Momjian's avatar
Bruce Momjian committed
1543 1544 1545 1546 1547
   <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.
1548
  </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1549

1550 1551 1552 1553 1554 1555 1556 1557 1558
  <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>
1559

1560 1561 1562 1563 1564 1565
  <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
1566

1567 1568 1569 1570
%.c: %.pgc
        $(ECPG) $<
</programlisting>
  </para>
1571

1572 1573
  <para>
   The complete syntax of the <command>ecpg</command> command is
1574
   detailed in <xref linkend="app-ecpg">.
1575
  </para>
1576 1577

  <para>
1578 1579 1580
   The <application>ecpg</application> library is thread-safe if it is built
   using the <option>--enable-thread-safety</> command-line option to
   <filename>configure</filename>.  (You might need to use other threading
1581 1582
   command-line options to compile your client code.)
  </para>
1583
 </sect1>
1584

1585 1586
 <sect1 id="ecpg-library">
  <title>Library Functions</title>
1587

1588 1589 1590 1591 1592 1593 1594
  <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>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1595

1596 1597 1598 1599 1600 1601
  <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
1602 1603 1604 1605 1606 1607
     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.
1608 1609
    </para>
   </listitem>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1610

1611 1612
   <listitem>
    <para>
1613 1614 1615 1616 1617
     <function>ECPGstatus(int <replaceable>lineno</replaceable>,
     const char* <replaceable>connection_name</replaceable>)</function>
     returns true if you are connected to a database and false if not.
     <replaceable>connection_name</replaceable> can be <literal>NULL</> 
     if a single connection is being used.
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1618
    </para>
1619 1620 1621
   </listitem>
  </itemizedlist>
 </sect1>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1622

1623
 <sect1 id="ecpg-develop">
1624
  <title>Internals</title>
1625 1626

  <para>
1627
   This section explains how <application>ECPG</application> works
1628
   internally. This information can occasionally be useful to help
1629
   users understand how to use <application>ECPG</application>.
1630
  </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1631 1632

   <para>
1633 1634 1635 1636 1637
    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.
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1638 1639 1640
   </para>

   <para>
1641
    When it sees an <command>EXEC SQL</command> statement, it
1642 1643
    intervenes and changes it. The command starts with <command>EXEC
    SQL</command> and ends with <command>;</command>. Everything in
1644 1645
    between is treated as an <acronym>SQL</acronym> statement and
    parsed for variable substitution.
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1646 1647 1648
   </para>

   <para>
1649 1650 1651
    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
1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727
    <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>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1728 1729 1730
   </para>

   <para>
1731
    For every variable that is part of the <acronym>SQL</acronym>
1732
    command, the function gets ten arguments:
1733

1734
    <orderedlist>
1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772
     <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>
1773
       A pointer to the indicator variable.
1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784
      </para>
     </listitem>

     <listitem>
      <para>
       0
      </para>
     </listitem>

     <listitem>
      <para>
1785
       The number of elements in the indicator array (for array fetches).
1786 1787 1788 1789 1790 1791 1792 1793 1794
      </para>
     </listitem>

     <listitem>
      <para>
       The offset to the next element in the indicator array (for
       array fetches).
      </para>
     </listitem>
1795
    </orderedlist>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1796 1797
   </para>

1798 1799 1800
   <para>
    Note that not all SQL commands are treated in this way.  For
    instance, an open cursor statement like
1801
<programlisting>
1802
EXEC SQL OPEN <replaceable>cursor</replaceable>;
1803
</programlisting>
1804
    is not copied to the output. Instead, the cursor's
1805 1806
    <command>DECLARE</> command is used at the position of the <command>OPEN</> command
    because it indeed opens the cursor.
1807
   </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1808

1809 1810 1811 1812
   <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):
1813
<programlisting>
1814
EXEC SQL BEGIN DECLARE SECTION;
1815 1816
int index;
int result;
1817
EXEC SQL END DECLARE SECTION;
1818
...
1819
EXEC SQL SELECT res INTO :result FROM mytable WHERE index = :index;
1820
</programlisting>
1821
    is translated into:
1822
<programlisting>
Michael Meskes's avatar
Michael Meskes committed
1823
/* Processed by ecpg (2.6.0) */
1824
/* These two include files are added by the preprocessor */
Michael Meskes's avatar
Michael Meskes committed
1825 1826 1827
#include &lt;ecpgtype.h&gt;;
#include &lt;ecpglib.h&gt;;

1828 1829
/* exec sql begin declare section */

Michael Meskes's avatar
Michael Meskes committed
1830 1831
#line 1 "foo.pgc"

1832 1833 1834 1835
 int index;
 int result;
/* exec sql end declare section */
...
1836
ECPGdo(__LINE__, NULL, "SELECT res FROM mytable WHERE index = ?     ",
1837
        ECPGt_int,&amp;(index),1L,1L,sizeof(int),
Michael Meskes's avatar
Michael Meskes committed
1838 1839 1840 1841
        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"
1842
</programlisting>
1843
    (The indentation here is added for readability and not
1844 1845 1846 1847
    something the preprocessor does.)
   </para>
 </sect1>
</chapter>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860

<!-- 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
1861
sgml-local-catalogs:("/usr/lib/sgml/catalog")
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1862 1863 1864
sgml-local-ecat-files:nil
End:
-->