PostgreSQL 8.0.1 Documentation
Prev	Fast Backward	Chapter 29. ECPG - Embedded SQL in C	Fast Forward	Next

29.4. Running SQL Commands

Any SQL command can be run from within an embedded SQL application. Below are some examples of how to do that.

Creating a table:

EXEC SQL CREATE TABLE foo (number integer, ascii char(16));EXEC SQL CREATE UNIQUE INDEX num1 ON foo(number);EXEC SQL COMMIT;

Inserting rows:

EXEC SQL INSERT INTO foo (number, ascii) VALUES (9999, 'doodad');EXEC SQL COMMIT;

Deleting rows:

EXEC SQL DELETE FROM foo WHERE number = 9999;EXEC SQL COMMIT;

Single-row select:

EXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE ascii = 'doodad';

Select using cursors:

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;

Updates:

EXEC SQL UPDATE foo SET ascii = 'foobar' WHERE number = 9999;EXEC SQL COMMIT;

The tokens of the form :something are host variables, that is, they refer to variables in the C program. They are explained in Section 29.6.

In the default mode, statements are committed only when EXEC SQL COMMIT is issued. The embedded SQL interface also supports autocommit of transactions (similar to libpq behavior) via the -t command-line option to ecpg (see below) or via the EXEC SQL SET AUTOCOMMIT TO ON 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 EXEC SQL SET AUTOCOMMIT TO OFF.

Prev	Home	Next
Closing a Connection	Up	Choosing a Connection