|PostgreSQL 8.0.26 Documentation|
|Prev||Fast Backward||Chapter 29. ECPG - Embedded SQL in C||Fast Forward||Next|
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.
The simplest way to execute an arbitrary SQL statement is to use the command EXECUTE IMMEDIATE. For example:
EXEC SQL BEGIN DECLARE SECTION; const char *stmt = "CREATE TABLE test1 (...);"; EXEC SQL END DECLARE SECTION; EXEC SQL EXECUTE IMMEDIATE :stmt;
You may not execute statements that retrieve data (e.g., SELECT) this way.
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:
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';
If the statement you are executing returns values, then add an INTO clause:
EXEC SQL BEGIN DECLARE SECTION; const char *stmt = "SELECT a, b, c FROM test1 WHERE a > ?"; int v1, v2; VARCHAR v3; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE mystmt FROM :stmt; ... EXEC SQL EXECUTE mystmt INTO v1, v2, v3 USING 37;
An EXECUTE command may have an INTO clause, a USING clause, both, or neither.
When you don't need the prepared statement anymore, you should deallocate it:
EXEC SQL DEALLOCATE PREPARE name;
Because I needed some hours to get it the right way, I'd like to share my code for a full featured dynamic query which uses a cursor to retrieve all rows:
// first declare some variables
EXEC SQL BEGIN DECLARE SECTION;
const char *input;
EXEC SQL END DECLARE SECTION;
// now construct your search query. This has to be stored in a non const char*. In this example I don't show any fancy construction code.
stmt=strdup("select nr from table where name=?");
// prepare the cursor
EXEC SQL PREPARE search FROM :stmt;
EXEC SQL DECLARE curs SCROLL CURSOR FOR search;
EXEC SQL OPEN curs USING :input;
// now loop over our results
EXEC SQL WHENEVER NOT FOUND DO break;
EXEC SQL FETCH NEXT FROM curs INTO :num;
// do whatever need to be done with "num"
EXEC SQL CLOSE curs;
EXEC SQL DEALLOCATE PREPARE search;
Of course multiple host variabled which should be bound in stmt are possible (aka multiple '?') and they have to be added in the comma separated list after the USING keyword when the cursor is OPENed.