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
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>30.7.Dynamic SQL</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-variables.html" title="30.6.Using Host Variables">
<link rel="next" href="ecpg-descriptors.html" title="30.8.Using SQL Descriptor Areas">
<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-dynamic"></a>30.7.Dynamic SQL</h2></div></div></div>
<p> In many cases, the particular SQL statements that an application
has to execute are known at the time the application is written.
In some cases, however, the SQL statements are composed at run time
or provided by an external source. In these cases you cannot embed
the SQL statements directly into the C source code, but there is a
facility that allows you to call arbitrary SQL statements that you
provide in a string variable.
</p>
<p> The simplest way to execute an arbitrary SQL statement is to use
the command <code class="command">EXECUTE IMMEDIATE</code>. For example:
</p>
<pre class="programlisting">EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "CREATE TABLE test1 (...);";
EXEC SQL END DECLARE SECTION;
EXEC SQL EXECUTE IMMEDIATE :stmt;</pre>
<p>
You may not execute statements that retrieve data (e.g.,
<code class="command">SELECT</code>) this way.
</p>
<p> A more powerful way to execute arbitrary SQL statements is to
prepare them once and execute the prepared statement as often as
you like. It is also possible to prepare a generalized version of
a statement and then execute specific versions of it by
substituting parameters. When preparing the statement, write
question marks where you want to substitute parameters later. For
example:
</p>
<pre class="programlisting">EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "INSERT INTO test1 VALUES(?, ?);";
EXEC SQL END DECLARE SECTION;
EXEC SQL PREPARE mystmt FROM :stmt;
...
EXEC SQL EXECUTE mystmt USING 42, 'foobar';</pre>
<p>
If the statement you are executing returns values, then add an
<code class="literal">INTO</code> clause:
</p>
<pre class="programlisting">EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "SELECT a, b, c FROM test1 WHERE a > ?";
int v1, v2;
VARCHAR v3;
EXEC SQL END DECLARE SECTION;
EXEC SQL PREPARE mystmt FROM :stmt;
...
EXEC SQL EXECUTE mystmt INTO v1, v2, v3 USING 37;</pre>
<p>
An <code class="command">EXECUTE</code> command may have an
<code class="literal">INTO</code> clause, a <code class="literal">USING</code> clause,
both, or neither.
</p>
<p> When you don't need the prepared statement anymore, you should
deallocate it:
</p>
<pre class="programlisting">EXEC SQL DEALLOCATE PREPARE <em class="replaceable"><code>name</code></em>;</pre>
<p>
</p>
</div></body>
</html>
|
