codekingpro/portable-devtools
115k
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 <pgtypes_timestamp.h>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 <stdio.h>269#include <stdlib.h>270#include <pgtypes_interval.h>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 <stdio.h>310#include <stdlib.h>311#include <pgtypes_numeric.h>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 < 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(&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(&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=> 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>