ESQL Data Manipulation

In this chapter we will give some examples of using ESQL in an application, including how to use host variables, indicator variables, and NULL values.

Embedded SQL Statements

You can issue the same SQL statements within an ESQL application that are available in interactive SQL, plus some additional ones described in this manual.

All SQL commands can be prefixed by EXEC SQL in an ESQL application, including: COMMIT, ROLLBACK, CONNECT, DISCONNECT, INSERT, SELECT, UPDATE, DELETE, etc. However, it is not common to use DDL SQL statements in an ESQL application.

Examples of additional embedded SQL statements used in ESQL are declarative statements such as BEGIN (END) DECLARE SECTION, DECLARE CURSOR, INCLUDE, and WHENEVER as well as additional executable statements such as CLOSE, DESCRIBE, EXECUTE (IMMEDIATE), FETCH, OPEN, and PREPARE. These executable statements are used in embedded SQL only.

Data Manipulation (INSERT, UPDATE, DELETE)

For INSERT, UPDATE, and DELETE, only input host variables are used to pass data from the application into the database. In addition to declaring the host variables in the declare section, you must initialize every input host variable before referencing it.

EXEC SQL BEGIN DECLARE SECTION; 
int hoDeptNo, inDeptNo; 
varchar hoDeptName[8]; 
EXEC SQL BEGIN DECLARE SECTION;

/* Use host variable hoDeptNo to input data into database */ 
   hoDeptNo = 1001;

EXEC SQL INSERT INTO Department (DeptName, DeptNo) 
VALUES ('Human Resource', :hoDeptNo);

Use an indicator variable to input the NULL value into the database. The corresponding host variable is ignored.

Please note the syntax of the indicator variable in the ESQL statement and its value.

inDeptNo = SQL_NULL_DATA;    
EXEC SQL INSERT INTO Department (DeptName, DeptNo) 
VALUES ('Human Resource', :hoDeptNo :inDeptNo);

The next example shows you how to use the host variable of SQL pseudo data types such as varchar that are not directly supported by the C language. As we mentioned before, dmppcc converts the varchar data type into a C structure with two elements: one length and one array.

strcpy(hoDeptName.arr, 'Human Resource'); 
hoDeptName.len = strlen(hoDeptName.arr); 
hoDeptNo = 1001;

EXEC SQL INSERT INTO Department (DeptName, DeptNo) 
VALUES (:hoDeptName, :hoDeptNo); 
UPDATE and DELETE use the input host variable in a similar way. 
strcpy(hoDeptName.arr, 'Human Resource'); 
hoDeptName.len = strlen(hoDeptName.arr); 
hoDeptNo = 1001;

EXEC SQL UPDATE Department SET DeptNo = :hoDeptNo WHERE DeptName = :hoDeptName;

EXEC SQL DELETE FROM Department WHERE DeptNo = :hoDeptNo + 1;

The keyword in SQL syntax for testing the NULL value in the WHERE clause is 'IS NULL', so you cannot use an indicator variable to indicate the NULL value in the WHERE clause.

Retrieving Single-Row Data

You can retrieve single-row data from the database to the output host variable, thus passing a value to the application. Use output host variables in the INTO clause of a SELECT statement.

EXEC SQL BEGIN DECLARE SECTION;    
int hoDeptNo, inDeptNo, inDeptName;    
varchar hoDeptName[8]; 
EXEC SQL BEGIN DECLARE SECTION;

hoDeptNo = 1001;

EXEC SQL SELECT DeptName FROM Department    
WHERE DeptNo = :hoDeptNo 
INTO :hoDeptName :inDeptName;

In this example, hoDeptMant is an output host variable and hoDeptNo is an input host variable. Note also that we have appended an indicator variable inDeptMent to the output host variable to check whether the query retrieves the NULL value or a truncated value. The 0 value indicates the result is normal.

SQL_NULL_DATA indicates the host variable received a NULL value; a value greater than 0 indicates the result in the host variable is truncated and the value in the indicator variable is the length of the original value. For example, since the length of hoDeptName is 8, if the result in the database has a length of 12, the indicator variable will be 12 and the host variable will contain the first 8 characters of the original value.

You can have the full range of standard SQL clauses (e.g., WHERE, GROUP BY, ORDER BY, HAVING,..etc.) within the embedded SELECT statement. You can use input host variables in the WHERE clause and HAVING clause.

Note also that every SELECT statement retrieves exactly one row. If a query returns more than one row you must use a cursor (see below). You should check the sqlca.sqlcode after each SELECT statement to see how many rows are retrieved. If sqlcode in SQLCA is SQL_NO_DATA_FOUND, it means no data was found, if you have specified the option in DBMaker's preprocessor dmppcc for returning an error when more than one row is retrieved in the SELECT statement.

dmppcc -d TESTDB -u john -p johnspwd -s ex1.ec 
                         ^^

When this option is set, it will check for errors, when the result is more than one row.

Transaction Processing

You should use the explicit EXEC SQL COMMIT and EXEC SQL ROLLBACK commands to control the integrity of a transaction. For more advanced applications, you can use SAVEPOINT and ROLLBACK TO SAVEPOINT to have more fine control of data processing. COMMIT WORK and ROLLBACK WORK erases all the savepoints set in a transaction. If you exit the program without explicitly issuing a COMMIT or ROLLBACK, all your changes in this transaction will be rolled back.

You can set the AUTOCOMMIT connection option in the dmconfig.ini file. If the AUTOCOMMIT connection option is set on, then all the SQL statements executed are committed immediately and the ESQL application cannot perform the rollback statement. Be sure to turn the autocommit option off before running the application. Or you can use the following syntax in your ESQL source file to explicitly turn on/off the autocommit option after you connect to a database.

EXEC SQL SET AUTOCOMMIT {ON|OFF}

Dynamic connection syntax

Sometimes you might want to access different database in an ESQL/C application. To achieve this goal, you can write several ESQL programs which you preprocessed with different database name and link all these programs as an executable. Or you can add "AT database name" in front of the SQL statement, and you specify the database name before executing this SQL statement.

Example:

EXEC SQL BEGIN DECLARE SECTION;    
char db1[20]; 
char usr1[10]; 
char pwd1[10]; 
int c1; 
EXEC SQL END DECLARE SECTION;    
/* GetDBInfo ()is user function which will pass back database name, user, password */ 
GetDBInfo(db1, usr1, pwd1); 
EXEC SQL CONNECT TO :db1 :usr1 :pwd1;    
EXEC SQL AT :db1 select c1 from t1 into :c1; 
EXEC SQL DISCONNECT :db1;