1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
12
TITLE="PostgreSQL 9.1beta1 Documentation"
13
HREF="index.html"><LINK
15
TITLE="ECPG - Embedded SQL in C"
16
HREF="ecpg.html"><LINK
18
TITLE="Using Host Variables"
19
HREF="ecpg-variables.html"><LINK
21
TITLE="pgtypes Library"
22
HREF="ecpg-pgtypes.html"><LINK
25
HREF="stylesheet.css"><META
26
HTTP-EQUIV="Content-Type"
27
CONTENT="text/html; charset=ISO-8859-1"><META
29
CONTENT="2011-04-27T21:20:33"></HEAD
35
SUMMARY="Header navigation table"
47
>PostgreSQL 9.1beta1 Documentation</A
56
TITLE="Using Host Variables"
57
HREF="ecpg-variables.html"
66
TITLE="ECPG - Embedded SQL in C"
86
TITLE="ECPG - Embedded SQL in C"
95
TITLE="pgtypes Library"
96
HREF="ecpg-pgtypes.html"
111
>33.5. Dynamic SQL</A
114
> In many cases, the particular SQL statements that an application
115
has to execute are known at the time the application is written.
116
In some cases, however, the SQL statements are composed at run time
117
or provided by an external source. In these cases you cannot embed
118
the SQL statements directly into the C source code, but there is a
119
facility that allows you to call arbitrary SQL statements that you
120
provide in a string variable.
127
NAME="ECPG-DYNAMIC-WITHOUT-RESULT"
128
>33.5.1. Executing Statements without a Result Set</A
131
> The simplest way to execute an arbitrary SQL statement is to use
134
>EXECUTE IMMEDIATE</TT
137
CLASS="PROGRAMLISTING"
138
>EXEC SQL BEGIN DECLARE SECTION;
139
const char *stmt = "CREATE TABLE test1 (...);";
140
EXEC SQL END DECLARE SECTION;
142
EXEC SQL EXECUTE IMMEDIATE :stmt;</PRE
146
>EXECUTE IMMEDIATE</TT
147
> can be used for SQL
148
statements that do not return a result set (e.g.,
159
>). You cannot execute statements that
160
retrieve data (e.g., <TT
164
next section describes how to do that.
172
NAME="ECPG-DYNAMIC-INPUT"
173
>33.5.2. Executing a Statement with Input Parameters</A
176
> A more powerful way to execute arbitrary SQL statements is to
177
prepare them once and execute the prepared statement as often as
178
you like. It is also possible to prepare a generalized version of
179
a statement and then execute specific versions of it by
180
substituting parameters. When preparing the statement, write
181
question marks where you want to substitute parameters later. For
184
CLASS="PROGRAMLISTING"
185
>EXEC SQL BEGIN DECLARE SECTION;
186
const char *stmt = "INSERT INTO test1 VALUES(?, ?);";
187
EXEC SQL END DECLARE SECTION;
189
EXEC SQL PREPARE mystmt FROM :stmt;
191
EXEC SQL EXECUTE mystmt USING 42, 'foobar';</PRE
195
> When you don't need the prepared statement anymore, you should
198
CLASS="PROGRAMLISTING"
199
>EXEC SQL DEALLOCATE PREPARE <TT
213
NAME="ECPG-DYNAMIC-WITH-RESULT"
214
>33.5.3. Executing a Statement with a Result Set</A
217
> To execute an SQL statement with a single result row,
221
> can be used. To save the result, add
227
CLASS="PROGRAMLISTING"
228
>EXEC SQL BEGIN DECLARE SECTION;
229
const char *stmt = "SELECT a, b, c FROM test1 WHERE a > ?";
232
EXEC SQL END DECLARE SECTION;
234
EXEC SQL PREPARE mystmt FROM :stmt;
236
EXEC SQL EXECUTE mystmt INTO :v1, :v2, :v3 USING 37;</PRE
241
> command can have an
252
> If a query is expected to return more than one result row, a
253
cursor should be used, as in the following example.
255
HREF="ecpg-commands.html#ECPG-CURSORS"
257
> for more details about the
260
CLASS="PROGRAMLISTING"
261
>EXEC SQL BEGIN DECLARE SECTION;
264
char *stmt = "SELECT u.usename as dbaname, d.datname "
265
" FROM pg_database d, pg_user u "
266
" WHERE d.datdba = u.usesysid";
267
EXEC SQL END DECLARE SECTION;
269
EXEC SQL CONNECT TO testdb AS con1 USER testuser;
271
EXEC SQL PREPARE stmt1 FROM :stmt;
273
EXEC SQL DECLARE cursor1 CURSOR FOR stmt1;
274
EXEC SQL OPEN cursor1;
276
EXEC SQL WHENEVER NOT FOUND DO BREAK;
280
EXEC SQL FETCH cursor1 INTO :dbaname,:datname;
281
printf("dbaname=%s, datname=%s\n", dbaname, datname);
284
EXEC SQL CLOSE cursor1;
287
EXEC SQL DISCONNECT ALL;</PRE
297
SUMMARY="Footer navigation table"
308
HREF="ecpg-variables.html"
326
HREF="ecpg-pgtypes.html"
336
>Using Host Variables</TD
b'\\ No newline at end of file'