| blob | 3d005f0f4706faaf707cb25062a179fc93904251 |
1 <!-- $PostgreSQL$ -->
10 <para>
17 </para>
19 <para>
20 This documentation is quite incomplete. But since this
21 interface is standardized, additional information can be found in
22 many resources about SQL.
23 </para>
28 <para>
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
34 compiler.
35 </para>
37 <para>
40 takes care of the tedious passing of information to and from
42 code in the program is checked at build time for syntactical
47 standard as much as possible, and it is usually possible to port
50 ease.
51 </para>
53 <para>
54 As already stated, programs written for the embedded
56 code inserted to perform database-related actions. This special
57 code always has the form:
58 <programlisting>
59 EXEC SQL ...;
60 </programlisting>
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
66 </para>
68 <para>
69 The following sections explain all the embedded SQL statements.
70 </para>
71 </sect1>
76 <para>
77 One connects to a database using the following statement:
78 <programlisting>
79 EXEC SQL CONNECT TO <replaceable>target</replaceable> <optional>AS <replaceable>connection-name</replaceable></optional> <optional>USER <replaceable>user-name</replaceable></optional>;
80 </programlisting>
82 following ways:
84 <itemizedlist>
85 <listitem>
86 <simpara>
87 <literal><replaceable>dbname</><optional>@<replaceable>hostname</></optional><optional>:<replaceable>port</></optional></literal>
88 </simpara>
89 </listitem>
91 <listitem>
92 <simpara>
93 <literal>tcp:postgresql://<replaceable>hostname</><optional>:<replaceable>port</></optional><optional>/<replaceable>dbname</></optional><optional>?<replaceable>options</></optional></literal>
94 </simpara>
95 </listitem>
97 <listitem>
98 <simpara>
99 <literal>unix:postgresql://<replaceable>hostname</><optional>:<replaceable>port</></optional><optional>/<replaceable>dbname</></optional><optional>?<replaceable>options</></optional></literal>
100 </simpara>
101 </listitem>
103 <listitem>
104 <simpara>
105 an SQL string literal containing one of the above forms
106 </simpara>
107 </listitem>
109 <listitem>
110 <simpara>
111 a reference to a character variable containing one of the above forms (see examples)
112 </simpara>
113 </listitem>
115 <listitem>
116 <simpara>
118 </simpara>
119 </listitem>
120 </itemizedlist>
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
129 to the default database under the default user name. No separate
130 user name or connection name can be specified in that case.
131 </para>
133 <para>
134 There are also different ways to specify the user name:
136 <itemizedlist>
137 <listitem>
138 <simpara>
140 </simpara>
141 </listitem>
143 <listitem>
144 <simpara>
146 </simpara>
147 </listitem>
149 <listitem>
150 <simpara>
151 <literal><replaceable>username</replaceable> IDENTIFIED BY <replaceable>password</replaceable></literal>
152 </simpara>
153 </listitem>
155 <listitem>
156 <simpara>
157 <literal><replaceable>username</replaceable> USING <replaceable>password</replaceable></literal>
158 </simpara>
159 </listitem>
160 </itemizedlist>
164 SQL string literal, or a reference to a character variable.
165 </para>
167 <para>
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
173 chapter).
174 </para>
176 <para>
178 <programlisting>
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;
187 ...
188 EXEC SQL CONNECT TO :target USER :user;
189 </programlisting>
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
193 colon.
194 </para>
196 <para>
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
201 somewhere.
202 </para>
203 </sect1>
208 <para>
209 To close a connection, use the following statement:
210 <programlisting>
212 </programlisting>
214 in the following ways:
216 <itemizedlist>
217 <listitem>
218 <simpara>
220 </simpara>
221 </listitem>
223 <listitem>
224 <simpara>
226 </simpara>
227 </listitem>
229 <listitem>
230 <simpara>
232 </simpara>
233 </listitem>
235 <listitem>
236 <simpara>
238 </simpara>
239 </listitem>
240 </itemizedlist>
242 If no connection name is specified, the current connection is
243 closed.
244 </para>
246 <para>
247 It is good style that an application always explicitly disconnect
248 from every connection it opened.
249 </para>
250 </sect1>
255 <para>
256 Any SQL command can be run from within an embedded SQL application.
257 Below are some examples of how to do that.
258 </para>
260 <para>
261 Creating a table:
262 <programlisting>
263 EXEC SQL CREATE TABLE foo (number integer, ascii char(16));
264 EXEC SQL CREATE UNIQUE INDEX num1 ON foo(number);
265 EXEC SQL COMMIT;
266 </programlisting>
267 </para>
269 <para>
270 Inserting rows:
271 <programlisting>
272 EXEC SQL INSERT INTO foo (number, ascii) VALUES (9999, 'doodad');
273 EXEC SQL COMMIT;
274 </programlisting>
275 </para>
277 <para>
278 Deleting rows:
279 <programlisting>
280 EXEC SQL DELETE FROM foo WHERE number = 9999;
281 EXEC SQL COMMIT;
282 </programlisting>
283 </para>
285 <para>
286 Single-row select:
287 <programlisting>
288 EXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE ascii = 'doodad';
289 </programlisting>
290 </para>
292 <para>
293 Select using cursors:
294 <programlisting>
295 EXEC SQL DECLARE foo_bar CURSOR FOR
296 SELECT number, ascii FROM foo
297 ORDER BY ascii;
298 EXEC SQL OPEN foo_bar;
299 EXEC SQL FETCH foo_bar INTO :FooBar, DooDad;
300 ...
301 EXEC SQL CLOSE foo_bar;
302 EXEC SQL COMMIT;
303 </programlisting>
304 </para>
306 <para>
307 Updates:
308 <programlisting>
309 EXEC SQL UPDATE foo
310 SET ascii = 'foobar'
311 WHERE number = 9999;
312 EXEC SQL COMMIT;
313 </programlisting>
314 </para>
316 <para>
317 The tokens of the form
320 variables in the C program. They are explained in <xref
322 </para>
324 <para>
325 In the default mode, statements are committed only when
327 interface also supports autocommit of transactions (similar to
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
334 </para>
335 </sect1>
340 <para>
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.
345 </para>
347 <para>
348 The first option is to explicitly choose a connection for each SQL
349 statement, for example:
350 <programlisting>
352 </programlisting>
353 This option is particularly suitable if the application needs to
354 use several connections in mixed order.
355 </para>
357 <para>
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.
362 </para>
364 <para>
365 The second option is to execute a statement to switch the current
366 connection. That statement is:
367 <programlisting>
369 </programlisting>
370 This option is particularly convenient if many statements are to be
371 executed on the same connection. It is not thread-aware.
372 </para>
373 </sect1>
378 <para>
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
390 the variables of the C program are called <firstterm>host
391 variables</firstterm>.
392 </para>
394 <sect2>
397 <para>
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:
404 <programlisting>
405 EXEC SQL INSERT INTO sometable VALUES (:v1, 'foo', :v2);
406 </programlisting>
407 This statements refers to two C variables named
409 regular SQL string literal, to illustrate that you are not
410 restricted to use one kind of data or the other.
411 </para>
413 <para>
414 This style of inserting C variables in SQL statements works
415 anywhere a value expression is expected in an SQL statement.
416 </para>
417 </sect2>
419 <sect2>
422 <para>
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.
428 </para>
430 <para>
431 This section starts with:
432 <programlisting>
433 EXEC SQL BEGIN DECLARE SECTION;
434 </programlisting>
435 and ends with:
436 <programlisting>
437 EXEC SQL END DECLARE SECTION;
438 </programlisting>
439 Between those lines, there must be normal C variable declarations,
440 such as:
441 <programlisting>
442 int x = 4;
444 </programlisting>
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:
450 <programlisting>
451 EXEC SQL int i = 4;
452 </programlisting>
453 You can have as many declare sections in a program as you like.
454 </para>
456 <para>
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.
461 </para>
463 <para>
464 The definition of a structure or union also must be listed inside
466 handle these types since it does not know the definition.
467 </para>
468 </sect2>
470 <sect2>
472 <para>
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
475 only in ECPG.
476 </para>
478 <para>
479 A few examples on host variables:
480 <variablelist>
481 <varlistentry>
483 <listitem>
484 <para>
485 One of the most common uses of an array declaration is probably the
486 allocation of a char array as in:
487 <programlisting>
488 EXEC SQL BEGIN DECLARE SECTION;
489 char str[50];
490 EXEC SQL END DECLARE SECTION;
491 </programlisting>
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.
495 </para>
496 </listitem>
497 </varlistentry>
499 <varlistentry>
501 <listitem>
502 <para>
504 existing types.
505 <programlisting>
506 EXEC SQL BEGIN DECLARE SECTION;
507 typedef char mychartype[40];
508 typedef long serial_t;
509 EXEC SQL END DECLARE SECTION;
510 </programlisting>
511 Note that you could also use:
512 <programlisting>
513 EXEC SQL TYPE serial_t IS long;
514 </programlisting>
515 This declaration does not need to be part of a declare section.
516 </para>
517 </listitem>
518 </varlistentry>
520 <varlistentry>
522 <listitem>
523 <para>
524 You can declare pointers to the most common types. Note however that
525 you cannot use pointers as target variables of queries without
527 information on auto-allocation.
528 </para>
529 <programlisting>
530 EXEC SQL BEGIN DECLARE SECTION;
531 int *intp;
532 char **charp;
533 EXEC SQL END DECLARE SECTION;
534 </programlisting>
535 </listitem>
536 </varlistentry>
538 <varlistentry>
540 <listitem>
541 <para>
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.
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.
548 </para>
549 <para>
552 declaration like:
553 <programlisting>
554 VARCHAR var[180];
555 </programlisting>
556 is converted into:
557 <programlisting>
558 struct varchar_var { int len; char arr[180]; } var;
559 </programlisting>
560 This structure is suitable for interfacing with SQL datums of type
562 </para>
563 </listitem>
564 </varlistentry>
565 </variablelist>
566 </para>
567 </sect2>
569 <sect2>
572 <para>
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
579 the retrieved values are to be stored in.
580 </para>
582 <para>
583 Here is an example:
584 <programlisting>
585 /*
586 * assume this table:
587 * CREATE TABLE test1 (a int, b varchar(50));
588 */
590 EXEC SQL BEGIN DECLARE SECTION;
591 int v1;
592 VARCHAR v2;
593 EXEC SQL END DECLARE SECTION;
595 ...
597 EXEC SQL SELECT a, b INTO :v1, :v2 FROM test;
598 </programlisting>
601 elements in the select list and the list after
603 equal.
604 </para>
606 <para>
608 <programlisting>
609 EXEC SQL BEGIN DECLARE SECTION;
610 int v1;
611 VARCHAR v2;
612 EXEC SQL END DECLARE SECTION;
614 ...
616 EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test;
618 ...
620 do {
621 ...
622 EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2;
623 ...
624 } while (...);
625 </programlisting>
627 normal clauses.
628 </para>
630 <para>
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.
634 </para>
635 </sect2>
637 <sect2>
640 <para>
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
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:
651 <programlisting>
652 EXEC SQL BEGIN DECLARE SECTION;
653 VARCHAR val;
654 int val_ind;
655 EXEC SQL END DECLARE SECTION:
657 ...
659 EXEC SQL SELECT b INTO :val :val_ind FROM test1;
660 </programlisting>
662 the value was not null, and it will be negative if the value was
663 null.
664 </para>
666 <para>
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.
670 </para>
671 </sect2>
672 </sect1>
677 <para>
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.
685 </para>
687 <para>
688 The simplest way to execute an arbitrary SQL statement is to use
690 <programlisting>
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;
696 </programlisting>
697 You cannot execute statements that retrieve data (e.g.,
699 </para>
701 <para>
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
708 example:
709 <programlisting>
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;
715 ...
716 EXEC SQL EXECUTE mystmt USING 42, 'foobar';
717 </programlisting>
718 If the statement you are executing returns values, then add an
720 <programlisting><![CDATA[
721 EXEC SQL BEGIN DECLARE SECTION;
722 const char *stmt = "SELECT a, b, c FROM test1 WHERE a > ?";
723 int v1, v2;
724 VARCHAR v3;
725 EXEC SQL END DECLARE SECTION;
727 EXEC SQL PREPARE mystmt FROM :stmt;
728 ...
729 EXEC SQL EXECUTE mystmt INTO v1, v2, v3 USING 37;
730 ]]>
731 </programlisting>
734 both, or neither.
735 </para>
737 <para>
738 When you don't need the prepared statement anymore, you should
739 deallocate it:
740 <programlisting>
742 </programlisting>
743 </para>
744 </sect1>
750 <para>
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
755 following example:
756 <programlisting><![CDATA[
757 EXEC SQL BEGIN DECLARE SECTION;
758 date date1;
759 timestamp ts1, tsout;
760 interval iv1;
761 char *out;
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);
769 free(out);
770 ]]>
771 </programlisting>
772 </para>
774 <sect2>
776 <para>
777 The numeric type offers to do calculations with arbitrary precision. See
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
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.
785 </para>
786 <para>
787 The following functions can be used to work with the numeric type:
788 <variablelist>
789 <varlistentry>
791 <listitem>
792 <para>
793 Request a pointer to a newly allocated numeric variable.
794 <synopsis>
795 numeric *PGTYPESnumeric_new(void);
796 </synopsis>
797 </para>
798 </listitem>
799 </varlistentry>
801 <varlistentry>
803 <listitem>
804 <para>
805 Free a numeric type, release all of its memory.
806 <synopsis>
807 void PGTYPESnumeric_free(numeric *var);
808 </synopsis>
809 </para>
810 </listitem>
811 </varlistentry>
813 <varlistentry>
815 <listitem>
816 <para>
817 Parse a numeric type from its string notation.
818 <synopsis>
819 numeric *PGTYPESnumeric_from_asc(char *str, char **endptr);
820 </synopsis>
821 Valid formats are for example:
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
832 </para>
833 </listitem>
834 </varlistentry>
836 <varlistentry>
838 <listitem>
839 <para>
842 <synopsis>
843 char *PGTYPESnumeric_to_asc(numeric *num, int dscale);
844 </synopsis>
846 digits, with rounding applied if necessary.
847 </para>
848 </listitem>
849 </varlistentry>
851 <varlistentry>
853 <listitem>
854 <para>
855 Add two numeric variables into a third one.
856 <synopsis>
857 int PGTYPESnumeric_add(numeric *var1, numeric *var2, numeric *result);
858 </synopsis>
863 </para>
864 </listitem>
865 </varlistentry>
867 <varlistentry>
869 <listitem>
870 <para>
871 Subtract two numeric variables and return the result in a third one.
872 <synopsis>
873 int PGTYPESnumeric_sub(numeric *var1, numeric *var2, numeric *result);
874 </synopsis>
879 </para>
880 </listitem>
881 </varlistentry>
883 <varlistentry>
885 <listitem>
886 <para>
887 Multiply two numeric variables and return the result in a third one.
888 <synopsis>
889 int PGTYPESnumeric_mul(numeric *var1, numeric *var2, numeric *result);
890 </synopsis>
895 </para>
896 </listitem>
897 </varlistentry>
899 <varlistentry>
901 <listitem>
902 <para>
903 Divide two numeric variables and return the result in a third one.
904 <synopsis>
905 int PGTYPESnumeric_div(numeric *var1, numeric *var2, numeric *result);
906 </synopsis>
911 </para>
912 </listitem>
913 </varlistentry>
915 <varlistentry>
917 <listitem>
918 <para>
919 Compare two numeric variables.
920 <synopsis>
921 int PGTYPESnumeric_cmp(numeric *var1, numeric *var2)
922 </synopsis>
923 This function compares two numeric variables. In case of error,
925 returns one of three possible results:
926 <itemizedlist>
927 <listitem>
928 <para>
930 </para>
931 </listitem>
932 <listitem>
933 <para>
935 </para>
936 </listitem>
937 <listitem>
938 <para>
940 </para>
941 </listitem>
942 </itemizedlist>
943 </para>
944 </listitem>
945 </varlistentry>
947 <varlistentry>
949 <listitem>
950 <para>
951 Convert an int variable to a numeric variable.
952 <synopsis>
953 int PGTYPESnumeric_from_int(signed int int_val, numeric *var);
954 </synopsis>
955 This function accepts a variable of type signed int and stores it
957 -1 in case of a failure.
958 </para>
959 </listitem>
960 </varlistentry>
962 <varlistentry>
964 <listitem>
965 <para>
966 Convert a long int variable to a numeric variable.
967 <synopsis>
968 int PGTYPESnumeric_from_long(signed long int long_val, numeric *var);
969 </synopsis>
970 This function accepts a variable of type signed long int and stores it
972 -1 in case of a failure.
973 </para>
974 </listitem>
975 </varlistentry>
977 <varlistentry>
979 <listitem>
980 <para>
981 Copy over one numeric variable into another one.
982 <synopsis>
983 int PGTYPESnumeric_copy(numeric *src, numeric *dst);
984 </synopsis>
985 This function copies over the value of the variable that
988 </para>
989 </listitem>
990 </varlistentry>
992 <varlistentry>
994 <listitem>
995 <para>
996 Convert a variable of type double to a numeric.
997 <synopsis>
998 int PGTYPESnumeric_from_double(double d, numeric *dst);
999 </synopsis>
1000 This function accepts a variable of type double and stores the result
1002 and -1 if an error occurs.
1003 </para>
1004 </listitem>
1005 </varlistentry>
1007 <varlistentry>
1009 <listitem>
1010 <para>
1011 Convert a variable of type numeric to double.
1012 <synopsis>
1013 int PGTYPESnumeric_to_double(numeric *nv, double *dp)
1014 </synopsis>
1015 The function converts the numeric value from the variable that
1020 </para>
1021 </listitem>
1022 </varlistentry>
1024 <varlistentry>
1026 <listitem>
1027 <para>
1028 Convert a variable of type numeric to int.
1029 <synopsis>
1030 int PGTYPESnumeric_to_int(numeric *nv, int *ip);
1031 </synopsis>
1032 The function converts the numeric value from the variable that
1037 </para>
1038 </listitem>
1039 </varlistentry>
1041 <varlistentry>
1043 <listitem>
1044 <para>
1045 Convert a variable of type numeric to long.
1046 <synopsis>
1047 int PGTYPESnumeric_to_long(numeric *nv, long *lp);
1048 </synopsis>
1049 The function converts the numeric value from the variable that
1052 occurs, including overflow. On overflow, the global variable
1054 additionally.
1055 </para>
1056 </listitem>
1057 </varlistentry>
1059 <varlistentry>
1061 <listitem>
1062 <para>
1063 Convert a variable of type numeric to decimal.
1064 <synopsis>
1065 int PGTYPESnumeric_to_decimal(numeric *src, decimal *dst);
1066 </synopsis>
1067 The function converts the numeric value from the variable that
1070 occurs, including overflow. On overflow, the global variable
1072 additionally.
1073 </para>
1074 </listitem>
1075 </varlistentry>
1077 <varlistentry>
1079 <listitem>
1080 <para>
1081 Convert a variable of type decimal to numeric.
1082 <synopsis>
1083 int PGTYPESnumeric_from_decimal(decimal *src, numeric *dst);
1084 </synopsis>
1085 The function converts the decimal value from the variable that
1088 occurs. Since the decimal type is implemented as a limited version of
1089 the numeric type, overflow cannot occur with this conversion.
1090 </para>
1091 </listitem>
1092 </varlistentry>
1093 </variablelist>
1094 </para>
1095 </sect2>
1097 <sect2>
1099 <para>
1100 The date type in C enables your programs to deal with data of the SQL type
1103 </para>
1104 <para>
1105 The following functions can be used to work with the date type:
1106 <variablelist>
1109 <listitem>
1110 <para>
1111 Extract the date part from a timestamp.
1112 <synopsis>
1113 date PGTYPESdate_from_timestamp(timestamp dt);
1114 </synopsis>
1115 The function receives a timestamp as its only argument and returns the
1116 extracted date part from this timestamp.
1117 </para>
1118 </listitem>
1119 </varlistentry>
1123 <listitem>
1124 <para>
1125 Parse a date from its textual representation.
1126 <synopsis>
1127 date PGTYPESdate_from_asc(char *str, char **endptr);
1128 </synopsis>
1131 the complete string and so it currently does not support to store the
1134 </para>
1135 <para>
1136 Note that the function always assumes MDY-formatted dates and there is
1137 currently no variable to change that within ecpg.
1138 </para>
1139 <para>
1141 </para>
1145 <thead>
1146 <row>
1149 </row>
1150 </thead>
1151 <tbody>
1152 <row>
1155 </row>
1156 <row>
1159 </row>
1160 <row>
1163 </row>
1164 <row>
1167 </row>
1168 <row>
1171 </row>
1172 <row>
1175 </row>
1176 <row>
1179 </row>
1180 <row>
1183 </row>
1184 <row>
1187 </row>
1188 <row>
1191 </row>
1192 <row>
1195 </row>
1196 <row>
1199 </row>
1200 <row>
1203 </row>
1204 <row>
1207 </row>
1208 <row>
1211 </row>
1212 <row>
1215 </row>
1216 <row>
1219 </row>
1220 </tbody>
1221 </tgroup>
1222 </table>
1223 </listitem>
1224 </varlistentry>
1228 <listitem>
1229 <para>
1230 Return the textual representation of a date variable.
1231 <synopsis>
1232 char *PGTYPESdate_to_asc(date dDate);
1233 </synopsis>
1237 </para>
1238 </listitem>
1239 </varlistentry>
1243 <listitem>
1244 <para>
1245 Extract the values for the day, the month and the year from a variable
1246 of type date.
1247 <synopsis>
1248 void PGTYPESdate_julmdy(date d, int *mdy);
1249 </synopsis>
1250 <!-- almost same description as for rjulmdy() -->
1256 </para>
1257 </listitem>
1258 </varlistentry>
1262 <listitem>
1263 <para>
1264 Create a date value from an array of 3 integers that specify the
1265 day, the month and the year of the date.
1266 <synopsis>
1267 void PGTYPESdate_mdyjul(int *mdy, date *jdate);
1268 </synopsis>
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.
1272 </para>
1273 </listitem>
1274 </varlistentry>
1278 <listitem>
1279 <para>
1280 Return a number representing the day of the week for a date value.
1281 <synopsis>
1282 int PGTYPESdate_dayofweek(date d);
1283 </synopsis>
1285 argument and returns an integer that indicates the day of the week for
1286 this date.
1287 <itemizedlist>
1288 <listitem>
1289 <para>
1290 0 - Sunday
1291 </para>
1292 </listitem>
1293 <listitem>
1294 <para>
1295 1 - Monday
1296 </para>
1297 </listitem>
1298 <listitem>
1299 <para>
1300 2 - Tuesday
1301 </para>
1302 </listitem>
1303 <listitem>
1304 <para>
1305 3 - Wednesday
1306 </para>
1307 </listitem>
1308 <listitem>
1309 <para>
1310 4 - Thursday
1311 </para>
1312 </listitem>
1313 <listitem>
1314 <para>
1315 5 - Friday
1316 </para>
1317 </listitem>
1318 <listitem>
1319 <para>
1320 6 - Saturday
1321 </para>
1322 </listitem>
1323 </itemizedlist>
1324 </para>
1325 </listitem>
1326 </varlistentry>
1330 <listitem>
1331 <para>
1332 Get the current date.
1333 <synopsis>
1334 void PGTYPESdate_today(date *d);
1335 </synopsis>
1337 that it sets to the current date.
1338 </para>
1339 </listitem>
1340 </varlistentry>
1344 <listitem>
1345 <para>
1346 Convert a variable of type date to its textual representation using a
1347 format mask.
1348 <synopsis>
1349 int PGTYPESdate_fmt_asc(date dDate, char *fmtstring, char *outbuf);
1350 </synopsis>
1354 </para>
1355 <para>
1356 On success, 0 is returned and a negative value if an error occurred.
1357 </para>
1358 <para>
1359 The following literals are the field specifiers you can use:
1360 <itemizedlist>
1361 <listitem>
1362 <para>
1364 </para>
1365 </listitem>
1366 <listitem>
1367 <para>
1369 </para>
1370 </listitem>
1371 <listitem>
1372 <para>
1374 </para>
1375 </listitem>
1376 <listitem>
1377 <para>
1379 </para>
1380 </listitem>
1381 <listitem>
1382 <para>
1384 </para>
1385 </listitem>
1386 <listitem>
1387 <para>
1389 </para>
1390 </listitem>
1391 </itemizedlist>
1393 </para>
1394 <para>
1395 <xref linkend="ecpg-pgtypesdate-fmt-asc-example-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
1398 </para>
1402 <thead>
1403 <row>
1406 </row>
1407 </thead>
1408 <tbody>
1409 <row>
1412 </row>
1413 <row>
1416 </row>
1417 <row>
1420 </row>
1421 <row>
1424 </row>
1425 <row>
1428 </row>
1429 <row>
1432 </row>
1433 <row>
1436 </row>
1437 <row>
1440 </row>
1441 <row>
1444 </row>
1445 <row>
1448 </row>
1449 <row>
1452 </row>
1453 <row>
1456 </row>
1457 </tbody>
1458 </tgroup>
1459 </table>
1460 </listitem>
1461 </varlistentry>
1465 <listitem>
1466 <para>
1467 Use a format mask to convert a C char* string to a value of type
1468 date.
1469 <synopsis>
1470 int PGTYPESdate_defmt_asc(date *d, char *fmt, char *str);
1471 </synopsis>
1472 <!-- same description as rdefmtdate -->
1473 The function receives a pointer to the date value that should hold the
1477 representation is expected to match the format mask. However you do not
1479 function only analyzes the sequential order and looks for the literals
1483 day.
1484 </para>
1485 <para>
1486 <xref linkend="ecpg-rdefmtdate-example-table"> indicates a few possible formats. This will give
1487 you an idea of how to use this function.
1488 </para>
1492 <thead>
1493 <row>
1497 </row>
1498 </thead>
1499 <tbody>
1500 <row>
1504 </row>
1505 <row>
1509 </row>
1510 <row>
1514 </row>
1515 <row>
1519 </row>
1520 <row>
1524 </row>
1525 <row>
1529 </row>
1530 <row>
1534 </row>
1535 <row>
1537 <entry><literal>In the year 2525, in the month of July, mankind will be alive on the 28th day</literal></entry>
1539 </row>
1540 <row>
1544 </row>
1545 <row>
1549 </row>
1550 <row>
1554 </row>
1555 <row>
1559 </row>
1560 <row>
1564 </row>
1565 </tbody>
1566 </tgroup>
1567 </table>
1568 </listitem>
1569 </varlistentry>
1570 </variablelist>
1571 </para>
1572 </sect2>
1574 <sect2>
1576 <para>
1577 The timestamp type in C enables your programs to deal with data of the SQL
1580 </para>
1581 <para>
1582 The following functions can be used to work with the timestamp type:
1583 <variablelist>
1586 <listitem>
1587 <para>
1588 Parse a timestamp from its textual representation into a timestamp
1589 variable.
1590 <synopsis>
1591 timestamp PGTYPEStimestamp_from_asc(char *str, char **endptr);
1592 </synopsis>
1595 At the moment ecpg always parses
1596 the complete string and so it currently does not support to store the
1599 </para>
1600 <para>
1601 The function returns the parsed timestamp on success. On error,
1603 set to <literal>PGTYPES_TS_BAD_TIMESTAMP</>. See <xref linkend="PGTYPESInvalidTimestamp"> for important notes on this value.
1605 </para>
1606 <para>
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
1612 specifiers are silently discarded.
1613 </para>
1614 <para>
1615 <xref linkend="ecpg-pgtypestimestamp-from-asc-example-table"> contains a few examples for input strings.
1616 </para>
1620 <thead>
1621 <row>
1624 </row>
1625 </thead>
1626 <tbody>
1627 <row>
1630 </row>
1631 <row>
1634 </row>
1635 <row>
1638 </row>
1639 <row>
1642 </row>
1643 </tbody>
1644 </tgroup>
1645 </table>
1646 </listitem>
1647 </varlistentry>
1651 <listitem>
1652 <para>
1653 Converts a date to a C char* string.
1654 <synopsis>
1655 char *PGTYPEStimestamp_to_asc(timestamp tstamp);
1656 </synopsis>
1658 its only argument and returns an allocated string that contains the
1659 textual representation of the timestamp.
1660 </para>
1661 </listitem>
1662 </varlistentry>
1666 <listitem>
1667 <para>
1668 Retrieve the current timestamp.
1669 <synopsis>
1670 void PGTYPEStimestamp_current(timestamp *ts);
1671 </synopsis>
1672 The function retrieves the current timestamp and saves it into the
1674 </para>
1675 </listitem>
1676 </varlistentry>
1680 <listitem>
1681 <para>
1682 Convert a timestamp variable to a C char* using a format mask.
1683 <synopsis>
1684 int PGTYPEStimestamp_fmt_asc(timestamp *ts, char *output, int str_len, char *fmtstr);
1685 </synopsis>
1686 The function receives a pointer to the timestamp to convert as its
1691 </para>
1692 <para>
1693 Upon success, the function returns 0 and a negative value if an
1694 error occurred.
1695 </para>
1696 <para>
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
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
1703 -->
1704 <itemizedlist>
1705 <listitem>
1706 <para>
1708 the full weekday name.
1709 </para>
1710 </listitem>
1711 <listitem>
1712 <para>
1714 the abbreviated weekday name.
1715 </para>
1716 </listitem>
1717 <listitem>
1718 <para>
1720 the full month name.
1721 </para>
1722 </listitem>
1723 <listitem>
1724 <para>
1726 the abbreviated month name.
1727 </para>
1728 </listitem>
1729 <listitem>
1730 <para>
1732 number; single digits are preceded by a zero.
1733 </para>
1734 </listitem>
1735 <listitem>
1736 <para>
1738 time and date.
1739 </para>
1740 </listitem>
1741 <listitem>
1742 <para>
1745 </para>
1746 </listitem>
1747 <listitem>
1748 <para>
1751 </para>
1752 </listitem>
1753 <listitem>
1754 <para>
1756 extensions. The sequences
1776 are supposed to provide alternative representations.
1777 </para>
1778 <para>
1780 alternative months names (used standalone, without day mentioned).
1781 </para>
1782 </listitem>
1783 <listitem>
1784 <para>
1787 </para>
1788 </listitem>
1789 <listitem>
1790 <para>
1792 </para>
1793 </listitem>
1794 <listitem>
1795 <para>
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).
1799 </para>
1800 </listitem>
1801 <listitem>
1802 <para>
1806 </para>
1807 </listitem>
1808 <listitem>
1809 <para>
1812 </para>
1813 </listitem>
1814 <listitem>
1815 <para>
1817 </para>
1818 </listitem>
1819 <listitem>
1820 <para>
1823 </para>
1824 </listitem>
1825 <listitem>
1826 <para>
1829 </para>
1830 </listitem>
1831 <listitem>
1832 <para>
1835 </para>
1836 </listitem>
1837 <listitem>
1838 <para>
1841 </para>
1842 </listitem>
1843 <listitem>
1844 <para>
1847 </para>
1848 </listitem>
1849 <listitem>
1850 <para>
1853 </para>
1854 </listitem>
1855 <listitem>
1856 <para>
1858 </para>
1859 </listitem>
1860 <listitem>
1861 <para>
1863 </para>
1864 </listitem>
1865 <listitem>
1866 <para>
1869 </para>
1870 </listitem>
1871 <listitem>
1872 <para>
1874 </para>
1875 </listitem>
1876 <listitem>
1877 <para>
1879 %p</literal>.
1880 </para>
1881 </listitem>
1882 <listitem>
1883 <para>
1886 </para>
1887 </listitem>
1888 <listitem>
1889 <para>
1891 the Epoch, UTC.
1892 </para>
1893 </listitem>
1894 <listitem>
1895 <para>
1897 </para>
1898 </listitem>
1899 <listitem>
1900 <para>
1902 </para>
1903 </listitem>
1904 <listitem>
1905 <para>
1908 </para>
1909 </listitem>
1910 <listitem>
1911 <para>
1914 </para>
1915 </listitem>
1916 <listitem>
1917 <para>
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.
1923 </para>
1924 </listitem>
1925 <listitem>
1926 <para>
1929 </para>
1930 </listitem>
1931 <listitem>
1932 <para>
1935 </para>
1936 </listitem>
1937 <listitem>
1938 <para>
1941 </para>
1942 </listitem>
1943 <listitem>
1944 <para>
1946 the time.
1947 </para>
1948 </listitem>
1949 <listitem>
1950 <para>
1952 the date.
1953 </para>
1954 </listitem>
1955 <listitem>
1956 <para>
1958 decimal number.
1959 </para>
1960 </listitem>
1961 <listitem>
1962 <para>
1965 </para>
1966 </listitem>
1967 <listitem>
1968 <para>
1970 </para>
1971 </listitem>
1972 <listitem>
1973 <para>
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).
1978 </para>
1979 </listitem>
1980 <listitem>
1981 <para>
1983 the date and time.
1984 </para>
1985 </listitem>
1986 <listitem>
1987 <para>
1989 when performing numerical outputs.
1990 </para>
1991 </listitem>
1992 <listitem>
1993 <para>
1994 $_* - GNU libc extension. Explicitly specify space for padding.
1995 </para>
1996 </listitem>
1997 <listitem>
1998 <para>
2000 for padding.
2001 </para>
2002 </listitem>
2003 <listitem>
2004 <para>
2006 </para>
2007 </listitem>
2008 </itemizedlist>
2009 </para>
2010 </listitem>
2011 </varlistentry>
2015 <listitem>
2016 <para>
2017 Subtract one timestamp from another one and save the result in a
2018 variable of type interval.
2019 <synopsis>
2020 int PGTYPEStimestamp_sub(timestamp *ts1, timestamp *ts2, interval *iv);
2021 </synopsis>
2025 points to.
2026 </para>
2027 <para>
2028 Upon success, the function returns 0 and a negative value if an
2029 error occurred.
2030 </para>
2031 </listitem>
2032 </varlistentry>
2036 <listitem>
2037 <para>
2038 Parse a timestamp value from its textual representation using a
2039 formatting mask.
2040 <synopsis>
2041 int PGTYPEStimestamp_defmt_asc(char *str, char *fmt, timestamp *d);
2042 </synopsis>
2043 The function receives the textual representation of a timestamp in the
2047 </para>
2048 <para>
2050 back to the default formatting mask which is <literal>%Y-%m-%d
2051 %H:%M:%S</literal>.
2052 </para>
2053 <para>
2054 This is the reverse function to <xref
2056 order to find out about the possible formatting mask entries.
2057 </para>
2058 </listitem>
2059 </varlistentry>
2063 <listitem>
2064 <para>
2065 Add an interval variable to a timestamp variable.
2066 <synopsis>
2067 int PGTYPEStimestamp_add_interval(timestamp *tin, interval *span, timestamp *tout);
2068 </synopsis>
2071 interval to the timestamp and saves the resulting timestamp in the
2073 </para>
2074 <para>
2075 Upon success, the function returns 0 and a negative value if an
2076 error occurred.
2077 </para>
2078 </listitem>
2079 </varlistentry>
2083 <listitem>
2084 <para>
2085 Subtract an interval variable from a timestamp variable.
2086 <synopsis>
2087 int PGTYPEStimestamp_sub_interval(timestamp *tin, interval *span, timestamp *tout);
2088 </synopsis>
2092 to.
2093 </para>
2094 <para>
2095 Upon success, the function returns 0 and a negative value if an
2096 error occurred.
2097 </para>
2098 </listitem>
2099 </varlistentry>
2100 </variablelist>
2101 </para>
2102 </sect2>
2104 <sect2>
2106 <para>
2107 The interval type in C enables your programs to deal with data of the SQL
2110 </para>
2111 <para>
2112 The following functions can be used to work with the interval type:
2113 <variablelist>
2117 <listitem>
2118 <para>
2119 Return a pointer to a newly allocated interval variable.
2120 <synopsis>
2121 interval *PGTYPESinterval_new(void);
2122 </synopsis>
2123 </para>
2124 </listitem>
2125 </varlistentry>
2129 <listitem>
2130 <para>
2131 Release the memory of a previously allocated interval variable.
2132 <synopsis>
2133 void PGTYPESinterval_new(interval *intvl);
2134 </synopsis>
2135 </para>
2136 </listitem>
2137 </varlistentry>
2141 <listitem>
2142 <para>
2143 Parse an interval from its textual representation.
2144 <synopsis>
2145 interval *PGTYPESinterval_from_asc(char *str, char **endptr);
2146 </synopsis>
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
2153 </para>
2154 </listitem>
2155 </varlistentry>
2159 <listitem>
2160 <para>
2161 Convert a variable of type interval to its textual representation.
2162 <synopsis>
2163 char *PGTYPESinterval_to_asc(interval *span);
2164 </synopsis>
2166 points to into a C char*. The output looks like this example:
2168 </para>
2169 </listitem>
2170 </varlistentry>
2174 <listitem>
2175 <para>
2176 Copy a variable of type interval.
2177 <synopsis>
2178 int PGTYPESinterval_copy(interval *intvlsrc, interval *intvldest);
2179 </synopsis>
2182 that you need to allocate the memory for the destination variable
2183 before.
2184 </para>
2185 </listitem>
2186 </varlistentry>
2187 </variablelist>
2188 </para>
2189 </sect2>
2191 <sect2>
2193 <para>
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
2201 </para>
2202 <para>
2203 The following functions can be used to work with the decimal type and are
2205 <variablelist>
2206 <varlistentry>
2208 <listitem>
2209 <para>
2210 Request a pointer to a newly allocated decimal variable.
2211 <synopsis>
2212 decimal *PGTYPESdecimal_new(void);
2213 </synopsis>
2214 </para>
2215 </listitem>
2216 </varlistentry>
2218 <varlistentry>
2220 <listitem>
2221 <para>
2222 Free a decimal type, release all of its memory.
2223 <synopsis>
2224 void PGTYPESdecimal_free(decimal *var);
2225 </synopsis>
2226 </para>
2227 </listitem>
2228 </varlistentry>
2229 </variablelist>
2230 </para>
2231 </sect2>
2233 <sect2>
2235 <para>
2236 <variablelist>
2237 <varlistentry>
2239 <listitem>
2240 <para>
2241 An argument should contain a numeric variable (or point to a numeric
2242 variable) but in fact its in-memory representation was invalid.
2243 </para>
2244 </listitem>
2245 </varlistentry>
2247 <varlistentry>
2249 <listitem>
2250 <para>
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.
2254 </para>
2255 </listitem>
2256 </varlistentry>
2258 <varlistentry>
2260 <listitem>
2261 <para>
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.
2265 </para>
2266 </listitem>
2267 </varlistentry>
2269 <varlistentry>
2271 <listitem>
2272 <para>
2273 A division by zero has been attempted.
2274 </para>
2275 </listitem>
2276 </varlistentry>
2278 <varlistentry>
2280 <listitem>
2281 <para>
2283 </para>
2284 </listitem>
2285 </varlistentry>
2287 <varlistentry>
2289 <listitem>
2290 <para>
2292 </para>
2293 </listitem>
2294 </varlistentry>
2296 <varlistentry>
2298 <listitem>
2299 <para>
2301 </para>
2302 </listitem>
2303 </varlistentry>
2305 <varlistentry>
2307 <listitem>
2308 <para>
2310 </para>
2311 </listitem>
2312 </varlistentry>
2314 <varlistentry>
2316 <listitem>
2317 <para>
2319 </para>
2320 </listitem>
2321 </varlistentry>
2323 <varlistentry>
2325 <listitem>
2326 <para>
2328 </para>
2329 </listitem>
2330 </varlistentry>
2332 <varlistentry>
2334 <listitem>
2335 <para>
2337 </para>
2338 </listitem>
2339 </varlistentry>
2341 <varlistentry>
2343 <listitem>
2344 <para>
2346 </para>
2347 </listitem>
2348 </varlistentry>
2349 </variablelist>
2350 </para>
2351 </sect2>
2353 <sect2>
2355 <para>
2356 <variablelist>
2359 <listitem>
2360 <para>
2361 A value of type timestamp representing an invalid time stamp. This is
2363 parse error.
2364 Note that due to the internal representation of the timestamp datatype,
2367 to detect errors, make sure that your application does not only test
2371 </para>
2372 </listitem>
2373 </varlistentry>
2374 </variablelist>
2375 </para>
2376 </sect2>
2377 </sect1>
2381 <para>
2384 precompiler for <productname>Informix</productname> E/SQL. Generally spoken this will allow you to use
2386 embedded SQL commands.:
2387 <programlisting>
2388 $int j = 3;
2389 $CONNECT TO :dbname;
2390 $CREATE TABLE test(i INT PRIMARY KEY, j INT);
2391 $INSERT INTO test(i, j) VALUES (7, :j);
2392 $COMMIT;
2393 </programlisting>
2394 </para>
2395 <para>
2396 There are two compatiblity modes: INFORMIX, INFORMIX_SE
2397 </para>
2398 <para>
2399 When linking programs that use this compatibility mode, remember to link
2401 </para>
2402 <para>
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.
2406 </para>
2407 <para>
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,
2419 find support in ecpg for that either.
2420 </para>
2422 <sect2>
2424 <para>
2425 <variablelist>
2426 <varlistentry>
2428 <listitem>
2429 <para>
2430 This statement closes the current connection. In fact, this is a
2432 <programlisting>
2433 $CLOSE DATABASE; /* close the current connection */
2434 EXEC SQL CLOSE DATABASE;
2435 </programlisting>
2436 </para>
2437 </listitem>
2438 </varlistentry>
2439 </variablelist>
2440 </para>
2441 </sect2>
2443 <sect2>
2445 <para>
2446 <variablelist>
2447 <varlistentry>
2449 <listitem>
2450 <para>
2451 Add two decimal type values.
2452 <synopsis>
2453 int decadd(decimal *arg1, decimal *arg2, decimal *sum);
2454 </synopsis>
2455 The function receives a pointer to the first operand of type decimal
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
2462 pgtypeslib.
2463 </para>
2464 </listitem>
2465 </varlistentry>
2467 <varlistentry>
2469 <listitem>
2470 <para>
2471 Compare two variables of type decimal.
2472 <synopsis>
2473 int deccmp(decimal *arg1, decimal *arg2);
2474 </synopsis>
2475 The function receives a pointer to the first decimal value
2478 the bigger value.
2479 <itemizedlist>
2480 <listitem>
2481 <para>
2484 </para>
2485 </listitem>
2486 <listitem>
2487 <para>
2490 </listitem>
2491 <listitem>
2492 <para>
2495 </para>
2496 </listitem>
2497 </itemizedlist>
2498 </para>
2499 </listitem>
2500 </varlistentry>
2502 <varlistentry>
2504 <listitem>
2505 <para>
2506 Copy a decimal value.
2507 <synopsis>
2508 void deccopy(decimal *src, decimal *target);
2509 </synopsis>
2510 The function receives a pointer to the decimal value that should be
2513 argument.
2514 </para>
2515 </listitem>
2516 </varlistentry>
2518 <varlistentry>
2520 <listitem>
2521 <para>
2522 Convert a value from its ASCII representation into a decimal type.
2523 <synopsis>
2524 int deccvasc(char *cp, int len, decimal *np);
2525 </synopsis>
2526 The function receives a pointer to string that contains the string
2529 decimal value that saves the result of the operation.
2530 </para>
2531 <para>
2532 Valid formats are for example:
2538 </para>
2539 <para>
2540 The function returns 0 on success. If overflow or underflow occurred,
2543 representation could not be parsed,
2546 parsing the exponent.
2547 </para>
2548 </listitem>
2549 </varlistentry>
2551 <varlistentry>
2553 <listitem>
2554 <para>
2555 Convert a value of type double to a value of type decimal.
2556 <synopsis>
2557 int deccvdbl(double dbl, decimal *np);
2558 </synopsis>
2559 The function receives the variable of type double that should be
2562 decimal variable that should hold the result of the operation.
2563 </para>
2564 <para>
2565 The function returns 0 on success and a negative value if the
2566 conversion failed.
2567 </para>
2568 </listitem>
2569 </varlistentry>
2571 <varlistentry>
2573 <listitem>
2574 <para>
2575 Convert a value of type int to a value of type decimal.
2576 <synopsis>
2577 int deccvint(int in, decimal *np);
2578 </synopsis>
2579 The function receives the variable of type int that should be
2582 decimal variable that should hold the result of the operation.
2583 </para>
2584 <para>
2585 The function returns 0 on success and a negative value if the
2586 conversion failed.
2587 </para>
2588 </listitem>
2589 </varlistentry>
2591 <varlistentry>
2593 <listitem>
2594 <para>
2595 Convert a value of type long to a value of type decimal.
2596 <synopsis>
2597 int deccvlong(long lng, decimal *np);
2598 </synopsis>
2599 The function receives the variable of type long that should be
2602 decimal variable that should hold the result of the operation.
2603 </para>
2604 <para>
2605 The function returns 0 on success and a negative value if the
2606 conversion failed.
2607 </para>
2608 </listitem>
2609 </varlistentry>
2611 <varlistentry>
2613 <listitem>
2614 <para>
2615 Divide two variables of type decimal.
2616 <synopsis>
2617 int decdiv(decimal *n1, decimal *n2, decimal *result);
2618 </synopsis>
2619 The function receives pointers to the variables that are the first
2622 pointer to the variable that should hold the result of the operation.
2623 </para>
2624 <para>
2625 On success, 0 is returned and a negative value if the division fails.
2626 If overflow or underflow occurred, the function returns
2629 divide by zero is observed, the function returns
2631 </para>
2632 </listitem>
2633 </varlistentry>
2635 <varlistentry>
2637 <listitem>
2638 <para>
2639 Multiply two decimal values.
2640 <synopsis>
2641 int decmul(decimal *n1, decimal *n2, decimal *result);
2642 </synopsis>
2643 The function receives pointers to the variables that are the first
2646 pointer to the variable that should hold the result of the operation.
2647 </para>
2648 <para>
2649 On success, 0 is returned and a negative value if the multiplication
2650 fails. If overflow or underflow occurred, the function returns
2653 </para>
2654 </listitem>
2655 </varlistentry>
2657 <varlistentry>
2659 <listitem>
2660 <para>
2661 Subtract one decimal value from another.
2662 <synopsis>
2663 int decsub(decimal *n1, decimal *n2, decimal *result);
2664 </synopsis>
2665 The function receives pointers to the variables that are the first
2668 pointer to the variable that should hold the result of the operation.
2669 </para>
2670 <para>
2671 On success, 0 is returned and a negative value if the subtraction
2672 fails. If overflow or underflow occurred, the function returns
2675 </para>
2676 </listitem>
2677 </varlistentry>
2679 <varlistentry>
2681 <listitem>
2682 <para>
2683 Convert a variable of type decimal to its ASCII representation in a C
2684 char* string.
2685 <synopsis>
2686 int dectoasc(decimal *np, char *cp, int len, int right)
2687 </synopsis>
2688 The function receives a pointer to a variable of type decimal
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
2695 should be included in the output. If the length of the output buffer,
2697 textual representation including the trailing NUL character, only a
2699 returned.
2700 </para>
2701 <para>
2704 exhausted.
2705 </para>
2706 </listitem>
2707 </varlistentry>
2709 <varlistentry>
2711 <listitem>
2712 <para>
2713 Convert a variable of type decimal to a double.
2714 <synopsis>
2715 int dectodbl(decimal *np, double *dblp);
2716 </synopsis>
2717 The function receives a pointer to the decimal value to convert
2720 </para>
2721 <para>
2722 On success, 0 is returned and a negative value if the conversion
2723 failed.
2724 </para>
2725 </listitem>
2726 </varlistentry>
2728 <varlistentry>
2730 <listitem>
2731 <para>
2732 Convert a variable to type decimal to an integer.
2733 <synopsis>
2734 int dectoint(decimal *np, int *ip);
2735 </synopsis>
2736 The function receives a pointer to the decimal value to convert
2739 </para>
2740 <para>
2741 On success, 0 is returned and a negative value if the conversion
2743 is returned.
2744 </para>
2745 <para>
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
2750 </para>
2751 </listitem>
2752 </varlistentry>
2754 <varlistentry>
2756 <listitem>
2757 <para>
2758 Convert a variable to type decimal to a long integer.
2759 <synopsis>
2760 int dectolong(decimal *np, long *lngp);
2761 </synopsis>
2762 The function receives a pointer to the decimal value to convert
2765 </para>
2766 <para>
2767 On success, 0 is returned and a negative value if the conversion
2769 is returned.
2770 </para>
2771 <para>
2775 implementation depend on the architecture (<literal>-LONG_MAX ..
2776 LONG_MAX</>).
2777 </para>
2778 </listitem>
2779 </varlistentry>
2781 <varlistentry>
2783 <listitem>
2784 <para>
2785 Converts a date to a C char* string.
2786 <synopsis>
2787 int rdatestr(date d, char *str);
2788 </synopsis>
2789 The function receives two arguments, the first one is the date to
2792 to allocate at least 11 bytes (including the NUL-terminator) for the
2793 string.
2794 </para>
2795 <para>
2796 The function returns 0 on success and a negative value in case of
2797 error.
2798 </para>
2799 <para>
2802 environment variables. In ecpg however, you cannot change the output
2803 format.
2804 </para>
2805 </listitem>
2806 </varlistentry>
2808 <varlistentry>
2810 <listitem>
2811 <para>
2812 Parse the textual representation of a date.
2813 <synopsis>
2814 int rstrdate(char *str, date *d);
2815 </synopsis>
2816 The function receives the textual representation of the date to convert
2822 not faster and if you have the choice you should opt for
2824 explicitly.
2825 </para>
2826 <para>
2828 </para>
2829 </listitem>
2830 </varlistentry>
2832 <varlistentry>
2834 <listitem>
2835 <para>
2836 Get the current date.
2837 <synopsis>
2838 void rtoday(date *d);
2839 </synopsis>
2841 that it sets to the current date.
2842 </para>
2843 <para>
2845 function.
2846 </para>
2847 </listitem>
2848 </varlistentry>
2850 <varlistentry>
2852 <listitem>
2853 <para>
2854 Extract the values for the day, the month and the year from a variable
2855 of type date.
2856 <synopsis>
2857 int rjulmdy(date d, short mdy[3]);
2858 </synopsis>
2864 </para>
2865 <para>
2866 The function always returns 0 at the moment.
2867 </para>
2868 <para>
2870 function.
2871 </para>
2872 </listitem>
2873 </varlistentry>
2875 <varlistentry>
2877 <listitem>
2878 <para>
2879 Use a format mask to convert a character string to a value of type
2880 date.
2881 <synopsis>
2882 int rdefmtdate(date *d, char *fmt, char *str);
2883 </synopsis>
2884 The function receives a pointer to the date value that should hold the
2888 representation is expected to match the format mask. However you do not
2890 function only analyzes the sequential order and looks for the literals
2894 day.
2895 </para>
2896 <para>
2897 The function returns the following values:
2898 <itemizedlist>
2899 <listitem>
2900 <para>
2901 0 - The function terminated successfully.
2902 </para>
2903 </listitem>
2904 <listitem>
2905 <para>
2907 delimiters between day, month and year. In this case the input
2909 </para>
2910 </listitem>
2911 <listitem>
2912 <para>
2914 correctly indicate the sequential order of year, month and day.
2915 </para>
2916 </listitem>
2917 <listitem>
2918 <para>
2920 contain a valid day.
2921 </para>
2922 </listitem>
2923 <listitem>
2924 <para>
2926 contain a valid month.
2927 </para>
2928 </listitem>
2929 <listitem>
2930 <para>
2932 contain a valid year.
2933 </para>
2934 </listitem>
2935 </itemizedlist>
2936 </para>
2937 <para>
2938 Internally this function is implemented to use the <xref
2940 table of example input.
2941 </para>
2942 </listitem>
2943 </varlistentry>
2945 <varlistentry>
2947 <listitem>
2948 <para>
2949 Convert a variable of type date to its textual representation using a
2950 format mask.
2951 <synopsis>
2952 int rfmtdate(date d, char *fmt, char *str);
2953 </synopsis>
2957 </para>
2958 <para>
2959 On success, 0 is returned and a negative value if an error occurred.
2960 </para>
2961 <para>
2963 function, see the reference there for examples.
2964 </para>
2965 </listitem>
2966 </varlistentry>
2968 <varlistentry>
2970 <listitem>
2971 <para>
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.
2974 <synopsis>
2975 int rmdyjul(short mdy[3], date *d);
2976 </synopsis>
2977 The function receives the array of the 3 short integers
2979 hold the result of the operation.
2980 </para>
2981 <para>
2982 Currently the function returns always 0.
2983 </para>
2984 <para>
2985 Internally the function is implemented to use the function <xref
2987 </para>
2988 </listitem>
2989 </varlistentry>
2991 <varlistentry>
2993 <listitem>
2994 <para>
2995 Return a number representing the day of the week for a date value.
2996 <synopsis>
2997 int rdayofweek(date d);
2998 </synopsis>
3000 argument and returns an integer that indicates the day of the week for
3001 this date.
3002 <itemizedlist>
3003 <listitem>
3004 <para>
3005 0 - Sunday
3006 </para>
3007 </listitem>
3008 <listitem>
3009 <para>
3010 1 - Monday
3011 </para>
3012 </listitem>
3013 <listitem>
3014 <para>
3015 2 - Tuesday
3016 </para>
3017 </listitem>
3018 <listitem>
3019 <para>
3020 3 - Wednesday
3021 </para>
3022 </listitem>
3023 <listitem>
3024 <para>
3025 4 - Thursday
3026 </para>
3027 </listitem>
3028 <listitem>
3029 <para>
3030 5 - Friday
3031 </para>
3032 </listitem>
3033 <listitem>
3034 <para>
3035 6 - Saturday
3036 </para>
3037 </listitem>
3038 </itemizedlist>
3039 </para>
3040 <para>
3041 Internally the function is implemented to use the function <xref
3043 </para>
3044 </listitem>
3045 </varlistentry>
3047 <varlistentry>
3049 <listitem>
3050 <para>
3051 Retrieve the current timestamp.
3052 <synopsis>
3053 void dtcurrent(timestamp *ts);
3054 </synopsis>
3055 The function retrieves the current timestamp and saves it into the
3057 </para>
3058 </listitem>
3059 </varlistentry>
3061 <varlistentry>
3063 <listitem>
3064 <para>
3065 Parses a timestamp from its textual representation in ANSI standard
3066 into a timestamp variable.
3067 <synopsis>
3068 int dtcvasc(char *str, timestamp *ts);
3069 </synopsis>
3071 pointer to the timestamp variable that should hold the result of the
3073 </para>
3074 <para>
3075 The function returns 0 on success and a negative value in case of
3076 error.
3077 </para>
3078 <para>
3079 Internally this function uses the <xref
3081 for a table with example inputs.
3082 </para>
3083 </listitem>
3084 </varlistentry>
3086 <varlistentry>
3088 <listitem>
3089 <para>
3090 Parses a timestamp from its textual representation in ANSI standard
3091 using a format mask into a timestamp variable.
3092 <synopsis>
3093 dtcvfmtasc(char *inbuf, char *fmtstr, timestamp *dtvalue)
3094 </synopsis>
3098 </para>
3099 <para>
3100 This functions is implemented by means of the <xref
3102 there for a list of format specifiers that can be used.
3103 </para>
3104 <para>
3105 The function returns 0 on success and a negative value in case of
3106 error.
3107 </para>
3108 </listitem>
3109 </varlistentry>
3111 <varlistentry>
3113 <listitem>
3114 <para>
3115 Subtract one timestamp from another and return a variable of type
3116 interval.
3117 <synopsis>
3118 int dtsub(timestamp *ts1, timestamp *ts2, interval *iv);
3119 </synopsis>
3123 points to.
3124 </para>
3125 <para>
3126 Upon success, the function returns 0 and a negative value if an
3127 error occurred.
3128 </para>
3129 </listitem>
3130 </varlistentry>
3132 <varlistentry>
3134 <listitem>
3135 <para>
3136 Convert a timestamp variable to a C char* string.
3137 <synopsis>
3138 int dttoasc(timestamp *ts, char *output);
3139 </synopsis>
3140 The function receives a pointer to the timestamp variable to convert
3143 textual representation in the ANSI SQL standard which is defined to
3145 </para>
3146 <para>
3147 Upon success, the function returns 0 and a negative value if an
3148 error occurred.
3149 </para>
3150 </listitem>
3151 </varlistentry>
3153 <varlistentry>
3155 <listitem>
3156 <para>
3157 Convert a timestamp variable to a C char* using a format mask.
3158 <synopsis>
3159 int dttofmtasc(timestamp *ts, char *output, int str_len, char *fmtstr);
3160 </synopsis>
3161 The function receives a pointer to the timestamp to convert as its
3166 </para>
3167 <para>
3168 Upon success, the function returns 0 and a negative value if an
3169 error occurred.
3170 </para>
3171 <para>
3172 Internally, this function uses the <xref
3174 information on what format mask specifiers can be used.
3175 </para>
3176 </listitem>
3177 </varlistentry>
3179 <varlistentry>
3181 <listitem>
3182 <para>
3183 Convert an interval variable to a C char* string.
3184 <synopsis>
3185 int intoasc(interval *i, char *str);
3186 </synopsis>
3187 The function receives a pointer to the interval variable to convert
3190 textual representation in the ANSI SQL standard which is defined to
3192 </para>
3193 <para>
3194 Upon success, the function returns 0 and a negative value if an
3195 error occurred.
3196 </para>
3197 </listitem>
3198 </varlistentry>
3200 <varlistentry>
3202 <listitem>
3203 <para>
3204 Convert a long integer value to its textual representation using a
3205 format mask.
3206 <synopsis>
3207 int rfmtlong(long lng_val, char *fmt, char *outbuf);
3208 </synopsis>
3212 mask to its textual representation.
3213 </para>
3214 <para>
3215 The format mask can be composed of the following format specifying
3216 characters:
3217 <itemizedlist>
3218 <listitem>
3219 <para>
3221 otherwise, fill it with an asterisk.
3222 </para>
3223 </listitem>
3224 <listitem>
3225 <para>
3227 blank otherwise, fill it with a zero.
3228 </para>
3229 </listitem>
3230 <listitem>
3231 <para>
3233 </para>
3234 </listitem>
3235 <listitem>
3236 <para>
3238 </para>
3239 </listitem>
3240 <listitem>
3241 <para>
3243 into groups of three digits separated by a comma.
3244 </para>
3245 </listitem>
3246 <listitem>
3247 <para>
3249 whole-number part of the number from the fractional part.
3250 </para>
3251 </listitem>
3252 <listitem>
3253 <para>
3255 is a negative value.
3256 </para>
3257 </listitem>
3258 <listitem>
3259 <para>
3261 a positive value.
3262 </para>
3263 </listitem>
3264 <listitem>
3265 <para>
3267 negative number. The minus sign will not appear.
3268 </para>
3269 </listitem>
3270 <listitem>
3271 <para>
3273 printed behind the negative value.
3274 </para>
3275 </listitem>
3276 <listitem>
3277 <para>
3279 </para>
3280 </listitem>
3281 </itemizedlist>
3282 </para>
3283 </listitem>
3284 </varlistentry>
3286 <varlistentry>
3288 <listitem>
3289 <para>
3290 Convert a string to upper case.
3291 <synopsis>
3292 void rupshift(char *str);
3293 </synopsis>
3294 The function receives a pointer to the string and transforms every
3295 lower case character to upper case.
3296 </para>
3297 </listitem>
3298 </varlistentry>
3300 <varlistentry>
3302 <listitem>
3303 <para>
3304 Return the number of characters in a string without counting trailing
3305 blanks.
3306 <synopsis>
3307 int byleng(char *str, int len);
3308 </synopsis>
3309 The function expects a fixed-length string as its first argument
3312 that is the length of the string without trailing blanks.
3313 </para>
3314 </listitem>
3315 </varlistentry>
3317 <varlistentry>
3319 <listitem>
3320 <para>
3321 Copy a fixed-length string into a null-terminated string.
3322 <synopsis>
3323 void ldchar(char *src, int len, char *dest);
3324 </synopsis>
3325 The function receives the fixed-length string to copy
3330 location (less if the source string has trailing blanks) and adds the
3331 null-terminator.
3332 </para>
3333 </listitem>
3334 </varlistentry>
3336 <varlistentry>
3338 <listitem>
3339 <para>
3340 <synopsis>
3341 int rgetmsg(int msgnum, char *s, int maxsize);
3342 </synopsis>
3343 This function exists but is not implemented at the moment!
3344 </para>
3345 </listitem>
3346 </varlistentry>
3348 <varlistentry>
3350 <listitem>
3351 <para>
3352 <synopsis>
3353 int rtypalign(int offset, int type);
3354 </synopsis>
3355 This function exists but is not implemented at the moment!
3356 </para>
3357 </listitem>
3358 </varlistentry>
3360 <varlistentry>
3362 <listitem>
3363 <para>
3364 <synopsis>
3365 int rtypmsize(int type, int len);
3366 </synopsis>
3367 This function exists but is not implemented at the moment!
3368 </para>
3369 </listitem>
3370 </varlistentry>
3372 <varlistentry>
3374 <listitem>
3375 <para>
3376 <synopsis>
3377 int rtypwidth(int sqltype, int sqllen);
3378 </synopsis>
3379 This function exists but is not implemented at the moment!
3380 </para>
3381 </listitem>
3382 </varlistentry>
3386 <listitem>
3387 <para>
3388 Set a variable to NULL.
3389 <synopsis>
3390 int rsetnull(int t, char *ptr);
3391 </synopsis>
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
3394 char* pointer.
3395 </para>
3396 <para>
3397 The following types exist:
3398 <itemizedlist>
3399 <listitem>
3400 <para>
3402 </para>
3403 </listitem>
3404 <listitem>
3405 <para>
3407 </para>
3408 </listitem>
3409 <listitem>
3410 <para>
3412 </para>
3413 </listitem>
3414 <listitem>
3415 <para>
3417 </para>
3418 </listitem>
3419 <listitem>
3420 <para>
3422 </para>
3423 </listitem>
3424 <listitem>
3425 <para>
3427 </para>
3428 </listitem>
3429 <listitem>
3430 <para>
3432 </para>
3433 </listitem>
3434 <listitem>
3435 <para>
3437 </para>
3438 </listitem>
3439 <listitem>
3440 <para>
3442 </para>
3443 </listitem>
3444 <listitem>
3445 <para>
3447 </para>
3448 </listitem>
3449 </itemizedlist>
3450 </para>
3452 <para>
3453 Here is an example of a call to this function:
3454 <programlisting><![CDATA[
3455 $char c[] = "abc ";
3456 $short s = 17;
3457 $int i = -74874;
3459 rsetnull(CCHARTYPE, (char *) c);
3460 rsetnull(CSHORTTYPE, (char *) &s);
3461 rsetnull(CINTTYPE, (char *) &i);
3462 ]]>
3463 </programlisting>
3464 </para>
3465 </listitem>
3466 </varlistentry>
3468 <varlistentry>
3470 <listitem>
3471 <para>
3472 Test if a variable is NULL.
3473 <synopsis>
3474 int risnull(int t, char *ptr);
3475 </synopsis>
3478 latter needs to be casted to a char*. See the function <xref
3480 </para>
3481 <para>
3482 Here is an example of how to use this function:
3483 <programlisting><![CDATA[
3484 $char c[] = "abc ";
3485 $short s = 17;
3486 $int i = -74874;
3488 risnull(CCHARTYPE, (char *) c);
3489 risnull(CSHORTTYPE, (char *) &s);
3490 risnull(CINTTYPE, (char *) &i);
3491 ]]>
3492 </programlisting>
3493 </para>
3494 </listitem>
3495 </varlistentry>
3496 </variablelist>
3497 </para>
3498 </sect2>
3500 <sect2>
3502 <para>
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
3508 values.
3509 <variablelist>
3510 <varlistentry>
3512 <listitem>
3513 <para>
3514 Functions return this value if an overflow occurred in a
3516 definition).
3517 </para>
3518 </listitem>
3519 </varlistentry>
3521 <varlistentry>
3523 <listitem>
3524 <para>
3525 Functions return this value if an underflow occurred in a calculation.
3527 </para>
3528 </listitem>
3529 </varlistentry>
3531 <varlistentry>
3533 <listitem>
3534 <para>
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).
3537 </para>
3538 </listitem>
3539 </varlistentry>
3541 <varlistentry>
3543 <listitem>
3544 <para>
3545 Functions return this value if a bad value for a year was found while
3547 definition).
3548 </para>
3549 </listitem>
3550 </varlistentry>
3552 <varlistentry>
3554 <listitem>
3555 <para>
3556 Functions return this value if a bad value for a month was found while
3558 definition).
3559 </para>
3560 </listitem>
3561 </varlistentry>
3563 <varlistentry>
3565 <listitem>
3566 <para>
3567 Functions return this value if a bad value for a day was found while
3569 definition).
3570 </para>
3571 </listitem>
3572 </varlistentry>
3574 <varlistentry>
3576 <listitem>
3577 <para>
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.
3581 </para>
3582 </listitem>
3583 </varlistentry>
3585 <varlistentry>
3587 <listitem>
3588 <para>
3589 Functions return this value if Internally it is defined to -1210 (the
3591 </para>
3592 </listitem>
3593 </varlistentry>
3595 <varlistentry>
3597 <listitem>
3598 <para>
3599 Functions return this value if Internally it is defined to -1211 (the
3601 </para>
3602 </listitem>
3603 </varlistentry>
3605 <varlistentry>
3607 <listitem>
3608 <para>
3609 Functions return this value if a parsing routine was supposed to get a
3611 correctly. Internally it is defined to -1212 (the <productname>Informix</productname> definition).
3612 </para>
3613 </listitem>
3614 </varlistentry>
3616 <varlistentry>
3618 <listitem>
3619 <para>
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.
3625 </para>
3626 </listitem>
3627 </varlistentry>
3629 <varlistentry>
3631 <listitem>
3632 <para>
3633 Functions return this value if Internally it is defined to -1216 (the
3635 </para>
3636 </listitem>
3637 </varlistentry>
3639 <varlistentry>
3641 <listitem>
3642 <para>
3643 Functions return this value if Internally it is defined to -1218 (the
3645 </para>
3646 </listitem>
3647 </varlistentry>
3649 <varlistentry>
3651 <listitem>
3652 <para>
3653 Functions return this value if Internally it is defined to -1264 (the
3655 </para>
3656 </listitem>
3657 </varlistentry>
3658 </variablelist>
3659 </para>
3660 </sect2>
3661 </sect1>
3666 <para>
3667 An SQL descriptor area is a more sophisticated method for
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.
3674 </para>
3676 <para>
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
3680 result row.
3681 </para>
3683 <para>
3684 Before you can use an SQL descriptor area, you need to allocate one:
3685 <programlisting>
3687 </programlisting>
3690 When you don't need the descriptor anymore, you should deallocate
3691 it:
3692 <programlisting>
3694 </programlisting>
3695 </para>
3697 <para>
3698 To use a descriptor area, specify it as the storage target in an
3700 <programlisting>
3701 EXEC SQL FETCH NEXT FROM mycursor INTO DESCRIPTOR mydesc;
3702 </programlisting>
3703 </para>
3705 <para>
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:
3710 <programlisting>
3711 EXEC SQL GET DESCRIPTOR <replaceable>name</replaceable> :<replaceable>hostvar</replaceable> = <replaceable>field</replaceable>;
3712 </programlisting>
3713 Currently, there is only one header field defined:
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
3718 command:
3719 <programlisting>
3720 EXEC SQL GET DESCRIPTOR <replaceable>name</replaceable> VALUE <replaceable>num</replaceable> :<replaceable>hostvar</replaceable> = <replaceable>field</replaceable>;
3721 </programlisting>
3723 variable containing an integer. Possible fields are:
3725 <variablelist>
3726 <varlistentry>
3728 <listitem>
3729 <para>
3730 number of rows in the result set
3731 </para>
3732 </listitem>
3733 </varlistentry>
3735 <varlistentry>
3737 <listitem>
3738 <para>
3739 actual data item (therefore, the data type of this field
3740 depends on the query)
3741 </para>
3742 </listitem>
3743 </varlistentry>
3745 <varlistentry>
3747 <listitem>
3748 <para>
3749 ?
3750 </para>
3751 </listitem>
3752 </varlistentry>
3754 <varlistentry>
3756 <listitem>
3757 <para>
3758 not implemented
3759 </para>
3760 </listitem>
3761 </varlistentry>
3763 <varlistentry>
3765 <listitem>
3766 <para>
3767 the indicator (indicating a null value or a value truncation)
3768 </para>
3769 </listitem>
3770 </varlistentry>
3772 <varlistentry>
3774 <listitem>
3775 <para>
3776 not implemented
3777 </para>
3778 </listitem>
3779 </varlistentry>
3781 <varlistentry>
3783 <listitem>
3784 <para>
3785 length of the datum in characters
3786 </para>
3787 </listitem>
3788 </varlistentry>
3790 <varlistentry>
3792 <listitem>
3793 <para>
3794 name of the column
3795 </para>
3796 </listitem>
3797 </varlistentry>
3799 <varlistentry>
3801 <listitem>
3802 <para>
3803 not implemented
3804 </para>
3805 </listitem>
3806 </varlistentry>
3808 <varlistentry>
3810 <listitem>
3811 <para>
3812 length of the character representation of the datum in bytes
3813 </para>
3814 </listitem>
3815 </varlistentry>
3817 <varlistentry>
3819 <listitem>
3820 <para>
3822 </para>
3823 </listitem>
3824 </varlistentry>
3826 <varlistentry>
3828 <listitem>
3829 <para>
3830 length of the datum in characters
3831 </para>
3832 </listitem>
3833 </varlistentry>
3835 <varlistentry>
3837 <listitem>
3838 <para>
3839 length of the character representation of the datum in bytes
3840 </para>
3841 </listitem>
3842 </varlistentry>
3844 <varlistentry>
3846 <listitem>
3847 <para>
3849 </para>
3850 </listitem>
3851 </varlistentry>
3853 <varlistentry>
3855 <listitem>
3856 <para>
3857 numeric code of the data type of the column
3858 </para>
3859 </listitem>
3860 </varlistentry>
3861 </variablelist>
3862 </para>
3863 </sect1>
3868 <para>
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.
3872 </para>
3874 <sect2>
3877 <para>
3878 One simple method to catch errors and warnings is to set a
3879 specific action to be executed whenever a particular condition
3880 occurs. In general:
3881 <programlisting>
3883 </programlisting>
3884 </para>
3886 <para>
3889 <variablelist>
3890 <varlistentry>
3892 <listitem>
3893 <para>
3894 The specified action is called whenever an error occurs during
3895 the execution of an SQL statement.
3896 </para>
3897 </listitem>
3898 </varlistentry>
3900 <varlistentry>
3902 <listitem>
3903 <para>
3904 The specified action is called whenever a warning occurs
3905 during the execution of an SQL statement.
3906 </para>
3907 </listitem>
3908 </varlistentry>
3910 <varlistentry>
3912 <listitem>
3913 <para>
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.)
3917 </para>
3918 </listitem>
3919 </varlistentry>
3920 </variablelist>
3921 </para>
3923 <para>
3926 <variablelist>
3927 <varlistentry>
3929 <listitem>
3930 <para>
3931 This effectively means that the condition is ignored. This is
3932 the default.
3933 </para>
3934 </listitem>
3935 </varlistentry>
3937 <varlistentry>
3940 <listitem>
3941 <para>
3943 statement).
3944 </para>
3945 </listitem>
3946 </varlistentry>
3948 <varlistentry>
3950 <listitem>
3951 <para>
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.
3955 </para>
3956 </listitem>
3957 </varlistentry>
3959 <varlistentry>
3961 <listitem>
3962 <para>
3964 program.
3965 </para>
3966 </listitem>
3967 </varlistentry>
3969 <varlistentry>
3971 <listitem>
3972 <para>
3975 </para>
3976 </listitem>
3977 </varlistentry>
3979 <varlistentry>
3980 <term><literal>CALL <replaceable>name</replaceable> (<replaceable>args</replaceable>)</literal></term>
3981 <term><literal>DO <replaceable>name</replaceable> (<replaceable>args</replaceable>)</literal></term>
3982 <listitem>
3983 <para>
3984 Call the specified C functions with the specified arguments.
3985 </para>
3986 </listitem>
3987 </varlistentry>
3988 </variablelist>
3990 The SQL standard only provides for the actions
3993 </para>
3995 <para>
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:
3999 <programlisting>
4000 EXEC SQL WHENEVER SQLWARNING SQLPRINT;
4001 EXEC SQL WHENEVER SQLERROR STOP;
4002 </programlisting>
4003 </para>
4005 <para>
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
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
4014 desired effect:
4015 <programlisting>
4016 /*
4017 * WRONG
4018 */
4019 int main(int argc, char *argv[])
4020 {
4021 ...
4022 if (verbose) {
4023 EXEC SQL WHENEVER SQLWARNING SQLPRINT;
4024 }
4025 ...
4026 EXEC SQL SELECT ...;
4027 ...
4028 }
4029 </programlisting>
4031 <programlisting>
4032 /*
4033 * WRONG
4034 */
4035 int main(int argc, char *argv[])
4036 {
4037 ...
4038 set_error_handler();
4039 ...
4040 EXEC SQL SELECT ...;
4041 ...
4042 }
4044 static void set_error_handler(void)
4045 {
4046 EXEC SQL WHENEVER SQLERROR STOP;
4047 }
4048 </programlisting>
4049 </para>
4050 </sect2>
4052 <sect2>
4055 <para>
4056 For more powerful error handling, the embedded SQL interface
4058 that has the following structure:
4059 <programlisting>
4060 struct
4061 {
4062 char sqlcaid[8];
4063 long sqlabc;
4064 long sqlcode;
4065 struct
4066 {
4067 int sqlerrml;
4068 char sqlerrmc[SQLERRMC_LEN];
4069 } sqlerrm;
4070 char sqlerrp[8];
4071 long sqlerrd[6];
4072 char sqlwarn[8];
4073 char sqlstate[5];
4074 } sqlca;
4075 </programlisting>
4076 (In a multithreaded program, every thread automatically gets its
4078 handling of the standard C global variable
4080 </para>
4082 <para>
4084 multiple warnings or errors occur during the execution of a
4086 information about the last one.
4087 </para>
4089 <para>
4098 such as that the last query returned zero rows.
4100 different error code schemes; details appear below.
4101 </para>
4103 <para>
4104 If the last SQL statement was successful, then
4106 processed row, if applicable, and
4108 processed or returned rows, if applicable to the command.
4109 </para>
4111 <para>
4112 In case of an error or warning,
4114 that describes the error. The field
4116 the error message that is stored in
4119 programmer). Note that some messages are too long to fit in the
4121 </para>
4123 <para>
4131 to indicate a warning.
4132 </para>
4134 <para>
4140 information.
4141 </para>
4143 <para>
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.
4149 </para>
4150 </sect2>
4152 <sect2>
4155 <para>
4158 provide error codes. Both are derived from the SQL standard, but
4160 edition of the standard and has been dropped in later editions.
4161 Therefore, new applications are strongly encouraged to use
4163 </para>
4165 <para>
4167 characters contain digits or upper-case letters that represent
4168 codes of various error and warning conditions.
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
4174 the most part defined in the SQL standard. The
4177 of consistency can be achieved by using this error code scheme
4178 throughout all applications. For further information see
4180 </para>
4182 <para>
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
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
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
4201 in each case.
4202 </para>
4204 <para>
4207 <variablelist>
4208 <varlistentry>
4210 <listitem>
4211 <para>
4212 Indicates that your virtual memory is exhausted. (SQLSTATE
4213 YE001)
4214 </para>
4215 </listitem>
4216 </varlistentry>
4218 <varlistentry>
4220 <listitem>
4221 <para>
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)
4226 </para>
4227 </listitem>
4228 </varlistentry>
4230 <varlistentry>
4232 <listitem>
4233 <para>
4234 This means that the command specified more host variables than
4236 </para>
4237 </listitem>
4238 </varlistentry>
4240 <varlistentry>
4242 <listitem>
4243 <para>
4244 This means that the command specified fewer host variables than
4246 </para>
4247 </listitem>
4248 </varlistentry>
4250 <varlistentry>
4252 <listitem>
4253 <para>
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)
4257 </para>
4258 </listitem>
4259 </varlistentry>
4261 <varlistentry>
4263 <listitem>
4264 <para>
4266 the database is of a different type and contains a value that
4269 42804)
4270 </para>
4271 </listitem>
4272 </varlistentry>
4274 <varlistentry>
4276 <listitem>
4277 <para>
4279 datum in the database is of a different type and contains a
4280 value that cannot be interpreted as an <type>unsigned
4282 for this conversion. (SQLSTATE 42804)
4283 </para>
4284 </listitem>
4285 </varlistentry>
4287 <varlistentry>
4289 <listitem>
4290 <para>
4292 in the database is of another type and contains a value that
4295 (SQLSTATE 42804)
4296 </para>
4297 </listitem>
4298 </varlistentry>
4300 <varlistentry>
4302 <listitem>
4303 <para>
4307 </para>
4308 </listitem>
4309 </varlistentry>
4311 <varlistentry>
4313 <listitem>
4314 <para>
4316 server was empty. (This cannot normally happen in an embedded
4317 SQL program, so it might point to an internal error.) (SQLSTATE
4318 YE002)
4319 </para>
4320 </listitem>
4321 </varlistentry>
4323 <varlistentry>
4325 <listitem>
4326 <para>
4327 A null value was returned and no null indicator variable was
4328 supplied. (SQLSTATE 22002)
4329 </para>
4330 </listitem>
4331 </varlistentry>
4333 <varlistentry>
4335 <listitem>
4336 <para>
4337 An ordinary variable was used in a place that requires an
4338 array. (SQLSTATE 42804)
4339 </para>
4340 </listitem>
4341 </varlistentry>
4343 <varlistentry>
4345 <listitem>
4346 <para>
4347 The database returned an ordinary variable in a place that
4348 requires array value. (SQLSTATE 42804)
4349 </para>
4350 </listitem>
4351 </varlistentry>
4353 <varlistentry>
4355 <listitem>
4356 <para>
4357 The program tried to access a connection that does not exist.
4358 (SQLSTATE 08003)
4359 </para>
4360 </listitem>
4361 </varlistentry>
4363 <varlistentry>
4365 <listitem>
4366 <para>
4367 The program tried to access a connection that does exist but is
4368 not open. (This is an internal error.) (SQLSTATE YE002)
4369 </para>
4370 </listitem>
4371 </varlistentry>
4373 <varlistentry>
4375 <listitem>
4376 <para>
4377 The statement you are trying to use has not been prepared.
4378 (SQLSTATE 26000)
4379 </para>
4380 </listitem>
4381 </varlistentry>
4383 <varlistentry>
4385 <listitem>
4386 <para>
4387 The descriptor specified was not found. The statement you are
4388 trying to use has not been prepared. (SQLSTATE 33000)
4389 </para>
4390 </listitem>
4391 </varlistentry>
4393 <varlistentry>
4395 <listitem>
4396 <para>
4397 The descriptor index specified was out of range. (SQLSTATE
4398 07009)
4399 </para>
4400 </listitem>
4401 </varlistentry>
4403 <varlistentry>
4405 <listitem>
4406 <para>
4407 An invalid descriptor item was requested. (This is an internal
4408 error.) (SQLSTATE YE002)
4409 </para>
4410 </listitem>
4411 </varlistentry>
4413 <varlistentry>
4415 <listitem>
4416 <para>
4417 During the execution of a dynamic statement, the database
4418 returned a numeric value and the host variable was not numeric.
4419 (SQLSTATE 07006)
4420 </para>
4421 </listitem>
4422 </varlistentry>
4424 <varlistentry>
4426 <listitem>
4427 <para>
4428 During the execution of a dynamic statement, the database
4429 returned a non-numeric value and the host variable was numeric.
4430 (SQLSTATE 07006)
4431 </para>
4432 </listitem>
4433 </varlistentry>
4435 <varlistentry>
4437 <listitem>
4438 <para>
4440 server. The message contains the error message from the
4442 </para>
4443 </listitem>
4444 </varlistentry>
4446 <varlistentry>
4448 <listitem>
4449 <para>
4451 we cannot start, commit, or rollback the transaction.
4452 (SQLSTATE 08007)
4453 </para>
4454 </listitem>
4455 </varlistentry>
4457 <varlistentry>
4459 <listitem>
4460 <para>
4461 The connection attempt to the database did not succeed.
4462 (SQLSTATE 08001)
4463 </para>
4464 </listitem>
4465 </varlistentry>
4467 <varlistentry>
4469 <listitem>
4470 <para>
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)
4474 </para>
4475 </listitem>
4476 </varlistentry>
4477 </variablelist>
4478 </para>
4479 </sect2>
4480 </sect1>
4485 <sect2>
4488 <para>
4489 To include an external file into your embedded SQL program, use:
4490 <programlisting>
4492 </programlisting>
4493 The embedded SQL preprocessor will look for a file named
4495 preprocess it, and include it in the resulting C output. Thus,
4496 embedded SQL statements in the included file are handled correctly.
4497 </para>
4499 <para>
4501 <programlisting>
4503 </programlisting>
4504 because this file would not be subject to SQL command preprocessing.
4505 Naturally, you can continue to use the C
4507 files.
4508 </para>
4510 <note>
4511 <para>
4512 The include file name is case-sensitive, even though the rest of
4514 SQL case-sensitivity rules.
4515 </para>
4516 </note>
4517 </sect2>
4519 <sect2>
4521 <para>
4523 embedded SQL has a similar concept:
4524 <programlisting>
4527 </programlisting>
4528 So you can define a name:
4529 <programlisting>
4530 EXEC SQL DEFINE HAVE_FEATURE;
4531 </programlisting>
4532 And you can also define constants:
4533 <programlisting>
4534 EXEC SQL DEFINE MYNUMBER 12;
4535 EXEC SQL DEFINE MYSTRING 'abc';
4536 </programlisting>
4538 <programlisting>
4539 EXEC SQL UNDEF MYNUMBER;
4540 </programlisting>
4541 </para>
4543 <para>
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:
4549 <programlisting>
4550 EXEC SQL DEFINE MYNUMBER 12;
4551 ...
4552 EXEC SQL UPDATE Tbl SET col = MYNUMBER;
4553 </programlisting>
4554 then ecpg will already do the substitution and your C compiler will never
4557 embedded SQL query because in this case the embedded SQL precompiler is not
4558 able to see this declaration.
4559 </para>
4560 </sect2>
4562 <sect2>
4564 <para>
4565 You can use the following directives to compile code sections conditionally:
4567 <variablelist>
4568 <varlistentry>
4570 <listitem>
4571 <para>
4575 </para>
4576 </listitem>
4577 </varlistentry>
4579 <varlistentry>
4581 <listitem>
4582 <para>
4586 </para>
4587 </listitem>
4588 </varlistentry>
4590 <varlistentry>
4592 <listitem>
4593 <para>
4594 Starts processing an alternative section to a section introduced by
4597 </para>
4598 </listitem>
4599 </varlistentry>
4601 <varlistentry>
4603 <listitem>
4604 <para>
4608 </para>
4609 </listitem>
4610 </varlistentry>
4612 <varlistentry>
4614 <listitem>
4615 <para>
4616 Ends an alternative section.
4617 </para>
4618 </listitem>
4619 </varlistentry>
4620 </variablelist>
4621 </para>
4623 <para>
4624 Example:
4625 <programlisting>
4626 exec sql ifndef TZVAR;
4627 exec sql SET TIMEZONE TO 'GMT';
4628 exec sql elif TZNAME;
4629 exec sql SET TIMEZONE TO TZNAME;
4630 exec sql else;
4631 exec sql SET TIMEZONE TO TZVAR;
4632 exec sql endif;
4633 </programlisting>
4634 </para>
4636 </sect2>
4637 </sect1>
4642 <para>
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
4648 calls. After compiling, you must link with a special library that
4649 contains the needed functions. These functions fetch information
4652 in the arguments specified for output.
4653 </para>
4655 <para>
4658 Embedded SQL programs are typically named with an extension
4661 calling:
4662 <programlisting>
4663 ecpg prog1.pgc
4664 </programlisting>
4666 your input files do not follow the suggested naming pattern, you
4667 can specify the output file explicitly using the
4669 </para>
4671 <para>
4672 The preprocessed file can be compiled normally, for example:
4673 <programlisting>
4674 cc -c prog1.c
4675 </programlisting>
4676 The generated C source files include header files from the
4679 default, you have to add an option such as
4681 command line.
4682 </para>
4684 <para>
4685 To link an embedded SQL program, you need to include the
4687 <programlisting>
4688 cc -o myprog prog1.o prog2.o ... -lecpg
4689 </programlisting>
4690 Again, you might have to add an option like
4692 </para>
4694 <para>
4695 If you manage the build process of a larger project using
4697 the following implicit rule to your makefiles:
4698 <programlisting>
4699 ECPG = ecpg
4701 %.c: %.pgc
4702 $(ECPG) $<
4703 </programlisting>
4704 </para>
4706 <para>
4709 </para>
4711 <para>
4715 command-line options to compile your client code.)
4716 </para>
4717 </sect1>
4722 <para>
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.
4728 </para>
4730 <itemizedlist>
4731 <listitem>
4732 <para>
4735 logging if called with the first argument non-zero. Debug logging
4738 variables inserted, and the results from the
4741 statements.
4742 </para>
4743 <note>
4744 <para>
4746 compiled with different flags, this function call will crash the
4747 application because the internal representation of the
4749 multithreaded/single-threaded, release/debug, and static/dynamic
4750 flags should be the same for the library and all applications using
4751 that library.
4752 </para>
4753 </note>
4754 </listitem>
4756 <listitem>
4757 <para>
4760 returns true if you are connected to a database and false if not.
4762 if a single connection is being used.
4763 </para>
4764 </listitem>
4765 </itemizedlist>
4766 </sect1>
4771 <para>
4773 internally. This information can occasionally be useful to help
4775 </para>
4777 <para>
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.
4783 </para>
4785 <para>
4787 intervenes and changes it. The command starts with <command>EXEC
4790 parsed for variable substitution.
4791 </para>
4793 <para>
4794 Variable substitution occurs when a symbol starts with a colon
4796 among the variables that were previously declared within a
4798 </para>
4800 <para>
4801 The most important function in the library is
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.
4806 </para>
4808 <para>
4809 The arguments are:
4811 <variablelist>
4812 <varlistentry>
4814 <listitem>
4815 <para>
4816 This is the line number of the original line; used in error
4817 messages only.
4818 </para>
4819 </listitem>
4820 </varlistentry>
4822 <varlistentry>
4824 <listitem>
4825 <para>
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
4831 </para>
4832 </listitem>
4833 </varlistentry>
4835 <varlistentry>
4837 <listitem>
4838 <para>
4839 Every input variable causes ten arguments to be created. (See below.)
4840 </para>
4841 </listitem>
4842 </varlistentry>
4844 <varlistentry>
4846 <listitem>
4847 <para>
4849 variables.
4850 </para>
4851 </listitem>
4852 </varlistentry>
4854 <varlistentry>
4856 <listitem>
4857 <para>
4858 Every output variable causes ten arguments to be created.
4859 (See below.) These variables are filled by the function.
4860 </para>
4861 </listitem>
4862 </varlistentry>
4864 <varlistentry>
4866 <listitem>
4867 <para>
4869 </para>
4870 </listitem>
4871 </varlistentry>
4872 </variablelist>
4873 </para>
4875 <para>
4877 command, the function gets ten arguments:
4879 <orderedlist>
4880 <listitem>
4881 <para>
4882 The type as a special symbol.
4883 </para>
4884 </listitem>
4886 <listitem>
4887 <para>
4888 A pointer to the value or a pointer to the pointer.
4889 </para>
4890 </listitem>
4892 <listitem>
4893 <para>
4895 </para>
4896 </listitem>
4898 <listitem>
4899 <para>
4900 The number of elements in the array (for array fetches).
4901 </para>
4902 </listitem>
4904 <listitem>
4905 <para>
4906 The offset to the next element in the array (for array fetches).
4907 </para>
4908 </listitem>
4910 <listitem>
4911 <para>
4912 The type of the indicator variable as a special symbol.
4913 </para>
4914 </listitem>
4916 <listitem>
4917 <para>
4918 A pointer to the indicator variable.
4919 </para>
4920 </listitem>
4922 <listitem>
4923 <para>
4924 0
4925 </para>
4926 </listitem>
4928 <listitem>
4929 <para>
4930 The number of elements in the indicator array (for array fetches).
4931 </para>
4932 </listitem>
4934 <listitem>
4935 <para>
4936 The offset to the next element in the indicator array (for
4937 array fetches).
4938 </para>
4939 </listitem>
4940 </orderedlist>
4941 </para>
4943 <para>
4944 Note that not all SQL commands are treated in this way. For
4945 instance, an open cursor statement like:
4946 <programlisting>
4948 </programlisting>
4949 is not copied to the output. Instead, the cursor's
4951 because it indeed opens the cursor.
4952 </para>
4954 <para>
4955 Here is a complete example describing the output of the
4957 change with each particular version of the preprocessor):
4958 <programlisting>
4959 EXEC SQL BEGIN DECLARE SECTION;
4960 int index;
4961 int result;
4962 EXEC SQL END DECLARE SECTION;
4963 ...
4964 EXEC SQL SELECT res INTO :result FROM mytable WHERE index = :index;
4965 </programlisting>
4966 is translated into:
4967 <programlisting><![CDATA[
4968 /* Processed by ecpg (2.6.0) */
4969 /* These two include files are added by the preprocessor */
4973 /* exec sql begin declare section */
4977 int index;
4978 int result;
4979 /* exec sql end declare section */
4980 ...
4981 ECPGdo(__LINE__, NULL, "SELECT res FROM mytable WHERE index = ? ",
4987 ]]>
4988 </programlisting>
4989 (The indentation here is added for readability and not
4990 something the preprocessor does.)
4991 </para>
4992 </sect1>
4993 </chapter>