1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
5 >Using Host Variables</TITLE
8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
12 TITLE="PostgreSQL 7.4.1 Documentation"
13 HREF="index.html"><LINK
15 TITLE="ECPG - Embedded SQL in C"
16 HREF="ecpg.html"><LINK
18 TITLE="Choosing a Connection"
19 HREF="ecpg-set-connection.html"><LINK
22 HREF="ecpg-dynamic.html"><LINK
25 HREF="stylesheet.css"><META
27 CONTENT="2003-12-22T03:48:47"></HEAD
33 SUMMARY="Header navigation table"
43 >PostgreSQL 7.4.1 Documentation</TH
51 HREF="ecpg-set-connection.html"
87 HREF="ecpg-dynamic.html"
101 NAME="ECPG-VARIABLES"
102 >30.6. Using Host Variables</A
106 HREF="ecpg-commands.html"
108 > you saw how you can execute SQL
109 statements from an embedded SQL program. Some of those statements
110 only used fixed values and did not provide a way to insert
111 user-supplied values into statements or have the program process
112 the values returned by the query. Those kinds of statements are
113 not really useful in real applications. This section explains in
114 detail how you can pass data between your C program and the
115 embedded SQL statements using a simple mechanism called
130 > Passing data between the C program and the SQL statements is
131 particularly simple in embedded SQL. Instead of having the
132 program paste the data into the statement, which entails various
133 complications, such as properly quoting the value, you can simply
134 write the name of a C variable into the SQL statement, prefixed by
135 a colon. For example:
137 CLASS="PROGRAMLISTING"
138 >EXEC SQL INSERT INTO sometable VALUES (:v1, 'foo', :v2);</PRE
140 This statements refers to two C variables named
148 regular SQL string literal, to illustrate that you are not
149 restricted to use one kind of data or the other.
152 > This style of inserting C variables in SQL statements works
153 anywhere a value expression is expected in an SQL statement. In
154 the SQL environment we call the references to C variables
167 >30.6.2. Declare Sections</A
170 > To pass data from the program to the database, for example as
171 parameters in a query, or to pass data from the database back to
172 the program, the C variables that are intended to contain this
173 data need to be declared in specially marked sections, so the
174 embedded SQL preprocessor is made aware of them.
177 > This section starts with
179 CLASS="PROGRAMLISTING"
180 >EXEC SQL BEGIN DECLARE SECTION;</PRE
184 CLASS="PROGRAMLISTING"
185 >EXEC SQL END DECLARE SECTION;</PRE
187 Between those lines, there must be normal C variable declarations,
190 CLASS="PROGRAMLISTING"
192 char foo[16], bar[16];</PRE
194 You can have as many declare sections in a program as you like.
197 > The declarations are also echoed to the output file as a normal C
198 variables, so there's no need to declare them again. Variables
199 that are not intended to be used with SQL commands can be declared
200 normally outside these special sections.
203 > The definition of a structure or union also must be listed inside
207 > section. Otherwise the preprocessor cannot
208 handle these types since it does not know the definition.
211 > The special type <TT
215 is converted into a named <TT
218 > for every variable. A
221 CLASS="PROGRAMLISTING"
222 >VARCHAR var[180];</PRE
226 CLASS="PROGRAMLISTING"
227 >struct varchar_var { int len; char arr[180]; } var;</PRE
229 This structure is suitable for interfacing with SQL datums of type
251 > Now you should be able to pass data generated by your program into
252 an SQL command. But how do you retrieve the results of a query?
253 For that purpose, embedded SQL provides special variants of the
261 >. These commands have a special
265 > clause that specifies which host variables
266 the retrieved values are to be stored in.
269 > Here is an example:
271 CLASS="PROGRAMLISTING"
274 * CREATE TABLE test1 (a int, b varchar(50));
277 EXEC SQL BEGIN DECLARE SECTION;
280 EXEC SQL END DECLARE SECTION;
284 EXEC SQL SELECT a, b INTO :v1, :v2 FROM test;</PRE
289 > clause appears between the select
293 > clause. The number of
294 elements in the select list and the list after
298 > (also called the target list) must be
302 > Here is an example using the command <TT
307 CLASS="PROGRAMLISTING"
308 >EXEC SQL BEGIN DECLARE SECTION;
311 EXEC SQL END DECLARE SECTION;
315 EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test;
321 EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2;
328 > clause appears after all the
332 > Both of these methods only allow retrieving one row at a time. If
333 you need to process result sets that potentially contain more than
334 one row, you need to use a cursor, as shown in the second example.
343 >30.6.4. Indicators</A
346 > The examples above do not handle null values. In fact, the
347 retrieval examples will raise an error if they fetch a null value
348 from the database. To be able to pass null values to the database
349 or retrieve null values from the database, you need to append a
350 second host variable specification to each host variable that
351 contains data. This second host variable is called the
355 > and contains a flag that tells
356 whether the datums is null, in which case the value of the real
357 host variable is ignored. Here is an example that handles the
358 retrieval of null values correctly:
360 CLASS="PROGRAMLISTING"
361 >EXEC SQL BEGIN DECLARE SECTION;
364 EXEC SQL END DECLARE SECTION:
368 EXEC SQL SELECT b INTO :val :val_ind FROM test1;</PRE
370 The indicator variable <VAR
374 the value was not null, and it will be negative if the value was
378 > The indicator has another function: if the indicator value is
379 positive, it means that the value is not null, but it was
380 truncated when it was stored in the host variable.
389 SUMMARY="Footer navigation table"
400 HREF="ecpg-set-connection.html"
418 HREF="ecpg-dynamic.html"
428 >Choosing a Connection</TD
OSDN Git Service
