4
<title><application>ECPG</application> - Embedded <acronym>SQL</acronym> in C</title>
6
<indexterm zone="ecpg"><primary>embedded SQL</primary><secondary>in C</secondary></indexterm>
7
<indexterm zone="ecpg"><primary>C</primary></indexterm>
8
<indexterm zone="ecpg"><primary>ECPG</primary></indexterm>
11
This chapter describes the embedded <acronym>SQL</acronym> package
12
for <productname>PostgreSQL</productname>. It was written by
13
Linus Tolke (<email>linus@epact.se</email>) and Michael Meskes
14
(<email>meskes@postgresql.org</email>). Originally it was written to work with
15
<acronym>C</acronym>. It also works with <acronym>C++</acronym>, but
16
it does not recognize all <acronym>C++</acronym> constructs yet.
20
This documentation is quite incomplete. But since this
21
interface is standardized, additional information can be found in
22
many resources about SQL.
25
<sect1 id="ecpg-concept">
26
<title>The Concept</title>
29
An embedded SQL program consists of code written in an ordinary
30
programming language, in this case C, mixed with SQL commands in
31
specially marked sections. To build the program, the source code
32
is first passed through the embedded SQL preprocessor, which converts it
33
to an ordinary C program, and afterwards it can be processed by a C
38
Embedded <acronym>SQL</acronym> has advantages over other methods
39
for handling <acronym>SQL</acronym> commands from C code. First, it
40
takes care of the tedious passing of information to and from
41
variables in your <acronym>C</acronym> program. Second, the SQL
42
code in the program is checked at build time for syntactical
43
correctness. Third, embedded <acronym>SQL</acronym> in C is
44
specified in the <acronym>SQL</acronym> standard and supported by
45
many other <acronym>SQL</acronym> database systems. The
46
<productname>PostgreSQL</> implementation is designed to match this
47
standard as much as possible, and it is usually possible to port
48
embedded <acronym>SQL</acronym> programs written for other SQL
49
databases to <productname>PostgreSQL</productname> with relative
54
As already stated, programs written for the embedded
55
<acronym>SQL</acronym> interface are normal C programs with special
56
code inserted to perform database-related actions. This special
57
code always has the form:
61
These statements syntactically take the place of a C statement.
62
Depending on the particular statement, they can appear at the
63
global level or within a function. Embedded
64
<acronym>SQL</acronym> statements follow the case-sensitivity rules
65
of normal <acronym>SQL</acronym> code, and not those of C.
69
The following sections explain all the embedded SQL statements.
73
<sect1 id="ecpg-connect">
74
<title>Connecting to the Database Server</title>
77
One connects to a database using the following statement:
79
EXEC SQL CONNECT TO <replaceable>target</replaceable> <optional>AS <replaceable>connection-name</replaceable></optional> <optional>USER <replaceable>user-name</replaceable></optional>;
81
The <replaceable>target</replaceable> can be specified in the
87
<literal><replaceable>dbname</><optional>@<replaceable>hostname</></optional><optional>:<replaceable>port</></optional></literal>
93
<literal>tcp:postgresql://<replaceable>hostname</><optional>:<replaceable>port</></optional><optional>/<replaceable>dbname</></optional><optional>?<replaceable>options</></optional></literal>
99
<literal>unix:postgresql://<replaceable>hostname</><optional>:<replaceable>port</></optional><optional>/<replaceable>dbname</></optional><optional>?<replaceable>options</></optional></literal>
105
an SQL string literal containing one of the above forms
111
a reference to a character variable containing one of the above forms (see examples)
117
<literal>DEFAULT</literal>
122
If you specify the connection target literally (that is, not
123
through a variable reference) and you don't quote the value, then
124
the case-insensitivity rules of normal SQL are applied. In that
125
case you can also double-quote the individual parameters separately
126
as needed. In practice, it is probably less error-prone to use a
127
(single-quoted) string literal or a variable reference. The
128
connection target <literal>DEFAULT</literal> initiates a connection
129
to the default database under the default user name. No separate
130
user name or connection name can be specified in that case.
134
There are also different ways to specify the user name:
139
<literal><replaceable>username</replaceable></literal>
145
<literal><replaceable>username</replaceable>/<replaceable>password</replaceable></literal>
151
<literal><replaceable>username</replaceable> IDENTIFIED BY <replaceable>password</replaceable></literal>
157
<literal><replaceable>username</replaceable> USING <replaceable>password</replaceable></literal>
162
As above, the parameters <replaceable>username</replaceable> and
163
<replaceable>password</replaceable> can be an SQL identifier, an
164
SQL string literal, or a reference to a character variable.
168
The <replaceable>connection-name</replaceable> is used to handle
169
multiple connections in one program. It can be omitted if a
170
program uses only one connection. The most recently opened
171
connection becomes the current connection, which is used by default
172
when an SQL statement is to be executed (see later in this
177
Here are some examples of <command>CONNECT</command> statements:
179
EXEC SQL CONNECT TO mydb@sql.mydomain.com;
181
EXEC SQL CONNECT TO unix:postgresql://sql.mydomain.com/mydb AS myconnection USER john;
183
EXEC SQL BEGIN DECLARE SECTION;
184
const char *target = "mydb@sql.mydomain.com";
185
const char *user = "john";
186
EXEC SQL END DECLARE SECTION;
188
EXEC SQL CONNECT TO :target USER :user;
190
The last form makes use of the variant referred to above as
191
character variable reference. You will see in later sections how C
192
variables can be used in SQL statements when you prefix them with a
197
Be advised that the format of the connection target is not
198
specified in the SQL standard. So if you want to develop portable
199
applications, you might want to use something based on the last
200
example above to encapsulate the connection target string
205
<sect1 id="ecpg-disconnect">
206
<title>Closing a Connection</title>
209
To close a connection, use the following statement:
211
EXEC SQL DISCONNECT <optional><replaceable>connection</replaceable></optional>;
213
The <replaceable>connection</replaceable> can be specified
214
in the following ways:
219
<literal><replaceable>connection-name</replaceable></literal>
225
<literal>DEFAULT</literal>
231
<literal>CURRENT</literal>
237
<literal>ALL</literal>
242
If no connection name is specified, the current connection is
247
It is good style that an application always explicitly disconnect
248
from every connection it opened.
252
<sect1 id="ecpg-commands">
253
<title>Running SQL Commands</title>
256
Any SQL command can be run from within an embedded SQL application.
257
Below are some examples of how to do that.
263
EXEC SQL CREATE TABLE foo (number integer, ascii char(16));
264
EXEC SQL CREATE UNIQUE INDEX num1 ON foo(number);
272
EXEC SQL INSERT INTO foo (number, ascii) VALUES (9999, 'doodad');
280
EXEC SQL DELETE FROM foo WHERE number = 9999;
288
EXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE ascii = 'doodad';
293
Select using cursors:
295
EXEC SQL DECLARE foo_bar CURSOR FOR
296
SELECT number, ascii FROM foo
298
EXEC SQL OPEN foo_bar;
299
EXEC SQL FETCH foo_bar INTO :FooBar, DooDad;
301
EXEC SQL CLOSE foo_bar;
317
The tokens of the form
318
<literal>:<replaceable>something</replaceable></literal> are
319
<firstterm>host variables</firstterm>, that is, they refer to
320
variables in the C program. They are explained in <xref
321
linkend="ecpg-variables">.
325
In the default mode, statements are committed only when
326
<command>EXEC SQL COMMIT</command> is issued. The embedded SQL
327
interface also supports autocommit of transactions (similar to
328
<application>libpq</> behavior) via the <option>-t</option> command-line
329
option to <command>ecpg</command> (see below) or via the <literal>EXEC SQL
330
SET AUTOCOMMIT TO ON</literal> statement. In autocommit mode, each
331
command is automatically committed unless it is inside an explicit
332
transaction block. This mode can be explicitly turned off using
333
<literal>EXEC SQL SET AUTOCOMMIT TO OFF</literal>.
337
<sect1 id="ecpg-set-connection">
338
<title>Choosing a Connection</title>
341
The SQL statements shown in the previous section are executed on
342
the current connection, that is, the most recently opened one. If
343
an application needs to manage multiple connections, then there are
344
two ways to handle this.
348
The first option is to explicitly choose a connection for each SQL
349
statement, for example:
351
EXEC SQL AT <replaceable>connection-name</replaceable> SELECT ...;
353
This option is particularly suitable if the application needs to
354
use several connections in mixed order.
358
If your application uses multiple threads of execution, they cannot share a
359
connection concurrently. You must either explicitly control access to the connection
360
(using mutexes) or use a connection for each thread. If each thread uses its own connection,
361
you will need to use the AT clause to specify which connection the thread will use.
365
The second option is to execute a statement to switch the current
366
connection. That statement is:
368
EXEC SQL SET CONNECTION <replaceable>connection-name</replaceable>;
370
This option is particularly convenient if many statements are to be
371
executed on the same connection. It is not thread-aware.
375
<sect1 id="ecpg-variables">
376
<title>Using Host Variables</title>
379
In <xref linkend="ecpg-commands"> you saw how you can execute SQL
380
statements from an embedded SQL program. Some of those statements
381
only used fixed values and did not provide a way to insert
382
user-supplied values into statements or have the program process
383
the values returned by the query. Those kinds of statements are
384
not really useful in real applications. This section explains in
385
detail how you can pass data between your C program and the
386
embedded SQL statements using a simple mechanism called
387
<firstterm>host variables</firstterm>. In an embedded SQL program we
388
consider the SQL statements to be <firstterm>guests</firstterm> in the C
389
program code which is the <firstterm>host language</firstterm>. Therefore
390
the variables of the C program are called <firstterm>host
391
variables</firstterm>.
395
<title>Overview</title>
398
Passing data between the C program and the SQL statements is
399
particularly simple in embedded SQL. Instead of having the
400
program paste the data into the statement, which entails various
401
complications, such as properly quoting the value, you can simply
402
write the name of a C variable into the SQL statement, prefixed by
403
a colon. For example:
405
EXEC SQL INSERT INTO sometable VALUES (:v1, 'foo', :v2);
407
This statements refers to two C variables named
408
<varname>v1</varname> and <varname>v2</varname> and also uses a
409
regular SQL string literal, to illustrate that you are not
410
restricted to use one kind of data or the other.
414
This style of inserting C variables in SQL statements works
415
anywhere a value expression is expected in an SQL statement.
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
As you can see, you can optionally assign an initial value to the variable.
446
The variable's scope is determined by the location of its declaring
447
section within the program.
448
You can also declare variables with the following syntax which implicitly
449
creates a declare section:
453
You can have as many declare sections in a program as you like.
457
The declarations are also echoed to the output file as normal C
458
variables, so there's no need to declare them again. Variables
459
that are not intended to be used in SQL commands can be declared
460
normally outside these special sections.
464
The definition of a structure or union also must be listed inside
465
a <literal>DECLARE</> section. Otherwise the preprocessor cannot
466
handle these types since it does not know the definition.
471
<title>Different types of host variables</title>
473
As a host variable you can also use arrays, typedefs, structs and
474
pointers. Moreover there are special types of host variables that exist
479
A few examples on host variables:
485
One of the most common uses of an array declaration is probably the
486
allocation of a char array as in:
488
EXEC SQL BEGIN DECLARE SECTION;
490
EXEC SQL END DECLARE SECTION;
492
Note that you have to take care of the length for yourself. If you use
493
this host variable as the target variable of a query which returns a
494
string with more than 49 characters, a buffer overflow occurs.
500
<term>Typedefs</term>
503
Use the <literal>typedef</literal> keyword to map new types to already
506
EXEC SQL BEGIN DECLARE SECTION;
507
typedef char mychartype[40];
508
typedef long serial_t;
509
EXEC SQL END DECLARE SECTION;
511
Note that you could also use:
513
EXEC SQL TYPE serial_t IS long;
515
This declaration does not need to be part of a declare section.
521
<term>Pointers</term>
524
You can declare pointers to the most common types. Note however that
525
you cannot use pointers as target variables of queries without
526
auto-allocation. See <xref linkend="ecpg-descriptors"> for more
527
information on auto-allocation.
530
EXEC SQL BEGIN DECLARE SECTION;
533
EXEC SQL END DECLARE SECTION;
539
<term>Special types of variables</term>
542
ECPG contains some special types that help you to interact easily with
543
data from the SQL server. For example it has implemented support for
544
the <type>varchar</>, <type>numeric</>, <type>date</>, <type>timestamp</>, and <type>interval</> types.
545
<xref linkend="ecpg-pgtypes"> contains basic functions to deal with
546
those types, such that you do not need to send a query to the SQL
547
server just for adding an interval to a timestamp for example.
550
The special type <type>VARCHAR</type>
551
is converted into a named <type>struct</> for every variable. A
558
struct varchar_var { int len; char arr[180]; } var;
560
This structure is suitable for interfacing with SQL datums of type
561
<type>varchar</type>.
570
<title><command>SELECT INTO</command> and <command>FETCH INTO</command></title>
573
Now you should be able to pass data generated by your program into
574
an SQL command. But how do you retrieve the results of a query?
575
For that purpose, embedded SQL provides special variants of the
576
usual commands <command>SELECT</command> and
577
<command>FETCH</command>. These commands have a special
578
<literal>INTO</literal> clause that specifies which host variables
579
the retrieved values are to be stored in.
587
* CREATE TABLE test1 (a int, b varchar(50));
590
EXEC SQL BEGIN DECLARE SECTION;
593
EXEC SQL END DECLARE SECTION;
597
EXEC SQL SELECT a, b INTO :v1, :v2 FROM test;
599
So the <literal>INTO</literal> clause appears between the select
600
list and the <literal>FROM</literal> clause. The number of
601
elements in the select list and the list after
602
<literal>INTO</literal> (also called the target list) must be
607
Here is an example using the command <command>FETCH</command>:
609
EXEC SQL BEGIN DECLARE SECTION;
612
EXEC SQL END DECLARE SECTION;
616
EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test;
622
EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2;
626
Here the <literal>INTO</literal> clause appears after all the
631
Both of these methods only allow retrieving one row at a time. If
632
you need to process result sets that potentially contain more than
633
one row, you need to use a cursor, as shown in the second example.
638
<title>Indicators</title>
641
The examples above do not handle null values. In fact, the
642
retrieval examples will raise an error if they fetch a null value
643
from the database. To be able to pass null values to the database
644
or retrieve null values from the database, you need to append a
645
second host variable specification to each host variable that
646
contains data. This second host variable is called the
647
<firstterm>indicator</firstterm> and contains a flag that tells
648
whether the datum is null, in which case the value of the real
649
host variable is ignored. Here is an example that handles the
650
retrieval of null values correctly:
652
EXEC SQL BEGIN DECLARE SECTION;
655
EXEC SQL END DECLARE SECTION:
659
EXEC SQL SELECT b INTO :val :val_ind FROM test1;
661
The indicator variable <varname>val_ind</varname> will be zero if
662
the value was not null, and it will be negative if the value was
667
The indicator has another function: if the indicator value is
668
positive, it means that the value is not null, but it was
669
truncated when it was stored in the host variable.
674
<sect1 id="ecpg-dynamic">
675
<title>Dynamic SQL</title>
678
In many cases, the particular SQL statements that an application
679
has to execute are known at the time the application is written.
680
In some cases, however, the SQL statements are composed at run time
681
or provided by an external source. In these cases you cannot embed
682
the SQL statements directly into the C source code, but there is a
683
facility that allows you to call arbitrary SQL statements that you
684
provide in a string variable.
688
The simplest way to execute an arbitrary SQL statement is to use
689
the command <command>EXECUTE IMMEDIATE</command>. For example:
691
EXEC SQL BEGIN DECLARE SECTION;
692
const char *stmt = "CREATE TABLE test1 (...);";
693
EXEC SQL END DECLARE SECTION;
695
EXEC SQL EXECUTE IMMEDIATE :stmt;
697
You cannot execute statements that retrieve data (e.g.,
698
<command>SELECT</command>) this way.
702
A more powerful way to execute arbitrary SQL statements is to
703
prepare them once and execute the prepared statement as often as
704
you like. It is also possible to prepare a generalized version of
705
a statement and then execute specific versions of it by
706
substituting parameters. When preparing the statement, write
707
question marks where you want to substitute parameters later. For
710
EXEC SQL BEGIN DECLARE SECTION;
711
const char *stmt = "INSERT INTO test1 VALUES(?, ?);";
712
EXEC SQL END DECLARE SECTION;
714
EXEC SQL PREPARE mystmt FROM :stmt;
716
EXEC SQL EXECUTE mystmt USING 42, 'foobar';
718
If the statement you are executing returns values, then add an
719
<literal>INTO</literal> clause:
720
<programlisting><![CDATA[
721
EXEC SQL BEGIN DECLARE SECTION;
722
const char *stmt = "SELECT a, b, c FROM test1 WHERE a > ?";
725
EXEC SQL END DECLARE SECTION;
727
EXEC SQL PREPARE mystmt FROM :stmt;
729
EXEC SQL EXECUTE mystmt INTO v1, v2, v3 USING 37;
732
An <command>EXECUTE</command> command can have an
733
<literal>INTO</literal> clause, a <literal>USING</literal> clause,
738
When you don't need the prepared statement anymore, you should
741
EXEC SQL DEALLOCATE PREPARE <replaceable>name</replaceable>;
747
<sect1 id="ecpg-pgtypes">
748
<title>pgtypes library</title>
751
The pgtypes library maps <productname>PostgreSQL</productname> database
752
types to C equivalents that can be used in C programs. It also offers
753
functions to do basic calculations with those types within C, i.e. without
754
the help of the <productname>PostgreSQL</productname> server. See the
756
<programlisting><![CDATA[
757
EXEC SQL BEGIN DECLARE SECTION;
759
timestamp ts1, tsout;
762
EXEC SQL END DECLARE SECTION;
764
PGTYPESdate_today(&date1);
765
EXEC SQL SELECT started, duration INTO :ts1, :iv1 FROM datetbl WHERE d=:date1;
766
PGTYPEStimestamp_add_interval(&ts1, &iv1, &tsout);
767
out = PGTYPEStimestamp_to_asc(&tsout);
768
printf("Started + duration: %s\n", out);
775
<title>The numeric type</title>
777
The numeric type offers to do calculations with arbitrary precision. See
778
<xref linkend="datatype-numeric"> for the equivalent type in the
779
<productname>PostgreSQL</> server. Because of the arbitrary precision this
780
variable needs to be able to expand and shrink dynamically. That's why you
781
can only create variables on the heap by means of the
782
<function>PGTYPESnumeric_new</> and <function>PGTYPESnumeric_free</>
783
functions. The decimal type, which is similar but limited in the precision,
784
can be created on the stack as well as on the heap.
787
The following functions can be used to work with the numeric type:
790
<term><function>PGTYPESnumeric_new</function></term>
793
Request a pointer to a newly allocated numeric variable.
795
numeric *PGTYPESnumeric_new(void);
802
<term><function>PGTYPESnumeric_free</function></term>
805
Free a numeric type, release all of its memory.
807
void PGTYPESnumeric_free(numeric *var);
814
<term><function>PGTYPESnumeric_from_asc</function></term>
817
Parse a numeric type from its string notation.
819
numeric *PGTYPESnumeric_from_asc(char *str, char **endptr);
821
Valid formats are for example:
822
<literal>-2</literal>,
823
<literal>.794</literal>,
824
<literal>+3.44</literal>,
825
<literal>592.49E07</literal> or
826
<literal>-32.84e-4</literal>.
827
If the value could be parsed successfully, a valid pointer is returned,
828
else the NULL pointer. At the moment ecpg always parses the complete
829
string and so it currently does not support to store the address of the
830
first invalid character in <literal>*endptr</literal>. You can safely
831
set <literal>endptr</literal> to NULL.
837
<term><function>PGTYPESnumeric_to_asc</function></term>
840
Returns a pointer to a string allocated by <function>malloc</function> that contains the string
841
representation of the numeric type <literal>num</literal>.
843
char *PGTYPESnumeric_to_asc(numeric *num, int dscale);
845
The numeric value will be printed with <literal>dscale</literal> decimal
846
digits, with rounding applied if necessary.
852
<term><function>PGTYPESnumeric_add</function></term>
855
Add two numeric variables into a third one.
857
int PGTYPESnumeric_add(numeric *var1, numeric *var2, numeric *result);
859
The function adds the variables <literal>var1</literal> and
860
<literal>var2</literal> into the result variable
861
<literal>result</literal>.
862
The function returns 0 on success and -1 in case of error.
868
<term><function>PGTYPESnumeric_sub</function></term>
871
Subtract two numeric variables and return the result in a third one.
873
int PGTYPESnumeric_sub(numeric *var1, numeric *var2, numeric *result);
875
The function subtracts the variable <literal>var2</literal> from
876
the variable <literal>var1</literal>. The result of the operation is
877
stored in the variable <literal>result</literal>.
878
The function returns 0 on success and -1 in case of error.
884
<term><function>PGTYPESnumeric_mul</function></term>
887
Multiply two numeric variables and return the result in a third one.
889
int PGTYPESnumeric_mul(numeric *var1, numeric *var2, numeric *result);
891
The function multiplies the variables <literal>var1</literal> and
892
<literal>var2</literal>. The result of the operation is stored in the
893
variable <literal>result</literal>.
894
The function returns 0 on success and -1 in case of error.
900
<term><function>PGTYPESnumeric_div</function></term>
903
Divide two numeric variables and return the result in a third one.
905
int PGTYPESnumeric_div(numeric *var1, numeric *var2, numeric *result);
907
The function divides the variables <literal>var1</literal> by
908
<literal>var2</literal>. The result of the operation is stored in the
909
variable <literal>result</literal>.
910
The function returns 0 on success and -1 in case of error.
916
<term><function>PGTYPESnumeric_cmp</function></term>
919
Compare two numeric variables.
921
int PGTYPESnumeric_cmp(numeric *var1, numeric *var2)
923
This function compares two numeric variables. In case of error,
924
<literal>INT_MAX</literal> is returned. On success, the function
925
returns one of three possible results:
929
1, if <literal>var1</> is bigger than <literal>var2</>
934
-1, if <literal>var1</> is smaller than <literal>var2</>
939
0, if <literal>var1</> and <literal>var2</> are equal
948
<term><function>PGTYPESnumeric_from_int</function></term>
951
Convert an int variable to a numeric variable.
953
int PGTYPESnumeric_from_int(signed int int_val, numeric *var);
955
This function accepts a variable of type signed int and stores it
956
in the numeric variable <literal>var</>. Upon success, 0 is returned and
957
-1 in case of a failure.
963
<term><function>PGTYPESnumeric_from_long</function></term>
966
Convert a long int variable to a numeric variable.
968
int PGTYPESnumeric_from_long(signed long int long_val, numeric *var);
970
This function accepts a variable of type signed long int and stores it
971
in the numeric variable <literal>var</>. Upon success, 0 is returned and
972
-1 in case of a failure.
978
<term><function>PGTYPESnumeric_copy</function></term>
981
Copy over one numeric variable into another one.
983
int PGTYPESnumeric_copy(numeric *src, numeric *dst);
985
This function copies over the value of the variable that
986
<literal>src</literal> points to into the variable that <literal>dst</>
987
points to. It returns 0 on success and -1 if an error occurs.
993
<term><function>PGTYPESnumeric_from_double</function></term>
996
Convert a variable of type double to a numeric.
998
int PGTYPESnumeric_from_double(double d, numeric *dst);
1000
This function accepts a variable of type double and stores the result
1001
in the variable that <literal>dst</> points to. It returns 0 on success
1002
and -1 if an error occurs.
1008
<term><function>PGTYPESnumeric_to_double</function></term>
1011
Convert a variable of type numeric to double.
1013
int PGTYPESnumeric_to_double(numeric *nv, double *dp)
1015
The function converts the numeric value from the variable that
1016
<literal>nv</> points to into the double variable that <literal>dp</> points
1017
to. It returns 0 on success and -1 if an error occurs, including
1018
overflow. On overflow, the global variable <literal>errno</> will be set
1019
to <literal>PGTYPES_NUM_OVERFLOW</> additionally.
1025
<term><function>PGTYPESnumeric_to_int</function></term>
1028
Convert a variable of type numeric to int.
1030
int PGTYPESnumeric_to_int(numeric *nv, int *ip);
1032
The function converts the numeric value from the variable that
1033
<literal>nv</> points to into the integer variable that <literal>ip</>
1034
points to. It returns 0 on success and -1 if an error occurs, including
1035
overflow. On overflow, the global variable <literal>errno</> will be set
1036
to <literal>PGTYPES_NUM_OVERFLOW</> additionally.
1042
<term><function>PGTYPESnumeric_to_long</function></term>
1045
Convert a variable of type numeric to long.
1047
int PGTYPESnumeric_to_long(numeric *nv, long *lp);
1049
The function converts the numeric value from the variable that
1050
<literal>nv</> points to into the long integer variable that
1051
<literal>lp</> points to. It returns 0 on success and -1 if an error
1052
occurs, including overflow. On overflow, the global variable
1053
<literal>errno</> will be set to <literal>PGTYPES_NUM_OVERFLOW</>
1060
<term><function>PGTYPESnumeric_to_decimal</function></term>
1063
Convert a variable of type numeric to decimal.
1065
int PGTYPESnumeric_to_decimal(numeric *src, decimal *dst);
1067
The function converts the numeric value from the variable that
1068
<literal>src</> points to into the decimal variable that
1069
<literal>dst</> points to. It returns 0 on success and -1 if an error
1070
occurs, including overflow. On overflow, the global variable
1071
<literal>errno</> will be set to <literal>PGTYPES_NUM_OVERFLOW</>
1078
<term><function>PGTYPESnumeric_from_decimal</function></term>
1081
Convert a variable of type decimal to numeric.
1083
int PGTYPESnumeric_from_decimal(decimal *src, numeric *dst);
1085
The function converts the decimal value from the variable that
1086
<literal>src</> points to into the numeric variable that
1087
<literal>dst</> points to. It returns 0 on success and -1 if an error
1088
occurs. Since the decimal type is implemented as a limited version of
1089
the numeric type, overflow cannot occur with this conversion.
1098
<title>The date type</title>
1100
The date type in C enables your programs to deal with data of the SQL type
1101
date. See <xref linkend="datatype-datetime"> for the equivalent type in the
1102
<productname>PostgreSQL</> server.
1105
The following functions can be used to work with the date type:
1107
<varlistentry id="PGTYPESdatefromtimestamp">
1108
<term><function>PGTYPESdate_from_timestamp</function></term>
1111
Extract the date part from a timestamp.
1113
date PGTYPESdate_from_timestamp(timestamp dt);
1115
The function receives a timestamp as its only argument and returns the
1116
extracted date part from this timestamp.
1121
<varlistentry id="PGTYPESdatefromasc">
1122
<term><function>PGTYPESdate_from_asc</function></term>
1125
Parse a date from its textual representation.
1127
date PGTYPESdate_from_asc(char *str, char **endptr);
1129
The function receives a C char* string <literal>str</> and a pointer to
1130
a C char* string <literal>endptr</>. At the moment ecpg always parses
1131
the complete string and so it currently does not support to store the
1132
address of the first invalid character in <literal>*endptr</literal>.
1133
You can safely set <literal>endptr</literal> to NULL.
1136
Note that the function always assumes MDY-formatted dates and there is
1137
currently no variable to change that within ecpg.
1140
The following input formats are allowed:
1142
<title>Valid input formats for <function>PGTYPESdate_from_asc</function></title>
1146
<entry>Input</entry>
1147
<entry>Result</entry>
1152
<entry><literal>January 8, 1999</literal></entry>
1153
<entry><literal>January 8, 1999</literal></entry>
1156
<entry><literal>1999-01-08</literal></entry>
1157
<entry><literal>January 8, 1999</literal></entry>
1160
<entry><literal>1/8/1999</literal></entry>
1161
<entry><literal>January 8, 1999</literal></entry>
1164
<entry><literal>1/18/1999</literal></entry>
1165
<entry><literal>January 18, 1999</literal></entry>
1168
<entry><literal>01/02/03</literal></entry>
1169
<entry><literal>February 1, 2003</literal></entry>
1172
<entry><literal>1999-Jan-08</literal></entry>
1173
<entry><literal>January 8, 1999</literal></entry>
1176
<entry><literal>Jan-08-1999</literal></entry>
1177
<entry><literal>January 8, 1999</literal></entry>
1180
<entry><literal>08-Jan-1999</literal></entry>
1181
<entry><literal>January 8, 1999</literal></entry>
1184
<entry><literal>99-Jan-08</literal></entry>
1185
<entry><literal>January 8, 1999</literal></entry>
1188
<entry><literal>08-Jan-99</literal></entry>
1189
<entry><literal>January 8, 1999</literal></entry>
1192
<entry><literal>08-Jan-06</literal></entry>
1193
<entry><literal>January 8, 2006</literal></entry>
1196
<entry><literal>Jan-08-99</literal></entry>
1197
<entry><literal>January 8, 1999</literal></entry>
1200
<entry><literal>19990108</literal></entry>
1201
<entry><literal>ISO 8601; January 8, 1999</literal></entry>
1204
<entry><literal>990108</literal></entry>
1205
<entry><literal>ISO 8601; January 8, 1999</literal></entry>
1208
<entry><literal>1999.008</literal></entry>
1209
<entry><literal>year and day of year</literal></entry>
1212
<entry><literal>J2451187</literal></entry>
1213
<entry><literal>Julian day</literal></entry>
1216
<entry><literal>January 8, 99 BC</literal></entry>
1217
<entry><literal>year 99 before the Common Era</literal></entry>
1226
<varlistentry id="PGTYPESdatetoasc">
1227
<term><function>PGTYPESdate_to_asc</function></term>
1230
Return the textual representation of a date variable.
1232
char *PGTYPESdate_to_asc(date dDate);
1234
The function receives the date <literal>dDate</> as its only parameter.
1235
It will output the date in the form <literal>1999-01-18</>, i.e. in the
1236
<literal>YYYY-MM-DD</> format.
1241
<varlistentry id="PGTYPESdatejulmdy">
1242
<term><function>PGTYPESdate_julmdy</function></term>
1245
Extract the values for the day, the month and the year from a variable
1248
void PGTYPESdate_julmdy(date d, int *mdy);
1250
<!-- almost same description as for rjulmdy() -->
1251
The function receives the date <literal>d</> and a pointer to an array
1252
of 3 integer values <literal>mdy</>. The variable name indicates
1253
the sequential order: <literal>mdy[0]</> will be set to contain the
1254
number of the month, <literal>mdy[1]</> will be set to the value of the
1255
day and <literal>mdy[2]</> will contain the year.
1260
<varlistentry id="PGTYPESdatemdyjul">
1261
<term><function>PGTYPESdate_mdyjul</function></term>
1264
Create a date value from an array of 3 integers that specify the
1265
day, the month and the year of the date.
1267
void PGTYPESdate_mdyjul(int *mdy, date *jdate);
1269
The function receives the array of the 3 integers (<literal>mdy</>) as
1270
its first argument and as its second argument a pointer to a variable
1271
of type date that should hold the result of the operation.
1276
<varlistentry id="PGTYPESdatedayofweek">
1277
<term><function>PGTYPESdate_dayofweek</function></term>
1280
Return a number representing the day of the week for a date value.
1282
int PGTYPESdate_dayofweek(date d);
1284
The function receives the date variable <literal>d</> as its only
1285
argument and returns an integer that indicates the day of the week for
1328
<varlistentry id="PGTYPESdatetoday">
1329
<term><function>PGTYPESdate_today</function></term>
1332
Get the current date.
1334
void PGTYPESdate_today(date *d);
1336
The function receives a pointer to a date variable (<literal>d</>)
1337
that it sets to the current date.
1342
<varlistentry id="PGTYPESdatefmtasc">
1343
<term><function>PGTYPESdate_fmt_asc</function></term>
1346
Convert a variable of type date to its textual representation using a
1349
int PGTYPESdate_fmt_asc(date dDate, char *fmtstring, char *outbuf);
1351
The function receives the date to convert (<literal>dDate</>), the
1352
format mask (<literal>fmtstring</>) and the string that will hold the
1353
textual representation of the date (<literal>outbuf</>).
1356
On success, 0 is returned and a negative value if an error occurred.
1359
The following literals are the field specifiers you can use:
1363
<literal>dd</literal> - The number of the day of the month.
1368
<literal>mm</literal> - The number of the month of the year.
1373
<literal>yy</literal> - The number of the year as a two digit number.
1378
<literal>yyyy</literal> - The number of the year as a four digit number.
1383
<literal>ddd</literal> - The name of the day (abbreviated).
1388
<literal>mmm</literal> - The name of the month (abbreviated).
1392
All other characters are copied 1:1 to the output string.
1395
The following table indicates a few possible formats. This will give
1396
you an idea of how to use this function. All output lines are based on
1397
the same date: November, 23rd, 1959.
1399
<title>Valid input formats for <function>PGTYPESdate_fmt_asc</function></title>
1404
<entry>result</entry>
1409
<entry><literal>mmddyy</literal></entry>
1410
<entry><literal>112359</literal></entry>
1413
<entry><literal>ddmmyy</literal></entry>
1414
<entry><literal>231159</literal></entry>
1417
<entry><literal>yymmdd</literal></entry>
1418
<entry><literal>591123</literal></entry>
1421
<entry><literal>yy/mm/dd</literal></entry>
1422
<entry><literal>59/11/23</literal></entry>
1425
<entry><literal>yy mm dd</literal></entry>
1426
<entry><literal>59 11 23</literal></entry>
1429
<entry><literal>yy.mm.dd</literal></entry>
1430
<entry><literal>59.11.23</literal></entry>
1433
<entry><literal>.mm.yyyy.dd.</literal></entry>
1434
<entry><literal>.11.1959.23.</literal></entry>
1437
<entry><literal>mmm. dd, yyyy</literal></entry>
1438
<entry><literal>Nov. 23, 1959</literal></entry>
1441
<entry><literal>mmm dd yyyy</literal></entry>
1442
<entry><literal>Nov 23 1959</literal></entry>
1445
<entry><literal>yyyy dd mm</literal></entry>
1446
<entry><literal>1959 23 11</literal></entry>
1449
<entry><literal>ddd, mmm. dd, yyyy</literal></entry>
1450
<entry><literal>Mon, Nov. 23, 1959</literal></entry>
1453
<entry><literal>(ddd) mmm. dd, yyyy</literal></entry>
1454
<entry><literal>(Mon) Nov. 23, 1959</literal></entry>
1463
<varlistentry id="PGTYPESdatedefmtasc">
1464
<term><function>PGTYPESdate_defmt_asc</function></term>
1467
Use a format mask to convert a C char* string to a value of type
1470
int PGTYPESdate_defmt_asc(date *d, char *fmt, char *str);
1472
<!-- same description as rdefmtdate -->
1473
The function receives a pointer to the date value that should hold the
1474
result of the operation (<literal>d</>), the format mask to use for
1475
parsing the date (<literal>fmt</>) and the C char* string containing
1476
the textual representation of the date (<literal>str</>). The textual
1477
representation is expected to match the format mask. However you do not
1478
need to have a 1:1 mapping of the string to the format mask. The
1479
function only analyzes the sequential order and looks for the literals
1480
<literal>yy</literal> or <literal>yyyy</literal> that indicate the
1481
position of the year, <literal>mm</literal> to indicate the position of
1482
the month and <literal>dd</literal> to indicate the position of the
1486
The following table indicates a few possible formats. This will give
1487
you an idea of how to use this function.
1489
<title>Valid input formats for <function>rdefmtdate</function></title>
1495
<entry>result</entry>
1500
<entry><literal>ddmmyy</literal></entry>
1501
<entry><literal>21-2-54</literal></entry>
1502
<entry><literal>1954-02-21</literal></entry>
1505
<entry><literal>ddmmyy</literal></entry>
1506
<entry><literal>2-12-54</literal></entry>
1507
<entry><literal>1954-12-02</literal></entry>
1510
<entry><literal>ddmmyy</literal></entry>
1511
<entry><literal>20111954</literal></entry>
1512
<entry><literal>1954-11-20</literal></entry>
1515
<entry><literal>ddmmyy</literal></entry>
1516
<entry><literal>130464</literal></entry>
1517
<entry><literal>1964-04-13</literal></entry>
1520
<entry><literal>mmm.dd.yyyy</literal></entry>
1521
<entry><literal>MAR-12-1967</literal></entry>
1522
<entry><literal>1967-03-12</literal></entry>
1525
<entry><literal>yy/mm/dd</literal></entry>
1526
<entry><literal>1954, February 3rd</literal></entry>
1527
<entry><literal>1954-02-03</literal></entry>
1530
<entry><literal>mmm.dd.yyyy</literal></entry>
1531
<entry><literal>041269</literal></entry>
1532
<entry><literal>1969-04-12</literal></entry>
1535
<entry><literal>yy/mm/dd</literal></entry>
1536
<entry><literal>In the year 2525, in the month of July, mankind will be alive on the 28th day</literal></entry>
1537
<entry><literal>2525-07-28</literal></entry>
1540
<entry><literal>dd-mm-yy</literal></entry>
1541
<entry><literal>I said on the 28th of July in the year 2525</literal></entry>
1542
<entry><literal>2525-07-28</literal></entry>
1545
<entry><literal>mmm.dd.yyyy</literal></entry>
1546
<entry><literal>9/14/58</literal></entry>
1547
<entry><literal>1958-09-14</literal></entry>
1550
<entry><literal>yy/mm/dd</literal></entry>
1551
<entry><literal>47/03/29</literal></entry>
1552
<entry><literal>1947-03-29</literal></entry>
1555
<entry><literal>mmm.dd.yyyy</literal></entry>
1556
<entry><literal>oct 28 1975</literal></entry>
1557
<entry><literal>1975-10-28</literal></entry>
1560
<entry><literal>mmddyy</literal></entry>
1561
<entry><literal>Nov 14th, 1985</literal></entry>
1562
<entry><literal>1985-11-14</literal></entry>
1575
<title>The timestamp type</title>
1577
The timestamp type in C enables your programs to deal with data of the SQL
1578
type timestamp. See <xref linkend="datatype-datetime"> for the equivalent
1579
type in the <productname>PostgreSQL</> server.
1582
The following functions can be used to work with the timestamp type:
1584
<varlistentry id="PGTYPEStimestampfromasc">
1585
<term><function>PGTYPEStimestamp_from_asc</function></term>
1588
Parse a timestamp from its textual representation into a timestamp
1591
timestamp PGTYPEStimestamp_from_asc(char *str, char **endptr);
1593
The function receives the string to parse (<literal>str</>) and a
1594
pointer to a C char* (<literal>endptr</>).
1595
At the moment ecpg always parses
1596
the complete string and so it currently does not support to store the
1597
address of the first invalid character in <literal>*endptr</literal>.
1598
You can safely set <literal>endptr</literal> to NULL.
1601
The function returns the parsed timestamp on success. On error,
1602
<literal>PGTYPESInvalidTimestamp</literal> is returned and errno is
1603
set to <literal>PGTYPES_TS_BAD_TIMESTAMP</>. See <xref linkend="PGTYPESInvalidTimestamp"> for important notes on this value.
1607
In general, the input string can contain any combination of an allowed
1608
date specification, a whitespace character and an allowed time
1609
specification. Note that timezones are not supported by ecpg. It can
1610
parse them but does not apply any calculation as the
1611
<productname>PostgreSQL</> server does for example. Timezone
1612
specifiers are silently discarded.
1615
The following table contains a few examples for input strings:
1617
<title>Valid input formats for <function>PGTYPEStimestamp_from_asc</function></title>
1621
<entry>Input</entry>
1622
<entry>Result</entry>
1627
<entry><literal>1999-01-08 04:05:06</literal></entry>
1628
<entry><literal>1999-01-08 04:05:06</literal></entry>
1631
<entry><literal>January 8 04:05:06 1999 PST</literal></entry>
1632
<entry><literal>1999-01-08 04:05:06</literal></entry>
1635
<entry><literal>1999-Jan-08 04:05:06.789-8</literal></entry>
1636
<entry><literal>1999-01-08 04:05:06.789 (time zone specifier ignored)</literal></entry>
1639
<entry><literal>J2451187 04:05-08:00</literal></entry>
1640
<entry><literal>1999-01-08 04:05:00 (time zone specifier ignored)</literal></entry>
1649
<varlistentry id="PGTYPEStimestamptoasc">
1650
<term><function>PGTYPEStimestamp_to_asc</function></term>
1653
Converts a date to a C char* string.
1655
char *PGTYPEStimestamp_to_asc(timestamp tstamp);
1657
The function receives the timestamp <literal>tstamp</> as
1658
its only argument and returns an allocated string that contains the
1659
textual representation of the timestamp.
1664
<varlistentry id="PGTYPEStimestampcurrent">
1665
<term><function>PGTYPEStimestamp_current</function></term>
1668
Retrieve the current timestamp.
1670
void PGTYPEStimestamp_current(timestamp *ts);
1672
The function retrieves the current timestamp and saves it into the
1673
timestamp variable that <literal>ts</> points to.
1678
<varlistentry id="PGTYPEStimestampfmtasc">
1679
<term><function>PGTYPEStimestamp_fmt_asc</function></term>
1682
Convert a timestamp variable to a C char* using a format mask.
1684
int PGTYPEStimestamp_fmt_asc(timestamp *ts, char *output, int str_len, char *fmtstr);
1686
The function receives a pointer to the timestamp to convert as its
1687
first argument (<literal>ts</>), a pointer to the output buffer
1688
(<literal>output</>), the maximal length that has been allocated for
1689
the output buffer (<literal>str_len</literal>) and the format mask to
1690
use for the conversion (<literal>fmtstr</literal>).
1693
Upon success, the function returns 0 and a negative value if an
1697
You can use the following format specifiers for the format mask. The
1698
format specifiers are the same ones that are used in the
1699
<function>strftime</> function in <productname>libc</productname>. Any
1700
non-format specifier will be copied into the output buffer.
1701
<!-- This is from the FreeBSD man page:
1702
http://www.freebsd.org/cgi/man.cgi?query=strftime&apropos=0&sektion=3&manpath=FreeBSD+7.0-current&format=html
1707
<literal>%A</literal> - is replaced by national representation of
1708
the full weekday name.
1713
<literal>%a</literal> - is replaced by national representation of
1714
the abbreviated weekday name.
1719
<literal>%B</literal> - is replaced by national representation of
1720
the full month name.
1725
<literal>%b</literal> - is replaced by national representation of
1726
the abbreviated month name.
1731
<literal>%C</literal> - is replaced by (year / 100) as decimal
1732
number; single digits are preceded by a zero.
1737
<literal>%c</literal> - is replaced by national representation of
1743
<literal>%D</literal> - is equivalent to
1744
<literal>%m/%d/%y</literal>.
1749
<literal>%d</literal> - is replaced by the day of the month as a
1750
decimal number (01-31).
1755
<literal>%E*</literal> <literal>%O*</literal> - POSIX locale
1756
extensions. The sequences
1757
<literal>%Ec</literal>
1758
<literal>%EC</literal>
1759
<literal>%Ex</literal>
1760
<literal>%EX</literal>
1761
<literal>%Ey</literal>
1762
<literal>%EY</literal>
1763
<literal>%Od</literal>
1764
<literal>%Oe</literal>
1765
<literal>%OH</literal>
1766
<literal>%OI</literal>
1767
<literal>%Om</literal>
1768
<literal>%OM</literal>
1769
<literal>%OS</literal>
1770
<literal>%Ou</literal>
1771
<literal>%OU</literal>
1772
<literal>%OV</literal>
1773
<literal>%Ow</literal>
1774
<literal>%OW</literal>
1775
<literal>%Oy</literal>
1776
are supposed to provide alternative representations.
1779
Additionally <literal>%OB</literal> implemented to represent
1780
alternative months names (used standalone, without day mentioned).
1785
<literal>%e</literal> - is replaced by the day of month as a decimal
1786
number (1-31); single digits are preceded by a blank.
1791
<literal>%F</literal> - is equivalent to <literal>%Y-%m-%d</literal>.
1796
<literal>%G</literal> - is replaced by a year as a decimal number
1797
with century. This year is the one that contains the greater part of
1798
the week (Monday as the first day of the week).
1803
<literal>%g</literal> - is replaced by the same year as in
1804
<literal>%G</literal>, but as a decimal number without century
1810
<literal>%H</literal> - is replaced by the hour (24-hour clock) as a
1811
decimal number (00-23).
1816
<literal>%h</literal> - the same as <literal>%b</literal>.
1821
<literal>%I</literal> - is replaced by the hour (12-hour clock) as a
1822
decimal number (01-12).
1827
<literal>%j</literal> - is replaced by the day of the year as a
1828
decimal number (001-366).
1833
<literal>%k</literal> - is replaced by the hour (24-hour clock) as a
1834
decimal number (0-23); single digits are preceded by a blank.
1839
<literal>%l</literal> - is replaced by the hour (12-hour clock) as a
1840
decimal number (1-12); single digits are preceded by a blank.
1845
<literal>%M</literal> - is replaced by the minute as a decimal
1851
<literal>%m</literal> - is replaced by the month as a decimal number
1857
<literal>%n</literal> - is replaced by a newline.
1862
<literal>%O*</literal> - the same as <literal>%E*</literal>.
1867
<literal>%p</literal> - is replaced by national representation of
1868
either "ante meridiem" or "post meridiem" as appropriate.
1873
<literal>%R</literal> - is equivalent to <literal>%H:%M</literal>.
1878
<literal>%r</literal> - is equivalent to <literal>%I:%M:%S
1884
<literal>%S</literal> - is replaced by the second as a decimal
1890
<literal>%s</literal> - is replaced by the number of seconds since
1896
<literal>%T</literal> - is equivalent to <literal>%H:%M:%S</literal>
1901
<literal>%t</literal> - is replaced by a tab.
1906
<literal>%U</literal> - is replaced by the week number of the year
1907
(Sunday as the first day of the week) as a decimal number (00-53).
1912
<literal>%u</literal> - is replaced by the weekday (Monday as the
1913
first day of the week) as a decimal number (1-7).
1918
<literal>%V</literal> - is replaced by the week number of the year
1919
(Monday as the first day of the week) as a decimal number (01-53).
1920
If the week containing January 1 has four or more days in the new
1921
year, then it is week 1; otherwise it is the last week of the
1922
previous year, and the next week is week 1.
1927
<literal>%v</literal> - is equivalent to
1928
<literal>%e-%b-%Y</literal>.
1933
<literal>%W</literal> - is replaced by the week number of the year
1934
(Monday as the first day of the week) as a decimal number (00-53).
1939
<literal>%w</literal> - is replaced by the weekday (Sunday as the
1940
first day of the week) as a decimal number (0-6).
1945
<literal>%X</literal> - is replaced by national representation of
1951
<literal>%x</literal> - is replaced by national representation of
1957
<literal>%Y</literal> - is replaced by the year with century as a
1963
<literal>%y</literal> - is replaced by the year without century as a
1964
decimal number (00-99).
1969
<literal>%Z</literal> - is replaced by the time zone name.
1974
<literal>%z</literal> - is replaced by the time zone offset from
1975
UTC; a leading plus sign stands for east of UTC, a minus sign for
1976
west of UTC, hours and minutes follow with two digits each and no
1977
delimiter between them (common form for RFC 822 date headers).
1982
<literal>%+</literal> - is replaced by national representation of
1988
<literal>%-*</literal> - GNU libc extension. Do not do any padding
1989
when performing numerical outputs.
1994
$_* - GNU libc extension. Explicitly specify space for padding.
1999
<literal>%0*</literal> - GNU libc extension. Explicitly specify zero
2005
<literal>%%</literal> - is replaced by <literal>%</literal>.
2013
<varlistentry id="PGTYPEStimestampsub">
2014
<term><function>PGTYPEStimestamp_sub</function></term>
2017
Subtract one timestamp from another one and save the result in a
2018
variable of type interval.
2020
int PGTYPEStimestamp_sub(timestamp *ts1, timestamp *ts2, interval *iv);
2022
The function will subtract the timestamp variable that <literal>ts2</>
2023
points to from the timestamp variable that <literal>ts1</> points to
2024
and will store the result in the interval variable that <literal>iv</>
2028
Upon success, the function returns 0 and a negative value if an
2034
<varlistentry id="PGTYPEStimestampdefmtasc">
2035
<term><function>PGTYPEStimestamp_defmt_asc</function></term>
2038
Parse a timestamp value from its textual representation using a
2041
int PGTYPEStimestamp_defmt_asc(char *str, char *fmt, timestamp *d);
2043
The function receives the textual representation of a timestamp in the
2044
variable <literal>str</> as well as the formatting mask to use in the
2045
variable <literal>fmt</>. The result will be stored in the variable
2046
that <literal>d</> points to.
2049
If the formatting mask <literal>fmt</> is NULL, the function will fall
2050
back to the default formatting mask which is <literal>%Y-%m-%d
2054
This is the reverse function to <xref
2055
linkend="PGTYPEStimestampfmtasc">. See the documentation there in
2056
order to find out about the possible formatting mask entries.
2061
<varlistentry id="PGTYPEStimestampaddinterval">
2062
<term><function>PGTYPEStimestamp_add_interval</function></term>
2065
Add an interval variable to a timestamp variable.
2067
int PGTYPEStimestamp_add_interval(timestamp *tin, interval *span, timestamp *tout);
2069
The function receives a pointer to a timestamp variable <literal>tin</>
2070
and a pointer to an interval variable <literal>span</>. It adds the
2071
interval to the timestamp and saves the resulting timestamp in the
2072
variable that <literal>tout</> points to.
2075
Upon success, the function returns 0 and a negative value if an
2081
<varlistentry id="PGTYPEStimestampsubinterval">
2082
<term><function>PGTYPEStimestamp_sub_interval</function></term>
2085
Subtract an interval variable from a timestamp variable.
2087
int PGTYPEStimestamp_sub_interval(timestamp *tin, interval *span, timestamp *tout);
2089
The function subtracts the interval variable that <literal>span</>
2090
points to from the timestamp variable that <literal>tin</> points to
2091
and saves the result into the variable that <literal>tout</> points
2095
Upon success, the function returns 0 and a negative value if an
2105
<title>The interval type</title>
2107
The interval type in C enables your programs to deal with data of the SQL
2108
type interval. See <xref linkend="datatype-datetime"> for the equivalent
2109
type in the <productname>PostgreSQL</> server.
2112
The following functions can be used to work with the interval type:
2115
<varlistentry id="PGTYPESintervalnew">
2116
<term><function>PGTYPESinterval_new</function></term>
2119
Return a pointer to a newly allocated interval variable.
2121
interval *PGTYPESinterval_new(void);
2127
<varlistentry id="PGTYPESintervalfree">
2128
<term><function>PGTYPESinterval_free</function></term>
2131
Release the memory of a previously allocated interval variable.
2133
void PGTYPESinterval_new(interval *intvl);
2139
<varlistentry id="PGTYPESintervalfromasc">
2140
<term><function>PGTYPESinterval_from_asc</function></term>
2143
Parse an interval from its textual representation.
2145
interval *PGTYPESinterval_from_asc(char *str, char **endptr);
2147
The function parses the input string <literal>str</> and returns a
2148
pointer to an allocated interval variable.
2149
At the moment ecpg always parses
2150
the complete string and so it currently does not support to store the
2151
address of the first invalid character in <literal>*endptr</literal>.
2152
You can safely set <literal>endptr</literal> to NULL.
2157
<varlistentry id="PGTYPESintervaltoasc">
2158
<term><function>PGTYPESinterval_to_asc</function></term>
2161
Convert a variable of type interval to its textual representation.
2163
char *PGTYPESinterval_to_asc(interval *span);
2165
The function converts the interval variable that <literal>span</>
2166
points to into a C char*. The output looks like this example:
2167
<literal>@ 1 day 12 hours 59 mins 10 secs</literal>.
2172
<varlistentry id="PGTYPESintervalcopy">
2173
<term><function>PGTYPESinterval_copy</function></term>
2176
Copy a variable of type interval.
2178
int PGTYPESinterval_copy(interval *intvlsrc, interval *intvldest);
2180
The function copies the interval variable that <literal>intvlsrc</>
2181
points to into the variable that <literal>intvldest</> points to. Note
2182
that you need to allocate the memory for the destination variable
2192
<title>The decimal type</title>
2194
The decimal type is similar to the numeric type. However it is limited to
2195
a maximal precision of 30 significant digits. In contrast to the numeric
2196
type which can be created on the heap only, the decimal type can be
2197
created either on the stack or on the heap (by means of the functions
2198
PGTYPESdecimal_new() and PGTYPESdecimal_free(). There are a lot of other
2199
functions that deal with the decimal type in the <productname>Informix</productname> compatibility
2200
mode described in <xref linkend="ecpg-informix-compat">.
2203
The following functions can be used to work with the decimal type and are
2204
not only contained in the <literal>libcompat</> library.
2207
<term><function>PGTYPESdecimal_new</function></term>
2210
Request a pointer to a newly allocated decimal variable.
2212
decimal *PGTYPESdecimal_new(void);
2219
<term><function>PGTYPESdecimal_free</function></term>
2222
Free a decimal type, release all of its memory.
2224
void PGTYPESdecimal_free(decimal *var);
2234
<title>errno values of pgtypeslib </title>
2238
<term><literal>PGTYPES_NUM_BAD_NUMERIC</literal></term>
2241
An argument should contain a numeric variable (or point to a numeric
2242
variable) but in fact its in-memory representation was invalid.
2248
<term><literal>PGTYPES_NUM_OVERFLOW</literal></term>
2251
An overflow occurred. Since the numeric type can deal with almost
2252
arbitrary precision, converting a numeric variable into other types
2253
might cause overflow.
2259
<term><literal>PGTYPES_NUM_OVERFLOW</literal></term>
2262
An underflow occurred. Since the numeric type can deal with almost
2263
arbitrary precision, converting a numeric variable into other types
2264
might cause underflow.
2270
<term><literal>PGTYPES_NUM_DIVIDE_ZERO</literal></term>
2273
A division by zero has been attempted.
2279
<term><literal>PGTYPES_DATE_BAD_DATE</literal></term>
2288
<term><literal>PGTYPES_DATE_ERR_EARGS</literal></term>
2297
<term><literal>PGTYPES_DATE_ERR_ENOSHORTDATE</literal></term>
2306
<term><literal>PGTYPES_INTVL_BAD_INTERVAL</literal></term>
2315
<term><literal>PGTYPES_DATE_ERR_ENOTDMY</literal></term>
2324
<term><literal>PGTYPES_DATE_BAD_DAY</literal></term>
2333
<term><literal>PGTYPES_DATE_BAD_MONTH</literal></term>
2342
<term><literal>PGTYPES_TS_BAD_TIMESTAMP</literal></term>
2354
<title>Special constants of pgtypeslib </title>
2357
<varlistentry id="PGTYPESInvalidTimestamp">
2358
<term><literal>PGTYPESInvalidTimestamp</literal></term>
2361
A value of type timestamp representing an invalid time stamp. This is
2362
returned by the function <function>PGTYPEStimestamp_from_asc</> on
2364
Note that due to the internal representation of the timestamp datatype,
2365
<literal>PGTYPESInvalidTimestamp</literal> is also a valid timestamp at
2366
the same time. It is set to <literal>1899-12-31 23:59:59</>. In order
2367
to detect errors, make sure that your application does not only test
2368
for <literal>PGTYPESInvalidTimestamp</literal> but also for
2369
<literal>errno != 0</> after each call to
2370
<function>PGTYPEStimestamp_from_asc</>.
2379
<sect1 id="ecpg-informix-compat">
2380
<title><productname>Informix</productname> compatibility mode</title>
2382
ecpg can be run in a so-called <firstterm>Informix compatibility mode</>. If
2383
this mode is active, it tries to behave as if it were the <productname>Informix</productname>
2384
precompiler for <productname>Informix</productname> E/SQL. Generally spoken this will allow you to use
2385
the dollar sign instead of the <literal>EXEC SQL</> primitive to introduce
2386
embedded SQL commands.:
2389
$CONNECT TO :dbname;
2390
$CREATE TABLE test(i INT PRIMARY KEY, j INT);
2391
$INSERT INTO test(i, j) VALUES (7, :j);
2396
There are two compatiblity modes: INFORMIX, INFORMIX_SE
2399
When linking programs that use this compatibility mode, remember to link
2400
against <literal>libcompat</> that is shipped with ecpg.
2403
Besides the previously explained syntactic sugar, the <productname>Informix</productname> compatibility
2404
mode ports some functions for input, output and transformation of data as
2405
well as embedded SQL statements known from E/SQL to ecpg.
2408
<productname>Informix</productname> compatibility mode is closely connected to the pgtypeslib library
2409
of ecpg. pgtypeslib maps SQL data types to data types within the C host
2410
program and most of the additional functions of the <productname>Informix</productname> compatibility
2411
mode allow you to operate on those C host program types. Note however that
2412
the extent of the compatibility is limited. It does not try to copy <productname>Informix</productname>
2413
behaviour; it allows you to do more or less the same operations and gives
2414
you functions that have the same name and the same basic behavior but it is
2415
no drop-in replacement if you are using <productname>Informix</productname> at the moment. Moreover,
2416
some of the data types are different. For example,
2417
<productname>PostgreSQL's</productname> datetime and interval types do not
2418
know about ranges like for example <literal>YEAR TO MINUTE</> so you won't
2419
find support in ecpg for that either.
2423
<title>Additional embedded SQL statements</title>
2427
<term><literal>CLOSE DATABASE</></term>
2430
This statement closes the current connection. In fact, this is a
2431
synonym for ecpg's <literal>DISCONNECT CURRENT</>.:
2433
$CLOSE DATABASE; /* close the current connection */
2434
EXEC SQL CLOSE DATABASE;
2444
<title>Additional functions</title>
2448
<term><function>decadd</></term>
2451
Add two decimal type values.
2453
int decadd(decimal *arg1, decimal *arg2, decimal *sum);
2455
The function receives a pointer to the first operand of type decimal
2456
(<literal>arg1</>), a pointer to the second operand of type decimal
2457
(<literal>arg2</>) and a pointer to a value of type decimal that will
2458
contain the sum (<literal>sum</>). On success, the function returns 0.
2459
ECPG_INFORMIX_NUM_OVERFLOW is returned in case of overflow and
2460
ECPG_INFORMIX_NUM_UNDERFLOW in case of underflow. -1 is returned for
2461
other failures and errno is set to the respective errno number of the
2468
<term><function>deccmp</></term>
2471
Compare two variables of type decimal.
2473
int deccmp(decimal *arg1, decimal *arg2);
2475
The function receives a pointer to the first decimal value
2476
(<literal>arg1</>), a pointer to the second decimal value
2477
(<literal>arg2</>) and returns an integer value that indicates which is
2482
1, if the value that <literal>arg1</> points to is bigger than the
2483
value that <literal>var2</> points to
2488
-1, if the value that <literal>arg1</> points to is smaller than the
2489
value that <literal>arg2</> points to </para>
2493
0, if the value that <literal>arg1</> points to and the value that
2494
<literal>arg2</> points to are equal
2503
<term><function>deccopy</></term>
2506
Copy a decimal value.
2508
void deccopy(decimal *src, decimal *target);
2510
The function receives a pointer to the decimal value that should be
2511
copied as the first argument (<literal>src</>) and a pointer to the
2512
target structure of type decimal (<literal>target</>) as the second
2519
<term><function>deccvasc</></term>
2522
Convert a value from its ASCII representation into a decimal type.
2524
int deccvasc(char *cp, int len, decimal *np);
2526
The function receives a pointer to string that contains the string
2527
representation of the number to be converted (<literal>cp</>) as well
2528
as its length <literal>len</>. <literal>np</> is a pointer to the
2529
decimal value that saves the result of the operation.
2532
Valid formats are for example:
2533
<literal>-2</literal>,
2534
<literal>.794</literal>,
2535
<literal>+3.44</literal>,
2536
<literal>592.49E07</literal> or
2537
<literal>-32.84e-4</literal>.
2540
The function returns 0 on success. If overflow or underflow occurred,
2541
<literal>ECPG_INFORMIX_NUM_OVERFLOW</> or
2542
<literal>ECPG_INFORMIX_NUM_UNDERFLOW</> is returned. If the ASCII
2543
representation could not be parsed,
2544
<literal>ECPG_INFORMIX_BAD_NUMERIC</> is returned or
2545
<literal>ECPG_INFORMIX_BAD_EXPONENT</> if this problem occurred while
2546
parsing the exponent.
2552
<term><function>deccvdbl</></term>
2555
Convert a value of type double to a value of type decimal.
2557
int deccvdbl(double dbl, decimal *np);
2559
The function receives the variable of type double that should be
2560
converted as its first argument (<literal>dbl</>). As the second
2561
argument (<literal>np</>), the function receives a pointer to the
2562
decimal variable that should hold the result of the operation.
2565
The function returns 0 on success and a negative value if the
2572
<term><function>deccvint</></term>
2575
Convert a value of type int to a value of type decimal.
2577
int deccvint(int in, decimal *np);
2579
The function receives the variable of type int that should be
2580
converted as its first argument (<literal>in</>). As the second
2581
argument (<literal>np</>), the function receives a pointer to the
2582
decimal variable that should hold the result of the operation.
2585
The function returns 0 on success and a negative value if the
2592
<term><function>deccvlong</></term>
2595
Convert a value of type long to a value of type decimal.
2597
int deccvlong(long lng, decimal *np);
2599
The function receives the variable of type long that should be
2600
converted as its first argument (<literal>lng</>). As the second
2601
argument (<literal>np</>), the function receives a pointer to the
2602
decimal variable that should hold the result of the operation.
2605
The function returns 0 on success and a negative value if the
2612
<term><function>decdiv</></term>
2615
Divide two variables of type decimal.
2617
int decdiv(decimal *n1, decimal *n2, decimal *result);
2619
The function receives pointers to the variables that are the first
2620
(<literal>n1</>) and the second (<literal>n2</>) operands and
2621
calculates <literal>n1</>/<literal>n2</>. <literal>result</> is a
2622
pointer to the variable that should hold the result of the operation.
2625
On success, 0 is returned and a negative value if the division fails.
2626
If overflow or underflow occurred, the function returns
2627
<literal>ECPG_INFORMIX_NUM_OVERFLOW</> or
2628
<literal>ECPG_INFORMIX_NUM_UNDERFLOW</> respectively. If an attempt to
2629
divide by zero is observed, the function returns
2630
<literal>ECPG_INFORMIX_DIVIDE_ZERO</literal>.
2636
<term><function>decmul</></term>
2639
Multiply two decimal values.
2641
int decmul(decimal *n1, decimal *n2, decimal *result);
2643
The function receives pointers to the variables that are the first
2644
(<literal>n1</>) and the second (<literal>n2</>) operands and
2645
calculates <literal>n1</>*<literal>n2</>. <literal>result</> is a
2646
pointer to the variable that should hold the result of the operation.
2649
On success, 0 is returned and a negative value if the multiplication
2650
fails. If overflow or underflow occurred, the function returns
2651
<literal>ECPG_INFORMIX_NUM_OVERFLOW</> or
2652
<literal>ECPG_INFORMIX_NUM_UNDERFLOW</> respectively.
2658
<term><function>decsub</></term>
2661
Subtract one decimal value from another.
2663
int decsub(decimal *n1, decimal *n2, decimal *result);
2665
The function receives pointers to the variables that are the first
2666
(<literal>n1</>) and the second (<literal>n2</>) operands and
2667
calculates <literal>n1</>-<literal>n2</>. <literal>result</> is a
2668
pointer to the variable that should hold the result of the operation.
2671
On success, 0 is returned and a negative value if the subtraction
2672
fails. If overflow or underflow occurred, the function returns
2673
<literal>ECPG_INFORMIX_NUM_OVERFLOW</> or
2674
<literal>ECPG_INFORMIX_NUM_UNDERFLOW</> respectively.
2680
<term><function>dectoasc</></term>
2683
Convert a variable of type decimal to its ASCII representation in a C
2686
int dectoasc(decimal *np, char *cp, int len, int right)
2688
The function receives a pointer to a variable of type decimal
2689
(<literal>np</>) that it converts to its textual representation.
2690
<literal>cp</> is the buffer that should hold the result of the
2691
operation. The parameter <literal>right</> specifies, how many digits
2692
right of the decimal point should be included in the output. The result
2693
will be rounded to this number of decimal digits. Setting
2694
<literal>right</> to -1 indicates that all available decimal digits
2695
should be included in the output. If the length of the output buffer,
2696
which is indicated by <literal>len</> is not sufficient to hold the
2697
textual representation including the trailing NUL character, only a
2698
single <literal>*</> character is stored in the result and -1 is
2702
The function returns either -1 if the buffer <literal>cp</> was too
2703
small or <literal>ECPG_INFORMIX_OUT_OF_MEMORY</> if memory was
2710
<term><function>dectodbl</></term>
2713
Convert a variable of type decimal to a double.
2715
int dectodbl(decimal *np, double *dblp);
2717
The function receives a pointer to the decimal value to convert
2718
(<literal>np</>) and a pointer to the double variable that
2719
should hold the result of the operation (<literal>dblp</>).
2722
On success, 0 is returned and a negative value if the conversion
2729
<term><function>dectoint</></term>
2732
Convert a variable to type decimal to an integer.
2734
int dectoint(decimal *np, int *ip);
2736
The function receives a pointer to the decimal value to convert
2737
(<literal>np</>) and a pointer to the integer variable that
2738
should hold the result of the operation (<literal>ip</>).
2741
On success, 0 is returned and a negative value if the conversion
2742
failed. If an overflow occurred, <literal>ECPG_INFORMIX_NUM_OVERFLOW</>
2746
Note that the ecpg implementation differs from the <productname>Informix</productname>
2747
implementation. <productname>Informix</productname> limits an integer to the range from -32767 to
2748
32767, while the limits in the ecpg implementation depend on the
2749
architecture (<literal>-INT_MAX .. INT_MAX</>).
2755
<term><function>dectolong</></term>
2758
Convert a variable to type decimal to a long integer.
2760
int dectolong(decimal *np, long *lngp);
2762
The function receives a pointer to the decimal value to convert
2763
(<literal>np</>) and a pointer to the long variable that
2764
should hold the result of the operation (<literal>lngp</>).
2767
On success, 0 is returned and a negative value if the conversion
2768
failed. If an overflow occurred, <literal>ECPG_INFORMIX_NUM_OVERFLOW</>
2772
Note that the ecpg implementation differs from the <productname>Informix</productname>
2773
implementation. <productname>Informix</productname> limits a long integer to the range from
2774
-2,147,483,647 to 2,147,483,647, while the limits in the ecpg
2775
implementation depend on the architecture (<literal>-LONG_MAX ..
2782
<term><function>rdatestr</></term>
2785
Converts a date to a C char* string.
2787
int rdatestr(date d, char *str);
2789
The function receives two arguments, the first one is the date to
2790
convert (<literal>d</> and the second one is a pointer to the target
2791
string. The output format is always <literal>yyyy-mm-dd</>, so you need
2792
to allocate at least 11 bytes (including the NUL-terminator) for the
2796
The function returns 0 on success and a negative value in case of
2800
Note that ecpg's implementation differs from the <productname>Informix</productname>
2801
implementation. In <productname>Informix</productname> the format can be influenced by setting
2802
environment variables. In ecpg however, you cannot change the output
2809
<term><function>rstrdate</></term>
2812
Parse the textual representation of a date.
2814
int rstrdate(char *str, date *d);
2816
The function receives the textual representation of the date to convert
2817
(<literal>str</>) and a pointer to a variable of type date
2818
(<literal>d</>). This function does not allow you to specify a format
2819
mask. It uses the default format mask of <productname>Informix</productname> which is
2820
<literal>mm/dd/yyyy</>. Internally, this function is implemented by
2821
means of <function>rdefmtdate</>. Therefore, <function>rstrdate</> is
2822
not faster and if you have the choice you should opt for
2823
<function>rdefmtdate</> which allows you to specify the format mask
2827
The function returns the same values as <function>rdefmtdate</>.
2833
<term><function>rtoday</></term>
2836
Get the current date.
2838
void rtoday(date *d);
2840
The function receives a pointer to a date variable (<literal>d</>)
2841
that it sets to the current date.
2844
Internally this function uses the <xref linkend="PGTYPESdatetoday">
2851
<term><function>rjulmdy</></term>
2854
Extract the values for the day, the month and the year from a variable
2857
int rjulmdy(date d, short mdy[3]);
2859
The function receives the date <literal>d</> and a pointer to an array
2860
of 3 short integer values <literal>mdy</>. The variable name indicates
2861
the sequential order: <literal>mdy[0]</> will be set to contain the
2862
number of the month, <literal>mdy[1]</> will be set to the value of the
2863
day and <literal>mdy[2]</> will contain the year.
2866
The function always returns 0 at the moment.
2869
Internally the function uses the <xref linkend="PGTYPESdatejulmdy">
2876
<term><function>rdefmtdate</></term>
2879
Use a format mask to convert a character string to a value of type
2882
int rdefmtdate(date *d, char *fmt, char *str);
2884
The function receives a pointer to the date value that should hold the
2885
result of the operation (<literal>d</>), the format mask to use for
2886
parsing the date (<literal>fmt</>) and the C char* string containing
2887
the textual representation of the date (<literal>str</>). The textual
2888
representation is expected to match the format mask. However you do not
2889
need to have a 1:1 mapping of the string to the format mask. The
2890
function only analyzes the sequential order and looks for the literals
2891
<literal>yy</literal> or <literal>yyyy</literal> that indicate the
2892
position of the year, <literal>mm</literal> to indicate the position of
2893
the month and <literal>dd</literal> to indicate the position of the
2897
The function returns the following values:
2901
0 - The function terminated successfully.
2906
<literal>ECPG_INFORMIX_ENOSHORTDATE</> - The date does not contain
2907
delimiters between day, month and year. In this case the input
2908
string must be exactly 6 or 8 bytes long but isn't.
2913
<literal>ECPG_INFORMIX_ENOTDMY</> - The format string did not
2914
correctly indicate the sequential order of year, month and day.
2919
<literal>ECPG_INFORMIX_BAD_DAY</> - The input string does not
2920
contain a valid day.
2925
<literal>ECPG_INFORMIX_BAD_MONTH</> - The input string does not
2926
contain a valid month.
2931
<literal>ECPG_INFORMIX_BAD_YEAR</> - The input string does not
2932
contain a valid year.
2938
Internally this function is implemented to use the <xref
2939
linkend="PGTYPESdatedefmtasc"> function. See the reference there for a
2940
table of example input.
2946
<term><function>rfmtdate</></term>
2949
Convert a variable of type date to its textual representation using a
2952
int rfmtdate(date d, char *fmt, char *str);
2954
The function receives the date to convert (<literal>d</>), the format
2955
mask (<literal>fmt</>) and the string that will hold the textual
2956
representation of the date (<literal>str</>).
2959
On success, 0 is returned and a negative value if an error occurred.
2962
Internally this function uses the <xref linkend="PGTYPESdatefmtasc">
2963
function, see the reference there for examples.
2969
<term><function>rmdyjul</></term>
2972
Create a date value from an array of 3 short integers that specify the
2973
day, the month and the year of the date.
2975
int rmdyjul(short mdy[3], date *d);
2977
The function receives the array of the 3 short integers
2978
(<literal>mdy</>) and a pointer to a variable of type date that should
2979
hold the result of the operation.
2982
Currently the function returns always 0.
2985
Internally the function is implemented to use the function <xref
2986
linkend="PGTYPESdatemdyjul">.
2992
<term><function>rdayofweek</></term>
2995
Return a number representing the day of the week for a date value.
2997
int rdayofweek(date d);
2999
The function receives the date variable <literal>d</> as its only
3000
argument and returns an integer that indicates the day of the week for
3041
Internally the function is implemented to use the function <xref
3042
linkend="PGTYPESdatedayofweek">.
3048
<term><function>dtcurrent</></term>
3051
Retrieve the current timestamp.
3053
void dtcurrent(timestamp *ts);
3055
The function retrieves the current timestamp and saves it into the
3056
timestamp variable that <literal>ts</> points to.
3062
<term><function>dtcvasc</></term>
3065
Parses a timestamp from its textual representation in ANSI standard
3066
into a timestamp variable.
3068
int dtcvasc(char *str, timestamp *ts);
3070
The function receives the string to parse (<literal>str</>) and a
3071
pointer to the timestamp variable that should hold the result of the
3072
operation (<literal>ts</>).
3075
The function returns 0 on success and a negative value in case of
3079
Internally this function uses the <xref
3080
linkend="PGTYPEStimestampfromasc"> function. See the reference there
3081
for a table with example inputs.
3087
<term><function>dtcvfmtasc</></term>
3090
Parses a timestamp from its textual representation in ANSI standard
3091
using a format mask into a timestamp variable.
3093
dtcvfmtasc(char *inbuf, char *fmtstr, timestamp *dtvalue)
3095
The function receives the string to parse (<literal>inbuf</>), the
3096
format mask to use (<literal>fmtstr</>) and a pointer to the timestamp
3097
variable that should hold the result of the operation (<literal>ts</>).
3100
This functions is implemented by means of the <xref
3101
linkend="PGTYPEStimestampdefmtasc">. See the documentation
3102
there for a list of format specifiers that can be used.
3105
The function returns 0 on success and a negative value in case of
3112
<term><function>dtsub</></term>
3115
Subtract one timestamp from another and return a variable of type
3118
int dtsub(timestamp *ts1, timestamp *ts2, interval *iv);
3120
The function will subtract the timestamp variable that <literal>ts2</>
3121
points to from the timestamp variable that <literal>ts1</> points to
3122
and will store the result in the interval variable that <literal>iv</>
3126
Upon success, the function returns 0 and a negative value if an
3133
<term><function>dttoasc</></term>
3136
Convert a timestamp variable to a C char* string.
3138
int dttoasc(timestamp *ts, char *output);
3140
The function receives a pointer to the timestamp variable to convert
3141
(<literal>ts</>) and the string that should hold the result of the
3142
operation <literal>output</>). It converts <literal>ts</> to its
3143
textual representation in the ANSI SQL standard which is defined to
3144
be <literal>YYYY-MM-DD HH:MM:SS</literal>.
3147
Upon success, the function returns 0 and a negative value if an
3154
<term><function>dttofmtasc</></term>
3157
Convert a timestamp variable to a C char* using a format mask.
3159
int dttofmtasc(timestamp *ts, char *output, int str_len, char *fmtstr);
3161
The function receives a pointer to the timestamp to convert as its
3162
first argument (<literal>ts</>), a pointer to the output buffer
3163
(<literal>output</>), the maximal length that has been allocated for
3164
the output buffer (<literal>str_len</literal>) and the format mask to
3165
use for the conversion (<literal>fmtstr</literal>).
3168
Upon success, the function returns 0 and a negative value if an
3172
Internally, this function uses the <xref
3173
linkend="PGTYPEStimestampfmtasc"> function. See the reference there for
3174
information on what format mask specifiers can be used.
3180
<term><function>intoasc</></term>
3183
Convert an interval variable to a C char* string.
3185
int intoasc(interval *i, char *str);
3187
The function receives a pointer to the interval variable to convert
3188
(<literal>i</>) and the string that should hold the result of the
3189
operation <literal>str</>). It converts <literal>i</> to its
3190
textual representation in the ANSI SQL standard which is defined to
3191
be <literal>YYYY-MM-DD HH:MM:SS</literal>.
3194
Upon success, the function returns 0 and a negative value if an
3201
<term><function>rfmtlong</></term>
3204
Convert a long integer value to its textual representation using a
3207
int rfmtlong(long lng_val, char *fmt, char *outbuf);
3209
The function receives the long value <literal>lng_val</>, the format
3210
mask <literal>fmt</> and a pointer to the output buffer
3211
<literal>outbuf</>. It converts the long value according to the format
3212
mask to its textual representation.
3215
The format mask can be composed of the following format specifying
3220
<literal>*</literal> (asterisk) - if this position would be blank
3221
otherwise, fill it with an asterisk.
3226
<literal>&</literal> (ampersand) - if this position would be
3227
blank otherwise, fill it with a zero.
3232
<literal>#</literal> - turn leading zeroes into blanks.
3237
<literal><</literal> - left-justify the number in the string.
3242
<literal>,</literal> (comma) - group numbers of four or more digits
3243
into groups of three digits separated by a comma.
3248
<literal>.</literal> (period) - this character separates the
3249
whole-number part of the number from the fractional part.
3254
<literal>-</literal> (minus) - the minus sign appears if the number
3255
is a negative value.
3260
<literal>+</literal> (plus) - the plus sign appears if the number is
3266
<literal>(</literal> - this replaces the minus sign in front of the
3267
negative number. The minus sign will not appear.
3272
<literal>)</literal> - this character replaces the minus and is
3273
printed behind the negative value.
3278
<literal>$</literal> - the currency symbol.
3287
<term><function>rupshift</></term>
3290
Convert a string to upper case.
3292
void rupshift(char *str);
3294
The function receives a pointer to the string and transforms every
3295
lower case character to upper case.
3301
<term><function>byleng</></term>
3304
Return the number of characters in a string without counting trailing
3307
int byleng(char *str, int len);
3309
The function expects a fixed-length string as its first argument
3310
(<literal>str</>) and its length as its second argument
3311
(<literal>len</>). It returns the number of significant characters,
3312
that is the length of the string without trailing blanks.
3318
<term><function>ldchar</></term>
3321
Copy a fixed-length string into a null-terminated string.
3323
void ldchar(char *src, int len, char *dest);
3325
The function receives the fixed-length string to copy
3326
(<literal>src</>), its length (<literal>len</>) and a pointer to the
3327
destination memory (<literal>dest</>). Note that you need to reserve at
3328
least <literal>len+1</> bytes for the string that <literal>dest</>
3329
points to. The function copies at most <literal>len</> bytes to the new
3330
location (less if the source string has trailing blanks) and adds the
3337
<term><function>rgetmsg</></term>
3341
int rgetmsg(int msgnum, char *s, int maxsize);
3343
This function exists but is not implemented at the moment!
3349
<term><function>rtypalign</></term>
3353
int rtypalign(int offset, int type);
3355
This function exists but is not implemented at the moment!
3361
<term><function>rtypmsize</></term>
3365
int rtypmsize(int type, int len);
3367
This function exists but is not implemented at the moment!
3373
<term><function>rtypwidth</></term>
3377
int rtypwidth(int sqltype, int sqllen);
3379
This function exists but is not implemented at the moment!
3384
<varlistentry id="rsetnull">
3385
<term><function>rsetnull</></term>
3388
Set a variable to NULL.
3390
int rsetnull(int t, char *ptr);
3392
The function receives an integer that indicates the type of the
3393
variable and a pointer to the variable itself that is casted to a C
3397
The following types exist:
3401
<literal>CCHARTYPE</literal> - For a variable of type <type>char</type> or <type>char*</type>
3406
<literal>CSHORTTYPE</literal> - For a variable of type <type>short int</type>
3411
<literal>CINTTYPE</literal> - For a variable of type <type>int</type>
3416
<literal>CBOOLTYPE</literal> - For a variable of type <type>boolean</type>
3421
<literal>CFLOATTYPE</literal> - For a variable of type <type>float</type>
3426
<literal>CLONGTYPE</literal> - For a variable of type <type>long</type>
3431
<literal>CDOUBLETYPE</literal> - For a variable of type <type>double</type>
3436
<literal>CDECIMALTYPE</literal> - For a variable of type <type>decimal</type>
3441
<literal>CDATETYPE</literal> - For a variable of type <type>date</type>
3446
<literal>CDTIMETYPE</literal> - For a variable of type <type>timestamp</type>
3453
Here is an example of a call to this function:
3454
<programlisting><![CDATA[
3459
rsetnull(CCHARTYPE, (char *) c);
3460
rsetnull(CSHORTTYPE, (char *) &s);
3461
rsetnull(CINTTYPE, (char *) &i);
3469
<term><function>risnull</></term>
3472
Test if a variable is NULL.
3474
int risnull(int t, char *ptr);
3476
The function receives the type of the variable to test (<literal>t</>)
3477
as well a pointer to this variable (<literal>ptr</>). Note that the
3478
latter needs to be casted to a char*. See the function <xref
3479
linkend="rsetnull"> for a list of possible variable types.
3482
Here is an example of how to use this function:
3483
<programlisting><![CDATA[
3488
risnull(CCHARTYPE, (char *) c);
3489
risnull(CSHORTTYPE, (char *) &s);
3490
risnull(CINTTYPE, (char *) &i);
3501
<title>Additional constants</title>
3503
Note that all constants here describe errors and all of them are defined
3504
to represent negative values. In the descriptions of the different
3505
constants you can also find the value that the constants represent in the
3506
current implementation. However you should not rely on this number. You can
3507
however rely on the fact all of them are defined to represent negative
3511
<term><literal>ECPG_INFORMIX_NUM_OVERFLOW</></term>
3514
Functions return this value if an overflow occurred in a
3515
calculation. Internally it is defined to -1200 (the <productname>Informix</productname>
3522
<term><literal>ECPG_INFORMIX_NUM_UNDERFLOW</></term>
3525
Functions return this value if an underflow occurred in a calculation.
3526
Internally it is defined to -1201 (the <productname>Informix</productname> definition).
3532
<term><literal>ECPG_INFORMIX_DIVIDE_ZERO</></term>
3535
Functions return this value if an attempt to divide by zero is
3536
observed. Internally it is defined to -1202 (the <productname>Informix</productname> definition).
3542
<term><literal>ECPG_INFORMIX_BAD_YEAR</></term>
3545
Functions return this value if a bad value for a year was found while
3546
parsing a date. Internally it is defined to -1204 (the <productname>Informix</productname>
3553
<term><literal>ECPG_INFORMIX_BAD_MONTH</></term>
3556
Functions return this value if a bad value for a month was found while
3557
parsing a date. Internally it is defined to -1205 (the <productname>Informix</productname>
3564
<term><literal>ECPG_INFORMIX_BAD_DAY</></term>
3567
Functions return this value if a bad value for a day was found while
3568
parsing a date. Internally it is defined to -1206 (the <productname>Informix</productname>
3575
<term><literal>ECPG_INFORMIX_ENOSHORTDATE</></term>
3578
Functions return this value if a parsing routine needs a short date
3579
representation but did not get the date string in the right length.
3580
Internally it is defined to -1209 (the <productname>Informix</productname> definition).
3586
<term><literal>ECPG_INFORMIX_DATE_CONVERT</></term>
3589
Functions return this value if Internally it is defined to -1210 (the
3590
<productname>Informix</productname> definition).
3596
<term><literal>ECPG_INFORMIX_OUT_OF_MEMORY</></term>
3599
Functions return this value if Internally it is defined to -1211 (the
3600
<productname>Informix</productname> definition).
3606
<term><literal>ECPG_INFORMIX_ENOTDMY</></term>
3609
Functions return this value if a parsing routine was supposed to get a
3610
format mask (like <literal>mmddyy</>) but not all fields were listed
3611
correctly. Internally it is defined to -1212 (the <productname>Informix</productname> definition).
3617
<term><literal>ECPG_INFORMIX_BAD_NUMERIC</></term>
3620
Functions return this value either if a parsing routine cannot parse
3621
the textual representation for a numeric value because it contains
3622
errors or if a routine cannot complete a calculation involving numeric
3623
variables because at least one of the numeric variables is invalid.
3624
Internally it is defined to -1213 (the <productname>Informix</productname> definition).
3630
<term><literal>ECPG_INFORMIX_BAD_EXPONENT</></term>
3633
Functions return this value if Internally it is defined to -1216 (the
3634
<productname>Informix</productname> definition).
3640
<term><literal>ECPG_INFORMIX_BAD_DATE</></term>
3643
Functions return this value if Internally it is defined to -1218 (the
3644
<productname>Informix</productname> definition).
3650
<term><literal>ECPG_INFORMIX_EXTRA_CHARS</></term>
3653
Functions return this value if Internally it is defined to -1264 (the
3654
<productname>Informix</productname> definition).
3663
<sect1 id="ecpg-descriptors">
3664
<title>Using SQL Descriptor Areas</title>
3667
An SQL descriptor area is a more sophisticated method for
3668
processing the result of a <command>SELECT</command> or
3669
<command>FETCH</command> statement. An SQL descriptor area groups
3670
the data of one row of data together with metadata items into one
3671
data structure. The metadata is particularly useful when executing
3672
dynamic SQL statements, where the nature of the result columns might
3673
not be known ahead of time.
3677
An SQL descriptor area consists of a header, which contains
3678
information concerning the entire descriptor, and one or more item
3679
descriptor areas, which basically each describe one column in the
3684
Before you can use an SQL descriptor area, you need to allocate one:
3686
EXEC SQL ALLOCATE DESCRIPTOR <replaceable>identifier</replaceable>;
3688
The identifier serves as the <quote>variable name</quote> of the
3689
descriptor area. <remark>The scope of the allocated descriptor is WHAT?.</remark>
3690
When you don't need the descriptor anymore, you should deallocate
3693
EXEC SQL DEALLOCATE DESCRIPTOR <replaceable>identifier</replaceable>;
3698
To use a descriptor area, specify it as the storage target in an
3699
<literal>INTO</literal> clause, instead of listing host variables:
3701
EXEC SQL FETCH NEXT FROM mycursor INTO DESCRIPTOR mydesc;
3706
Now how do you get the data out of the descriptor area? You can
3707
think of the descriptor area as a structure with named fields. To
3708
retrieve the value of a field from the header and store it into a
3709
host variable, use the following command:
3711
EXEC SQL GET DESCRIPTOR <replaceable>name</replaceable> :<replaceable>hostvar</replaceable> = <replaceable>field</replaceable>;
3713
Currently, there is only one header field defined:
3714
<replaceable>COUNT</replaceable>, which tells how many item
3715
descriptor areas exist (that is, how many columns are contained in
3716
the result). The host variable needs to be of an integer type. To
3717
get a field from the item descriptor area, use the following
3720
EXEC SQL GET DESCRIPTOR <replaceable>name</replaceable> VALUE <replaceable>num</replaceable> :<replaceable>hostvar</replaceable> = <replaceable>field</replaceable>;
3722
<replaceable>num</replaceable> can be a literal integer or a host
3723
variable containing an integer. Possible fields are:
3727
<term><literal>CARDINALITY</literal> (integer)</term>
3730
number of rows in the result set
3736
<term><literal>DATA</literal></term>
3739
actual data item (therefore, the data type of this field
3740
depends on the query)
3746
<term><literal>DATETIME_INTERVAL_CODE</literal> (integer)</term>
3755
<term><literal>DATETIME_INTERVAL_PRECISION</literal> (integer)</term>
3764
<term><literal>INDICATOR</literal> (integer)</term>
3767
the indicator (indicating a null value or a value truncation)
3773
<term><literal>KEY_MEMBER</literal> (integer)</term>
3782
<term><literal>LENGTH</literal> (integer)</term>
3785
length of the datum in characters
3791
<term><literal>NAME</literal> (string)</term>
3800
<term><literal>NULLABLE</literal> (integer)</term>
3809
<term><literal>OCTET_LENGTH</literal> (integer)</term>
3812
length of the character representation of the datum in bytes
3818
<term><literal>PRECISION</literal> (integer)</term>
3821
precision (for type <type>numeric</type>)
3827
<term><literal>RETURNED_LENGTH</literal> (integer)</term>
3830
length of the datum in characters
3836
<term><literal>RETURNED_OCTET_LENGTH</literal> (integer)</term>
3839
length of the character representation of the datum in bytes
3845
<term><literal>SCALE</literal> (integer)</term>
3848
scale (for type <type>numeric</type>)
3854
<term><literal>TYPE</literal> (integer)</term>
3857
numeric code of the data type of the column
3865
<sect1 id="ecpg-errors">
3866
<title>Error Handling</title>
3869
This section describes how you can handle exceptional conditions
3870
and warnings in an embedded SQL program. There are several
3871
nonexclusive facilities for this.
3875
<title>Setting Callbacks</title>
3878
One simple method to catch errors and warnings is to set a
3879
specific action to be executed whenever a particular condition
3882
EXEC SQL WHENEVER <replaceable>condition</replaceable> <replaceable>action</replaceable>;
3887
<replaceable>condition</replaceable> can be one of the following:
3891
<term><literal>SQLERROR</literal></term>
3894
The specified action is called whenever an error occurs during
3895
the execution of an SQL statement.
3901
<term><literal>SQLWARNING</literal></term>
3904
The specified action is called whenever a warning occurs
3905
during the execution of an SQL statement.
3911
<term><literal>NOT FOUND</literal></term>
3914
The specified action is called whenever an SQL statement
3915
retrieves or affects zero rows. (This condition is not an
3916
error, but you might be interested in handling it specially.)
3924
<replaceable>action</replaceable> can be one of the following:
3928
<term><literal>CONTINUE</literal></term>
3931
This effectively means that the condition is ignored. This is
3938
<term><literal>GOTO <replaceable>label</replaceable></literal></term>
3939
<term><literal>GO TO <replaceable>label</replaceable></literal></term>
3942
Jump to the specified label (using a C <literal>goto</literal>
3949
<term><literal>SQLPRINT</literal></term>
3952
Print a message to standard error. This is useful for simple
3953
programs or during prototyping. The details of the message
3954
cannot be configured.
3960
<term><literal>STOP</literal></term>
3963
Call <literal>exit(1)</literal>, which will terminate the
3970
<term><literal>DO BREAK</literal></term>
3973
Execute the C statement <literal>break</literal>. This should
3974
only be used in loops or <literal>switch</literal> statements.
3980
<term><literal>CALL <replaceable>name</replaceable> (<replaceable>args</replaceable>)</literal></term>
3981
<term><literal>DO <replaceable>name</replaceable> (<replaceable>args</replaceable>)</literal></term>
3984
Call the specified C functions with the specified arguments.
3990
The SQL standard only provides for the actions
3991
<literal>CONTINUE</literal> and <literal>GOTO</literal> (and
3992
<literal>GO TO</literal>).
3996
Here is an example that you might want to use in a simple program.
3997
It prints a simple message when a warning occurs and aborts the
3998
program when an error happens:
4000
EXEC SQL WHENEVER SQLWARNING SQLPRINT;
4001
EXEC SQL WHENEVER SQLERROR STOP;
4006
The statement <literal>EXEC SQL WHENEVER</literal> is a directive
4007
of the SQL preprocessor, not a C statement. The error or warning
4008
actions that it sets apply to all embedded SQL statements that
4009
appear below the point where the handler is set, unless a
4010
different action was set for the same condition between the first
4011
<literal>EXEC SQL WHENEVER</literal> and the SQL statement causing
4012
the condition, regardless of the flow of control in the C program.
4013
So neither of the two following C program excerpts will have the
4019
int main(int argc, char *argv[])
4023
EXEC SQL WHENEVER SQLWARNING SQLPRINT;
4026
EXEC SQL SELECT ...;
4035
int main(int argc, char *argv[])
4038
set_error_handler();
4040
EXEC SQL SELECT ...;
4044
static void set_error_handler(void)
4046
EXEC SQL WHENEVER SQLERROR STOP;
4053
<title>sqlca</title>
4056
For more powerful error handling, the embedded SQL interface
4057
provides a global variable with the name <varname>sqlca</varname>
4058
that has the following structure:
4068
char sqlerrmc[SQLERRMC_LEN];
4076
(In a multithreaded program, every thread automatically gets its
4077
own copy of <varname>sqlca</varname>. This works similarly to the
4078
handling of the standard C global variable
4079
<varname>errno</varname>.)
4083
<varname>sqlca</varname> covers both warnings and errors. If
4084
multiple warnings or errors occur during the execution of a
4085
statement, then <varname>sqlca</varname> will only contain
4086
information about the last one.
4090
If no error occurred in the last <acronym>SQL</acronym> statement,
4091
<literal>sqlca.sqlcode</literal> will be 0 and
4092
<literal>sqlca.sqlstate</literal> will be
4093
<literal>"00000"</literal>. If a warning or error occurred, then
4094
<literal>sqlca.sqlcode</literal> will be negative and
4095
<literal>sqlca.sqlstate</literal> will be different from
4096
<literal>"00000"</literal>. A positive
4097
<literal>sqlca.sqlcode</literal> indicates a harmless condition,
4098
such as that the last query returned zero rows.
4099
<literal>sqlcode</literal> and <literal>sqlstate</literal> are two
4100
different error code schemes; details appear below.
4104
If the last SQL statement was successful, then
4105
<literal>sqlca.sqlerrd[1]</literal> contains the OID of the
4106
processed row, if applicable, and
4107
<literal>sqlca.sqlerrd[2]</literal> contains the number of
4108
processed or returned rows, if applicable to the command.
4112
In case of an error or warning,
4113
<literal>sqlca.sqlerrm.sqlerrmc</literal> will contain a string
4114
that describes the error. The field
4115
<literal>sqlca.sqlerrm.sqlerrml</literal> contains the length of
4116
the error message that is stored in
4117
<literal>sqlca.sqlerrm.sqlerrmc</literal> (the result of
4118
<function>strlen()</function>, not really interesting for a C
4119
programmer). Note that some messages are too long to fit in the
4120
fixed-size <literal>sqlerrmc</literal> array; they will be truncated.
4124
In case of a warning, <literal>sqlca.sqlwarn[2]</literal> is set
4125
to <literal>W</literal>. (In all other cases, it is set to
4126
something different from <literal>W</literal>.) If
4127
<literal>sqlca.sqlwarn[1]</literal> is set to
4128
<literal>W</literal>, then a value was truncated when it was
4129
stored in a host variable. <literal>sqlca.sqlwarn[0]</literal> is
4130
set to <literal>W</literal> if any of the other elements are set
4131
to indicate a warning.
4135
The fields <structfield>sqlcaid</structfield>,
4136
<structfield>sqlcabc</structfield>,
4137
<structfield>sqlerrp</structfield>, and the remaining elements of
4138
<structfield>sqlerrd</structfield> and
4139
<structfield>sqlwarn</structfield> currently contain no useful
4144
The structure <varname>sqlca</varname> is not defined in the SQL
4145
standard, but is implemented in several other SQL database
4146
systems. The definitions are similar at the core, but if you want
4147
to write portable applications, then you should investigate the
4148
different implementations carefully.
4153
<title><literal>SQLSTATE</literal> vs <literal>SQLCODE</literal></title>
4156
The fields <literal>sqlca.sqlstate</literal> and
4157
<literal>sqlca.sqlcode</literal> are two different schemes that
4158
provide error codes. Both are derived from the SQL standard, but
4159
<literal>SQLCODE</literal> has been marked deprecated in the SQL-92
4160
edition of the standard and has been dropped in later editions.
4161
Therefore, new applications are strongly encouraged to use
4162
<literal>SQLSTATE</literal>.
4166
<literal>SQLSTATE</literal> is a five-character array. The five
4167
characters contain digits or upper-case letters that represent
4168
codes of various error and warning conditions.
4169
<literal>SQLSTATE</literal> has a hierarchical scheme: the first
4170
two characters indicate the general class of the condition, the
4171
last three characters indicate a subclass of the general
4172
condition. A successful state is indicated by the code
4173
<literal>00000</literal>. The <literal>SQLSTATE</literal> codes are for
4174
the most part defined in the SQL standard. The
4175
<productname>PostgreSQL</productname> server natively supports
4176
<literal>SQLSTATE</literal> error codes; therefore a high degree
4177
of consistency can be achieved by using this error code scheme
4178
throughout all applications. For further information see
4179
<xref linkend="errcodes-appendix">.
4183
<literal>SQLCODE</literal>, the deprecated error code scheme, is a
4184
simple integer. A value of 0 indicates success, a positive value
4185
indicates success with additional information, a negative value
4186
indicates an error. The SQL standard only defines the positive
4187
value +100, which indicates that the last command returned or
4188
affected zero rows, and no specific negative values. Therefore,
4189
this scheme can only achieve poor portability and does not have a
4190
hierarchical code assignment. Historically, the embedded SQL
4191
processor for <productname>PostgreSQL</productname> has assigned
4192
some specific <literal>SQLCODE</literal> values for its use, which
4193
are listed below with their numeric value and their symbolic name.
4194
Remember that these are not portable to other SQL implementations.
4195
To simplify the porting of applications to the
4196
<literal>SQLSTATE</literal> scheme, the corresponding
4197
<literal>SQLSTATE</literal> is also listed. There is, however, no
4198
one-to-one or one-to-many mapping between the two schemes (indeed
4199
it is many-to-many), so you should consult the global
4200
<literal>SQLSTATE</literal> listing in <xref linkend="errcodes-appendix">
4205
These are the assigned <literal>SQLCODE</literal> values:
4209
<term>-12 (<symbol>ECPG_OUT_OF_MEMORY</symbol>)</term>
4212
Indicates that your virtual memory is exhausted. (SQLSTATE
4219
<term>-200 (<symbol>ECPG_UNSUPPORTED</symbol>)</term>
4222
Indicates the preprocessor has generated something that the
4223
library does not know about. Perhaps you are running
4224
incompatible versions of the preprocessor and the
4225
library. (SQLSTATE YE002)
4231
<term>-201 (<symbol>ECPG_TOO_MANY_ARGUMENTS</symbol>)</term>
4234
This means that the command specified more host variables than
4235
the command expected. (SQLSTATE 07001 or 07002)
4241
<term>-202 (<symbol>ECPG_TOO_FEW_ARGUMENTS</symbol>)</term>
4244
This means that the command specified fewer host variables than
4245
the command expected. (SQLSTATE 07001 or 07002)
4251
<term>-203 (<symbol>ECPG_TOO_MANY_MATCHES</symbol>)</term>
4254
This means a query has returned multiple rows but the statement
4255
was only prepared to store one result row (for example, because
4256
the specified variables are not arrays). (SQLSTATE 21000)
4262
<term>-204 (<symbol>ECPG_INT_FORMAT</symbol>)</term>
4265
The host variable is of type <type>int</type> and the datum in
4266
the database is of a different type and contains a value that
4267
cannot be interpreted as an <type>int</type>. The library uses
4268
<function>strtol()</function> for this conversion. (SQLSTATE
4275
<term>-205 (<symbol>ECPG_UINT_FORMAT</symbol>)</term>
4278
The host variable is of type <type>unsigned int</type> and the
4279
datum in the database is of a different type and contains a
4280
value that cannot be interpreted as an <type>unsigned
4281
int</type>. The library uses <function>strtoul()</function>
4282
for this conversion. (SQLSTATE 42804)
4288
<term>-206 (<symbol>ECPG_FLOAT_FORMAT</symbol>)</term>
4291
The host variable is of type <type>float</type> and the datum
4292
in the database is of another type and contains a value that
4293
cannot be interpreted as a <type>float</type>. The library
4294
uses <function>strtod()</function> for this conversion.
4301
<term>-207 (<symbol>ECPG_CONVERT_BOOL</symbol>)</term>
4304
This means the host variable is of type <type>bool</type> and
4305
the datum in the database is neither <literal>'t'</> nor
4306
<literal>'f'</>. (SQLSTATE 42804)
4312
<term>-208 (<symbol>ECPG_EMPTY</symbol>)</term>
4315
The statement sent to the <productname>PostgreSQL</productname>
4316
server was empty. (This cannot normally happen in an embedded
4317
SQL program, so it might point to an internal error.) (SQLSTATE
4324
<term>-209 (<symbol>ECPG_MISSING_INDICATOR</symbol>)</term>
4327
A null value was returned and no null indicator variable was
4328
supplied. (SQLSTATE 22002)
4334
<term>-210 (<symbol>ECPG_NO_ARRAY</symbol>)</term>
4337
An ordinary variable was used in a place that requires an
4338
array. (SQLSTATE 42804)
4344
<term>-211 (<symbol>ECPG_DATA_NOT_ARRAY</symbol>)</term>
4347
The database returned an ordinary variable in a place that
4348
requires array value. (SQLSTATE 42804)
4354
<term>-220 (<symbol>ECPG_NO_CONN</symbol>)</term>
4357
The program tried to access a connection that does not exist.
4364
<term>-221 (<symbol>ECPG_NOT_CONN</symbol>)</term>
4367
The program tried to access a connection that does exist but is
4368
not open. (This is an internal error.) (SQLSTATE YE002)
4374
<term>-230 (<symbol>ECPG_INVALID_STMT</symbol>)</term>
4377
The statement you are trying to use has not been prepared.
4384
<term>-240 (<symbol>ECPG_UNKNOWN_DESCRIPTOR</symbol>)</term>
4387
The descriptor specified was not found. The statement you are
4388
trying to use has not been prepared. (SQLSTATE 33000)
4394
<term>-241 (<symbol>ECPG_INVALID_DESCRIPTOR_INDEX</symbol>)</term>
4397
The descriptor index specified was out of range. (SQLSTATE
4404
<term>-242 (<symbol>ECPG_UNKNOWN_DESCRIPTOR_ITEM</symbol>)</term>
4407
An invalid descriptor item was requested. (This is an internal
4408
error.) (SQLSTATE YE002)
4414
<term>-243 (<symbol>ECPG_VAR_NOT_NUMERIC</symbol>)</term>
4417
During the execution of a dynamic statement, the database
4418
returned a numeric value and the host variable was not numeric.
4425
<term>-244 (<symbol>ECPG_VAR_NOT_CHAR</symbol>)</term>
4428
During the execution of a dynamic statement, the database
4429
returned a non-numeric value and the host variable was numeric.
4436
<term>-400 (<symbol>ECPG_PGSQL</symbol>)</term>
4439
Some error caused by the <productname>PostgreSQL</productname>
4440
server. The message contains the error message from the
4441
<productname>PostgreSQL</productname> server.
4447
<term>-401 (<symbol>ECPG_TRANS</symbol>)</term>
4450
The <productname>PostgreSQL</productname> server signaled that
4451
we cannot start, commit, or rollback the transaction.
4458
<term>-402 (<symbol>ECPG_CONNECT</symbol>)</term>
4461
The connection attempt to the database did not succeed.
4468
<term>100 (<symbol>ECPG_NOT_FOUND</symbol>)</term>
4471
This is a harmless condition indicating that the last command
4472
retrieved or processed zero rows, or that you are at the end of
4473
the cursor. (SQLSTATE 02000)
4482
<sect1 id="ecpg-preproc">
4483
<title>Preprocessor directives</title>
4486
<title>Including files</title>
4489
To include an external file into your embedded SQL program, use:
4491
EXEC SQL INCLUDE <replaceable>filename</replaceable>;
4493
The embedded SQL preprocessor will look for a file named
4494
<literal><replaceable>filename</replaceable>.h</literal>,
4495
preprocess it, and include it in the resulting C output. Thus,
4496
embedded SQL statements in the included file are handled correctly.
4500
Note that this is <emphasis>not</emphasis> the same as:
4502
#include <<replaceable>filename</replaceable>.h>
4504
because this file would not be subject to SQL command preprocessing.
4505
Naturally, you can continue to use the C
4506
<literal>#include</literal> directive to include other header
4512
The include file name is case-sensitive, even though the rest of
4513
the <literal>EXEC SQL INCLUDE</literal> command follows the normal
4514
SQL case-sensitivity rules.
4520
<title>The #define and #undef directives</title>
4522
Similar to the directive <literal>#define</literal> that is known from C,
4523
embedded SQL has a similar concept:
4525
EXEC SQL DEFINE <replaceable>name</>;
4526
EXEC SQL DEFINE <replaceable>name</> <replaceable>value</>;
4528
So you can define a name:
4530
EXEC SQL DEFINE HAVE_FEATURE;
4532
And you can also define constants:
4534
EXEC SQL DEFINE MYNUMBER 12;
4535
EXEC SQL DEFINE MYSTRING 'abc';
4537
Use <literal>undef</> to remove a previous definition:
4539
EXEC SQL UNDEF MYNUMBER;
4544
Of course you can continue to use the C versions <literal>#define</literal>
4545
and <literal>#undef</literal> in your embedded SQL program. The difference
4546
is where your defined values get evaluated. If you use <literal>EXEC SQL
4547
DEFINE</> then the ecpg preprocessor evaluates the defines and substitutes
4548
the values. For example if you write:
4550
EXEC SQL DEFINE MYNUMBER 12;
4552
EXEC SQL UPDATE Tbl SET col = MYNUMBER;
4554
then ecpg will already do the substitution and your C compiler will never
4555
see any name or identifier <literal>MYNUMBER</>. Note that you cannot use
4556
<literal>#define</literal> for a constant that you are going to use in an
4557
embedded SQL query because in this case the embedded SQL precompiler is not
4558
able to see this declaration.
4563
<title>ifdef, ifndef, else, elif and endif directives</title>
4565
You can use the following directives to compile code sections conditionally:
4569
<term><literal>EXEC SQL ifdef <replaceable>name</>;</literal></term>
4572
Checks a <replaceable>name</> and processes subsequent lines if
4573
<replaceable>name</> has been created with <literal>EXEC SQL define
4574
<replaceable>name</></literal>.
4580
<term><literal>EXEC SQL ifndef <replaceable>name</>;</literal></term>
4583
Checks a <replaceable>name</> and processes subsequent lines if
4584
<replaceable>name</> has <emphasis>not</emphasis> been created with
4585
<literal>EXEC SQL define <replaceable>name</></literal>.
4591
<term><literal>EXEC SQL else;</literal></term>
4594
Starts processing an alternative section to a section introduced by
4595
either <literal>EXEC SQL ifdef <replaceable>name</></literal> or
4596
<literal>EXEC SQL ifndef <replaceable>name</></literal>.
4602
<term><literal>EXEC SQL elif <replaceable>name</>;</literal></term>
4605
Checks <replaceable>name</> and starts an alternative section if
4606
<replaceable>name</> has been created with <literal>EXEC SQL define
4607
<replaceable>name</></literal>.
4613
<term><literal>EXEC SQL endif;</literal></term>
4616
Ends an alternative section.
4626
exec sql ifndef TZVAR;
4627
exec sql SET TIMEZONE TO 'GMT';
4628
exec sql elif TZNAME;
4629
exec sql SET TIMEZONE TO TZNAME;
4631
exec sql SET TIMEZONE TO TZVAR;
4639
<sect1 id="ecpg-process">
4640
<title>Processing Embedded SQL Programs</title>
4643
Now that you have an idea how to form embedded SQL C programs, you
4644
probably want to know how to compile them. Before compiling you
4645
run the file through the embedded <acronym>SQL</acronym>
4646
<acronym>C</acronym> preprocessor, which converts the
4647
<acronym>SQL</acronym> statements you used to special function
4648
calls. After compiling, you must link with a special library that
4649
contains the needed functions. These functions fetch information
4650
from the arguments, perform the <acronym>SQL</acronym> command using
4651
the <application>libpq</application> interface, and put the result
4652
in the arguments specified for output.
4656
The preprocessor program is called <filename>ecpg</filename> and is
4657
included in a normal <productname>PostgreSQL</> installation.
4658
Embedded SQL programs are typically named with an extension
4659
<filename>.pgc</filename>. If you have a program file called
4660
<filename>prog1.pgc</filename>, you can preprocess it by simply
4665
This will create a file called <filename>prog1.c</filename>. If
4666
your input files do not follow the suggested naming pattern, you
4667
can specify the output file explicitly using the
4668
<option>-o</option> option.
4672
The preprocessed file can be compiled normally, for example:
4676
The generated C source files include header files from the
4677
<productname>PostgreSQL</> installation, so if you installed
4678
<productname>PostgreSQL</> in a location that is not searched by
4679
default, you have to add an option such as
4680
<literal>-I/usr/local/pgsql/include</literal> to the compilation
4685
To link an embedded SQL program, you need to include the
4686
<filename>libecpg</filename> library, like so:
4688
cc -o myprog prog1.o prog2.o ... -lecpg
4690
Again, you might have to add an option like
4691
<literal>-L/usr/local/pgsql/lib</literal> to that command line.
4695
If you manage the build process of a larger project using
4696
<application>make</application>, it might be convenient to include
4697
the following implicit rule to your makefiles:
4707
The complete syntax of the <command>ecpg</command> command is
4708
detailed in <xref linkend="app-ecpg">.
4712
The <application>ecpg</application> library is thread-safe if it is built
4713
using the <option>--enable-thread-safety</> command-line option to
4714
<filename>configure</filename>. (You might need to use other threading
4715
command-line options to compile your client code.)
4719
<sect1 id="ecpg-library">
4720
<title>Library Functions</title>
4723
The <filename>libecpg</filename> library primarily contains
4724
<quote>hidden</quote> functions that are used to implement the
4725
functionality expressed by the embedded SQL commands. But there
4726
are some functions that can usefully be called directly. Note that
4727
this makes your code unportable.
4733
<function>ECPGdebug(int <replaceable>on</replaceable>, FILE
4734
*<replaceable>stream</replaceable>)</function> turns on debug
4735
logging if called with the first argument non-zero. Debug logging
4736
is done on <replaceable>stream</replaceable>. The log contains
4737
all <acronym>SQL</acronym> statements with all the input
4738
variables inserted, and the results from the
4739
<productname>PostgreSQL</productname> server. This can be very
4740
useful when searching for errors in your <acronym>SQL</acronym>
4745
On Windows, if the <application>ecpg</> libraries and an application are
4746
compiled with different flags, this function call will crash the
4747
application because the internal representation of the
4748
<literal>FILE</> pointers differ. Specifically,
4749
multithreaded/single-threaded, release/debug, and static/dynamic
4750
flags should be the same for the library and all applications using
4758
<function>ECPGstatus(int <replaceable>lineno</replaceable>,
4759
const char* <replaceable>connection_name</replaceable>)</function>
4760
returns true if you are connected to a database and false if not.
4761
<replaceable>connection_name</replaceable> can be <literal>NULL</>
4762
if a single connection is being used.
4768
<sect1 id="ecpg-develop">
4769
<title>Internals</title>
4772
This section explains how <application>ECPG</application> works
4773
internally. This information can occasionally be useful to help
4774
users understand how to use <application>ECPG</application>.
4778
The first four lines written by <command>ecpg</command> to the
4779
output are fixed lines. Two are comments and two are include
4780
lines necessary to interface to the library. Then the
4781
preprocessor reads through the file and writes output. Normally
4782
it just echoes everything to the output.
4786
When it sees an <command>EXEC SQL</command> statement, it
4787
intervenes and changes it. The command starts with <command>EXEC
4788
SQL</command> and ends with <command>;</command>. Everything in
4789
between is treated as an <acronym>SQL</acronym> statement and
4790
parsed for variable substitution.
4794
Variable substitution occurs when a symbol starts with a colon
4795
(<literal>:</literal>). The variable with that name is looked up
4796
among the variables that were previously declared within a
4797
<literal>EXEC SQL DECLARE</> section.
4801
The most important function in the library is
4802
<function>ECPGdo</function>, which takes care of executing most
4803
commands. It takes a variable number of arguments. This can easily
4804
add up to 50 or so arguments, and we hope this will not be a
4805
problem on any platform.
4813
<term>A line number</term>
4816
This is the line number of the original line; used in error
4823
<term>A string</term>
4826
This is the <acronym>SQL</acronym> command that is to be issued.
4827
It is modified by the input variables, i.e., the variables that
4828
where not known at compile time but are to be entered in the
4829
command. Where the variables should go the string contains
4830
<literal>?</literal>.
4836
<term>Input variables</term>
4839
Every input variable causes ten arguments to be created. (See below.)
4845
<term><parameter>ECPGt_EOIT</></term>
4848
An <type>enum</> telling that there are no more input
4855
<term>Output variables</term>
4858
Every output variable causes ten arguments to be created.
4859
(See below.) These variables are filled by the function.
4865
<term><parameter>ECPGt_EORT</></term>
4868
An <type>enum</> telling that there are no more variables.
4876
For every variable that is part of the <acronym>SQL</acronym>
4877
command, the function gets ten arguments:
4882
The type as a special symbol.
4888
A pointer to the value or a pointer to the pointer.
4894
The size of the variable if it is a <type>char</type> or <type>varchar</type>.
4900
The number of elements in the array (for array fetches).
4906
The offset to the next element in the array (for array fetches).
4912
The type of the indicator variable as a special symbol.
4918
A pointer to the indicator variable.
4930
The number of elements in the indicator array (for array fetches).
4936
The offset to the next element in the indicator array (for
4944
Note that not all SQL commands are treated in this way. For
4945
instance, an open cursor statement like:
4947
EXEC SQL OPEN <replaceable>cursor</replaceable>;
4949
is not copied to the output. Instead, the cursor's
4950
<command>DECLARE</> command is used at the position of the <command>OPEN</> command
4951
because it indeed opens the cursor.
4955
Here is a complete example describing the output of the
4956
preprocessor of a file <filename>foo.pgc</filename> (details might
4957
change with each particular version of the preprocessor):
4959
EXEC SQL BEGIN DECLARE SECTION;
4962
EXEC SQL END DECLARE SECTION;
4964
EXEC SQL SELECT res INTO :result FROM mytable WHERE index = :index;
4967
<programlisting><
added
removed
doc/src/sgml/ecpg.sgml
