codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>36.5. Dynamic SQL</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="ecpg-variables.html" title="36.4. Using Host Variables" /><link rel="next" href="ecpg-pgtypes.html" title="36.6. pgtypes Library" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">36.5. Dynamic SQL</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ecpg-variables.html" title="36.4. Using Host Variables">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="ecpg.html" title="Chapter 36. ECPG — Embedded SQL in C">Up</a></td><th width="60%" align="center">Chapter 36. <span class="application">ECPG</span> — Embedded <acronym class="acronym">SQL</acronym> in C</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="ecpg-pgtypes.html" title="36.6. pgtypes Library">Next</a></td></tr></table><hr /></div><div class="sect1" id="ECPG-DYNAMIC"><div class="titlepage"><div><div><h2 class="title" style="clear: both">36.5. Dynamic SQL <a href="#ECPG-DYNAMIC" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="ecpg-dynamic.html#ECPG-DYNAMIC-WITHOUT-RESULT">36.5.1. Executing Statements without a Result Set</a></span></dt><dt><span class="sect2"><a href="ecpg-dynamic.html#ECPG-DYNAMIC-INPUT">36.5.2. Executing a Statement with Input Parameters</a></span></dt><dt><span class="sect2"><a href="ecpg-dynamic.html#ECPG-DYNAMIC-WITH-RESULT">36.5.3. Executing a Statement with a Result Set</a></span></dt></dl></div><p>3   In many cases, the particular SQL statements that an application4   has to execute are known at the time the application is written.5   In some cases, however, the SQL statements are composed at run time6   or provided by an external source.  In these cases you cannot embed7   the SQL statements directly into the C source code, but there is a8   facility that allows you to call arbitrary SQL statements that you9   provide in a string variable.10  </p><div class="sect2" id="ECPG-DYNAMIC-WITHOUT-RESULT"><div class="titlepage"><div><div><h3 class="title">36.5.1. Executing Statements without a Result Set <a href="#ECPG-DYNAMIC-WITHOUT-RESULT" class="id_link">#</a></h3></div></div></div><p>11    The simplest way to execute an arbitrary SQL statement is to use12    the command <code class="command">EXECUTE IMMEDIATE</code>.  For example:13</p><pre class="programlisting">14EXEC SQL BEGIN DECLARE SECTION;15const char *stmt = "CREATE TABLE test1 (...);";16EXEC SQL END DECLARE SECTION;17 18EXEC SQL EXECUTE IMMEDIATE :stmt;19</pre><p>20    <code class="command">EXECUTE IMMEDIATE</code> can be used for SQL21    statements that do not return a result set (e.g.,22    DDL, <code class="command">INSERT</code>, <code class="command">UPDATE</code>,23    <code class="command">DELETE</code>).  You cannot execute statements that24    retrieve data (e.g., <code class="command">SELECT</code>) this way.  The25    next section describes how to do that.26   </p></div><div class="sect2" id="ECPG-DYNAMIC-INPUT"><div class="titlepage"><div><div><h3 class="title">36.5.2. Executing a Statement with Input Parameters <a href="#ECPG-DYNAMIC-INPUT" class="id_link">#</a></h3></div></div></div><p>27    A more powerful way to execute arbitrary SQL statements is to28    prepare them once and execute the prepared statement as often as29    you like.  It is also possible to prepare a generalized version of30    a statement and then execute specific versions of it by31    substituting parameters.  When preparing the statement, write32    question marks where you want to substitute parameters later.  For33    example:34</p><pre class="programlisting">35EXEC SQL BEGIN DECLARE SECTION;36const char *stmt = "INSERT INTO test1 VALUES(?, ?);";37EXEC SQL END DECLARE SECTION;38 39EXEC SQL PREPARE mystmt FROM :stmt;40 ...41EXEC SQL EXECUTE mystmt USING 42, 'foobar';42</pre><p>43   </p><p>44    When you don't need the prepared statement anymore, you should45    deallocate it:46</p><pre class="programlisting">47EXEC SQL DEALLOCATE PREPARE <em class="replaceable"><code>name</code></em>;48</pre><p>49   </p></div><div class="sect2" id="ECPG-DYNAMIC-WITH-RESULT"><div class="titlepage"><div><div><h3 class="title">36.5.3. Executing a Statement with a Result Set <a href="#ECPG-DYNAMIC-WITH-RESULT" class="id_link">#</a></h3></div></div></div><p>50    To execute an SQL statement with a single result row,51    <code class="command">EXECUTE</code> can be used.  To save the result, add52    an <code class="literal">INTO</code> clause.53</p><pre class="programlisting">54EXEC SQL BEGIN DECLARE SECTION;55const char *stmt = "SELECT a, b, c FROM test1 WHERE a &gt; ?";56int v1, v2;57VARCHAR v3[50];58EXEC SQL END DECLARE SECTION;59 60EXEC SQL PREPARE mystmt FROM :stmt;61 ...62EXEC SQL EXECUTE mystmt INTO :v1, :v2, :v3 USING 37;63 64</pre><p>65    An <code class="command">EXECUTE</code> command can have an66    <code class="literal">INTO</code> clause, a <code class="literal">USING</code> clause,67    both, or neither.68   </p><p>69    If a query is expected to return more than one result row, a70    cursor should be used, as in the following example.71    (See <a class="xref" href="ecpg-commands.html#ECPG-CURSORS" title="36.3.2. Using Cursors">Section 36.3.2</a> for more details about the72    cursor.)73</p><pre class="programlisting">74EXEC SQL BEGIN DECLARE SECTION;75char dbaname[128];76char datname[128];77char *stmt = "SELECT u.usename as dbaname, d.datname "78             "  FROM pg_database d, pg_user u "79             "  WHERE d.datdba = u.usesysid";80EXEC SQL END DECLARE SECTION;81 82EXEC SQL CONNECT TO testdb AS con1 USER testuser;83EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;84 85EXEC SQL PREPARE stmt1 FROM :stmt;86 87EXEC SQL DECLARE cursor1 CURSOR FOR stmt1;88EXEC SQL OPEN cursor1;89 90EXEC SQL WHENEVER NOT FOUND DO BREAK;91 92while (1)93{94    EXEC SQL FETCH cursor1 INTO :dbaname,:datname;95    printf("dbaname=%s, datname=%s\n", dbaname, datname);96}97 98EXEC SQL CLOSE cursor1;99 100EXEC SQL COMMIT;101EXEC SQL DISCONNECT ALL;102</pre><p>103   </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="ecpg-variables.html" title="36.4. Using Host Variables">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="ecpg.html" title="Chapter 36. ECPG — Embedded SQL in C">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="ecpg-pgtypes.html" title="36.6. pgtypes Library">Next</a></td></tr><tr><td width="40%" align="left" valign="top">36.4. Using Host Variables </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 36.6. pgtypes Library</td></tr></table></div></body></html>