File: ecpg-commands.html

package info (click to toggle)
pgadmin3 1.4.3-2
  • links: PTS
  • area: main
  • in suites: etch, etch-m68k
  • size: 29,796 kB
  • ctags: 10,758
  • sloc: cpp: 55,356; sh: 6,164; ansic: 1,520; makefile: 576; sql: 482; xml: 100; perl: 18
file content (80 lines) | stat: -rw-r--r-- 3,237 bytes parent folder | download
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, &#8220;Using Host Variables&#8221;</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>