1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>30.6.Using Host Variables</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="ecpg.html" title="Chapter30.ECPG - Embedded SQL in C">
<link rel="prev" href="ecpg-set-connection.html" title="30.5.Choosing a Connection">
<link rel="next" href="ecpg-dynamic.html" title="30.7.Dynamic SQL">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="ecpg-variables"></a>30.6.Using Host Variables</h2></div></div></div>
<p> In <a href="ecpg-commands.html" title="30.4.Running SQL Commands">Section30.4, “Running SQL Commands”</a> you saw how you can execute SQL
statements from an embedded SQL program. Some of those statements
only used fixed values and did not provide a way to insert
user-supplied values into statements or have the program process
the values returned by the query. Those kinds of statements are
not really useful in real applications. This section explains in
detail how you can pass data between your C program and the
embedded SQL statements using a simple mechanism called
<em class="firstterm">host variables</em>.
</p>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="id689929"></a>30.6.1.Overview</h3></div></div></div>
<p> Passing data between the C program and the SQL statements is
particularly simple in embedded SQL. Instead of having the
program paste the data into the statement, which entails various
complications, such as properly quoting the value, you can simply
write the name of a C variable into the SQL statement, prefixed by
a colon. For example:
</p>
<pre class="programlisting">EXEC SQL INSERT INTO sometable VALUES (:v1, 'foo', :v2);</pre>
<p>
This statements refers to two C variables named
<code class="varname">v1</code> and <code class="varname">v2</code> and also uses a
regular SQL string literal, to illustrate that you are not
restricted to use one kind of data or the other.
</p>
<p> This style of inserting C variables in SQL statements works
anywhere a value expression is expected in an SQL statement. In
the SQL environment we call the references to C variables
<em class="firstterm">host variables</em>.
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="id689971"></a>30.6.2.Declare Sections</h3></div></div></div>
<p> To pass data from the program to the database, for example as
parameters in a query, or to pass data from the database back to
the program, the C variables that are intended to contain this
data need to be declared in specially marked sections, so the
embedded SQL preprocessor is made aware of them.
</p>
<p> This section starts with
</p>
<pre class="programlisting">EXEC SQL BEGIN DECLARE SECTION;</pre>
<p>
and ends with
</p>
<pre class="programlisting">EXEC SQL END DECLARE SECTION;</pre>
<p>
Between those lines, there must be normal C variable declarations,
such as
</p>
<pre class="programlisting">int x;
char foo[16], bar[16];</pre>
<p>
You can have as many declare sections in a program as you like.
</p>
<p> The declarations are also echoed to the output file as normal C
variables, so there's no need to declare them again. Variables
that are not intended to be used in SQL commands can be declared
normally outside these special sections.
</p>
<p> The definition of a structure or union also must be listed inside
a <code class="literal">DECLARE</code> section. Otherwise the preprocessor cannot
handle these types since it does not know the definition.
</p>
<p> The special type <code class="type">VARCHAR</code>
is converted into a named <code class="type">struct</code> for every variable. A
declaration like
</p>
<pre class="programlisting">VARCHAR var[180];</pre>
<p>
is converted into
</p>
<pre class="programlisting">struct varchar_var { int len; char arr[180]; } var;</pre>
<p>
This structure is suitable for interfacing with SQL datums of type
<code class="type">varchar</code>.
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="id690062"></a>30.6.3.<code class="command">SELECT INTO</code> and <code class="command">FETCH INTO</code></h3></div></div></div>
<p> Now you should be able to pass data generated by your program into
an SQL command. But how do you retrieve the results of a query?
For that purpose, embedded SQL provides special variants of the
usual commands <code class="command">SELECT</code> and
<code class="command">FETCH</code>. These commands have a special
<code class="literal">INTO</code> clause that specifies which host variables
the retrieved values are to be stored in.
</p>
<p> Here is an example:
</p>
<pre class="programlisting">/*
* assume this table:
* CREATE TABLE test1 (a int, b varchar(50));
*/
EXEC SQL BEGIN DECLARE SECTION;
int v1;
VARCHAR v2;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL SELECT a, b INTO :v1, :v2 FROM test;</pre>
<p>
So the <code class="literal">INTO</code> clause appears between the select
list and the <code class="literal">FROM</code> clause. The number of
elements in the select list and the list after
<code class="literal">INTO</code> (also called the target list) must be
equal.
</p>
<p> Here is an example using the command <code class="command">FETCH</code>:
</p>
<pre class="programlisting">EXEC SQL BEGIN DECLARE SECTION;
int v1;
VARCHAR v2;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test;
...
do {
...
EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2;
...
} while (...);</pre>
<p>
Here the <code class="literal">INTO</code> clause appears after all the
normal clauses.
</p>
<p> Both of these methods only allow retrieving one row at a time. If
you need to process result sets that potentially contain more than
one row, you need to use a cursor, as shown in the second example.
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="id690170"></a>30.6.4.Indicators</h3></div></div></div>
<p> The examples above do not handle null values. In fact, the
retrieval examples will raise an error if they fetch a null value
from the database. To be able to pass null values to the database
or retrieve null values from the database, you need to append a
second host variable specification to each host variable that
contains data. This second host variable is called the
<em class="firstterm">indicator</em> and contains a flag that tells
whether the datum is null, in which case the value of the real
host variable is ignored. Here is an example that handles the
retrieval of null values correctly:
</p>
<pre class="programlisting">EXEC SQL BEGIN DECLARE SECTION;
VARCHAR val;
int val_ind;
EXEC SQL END DECLARE SECTION:
...
EXEC SQL SELECT b INTO :val :val_ind FROM test1;</pre>
<p>
The indicator variable <code class="varname">val_ind</code> will be zero if
the value was not null, and it will be negative if the value was
null.
</p>
<p> The indicator has another function: if the indicator value is
positive, it means that the value is not null, but it was
truncated when it was stored in the host variable.
</p>
</div>
</div></body>
</html>
|