<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Dynamic SQL</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.3.9 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="ECPG - Embedded SQL in C"
HREF="ecpg.html"><LINK
REL="PREVIOUS"
TITLE="Using Host Variables"
HREF="ecpg-variables.html"><LINK
REL="NEXT"
TITLE="pgtypes Library"
HREF="ecpg-pgtypes.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2015-06-13T20:07:22"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.3.9 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Using Host Variables"
HREF="ecpg-variables.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="ecpg.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 33. <SPAN
CLASS="APPLICATION"
>ECPG</SPAN
> - Embedded <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> in C</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="pgtypes Library"
HREF="ecpg-pgtypes.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="ECPG-DYNAMIC"
>33.5. Dynamic SQL</A
></H1
><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
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="ECPG-DYNAMIC-WITHOUT-RESULT"
>33.5.1. Executing Statements without a Result Set</A
></H2
><P
> The simplest way to execute an arbitrary SQL statement is to use
the command <TT
CLASS="COMMAND"
>EXECUTE IMMEDIATE</TT
>. 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>
<TT
CLASS="COMMAND"
>EXECUTE IMMEDIATE</TT
> can be used for SQL
statements that do not return a result set (e.g.,
DDL, <TT
CLASS="COMMAND"
>INSERT</TT
>, <TT
CLASS="COMMAND"
>UPDATE</TT
>,
<TT
CLASS="COMMAND"
>DELETE</TT
>). You cannot execute statements that
retrieve data (e.g., <TT
CLASS="COMMAND"
>SELECT</TT
>) this way. The
next section describes how to do that.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="ECPG-DYNAMIC-INPUT"
>33.5.2. Executing a Statement with Input Parameters</A
></H2
><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>
</P
><P
> When you don't need the prepared statement anymore, you should
deallocate it:
</P><PRE
CLASS="PROGRAMLISTING"
>EXEC SQL DEALLOCATE PREPARE <TT
CLASS="REPLACEABLE"
><I
>name</I
></TT
>;</PRE
><P>
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="ECPG-DYNAMIC-WITH-RESULT"
>33.5.3. Executing a Statement with a Result Set</A
></H2
><P
> To execute an SQL statement with a single result row,
<TT
CLASS="COMMAND"
>EXECUTE</TT
> can be used. To save the result, add
an <TT
CLASS="LITERAL"
>INTO</TT
> 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[50];
EXEC SQL END DECLARE SECTION;
EXEC SQL PREPARE mystmt FROM :stmt;
...
EXEC SQL EXECUTE mystmt INTO :v1, :v2, :v3 USING 37;</PRE
><P>
An <TT
CLASS="COMMAND"
>EXECUTE</TT
> command can have an
<TT
CLASS="LITERAL"
>INTO</TT
> clause, a <TT
CLASS="LITERAL"
>USING</TT
> clause,
both, or neither.
</P
><P
> If a query is expected to return more than one result row, a
cursor should be used, as in the following example.
(See <A
HREF="ecpg-commands.html#ECPG-CURSORS"
>Section 33.3.2</A
> for more details about the
cursor.)
</P><PRE
CLASS="PROGRAMLISTING"
>EXEC SQL BEGIN DECLARE SECTION;
char dbaname[128];
char datname[128];
char *stmt = "SELECT u.usename as dbaname, d.datname "
" FROM pg_database d, pg_user u "
" WHERE d.datdba = u.usesysid";
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT TO testdb AS con1 USER testuser;
EXEC SQL PREPARE stmt1 FROM :stmt;
EXEC SQL DECLARE cursor1 CURSOR FOR stmt1;
EXEC SQL OPEN cursor1;
EXEC SQL WHENEVER NOT FOUND DO BREAK;
while (1)
{
EXEC SQL FETCH cursor1 INTO :dbaname,:datname;
printf("dbaname=%s, datname=%s\n", dbaname, datname);
}
EXEC SQL CLOSE cursor1;
EXEC SQL COMMIT;
EXEC SQL DISCONNECT ALL;</PRE
><P>
</P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="ecpg-variables.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="ecpg-pgtypes.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Using Host Variables</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="ecpg.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>pgtypes Library</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
