Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
Obserwuj 1
Polub 0
Wiki 捐赠
gooderp18绿色标准版
Nie możesz wybrać więcej, niż 25 tematów Tematy muszą się zaczynać od litery lub cyfry, mogą zawierać myślniki ('-') i mogą mieć do 35 znaków.
Gałąź: master
Gałęzie Tagi
${ item.name }
Utwórz gałąź ${ searchTerm }
z 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/ecpg-variables.html

880 lines
44KB
Czysty Bezpośredni odnośnik Wina Historia 

  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>35.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 V1.79.1" /><link rel="prev" href="ecpg-commands.html" title="35.3. Running SQL Commands" /><link rel="next" href="ecpg-dynamic.html" title="35.5. Dynamic SQL" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">35.4. Using Host Variables</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ecpg-commands.html" title="35.3. Running SQL Commands">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="ecpg.html" title="Chapter 35. ECPG - Embedded SQL in C">Up</a></td><th width="60%" align="center">Chapter 35. <span xmlns="http://www.w3.org/1999/xhtml" class="application">ECPG</span> - Embedded <acronym xmlns="http://www.w3.org/1999/xhtml" class="acronym">SQL</acronym> in C</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="ecpg-dynamic.html" title="35.5. Dynamic SQL">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="ECPG-VARIABLES"><div class="titlepage"><div><div><h2 class="title" style="clear: both">35.4. Using Host Variables</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="ecpg-variables.html#ECPG-VARIABLES-OVERVIEW">35.4.1. Overview</a></span></dt><dt><span class="sect2"><a href="ecpg-variables.html#ECPG-DECLARE-SECTIONS">35.4.2. Declare Sections</a></span></dt><dt><span class="sect2"><a href="ecpg-variables.html#ECPG-RETRIEVING">35.4.3. Retrieving Query Results</a></span></dt><dt><span class="sect2"><a href="ecpg-variables.html#ECPG-VARIABLES-TYPE-MAPPING">35.4.4. Type Mapping</a></span></dt><dt><span class="sect2"><a href="ecpg-variables.html#ECPG-VARIABLES-NONPRIMITIVE-SQL">35.4.5. Handling Nonprimitive SQL Data Types</a></span></dt><dt><span class="sect2"><a href="ecpg-variables.html#ECPG-INDICATORS">35.4.6. Indicators</a></span></dt></dl></div><p>

  3.    In <a class="xref" href="ecpg-commands.html" title="35.3. Running SQL Commands">Section 35.3</a> you saw how you can execute SQL

  4.    statements from an embedded SQL program.  Some of those statements

  5.    only used fixed values and did not provide a way to insert

  6.    user-supplied values into statements or have the program process

  7.    the values returned by the query.  Those kinds of statements are

  8.    not really useful in real applications.  This section explains in

  9.    detail how you can pass data between your C program and the

  10.    embedded SQL statements using a simple mechanism called

  11.    <em class="firstterm">host variables</em>. In an embedded SQL program we

  12.    consider the SQL statements to be <em class="firstterm">guests</em> in the C

  13.    program code which is the <em class="firstterm">host language</em>. Therefore

  14.    the variables of the C program are called <em class="firstterm">host

  15.    variables</em>.

  16.   </p><p>

  17.    Another way to exchange values between PostgreSQL backends and ECPG

  18.    applications is the use of SQL descriptors, described

  19.    in <a class="xref" href="ecpg-descriptors.html" title="35.7. Using Descriptor Areas">Section 35.7</a>.

  20.   </p><div class="sect2" id="ECPG-VARIABLES-OVERVIEW"><div class="titlepage"><div><div><h3 class="title">35.4.1. Overview</h3></div></div></div><p>

  21.     Passing data between the C program and the SQL statements is

  22.     particularly simple in embedded SQL.  Instead of having the

  23.     program paste the data into the statement, which entails various

  24.     complications, such as properly quoting the value, you can simply

  25.     write the name of a C variable into the SQL statement, prefixed by

  26.     a colon.  For example:

  27. </p><pre class="programlisting">

  28. EXEC SQL INSERT INTO sometable VALUES (:v1, 'foo', :v2);

  29. </pre><p>

  30.     This statement refers to two C variables named

  31.     <code class="varname">v1</code> and <code class="varname">v2</code> and also uses a

  32.     regular SQL string literal, to illustrate that you are not

  33.     restricted to use one kind of data or the other.

  34.    </p><p>

  35.     This style of inserting C variables in SQL statements works

  36.     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">35.4.2. Declare Sections</h3></div></div></div><p>

  38.     To pass data from the program to the database, for example as

  39.     parameters in a query, or to pass data from the database back to

  40.     the program, the C variables that are intended to contain this

  41.     data need to be declared in specially marked sections, so the

  42.     embedded SQL preprocessor is made aware of them.

  43.    </p><p>

  44.     This section starts with:

  45. </p><pre class="programlisting">

  46. EXEC SQL BEGIN DECLARE SECTION;

  47. </pre><p>

  48.     and ends with:

  49. </p><pre class="programlisting">

  50. EXEC 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">

  55. int   x = 4;

  56. char  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 declaring

  60.     section within the program.

  61.     You can also declare variables with the following syntax which implicitly

  62.     creates a declare section:

  63. </p><pre class="programlisting">

  64. EXEC 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 C

  69.     variables, so there's no need to declare them again.  Variables

  70.     that are not intended to be used in SQL commands can be declared

  71.     normally outside these special sections.

  72.    </p><p>

  73.     The definition of a structure or union also must be listed inside

  74.     a <code class="literal">DECLARE</code> section. Otherwise the preprocessor cannot

  75.     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">35.4.3. Retrieving Query Results</h3></div></div></div><p>

  77.     Now you should be able to pass data generated by your program into

  78.     an SQL command.  But how do you retrieve the results of a query?

  79.     For that purpose, embedded SQL provides special variants of the

  80.     usual commands <code class="command">SELECT</code> and

  81.     <code class="command">FETCH</code>.  These commands have a special

  82.     <code class="literal">INTO</code> clause that specifies which host variables

  83.     the retrieved values are to be stored in.

  84.     <code class="command">SELECT</code> is used for a query that returns only

  85.     single row, and <code class="command">FETCH</code> is used for a query that

  86.     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. 

  95. EXEC SQL BEGIN DECLARE SECTION;

  96. int v1;

  97. VARCHAR v2;

  98. EXEC SQL END DECLARE SECTION;

  99. 

  100.  ...

  101. 

  102. EXEC SQL SELECT a, b INTO :v1, :v2 FROM test;

  103. </pre><p>

  104.     So the <code class="literal">INTO</code> clause appears between the select

  105.     list and the <code class="literal">FROM</code> clause.  The number of

  106.     elements in the select list and the list after

  107.     <code class="literal">INTO</code> (also called the target list) must be

  108.     equal.

  109.    </p><p>

  110.     Here is an example using the command <code class="command">FETCH</code>:

  111. </p><pre class="programlisting">

  112. EXEC SQL BEGIN DECLARE SECTION;

  113. int v1;

  114. VARCHAR v2;

  115. EXEC SQL END DECLARE SECTION;

  116. 

  117.  ...

  118. 

  119. EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test;

  120. 

  121.  ...

  122. 

  123. do

  124. {

  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 the

  131.     normal clauses.

  132.    </p></div><div class="sect2" id="ECPG-VARIABLES-TYPE-MAPPING"><div class="titlepage"><div><div><h3 class="title">35.4.4. Type Mapping</h3></div></div></div><p>

  133.     When ECPG applications exchange values between the PostgreSQL

  134.     server and the C application, such as when retrieving query

  135.     results from the server or executing SQL statements with input

  136.     parameters, the values need to be converted between PostgreSQL

  137.     data types and host language variable types (C language data

  138.     types, concretely).  One of the main points of ECPG is that it

  139.     takes care of this automatically in most cases.

  140.    </p><p>

  141.     In this respect, there are two kinds of data types: Some simple

  142.     PostgreSQL data types, such as <code class="type">integer</code>

  143.     and <code class="type">text</code>, can be read and written by the application

  144.     directly.  Other PostgreSQL data types, such

  145.     as <code class="type">timestamp</code> and <code class="type">numeric</code> can only be

  146.     accessed through special library functions; see

  147.     <a class="xref" href="ecpg-variables.html#ECPG-SPECIAL-TYPES" title="35.4.4.2. Accessing Special Data Types">Section 35.4.4.2</a>.

  148.    </p><p>

  149.     <a class="xref" href="ecpg-variables.html#ECPG-DATATYPE-HOSTVARS-TABLE" title="Table 35.1. Mapping Between PostgreSQL Data Types and C Variable Types">Table 35.1</a> shows which PostgreSQL

  150.     data types correspond to which C data types.  When you wish to

  151.     send or receive a value of a given PostgreSQL data type, you

  152.     should declare a C variable of the corresponding C data type in

  153.     the declare section.

  154.    </p><div class="table" id="ECPG-DATATYPE-HOSTVARS-TABLE"><p class="title"><strong>Table 35.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="35.4.4.2. Accessing Special Data Types">Section 35.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">35.4.4.1. Handling Character Strings</h4></div></div></div><p>

  155.      To handle SQL character string data types, such

  156.      as <code class="type">varchar</code> and <code class="type">text</code>, there are two

  157.      possible ways to declare the host variables.

  158.     </p><p>

  159.      One way is using <code class="type">char[]</code>, an array

  160.      of <code class="type">char</code>, which is the most common way to handle

  161.      character data in C.

  162. </p><pre class="programlisting">

  163. EXEC SQL BEGIN DECLARE SECTION;

  164.     char str[50];

  165. EXEC SQL END DECLARE SECTION;

  166. </pre><p>

  167.      Note that you have to take care of the length yourself.  If you

  168.      use this host variable as the target variable of a query which

  169.      returns a string with more than 49 characters, a buffer overflow

  170.      occurs.

  171.     </p><p>

  172.      The other way is using the <code class="type">VARCHAR</code> type, which is a

  173.      special type provided by ECPG.  The definition on an array of

  174.      type <code class="type">VARCHAR</code> is converted into a

  175.      named <code class="type">struct</code> for every variable. A declaration like:

  176. </p><pre class="programlisting">

  177. VARCHAR var[180];

  178. </pre><p>

  179.      is converted into:

  180. </p><pre class="programlisting">

  181. struct varchar_var { int len; char arr[180]; } var;

  182. </pre><p>

  183.      The member <code class="structfield">arr</code> hosts the string

  184.      including a terminating zero byte.  Thus, to store a string in

  185.      a <code class="type">VARCHAR</code> host variable, the host variable has to be

  186.      declared with the length including the zero byte terminator.  The

  187.      member <code class="structfield">len</code> holds the length of the

  188.      string stored in the <code class="structfield">arr</code> without the

  189.      terminating zero byte.  When a host variable is used as input for

  190.      a query, if <code class="literal">strlen(arr)</code>

  191.      and <code class="structfield">len</code> are different, the shorter one

  192.      is used.

  193.     </p><p>

  194.      <code class="type">VARCHAR</code> can be written in upper or lower case, but

  195.      not in mixed case.

  196.     </p><p>

  197.      <code class="type">char</code> and <code class="type">VARCHAR</code> host variables can

  198.      also hold values of other SQL types, which will be stored in

  199.      their string forms.

  200.     </p></div><div class="sect3" id="ECPG-SPECIAL-TYPES"><div class="titlepage"><div><div><h4 class="title">35.4.4.2. Accessing Special Data Types</h4></div></div></div><p>

  201.      ECPG contains some special types that help you to interact easily

  202.      with some special data types from the PostgreSQL server. In

  203.      particular, it has implemented support for the

  204.      <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 be

  206.      mapped to primitive host variable types (such

  207.      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 internal

  209.      structure.  Applications deal with these types by declaring host

  210.      variables in special types and accessing them using functions in

  211.      the pgtypes library.  The pgtypes library, described in detail

  212.      in <a class="xref" href="ecpg-pgtypes.html" title="35.6. pgtypes Library">Section 35.6</a> contains basic functions to deal

  213.      with those types, such that you do not need to send a query to

  214.      the SQL server just for adding an interval to a time stamp for

  215.      example.

  216.     </p><p>

  217.      The follow subsections describe these special data types. For

  218.      more details about pgtypes library functions,

  219.      see <a class="xref" href="ecpg-pgtypes.html" title="35.6. pgtypes Library">Section 35.6</a>.

  220.     </p><div class="sect4" id="id-1.7.5.10.7.7.4"><div class="titlepage"><div><div><h5 class="title">35.4.4.2.1. timestamp, date</h5></div></div></div><p>

  221.       Here is a pattern for handling <code class="type">timestamp</code> variables

  222.       in the ECPG host application.

  223.      </p><p>

  224.       First, the program has to include the header file for the

  225.       <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> in

  231.       the declare section:

  232. </p><pre class="programlisting">

  233. EXEC SQL BEGIN DECLARE SECTION;

  234. timestamp ts;

  235. EXEC SQL END DECLARE SECTION;

  236. </pre><p>

  237.      </p><p>

  238.       And after reading a value into the host variable, process it

  239.       using pgtypes library functions. In following example, the

  240.       <code class="type">timestamp</code> value is converted into text (ASCII) form

  241.       with the <code class="function">PGTYPEStimestamp_to_asc()</code>

  242.       function:

  243. </p><pre class="programlisting">

  244. EXEC SQL SELECT now()::timestamp INTO :ts;

  245. 

  246. printf("ts = %s\n", PGTYPEStimestamp_to_asc(ts));

  247. </pre><p>

  248.       This example will show some result like following:

  249. </p><pre class="screen">

  250. ts = 2010-06-27 18:03:56.949343

  251. </pre><p>

  252.      </p><p>

  253.       In addition, the DATE type can be handled in the same way. The

  254.       program has to include <code class="filename">pgtypes_date.h</code>, declare a host variable

  255.       as the date type and convert a DATE value into a text form using

  256.       <code class="function">PGTYPESdate_to_asc()</code> function. For more details about the

  257.       pgtypes library functions, see <a class="xref" href="ecpg-pgtypes.html" title="35.6. pgtypes Library">Section 35.6</a>.

  258.      </p></div><div class="sect4" id="ECPG-TYPE-INTERVAL"><div class="titlepage"><div><div><h5 class="title">35.4.4.2.2. interval</h5></div></div></div><p>

  259.       The handling of the <code class="type">interval</code> type is also similar

  260.       to the <code class="type">timestamp</code> and <code class="type">date</code> types.  It

  261.       is required, however, to allocate memory for

  262.       an <code class="type">interval</code> type value explicitly.  In other words,

  263.       the memory space for the variable has to be allocated in the

  264.       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. 

  272. int

  273. main(void)

  274. {

  275. EXEC SQL BEGIN DECLARE SECTION;

  276.     interval *in;

  277. EXEC 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">35.4.4.2.3. numeric, decimal</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 the

  295.       <code class="type">interval</code> type: It requires defining a pointer,

  296.       allocating some memory space on the heap, and accessing the

  297.       variable using the pgtypes library functions.  For more details

  298.       about the pgtypes library functions,

  299.       see <a class="xref" href="ecpg-pgtypes.html" title="35.6. pgtypes Library">Section 35.6</a>.

  300.      </p><p>

  301.       No functions are provided specifically for

  302.       the <code class="type">decimal</code> type.  An application has to convert it

  303.       to a <code class="type">numeric</code> variable using a pgtypes library

  304.       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. 

  313. EXEC SQL WHENEVER SQLERROR STOP;

  314. 

  315. int

  316. main(void)

  317. {

  318. EXEC SQL BEGIN DECLARE SECTION;

  319.     numeric *num;

  320.     numeric *num2;

  321.     decimal *dec;

  322. EXEC 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="id-1.7.5.10.7.7.7"><div class="titlepage"><div><div><h5 class="title">35.4.4.2.4. bytea</h5></div></div></div><p>

  354.       The handling of the <code class="type">bytea</code> type is similar to

  355.       that of <code class="type">VARCHAR</code>. The definition on an array of type

  356.       <code class="type">bytea</code> is converted into a named struct for every

  357.       variable. A declaration like:

  358. </p><pre class="programlisting">

  359. bytea var[180];

  360. </pre><p>

  361.      is converted into:

  362. </p><pre class="programlisting">

  363. struct bytea_var { int len; char arr[180]; } var;

  364. </pre><p>

  365.       The member <code class="structfield">arr</code> hosts binary format

  366.       data. It can also handle <code class="literal">'\0'</code> as part of

  367.       data, unlike <code class="type">VARCHAR</code>.

  368.       The data is converted from/to hex format and sent/received by

  369.       ecpglib.

  370.      </p><div class="note"><h3 class="title">Note</h3><p>

  371.        <code class="type">bytea</code> variable can be used only when

  372.        <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">35.4.4.3. Host Variables with Nonprimitive Types</h4></div></div></div><p>

  374.      As a host variable you can also use arrays, typedefs, structs, and

  375.      pointers.

  376.     </p><div class="sect4" id="ECPG-VARIABLES-ARRAYS"><div class="titlepage"><div><div><h5 class="title">35.4.4.3.1. Arrays</h5></div></div></div><p>

  377.       There are two use cases for arrays as host variables.  The first

  378.       is a way to store some text string in <code class="type">char[]</code>

  379.       or <code class="type">VARCHAR[]</code>, as

  380.       explained in <a class="xref" href="ecpg-variables.html#ECPG-CHAR" title="35.4.4.1. Handling Character Strings">Section 35.4.4.1</a>.  The second use case is to

  381.       retrieve multiple rows from a query result without using a

  382.       cursor.  Without an array, to process a query result consisting

  383.       of multiple rows, it is required to use a cursor and

  384.       the <code class="command">FETCH</code> command.  But with array host

  385.       variables, multiple rows can be received at once.  The length of

  386.       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 available

  391.       databases:

  392. </p><pre class="programlisting">

  393. int

  394. main(void)

  395. {

  396. EXEC SQL BEGIN DECLARE SECTION;

  397.     int dbid[8];

  398.     char dbname[8][16];

  399.     int i;

  400. EXEC 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 on

  421.     local circumstances.)

  422. </p><pre class="screen">

  423. oid=1, dbname=template1

  424. oid=11510, dbname=template0

  425. oid=11511, dbname=postgres

  426. oid=313780, dbname=testdb

  427. oid=0, dbname=

  428. oid=0, dbname=

  429. oid=0, dbname=

  430. </pre><p>

  431.      </p></div><div class="sect4" id="ECPG-VARIABLES-STRUCT"><div class="titlepage"><div><div><h5 class="title">35.4.4.3.2. Structures</h5></div></div></div><p>

  432.       A structure whose member names match the column names of a query

  433.       result, can be used to retrieve multiple columns at once.  The

  434.       structure enables handling multiple column values in a single

  435.       host variable.

  436.      </p><p>

  437.       The following example retrieves OIDs, names, and sizes of the

  438.       available databases from the <code class="literal">pg_database</code>

  439.       system table and using

  440.       the <code class="function">pg_database_size()</code> function.  In this

  441.       example, a structure variable <code class="varname">dbinfo_t</code> with

  442.       members whose names match each column in

  443.       the <code class="literal">SELECT</code> result is used to retrieve one

  444.       result row without putting multiple host variables in

  445.       the <code class="literal">FETCH</code> statement.

  446. </p><pre class="programlisting">

  447. EXEC SQL BEGIN DECLARE SECTION;

  448.     typedef struct

  449.     {

  450.        int oid;

  451.        char datname[65];

  452.        long long int size;

  453.     } dbinfo_t;

  454. 

  455.     dbinfo_t dbval;

  456. EXEC 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 on

  479.       local circumstances.)

  480. </p><pre class="screen">

  481. oid=1, datname=template1, size=4324580

  482. oid=11510, datname=template0, size=4243460

  483. oid=11511, datname=postgres, size=4324580

  484. oid=313780, datname=testdb, size=8183012

  485. </pre><p>

  486.      </p><p>

  487.       Structure host variables <span class="quote">“<span class="quote">absorb</span>”</span> as many columns

  488.       as the structure as fields.  Additional columns can be assigned

  489.       to other host variables. For example, the above program could

  490.       also be restructured like this, with the <code class="varname">size</code>

  491.       variable outside the structure:

  492. </p><pre class="programlisting">

  493. EXEC SQL BEGIN DECLARE SECTION;

  494.     typedef struct

  495.     {

  496.        int oid;

  497.        char datname[65];

  498.     } dbinfo_t;

  499. 

  500.     dbinfo_t dbval;

  501.     long long int size;

  502. EXEC 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="id-1.7.5.10.7.8.5"><div class="titlepage"><div><div><h5 class="title">35.4.4.3.3. Typedefs</h5></div></div></div><p>

  524.       Use the <code class="literal">typedef</code> keyword to map new types to already

  525.       existing types.

  526. </p><pre class="programlisting">

  527. EXEC SQL BEGIN DECLARE SECTION;

  528.     typedef char mychartype[40];

  529.     typedef long serial_t;

  530. EXEC SQL END DECLARE SECTION;

  531. </pre><p>

  532.       Note that you could also use:

  533. </p><pre class="programlisting">

  534. EXEC SQL TYPE serial_t IS long;

  535. </pre><p>

  536.       This declaration does not need to be part of a declare section.

  537.      </p></div><div class="sect4" id="id-1.7.5.10.7.8.6"><div class="titlepage"><div><div><h5 class="title">35.4.4.3.4. Pointers</h5></div></div></div><p>

  538.       You can declare pointers to the most common types. Note however

  539.       that you cannot use pointers as target variables of queries

  540.       without auto-allocation. See <a class="xref" href="ecpg-descriptors.html" title="35.7. Using Descriptor Areas">Section 35.7</a>

  541.       for more information on auto-allocation.

  542.      </p><p>

  543. </p><pre class="programlisting">

  544. EXEC SQL BEGIN DECLARE SECTION;

  545.     int   *intp;

  546.     char **charp;

  547. EXEC SQL END DECLARE SECTION;

  548. </pre><p>

  549.      </p></div></div></div><div class="sect2" id="ECPG-VARIABLES-NONPRIMITIVE-SQL"><div class="titlepage"><div><div><h3 class="title">35.4.5. Handling Nonprimitive SQL Data Types</h3></div></div></div><p>

  550.     This section contains information on how to handle nonscalar and

  551.     user-defined SQL-level data types in ECPG applications.  Note that

  552.     this is distinct from the handling of host variables of

  553.     nonprimitive types, described in the previous section.

  554.    </p><div class="sect3" id="id-1.7.5.10.8.3"><div class="titlepage"><div><div><h4 class="title">35.4.5.1. Arrays</h4></div></div></div><p>

  555.      Multi-dimensional SQL-level arrays are not directly supported in ECPG.

  556.      One-dimensional SQL-level arrays can be mapped into C array host

  557.      variables and vice-versa.  However, when creating a statement ecpg does

  558.      not know the types of the columns, so that it cannot check if a C array

  559.      is input into a corresponding SQL-level array.  When processing the

  560.      output of a SQL statement, ecpg has the necessary information and thus

  561.      checks if both are arrays.

  562.     </p><p>

  563.      If a query accesses <span class="emphasis"><em>elements</em></span> of an array

  564.      separately, then this avoids the use of arrays in ECPG.  Then, a

  565.      host variable with a type that can be mapped to the element type

  566.      should be used.  For example, if a column type is array of

  567.      <code class="type">integer</code>, a host variable of type <code class="type">int</code>

  568.      can be used.  Also if the element type is <code class="type">varchar</code>

  569.      or <code class="type">text</code>, a host variable of type <code class="type">char[]</code>

  570.      or <code class="type">VARCHAR[]</code> can be used.

  571.     </p><p>

  572.      Here is an example.  Assume the following table:

  573. </p><pre class="programlisting">

  574. CREATE TABLE t3 (

  575.     ii integer[]

  576. );

  577. 

  578. testdb=&gt; SELECT * FROM t3;

  579.      ii

  580. -------------

  581.  {1,2,3,4,5}

  582. (1 row)

  583. </pre><p>

  584. 

  585.      The following example program retrieves the 4th element of the

  586.      array and stores it into a host variable of

  587.      type <code class="type">int</code>:

  588. </p><pre class="programlisting">

  589. EXEC SQL BEGIN DECLARE SECTION;

  590. int ii;

  591. EXEC SQL END DECLARE SECTION;

  592. 

  593. EXEC SQL DECLARE cur1 CURSOR FOR SELECT ii[4] FROM t3;

  594. EXEC SQL OPEN cur1;

  595. 

  596. EXEC SQL WHENEVER NOT FOUND DO BREAK;

  597. 

  598. while (1)

  599. {

  600.     EXEC SQL FETCH FROM cur1 INTO :ii ;

  601.     printf("ii=%d\n", ii);

  602. }

  603. 

  604. EXEC SQL CLOSE cur1;

  605. </pre><p>

  606. 

  607.      This example shows the following result:

  608. </p><pre class="screen">

  609. ii=4

  610. </pre><p>

  611.     </p><p>

  612.      To map multiple array elements to the multiple elements in an

  613.      array type host variables each element of array column and each

  614.      element of the host variable array have to be managed separately,

  615.      for example:

  616. </p><pre class="programlisting">

  617. EXEC SQL BEGIN DECLARE SECTION;

  618. int ii_a[8];

  619. EXEC SQL END DECLARE SECTION;

  620. 

  621. EXEC SQL DECLARE cur1 CURSOR FOR SELECT ii[1], ii[2], ii[3], ii[4] FROM t3;

  622. EXEC SQL OPEN cur1;

  623. 

  624. EXEC SQL WHENEVER NOT FOUND DO BREAK;

  625. 

  626. while (1)

  627. {

  628.     EXEC SQL FETCH FROM cur1 INTO :ii_a[0], :ii_a[1], :ii_a[2], :ii_a[3];

  629.     ...

  630. }

  631. </pre><p>

  632.     </p><p>

  633.      Note again that

  634. </p><pre class="programlisting">

  635. EXEC SQL BEGIN DECLARE SECTION;

  636. int ii_a[8];

  637. EXEC SQL END DECLARE SECTION;

  638. 

  639. EXEC SQL DECLARE cur1 CURSOR FOR SELECT ii FROM t3;

  640. EXEC SQL OPEN cur1;

  641. 

  642. EXEC SQL WHENEVER NOT FOUND DO BREAK;

  643. 

  644. while (1)

  645. {

  646.     /* WRONG */

  647.     EXEC SQL FETCH FROM cur1 INTO :ii_a;

  648.     ...

  649. }

  650. </pre><p>

  651.      would not work correctly in this case, because you cannot map an

  652.      array type column to an array host variable directly.

  653.     </p><p>

  654.      Another workaround is to store arrays in their external string

  655.      representation in host variables of type <code class="type">char[]</code>

  656.      or <code class="type">VARCHAR[]</code>.  For more details about this

  657.      representation, see <a class="xref" href="arrays.html#ARRAYS-INPUT" title="8.15.2. Array Value Input">Section 8.15.2</a>.  Note that

  658.      this means that the array cannot be accessed naturally as an

  659.      array in the host program (without further processing that parses

  660.      the text representation).

  661.     </p></div><div class="sect3" id="id-1.7.5.10.8.4"><div class="titlepage"><div><div><h4 class="title">35.4.5.2. Composite Types</h4></div></div></div><p>

  662.      Composite types are not directly supported in ECPG, but an easy workaround is possible.

  663.   The

  664.      available workarounds are similar to the ones described for

  665.      arrays above: Either access each attribute separately or use the

  666.      external string representation.

  667.     </p><p>

  668.      For the following examples, assume the following type and table:

  669. </p><pre class="programlisting">

  670. CREATE TYPE comp_t AS (intval integer, textval varchar(32));

  671. CREATE TABLE t4 (compval comp_t);

  672. INSERT INTO t4 VALUES ( (256, 'PostgreSQL') );

  673. </pre><p>

  674. 

  675.      The most obvious solution is to access each attribute separately.

  676.      The following program retrieves data from the example table by

  677.      selecting each attribute of the type <code class="type">comp_t</code>

  678.      separately:

  679. </p><pre class="programlisting">

  680. EXEC SQL BEGIN DECLARE SECTION;

  681. int intval;

  682. varchar textval[33];

  683. EXEC SQL END DECLARE SECTION;

  684. 

  685. /* Put each element of the composite type column in the SELECT list. */

  686. EXEC SQL DECLARE cur1 CURSOR FOR SELECT (compval).intval, (compval).textval FROM t4;

  687. EXEC SQL OPEN cur1;

  688. 

  689. EXEC SQL WHENEVER NOT FOUND DO BREAK;

  690. 

  691. while (1)

  692. {

  693.     /* Fetch each element of the composite type column into host variables. */

  694.     EXEC SQL FETCH FROM cur1 INTO :intval, :textval;

  695. 

  696.     printf("intval=%d, textval=%s\n", intval, textval.arr);

  697. }

  698. 

  699. EXEC SQL CLOSE cur1;

  700. </pre><p>

  701.     </p><p>

  702.      To enhance this example, the host variables to store values in

  703.      the <code class="command">FETCH</code> command can be gathered into one

  704.      structure.  For more details about the host variable in the

  705.      structure form, see <a class="xref" href="ecpg-variables.html#ECPG-VARIABLES-STRUCT" title="35.4.4.3.2. Structures">Section 35.4.4.3.2</a>.

  706.      To switch to the structure, the example can be modified as below.

  707.      The two host variables, <code class="varname">intval</code>

  708.      and <code class="varname">textval</code>, become members of

  709.      the <code class="structname">comp_t</code> structure, and the structure

  710.      is specified on the <code class="command">FETCH</code> command.

  711. </p><pre class="programlisting">

  712. EXEC SQL BEGIN DECLARE SECTION;

  713. typedef struct

  714. {

  715.     int intval;

  716.     varchar textval[33];

  717. } comp_t;

  718. 

  719. comp_t compval;

  720. EXEC SQL END DECLARE SECTION;

  721. 

  722. /* Put each element of the composite type column in the SELECT list. */

  723. EXEC SQL DECLARE cur1 CURSOR FOR SELECT (compval).intval, (compval).textval FROM t4;

  724. EXEC SQL OPEN cur1;

  725. 

  726. EXEC SQL WHENEVER NOT FOUND DO BREAK;

  727. 

  728. while (1)

  729. {

  730.     /* Put all values in the SELECT list into one structure. */

  731.     EXEC SQL FETCH FROM cur1 INTO :compval;

  732. 

  733.     printf("intval=%d, textval=%s\n", compval.intval, compval.textval.arr);

  734. }

  735. 

  736. EXEC SQL CLOSE cur1;

  737. </pre><p>

  738. 

  739.      Although a structure is used in the <code class="command">FETCH</code>

  740.      command, the attribute names in the <code class="command">SELECT</code>

  741.      clause are specified one by one.  This can be enhanced by using

  742.      a <code class="literal">*</code> to ask for all attributes of the composite

  743.      type value.

  744. </p><pre class="programlisting">

  745. ...

  746. EXEC SQL DECLARE cur1 CURSOR FOR SELECT (compval).* FROM t4;

  747. EXEC SQL OPEN cur1;

  748. 

  749. EXEC SQL WHENEVER NOT FOUND DO BREAK;

  750. 

  751. while (1)

  752. {

  753.     /* Put all values in the SELECT list into one structure. */

  754.     EXEC SQL FETCH FROM cur1 INTO :compval;

  755. 

  756.     printf("intval=%d, textval=%s\n", compval.intval, compval.textval.arr);

  757. }

  758. ...

  759. </pre><p>

  760.      This way, composite types can be mapped into structures almost

  761.      seamlessly, even though ECPG does not understand the composite

  762.      type itself.

  763.     </p><p>

  764.      Finally, it is also possible to store composite type values in

  765.      their external string representation in host variables of

  766.      type <code class="type">char[]</code> or <code class="type">VARCHAR[]</code>.  But that

  767.      way, it is not easily possible to access the fields of the value

  768.      from the host program.

  769.     </p></div><div class="sect3" id="id-1.7.5.10.8.5"><div class="titlepage"><div><div><h4 class="title">35.4.5.3. User-Defined Base Types</h4></div></div></div><p>

  770.      New user-defined base types are not directly supported by ECPG.

  771.      You can use the external string representation and host variables

  772.      of type <code class="type">char[]</code> or <code class="type">VARCHAR[]</code>, and this

  773.      solution is indeed appropriate and sufficient for many types.

  774.     </p><p>

  775.      Here is an example using the data type <code class="type">complex</code> from

  776.      the example in <a class="xref" href="xtypes.html" title="37.13. User-Defined Types">Section 37.13</a>.  The external string

  777.      representation of that type is <code class="literal">(%f,%f)</code>,

  778.      which is defined in the

  779.      functions <code class="function">complex_in()</code>

  780.      and <code class="function">complex_out()</code> functions

  781.      in <a class="xref" href="xtypes.html" title="37.13. User-Defined Types">Section 37.13</a>.  The following example inserts the

  782.      complex type values <code class="literal">(1,1)</code>

  783.      and <code class="literal">(3,3)</code> into the

  784.      columns <code class="literal">a</code> and <code class="literal">b</code>, and select

  785.      them from the table after that.

  786. 

  787. </p><pre class="programlisting">

  788. EXEC SQL BEGIN DECLARE SECTION;

  789.     varchar a[64];

  790.     varchar b[64];

  791. EXEC SQL END DECLARE SECTION;

  792. 

  793.     EXEC SQL INSERT INTO test_complex VALUES ('(1,1)', '(3,3)');

  794. 

  795.     EXEC SQL DECLARE cur1 CURSOR FOR SELECT a, b FROM test_complex;

  796.     EXEC SQL OPEN cur1;

  797. 

  798.     EXEC SQL WHENEVER NOT FOUND DO BREAK;

  799. 

  800.     while (1)

  801.     {

  802.         EXEC SQL FETCH FROM cur1 INTO :a, :b;

  803.         printf("a=%s, b=%s\n", a.arr, b.arr);

  804.     }

  805. 

  806.     EXEC SQL CLOSE cur1;

  807. </pre><p>

  808. 

  809.      This example shows following result:

  810. </p><pre class="screen">

  811. a=(1,1), b=(3,3)

  812. </pre><p>

  813.    </p><p>

  814.      Another workaround is avoiding the direct use of the user-defined

  815.      types in ECPG and instead create a function or cast that converts

  816.      between the user-defined type and a primitive type that ECPG can

  817.      handle.  Note, however, that type casts, especially implicit

  818.      ones, should be introduced into the type system very carefully.

  819.     </p><p>

  820.      For example,

  821. </p><pre class="programlisting">

  822. CREATE FUNCTION create_complex(r double, i double) RETURNS complex

  823. LANGUAGE SQL

  824. IMMUTABLE

  825. AS $$ SELECT $1 * complex '(1,0')' + $2 * complex '(0,1)' $$;

  826. </pre><p>

  827.     After this definition, the following

  828. </p><pre class="programlisting">

  829. EXEC SQL BEGIN DECLARE SECTION;

  830. double a, b, c, d;

  831. EXEC SQL END DECLARE SECTION;

  832. 

  833. a = 1;

  834. b = 2;

  835. c = 3;

  836. d = 4;

  837. 

  838. EXEC SQL INSERT INTO test_complex VALUES (create_complex(:a, :b), create_complex(:c, :d));

  839. </pre><p>

  840.     has the same effect as

  841. </p><pre class="programlisting">

  842. EXEC SQL INSERT INTO test_complex VALUES ('(1,2)', '(3,4)');

  843. </pre><p>

  844.     </p></div></div><div class="sect2" id="ECPG-INDICATORS"><div class="titlepage"><div><div><h3 class="title">35.4.6. Indicators</h3></div></div></div><p>

  845.     The examples above do not handle null values.  In fact, the

  846.     retrieval examples will raise an error if they fetch a null value

  847.     from the database.  To be able to pass null values to the database

  848.     or retrieve null values from the database, you need to append a

  849.     second host variable specification to each host variable that

  850.     contains data.  This second host variable is called the

  851.     <em class="firstterm">indicator</em> and contains a flag that tells

  852.     whether the datum is null, in which case the value of the real

  853.     host variable is ignored.  Here is an example that handles the

  854.     retrieval of null values correctly:

  855. </p><pre class="programlisting">

  856. EXEC SQL BEGIN DECLARE SECTION;

  857. VARCHAR val;

  858. int val_ind;

  859. EXEC SQL END DECLARE SECTION:

  860. 

  861.  ...

  862. 

  863. EXEC SQL SELECT b INTO :val :val_ind FROM test1;

  864. </pre><p>

  865.     The indicator variable <code class="varname">val_ind</code> will be zero if

  866.     the value was not null, and it will be negative if the value was

  867.     null.

  868.    </p><p>

  869.     The indicator has another function: if the indicator value is

  870.     positive, it means that the value is not null, but it was

  871.     truncated when it was stored in the host variable.

  872.    </p><p>

  873.     If the argument <code class="literal">-r no_indicator</code> is passed to

  874.     the preprocessor <code class="command">ecpg</code>, it works in

  875.     <span class="quote">“<span class="quote">no-indicator</span>”</span> mode. In no-indicator mode, if no

  876.     indicator variable is specified, null values are signaled (on

  877.     input and output) for character string types as empty string and

  878.     for integer types as the lowest possible value for type (for

  879.     example, <code class="symbol">INT_MIN</code> for <code class="type">int</code>).

  880.    </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">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="ecpg.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="ecpg-dynamic.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">35.3. Running SQL Commands </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 35.5. Dynamic SQL</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1