OceanBase Embedded SQL in C (ECOB) supports the following basic SQL statements: SELECT, INSERT, UPDATE, DELETE, COMMIT, ROLLBACK, and SAVEPOINT.
COMMIT
You can execute the COMMIT statement to commit a transaction. You can specify whether to release resources and disconnect a database connection.
Syntax:
EXEC SQL COMMIT [WORK] [COMMENT 'text'] [RELEASE]
The COMMIT WORK parameter and the COMMIT parameter are equivalent. The RELEASE parameter is used to release all resources and disconnect the current database connection.
Sample statements:
EXEC SQL COMMIT WORK;
EXEC SQL COMMIT WORK RELEASE;
EXEC SQL COMMIT RELEASE;
ROLLBACK
You can execute the ROLLBACK statement to roll back a transaction. You can specify whether to release resources and disconnect a database connection.
Syntax:
EXEC SQL ROLLBACK [WORK] [RELEASE]
The RELEASE parameter is used to release all resources and disconnect the current database connection.
Sample statements:
EXEC SQL ROLLBACK WORK;
EXEC SQL ROLLBACK WORK RELEASE;
SELECT
You can execute the SELECT statement to execute the query statement and assign the query result to external host variables.
Syntax:
EXEC SQL SELECT <select_list> INTO : (host_variable) [ [INDICATOR] : (indicator_variable) ] FROM (table_list) [WHERE (condition)]
The SELECT statement follows the syntax of the SELECT statement in OceanBase Oracle mode. The host variable after the SELECT...INTO clause is used to import data read from databases to an application. The query result of the SELECT statement must be one row. Otherwise, an error is reported.
Sample statements:
short ind;
int c1val;
EXEC SQL CREATE TABLE T1 (c1 int,c2 int);
EXEC SQL INSERT t1 values (1,2);
EXEC SQL SELECT c1 INTO :c1val:ind FROM t1 WHERE c2=2;
SELECT INTO
ECOB supports the storage of SQL results in two-dimensional arrays by using the SELECT INTO statement.
Sample statements:
EXEC SQL create table t1(id number not null primary key , name varchar2(50) not null, unique (name)) partition by hash(id) partitions 8;
// An arr_id one-dimensional array.
int arr_id[2];
EXEC SQL select id into :arr_id from t1 where id>1 and id<4 order by id;
printf("Character strings can be stored in an arr_name two-dimensional array. The row indicates the number of strings, and the column indicates the length \n");
// An arr_mix two-dimensional array.
char arr_mix[2][4];
EXEC SQL select id into :arr_mix from t1 where id=1;
INSERT
You can execute INSERT statement to execute an insert statement. This statement allows you to write external host variables into table columns of databases.
Syntax:
EXEC SQL INSERT INTO <table_name> ( col_list ) VALUES ( expr | :host_variable )
The INSERT statement follows the syntax of the INSERT statement in OceanBase Oracle mode.
Sample statements:
int c1val = 0;
char * c2val = "demo";
EXEC SQL CREATE TABLE t1 (c1 int,c2 varchar2(100));
EXEC SQL INSERT INTO t1 VALUES (:c1val,:c2val);
EXEC SQL INSERT INTO t1(c1) VALUES (:c1val);
EXEC SQL COMMIT WORK;
EXEC SQL DROP TABLE t1;
UPDATE
You can execute the UPDATE statement to execute an update statement. You can specify whether to synchronize values of external variables to table columns of databases or update the column where the cursor is located.
Syntax:
EXEC SQL UPDATE <table_name> SET <column = expr> [WHERE (condition | CURRENT OF <cursor>)]
The UPDATE statement follows the syntax of the UPDATE statement in OceanBase Oracle mode. In this statement, CURRENT OF <cursor> specifies the current column pointed by the cursor, and cursor specifies the name of the cursor. When you define a cursor, you must explicitly add the FOR UPDATE statement to the SELECT statement.
Sample statements:
int c1val = 10;
int c1ret;
char * c2val = "update demo";
EXEC SQL CREATE TABLE t1 (c1 int,c2 varchar2(100));
EXEC SQL INSERT INTO t1 VALUES (1,'init val');
EXEC SQL UPDATE t1 SET c2=:c2val WHERE c1 < :c1val;
EXEC SQL INSERT INTO t1 VALUES (10,'more val');
EXEC SQL INSERT INTO t1 VALUES (20,'more val');
EXEC SQL INSERT INTO t1 VALUES (30,'more val');
EXEC SQL COMMIT WORK;
EXEC SQL DECLARE cur SCROLL CURSOR for select c1 from t1 for update;
EXEC SQL OPEN cur;
EXEC SQL FETCH ABSOLUTE 2 cur INTO :c1ret ;
EXEC SQL UPDATE t1 set c2=:c2val where CURRENT OF cur;
EXEC SQL CLOSE cur;
EXEC SQL COMMIT WORK;
DELETE
You can execute the DELETE statement to execute a delete statement. This statement allows you to delete one or more rows of data in databases or delete the column where the cursor is located.
Syntax:
EXEC SQL DELETE FROM <table_name> [WHERE (condition | CURRENT OF <cursor>) ]
The DELETE statement follows the syntax of the DELETE statement in OceanBase Oracle mode. In this statement, CURRENT OF <cursor> specifies the current column pointed by the cursor, and cursor specifies the name of the cursor. When you define a cursor, you must explicitly add the FOR UPDATE statement to the SELECT statement.
Sample statements:
int c1val = 10;
int pos=2;
int c1ret;
char * c2val = "update demo";
char * username="**u***";
char * password="**1***";
char * servicename="**s***";
EXEC SQL CONNECT :username identified by :password using :servicename;
EXEC SQL DROP TABLE T1;
EXEC SQL CREATE TABLE t1 (c1 int,c2 varchar2(100));
EXEC SQL INSERT INTO t1 VALUES (1,'init val');
EXEC SQL UPDATE t1 SET c2=:c2val WHERE c1 < :c1val;
EXEC SQL INSERT INTO t1 VALUES (10,'more val');
EXEC SQL INSERT INTO t1 VALUES (20,'more val');
EXEC SQL INSERT INTO t1 VALUES (30,'more val');
EXEC SQL COMMIT WORK;
EXEC SQL DECLARE cur SCROLL CURSOR for select c1 from t1 for update;
EXEC SQL OPEN cur;
EXEC SQL FETCH ABSOLUTE 3 cur INTO :c1ret ;
EXEC SQL delete from t1 where CURRENT OF cur;
EXEC SQL CLOSE cur;
EXEC SQL COMMIT WORK;
SAVEPOINT statement
The SAVEPOINT statement is used to set a transaction savepoint. If a savepoint with the same name already exists in the current transaction, the old savepoint is deleted and a new savepoint is set. After a transaction ends, all savepoints are automatically released.
Syntax:
EXEC SQL SAVEPOINT pointname;
Sample statement:
EXEC SQL SAVEPOINT saveX;
exec sql rollback to savepoint savex;