Basic SQL statements

#docslug#/ecob/ecob/V1.1.0/dcl-statement

OceanBase Embedded SQL in C (ECOB) V1.1.0 supports the following basic SQL statements: SELECT, INSERT, UPDATE, DELETE, COMMIT, and ROLLBACK.

COMMIT

You can execute the COMMIT statement to commit a transaction. You can specify whether to release resources and disconnect the database connection.

Syntax:

EXEC SQL COMMIT [WORK] [COMMENT 'text'] [RELEASE]

The COMMIT WORK parameter and the COMMIT parameter are equivalent. The RELEASE parameter releases all resources and disconnects the current database connection.

Sample statement:

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 the database connection.

Syntax:

EXEC SQL ROLLBACK [WORK] [RELEASE]

The RELEASE parameter releases all resources and disconnects the current database connection.

Sample statement:

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 rule of the SELECT statement in OceanBase Oracle mode. The host variable after the SELECT...INTO clause pulls the data read from OceanBase Database to an application. The query result of the SELECT statement must be one row. Otherwise, an error is returned.

Sample statement:

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;

INSERT

You can execute INSERT statement to execute an insert statement. This statement enables you to write external host variables into table columns of OceanBase Database.

Syntax:

EXEC SQL INSERT INTO <table_name> ( col_list ) VALUES ( expr | :host_variable )

The INSERT statement follows the syntax rule of the INSERT statement in OceanBase Oracle mode.

Sample statement:

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 OceanBase Database 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 rule 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 statement:

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 enables you to delete one or more rows of data in OceanBase Database 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 rule 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 statement:

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;