Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
ecpg-variables.html908 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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 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 class="application">ECPG</span> — Embedded <acronym class="acronym">SQL</acronym> in C</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 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 /></div><div class="sect1" id="ECPG-VARIABLES"><div class="titlepage"><div><div><h2 class="title" style="clear: both">36.4. Using Host Variables <a href="#ECPG-VARIABLES" class="id_link">#</a></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>3   In <a class="xref" href="ecpg-commands.html" title="36.3. Running SQL Commands">Section 36.3</a> you saw how you can execute SQL4   statements from an embedded SQL program.  Some of those statements5   only used fixed values and did not provide a way to insert6   user-supplied values into statements or have the program process7   the values returned by the query.  Those kinds of statements are8   not really useful in real applications.  This section explains in9   detail how you can pass data between your C program and the10   embedded SQL statements using a simple mechanism called11   <em class="firstterm">host variables</em>. In an embedded SQL program we12   consider the SQL statements to be <em class="firstterm">guests</em> in the C13   program code which is the <em class="firstterm">host language</em>. Therefore14   the variables of the C program are called <em class="firstterm">host15   variables</em>.16  </p><p>17   Another way to exchange values between PostgreSQL backends and ECPG18   applications is the use of SQL descriptors, described19   in <a class="xref" href="ecpg-descriptors.html" title="36.7. Using Descriptor Areas">Section 36.7</a>.20  </p><div class="sect2" id="ECPG-VARIABLES-OVERVIEW"><div class="titlepage"><div><div><h3 class="title">36.4.1. Overview <a href="#ECPG-VARIABLES-OVERVIEW" class="id_link">#</a></h3></div></div></div><p>21    Passing data between the C program and the SQL statements is22    particularly simple in embedded SQL.  Instead of having the23    program paste the data into the statement, which entails various24    complications, such as properly quoting the value, you can simply25    write the name of a C variable into the SQL statement, prefixed by26    a colon.  For example:27</p><pre class="programlisting">28EXEC SQL INSERT INTO sometable VALUES (:v1, 'foo', :v2);29</pre><p>30    This statement refers to two C variables named31    <code class="varname">v1</code> and <code class="varname">v2</code> and also uses a32    regular SQL string literal, to illustrate that you are not33    restricted to use one kind of data or the other.34   </p><p>35    This style of inserting C variables in SQL statements works36    anywhere a value expression is expected in an SQL statement.37   </p></div><div class="sect2" id="ECPG-DECLARE-SECTIONS"><div class="titlepage"><div><div><h3 class="title">36.4.2. Declare Sections <a href="#ECPG-DECLARE-SECTIONS" class="id_link">#</a></h3></div></div></div><p>38    To pass data from the program to the database, for example as39    parameters in a query, or to pass data from the database back to40    the program, the C variables that are intended to contain this41    data need to be declared in specially marked sections, so the42    embedded SQL preprocessor is made aware of them.43   </p><p>44    This section starts with:45</p><pre class="programlisting">46EXEC SQL BEGIN DECLARE SECTION;47</pre><p>48    and ends with:49</p><pre class="programlisting">50EXEC SQL END DECLARE SECTION;51</pre><p>52    Between those lines, there must be normal C variable declarations,53    such as:54</p><pre class="programlisting">55int   x = 4;56char  foo[16], bar[16];57</pre><p>58    As you can see, you can optionally assign an initial value to the variable.59    The variable's scope is determined by the location of its declaring60    section within the program.61    You can also declare variables with the following syntax which implicitly62    creates a declare section:63</p><pre class="programlisting">64EXEC SQL int i = 4;65</pre><p>66    You can have as many declare sections in a program as you like.67   </p><p>68    The declarations are also echoed to the output file as normal C69    variables, so there's no need to declare them again.  Variables70    that are not intended to be used in SQL commands can be declared71    normally outside these special sections.72   </p><p>73    The definition of a structure or union also must be listed inside74    a <code class="literal">DECLARE</code> section. Otherwise the preprocessor cannot75    handle these types since it does not know the definition.76   </p></div><div class="sect2" id="ECPG-RETRIEVING"><div class="titlepage"><div><div><h3 class="title">36.4.3. Retrieving Query Results <a href="#ECPG-RETRIEVING" class="id_link">#</a></h3></div></div></div><p>77    Now you should be able to pass data generated by your program into78    an SQL command.  But how do you retrieve the results of a query?79    For that purpose, embedded SQL provides special variants of the80    usual commands <code class="command">SELECT</code> and81    <code class="command">FETCH</code>.  These commands have a special82    <code class="literal">INTO</code> clause that specifies which host variables83    the retrieved values are to be stored in.84    <code class="command">SELECT</code> is used for a query that returns only85    single row, and <code class="command">FETCH</code> is used for a query that86    returns multiple rows, using a cursor.87   </p><p>88    Here is an example:89</p><pre class="programlisting">90/*91 * assume this table:92 * CREATE TABLE test1 (a int, b varchar(50));93 */94 95EXEC SQL BEGIN DECLARE SECTION;96int v1;97VARCHAR v2;98EXEC SQL END DECLARE SECTION;99 100 ...101 102EXEC SQL SELECT a, b INTO :v1, :v2 FROM test;103</pre><p>104    So the <code class="literal">INTO</code> clause appears between the select105    list and the <code class="literal">FROM</code> clause.  The number of106    elements in the select list and the list after107    <code class="literal">INTO</code> (also called the target list) must be108    equal.109   </p><p>110    Here is an example using the command <code class="command">FETCH</code>:111</p><pre class="programlisting">112EXEC SQL BEGIN DECLARE SECTION;113int v1;114VARCHAR v2;115EXEC SQL END DECLARE SECTION;116 117 ...118 119EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test;120 121 ...122 123do124{125    ...126    EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2;127    ...128} while (...);129</pre><p>130    Here the <code class="literal">INTO</code> clause appears after all the131    normal clauses.132   </p></div><div class="sect2" id="ECPG-VARIABLES-TYPE-MAPPING"><div class="titlepage"><div><div><h3 class="title">36.4.4. Type Mapping <a href="#ECPG-VARIABLES-TYPE-MAPPING" class="id_link">#</a></h3></div></div></div><p>133    When ECPG applications exchange values between the PostgreSQL134    server and the C application, such as when retrieving query135    results from the server or executing SQL statements with input136    parameters, the values need to be converted between PostgreSQL137    data types and host language variable types (C language data138    types, concretely).  One of the main points of ECPG is that it139    takes care of this automatically in most cases.140   </p><p>141    In this respect, there are two kinds of data types: Some simple142    PostgreSQL data types, such as <code class="type">integer</code>143    and <code class="type">text</code>, can be read and written by the application144    directly.  Other PostgreSQL data types, such145    as <code class="type">timestamp</code> and <code class="type">numeric</code> can only be146    accessed through special library functions; see147    <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>.148   </p><p>149    <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 PostgreSQL150    data types correspond to which C data types.  When you wish to151    send or receive a value of a given PostgreSQL data type, you152    should declare a C variable of the corresponding C data type in153    the declare section.154   </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 <a href="#ECPG-CHAR" class="id_link">#</a></h4></div></div></div><p>155     To handle SQL character string data types, such156     as <code class="type">varchar</code> and <code class="type">text</code>, there are two157     possible ways to declare the host variables.158    </p><p>159     One way is using <code class="type">char[]</code>, an array160     of <code class="type">char</code>, which is the most common way to handle161     character data in C.162</p><pre class="programlisting">163EXEC SQL BEGIN DECLARE SECTION;164    char str[50];165EXEC SQL END DECLARE SECTION;166</pre><p>167     Note that you have to take care of the length yourself.  If you168     use this host variable as the target variable of a query which169     returns a string with more than 49 characters, a buffer overflow170     occurs.171    </p><p>172     The other way is using the <code class="type">VARCHAR</code> type, which is a173     special type provided by ECPG.  The definition on an array of174     type <code class="type">VARCHAR</code> is converted into a175     named <code class="type">struct</code> for every variable. A declaration like:176</p><pre class="programlisting">177VARCHAR var[180];178</pre><p>179     is converted into:180</p><pre class="programlisting">181struct varchar_var { int len; char arr[180]; } var;182</pre><p>183     The member <code class="structfield">arr</code> hosts the string184     including a terminating zero byte.  Thus, to store a string in185     a <code class="type">VARCHAR</code> host variable, the host variable has to be186     declared with the length including the zero byte terminator.  The187     member <code class="structfield">len</code> holds the length of the188     string stored in the <code class="structfield">arr</code> without the189     terminating zero byte.  When a host variable is used as input for190     a query, if <code class="literal">strlen(arr)</code>191     and <code class="structfield">len</code> are different, the shorter one192     is used.193    </p><p>194     <code class="type">VARCHAR</code> can be written in upper or lower case, but195     not in mixed case.196    </p><p>197     <code class="type">char</code> and <code class="type">VARCHAR</code> host variables can198     also hold values of other SQL types, which will be stored in199     their string forms.200    </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 <a href="#ECPG-SPECIAL-TYPES" class="id_link">#</a></h4></div></div></div><p>201     ECPG contains some special types that help you to interact easily202     with some special data types from the PostgreSQL server. In203     particular, it has implemented support for the204     <code class="type">numeric</code>, <code class="type">decimal</code>, <code class="type">date</code>, <code class="type">timestamp</code>,205     and <code class="type">interval</code> types.  These data types cannot usefully be206     mapped to primitive host variable types (such207     as <code class="type">int</code>, <code class="type">long long int</code>,208     or <code class="type">char[]</code>), because they have a complex internal209     structure.  Applications deal with these types by declaring host210     variables in special types and accessing them using functions in211     the pgtypes library.  The pgtypes library, described in detail212     in <a class="xref" href="ecpg-pgtypes.html" title="36.6. pgtypes Library">Section 36.6</a> contains basic functions to deal213     with those types, such that you do not need to send a query to214     the SQL server just for adding an interval to a time stamp for215     example.216    </p><p>217     The follow subsections describe these special data types. For218     more details about pgtypes library functions,219     see <a class="xref" href="ecpg-pgtypes.html" title="36.6. pgtypes Library">Section 36.6</a>.220    </p><div class="sect4" id="ECPG-SPECIAL-TYPES-TIMESTAMP-DATE"><div class="titlepage"><div><div><h5 class="title">36.4.4.2.1. timestamp, date <a href="#ECPG-SPECIAL-TYPES-TIMESTAMP-DATE" class="id_link">#</a></h5></div></div></div><p>221      Here is a pattern for handling <code class="type">timestamp</code> variables222      in the ECPG host application.223     </p><p>224      First, the program has to include the header file for the225      <code class="type">timestamp</code> type:226</p><pre class="programlisting">227#include &lt;pgtypes_timestamp.h&gt;228</pre><p>229     </p><p>230      Next, declare a host variable as type <code class="type">timestamp</code> in231      the declare section:232</p><pre class="programlisting">233EXEC SQL BEGIN DECLARE SECTION;234timestamp ts;235EXEC SQL END DECLARE SECTION;236</pre><p>237     </p><p>238      And after reading a value into the host variable, process it239      using pgtypes library functions. In following example, the240      <code class="type">timestamp</code> value is converted into text (ASCII) form241      with the <code class="function">PGTYPEStimestamp_to_asc()</code>242      function:243</p><pre class="programlisting">244EXEC SQL SELECT now()::timestamp INTO :ts;245 246printf("ts = %s\n", PGTYPEStimestamp_to_asc(ts));247</pre><p>248      This example will show some result like following:249</p><pre class="screen">250ts = 2010-06-27 18:03:56.949343251</pre><p>252     </p><p>253      In addition, the DATE type can be handled in the same way. The254      program has to include <code class="filename">pgtypes_date.h</code>, declare a host variable255      as the date type and convert a DATE value into a text form using256      <code class="function">PGTYPESdate_to_asc()</code> function. For more details about the257      pgtypes library functions, see <a class="xref" href="ecpg-pgtypes.html" title="36.6. pgtypes Library">Section 36.6</a>.258     </p></div><div class="sect4" id="ECPG-TYPE-INTERVAL"><div class="titlepage"><div><div><h5 class="title">36.4.4.2.2. interval <a href="#ECPG-TYPE-INTERVAL" class="id_link">#</a></h5></div></div></div><p>259      The handling of the <code class="type">interval</code> type is also similar260      to the <code class="type">timestamp</code> and <code class="type">date</code> types.  It261      is required, however, to allocate memory for262      an <code class="type">interval</code> type value explicitly.  In other words,263      the memory space for the variable has to be allocated in the264      heap memory, not in the stack memory.265     </p><p>266      Here is an example program:267</p><pre class="programlisting">268#include &lt;stdio.h&gt;269#include &lt;stdlib.h&gt;270#include &lt;pgtypes_interval.h&gt;271 272int273main(void)274{275EXEC SQL BEGIN DECLARE SECTION;276    interval *in;277EXEC SQL END DECLARE SECTION;278 279    EXEC SQL CONNECT TO testdb;280    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;281 282    in = PGTYPESinterval_new();283    EXEC SQL SELECT '1 min'::interval INTO :in;284    printf("interval = %s\n", PGTYPESinterval_to_asc(in));285    PGTYPESinterval_free(in);286 287    EXEC SQL COMMIT;288    EXEC SQL DISCONNECT ALL;289    return 0;290}291</pre><p>292     </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 <a href="#ECPG-TYPE-NUMERIC-DECIMAL" class="id_link">#</a></h5></div></div></div><p>293      The handling of the <code class="type">numeric</code>294      and <code class="type">decimal</code> types is similar to the295      <code class="type">interval</code> type: It requires defining a pointer,296      allocating some memory space on the heap, and accessing the297      variable using the pgtypes library functions.  For more details298      about the pgtypes library functions,299      see <a class="xref" href="ecpg-pgtypes.html" title="36.6. pgtypes Library">Section 36.6</a>.300     </p><p>301      No functions are provided specifically for302      the <code class="type">decimal</code> type.  An application has to convert it303      to a <code class="type">numeric</code> variable using a pgtypes library304      function to do further processing.305     </p><p>306      Here is an example program handling <code class="type">numeric</code>307      and <code class="type">decimal</code> type variables.308</p><pre class="programlisting">309#include &lt;stdio.h&gt;310#include &lt;stdlib.h&gt;311#include &lt;pgtypes_numeric.h&gt;312 313EXEC SQL WHENEVER SQLERROR STOP;314 315int316main(void)317{318EXEC SQL BEGIN DECLARE SECTION;319    numeric *num;320    numeric *num2;321    decimal *dec;322EXEC SQL END DECLARE SECTION;323 324    EXEC SQL CONNECT TO testdb;325    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;326 327    num = PGTYPESnumeric_new();328    dec = PGTYPESdecimal_new();329 330    EXEC SQL SELECT 12.345::numeric(4,2), 23.456::decimal(4,2) INTO :num, :dec;331 332    printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 0));333    printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 1));334    printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 2));335 336    /* Convert decimal to numeric to show a decimal value. */337    num2 = PGTYPESnumeric_new();338    PGTYPESnumeric_from_decimal(dec, num2);339 340    printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 0));341    printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 1));342    printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 2));343 344    PGTYPESnumeric_free(num2);345    PGTYPESdecimal_free(dec);346    PGTYPESnumeric_free(num);347 348    EXEC SQL COMMIT;349    EXEC SQL DISCONNECT ALL;350    return 0;351}352</pre><p>353     </p></div><div class="sect4" id="ECPG-SPECIAL-TYPES-BYTEA"><div class="titlepage"><div><div><h5 class="title">36.4.4.2.4. bytea <a href="#ECPG-SPECIAL-TYPES-BYTEA" class="id_link">#</a></h5></div></div></div><p>354      The handling of the <code class="type">bytea</code> type is similar to355      that of <code class="type">VARCHAR</code>. The definition on an array of type356      <code class="type">bytea</code> is converted into a named struct for every357      variable. A declaration like:358</p><pre class="programlisting">359bytea var[180];360</pre><p>361     is converted into:362</p><pre class="programlisting">363struct bytea_var { int len; char arr[180]; } var;364</pre><p>365      The member <code class="structfield">arr</code> hosts binary format366      data. It can also handle <code class="literal">'\0'</code> as part of367      data, unlike <code class="type">VARCHAR</code>.368      The data is converted from/to hex format and sent/received by369      ecpglib.370     </p><div class="note"><h3 class="title">Note</h3><p>371       <code class="type">bytea</code> variable can be used only when372       <a class="xref" href="runtime-config-client.html#GUC-BYTEA-OUTPUT">bytea_output</a> is set to <code class="literal">hex</code>.373      </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 <a href="#ECPG-VARIABLES-NONPRIMITIVE-C" class="id_link">#</a></h4></div></div></div><p>374     As a host variable you can also use arrays, typedefs, structs, and375     pointers.376    </p><div class="sect4" id="ECPG-VARIABLES-ARRAYS"><div class="titlepage"><div><div><h5 class="title">36.4.4.3.1. Arrays <a href="#ECPG-VARIABLES-ARRAYS" class="id_link">#</a></h5></div></div></div><p>377      There are two use cases for arrays as host variables.  The first378      is a way to store some text string in <code class="type">char[]</code>379      or <code class="type">VARCHAR[]</code>, as380      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 to381      retrieve multiple rows from a query result without using a382      cursor.  Without an array, to process a query result consisting383      of multiple rows, it is required to use a cursor and384      the <code class="command">FETCH</code> command.  But with array host385      variables, multiple rows can be received at once.  The length of386      the array has to be defined to be able to accommodate all rows,387      otherwise a buffer overflow will likely occur.388     </p><p>389      Following example scans the <code class="literal">pg_database</code>390      system table and shows all OIDs and names of the available391      databases:392</p><pre class="programlisting">393int394main(void)395{396EXEC SQL BEGIN DECLARE SECTION;397    int dbid[8];398    char dbname[8][16];399    int i;400EXEC SQL END DECLARE SECTION;401 402    memset(dbname, 0, sizeof(char)* 16 * 8);403    memset(dbid, 0, sizeof(int) * 8);404 405    EXEC SQL CONNECT TO testdb;406    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;407 408    /* Retrieve multiple rows into arrays at once. */409    EXEC SQL SELECT oid,datname INTO :dbid, :dbname FROM pg_database;410 411    for (i = 0; i &lt; 8; i++)412        printf("oid=%d, dbname=%s\n", dbid[i], dbname[i]);413 414    EXEC SQL COMMIT;415    EXEC SQL DISCONNECT ALL;416    return 0;417}418</pre><p>419 420    This example shows following result. (The exact values depend on421    local circumstances.)422</p><pre class="screen">423oid=1, dbname=template1424oid=11510, dbname=template0425oid=11511, dbname=postgres426oid=313780, dbname=testdb427oid=0, dbname=428oid=0, dbname=429oid=0, dbname=430</pre><p>431     </p></div><div class="sect4" id="ECPG-VARIABLES-STRUCT"><div class="titlepage"><div><div><h5 class="title">36.4.4.3.2. Structures <a href="#ECPG-VARIABLES-STRUCT" class="id_link">#</a></h5></div></div></div><p>432      A structure whose member names match the column names of a query433      result, can be used to retrieve multiple columns at once.  The434      structure enables handling multiple column values in a single435      host variable.436     </p><p>437      The following example retrieves OIDs, names, and sizes of the438      available databases from the <code class="literal">pg_database</code>439      system table and using440      the <code class="function">pg_database_size()</code> function.  In this441      example, a structure variable <code class="varname">dbinfo_t</code> with442      members whose names match each column in443      the <code class="literal">SELECT</code> result is used to retrieve one444      result row without putting multiple host variables in445      the <code class="literal">FETCH</code> statement.446</p><pre class="programlisting">447EXEC SQL BEGIN DECLARE SECTION;448    typedef struct449    {450       int oid;451       char datname[65];452       long long int size;453    } dbinfo_t;454 455    dbinfo_t dbval;456EXEC SQL END DECLARE SECTION;457 458    memset(&amp;dbval, 0, sizeof(dbinfo_t));459 460    EXEC SQL DECLARE cur1 CURSOR FOR SELECT oid, datname, pg_database_size(oid) AS size FROM pg_database;461    EXEC SQL OPEN cur1;462 463    /* when end of result set reached, break out of while loop */464    EXEC SQL WHENEVER NOT FOUND DO BREAK;465 466    while (1)467    {468        /* Fetch multiple columns into one structure. */469        EXEC SQL FETCH FROM cur1 INTO :dbval;470 471        /* Print members of the structure. */472        printf("oid=%d, datname=%s, size=%lld\n", dbval.oid, dbval.datname, dbval.size);473    }474 475    EXEC SQL CLOSE cur1;476</pre><p>477     </p><p>478      This example shows following result. (The exact values depend on479      local circumstances.)480</p><pre class="screen">481oid=1, datname=template1, size=4324580482oid=11510, datname=template0, size=4243460483oid=11511, datname=postgres, size=4324580484oid=313780, datname=testdb, size=8183012485</pre><p>486     </p><p>487      Structure host variables <span class="quote">“<span class="quote">absorb</span>”</span> as many columns488      as the structure as fields.  Additional columns can be assigned489      to other host variables. For example, the above program could490      also be restructured like this, with the <code class="varname">size</code>491      variable outside the structure:492</p><pre class="programlisting">493EXEC SQL BEGIN DECLARE SECTION;494    typedef struct495    {496       int oid;497       char datname[65];498    } dbinfo_t;499 500    dbinfo_t dbval;501    long long int size;502EXEC SQL END DECLARE SECTION;503 504    memset(&amp;dbval, 0, sizeof(dbinfo_t));505 506    EXEC SQL DECLARE cur1 CURSOR FOR SELECT oid, datname, pg_database_size(oid) AS size FROM pg_database;507    EXEC SQL OPEN cur1;508 509    /* when end of result set reached, break out of while loop */510    EXEC SQL WHENEVER NOT FOUND DO BREAK;511 512    while (1)513    {514        /* Fetch multiple columns into one structure. */515        EXEC SQL FETCH FROM cur1 INTO :dbval, :size;516 517        /* Print members of the structure. */518        printf("oid=%d, datname=%s, size=%lld\n", dbval.oid, dbval.datname, size);519    }520 521    EXEC SQL CLOSE cur1;522</pre><p>523     </p></div><div class="sect4" id="ECPG-VARIABLES-NONPRIMITIVE-C-TYPEDEFS"><div class="titlepage"><div><div><h5 class="title">36.4.4.3.3. Typedefs <a href="#ECPG-VARIABLES-NONPRIMITIVE-C-TYPEDEFS" class="id_link">#</a></h5></div></div></div><a id="id-1.7.5.10.7.8.5.2" class="indexterm"></a><p>524      Use the <code class="literal">typedef</code> keyword to map new types to already525      existing types.526</p><pre class="programlisting">527EXEC SQL BEGIN DECLARE SECTION;528    typedef char mychartype[40];529    typedef long serial_t;530EXEC SQL END DECLARE SECTION;531</pre><p>532      Note that you could also use:533</p><pre class="programlisting">534EXEC SQL TYPE serial_t IS long;535</pre><p>536      This declaration does not need to be part of a declare section;537      that is, you can also write typedefs as normal C statements.538     </p><p>539      Any word you declare as a typedef cannot be used as an SQL keyword540      in <code class="literal">EXEC SQL</code> commands later in the same program.541      For example, this won't work:542</p><pre class="programlisting">543EXEC SQL BEGIN DECLARE SECTION;544    typedef int start;545EXEC SQL END DECLARE SECTION;546...547EXEC SQL START TRANSACTION;548</pre><p>549      ECPG will report a syntax error for <code class="literal">START550      TRANSACTION</code>, because it no longer551      recognizes <code class="literal">START</code> as an SQL keyword,552      only as a typedef.553      (If you have such a conflict, and renaming the typedef554      seems impractical, you could write the SQL command555      using <a class="link" href="ecpg-dynamic.html" title="36.5. Dynamic SQL">dynamic SQL</a>.)556     </p><div class="note"><h3 class="title">Note</h3><p>557       In <span class="productname">PostgreSQL</span> releases before v16, use558       of SQL keywords as typedef names was likely to result in syntax559       errors associated with use of the typedef itself, rather than use560       of the name as an SQL keyword.  The new behavior is less likely to561       cause problems when an existing ECPG application is recompiled in562       a new <span class="productname">PostgreSQL</span> release with new563       keywords.564      </p></div></div><div class="sect4" id="ECPG-VARIABLES-NONPRIMITIVE-C-POINTERS"><div class="titlepage"><div><div><h5 class="title">36.4.4.3.4. Pointers <a href="#ECPG-VARIABLES-NONPRIMITIVE-C-POINTERS" class="id_link">#</a></h5></div></div></div><p>565      You can declare pointers to the most common types. Note however566      that you cannot use pointers as target variables of queries567      without auto-allocation. See <a class="xref" href="ecpg-descriptors.html" title="36.7. Using Descriptor Areas">Section 36.7</a>568      for more information on auto-allocation.569     </p><p>570</p><pre class="programlisting">571EXEC SQL BEGIN DECLARE SECTION;572    int   *intp;573    char **charp;574EXEC SQL END DECLARE SECTION;575</pre><p>576     </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 <a href="#ECPG-VARIABLES-NONPRIMITIVE-SQL" class="id_link">#</a></h3></div></div></div><p>577    This section contains information on how to handle nonscalar and578    user-defined SQL-level data types in ECPG applications.  Note that579    this is distinct from the handling of host variables of580    nonprimitive types, described in the previous section.581   </p><div class="sect3" id="ECPG-VARIABLES-NONPRIMITIVE-SQL-ARRAYS"><div class="titlepage"><div><div><h4 class="title">36.4.5.1. Arrays <a href="#ECPG-VARIABLES-NONPRIMITIVE-SQL-ARRAYS" class="id_link">#</a></h4></div></div></div><p>582     Multi-dimensional SQL-level arrays are not directly supported in ECPG.583     One-dimensional SQL-level arrays can be mapped into C array host584     variables and vice-versa.  However, when creating a statement ecpg does585     not know the types of the columns, so that it cannot check if a C array586     is input into a corresponding SQL-level array.  When processing the587     output of an SQL statement, ecpg has the necessary information and thus588     checks if both are arrays.589    </p><p>590     If a query accesses <span class="emphasis"><em>elements</em></span> of an array591     separately, then this avoids the use of arrays in ECPG.  Then, a592     host variable with a type that can be mapped to the element type593     should be used.  For example, if a column type is array of594     <code class="type">integer</code>, a host variable of type <code class="type">int</code>595     can be used.  Also if the element type is <code class="type">varchar</code>596     or <code class="type">text</code>, a host variable of type <code class="type">char[]</code>597     or <code class="type">VARCHAR[]</code> can be used.598    </p><p>599     Here is an example.  Assume the following table:600</p><pre class="programlisting">601CREATE TABLE t3 (602    ii integer[]603);604 605testdb=&gt; SELECT * FROM t3;606     ii607-------------608 {1,2,3,4,5}609(1 row)610</pre><p>611 612     The following example program retrieves the 4th element of the613     array and stores it into a host variable of614     type <code class="type">int</code>:615</p><pre class="programlisting">616EXEC SQL BEGIN DECLARE SECTION;617int ii;618EXEC SQL END DECLARE SECTION;619 620EXEC SQL DECLARE cur1 CURSOR FOR SELECT ii[4] FROM t3;621EXEC SQL OPEN cur1;622 623EXEC SQL WHENEVER NOT FOUND DO BREAK;624 625while (1)626{627    EXEC SQL FETCH FROM cur1 INTO :ii ;628    printf("ii=%d\n", ii);629}630 631EXEC SQL CLOSE cur1;632</pre><p>633 634     This example shows the following result:635</p><pre class="screen">636ii=4637</pre><p>638    </p><p>639     To map multiple array elements to the multiple elements in an640     array type host variables each element of array column and each641     element of the host variable array have to be managed separately,642     for example:643</p><pre class="programlisting">644EXEC SQL BEGIN DECLARE SECTION;645int ii_a[8];646EXEC SQL END DECLARE SECTION;647 648EXEC SQL DECLARE cur1 CURSOR FOR SELECT ii[1], ii[2], ii[3], ii[4] FROM t3;649EXEC SQL OPEN cur1;650 651EXEC SQL WHENEVER NOT FOUND DO BREAK;652 653while (1)654{655    EXEC SQL FETCH FROM cur1 INTO :ii_a[0], :ii_a[1], :ii_a[2], :ii_a[3];656    ...657}658</pre><p>659    </p><p>660     Note again that661</p><pre class="programlisting">662EXEC SQL BEGIN DECLARE SECTION;663int ii_a[8];664EXEC SQL END DECLARE SECTION;665 666EXEC SQL DECLARE cur1 CURSOR FOR SELECT ii FROM t3;667EXEC SQL OPEN cur1;668 669EXEC SQL WHENEVER NOT FOUND DO BREAK;670 671while (1)672{673    /* WRONG */674    EXEC SQL FETCH FROM cur1 INTO :ii_a;675    ...676}677</pre><p>678     would not work correctly in this case, because you cannot map an679     array type column to an array host variable directly.680    </p><p>681     Another workaround is to store arrays in their external string682     representation in host variables of type <code class="type">char[]</code>683     or <code class="type">VARCHAR[]</code>.  For more details about this684     representation, see <a class="xref" href="arrays.html#ARRAYS-INPUT" title="8.15.2. Array Value Input">Section 8.15.2</a>.  Note that685     this means that the array cannot be accessed naturally as an686     array in the host program (without further processing that parses687     the text representation).688    </p></div><div class="sect3" id="ECPG-VARIABLES-NONPRIMITIVE-SQL-COMPOSITE"><div class="titlepage"><div><div><h4 class="title">36.4.5.2. Composite Types <a href="#ECPG-VARIABLES-NONPRIMITIVE-SQL-COMPOSITE" class="id_link">#</a></h4></div></div></div><p>689     Composite types are not directly supported in ECPG, but an easy workaround is possible.690  The691     available workarounds are similar to the ones described for692     arrays above: Either access each attribute separately or use the693     external string representation.694    </p><p>695     For the following examples, assume the following type and table:696</p><pre class="programlisting">697CREATE TYPE comp_t AS (intval integer, textval varchar(32));698CREATE TABLE t4 (compval comp_t);699INSERT INTO t4 VALUES ( (256, 'PostgreSQL') );700</pre><p>701 702     The most obvious solution is to access each attribute separately.703     The following program retrieves data from the example table by704     selecting each attribute of the type <code class="type">comp_t</code>705     separately:706</p><pre class="programlisting">707EXEC SQL BEGIN DECLARE SECTION;708int intval;709varchar textval[33];710EXEC SQL END DECLARE SECTION;711 712/* Put each element of the composite type column in the SELECT list. */713EXEC SQL DECLARE cur1 CURSOR FOR SELECT (compval).intval, (compval).textval FROM t4;714EXEC SQL OPEN cur1;715 716EXEC SQL WHENEVER NOT FOUND DO BREAK;717 718while (1)719{720    /* Fetch each element of the composite type column into host variables. */721    EXEC SQL FETCH FROM cur1 INTO :intval, :textval;722 723    printf("intval=%d, textval=%s\n", intval, textval.arr);724}725 726EXEC SQL CLOSE cur1;727</pre><p>728    </p><p>729     To enhance this example, the host variables to store values in730     the <code class="command">FETCH</code> command can be gathered into one731     structure.  For more details about the host variable in the732     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>.733     To switch to the structure, the example can be modified as below.734     The two host variables, <code class="varname">intval</code>735     and <code class="varname">textval</code>, become members of736     the <code class="structname">comp_t</code> structure, and the structure737     is specified on the <code class="command">FETCH</code> command.738</p><pre class="programlisting">739EXEC SQL BEGIN DECLARE SECTION;740typedef struct741{742    int intval;743    varchar textval[33];744} comp_t;745 746comp_t compval;747EXEC SQL END DECLARE SECTION;748 749/* Put each element of the composite type column in the SELECT list. */750EXEC SQL DECLARE cur1 CURSOR FOR SELECT (compval).intval, (compval).textval FROM t4;751EXEC SQL OPEN cur1;752 753EXEC SQL WHENEVER NOT FOUND DO BREAK;754 755while (1)756{757    /* Put all values in the SELECT list into one structure. */758    EXEC SQL FETCH FROM cur1 INTO :compval;759 760    printf("intval=%d, textval=%s\n", compval.intval, compval.textval.arr);761}762 763EXEC SQL CLOSE cur1;764</pre><p>765 766     Although a structure is used in the <code class="command">FETCH</code>767     command, the attribute names in the <code class="command">SELECT</code>768     clause are specified one by one.  This can be enhanced by using769     a <code class="literal">*</code> to ask for all attributes of the composite770     type value.771</p><pre class="programlisting">772...773EXEC SQL DECLARE cur1 CURSOR FOR SELECT (compval).* FROM t4;774EXEC SQL OPEN cur1;775 776EXEC SQL WHENEVER NOT FOUND DO BREAK;777 778while (1)779{780    /* Put all values in the SELECT list into one structure. */781    EXEC SQL FETCH FROM cur1 INTO :compval;782 783    printf("intval=%d, textval=%s\n", compval.intval, compval.textval.arr);784}785...786</pre><p>787     This way, composite types can be mapped into structures almost788     seamlessly, even though ECPG does not understand the composite789     type itself.790    </p><p>791     Finally, it is also possible to store composite type values in792     their external string representation in host variables of793     type <code class="type">char[]</code> or <code class="type">VARCHAR[]</code>.  But that794     way, it is not easily possible to access the fields of the value795     from the host program.796    </p></div><div class="sect3" id="ECPG-VARIABLES-NONPRIMITIVE-SQL-USER-DEFINED-BASE-TYPES"><div class="titlepage"><div><div><h4 class="title">36.4.5.3. User-Defined Base Types <a href="#ECPG-VARIABLES-NONPRIMITIVE-SQL-USER-DEFINED-BASE-TYPES" class="id_link">#</a></h4></div></div></div><p>797     New user-defined base types are not directly supported by ECPG.798     You can use the external string representation and host variables799     of type <code class="type">char[]</code> or <code class="type">VARCHAR[]</code>, and this800     solution is indeed appropriate and sufficient for many types.801    </p><p>802     Here is an example using the data type <code class="type">complex</code> from803     the example in <a class="xref" href="xtypes.html" title="38.13. User-Defined Types">Section 38.13</a>.  The external string804     representation of that type is <code class="literal">(%f,%f)</code>,805     which is defined in the806     functions <code class="function">complex_in()</code>807     and <code class="function">complex_out()</code> functions808     in <a class="xref" href="xtypes.html" title="38.13. User-Defined Types">Section 38.13</a>.  The following example inserts the809     complex type values <code class="literal">(1,1)</code>810     and <code class="literal">(3,3)</code> into the811     columns <code class="literal">a</code> and <code class="literal">b</code>, and select812     them from the table after that.813 814</p><pre class="programlisting">815EXEC SQL BEGIN DECLARE SECTION;816    varchar a[64];817    varchar b[64];818EXEC SQL END DECLARE SECTION;819 820    EXEC SQL INSERT INTO test_complex VALUES ('(1,1)', '(3,3)');821 822    EXEC SQL DECLARE cur1 CURSOR FOR SELECT a, b FROM test_complex;823    EXEC SQL OPEN cur1;824 825    EXEC SQL WHENEVER NOT FOUND DO BREAK;826 827    while (1)828    {829        EXEC SQL FETCH FROM cur1 INTO :a, :b;830        printf("a=%s, b=%s\n", a.arr, b.arr);831    }832 833    EXEC SQL CLOSE cur1;834</pre><p>835 836     This example shows following result:837</p><pre class="screen">838a=(1,1), b=(3,3)839</pre><p>840   </p><p>841     Another workaround is avoiding the direct use of the user-defined842     types in ECPG and instead create a function or cast that converts843     between the user-defined type and a primitive type that ECPG can844     handle.  Note, however, that type casts, especially implicit845     ones, should be introduced into the type system very carefully.846    </p><p>847     For example,848</p><pre class="programlisting">849CREATE FUNCTION create_complex(r double, i double) RETURNS complex850LANGUAGE SQL851IMMUTABLE852AS $$ SELECT $1 * complex '(1,0')' + $2 * complex '(0,1)' $$;853</pre><p>854    After this definition, the following855</p><pre class="programlisting">856EXEC SQL BEGIN DECLARE SECTION;857double a, b, c, d;858EXEC SQL END DECLARE SECTION;859 860a = 1;861b = 2;862c = 3;863d = 4;864 865EXEC SQL INSERT INTO test_complex VALUES (create_complex(:a, :b), create_complex(:c, :d));866</pre><p>867    has the same effect as868</p><pre class="programlisting">869EXEC SQL INSERT INTO test_complex VALUES ('(1,2)', '(3,4)');870</pre><p>871    </p></div></div><div class="sect2" id="ECPG-INDICATORS"><div class="titlepage"><div><div><h3 class="title">36.4.6. Indicators <a href="#ECPG-INDICATORS" class="id_link">#</a></h3></div></div></div><p>872    The examples above do not handle null values.  In fact, the873    retrieval examples will raise an error if they fetch a null value874    from the database.  To be able to pass null values to the database875    or retrieve null values from the database, you need to append a876    second host variable specification to each host variable that877    contains data.  This second host variable is called the878    <em class="firstterm">indicator</em> and contains a flag that tells879    whether the datum is null, in which case the value of the real880    host variable is ignored.  Here is an example that handles the881    retrieval of null values correctly:882</p><pre class="programlisting">883EXEC SQL BEGIN DECLARE SECTION;884VARCHAR val;885int val_ind;886EXEC SQL END DECLARE SECTION:887 888 ...889 890EXEC SQL SELECT b INTO :val :val_ind FROM test1;891</pre><p>892    The indicator variable <code class="varname">val_ind</code> will be zero if893    the value was not null, and it will be negative if the value was894    null.  (See <a class="xref" href="ecpg-oracle-compat.html" title="36.16. Oracle Compatibility Mode">Section 36.16</a> to enable895    Oracle-specific behavior.)896   </p><p>897    The indicator has another function: if the indicator value is898    positive, it means that the value is not null, but it was899    truncated when it was stored in the host variable.900   </p><p>901    If the argument <code class="literal">-r no_indicator</code> is passed to902    the preprocessor <code class="command">ecpg</code>, it works in903    <span class="quote">“<span class="quote">no-indicator</span>”</span> mode. In no-indicator mode, if no904    indicator variable is specified, null values are signaled (on905    input and output) for character string types as empty string and906    for integer types as the lowest possible value for type (for907    example, <code class="symbol">INT_MIN</code> for <code class="type">int</code>).908   </p></div></div><div class="navfooter"><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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 36.5. Dynamic SQL</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai