diff options
Diffstat (limited to 'doc/src/sgml/html/ecpg-variables.html')
| -rw-r--r-- | doc/src/sgml/html/ecpg-variables.html | 881 |
1 files changed, 881 insertions, 0 deletions
diff --git a/doc/src/sgml/html/ecpg-variables.html b/doc/src/sgml/html/ecpg-variables.html new file mode 100644 index 0000000..18b4674 --- /dev/null +++ b/doc/src/sgml/html/ecpg-variables.html @@ -0,0 +1,881 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>36.4. Using Host Variables</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="ecpg-commands.html" title="36.3. Running SQL Commands" /><link rel="next" href="ecpg-dynamic.html" title="36.5. Dynamic SQL" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">36.4. Using Host Variables</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ecpg-commands.html" title="36.3. Running SQL Commands">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="ecpg.html" title="Chapter 36. ECPG — Embedded SQL in C">Up</a></td><th width="60%" align="center">Chapter 36. <span xmlns="http://www.w3.org/1999/xhtml" class="application">ECPG</span> — Embedded <acronym xmlns="http://www.w3.org/1999/xhtml" class="acronym">SQL</acronym> in C</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="ecpg-dynamic.html" title="36.5. Dynamic SQL">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="ECPG-VARIABLES"><div class="titlepage"><div><div><h2 class="title" style="clear: both">36.4. Using Host Variables</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="ecpg-variables.html#ECPG-VARIABLES-OVERVIEW">36.4.1. Overview</a></span></dt><dt><span class="sect2"><a href="ecpg-variables.html#ECPG-DECLARE-SECTIONS">36.4.2. Declare Sections</a></span></dt><dt><span class="sect2"><a href="ecpg-variables.html#ECPG-RETRIEVING">36.4.3. Retrieving Query Results</a></span></dt><dt><span class="sect2"><a href="ecpg-variables.html#ECPG-VARIABLES-TYPE-MAPPING">36.4.4. Type Mapping</a></span></dt><dt><span class="sect2"><a href="ecpg-variables.html#ECPG-VARIABLES-NONPRIMITIVE-SQL">36.4.5. Handling Nonprimitive SQL Data Types</a></span></dt><dt><span class="sect2"><a href="ecpg-variables.html#ECPG-INDICATORS">36.4.6. Indicators</a></span></dt></dl></div><p> + In <a class="xref" href="ecpg-commands.html" title="36.3. Running SQL Commands">Section 36.3</a> you saw how you can execute SQL + statements from an embedded SQL program. Some of those statements + only used fixed values and did not provide a way to insert + user-supplied values into statements or have the program process + the values returned by the query. Those kinds of statements are + not really useful in real applications. This section explains in + detail how you can pass data between your C program and the + embedded SQL statements using a simple mechanism called + <em class="firstterm">host variables</em>. In an embedded SQL program we + consider the SQL statements to be <em class="firstterm">guests</em> in the C + program code which is the <em class="firstterm">host language</em>. Therefore + the variables of the C program are called <em class="firstterm">host + variables</em>. + </p><p> + Another way to exchange values between PostgreSQL backends and ECPG + applications is the use of SQL descriptors, described + in <a class="xref" href="ecpg-descriptors.html" title="36.7. Using Descriptor Areas">Section 36.7</a>. + </p><div class="sect2" id="ECPG-VARIABLES-OVERVIEW"><div class="titlepage"><div><div><h3 class="title">36.4.1. Overview</h3></div></div></div><p> + Passing data between the C program and the SQL statements is + particularly simple in embedded SQL. Instead of having the + program paste the data into the statement, which entails various + complications, such as properly quoting the value, you can simply + write the name of a C variable into the SQL statement, prefixed by + a colon. For example: +</p><pre class="programlisting"> +EXEC SQL INSERT INTO sometable VALUES (:v1, 'foo', :v2); +</pre><p> + This statement refers to two C variables named + <code class="varname">v1</code> and <code class="varname">v2</code> and also uses a + regular SQL string literal, to illustrate that you are not + restricted to use one kind of data or the other. + </p><p> + This style of inserting C variables in SQL statements works + anywhere a value expression is expected in an SQL statement. + </p></div><div class="sect2" id="ECPG-DECLARE-SECTIONS"><div class="titlepage"><div><div><h3 class="title">36.4.2. Declare Sections</h3></div></div></div><p> + To pass data from the program to the database, for example as + parameters in a query, or to pass data from the database back to + the program, the C variables that are intended to contain this + data need to be declared in specially marked sections, so the + embedded SQL preprocessor is made aware of them. + </p><p> + This section starts with: +</p><pre class="programlisting"> +EXEC SQL BEGIN DECLARE SECTION; +</pre><p> + and ends with: +</p><pre class="programlisting"> +EXEC SQL END DECLARE SECTION; +</pre><p> + Between those lines, there must be normal C variable declarations, + such as: +</p><pre class="programlisting"> +int x = 4; +char foo[16], bar[16]; +</pre><p> + As you can see, you can optionally assign an initial value to the variable. + The variable's scope is determined by the location of its declaring + section within the program. + You can also declare variables with the following syntax which implicitly + creates a declare section: +</p><pre class="programlisting"> +EXEC SQL int i = 4; +</pre><p> + You can have as many declare sections in a program as you like. + </p><p> + The declarations are also echoed to the output file as normal C + variables, so there's no need to declare them again. Variables + that are not intended to be used in SQL commands can be declared + normally outside these special sections. + </p><p> + The definition of a structure or union also must be listed inside + a <code class="literal">DECLARE</code> section. Otherwise the preprocessor cannot + handle these types since it does not know the definition. + </p></div><div class="sect2" id="ECPG-RETRIEVING"><div class="titlepage"><div><div><h3 class="title">36.4.3. Retrieving Query Results</h3></div></div></div><p> + Now you should be able to pass data generated by your program into + an SQL command. But how do you retrieve the results of a query? + For that purpose, embedded SQL provides special variants of the + usual commands <code class="command">SELECT</code> and + <code class="command">FETCH</code>. These commands have a special + <code class="literal">INTO</code> clause that specifies which host variables + the retrieved values are to be stored in. + <code class="command">SELECT</code> is used for a query that returns only + single row, and <code class="command">FETCH</code> is used for a query that + returns multiple rows, using a cursor. + </p><p> + Here is an example: +</p><pre class="programlisting"> +/* + * assume this table: + * CREATE TABLE test1 (a int, b varchar(50)); + */ + +EXEC SQL BEGIN DECLARE SECTION; +int v1; +VARCHAR v2; +EXEC SQL END DECLARE SECTION; + + ... + +EXEC SQL SELECT a, b INTO :v1, :v2 FROM test; +</pre><p> + So the <code class="literal">INTO</code> clause appears between the select + list and the <code class="literal">FROM</code> clause. The number of + elements in the select list and the list after + <code class="literal">INTO</code> (also called the target list) must be + equal. + </p><p> + Here is an example using the command <code class="command">FETCH</code>: +</p><pre class="programlisting"> +EXEC SQL BEGIN DECLARE SECTION; +int v1; +VARCHAR v2; +EXEC SQL END DECLARE SECTION; + + ... + +EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test; + + ... + +do +{ + ... + EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2; + ... +} while (...); +</pre><p> + Here the <code class="literal">INTO</code> clause appears after all the + normal clauses. + </p></div><div class="sect2" id="ECPG-VARIABLES-TYPE-MAPPING"><div class="titlepage"><div><div><h3 class="title">36.4.4. Type Mapping</h3></div></div></div><p> + When ECPG applications exchange values between the PostgreSQL + server and the C application, such as when retrieving query + results from the server or executing SQL statements with input + parameters, the values need to be converted between PostgreSQL + data types and host language variable types (C language data + types, concretely). One of the main points of ECPG is that it + takes care of this automatically in most cases. + </p><p> + In this respect, there are two kinds of data types: Some simple + PostgreSQL data types, such as <code class="type">integer</code> + and <code class="type">text</code>, can be read and written by the application + directly. Other PostgreSQL data types, such + as <code class="type">timestamp</code> and <code class="type">numeric</code> can only be + accessed through special library functions; see + <a class="xref" href="ecpg-variables.html#ECPG-SPECIAL-TYPES" title="36.4.4.2. Accessing Special Data Types">Section 36.4.4.2</a>. + </p><p> + <a class="xref" href="ecpg-variables.html#ECPG-DATATYPE-HOSTVARS-TABLE" title="Table 36.1. Mapping Between PostgreSQL Data Types and C Variable Types">Table 36.1</a> shows which PostgreSQL + data types correspond to which C data types. When you wish to + send or receive a value of a given PostgreSQL data type, you + should declare a C variable of the corresponding C data type in + the declare section. + </p><div class="table" id="ECPG-DATATYPE-HOSTVARS-TABLE"><p class="title"><strong>Table 36.1. Mapping Between PostgreSQL Data Types and C Variable Types</strong></p><div class="table-contents"><table class="table" summary="Mapping Between PostgreSQL Data Types and C Variable Types" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>PostgreSQL data type</th><th>Host variable type</th></tr></thead><tbody><tr><td><code class="type">smallint</code></td><td><code class="type">short</code></td></tr><tr><td><code class="type">integer</code></td><td><code class="type">int</code></td></tr><tr><td><code class="type">bigint</code></td><td><code class="type">long long int</code></td></tr><tr><td><code class="type">decimal</code></td><td><code class="type">decimal</code><a href="#ftn.ECPG-DATATYPE-TABLE-FN" class="footnote"><sup class="footnote" id="ECPG-DATATYPE-TABLE-FN">[a]</sup></a></td></tr><tr><td><code class="type">numeric</code></td><td><code class="type">numeric</code><a href="ecpg-variables.html#ftn.ECPG-DATATYPE-TABLE-FN" class="footnoteref"><sup class="footnoteref">[a]</sup></a></td></tr><tr><td><code class="type">real</code></td><td><code class="type">float</code></td></tr><tr><td><code class="type">double precision</code></td><td><code class="type">double</code></td></tr><tr><td><code class="type">smallserial</code></td><td><code class="type">short</code></td></tr><tr><td><code class="type">serial</code></td><td><code class="type">int</code></td></tr><tr><td><code class="type">bigserial</code></td><td><code class="type">long long int</code></td></tr><tr><td><code class="type">oid</code></td><td><code class="type">unsigned int</code></td></tr><tr><td><code class="type">character(<em class="replaceable"><code>n</code></em>)</code>, <code class="type">varchar(<em class="replaceable"><code>n</code></em>)</code>, <code class="type">text</code></td><td><code class="type">char[<em class="replaceable"><code>n</code></em>+1]</code>, <code class="type">VARCHAR[<em class="replaceable"><code>n</code></em>+1]</code></td></tr><tr><td><code class="type">name</code></td><td><code class="type">char[NAMEDATALEN]</code></td></tr><tr><td><code class="type">timestamp</code></td><td><code class="type">timestamp</code><a href="ecpg-variables.html#ftn.ECPG-DATATYPE-TABLE-FN" class="footnoteref"><sup class="footnoteref">[a]</sup></a></td></tr><tr><td><code class="type">interval</code></td><td><code class="type">interval</code><a href="ecpg-variables.html#ftn.ECPG-DATATYPE-TABLE-FN" class="footnoteref"><sup class="footnoteref">[a]</sup></a></td></tr><tr><td><code class="type">date</code></td><td><code class="type">date</code><a href="ecpg-variables.html#ftn.ECPG-DATATYPE-TABLE-FN" class="footnoteref"><sup class="footnoteref">[a]</sup></a></td></tr><tr><td><code class="type">boolean</code></td><td><code class="type">bool</code><a href="#ftn.id-1.7.5.10.7.5.2.2.17.2.2" class="footnote"><sup class="footnote" id="id-1.7.5.10.7.5.2.2.17.2.2">[b]</sup></a></td></tr><tr><td><code class="type">bytea</code></td><td><code class="type">char *</code>, <code class="type">bytea[<em class="replaceable"><code>n</code></em>]</code></td></tr></tbody><tbody class="footnotes"><tr><td colspan="2"><div id="ftn.ECPG-DATATYPE-TABLE-FN" class="footnote"><p><a href="#ECPG-DATATYPE-TABLE-FN" class="para"><sup class="para">[a] </sup></a>This type can only be accessed through special library functions; see <a class="xref" href="ecpg-variables.html#ECPG-SPECIAL-TYPES" title="36.4.4.2. Accessing Special Data Types">Section 36.4.4.2</a>.</p></div><div id="ftn.id-1.7.5.10.7.5.2.2.17.2.2" class="footnote"><p><a href="#id-1.7.5.10.7.5.2.2.17.2.2" class="para"><sup class="para">[b] </sup></a>declared in <code class="filename">ecpglib.h</code> if not native</p></div></td></tr></tbody></table></div></div><br class="table-break" /><div class="sect3" id="ECPG-CHAR"><div class="titlepage"><div><div><h4 class="title">36.4.4.1. Handling Character Strings</h4></div></div></div><p> + To handle SQL character string data types, such + as <code class="type">varchar</code> and <code class="type">text</code>, there are two + possible ways to declare the host variables. + </p><p> + One way is using <code class="type">char[]</code>, an array + of <code class="type">char</code>, which is the most common way to handle + character data in C. +</p><pre class="programlisting"> +EXEC SQL BEGIN DECLARE SECTION; + char str[50]; +EXEC SQL END DECLARE SECTION; +</pre><p> + Note that you have to take care of the length yourself. If you + use this host variable as the target variable of a query which + returns a string with more than 49 characters, a buffer overflow + occurs. + </p><p> + The other way is using the <code class="type">VARCHAR</code> type, which is a + special type provided by ECPG. The definition on an array of + type <code class="type">VARCHAR</code> is converted into a + named <code class="type">struct</code> for every variable. A declaration like: +</p><pre class="programlisting"> +VARCHAR var[180]; +</pre><p> + is converted into: +</p><pre class="programlisting"> +struct varchar_var { int len; char arr[180]; } var; +</pre><p> + The member <code class="structfield">arr</code> hosts the string + including a terminating zero byte. Thus, to store a string in + a <code class="type">VARCHAR</code> host variable, the host variable has to be + declared with the length including the zero byte terminator. The + member <code class="structfield">len</code> holds the length of the + string stored in the <code class="structfield">arr</code> without the + terminating zero byte. When a host variable is used as input for + a query, if <code class="literal">strlen(arr)</code> + and <code class="structfield">len</code> are different, the shorter one + is used. + </p><p> + <code class="type">VARCHAR</code> can be written in upper or lower case, but + not in mixed case. + </p><p> + <code class="type">char</code> and <code class="type">VARCHAR</code> host variables can + also hold values of other SQL types, which will be stored in + their string forms. + </p></div><div class="sect3" id="ECPG-SPECIAL-TYPES"><div class="titlepage"><div><div><h4 class="title">36.4.4.2. Accessing Special Data Types</h4></div></div></div><p> + ECPG contains some special types that help you to interact easily + with some special data types from the PostgreSQL server. In + particular, it has implemented support for the + <code class="type">numeric</code>, <code class="type">decimal</code>, <code class="type">date</code>, <code class="type">timestamp</code>, + and <code class="type">interval</code> types. These data types cannot usefully be + mapped to primitive host variable types (such + as <code class="type">int</code>, <code class="type">long long int</code>, + or <code class="type">char[]</code>), because they have a complex internal + structure. Applications deal with these types by declaring host + variables in special types and accessing them using functions in + the pgtypes library. The pgtypes library, described in detail + in <a class="xref" href="ecpg-pgtypes.html" title="36.6. pgtypes Library">Section 36.6</a> contains basic functions to deal + with those types, such that you do not need to send a query to + the SQL server just for adding an interval to a time stamp for + example. + </p><p> + The follow subsections describe these special data types. For + more details about pgtypes library functions, + see <a class="xref" href="ecpg-pgtypes.html" title="36.6. pgtypes Library">Section 36.6</a>. + </p><div class="sect4" id="id-1.7.5.10.7.7.4"><div class="titlepage"><div><div><h5 class="title">36.4.4.2.1. timestamp, date</h5></div></div></div><p> + Here is a pattern for handling <code class="type">timestamp</code> variables + in the ECPG host application. + </p><p> + First, the program has to include the header file for the + <code class="type">timestamp</code> type: +</p><pre class="programlisting"> +#include <pgtypes_timestamp.h> +</pre><p> + </p><p> + Next, declare a host variable as type <code class="type">timestamp</code> in + the declare section: +</p><pre class="programlisting"> +EXEC SQL BEGIN DECLARE SECTION; +timestamp ts; +EXEC SQL END DECLARE SECTION; +</pre><p> + </p><p> + And after reading a value into the host variable, process it + using pgtypes library functions. In following example, the + <code class="type">timestamp</code> value is converted into text (ASCII) form + with the <code class="function">PGTYPEStimestamp_to_asc()</code> + function: +</p><pre class="programlisting"> +EXEC SQL SELECT now()::timestamp INTO :ts; + +printf("ts = %s\n", PGTYPEStimestamp_to_asc(ts)); +</pre><p> + This example will show some result like following: +</p><pre class="screen"> +ts = 2010-06-27 18:03:56.949343 +</pre><p> + </p><p> + In addition, the DATE type can be handled in the same way. The + program has to include <code class="filename">pgtypes_date.h</code>, declare a host variable + as the date type and convert a DATE value into a text form using + <code class="function">PGTYPESdate_to_asc()</code> function. For more details about the + pgtypes library functions, see <a class="xref" href="ecpg-pgtypes.html" title="36.6. pgtypes Library">Section 36.6</a>. + </p></div><div class="sect4" id="ECPG-TYPE-INTERVAL"><div class="titlepage"><div><div><h5 class="title">36.4.4.2.2. interval</h5></div></div></div><p> + The handling of the <code class="type">interval</code> type is also similar + to the <code class="type">timestamp</code> and <code class="type">date</code> types. It + is required, however, to allocate memory for + an <code class="type">interval</code> type value explicitly. In other words, + the memory space for the variable has to be allocated in the + heap memory, not in the stack memory. + </p><p> + Here is an example program: +</p><pre class="programlisting"> +#include <stdio.h> +#include <stdlib.h> +#include <pgtypes_interval.h> + +int +main(void) +{ +EXEC SQL BEGIN DECLARE SECTION; + interval *in; +EXEC SQL END DECLARE SECTION; + + EXEC SQL CONNECT TO testdb; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + + in = PGTYPESinterval_new(); + EXEC SQL SELECT '1 min'::interval INTO :in; + printf("interval = %s\n", PGTYPESinterval_to_asc(in)); + PGTYPESinterval_free(in); + + EXEC SQL COMMIT; + EXEC SQL DISCONNECT ALL; + return 0; +} +</pre><p> + </p></div><div class="sect4" id="ECPG-TYPE-NUMERIC-DECIMAL"><div class="titlepage"><div><div><h5 class="title">36.4.4.2.3. numeric, decimal</h5></div></div></div><p> + The handling of the <code class="type">numeric</code> + and <code class="type">decimal</code> types is similar to the + <code class="type">interval</code> type: It requires defining a pointer, + allocating some memory space on the heap, and accessing the + variable using the pgtypes library functions. For more details + about the pgtypes library functions, + see <a class="xref" href="ecpg-pgtypes.html" title="36.6. pgtypes Library">Section 36.6</a>. + </p><p> + No functions are provided specifically for + the <code class="type">decimal</code> type. An application has to convert it + to a <code class="type">numeric</code> variable using a pgtypes library + function to do further processing. + </p><p> + Here is an example program handling <code class="type">numeric</code> + and <code class="type">decimal</code> type variables. +</p><pre class="programlisting"> +#include <stdio.h> +#include <stdlib.h> +#include <pgtypes_numeric.h> + +EXEC SQL WHENEVER SQLERROR STOP; + +int +main(void) +{ +EXEC SQL BEGIN DECLARE SECTION; + numeric *num; + numeric *num2; + decimal *dec; +EXEC SQL END DECLARE SECTION; + + EXEC SQL CONNECT TO testdb; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + + num = PGTYPESnumeric_new(); + dec = PGTYPESdecimal_new(); + + EXEC SQL SELECT 12.345::numeric(4,2), 23.456::decimal(4,2) INTO :num, :dec; + + printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 0)); + printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 1)); + printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 2)); + + /* Convert decimal to numeric to show a decimal value. */ + num2 = PGTYPESnumeric_new(); + PGTYPESnumeric_from_decimal(dec, num2); + + printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 0)); + printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 1)); + printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 2)); + + PGTYPESnumeric_free(num2); + PGTYPESdecimal_free(dec); + PGTYPESnumeric_free(num); + + EXEC SQL COMMIT; + EXEC SQL DISCONNECT ALL; + return 0; +} +</pre><p> + </p></div><div class="sect4" id="id-1.7.5.10.7.7.7"><div class="titlepage"><div><div><h5 class="title">36.4.4.2.4. bytea</h5></div></div></div><p> + The handling of the <code class="type">bytea</code> type is similar to + that of <code class="type">VARCHAR</code>. The definition on an array of type + <code class="type">bytea</code> is converted into a named struct for every + variable. A declaration like: +</p><pre class="programlisting"> +bytea var[180]; +</pre><p> + is converted into: +</p><pre class="programlisting"> +struct bytea_var { int len; char arr[180]; } var; +</pre><p> + The member <code class="structfield">arr</code> hosts binary format + data. It can also handle <code class="literal">'\0'</code> as part of + data, unlike <code class="type">VARCHAR</code>. + The data is converted from/to hex format and sent/received by + ecpglib. + </p><div class="note"><h3 class="title">Note</h3><p> + <code class="type">bytea</code> variable can be used only when + <a class="xref" href="runtime-config-client.html#GUC-BYTEA-OUTPUT">bytea_output</a> is set to <code class="literal">hex</code>. + </p></div></div></div><div class="sect3" id="ECPG-VARIABLES-NONPRIMITIVE-C"><div class="titlepage"><div><div><h4 class="title">36.4.4.3. Host Variables with Nonprimitive Types</h4></div></div></div><p> + As a host variable you can also use arrays, typedefs, structs, and + pointers. + </p><div class="sect4" id="ECPG-VARIABLES-ARRAYS"><div class="titlepage"><div><div><h5 class="title">36.4.4.3.1. Arrays</h5></div></div></div><p> + There are two use cases for arrays as host variables. The first + is a way to store some text string in <code class="type">char[]</code> + or <code class="type">VARCHAR[]</code>, as + explained in <a class="xref" href="ecpg-variables.html#ECPG-CHAR" title="36.4.4.1. Handling Character Strings">Section 36.4.4.1</a>. The second use case is to + retrieve multiple rows from a query result without using a + cursor. Without an array, to process a query result consisting + of multiple rows, it is required to use a cursor and + the <code class="command">FETCH</code> command. But with array host + variables, multiple rows can be received at once. The length of + the array has to be defined to be able to accommodate all rows, + otherwise a buffer overflow will likely occur. + </p><p> + Following example scans the <code class="literal">pg_database</code> + system table and shows all OIDs and names of the available + databases: +</p><pre class="programlisting"> +int +main(void) +{ +EXEC SQL BEGIN DECLARE SECTION; + int dbid[8]; + char dbname[8][16]; + int i; +EXEC SQL END DECLARE SECTION; + + memset(dbname, 0, sizeof(char)* 16 * 8); + memset(dbid, 0, sizeof(int) * 8); + + EXEC SQL CONNECT TO testdb; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + + /* Retrieve multiple rows into arrays at once. */ + EXEC SQL SELECT oid,datname INTO :dbid, :dbname FROM pg_database; + + for (i = 0; i < 8; i++) + printf("oid=%d, dbname=%s\n", dbid[i], dbname[i]); + + EXEC SQL COMMIT; + EXEC SQL DISCONNECT ALL; + return 0; +} +</pre><p> + + This example shows following result. (The exact values depend on + local circumstances.) +</p><pre class="screen"> +oid=1, dbname=template1 +oid=11510, dbname=template0 +oid=11511, dbname=postgres +oid=313780, dbname=testdb +oid=0, dbname= +oid=0, dbname= +oid=0, dbname= +</pre><p> + </p></div><div class="sect4" id="ECPG-VARIABLES-STRUCT"><div class="titlepage"><div><div><h5 class="title">36.4.4.3.2. Structures</h5></div></div></div><p> + A structure whose member names match the column names of a query + result, can be used to retrieve multiple columns at once. The + structure enables handling multiple column values in a single + host variable. + </p><p> + The following example retrieves OIDs, names, and sizes of the + available databases from the <code class="literal">pg_database</code> + system table and using + the <code class="function">pg_database_size()</code> function. In this + example, a structure variable <code class="varname">dbinfo_t</code> with + members whose names match each column in + the <code class="literal">SELECT</code> result is used to retrieve one + result row without putting multiple host variables in + the <code class="literal">FETCH</code> statement. +</p><pre class="programlisting"> +EXEC SQL BEGIN DECLARE SECTION; + typedef struct + { + int oid; + char datname[65]; + long long int size; + } dbinfo_t; + + dbinfo_t dbval; +EXEC SQL END DECLARE SECTION; + + memset(&dbval, 0, sizeof(dbinfo_t)); + + EXEC SQL DECLARE cur1 CURSOR FOR SELECT oid, datname, pg_database_size(oid) AS size FROM pg_database; + EXEC SQL OPEN cur1; + + /* when end of result set reached, break out of while loop */ + EXEC SQL WHENEVER NOT FOUND DO BREAK; + + while (1) + { + /* Fetch multiple columns into one structure. */ + EXEC SQL FETCH FROM cur1 INTO :dbval; + + /* Print members of the structure. */ + printf("oid=%d, datname=%s, size=%lld\n", dbval.oid, dbval.datname, dbval.size); + } + + EXEC SQL CLOSE cur1; +</pre><p> + </p><p> + This example shows following result. (The exact values depend on + local circumstances.) +</p><pre class="screen"> +oid=1, datname=template1, size=4324580 +oid=11510, datname=template0, size=4243460 +oid=11511, datname=postgres, size=4324580 +oid=313780, datname=testdb, size=8183012 +</pre><p> + </p><p> + Structure host variables <span class="quote">“<span class="quote">absorb</span>”</span> as many columns + as the structure as fields. Additional columns can be assigned + to other host variables. For example, the above program could + also be restructured like this, with the <code class="varname">size</code> + variable outside the structure: +</p><pre class="programlisting"> +EXEC SQL BEGIN DECLARE SECTION; + typedef struct + { + int oid; + char datname[65]; + } dbinfo_t; + + dbinfo_t dbval; + long long int size; +EXEC SQL END DECLARE SECTION; + + memset(&dbval, 0, sizeof(dbinfo_t)); + + EXEC SQL DECLARE cur1 CURSOR FOR SELECT oid, datname, pg_database_size(oid) AS size FROM pg_database; + EXEC SQL OPEN cur1; + + /* when end of result set reached, break out of while loop */ + EXEC SQL WHENEVER NOT FOUND DO BREAK; + + while (1) + { + /* Fetch multiple columns into one structure. */ + EXEC SQL FETCH FROM cur1 INTO :dbval, :size; + + /* Print members of the structure. */ + printf("oid=%d, datname=%s, size=%lld\n", dbval.oid, dbval.datname, size); + } + + EXEC SQL CLOSE cur1; +</pre><p> + </p></div><div class="sect4" id="id-1.7.5.10.7.8.5"><div class="titlepage"><div><div><h5 class="title">36.4.4.3.3. Typedefs</h5></div></div></div><p> + Use the <code class="literal">typedef</code> keyword to map new types to already + existing types. +</p><pre class="programlisting"> +EXEC SQL BEGIN DECLARE SECTION; + typedef char mychartype[40]; + typedef long serial_t; +EXEC SQL END DECLARE SECTION; +</pre><p> + Note that you could also use: +</p><pre class="programlisting"> +EXEC SQL TYPE serial_t IS long; +</pre><p> + This declaration does not need to be part of a declare section. + </p></div><div class="sect4" id="id-1.7.5.10.7.8.6"><div class="titlepage"><div><div><h5 class="title">36.4.4.3.4. Pointers</h5></div></div></div><p> + You can declare pointers to the most common types. Note however + that you cannot use pointers as target variables of queries + without auto-allocation. See <a class="xref" href="ecpg-descriptors.html" title="36.7. Using Descriptor Areas">Section 36.7</a> + for more information on auto-allocation. + </p><p> +</p><pre class="programlisting"> +EXEC SQL BEGIN DECLARE SECTION; + int *intp; + char **charp; +EXEC SQL END DECLARE SECTION; +</pre><p> + </p></div></div></div><div class="sect2" id="ECPG-VARIABLES-NONPRIMITIVE-SQL"><div class="titlepage"><div><div><h3 class="title">36.4.5. Handling Nonprimitive SQL Data Types</h3></div></div></div><p> + This section contains information on how to handle nonscalar and + user-defined SQL-level data types in ECPG applications. Note that + this is distinct from the handling of host variables of + nonprimitive types, described in the previous section. + </p><div class="sect3" id="id-1.7.5.10.8.3"><div class="titlepage"><div><div><h4 class="title">36.4.5.1. Arrays</h4></div></div></div><p> + Multi-dimensional SQL-level arrays are not directly supported in ECPG. + One-dimensional SQL-level arrays can be mapped into C array host + variables and vice-versa. However, when creating a statement ecpg does + not know the types of the columns, so that it cannot check if a C array + is input into a corresponding SQL-level array. When processing the + output of an SQL statement, ecpg has the necessary information and thus + checks if both are arrays. + </p><p> + If a query accesses <span class="emphasis"><em>elements</em></span> of an array + separately, then this avoids the use of arrays in ECPG. Then, a + host variable with a type that can be mapped to the element type + should be used. For example, if a column type is array of + <code class="type">integer</code>, a host variable of type <code class="type">int</code> + can be used. Also if the element type is <code class="type">varchar</code> + or <code class="type">text</code>, a host variable of type <code class="type">char[]</code> + or <code class="type">VARCHAR[]</code> can be used. + </p><p> + Here is an example. Assume the following table: +</p><pre class="programlisting"> +CREATE TABLE t3 ( + ii integer[] +); + +testdb=> SELECT * FROM t3; + ii +------------- + {1,2,3,4,5} +(1 row) +</pre><p> + + The following example program retrieves the 4th element of the + array and stores it into a host variable of + type <code class="type">int</code>: +</p><pre class="programlisting"> +EXEC SQL BEGIN DECLARE SECTION; +int ii; +EXEC SQL END DECLARE SECTION; + +EXEC SQL DECLARE cur1 CURSOR FOR SELECT ii[4] FROM t3; +EXEC SQL OPEN cur1; + +EXEC SQL WHENEVER NOT FOUND DO BREAK; + +while (1) +{ + EXEC SQL FETCH FROM cur1 INTO :ii ; + printf("ii=%d\n", ii); +} + +EXEC SQL CLOSE cur1; +</pre><p> + + This example shows the following result: +</p><pre class="screen"> +ii=4 +</pre><p> + </p><p> + To map multiple array elements to the multiple elements in an + array type host variables each element of array column and each + element of the host variable array have to be managed separately, + for example: +</p><pre class="programlisting"> +EXEC SQL BEGIN DECLARE SECTION; +int ii_a[8]; +EXEC SQL END DECLARE SECTION; + +EXEC SQL DECLARE cur1 CURSOR FOR SELECT ii[1], ii[2], ii[3], ii[4] FROM t3; +EXEC SQL OPEN cur1; + +EXEC SQL WHENEVER NOT FOUND DO BREAK; + +while (1) +{ + EXEC SQL FETCH FROM cur1 INTO :ii_a[0], :ii_a[1], :ii_a[2], :ii_a[3]; + ... +} +</pre><p> + </p><p> + Note again that +</p><pre class="programlisting"> +EXEC SQL BEGIN DECLARE SECTION; +int ii_a[8]; +EXEC SQL END DECLARE SECTION; + +EXEC SQL DECLARE cur1 CURSOR FOR SELECT ii FROM t3; +EXEC SQL OPEN cur1; + +EXEC SQL WHENEVER NOT FOUND DO BREAK; + +while (1) +{ + /* WRONG */ + EXEC SQL FETCH FROM cur1 INTO :ii_a; + ... +} +</pre><p> + would not work correctly in this case, because you cannot map an + array type column to an array host variable directly. + </p><p> + Another workaround is to store arrays in their external string + representation in host variables of type <code class="type">char[]</code> + or <code class="type">VARCHAR[]</code>. For more details about this + representation, see <a class="xref" href="arrays.html#ARRAYS-INPUT" title="8.15.2. Array Value Input">Section 8.15.2</a>. Note that + this means that the array cannot be accessed naturally as an + array in the host program (without further processing that parses + the text representation). + </p></div><div class="sect3" id="id-1.7.5.10.8.4"><div class="titlepage"><div><div><h4 class="title">36.4.5.2. Composite Types</h4></div></div></div><p> + Composite types are not directly supported in ECPG, but an easy workaround is possible. + The + available workarounds are similar to the ones described for + arrays above: Either access each attribute separately or use the + external string representation. + </p><p> + For the following examples, assume the following type and table: +</p><pre class="programlisting"> +CREATE TYPE comp_t AS (intval integer, textval varchar(32)); +CREATE TABLE t4 (compval comp_t); +INSERT INTO t4 VALUES ( (256, 'PostgreSQL') ); +</pre><p> + + The most obvious solution is to access each attribute separately. + The following program retrieves data from the example table by + selecting each attribute of the type <code class="type">comp_t</code> + separately: +</p><pre class="programlisting"> +EXEC SQL BEGIN DECLARE SECTION; +int intval; +varchar textval[33]; +EXEC SQL END DECLARE SECTION; + +/* Put each element of the composite type column in the SELECT list. */ +EXEC SQL DECLARE cur1 CURSOR FOR SELECT (compval).intval, (compval).textval FROM t4; +EXEC SQL OPEN cur1; + +EXEC SQL WHENEVER NOT FOUND DO BREAK; + +while (1) +{ + /* Fetch each element of the composite type column into host variables. */ + EXEC SQL FETCH FROM cur1 INTO :intval, :textval; + + printf("intval=%d, textval=%s\n", intval, textval.arr); +} + +EXEC SQL CLOSE cur1; +</pre><p> + </p><p> + To enhance this example, the host variables to store values in + the <code class="command">FETCH</code> command can be gathered into one + structure. For more details about the host variable in the + structure form, see <a class="xref" href="ecpg-variables.html#ECPG-VARIABLES-STRUCT" title="36.4.4.3.2. Structures">Section 36.4.4.3.2</a>. + To switch to the structure, the example can be modified as below. + The two host variables, <code class="varname">intval</code> + and <code class="varname">textval</code>, become members of + the <code class="structname">comp_t</code> structure, and the structure + is specified on the <code class="command">FETCH</code> command. +</p><pre class="programlisting"> +EXEC SQL BEGIN DECLARE SECTION; +typedef struct +{ + int intval; + varchar textval[33]; +} comp_t; + +comp_t compval; +EXEC SQL END DECLARE SECTION; + +/* Put each element of the composite type column in the SELECT list. */ +EXEC SQL DECLARE cur1 CURSOR FOR SELECT (compval).intval, (compval).textval FROM t4; +EXEC SQL OPEN cur1; + +EXEC SQL WHENEVER NOT FOUND DO BREAK; + +while (1) +{ + /* Put all values in the SELECT list into one structure. */ + EXEC SQL FETCH FROM cur1 INTO :compval; + + printf("intval=%d, textval=%s\n", compval.intval, compval.textval.arr); +} + +EXEC SQL CLOSE cur1; +</pre><p> + + Although a structure is used in the <code class="command">FETCH</code> + command, the attribute names in the <code class="command">SELECT</code> + clause are specified one by one. This can be enhanced by using + a <code class="literal">*</code> to ask for all attributes of the composite + type value. +</p><pre class="programlisting"> +... +EXEC SQL DECLARE cur1 CURSOR FOR SELECT (compval).* FROM t4; +EXEC SQL OPEN cur1; + +EXEC SQL WHENEVER NOT FOUND DO BREAK; + +while (1) +{ + /* Put all values in the SELECT list into one structure. */ + EXEC SQL FETCH FROM cur1 INTO :compval; + + printf("intval=%d, textval=%s\n", compval.intval, compval.textval.arr); +} +... +</pre><p> + This way, composite types can be mapped into structures almost + seamlessly, even though ECPG does not understand the composite + type itself. + </p><p> + Finally, it is also possible to store composite type values in + their external string representation in host variables of + type <code class="type">char[]</code> or <code class="type">VARCHAR[]</code>. But that + way, it is not easily possible to access the fields of the value + from the host program. + </p></div><div class="sect3" id="id-1.7.5.10.8.5"><div class="titlepage"><div><div><h4 class="title">36.4.5.3. User-Defined Base Types</h4></div></div></div><p> + New user-defined base types are not directly supported by ECPG. + You can use the external string representation and host variables + of type <code class="type">char[]</code> or <code class="type">VARCHAR[]</code>, and this + solution is indeed appropriate and sufficient for many types. + </p><p> + Here is an example using the data type <code class="type">complex</code> from + the example in <a class="xref" href="xtypes.html" title="38.13. User-Defined Types">Section 38.13</a>. The external string + representation of that type is <code class="literal">(%f,%f)</code>, + which is defined in the + functions <code class="function">complex_in()</code> + and <code class="function">complex_out()</code> functions + in <a class="xref" href="xtypes.html" title="38.13. User-Defined Types">Section 38.13</a>. The following example inserts the + complex type values <code class="literal">(1,1)</code> + and <code class="literal">(3,3)</code> into the + columns <code class="literal">a</code> and <code class="literal">b</code>, and select + them from the table after that. + +</p><pre class="programlisting"> +EXEC SQL BEGIN DECLARE SECTION; + varchar a[64]; + varchar b[64]; +EXEC SQL END DECLARE SECTION; + + EXEC SQL INSERT INTO test_complex VALUES ('(1,1)', '(3,3)'); + + EXEC SQL DECLARE cur1 CURSOR FOR SELECT a, b FROM test_complex; + EXEC SQL OPEN cur1; + + EXEC SQL WHENEVER NOT FOUND DO BREAK; + + while (1) + { + EXEC SQL FETCH FROM cur1 INTO :a, :b; + printf("a=%s, b=%s\n", a.arr, b.arr); + } + + EXEC SQL CLOSE cur1; +</pre><p> + + This example shows following result: +</p><pre class="screen"> +a=(1,1), b=(3,3) +</pre><p> + </p><p> + Another workaround is avoiding the direct use of the user-defined + types in ECPG and instead create a function or cast that converts + between the user-defined type and a primitive type that ECPG can + handle. Note, however, that type casts, especially implicit + ones, should be introduced into the type system very carefully. + </p><p> + For example, +</p><pre class="programlisting"> +CREATE FUNCTION create_complex(r double, i double) RETURNS complex +LANGUAGE SQL +IMMUTABLE +AS $$ SELECT $1 * complex '(1,0')' + $2 * complex '(0,1)' $$; +</pre><p> + After this definition, the following +</p><pre class="programlisting"> +EXEC SQL BEGIN DECLARE SECTION; +double a, b, c, d; +EXEC SQL END DECLARE SECTION; + +a = 1; +b = 2; +c = 3; +d = 4; + +EXEC SQL INSERT INTO test_complex VALUES (create_complex(:a, :b), create_complex(:c, :d)); +</pre><p> + has the same effect as +</p><pre class="programlisting"> +EXEC SQL INSERT INTO test_complex VALUES ('(1,2)', '(3,4)'); +</pre><p> + </p></div></div><div class="sect2" id="ECPG-INDICATORS"><div class="titlepage"><div><div><h3 class="title">36.4.6. Indicators</h3></div></div></div><p> + The examples above do not handle null values. In fact, the + retrieval examples will raise an error if they fetch a null value + from the database. To be able to pass null values to the database + or retrieve null values from the database, you need to append a + second host variable specification to each host variable that + contains data. This second host variable is called the + <em class="firstterm">indicator</em> and contains a flag that tells + whether the datum is null, in which case the value of the real + host variable is ignored. Here is an example that handles the + retrieval of null values correctly: +</p><pre class="programlisting"> +EXEC SQL BEGIN DECLARE SECTION; +VARCHAR val; +int val_ind; +EXEC SQL END DECLARE SECTION: + + ... + +EXEC SQL SELECT b INTO :val :val_ind FROM test1; +</pre><p> + The indicator variable <code class="varname">val_ind</code> will be zero if + the value was not null, and it will be negative if the value was + null. (See <a class="xref" href="ecpg-oracle-compat.html" title="36.16. Oracle Compatibility Mode">Section 36.16</a> to enable + Oracle-specific behavior.) + </p><p> + The indicator has another function: if the indicator value is + positive, it means that the value is not null, but it was + truncated when it was stored in the host variable. + </p><p> + If the argument <code class="literal">-r no_indicator</code> is passed to + the preprocessor <code class="command">ecpg</code>, it works in + <span class="quote">“<span class="quote">no-indicator</span>”</span> mode. In no-indicator mode, if no + indicator variable is specified, null values are signaled (on + input and output) for character string types as empty string and + for integer types as the lowest possible value for type (for + example, <code class="symbol">INT_MIN</code> for <code class="type">int</code>). + </p></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="ecpg-commands.html" title="36.3. Running SQL Commands">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="ecpg.html" title="Chapter 36. ECPG — Embedded SQL in C">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="ecpg-dynamic.html" title="36.5. Dynamic SQL">Next</a></td></tr><tr><td width="40%" align="left" valign="top">36.3. Running SQL Commands </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 36.5. Dynamic SQL</td></tr></table></div></body></html>
\ No newline at end of file |