2
$PostgreSQL: pgsql/doc/src/sgml/ecpg.sgml,v 1.62.4.1 2005-01-22 23:05:47 momjian Exp $
6
<title><application>ECPG</application> - Embedded <acronym>SQL</acronym> in C</title>
8
<indexterm zone="ecpg"><primary>embedded SQL</primary><secondary>in C</secondary></indexterm>
9
<indexterm zone="ecpg"><primary>C</primary></indexterm>
10
<indexterm zone="ecpg"><primary>ECPG</primary></indexterm>
13
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
(<email>meskes@postgresql.org</email>). Originally it was written to work with
17
<acronym>C</acronym>. It also works with <acronym>C++</acronym>, but
18
it does not recognize all <acronym>C++</acronym> constructs yet.
22
This documentation is quite incomplete. But since this
23
interface is standardized, additional information can be found in
24
many resources about SQL.
27
<sect1 id="ecpg-concept">
28
<title>The Concept</title>
31
An embedded SQL program consists of code written in an ordinary
32
programming language, in this case C, mixed with SQL commands in
33
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
40
Embedded <acronym>SQL</acronym> has advantages over other methods
41
for handling <acronym>SQL</acronym> commands from C code. First, it
42
takes care of the tedious passing of information to and from
43
variables in your <acronym>C</acronym> program. Second, the SQL
44
code in the program is checked at build time for syntactical
45
correctness. Third, embedded <acronym>SQL</acronym> in C is
46
specified in the <acronym>SQL</acronym> standard and supported by
47
many other <acronym>SQL</acronym> database systems. The
48
<productname>PostgreSQL</> implementation is designed to match this
49
standard as much as possible, and it is usually possible to port
50
embedded <acronym>SQL</acronym> programs written for other SQL
51
databases to <productname>PostgreSQL</productname> with relative
56
As already stated, programs written for the embedded
57
<acronym>SQL</acronym> interface are normal C programs with special
58
code inserted to perform database-related actions. This special
59
code always has the form
63
These statements syntactically take the place of a C statement.
64
Depending on the particular statement, they may appear at the
65
global level or within a function. Embedded
66
<acronym>SQL</acronym> statements follow the case-sensitivity rules
67
of normal <acronym>SQL</acronym> code, and not those of C.
71
The following sections explain all the embedded SQL statements.
75
<sect1 id="ecpg-connect">
76
<title>Connecting to the Database Server</title>
79
One connects to a database using the following statement:
81
EXEC SQL CONNECT TO <replaceable>target</replaceable> <optional>AS <replaceable>connection-name</replaceable></optional> <optional>USER <replaceable>user-name</replaceable></optional>;
83
The <replaceable>target</replaceable> can be specified in the
89
<literal><replaceable>dbname</><optional>@<replaceable>hostname</></optional><optional>:<replaceable>port</></optional></literal>
95
<literal>tcp:postgresql://<replaceable>hostname</><optional>:<replaceable>port</></optional><optional>/<replaceable>dbname</></optional><optional>?<replaceable>options</></optional></literal>
101
<literal>unix:postgresql://<replaceable>hostname</><optional>:<replaceable>port</></optional><optional>/<replaceable>dbname</></optional><optional>?<replaceable>options</></optional></literal>
107
an SQL string literal containing one of the above forms
113
a reference to a character variable containing one of the above forms (see examples)
119
<literal>DEFAULT</literal>
124
If you specify the connection target literally (that is, not
125
through a variable reference) and you don't quote the value, then
126
the case-insensitivity rules of normal SQL are applied. In that
127
case you can also double-quote the individual parameters separately
128
as needed. In practice, it is probably less error-prone to use a
129
(single-quoted) string literal or a variable reference. The
130
connection target <literal>DEFAULT</literal> initiates a connection
131
to the default database under the default user name. No separate
132
user name or connection name may be specified in that case.
136
There are also different ways to specify the user name:
141
<literal><replaceable>username</replaceable></literal>
147
<literal><replaceable>username</replaceable>/<replaceable>password</replaceable></literal>
153
<literal><replaceable>username</replaceable> IDENTIFIED BY <replaceable>password</replaceable></literal>
159
<literal><replaceable>username</replaceable> USING <replaceable>password</replaceable></literal>
164
As above, the parameters <replaceable>username</replaceable> and
165
<replaceable>password</replaceable> may be an SQL identifier, an
166
SQL string literal, or a reference to a character variable.
170
The <replaceable>connection-name</replaceable> is used to handle
171
multiple connections in one program. It can be omitted if a
172
program uses only one connection. The most recently opened
173
connection becomes the current connection, which is used by default
174
when an SQL statement is to be executed (see later in this
179
Here are some examples of <command>CONNECT</command> statements:
181
EXEC SQL CONNECT TO mydb@sql.mydomain.com;
183
EXEC SQL CONNECT TO 'unix:postgresql://sql.mydomain.com/mydb' AS myconnection USER john;
185
EXEC SQL BEGIN DECLARE SECTION;
186
const char *target = "mydb@sql.mydomain.com";
187
const char *user = "john";
188
EXEC SQL END DECLARE SECTION;
190
EXEC SQL CONNECT TO :target USER :user;
192
The last form makes use of the variant referred to above as
193
character variable reference. You will see in later sections how C
194
variables can be used in SQL statements when you prefix them with a
199
Be advised that the format of the connection target is not
200
specified in the SQL standard. So if you want to develop portable
201
applications, you might want to use something based on the last
202
example above to encapsulate the connection target string
207
<sect1 id="ecpg-disconnect">
208
<title>Closing a Connection</title>
211
To close a connection, use the following statement:
213
EXEC SQL DISCONNECT <optional><replaceable>connection</replaceable></optional>;
215
The <replaceable>connection</replaceable> can be specified
216
in the following ways:
221
<literal><replaceable>connection-name</replaceable></literal>
227
<literal>DEFAULT</literal>
233
<literal>CURRENT</literal>
239
<literal>ALL</literal>
244
If no connection name is specified, the current connection is
249
It is good style that an application always explicitly disconnect
250
from every connection it opened.
254
<sect1 id="ecpg-commands">
255
<title>Running SQL Commands</title>
258
Any SQL command can be run from within an embedded SQL application.
259
Below are some examples of how to do that.
265
EXEC SQL CREATE TABLE foo (number integer, ascii char(16));
266
EXEC SQL CREATE UNIQUE INDEX num1 ON foo(number);
274
EXEC SQL INSERT INTO foo (number, ascii) VALUES (9999, 'doodad');
282
EXEC SQL DELETE FROM foo WHERE number = 9999;
290
EXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE ascii = 'doodad';
295
Select using cursors:
297
EXEC SQL DECLARE foo_bar CURSOR FOR
298
SELECT number, ascii FROM foo
300
EXEC SQL OPEN foo_bar;
301
EXEC SQL FETCH foo_bar INTO :FooBar, DooDad;
303
EXEC SQL CLOSE foo_bar;
319
The tokens of the form
320
<literal>:<replaceable>something</replaceable></literal> are
321
<firstterm>host variables</firstterm>, that is, they refer to
322
variables in the C program. They are explained in <xref
323
linkend="ecpg-variables">.
327
In the default mode, statements are committed only when
328
<command>EXEC SQL COMMIT</command> is issued. The embedded SQL
329
interface also supports autocommit of transactions (similar to
330
<application>libpq</> behavior) via the <option>-t</option> command-line
331
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
transaction block. This mode can be explicitly turned off using
335
<literal>EXEC SQL SET AUTOCOMMIT TO OFF</literal>.
339
<sect1 id="ecpg-set-connection">
340
<title>Choosing a Connection</title>
343
The SQL statements shown in the previous section are executed on
344
the current connection, that is, the most recently opened one. If
345
an application needs to manage multiple connections, then there are
346
two ways to handle this.
350
The first option is to explicitly choose a connection for each SQL
351
statement, for example
353
EXEC SQL AT <replaceable>connection-name</replaceable> SELECT ...;
355
This option is particularly suitable if the application needs to
356
use several connections in mixed order.
360
If your application uses multiple threads of execution, they cannot share a
361
connection concurrently. You must either explicitly control access to the connection
362
(using mutexes) or use a connection for each thread. If each thread uses its own connection,
363
you will need to use the AT clause to specify which connection the thread will use.
367
The second option is to execute a statement to switch the current
368
connection. That statement is:
370
EXEC SQL SET CONNECTION <replaceable>connection-name</replaceable>;
372
This option is particularly convenient if many statements are to be
373
executed on the same connection. It is not thread-aware.
377
<sect1 id="ecpg-variables">
378
<title>Using Host Variables</title>
381
In <xref linkend="ecpg-commands"> you saw how you can execute SQL
382
statements from an embedded SQL program. Some of those statements
383
only used fixed values and did not provide a way to insert
384
user-supplied values into statements or have the program process
385
the values returned by the query. Those kinds of statements are
386
not really useful in real applications. This section explains in
387
detail how you can pass data between your C program and the
388
embedded SQL statements using a simple mechanism called
389
<firstterm>host variables</firstterm>.
393
<title>Overview</title>
396
Passing data between the C program and the SQL statements is
397
particularly simple in embedded SQL. Instead of having the
398
program paste the data into the statement, which entails various
399
complications, such as properly quoting the value, you can simply
400
write the name of a C variable into the SQL statement, prefixed by
401
a colon. For example:
403
EXEC SQL INSERT INTO sometable VALUES (:v1, 'foo', :v2);
405
This statements refers to two C variables named
406
<varname>v1</varname> and <varname>v2</varname> and also uses a
407
regular SQL string literal, to illustrate that you are not
408
restricted to use one kind of data or the other.
412
This style of inserting C variables in SQL statements works
413
anywhere a value expression is expected in an SQL statement. In
414
the SQL environment we call the references to C variables
415
<firstterm>host variables</firstterm>.
420
<title>Declare Sections</title>
423
To pass data from the program to the database, for example as
424
parameters in a query, or to pass data from the database back to
425
the program, the C variables that are intended to contain this
426
data need to be declared in specially marked sections, so the
427
embedded SQL preprocessor is made aware of them.
431
This section starts with
433
EXEC SQL BEGIN DECLARE SECTION;
437
EXEC SQL END DECLARE SECTION;
439
Between those lines, there must be normal C variable declarations,
443
char foo[16], bar[16];
445
You can have as many declare sections in a program as you like.
449
The declarations are also echoed to the output file as a normal C
450
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
normally outside these special sections.
456
The definition of a structure or union also must be listed inside
457
a <literal>DECLARE</> section. Otherwise the preprocessor cannot
458
handle these types since it does not know the definition.
462
The special type <type>VARCHAR</type>
463
is converted into a named <type>struct</> for every variable. A
470
struct varchar_var { int len; char arr[180]; } var;
472
This structure is suitable for interfacing with SQL datums of type
473
<type>varchar</type>.
478
<title><command>SELECT INTO</command> and <command>FETCH INTO</command></title>
481
Now you should be able to pass data generated by your program into
482
an SQL command. But how do you retrieve the results of a query?
483
For that purpose, embedded SQL provides special variants of the
484
usual commands <command>SELECT</command> and
485
<command>FETCH</command>. These commands have a special
486
<literal>INTO</literal> clause that specifies which host variables
487
the retrieved values are to be stored in.
495
* CREATE TABLE test1 (a int, b varchar(50));
498
EXEC SQL BEGIN DECLARE SECTION;
501
EXEC SQL END DECLARE SECTION;
505
EXEC SQL SELECT a, b INTO :v1, :v2 FROM test;
507
So the <literal>INTO</literal> clause appears between the select
508
list and the <literal>FROM</literal> clause. The number of
509
elements in the select list and the list after
510
<literal>INTO</literal> (also called the target list) must be
515
Here is an example using the command <command>FETCH</command>:
517
EXEC SQL BEGIN DECLARE SECTION;
520
EXEC SQL END DECLARE SECTION;
524
EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test;
530
EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2;
534
Here the <literal>INTO</literal> clause appears after all the
539
Both of these methods only allow retrieving one row at a time. If
540
you need to process result sets that potentially contain more than
541
one row, you need to use a cursor, as shown in the second example.
546
<title>Indicators</title>
549
The examples above do not handle null values. In fact, the
550
retrieval examples will raise an error if they fetch a null value
551
from the database. To be able to pass null values to the database
552
or retrieve null values from the database, you need to append a
553
second host variable specification to each host variable that
554
contains data. This second host variable is called the
555
<firstterm>indicator</firstterm> and contains a flag that tells
556
whether the datum is null, in which case the value of the real
557
host variable is ignored. Here is an example that handles the
558
retrieval of null values correctly:
560
EXEC SQL BEGIN DECLARE SECTION;
563
EXEC SQL END DECLARE SECTION:
567
EXEC SQL SELECT b INTO :val :val_ind FROM test1;
569
The indicator variable <varname>val_ind</varname> will be zero if
570
the value was not null, and it will be negative if the value was
575
The indicator has another function: if the indicator value is
576
positive, it means that the value is not null, but it was
577
truncated when it was stored in the host variable.
582
<sect1 id="ecpg-dynamic">
583
<title>Dynamic SQL</title>
586
In many cases, the particular SQL statements that an application
587
has to execute are known at the time the application is written.
588
In some cases, however, the SQL statements are composed at run time
589
or provided by an external source. In these cases you cannot embed
590
the SQL statements directly into the C source code, but there is a
591
facility that allows you to call arbitrary SQL statements that you
592
provide in a string variable.
596
The simplest way to execute an arbitrary SQL statement is to use
597
the command <command>EXECUTE IMMEDIATE</command>. For example:
599
EXEC SQL BEGIN DECLARE SECTION;
600
const char *stmt = "CREATE TABLE test1 (...);";
601
EXEC SQL END DECLARE SECTION;
603
EXEC SQL EXECUTE IMMEDIATE :stmt;
605
You may not execute statements that retrieve data (e.g.,
606
<command>SELECT</command>) this way.
610
A more powerful way to execute arbitrary SQL statements is to
611
prepare them once and execute the prepared statement as often as
612
you like. It is also possible to prepare a generalized version of
613
a statement and then execute specific versions of it by
614
substituting parameters. When preparing the statement, write
615
question marks where you want to substitute parameters later. For
618
EXEC SQL BEGIN DECLARE SECTION;
619
const char *stmt = "INSERT INTO test1 VALUES(?, ?);";
620
EXEC SQL END DECLARE SECTION;
622
EXEC SQL PREPARE mystmt FROM :stmt;
624
EXEC SQL EXECUTE mystmt USING 42, 'foobar';
626
If the statement you are executing returns values, then add an
627
<literal>INTO</literal> clause:
629
EXEC SQL BEGIN DECLARE SECTION;
630
const char *stmt = "SELECT a, b, c FROM test1 WHERE a > ?";
633
EXEC SQL END DECLARE SECTION;
635
EXEC SQL PREPARE mystmt FROM :stmt;
637
EXEC SQL EXECUTE mystmt INTO v1, v2, v3 USING 37;
639
An <command>EXECUTE</command> command may have an
640
<literal>INTO</literal> clause, a <literal>USING</literal> clause,
645
When you don't need the prepared statement anymore, you should
648
EXEC SQL DEALLOCATE PREPARE <replaceable>name</replaceable>;
653
<sect1 id="ecpg-descriptors">
654
<title>Using SQL Descriptor Areas</title>
657
An SQL descriptor area is a more sophisticated method for
658
processing the result of a <command>SELECT</command> or
659
<command>FETCH</command> statement. An SQL descriptor area groups
660
the data of one row of data together with metadata items into one
661
data structure. The metadata is particularly useful when executing
662
dynamic SQL statements, where the nature of the result columns may
663
not be known ahead of time.
667
An SQL descriptor area consists of a header, which contains
668
information concerning the entire descriptor, and one or more item
669
descriptor areas, which basically each describe one column in the
674
Before you can use an SQL descriptor area, you need to allocate one:
676
EXEC SQL ALLOCATE DESCRIPTOR <replaceable>identifier</replaceable>;
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
When you don't need the descriptor anymore, you should deallocate
683
EXEC SQL DEALLOCATE DESCRIPTOR <replaceable>identifier</replaceable>;
688
To use a descriptor area, specify it as the storage target in an
689
<literal>INTO</literal> clause, instead of listing host variables:
691
EXEC SQL FETCH NEXT FROM mycursor INTO DESCRIPTOR mydesc;
696
Now how do you get the data out of the descriptor area? You can
697
think of the descriptor area as a structure with named fields. To
698
retrieve the value of a field from the header and store it into a
699
host variable, use the following command:
701
EXEC SQL GET DESCRIPTOR <replaceable>name</replaceable> :<replaceable>hostvar</replaceable> = <replaceable>field</replaceable>;
703
Currently, there is only one header field defined:
704
<replaceable>COUNT</replaceable>, which tells how many item
705
descriptor areas exist (that is, how many columns are contained in
706
the result). The host variable needs to be of an integer type. To
707
get a field from the item descriptor area, use the following
710
EXEC SQL GET DESCRIPTOR <replaceable>name</replaceable> VALUE <replaceable>num</replaceable> :<replaceable>hostvar</replaceable> = <replaceable>field</replaceable>;
712
<replaceable>num</replaceable> can be a literal integer or a host
713
variable containing an integer. Possible fields are:
717
<term><literal>CARDINALITY</literal> (integer)</term>
720
number of rows in the result set
726
<term><literal>DATA</literal></term>
729
actual data item (therefore, the data type of this field
730
depends on the query)
736
<term><literal>DATETIME_INTERVAL_CODE</literal> (integer)</term>
745
<term><literal>DATETIME_INTERVAL_PRECISION</literal> (integer)</term>
754
<term><literal>INDICATOR</literal> (integer)</term>
757
the indicator (indicating a null value or a value truncation)
763
<term><literal>KEY_MEMBER</literal> (integer)</term>
772
<term><literal>LENGTH</literal> (integer)</term>
775
length of the datum in characters
781
<term><literal>NAME</literal> (string)</term>
790
<term><literal>NULLABLE</literal> (integer)</term>
799
<term><literal>OCTET_LENGTH</literal> (integer)</term>
802
length of the character representation of the datum in bytes
808
<term><literal>PRECISION</literal> (integer)</term>
811
precision (for type <type>numeric</type>)
817
<term><literal>RETURNED_LENGTH</literal> (integer)</term>
820
length of the datum in characters
826
<term><literal>RETURNED_OCTET_LENGTH</literal> (integer)</term>
829
length of the character representation of the datum in bytes
835
<term><literal>SCALE</literal> (integer)</term>
838
scale (for type <type>numeric</type>)
844
<term><literal>TYPE</literal> (integer)</term>
847
numeric code of the data type of the column
855
<sect1 id="ecpg-errors">
856
<title>Error Handling</title>
859
This section describes how you can handle exceptional conditions
860
and warnings in an embedded SQL program. There are several
861
nonexclusive facilities for this.
865
<title>Setting Callbacks</title>
868
One simple method to catch errors and warnings is to set a
869
specific action to be executed whenever a particular condition
872
EXEC SQL WHENEVER <replaceable>condition</replaceable> <replaceable>action</replaceable>;
877
<replaceable>condition</replaceable> can be one of the following:
881
<term><literal>SQLERROR</literal></term>
884
The specified action is called whenever an error occurs during
885
the execution of an SQL statement.
891
<term><literal>SQLWARNING</literal></term>
894
The specified action is called whenever a warning occurs
895
during the execution of an SQL statement.
901
<term><literal>NOT FOUND</literal></term>
904
The specified action is called whenever an SQL statement
905
retrieves or affects zero rows. (This condition is not an
906
error, but you might be interested in handling it specially.)
914
<replaceable>action</replaceable> can be one of the following:
918
<term><literal>CONTINUE</literal></term>
921
This effectively means that the condition is ignored. This is
928
<term><literal>GOTO <replaceable>label</replaceable></literal></term>
929
<term><literal>GO TO <replaceable>label</replaceable></literal></term>
932
Jump to the specified label (using a C <literal>goto</literal>
939
<term><literal>SQLPRINT</literal></term>
942
Print a message to standard error. This is useful for simple
943
programs or during prototyping. The details of the message
944
cannot be configured.
950
<term><literal>STOP</literal></term>
953
Call <literal>exit(1)</literal>, which will terminate the
960
<term><literal>BREAK</literal></term>
963
Execute the C statement <literal>break</literal>. This should
964
only be used in loops or <literal>switch</literal> statements.
970
<term><literal>CALL <replaceable>name</replaceable> (<replaceable>args</replaceable>)</literal></term>
971
<term><literal>DO <replaceable>name</replaceable> (<replaceable>args</replaceable>)</literal></term>
974
Call the specified C functions with the specified arguments.
980
The SQL standard only provides for the actions
981
<literal>CONTINUE</literal> and <literal>GOTO</literal> (and
982
<literal>GO TO</literal>).
986
Here is an example that you might want to use in a simple program.
987
It prints a simple message when a warning occurs and aborts the
988
program when an error happens.
990
EXEC SQL WHENEVER SQLWARNING SQLPRINT;
991
EXEC SQL WHENEVER SQLERROR STOP;
996
The statement <literal>EXEC SQL WHENEVER</literal> is a directive
997
of the SQL preprocessor, not a C statement. The error or warning
998
actions that it sets apply to all embedded SQL statements that
999
appear below the point where the handler is set, unless a
1000
different action was set for the same condition between the first
1001
<literal>EXEC SQL WHENEVER</literal> and the SQL statement causing
1002
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
1009
int main(int argc, char *argv[])
1013
EXEC SQL WHENEVER SQLWARNING SQLPRINT;
1016
EXEC SQL SELECT ...;
1025
int main(int argc, char *argv[])
1028
set_error_handler();
1030
EXEC SQL SELECT ...;
1034
static void set_error_handler(void)
1036
EXEC SQL WHENEVER SQLERROR STOP;
1043
<title>sqlca</title>
1046
For more powerful error handling, the embedded SQL interface
1047
provides a global variable with the name <varname>sqlca</varname>
1048
that has the following structure:
1066
(In a multithreaded program, every thread automatically gets its
1067
own copy of <varname>sqlca</varname>. This works similarly to the
1068
handling of the standard C global variable
1069
<varname>errno</varname>.)
1073
<varname>sqlca</varname> covers both warnings and errors. If
1074
multiple warnings or errors occur during the execution of a
1075
statement, then <varname>sqlca</varname> will only contain
1076
information about the last one.
1080
If no error occurred in the last <acronym>SQL</acronym> statement,
1081
<literal>sqlca.sqlcode</literal> will be 0 and
1082
<literal>sqlca.sqlstate</literal> will be
1083
<literal>"00000"</literal>. If a warning or error occurred, then
1084
<literal>sqlca.sqlcode</literal> will be negative and
1085
<literal>sqlca.sqlstate</literal> will be different from
1086
<literal>"00000"</literal>. A positive
1087
<literal>sqlca.sqlcode</literal> indicates a harmless condition,
1088
such as that the last query returned zero rows.
1089
<literal>sqlcode</literal> and <literal>sqlstate</literal> are two
1090
different error code schemes; details appear below.
1094
If the last SQL statement was successful, then
1095
<literal>sqlca.sqlerrd[1]</literal> contains the OID of the
1096
processed row, if applicable, and
1097
<literal>sqlca.sqlerrd[2]</literal> contains the number of
1098
processed or returned rows, if applicable to the command.
1102
In case of an error or warning,
1103
<literal>sqlca.sqlerrm.sqlerrmc</literal> will contain a string
1104
that describes the error. The field
1105
<literal>sqlca.sqlerrm.sqlerrml</literal> contains the length of
1106
the error message that is stored in
1107
<literal>sqlca.sqlerrm.sqlerrmc</literal> (the result of
1108
<function>strlen()</function>, not really interesting for a C
1109
programmer). Note that some messages are too long to fit in the
1110
fixed-size <literal>sqlerrmc</literal> array; they will be truncated.
1114
In case of a warning, <literal>sqlca.sqlwarn[2]</literal> is set
1115
to <literal>W</literal>. (In all other cases, it is set to
1116
something different from <literal>W</literal>.) If
1117
<literal>sqlca.sqlwarn[1]</literal> is set to
1118
<literal>W</literal>, then a value was truncated when it was
1119
stored in a host variable. <literal>sqlca.sqlwarn[0]</literal> is
1120
set to <literal>W</literal> if any of the other elements are set
1121
to indicate a warning.
1125
The fields <structfield>sqlcaid</structfield>,
1126
<structfield>sqlcabc</structfield>,
1127
<structfield>sqlerrp</structfield>, and the remaining elements of
1128
<structfield>sqlerrd</structfield> and
1129
<structfield>sqlwarn</structfield> currently contain no useful
1134
The structure <varname>sqlca</varname> is not defined in the SQL
1135
standard, but is implemented in several other SQL database
1136
systems. The definitions are similar at the core, but if you want
1137
to write portable applications, then you should investigate the
1138
different implementations carefully.
1143
<title><literal>SQLSTATE</literal> vs <literal>SQLCODE</literal></title>
1146
The fields <literal>sqlca.sqlstate</literal> and
1147
<literal>sqlca.sqlcode</literal> are two different schemes that
1148
provide error codes. Both are specified in the SQL standard, but
1149
<literal>SQLCODE</literal> has been marked deprecated in the 1992
1150
edition of the standard and has been dropped in the 1999 edition.
1151
Therefore, new applications are strongly encouraged to use
1152
<literal>SQLSTATE</literal>.
1156
<literal>SQLSTATE</literal> is a five-character array. The five
1157
characters contain digits or upper-case letters that represent
1158
codes of various error and warning conditions.
1159
<literal>SQLSTATE</literal> has a hierarchical scheme: the first
1160
two characters indicate the general class of the condition, the
1161
last three characters indicate a subclass of the general
1162
condition. A successful state is indicated by the code
1163
<literal>00000</literal>. The <literal>SQLSTATE</literal> codes are for
1164
the most part defined in the SQL standard. The
1165
<productname>PostgreSQL</productname> server natively supports
1166
<literal>SQLSTATE</literal> error codes; therefore a high degree
1167
of consistency can be achieved by using this error code scheme
1168
throughout all applications. For further information see
1169
<xref linkend="errcodes-appendix">.
1173
<literal>SQLCODE</literal>, the deprecated error code scheme, is a
1174
simple integer. A value of 0 indicates success, a positive value
1175
indicates success with additional information, a negative value
1176
indicates an error. The SQL standard only defines the positive
1177
value +100, which indicates that the last command returned or
1178
affected zero rows, and no specific negative values. Therefore,
1179
this scheme can only achieve poor portability and does not have a
1180
hierarchical code assignment. Historically, the embedded SQL
1181
processor for <productname>PostgreSQL</productname> has assigned
1182
some specific <literal>SQLCODE</literal> values for its use, which
1183
are listed below with their numeric value and their symbolic name.
1184
Remember that these are not portable to other SQL implementations.
1185
To simplify the porting of applications to the
1186
<literal>SQLSTATE</literal> scheme, the corresponding
1187
<literal>SQLSTATE</literal> is also listed. There is, however, no
1188
one-to-one or one-to-many mapping between the two schemes (indeed
1189
it is many-to-many), so you should consult the global
1190
<literal>SQLSTATE</literal> listing in <xref linkend="errcodes-appendix">
1195
These are the assigned <literal>SQLCODE</literal> values:
1199
<term>-12 (<symbol>ECPG_OUT_OF_MEMORY</symbol>)</term>
1202
Indicates that your virtual memory is exhausted. (SQLSTATE
1209
<term>-200 (<symbol>ECPG_UNSUPPORTED</symbol>)</term>
1212
Indicates the preprocessor has generated something that the
1213
library does not know about. Perhaps you are running
1214
incompatible versions of the preprocessor and the
1215
library. (SQLSTATE YE002)
1221
<term>-201 (<symbol>ECPG_TOO_MANY_ARGUMENTS</symbol>)</term>
1224
This means that the command specified more host variables than
1225
the command expected. (SQLSTATE 07001 or 07002)
1231
<term>-202 (<symbol>ECPG_TOO_FEW_ARGUMENTS</symbol>)</term>
1234
This means that the command specified fewer host variables than
1235
the command expected. (SQLSTATE 07001 or 07002)
1241
<term>-203 (<symbol>ECPG_TOO_MANY_MATCHES</symbol>)</term>
1244
This means a query has returned multiple rows but the statement
1245
was only prepared to store one result row (for example, because
1246
the specified variables are not arrays). (SQLSTATE 21000)
1252
<term>-204 (<symbol>ECPG_INT_FORMAT</symbol>)</term>
1255
The host variable is of type <type>int</type> and the datum in
1256
the database is of a different type and contains a value that
1257
cannot be interpreted as an <type>int</type>. The library uses
1258
<function>strtol()</function> for this conversion. (SQLSTATE
1265
<term>-205 (<symbol>ECPG_UINT_FORMAT</symbol>)</term>
1268
The host variable is of type <type>unsigned int</type> and the
1269
datum in the database is of a different type and contains a
1270
value that cannot be interpreted as an <type>unsigned
1271
int</type>. The library uses <function>strtoul()</function>
1272
for this conversion. (SQLSTATE 42804)
1278
<term>-206 (<symbol>ECPG_FLOAT_FORMAT</symbol>)</term>
1281
The host variable is of type <type>float</type> and the datum
1282
in the database is of another type and contains a value that
1283
cannot be interpreted as a <type>float</type>. The library
1284
uses <function>strtod()</function> for this conversion.
1291
<term>-207 (<symbol>ECPG_CONVERT_BOOL</symbol>)</term>
1294
This means the host variable is of type <type>bool</type> and
1295
the datum in the database is neither <literal>'t'</> nor
1296
<literal>'f'</>. (SQLSTATE 42804)
1302
<term>-208 (<symbol>ECPG_EMPTY</symbol>)</term>
1305
The statement sent to the <productname>PostgreSQL</productname>
1306
server was empty. (This cannot normally happen in an embedded
1307
SQL program, so it may point to an internal error.) (SQLSTATE
1314
<term>-209 (<symbol>ECPG_MISSING_INDICATOR</symbol>)</term>
1317
A null value was returned and no null indicator variable was
1318
supplied. (SQLSTATE 22002)
1324
<term>-210 (<symbol>ECPG_NO_ARRAY</symbol>)</term>
1327
An ordinary variable was used in a place that requires an
1328
array. (SQLSTATE 42804)
1334
<term>-211 (<symbol>ECPG_DATA_NOT_ARRAY</symbol>)</term>
1337
The database returned an ordinary variable in a place that
1338
requires array value. (SQLSTATE 42804)
1344
<term>-220 (<symbol>ECPG_NO_CONN</symbol>)</term>
1347
The program tried to access a connection that does not exist.
1354
<term>-221 (<symbol>ECPG_NOT_CONN</symbol>)</term>
1357
The program tried to access a connection that does exist but is
1358
not open. (This is an internal error.) (SQLSTATE YE002)
1364
<term>-230 (<symbol>ECPG_INVALID_STMT</symbol>)</term>
1367
The statement you are trying to use has not been prepared.
1374
<term>-240 (<symbol>ECPG_UNKNOWN_DESCRIPTOR</symbol>)</term>
1377
The descriptor specified was not found. The statement you are
1378
trying to use has not been prepared. (SQLSTATE 33000)
1384
<term>-241 (<symbol>ECPG_INVALID_DESCRIPTOR_INDEX</symbol>)</term>
1387
The descriptor index specified was out of range. (SQLSTATE
1394
<term>-242 (<symbol>ECPG_UNKNOWN_DESCRIPTOR_ITEM</symbol>)</term>
1397
An invalid descriptor item was requested. (This is an internal
1398
error.) (SQLSTATE YE002)
1404
<term>-243 (<symbol>ECPG_VAR_NOT_NUMERIC</symbol>)</term>
1407
During the execution of a dynamic statement, the database
1408
returned a numeric value and the host variable was not numeric.
1415
<term>-244 (<symbol>ECPG_VAR_NOT_CHAR</symbol>)</term>
1418
During the execution of a dynamic statement, the database
1419
returned a non-numeric value and the host variable was numeric.
1426
<term>-400 (<symbol>ECPG_PGSQL</symbol>)</term>
1429
Some error caused by the <productname>PostgreSQL</productname>
1430
server. The message contains the error message from the
1431
<productname>PostgreSQL</productname> server.
1437
<term>-401 (<symbol>ECPG_TRANS</symbol>)</term>
1440
The <productname>PostgreSQL</productname> server signaled that
1441
we cannot start, commit, or rollback the transaction.
1448
<term>-402 (<symbol>ECPG_CONNECT</symbol>)</term>
1451
The connection attempt to the database did not succeed.
1458
<term>100 (<symbol>ECPG_NOT_FOUND</symbol>)</term>
1461
This is a harmless condition indicating that the last command
1462
retrieved or processed zero rows, or that you are at the end of
1463
the cursor. (SQLSTATE 02000)
1472
<sect1 id="ecpg-include">
1473
<title>Including Files</title>
1476
To include an external file into your embedded SQL program, use:
1478
EXEC SQL INCLUDE <replaceable>filename</replaceable>;
1480
The embedded SQL preprocessor will look for a file named
1481
<literal><replaceable>filename</replaceable>.h</literal>,
1482
preprocess it, and include it in the resulting C output. Thus,
1483
embedded SQL statements in the included file are handled correctly.
1487
Note that this is <emphasis>not</emphasis> the same as
1489
#include <<replaceable>filename</replaceable>.h>
1491
because this file would not be subject to SQL command preprocessing.
1492
Naturally, you can continue to use the C
1493
<literal>#include</literal> directive to include other header
1499
The include file name is case-sensitive, even though the rest of
1500
the <literal>EXEC SQL INCLUDE</literal> command follows the normal
1501
SQL case-sensitivity rules.
1506
<sect1 id="ecpg-process">
1507
<title>Processing Embedded SQL Programs</title>
1510
Now that you have an idea how to form embedded SQL C programs, you
1511
probably want to know how to compile them. Before compiling you
1512
run the file through the embedded <acronym>SQL</acronym>
1513
<acronym>C</acronym> preprocessor, which converts the
1514
<acronym>SQL</acronym> statements you used to special function
1515
calls. After compiling, you must link with a special library that
1516
contains the needed functions. These functions fetch information
1517
from the arguments, perform the <acronym>SQL</acronym> command using
1518
the <application>libpq</application> interface, and put the result
1519
in the arguments specified for output.
1523
The preprocessor program is called <filename>ecpg</filename> and is
1524
included in a normal <productname>PostgreSQL</> installation.
1525
Embedded SQL programs are typically named with an extension
1526
<filename>.pgc</filename>. If you have a program file called
1527
<filename>prog1.pgc</filename>, you can preprocess it by simply
1532
This will create a file called <filename>prog1.c</filename>. If
1533
your input files do not follow the suggested naming pattern, you
1534
can specify the output file explicitly using the
1535
<option>-o</option> option.
1539
The preprocessed file can be compiled normally, for example:
1543
The generated C source files include header files from the
1544
<productname>PostgreSQL</> installation, so if you installed
1545
<productname>PostgreSQL</> in a location that is not searched by
1546
default, you have to add an option such as
1547
<literal>-I/usr/local/pgsql/include</literal> to the compilation
1552
To link an embedded SQL program, you need to include the
1553
<filename>libecpg</filename> library, like so:
1555
cc -o myprog prog1.o prog2.o ... -lecpg
1557
Again, you might have to add an option like
1558
<literal>-L/usr/local/pgsql/lib</literal> to that command line.
1562
If you manage the build process of a larger project using
1563
<application>make</application>, it may be convenient to include
1564
the following implicit rule to your makefiles:
1574
The complete syntax of the <command>ecpg</command> command is
1575
detailed in <xref linkend="app-ecpg">.
1579
The <application>ecpg</application> library is thread-safe if it is built
1580
using the <option>--enable-thread-safety</> command-line option to
1581
<filename>configure</filename>. (You might need to use other threading
1582
command-line options to compile your client code.)
1586
<sect1 id="ecpg-library">
1587
<title>Library Functions</title>
1590
The <filename>libecpg</filename> library primarily contains
1591
<quote>hidden</quote> functions that are used to implement the
1592
functionality expressed by the embedded SQL commands. But there
1593
are some functions that can usefully be called directly. Note that
1594
this makes your code unportable.
1600
<function>ECPGdebug(int <replaceable>on</replaceable>, FILE
1601
*<replaceable>stream</replaceable>)</function> turns on debug
1602
logging if called with the first argument non-zero. Debug logging
1603
is done on <replaceable>stream</replaceable>. The log contains
1604
all <acronym>SQL</acronym> statements with all the input
1605
variables inserted, and the results from the
1606
<productname>PostgreSQL</productname> server. This can be very
1607
useful when searching for errors in your <acronym>SQL</acronym>
1614
<function>ECPGstatus(int <replaceable>lineno</replaceable>,
1615
const char* <replaceable>connection_name</replaceable>)</function>
1616
returns true if you are connected to a database and false if not.
1617
<replaceable>connection_name</replaceable> can be <literal>NULL</>
1618
if a single connection is being used.
1624
<sect1 id="ecpg-develop">
1625
<title>Internals</title>
1628
This section explains how <application>ECPG</application> works
1629
internally. This information can occasionally be useful to help
1630
users understand how to use <application>ECPG</application>.
1634
The first four lines written by <command>ecpg</command> to the
1635
output are fixed lines. Two are comments and two are include
1636
lines necessary to interface to the library. Then the
1637
preprocessor reads through the file and writes output. Normally
1638
it just echoes everything to the output.
1642
When it sees an <command>EXEC SQL</command> statement, it
1643
intervenes and changes it. The command starts with <command>EXEC
1644
SQL</command> and ends with <command>;</command>. Everything in
1645
between is treated as an <acronym>SQL</acronym> statement and
1646
parsed for variable substitution.
1650
Variable substitution occurs when a symbol starts with a colon
1651
(<literal>:</literal>). The variable with that name is looked up
1652
among the variables that were previously declared within a
1653
<literal>EXEC SQL DECLARE</> section.
1657
The most important function in the library is
1658
<function>ECPGdo</function>, which takes care of executing most
1659
commands. It takes a variable number of arguments. This can easily
1660
add up to 50 or so arguments, and we hope this will not be a
1661
problem on any platform.
1669
<term>A line number</term>
1672
This is the line number of the original line; used in error
1679
<term>A string</term>
1682
This is the <acronym>SQL</acronym> command that is to be issued.
1683
It is modified by the input variables, i.e., the variables that
1684
where not known at compile time but are to be entered in the
1685
command. Where the variables should go the string contains
1686
<literal>?</literal>.
1692
<term>Input variables</term>
1695
Every input variable causes ten arguments to be created. (See below.)
1701
<term><parameter>ECPGt_EOIT</></term>
1704
An <type>enum</> telling that there are no more input
1711
<term>Output variables</term>
1714
Every output variable causes ten arguments to be created.
1715
(See below.) These variables are filled by the function.
1721
<term><parameter>ECPGt_EORT</></term>
1724
An <type>enum</> telling that there are no more variables.
1732
For every variable that is part of the <acronym>SQL</acronym>
1733
command, the function gets ten arguments:
1738
The type as a special symbol.
1744
A pointer to the value or a pointer to the pointer.
1750
The size of the variable if it is a <type>char</type> or <type>varchar</type>.
1756
The number of elements in the array (for array fetches).
1762
The offset to the next element in the array (for array fetches).
1768
The type of the indicator variable as a special symbol.
1774
A pointer to the indicator variable.
1786
The number of elements in the indicator array (for array fetches).
1792
The offset to the next element in the indicator array (for
1800
Note that not all SQL commands are treated in this way. For
1801
instance, an open cursor statement like
1803
EXEC SQL OPEN <replaceable>cursor</replaceable>;
1805
is not copied to the output. Instead, the cursor's
1806
<command>DECLARE</> command is used at the position of the <command>OPEN</> command
1807
because it indeed opens the cursor.
1811
Here is a complete example describing the output of the
1812
preprocessor of a file <filename>foo.pgc</filename> (details may
1813
change with each particular version of the preprocessor):
1815
EXEC SQL BEGIN DECLARE SECTION;
1818
EXEC SQL END DECLARE SECTION;
1820
EXEC SQL SELECT res INTO :result FROM mytable WHERE index = :index;
1824
/* Processed by ecpg (2.6.0) */
1825
/* These two include files are added by the preprocessor */
1826
#include <ecpgtype.h>;
1827
#include <ecpglib.h>;
1829
/* exec sql begin declare section */
1835
/* exec sql end declare section */
1837
ECPGdo(__LINE__, NULL, "SELECT res FROM mytable WHERE index = ? ",
1838
ECPGt_int,&(index),1L,1L,sizeof(int),
1839
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT,
1840
ECPGt_int,&(result),1L,1L,sizeof(int),
1841
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
1844
(The indentation here is added for readability and not
1845
something the preprocessor does.)
1850
<!-- Keep this comment at the end of the file
1855
sgml-minimize-attributes:nil
1856
sgml-always-quote-attributes:t
1859
sgml-parent-document:nil
1860
sgml-default-dtd-file:"./reference.ced"
1861
sgml-exposed-tags:nil
1862
sgml-local-catalogs:("/usr/lib/sgml/catalog")
1863
sgml-local-ecat-files:nil
added
removed
doc/src/sgml/ecpg.sgml
