3
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
4
<title>29.6.�Using Host Variables</title>
5
<link rel="stylesheet" href="stylesheet.css" type="text/css">
6
<link rev="made" href="pgsql-docs@postgresql.org">
7
<meta name="generator" content="DocBook XSL Stylesheets V1.64.1">
8
<link rel="home" href="index.html" title="PostgreSQL 8.0.0beta5 Documentation">
9
<link rel="up" href="ecpg.html" title="Chapter�29.�ECPG - Embedded SQL in C">
10
<link rel="previous" href="ecpg-set-connection.html" title="29.5.�Choosing a Connection">
11
<link rel="next" href="ecpg-dynamic.html" title="29.7.�Dynamic SQL">
13
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
14
<div class="titlepage">
15
<div><div><h2 class="title" style="clear: both">
16
<a name="ecpg-variables"></a>29.6.�Using Host Variables</h2></div></div>
19
<p> In <a href="ecpg-commands.html" title="29.4.�Running SQL Commands">Section�29.4, “Running SQL Commands”</a> you saw how you can execute SQL
20
statements from an embedded SQL program. Some of those statements
21
only used fixed values and did not provide a way to insert
22
user-supplied values into statements or have the program process
23
the values returned by the query. Those kinds of statements are
24
not really useful in real applications. This section explains in
25
detail how you can pass data between your C program and the
26
embedded SQL statements using a simple mechanism called
27
<i class="firstterm">host variables</i>.
29
<div class="sect2" lang="en">
30
<div class="titlepage">
31
<div><div><h3 class="title">
32
<a name="id2616906"></a>29.6.1.�Overview</h3></div></div>
35
<p> Passing data between the C program and the SQL statements is
36
particularly simple in embedded SQL. Instead of having the
37
program paste the data into the statement, which entails various
38
complications, such as properly quoting the value, you can simply
39
write the name of a C variable into the SQL statement, prefixed by
42
<pre class="programlisting">EXEC SQL INSERT INTO sometable VALUES (:v1, 'foo', :v2);</pre>
44
This statements refers to two C variables named
45
<tt class="varname">v1</tt> and <tt class="varname">v2</tt> and also uses a
46
regular SQL string literal, to illustrate that you are not
47
restricted to use one kind of data or the other.
49
<p> This style of inserting C variables in SQL statements works
50
anywhere a value expression is expected in an SQL statement. In
51
the SQL environment we call the references to C variables
52
<i class="firstterm">host variables</i>.
55
<div class="sect2" lang="en">
56
<div class="titlepage">
57
<div><div><h3 class="title">
58
<a name="id2616951"></a>29.6.2.�Declare Sections</h3></div></div>
61
<p> To pass data from the program to the database, for example as
62
parameters in a query, or to pass data from the database back to
63
the program, the C variables that are intended to contain this
64
data need to be declared in specially marked sections, so the
65
embedded SQL preprocessor is made aware of them.
67
<p> This section starts with
69
<pre class="programlisting">EXEC SQL BEGIN DECLARE SECTION;</pre>
73
<pre class="programlisting">EXEC SQL END DECLARE SECTION;</pre>
75
Between those lines, there must be normal C variable declarations,
78
<pre class="programlisting">int x;
79
char foo[16], bar[16];</pre>
81
You can have as many declare sections in a program as you like.
83
<p> The declarations are also echoed to the output file as a normal C
84
variables, so there's no need to declare them again. Variables
85
that are not intended to be used with SQL commands can be declared
86
normally outside these special sections.
88
<p> The definition of a structure or union also must be listed inside
89
a <tt class="literal">DECLARE</tt> section. Otherwise the preprocessor cannot
90
handle these types since it does not know the definition.
92
<p> The special type <tt class="type">VARCHAR</tt>
93
is converted into a named <tt class="type">struct</tt> for every variable. A
96
<pre class="programlisting">VARCHAR var[180];</pre>
100
<pre class="programlisting">struct varchar_var { int len; char arr[180]; } var;</pre>
102
This structure is suitable for interfacing with SQL datums of type
103
<tt class="type">varchar</tt>.
106
<div class="sect2" lang="en">
107
<div class="titlepage">
108
<div><div><h3 class="title">
109
<a name="id2617035"></a>29.6.3.�<tt class="command">SELECT INTO</tt> and <tt class="command">FETCH INTO</tt></h3></div></div>
112
<p> Now you should be able to pass data generated by your program into
113
an SQL command. But how do you retrieve the results of a query?
114
For that purpose, embedded SQL provides special variants of the
115
usual commands <tt class="command">SELECT</tt> and
116
<tt class="command">FETCH</tt>. These commands have a special
117
<tt class="literal">INTO</tt> clause that specifies which host variables
118
the retrieved values are to be stored in.
120
<p> Here is an example:
122
<pre class="programlisting">/*
124
* CREATE TABLE test1 (a int, b varchar(50));
127
EXEC SQL BEGIN DECLARE SECTION;
130
EXEC SQL END DECLARE SECTION;
134
EXEC SQL SELECT a, b INTO :v1, :v2 FROM test;</pre>
136
So the <tt class="literal">INTO</tt> clause appears between the select
137
list and the <tt class="literal">FROM</tt> clause. The number of
138
elements in the select list and the list after
139
<tt class="literal">INTO</tt> (also called the target list) must be
142
<p> Here is an example using the command <tt class="command">FETCH</tt>:
144
<pre class="programlisting">EXEC SQL BEGIN DECLARE SECTION;
147
EXEC SQL END DECLARE SECTION;
151
EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test;
157
EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2;
161
Here the <tt class="literal">INTO</tt> clause appears after all the
164
<p> Both of these methods only allow retrieving one row at a time. If
165
you need to process result sets that potentially contain more than
166
one row, you need to use a cursor, as shown in the second example.
169
<div class="sect2" lang="en">
170
<div class="titlepage">
171
<div><div><h3 class="title">
172
<a name="id2617138"></a>29.6.4.�Indicators</h3></div></div>
175
<p> The examples above do not handle null values. In fact, the
176
retrieval examples will raise an error if they fetch a null value
177
from the database. To be able to pass null values to the database
178
or retrieve null values from the database, you need to append a
179
second host variable specification to each host variable that
180
contains data. This second host variable is called the
181
<i class="firstterm">indicator</i> and contains a flag that tells
182
whether the datums is null, in which case the value of the real
183
host variable is ignored. Here is an example that handles the
184
retrieval of null values correctly:
186
<pre class="programlisting">EXEC SQL BEGIN DECLARE SECTION;
189
EXEC SQL END DECLARE SECTION:
193
EXEC SQL SELECT b INTO :val :val_ind FROM test1;</pre>
195
The indicator variable <tt class="varname">val_ind</tt> will be zero if
196
the value was not null, and it will be negative if the value was
199
<p> The indicator has another function: if the indicator value is
200
positive, it means that the value is not null, but it was
201
truncated when it was stored in the host variable.
added
removed
docs/en_US/pg/ecpg-variables.html
