codekingpro/portable-devtools
115k
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.3. Running SQL Commands</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-connect.html" title="36.2. Managing Database Connections" /><link rel="next" href="ecpg-variables.html" title="36.4. Using Host Variables" /></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.3. Running SQL Commands</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ecpg-connect.html" title="36.2. Managing Database Connections">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-variables.html" title="36.4. Using Host Variables">Next</a></td></tr></table><hr /></div><div class="sect1" id="ECPG-COMMANDS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">36.3. Running SQL Commands <a href="#ECPG-COMMANDS" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="ecpg-commands.html#ECPG-EXECUTING">36.3.1. Executing SQL Statements</a></span></dt><dt><span class="sect2"><a href="ecpg-commands.html#ECPG-CURSORS">36.3.2. Using Cursors</a></span></dt><dt><span class="sect2"><a href="ecpg-commands.html#ECPG-TRANSACTIONS">36.3.3. Managing Transactions</a></span></dt><dt><span class="sect2"><a href="ecpg-commands.html#ECPG-PREPARED">36.3.4. Prepared Statements</a></span></dt></dl></div><p>3 Any SQL command can be run from within an embedded SQL application.4 Below are some examples of how to do that.5 </p><div class="sect2" id="ECPG-EXECUTING"><div class="titlepage"><div><div><h3 class="title">36.3.1. Executing SQL Statements <a href="#ECPG-EXECUTING" class="id_link">#</a></h3></div></div></div><p>6 Creating a table:7</p><pre class="programlisting">8EXEC SQL CREATE TABLE foo (number integer, ascii char(16));9EXEC SQL CREATE UNIQUE INDEX num1 ON foo(number);10EXEC SQL COMMIT;11</pre><p>12 </p><p>13 Inserting rows:14</p><pre class="programlisting">15EXEC SQL INSERT INTO foo (number, ascii) VALUES (9999, 'doodad');16EXEC SQL COMMIT;17</pre><p>18 </p><p>19 Deleting rows:20</p><pre class="programlisting">21EXEC SQL DELETE FROM foo WHERE number = 9999;22EXEC SQL COMMIT;23</pre><p>24 </p><p>25 Updates:26</p><pre class="programlisting">27EXEC SQL UPDATE foo28 SET ascii = 'foobar'29 WHERE number = 9999;30EXEC SQL COMMIT;31</pre><p>32 </p><p>33 <code class="literal">SELECT</code> statements that return a single result34 row can also be executed using35 <code class="literal">EXEC SQL</code> directly. To handle result sets with36 multiple rows, an application has to use a cursor;37 see <a class="xref" href="ecpg-commands.html#ECPG-CURSORS" title="36.3.2. Using Cursors">Section 36.3.2</a> below. (As a special case, an38 application can fetch multiple rows at once into an array host39 variable; see <a class="xref" href="ecpg-variables.html#ECPG-VARIABLES-ARRAYS" title="36.4.4.3.1. Arrays">Section 36.4.4.3.1</a>.)40 </p><p>41 Single-row select:42</p><pre class="programlisting">43EXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE ascii = 'doodad';44</pre><p>45 </p><p>46 Also, a configuration parameter can be retrieved with the47 <code class="literal">SHOW</code> command:48</p><pre class="programlisting">49EXEC SQL SHOW search_path INTO :var;50</pre><p>51 </p><p>52 The tokens of the form53 <code class="literal">:<em class="replaceable"><code>something</code></em></code> are54 <em class="firstterm">host variables</em>, that is, they refer to55 variables in the C program. They are explained in <a class="xref" href="ecpg-variables.html" title="36.4. Using Host Variables">Section 36.4</a>.56 </p></div><div class="sect2" id="ECPG-CURSORS"><div class="titlepage"><div><div><h3 class="title">36.3.2. Using Cursors <a href="#ECPG-CURSORS" class="id_link">#</a></h3></div></div></div><p>57 To retrieve a result set holding multiple rows, an application has58 to declare a cursor and fetch each row from the cursor. The steps59 to use a cursor are the following: declare a cursor, open it, fetch60 a row from the cursor, repeat, and finally close it.61 </p><p>62 Select using cursors:63</p><pre class="programlisting">64EXEC SQL DECLARE foo_bar CURSOR FOR65 SELECT number, ascii FROM foo66 ORDER BY ascii;67EXEC SQL OPEN foo_bar;68EXEC SQL FETCH foo_bar INTO :FooBar, DooDad;69...70EXEC SQL CLOSE foo_bar;71EXEC SQL COMMIT;72</pre><p>73 </p><p>74 For more details about declaring a cursor, see <a class="xref" href="ecpg-sql-declare.html" title="DECLARE">DECLARE</a>; for more details about fetching rows from a75 cursor, see <a class="xref" href="sql-fetch.html" title="FETCH"><span class="refentrytitle">FETCH</span></a>.76 </p><div class="note"><h3 class="title">Note</h3><p>77 The ECPG <code class="command">DECLARE</code> command does not actually78 cause a statement to be sent to the PostgreSQL backend. The79 cursor is opened in the backend (using the80 backend's <code class="command">DECLARE</code> command) at the point when81 the <code class="command">OPEN</code> command is executed.82 </p></div></div><div class="sect2" id="ECPG-TRANSACTIONS"><div class="titlepage"><div><div><h3 class="title">36.3.3. Managing Transactions <a href="#ECPG-TRANSACTIONS" class="id_link">#</a></h3></div></div></div><p>83 In the default mode, statements are committed only when84 <code class="command">EXEC SQL COMMIT</code> is issued. The embedded SQL85 interface also supports autocommit of transactions (similar to86 <span class="application">psql</span>'s default behavior) via the <code class="option">-t</code>87 command-line option to <code class="command">ecpg</code> (see <a class="xref" href="app-ecpg.html" title="ecpg"><span class="refentrytitle"><span class="application">ecpg</span></span></a>) or via the <code class="literal">EXEC SQL SET AUTOCOMMIT TO88 ON</code> statement. In autocommit mode, each command is89 automatically committed unless it is inside an explicit transaction90 block. This mode can be explicitly turned off using <code class="literal">EXEC91 SQL SET AUTOCOMMIT TO OFF</code>.92 </p><p>93 The following transaction management commands are available:94 95 </p><div class="variablelist"><dl class="variablelist"><dt id="ECPG-TRANSACTIONS-EXEC-SQL-COMMIT"><span class="term"><code class="literal">EXEC SQL COMMIT</code></span> <a href="#ECPG-TRANSACTIONS-EXEC-SQL-COMMIT" class="id_link">#</a></dt><dd><p>96 Commit an in-progress transaction.97 </p></dd><dt id="ECPG-TRANSACTIONS-EXEC-SQL-ROLLBACK"><span class="term"><code class="literal">EXEC SQL ROLLBACK</code></span> <a href="#ECPG-TRANSACTIONS-EXEC-SQL-ROLLBACK" class="id_link">#</a></dt><dd><p>98 Roll back an in-progress transaction.99 </p></dd><dt id="ECPG-TRANSACTIONS-EXEC-SQL-PREPARE-TRANSACTION"><span class="term"><code class="literal">EXEC SQL PREPARE TRANSACTION </code><em class="replaceable"><code>transaction_id</code></em></span> <a href="#ECPG-TRANSACTIONS-EXEC-SQL-PREPARE-TRANSACTION" class="id_link">#</a></dt><dd><p>100 Prepare the current transaction for two-phase commit.101 </p></dd><dt id="ECPG-TRANSACTIONS-EXEC-SQL-COMMIT-PREPARED"><span class="term"><code class="literal">EXEC SQL COMMIT PREPARED </code><em class="replaceable"><code>transaction_id</code></em></span> <a href="#ECPG-TRANSACTIONS-EXEC-SQL-COMMIT-PREPARED" class="id_link">#</a></dt><dd><p>102 Commit a transaction that is in prepared state.103 </p></dd><dt id="ECPG-TRANSACTIONS-EXEC-SQL-ROLLBACK-PREPARED"><span class="term"><code class="literal">EXEC SQL ROLLBACK PREPARED </code><em class="replaceable"><code>transaction_id</code></em></span> <a href="#ECPG-TRANSACTIONS-EXEC-SQL-ROLLBACK-PREPARED" class="id_link">#</a></dt><dd><p>104 Roll back a transaction that is in prepared state.105 </p></dd><dt id="ECPG-TRANSACTIONS-EXEC-SQL-AUTOCOMMIT-ON"><span class="term"><code class="literal">EXEC SQL SET AUTOCOMMIT TO ON</code></span> <a href="#ECPG-TRANSACTIONS-EXEC-SQL-AUTOCOMMIT-ON" class="id_link">#</a></dt><dd><p>106 Enable autocommit mode.107 </p></dd><dt id="ECPG-TRANSACTIONS-EXEC-SQL-AUTOCOMMIT-OFF"><span class="term"><code class="literal">EXEC SQL SET AUTOCOMMIT TO OFF</code></span> <a href="#ECPG-TRANSACTIONS-EXEC-SQL-AUTOCOMMIT-OFF" class="id_link">#</a></dt><dd><p>108 Disable autocommit mode. This is the default.109 </p></dd></dl></div><p>110 </p></div><div class="sect2" id="ECPG-PREPARED"><div class="titlepage"><div><div><h3 class="title">36.3.4. Prepared Statements <a href="#ECPG-PREPARED" class="id_link">#</a></h3></div></div></div><p>111 When the values to be passed to an SQL statement are not known at112 compile time, or the same statement is going to be used many113 times, then prepared statements can be useful.114 </p><p>115 The statement is prepared using the116 command <code class="literal">PREPARE</code>. For the values that are not117 known yet, use the118 placeholder <span class="quote">“<span class="quote"><code class="literal">?</code></span>”</span>:119</p><pre class="programlisting">120EXEC SQL PREPARE stmt1 FROM "SELECT oid, datname FROM pg_database WHERE oid = ?";121</pre><p>122 </p><p>123 If a statement returns a single row, the application can124 call <code class="literal">EXECUTE</code> after125 <code class="literal">PREPARE</code> to execute the statement, supplying the126 actual values for the placeholders with a <code class="literal">USING</code>127 clause:128</p><pre class="programlisting">129EXEC SQL EXECUTE stmt1 INTO :dboid, :dbname USING 1;130</pre><p>131 </p><p>132 If a statement returns multiple rows, the application can use a133 cursor declared based on the prepared statement. To bind input134 parameters, the cursor must be opened with135 a <code class="literal">USING</code> clause:136</p><pre class="programlisting">137EXEC SQL PREPARE stmt1 FROM "SELECT oid,datname FROM pg_database WHERE oid > ?";138EXEC SQL DECLARE foo_bar CURSOR FOR stmt1;139 140/* when end of result set reached, break out of while loop */141EXEC SQL WHENEVER NOT FOUND DO BREAK;142 143EXEC SQL OPEN foo_bar USING 100;144...145while (1)146{147 EXEC SQL FETCH NEXT FROM foo_bar INTO :dboid, :dbname;148 ...149}150EXEC SQL CLOSE foo_bar;151</pre><p>152 </p><p>153 When you don't need the prepared statement anymore, you should154 deallocate it:155</p><pre class="programlisting">156EXEC SQL DEALLOCATE PREPARE <em class="replaceable"><code>name</code></em>;157</pre><p>158 </p><p>159 For more details about <code class="literal">PREPARE</code>,160 see <a class="xref" href="ecpg-sql-prepare.html" title="PREPARE">PREPARE</a>. Also161 see <a class="xref" href="ecpg-dynamic.html" title="36.5. Dynamic SQL">Section 36.5</a> for more details about using162 placeholders and input parameters.163 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="ecpg-connect.html" title="36.2. Managing Database Connections">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-variables.html" title="36.4. Using Host Variables">Next</a></td></tr><tr><td width="40%" align="left" valign="top">36.2. Managing Database Connections </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.4. Using Host Variables</td></tr></table></div></body></html>