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;

Updates:

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

SELECT statements that return a single result row can also be executed using EXEC SQL directly. To handle result sets with multiple rows, an application has to use a cursor; see Section 34.3.2 below. (As a special case, an application can fetch multiple rows at once into an array host variable; see Section 34.4.5.3.1.)

Single-row select:

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

After a normal connection, transactions are opened when SELECT, INSERT, UPDATE, DELETE, cursor, anonymous block, and dynamic SQL are executed using EXEC SQL. If nothing else, be sure to commit the transaction using EXEC SQL COMMIT. If there is an idle in transaction during execution, the transcation will hold locks on used tables and other objects, blocking the execution of other transactions and increasing the chance of deadlock. The transaction in idle in transaction, but also the execution of anonymous block scenarios have been dealt with, that is, before the execution of anonymous blocks, will be submitted to the transaction idle in transaction. Note: Even if the execution in the anonymous block reports an error, the DML operations in the idle in transaction will not be rolled back.

    EXEC SQL update test_commit set a = 13;
    EXEC SQL EXECUTE
       BEGIN
          update_commit(17);
       end;
    END-EXEC;

LightDB Pro*C in non-autocommit mode ensures non-autocommit functionality by sending 'begin transaction' to the server before executing the statement. For select and close cursors executed in a transaction, all operations prior to the execution of the statement are committed. To use this feature, you need to ensure that EXEC SQL COMMIT is executed after the update, delete, and insert.

Also, a configuration parameter can be retrieved with the SHOW command:

EXEC SQL SHOW search_path INTO :var;

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

When executing SQL commands (currently UPDATE and INSERT statements), you can use FOR :something to specify the number of times the SQL statement should be executed, and you can loop through the SQL statement to change the value of the host variables for :somethingsomething. The syntax is as follows:

EXEC SQL FOR :count UpdateStmt;
EXEC SQL FOR :count InsertStmt;

There are several precautions to take when using them:

To retrieve a result set holding multiple rows, an application has to declare a cursor and fetch each row from the cursor. The steps to use a cursor are the following: declare a cursor, open it, fetch a row from the cursor, repeat, and finally close it.

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;

For more details about declaration of the cursor, see DECLARE, and see FETCH for FETCH command details.

From the database fetch data to the array, if the user does not specify the number of fetch, the minimum length of the array will be used as the fetch number, and the char/int/short/long/long_long/double/float array type is supported, and the char three-dimensional array is not supported.

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 ltsql's default behavior) via the -t command-line option to ecpg(Oracle Pro*c compatible) (see ecpg(Oracle Pro*c compatible)) 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.

The following transaction management commands are available:

When the values to be passed to an SQL statement are not known at compile time, or the same statement is going to be used many times, then prepared statements can be useful.

The statement is prepared using the command PREPARE. For the values that are not known yet, use the placeholder “?”:

EXEC SQL PREPARE stmt1 FROM "SELECT oid, datname FROM pg_database WHERE oid = ?";

If a statement returns a single row, the application can call EXECUTE after PREPARE to execute the statement, supplying the actual values for the placeholders with a USING clause:

EXEC SQL EXECUTE stmt1 INTO :dboid, :dbname USING 1;

If a statement returns multiple rows, the application can use a cursor declared based on the prepared statement. To bind input parameters, the cursor must be opened with a USING clause:

EXEC SQL PREPARE stmt1 FROM "SELECT oid,datname FROM pg_database WHERE oid > ?";
EXEC SQL DECLARE foo_bar CURSOR FOR stmt1;

/* when end of result set reached, break out of while loop */
EXEC SQL WHENEVER NOT FOUND DO BREAK;

EXEC SQL OPEN foo_bar USING 100;
...
while (1)
{
    EXEC SQL FETCH NEXT FROM foo_bar INTO :dboid, :dbname;
    ...
}
EXEC SQL CLOSE foo_bar;

When you don't need the prepared statement anymore, you should deallocate it:

EXEC SQL DEALLOCATE PREPARE name;

For more details about PREPARE, see PREPARE. Also see Section 34.5 for more details about using placeholders and input parameters.