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 78 79 80
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>30.4.Running SQL Commands</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-disconnect.html" title="30.3.Closing a Connection">
<link rel="next" href="ecpg-set-connection.html" title="30.5.Choosing a Connection">
<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-commands"></a>30.4.Running SQL Commands</h2></div></div></div>
<p> Any SQL command can be run from within an embedded SQL application.
Below are some examples of how to do that.
</p>
<p> Creating a table:
</p>
<pre class="programlisting">EXEC SQL CREATE TABLE foo (number integer, ascii char(16));
EXEC SQL CREATE UNIQUE INDEX num1 ON foo(number);
EXEC SQL COMMIT;</pre>
<p>
</p>
<p> Inserting rows:
</p>
<pre class="programlisting">EXEC SQL INSERT INTO foo (number, ascii) VALUES (9999, 'doodad');
EXEC SQL COMMIT;</pre>
<p>
</p>
<p> Deleting rows:
</p>
<pre class="programlisting">EXEC SQL DELETE FROM foo WHERE number = 9999;
EXEC SQL COMMIT;</pre>
<p>
</p>
<p> Single-row select:
</p>
<pre class="programlisting">EXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE ascii = 'doodad';</pre>
<p>
</p>
<p> Select using cursors:
</p>
<pre class="programlisting">EXEC SQL DECLARE foo_bar CURSOR FOR
SELECT number, ascii FROM foo
ORDER BY ascii;
EXEC SQL OPEN foo_bar;
EXEC SQL FETCH foo_bar INTO :FooBar, DooDad;
...
EXEC SQL CLOSE foo_bar;
EXEC SQL COMMIT;</pre>
<p>
</p>
<p> Updates:
</p>
<pre class="programlisting">EXEC SQL UPDATE foo
SET ascii = 'foobar'
WHERE number = 9999;
EXEC SQL COMMIT;</pre>
<p>
</p>
<p> The tokens of the form
<code class="literal">:<em class="replaceable"><code>something</code></em></code> are
<em class="firstterm">host variables</em>, that is, they refer to
variables in the C program. They are explained in <a href="ecpg-variables.html" title="30.6.Using Host Variables">Section30.6, “Using Host Variables”</a>.
</p>
<p> In the default mode, statements are committed only when
<code class="command">EXEC SQL COMMIT</code> is issued. The embedded SQL
interface also supports autocommit of transactions (similar to
<span class="application">libpq</span> behavior) via the <code class="option">-t</code> command-line
option to <code class="command">ecpg</code> (see below) or via the <code class="literal">EXEC SQL
SET AUTOCOMMIT TO ON</code> statement. In autocommit mode, each
command is automatically committed unless it is inside an explicit
transaction block. This mode can be explicitly turned off using
<code class="literal">EXEC SQL SET AUTOCOMMIT TO OFF</code>.
</p>
</div></body>
</html>
|