Client programming languages

Embedded SQL C Preprocessor for OceanBase (ECOB) is the precompiler in OceanBase Database. ECOB provides complete features that are compatible with Oracle Pro*C.

Pro*C is a tool provided by the Oracle Database ecosystem to develop applications. When you develop an application in C language, you can directly embed SQL statements into the source code of the application. These embedded SQL statements can take C language variables in the host C application as the input and output. Then, proc, the precompiling program of Pro*C, preprocesses the source code for complete semantic analysis and converts the embedded SQL statements and commands to calls of functions in the sqllib runtime library. Finally, a source code file of the application in C language is generated. This file is compiled and linked by the C language compiler and generated as an executable program. Therefore, a Pro*C project consists of a large number of .pc source files and is built by using Makefile or other methods. To be compatible with Pro*C, OceanBase Database uses ECOB, which provides a precompiling program ecob and a runtime library ecoblib. To minimize migration costs, ecob offers the same CLI options and features as proc.

Concepts

• .pc: the filename extension of a C language program file that is embedded with SQL statements.

• ECOB: the precompiler provided by OceanBase Database to compile C language programs that are embedded with SQL statements.

• ecoblib: the runtime library that is provided by OceanBase Database to store the interfaces for function that are required by ECOB.

• Pro*C: the precompiler provided by Oracle to compile C language programs that are embedded with SQL statements.

• sqllib: the runtime library that is provided by Oracle to store the interfaces for function that are required by Pro*C.

Features

• Supports SQL syntax that is compatible with OceanBase Database in Oracle mode.

• Supports basic embedded SQL statements, such as COMMIT, CONNECT, DELETE, EXECUTE, EXECUTE IMMEDIATE, INSERT, SELECT, UPDATE, WHENEVER, CALL, PREPARE, and ROLLBACK.

• Supports cursor-related statements, such as DECLARE, OPEN, CLOSE, and FETCH (including NEXT, ABSOLUTE, WITH HOLD, and CURRENT OF).

• Supports descriptors required by ANSI dynamic SQL and descriptor-related statements, such as ALLOCATE, DEALLOCATE, GET, SET, DESCRIBE, and FETCH.

• Supports the direct use of host variables without declaration in BEGIN DECLARE SECTION and END DECLARE SECTION.

• Recognizes and processes C preprocessor macros #ifdef, #ifndef, #else, #endif, and #define.

• The ecoblib library supports the runtime behavior of all the preceding statements. The ecoblib library provides options (such as CHAR_MAP) that are compatible with the proc program and allows the program to run in the Tuxedo environment.

• The ecob program recognizes all options and compatible semantics (such as PARSE=full) of the proc program, and therefore can be used as a replacement for the proc program.

Data types and variables

ECOB supports internal data types in Oracle, external data types in C language, and conversion between the data types. ECOB also supports variables such as indicators, VARCHAR, structures, and string pointers.

Internal data types

Internal data types in ECOB refer to the data types that can be used in OceanBase Database. The following table describes the internal data types that ECOB supports.

External data types

The external data types in ECOB are used to specify the storage types of the host variables, which are the data types in C language. When you write data into OceanBase Database, ECOB converts the external data types of the host variables into internal data types. When you export data to an external application, ECOB converts the internal data types of OceanBase Database into external data types of the host variables.

The following table describes the external data types supported by ECOB and the mapping between them and host variable data types.

Data type conversion

The ecob program uses external data types for host variables. When the ecob program reads data from OceanBase Database and writes it to C language variables or writes C language data to OceanBase Database, data conversion between the external and internal data types is involved.

The following table describes conversion between different data types. IN refers to conversion from a C language data type to an internal data type of OceanBase Database, and OUT refers to conversion from an internal data type to a C language data type. In the former case, data is written into OceanBase Database. In the latter case, data is read from OceanBase Database.

Indicator variables

An indicator variable is used to process a variable with the NULL value in a database. When you execute a SELECT or FETCH statement, an error is returned if no indicator variable is used and the returned column value is NULL. An indicator variable must use the short data type and be appended to a host variable.

The syntax of an indicator variable is as follows:

:host_variable [INDICATOR] :indicator_variable

After an indicator variable is used, ECOB detects whether the returned column value is NULL based on the following rules:

• If the returned value of the indicator variable is -1, the database returns a NULL value.

• If the returned value of the indicator variable is 0, the column values are assigned to the output host variables.

• If the returned value of the indicator variable is greater than 0, the column values are truncated and assigned to the output host variables. The indicator variable stores the actual length of the column values.

• If the returned value of the indicator variable is -2, the column values are truncated and assigned to the output host variables. The actual length of column values is unknown.

VARCHAR variable

A VARCHAR variable is a character string with a variable length that is defined by using the VARCHAR data type. The VARCHAR data type can be used to define host variables only. To define a VARCHAR variable, you must specify the length of the variable. Here is an example:

VARCHAR name[20];

After the VARCHAR variable is precompiled, the following C structure is generated:

struct{
 unsigned short len;
 unsigned char arr[20];
}name;

In the structure, arr corresponds to the character string, and len indicates the actual length of the character string.

Structure and string pointer variables

To use a structure or a string pointer as a host variable in the ecob program, take note of the following items:

• A structure (Struct) can be delivered as a whole when it is used as a host variable. The structure will be automatically split and allocated to each column when it is precompiled.

• When the string pointer is used as the output, the pointer must be initialized and its value cannot be null. The syntax is as follows:

  char * p1 ;
  char * p2;
  p1=(char *) malloc(11);
  p2=(char*) malloc(11);
  strcpy(p1,"     ");
  strcpy(p2,"00000");
  EXEC SQL SELECT c1,c2 into :p1,:p2 from t1 where rownum < 2;
  

Embedded SQL statements

The embedded SQL statements supported by ECOB cover all standard SQL statements and some extended SQL statements. The extended SQL statements are used to deliver data between the host application and the OBServer node. The following sample code shows the syntax to embed SQL statements in ECOB. You must add EXEC SQL before the statements.

EXEC SQL <standard SQL statement>|<extension SQL statement>

Variable declaration statements

You can use the DECLARE SECTION statement to declare the host variables in ECOB. This SQL statement is specific to ECOB. The syntax is as follows:

EXEC SQL BEGIN DECLARE SECTION;
...
EXEC SQL END DECLARE SECTION;

Here is an example:

EXEC SQL BEGIN DECLARE SECTION;
int a;
char * b;
EXEC SQL END DECLARE SECTION;

You can use only the following elements in a DECLARE SECTION statement:

• Host variable or indicator variable

• Comments of C language

• EXEC SQL INCLUDE statement

• Keyword typedef

Note that if you set PARSE to its default value full, there is no need to declare host variables in the DECLARE SECTION block in most cases. When the structure contains VARCHAR variables, you must declare the host variables in the DECLARE SECTION statement. If you set PARSE to NONE or partial, you must declare the host variables in the DECLARE SECTION statement.

Database connection statements

Among the extended SQL statements supported by ECOB, the CONNECT statement is used to connect to an OceanBase database. The syntax is as follows:

Syntax 1: EXEC SQL CONNECT <:username> identified by <:password> (using <:dbstring>)
Syntax 2: EXEC SQL CONNECT <:user_password> (using <:dbstring>)
Syntax 3: EXEC SQL CONNECT <:username> identified by <:password> (AT <:dbname>) (using <:dbstring>)

Variables are described as follows:

• <:username>:

  The username that is used to connect to the OceanBase tenant in Oracle mode. The username follows the username@tenant name format. For example, you can set this variable to test@oracle. If OceanBase Database Proxy (ODP) is used for connection, the cluster name must be added. In this case, the username follows the username@tenant name#cluster name format. For example, you can set this variable to test@oracle#cluster1.

• <:password>:

  The password that is used to connect to the OceanBase tenant in Oracle mode.

• <:dbstring>:

  The service name that is used to connect to the OceanBase tenant in Oracle mode. The service name is the name of the connection string in the tnsnames.ora file that is specified in the TNS_ADMIN environment variable. Here is an example of the connection string in the tnsnames.ora file:

  demo=
       (DESCRIPTION=
         (ADDRESS=(PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 30035))
         (CONNECT_DATA=
           (SERVICE_NAME=TEST))
  )
  

  In this example, the value of <:dbstring> is demo. If the connection string follows the ip+port pattern, the <:dbstring> value can be written in the ip:port/dbname format. For example, you can set this variable to 10.10.10.10:30035/test.

• <:user_password>:

  The username and password used to connect to the OceanBase tenant in Oracle mode, for example, testUser@oracle/******. The username is separated from the password with a forward slash (/).

  The syntax is as follows:

  //connect method 1
  char * username ="test***@oracle";
  char * password = "******";
  char * servicename = "obdb";
  EXEC SQL CONNECT :username identified by :password using :servicename;
  
  //connect method 2
  char * userpass = "test***@oracle/welcome1";
  char * servicename = "obdb";
  EXEC SQL CONNECT :userpass using :servicename;
  
  //connect method 3
  char * username ="test***@oracle";
  char * password = "******";
  char * servicename = "obdb";
  char * connname = "democonn";
  EXEC SQL CONNECT :username identified by :password AT :connname using :servicename;
  

Basic SQL statements

Among the extended SQL statements supported by ECOB, the following are basic SQL statements: SELECT, INSERT, UPDATE, DELETE, COMMIT, and ROLLBACK.

• You can execute the SELECT statement to execute a query and assign the query results to external host variables.

• You can execute the INSERT statement to insert external host variables to table columns in a database.

• You can execute the UPDATE statement to update table columns in a database with the values of external variables or update only the column in which the cursor is located.

• You can execute the DELETE statement to delete one or more rows in a database or delete only the column in which the cursor is located.

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

• You can execute the ROLLBACK statement to roll back a transaction. You can specify whether to release resources and disconnect from the database.

Prepared statements

Among the extended SQL statements supported by ECOB, the PREPARE and EXECUTE statements are related to prepared (precompiled) statements. They are dynamic SQL statements and can include a fixed number of input or output host variables.

• You can execute the PREPARE statement to prepare (precompile) an SQL statement in PS mode.

• You can execute the EXECUTE statement to execute a prepared SQL statement.

Stored procedures

Among the extended SQL statements supported by ECOB, the CALL statement is used to execute a stored procedure. At present, ECOB supports only stored procedures that do not contain the OUT parameter.

Cursors

Among the extended SQL statements supported by ECOB, the DECLARE CURSOR, OPEN, FETCH, and CLOSE statements are related to cursors.

• You can execute the DECLARE CURSOR statement to declare a cursor variable. At present, cursor variables support only query statements.

• You can execute the OPEN statement to open a cursor variable.

• You can execute the FETCH statement to fetch the result set stored by a cursor variable. For a scroll cursor, you can specify the fetch position.

• You can execute the CLOSE statement to close a cursor variable.

Simple dynamic SQL statements

Among the extended SQL statements supported by ECOB, the EXECUTE IMMEDIATE statement is used to execute dynamic SQL statements. These dynamic SQL statements can be created at runtime. However, they cannot use host variables.

ANSI dynamic SQL statements

Among the extended SQL statements supported by ECOB, there are dynamic SQL statements that are compatible with American National Standards Institute (ANSI) standards. ECOB supports the following ANSI dynamic SQL statements: ALLOCATE DESCRIPTOR, DEALLOCATE DESCRIPTOR, DESCRIBE INPUT DESCRIPTOR, DESCRIBE OUTPUT DESCRIPTOR, GET DESCRIPTOR, SET DESCRIPTOR, OPEN USING DESCRIPTOR, and FETCH INTO DESCRIPTOR. If you are not sure about the number of input or output host variables of an SQL statement, you can use an ANSI dynamic SQL statement.

• You can execute the ALLOCATE DESCRIPTOR statement to allocate an SQL descriptor area.

• You can execute the DEALLOCATE DESCRIPTOR statement to deallocate an SQL descriptor area.

• You can execute the DESCRIBE INPUT DESCRIPTOR statement to bind variable information.

• You can execute the DESCRIBE OUTPUT DESCRIPTOR statement to query the output column information.

• You can execute the GET DESCRIPTOR statement to retrieve the item information of an SQL descriptor area.

• You can execute the SET DESCRIPTOR statement to set the item information of an SQL descriptor area.

• You can execute the OPEN USING DESCRIPTOR statement to open a cursor variable in an ANSI dynamic SQL statement.

• You can execute the FETCH INTO DESCRIPTOR statement to obtain the result set stored by a cursor variable by using a descriptor.

Error handling

SQLCA structure

ECOB implements the SQL Communications Area (SQLCA) structure. You do not need to explicitly use the #include<sqlca.h> or EXEC SQL INCLUDE SQLCA statement. The SQLCA structure comprises several members, including sqlcode, sqlerrm, and sqlerrd.

The value range of sqlcode is described as follows:

• If the value is 0, the statement is successfully executed.

• If the value is smaller than 0, the statement fails to be executed in the database. The specific error code may be different from that in Oracle. It is recommended that the sqlca.sqlcode<0 logic be performed in an application.

• If the value is greater than 0, no appropriate data is found in the database. At present, 1403 is the only valid value in this scenario, which means DATA NOT FOUND.

The sqlerrm structure has two members: sqlerrml and sqlerrmc. The sqlerrml member stores the length of the error message, and the sqlerrmc member stores the error message.

The value of sqlerrd is a long array with six elements. An element with the subscript 0, 1, 3, 4, or 5 is set to NULL. The element with the subscript 2 stores the number of rows processed by the INSERT, UPDATE, and DELETE statements.

The sqlgls function is provided to query the last executed SQL statement, and the sqlglm function is provided to query the error message of the last execution.

The sqlgls function returns the SQL statement. The stmlen parameter returns the length of the SQL statement, and the sqlfc parameter returns the SQL FUNCTION code. The sqlglm function returns the error message. The buffer_size parameter specifies the maximum length of the buffer, and the message_length parameter stores the actual length of the error message. WHENEVER statement

You can execute the WHENEVER statement to specify the method of handling errors and the alert conditions.

The syntax is as follows:

EXEC SQL WHENEVER (SQLERROR | NOT FOUND) (DO (routine | BREAK | CONTINUE) | CONTINUE | GOTO <label> | STOP )

In this statement, SQLERROR specifies to use the action defined in the current statement to handle an error that occurs during statement execution. NOT FOUND specifies to use the action defined in the current statement to handle an exception if no data is found based on the statement.

The following actions are supported:

• CONTINUE: Continue executing the statement. This is the default action, which means no handling of any exception.

• DO: Execute the error handling function.

• DO BREAK: Add a BREAK statement to the application. This action is used in a loop.

• DO CONTINUE: Add a CONTINUE statement to the application. This action is used in a loop.

• GOTO label_name: Jump to the position in which label_name is located in the application.

• STOP: Stop the application. In this case, the transactions that have not been committed are rolled back. This action is similar to executing the EXIT() function.

Note that the WHENEVER statement takes effect on all embedded statements after the WHENEVER statement. Pay special attention to the DO BREAK and DO CONTINUE actions, which must be used in a loop. Otherwise, an error is returned. Therefore, if the DO BREAK or DO CONTINUE action in a DML statement falls out of the loop, you must reset the error handling method before the statement.

{
EXEC SQL WHENEVER SQLERROR DO sqlerror();
EXEC SQL INSERT INTO t1 VALUES(1,'ABC');
...
}
void sqlerror(){
...    
}
Sample statement 2:
int c1val;
EXEC SQL DECLARE cur CURSOR FOR select c1 from t1;
EXEC SQL WHENEVER NOT FOUND DO BREAK;
for(;;){
EXEC SQL FETCH cur INTO :c1val;
}