3 ESQL Syntax

3.1 Static ESQL with Dynamic ESQL syntax

3.2 Static ESQL

3.3 Variables

3.4 Include Variables

3. ESQL Syntax

The ESQL/C preprocessor will preprocess all statements that are prefixed by "EXEC SQL" or "$" in an ESQL source program. An SQL statement can be placed in a C application program anywhere a C statement is valid. However, the ESQL preprocessor can't handle the EXEC SQL statement in a macro definition, and the ESQL/C pre-processor will not preprocess any EXEC SQL statement in the header files. An SQL statement must be preceded by "EXEC SQL" or "$", and both keywords must be placed on the same line. An SQL statement must end with a semicolon (;).

For example:

if (c1 > 0) EXEC SQL COMMIT WORK;

3.1 Static ESQL with Dynamic ESQL syntax

According to whether the SQL statement is known or unknown at the time of writing and preprocessing the ESQL program, the ESQL syntax can be divided into two types, static ESQL and dynamic ESQL.

For example, the application can use static ESQL syntax when the complete SQL statement is known at preprocessing time. In other words, when doing a delete/insert/update/select operation, and the referenced table and search condition are already known, only the input parameter value may be changed at execution time. For this kind of syntax, DBMaker will check security and compile it into an execution plan and then store the plan in the database. You can reference Chapter 9, "Security", for information about how to manage the stored information in the database.

Here is a sample of a static ESQL/C program:

EXEC SQL INCLUDE DBENVCA; 
EXEC SQL INCLUDE SQLCA; 
main ( ) 
    { 
    EXEC SQL BEGIN DECLARE SECTION;    
    int emp_id; 
    char emp_name[20], emp_addr[50];    
    EXEC SQL END DECLARE SECTION;

    EXEC SQL CONNECT TO testdb john johnspwd;

    /* get user input for host var */    
     
    user_input(&emp_id, emp_name, emp_addr);

    EXEC SQL INSERT INTO emp_table VALUES    (:emp_id, :emp_name, :emp_addr); 
     
    EXEC SQL DISCONNECT; 
    }

Note: When a host variable is referenced in a general C statement, the method is the same as for other C variables. But when the host variable is referenced in an EXEC SQL statement, it must start with a colon (:).

The application can use dynamic ESQL syntax when the complete or partial SQL statement is unknown at preprocessing time, such as when the SQL statement is input by user input (e.g. dmsqlc) or the whole SQL statement is composed by the query tool (QBE). For this dynamic ESQL syntax, DBMaker will not be able to compile the SQL syntax at preprocessing time, so all the compile and check security operations are executed at run time.

Detailed dynamic ESQL syntax will be illustrated in Chapter 8, "Dynamic ESQL".Here is a sample of a dynamic ESQL/C program:

EXEC SQL INCLUDE DBENVCA; 
EXEC SQL INCLUDE SQLCA;

main ( ) 
    { 
    EXEC SQL BEGIN DECLARE SECTION;    
    char sql_string[255]; 
    EXEC SQL END DECLARE SECTION;

    EXEC SQL CONNECT TO testdb john johnspwd;

   /* get user input for SQL statement */    
    user_input(sql_string);

   /* Dynamic ESQL statement without host    variable */ 
    EXEC SQL PREPARE statement_name FROM :sql_string;    
    EXEC SQL EXECUTE statement_name;    
     
    EXEC SQL DISCONNECT; 
    }

3.2 Static ESQL

The valid static ESQL syntax is listed as follows:

EXEC SQL CONNECT TO database_name user_name password 
EXEC SQL DISCONNECT [database_name] 
- EXEC SQL INCLUDE {DBENVCA | SQLCA | SQLDA} 
- EXEC SQL WHENEVER {SQLERROR | SQLWARNING | NOT FOUND} 
                    {CONTINUE| STOP | GO TO label| GOTO label | DO action} 
- EXEC SQL BEGIN DECLARE SECTION, EXEC SQL END DECLARE SECTION 
- EXEC SQL [AT DATABASE_NAME] any SQL statement 
- EXEC SQL [AT DATABASE_NAME] DECLARE cursor_name CURSOR FOR sql_query_statement    
- EXEC SQL [AT DATABASE_NAME] OPEN cursor_name [USING host_variable_[indicator]_list]    
- EXEC SQL [AT DATABASE_NAME] FETCH cursor_name [INTO host_variable_[indicator]_list]    
- EXEC SQL [AT DATABASE_NAME] CLOSE cursor_name

3.3 Variables

You can use variables from the host program in ESQL statements. In this case they are called host variables and they pass data between the C application and the DBMaker database. A host variable is always accompanied by another variable called an indicator variable. While the host variable holds a value, the indicator variable registers the special nature of that value (if it is NULL or has been truncated).

Declare Section

Any host variable and indicator variable that have been referenced in EXEC SQL statements must be declared in the declare section. The declare section is made up of C variable declarations contained within the EXEC SQL statements BEGIN DECLARE SECTION and END DECLARE SECTION.

For example:

EXEC SQL BEGIN DECLARE SECTION; 
varchar hoEmpNo[8];            /* A host variable */ 
int inEmpNo;                   /* An indicator variable */ 
EXEC SQL END DECLARE SECTION;

There can be any number of declare sections within an application program. Every function should have its own declare section if it has host variables and indicator variables referenced in the EXEC SQL statement. DBMaker's dmppcc will return an error if the host variable and indicator variable cannot be found in the declare section.

Host Variable Data Types

You can declare a host variable as a singleton C variable. C structures and unions are not allowed in a host variable. Except for one-dimensional character arrays are used for specify char buffer length, fileobj data type cannot be used for specifying one-dimensional array, other types of C arrays can be declared as one dimensional array, for fetching a row set with more than one data value in a single FETCH statement. For CHAR or BINARY type, user can use two dimensional array to retrieve more than one data value in a single FETCH statement.

All available ESQL type are defined in esqltype.h.

ESQL/C type

Type Definition

Definition

char var_name[n]

char[n]

fix length char
input (n) char
output (n-1) char+ NULL terminate

binary var_name[n]

char[n]

fix length binary
input (n) char
output (n) char

short

short

short integer

int

int

integer

long

long

long integer

float

float

float

double

double

double

In addition to the above data types, you can use the following SQL data types in the declare section:

ESQL/C Type

Type Definition

Definition

date

typedef struct date_s {
short year;
unsigned short month;
unsigned short day;
} eq_date;

Date

time

typedef struct time_s {
unsigned short hour;
unsigned short minute;
unsigned short second;
} eq_time;

Time

timestamp

typedef struct timestamp_s { short year;
unsigned short month;
unsigned short day;
unsigned short hour;
unsigned short minute;
unsigned short second;
unsigned long fraction;
} eq_timestamp;

Timestamp

ESQL/C Type

Type Definition

Definition

varchar var_name[n]

typedef struct varchar_s {
long len;
char arr[n];
} varchar;

Variable length char.
Input: Null terminated string if user has not specified len.
Output: (n-1) char +Null terminated.

varcptr var_name[n]

typedef struct varcptr_s {
long len;
char *arr;
} varcptr;

Variable length char, user must assign the len value.
Input/output same as varchar

varbinary var_name[n]

typedef struct varbinary_s
{
long len;
char arr[n];
} varbinary;

Variable length binary, user must assign the len value.
Input/output same as binary.

varbptr

typedef struct varbpr_s
{
long len; /* must assign a buffer length */
char *arr; /* must assign a valid buffer ptr address */
} varbptr;

Variable length binary
Input/output: same as varbinary

BLOB data type.

ESQL/C Type

Type Definition

Definition

Longvarchar

typedef struct longvarchar_s
{
long bufsize;
char *buf;
} longvarchar;

BLOB (text data)

Longvarbinary

typedef struct longvarbinary_s {
long bufsize;
char *buf;
} longvarbinary;

BLOB (binary data)

fileobj

typedef struct fileobj_s {
long type;
char fname[MAX_FNAME_LEN];
} fileobj;

BLOB (file object)
Default type is ESQL_STORE_FILE_CONTENT. You can also set type as ESQL_STORE_FILE_NAME to store only file name on server.

The type varchar is a null terminated variable length character string , and varbinary is a variable length binary string without null terminate. You can vary the input or output variable's length by assign actual length in the varchar or varbinary type's len field. Because the actual buffer length and buffer address is not defined for varcptr or varbptr type, you must remember to assign it prior using it in the program.

For these non-standard C data types, dmppcc converts them into C structures that are recognized by the C compiler. For example, varchar is resolved into a C structure having two elements: a length field and a character array. .

About fileobj's type: if you don't set the file type, the default type is ESQL_STORE_FILE_CONTENT, which means the database server will store user specified file's content into database, which means the file has been copied into the database. It won't matter if the file has been deleted after you insert this file. However, if you set file type = ESQL_STORE_FILE_NAME, the database will only store the file name you specified in the fname field.

Because the database will only store the file name, you must make sure the file is accessible by DBMaker's database server, and once you delete the file, DBMaker will return error when you reference the file. The setting of file type only work when using fileobj type as input parameter. As output parameter, the database will always try to copy the data into the filename you specified.

Example: Use fileobj type as input host variable.

EXEC SQL BEGIN DECLARE SECTION; 
fileobj fname1; 
EXEC SQL END DECLARE SECTION; 
EXEC SQL CREATE TABLE t1 (c1 file); 
strcpy(fname1.name , "u:\image_path\test1.gif"; 
/* This INSERT statement will store all the content of file test.gif into database    */ 
EXEC SQL INSERT INTO t1 VALUES (:fname1); 
/* If you only want to save some database storage, and the file is located at where DBMaker
 server can access network directories. You should assign the type as ESQL_STORE_FILE_NAME,
 you can switch it back to store file content by assign the type to ESQL_STORE_FILE_CONTENT */ 
fname1.type = ESQL_STORE_FILE_NAME; 
strcpy(fname1.name , "u:\image_path\test2.gif"; 
EXEC SQL INSERT INTO t1 VALUES (:fname1);

Example: Use fileobj type as output variable. The schema for table t2 is c1 long varchar.

EXEC SQL BEGIN DECLARE SECTION; 
fileobj fname1; 
EXEC SQL END DECLARE SECTION; 
strcpy(fname1.name , "u:\image_path\test1.gif"; 
/* This SELECT statement will fetch the blob data from server site and put into user's local file. */ 
strcpy(fname1.name , "u:\local_path\test1.gif"; 
EXEC SQL select c1 from t2 into :fname1;

Another example: Use fileobj as output variable with cursor:

EXEC SQL BEGIN DECLARE SECTION; 
fileobj fname1; 
EXEC SQL END DECLARE SECTION; 
int idx1=0; 
strcpy(fname1.name , "u:\image_path\test1.gif"; 
EXEC SQL DECLARE myCur1 CURSOR FOR select c1 from t2 into :fname1; 
While (1) 
  { 
   idx1++; 
   /* This FETCH statement will fetch the blob data from server site and put into
    user's local file. If you do not change output file name, in next FETCH statement,
    the output file will be overwritten. */ 
   sprintf(fname1.name , "test%d.gif", idx1); 
   EXEC SQL FETCH myCur1; 
   if (SQLCODE) 
   { /* Break while loop when no more data or there's error */ 
   if (SQLCODE != SQL_SUCCESS_WITH_INFO)
   break;
  }      
}

Host Variables

You must apply the following rules when using host variables in a C application program:

The host variables must be declared according to the regular syntax of the host language (the C language) and DBMaker's ESQL supported data types as defined in this guide.
The number of host variables in the INTO clause is equal to or less than the number of columns named in the SELECT statement., and the data type of input or output host variable must be a compatible data type with the corresponding parameter or projection column.

For example:

EXEC SQL BEGIN DECLARE SECTION; 
char emp_id[20];                /* employee ID */ 
char emp_tel[20];               /* employee telephone # */ 
int indvalue = SQL_NTS 
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT emp_id, emp_tel FROM emp_tab INTO :emp_id :indvalue, 
:emp_tel :indvalue

The host variables must be compatible with column value types.
The host variables must always be prefixed by a colon (:) in SQL statements; when used elsewhere in an application program, no colon is necessary.

For example:

EXEC SQL BEGIN DECLARE SECTION; 
char emp_id[20]; /* employee ID */ 
char emp_tel[20]; /* employee telephone # */ 
int indvalue = SQL_NTS; /* indicates input parameter is null terminated */ 
EXEC SQL END DECLARE SECTION;

strcpy(emp_id, "john Smith"); 
strcpy(emp_tel, "765-4321");

EXEC SQL INSERT INTO emp_tab VALUES (:emp_id :indvalue, :emp_tel :indvalue);

Once host variables are established, the data can be retrieved into the variable by DBMaker for use by the application program. A host variable can also be used to insert and update data, or in the WHERE and HAVING clauses.

In the following example, the host variable hoDeptNo is an output host variable that receives the output. The host variable hoEmpNo is an input host variable that has been initialized with the code for select criteria.

EXEC SQL BEGIN DECLARE SECTION; 
int hoDeptNo, hoEmpNo; 
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT deptNo FROM Employee WHERE empNo = :hoEmpNo INTO :hoDeptNo;

Variable Scope

You can declare the variable scope as EXTERN or STATIC in the declare section in the same way as other C variables.

External global variable

When you want to reference an external variable, whose data type is the same as one of ESQL's supported data types, you can add æ'XTERN" in front of the ESQL variable declaration.

For example:

EXEC SQL BEGIN DECLARE SECTION; 
extern int var1; 
EXEC SQL END DECLARE SECTION;

Static variable

When you want to set the local variable as a static variable, you can add "STATIC" in front of the ESQL variable declaration.

For example:

EXEC SQL BEGIN DECLARE SECTION; 
static int var1; 
EXEC SQL END DECLARE SECTION;

Variable passed in function

When you want to reference the value from this function's input variable, or return the value gotten from an ESQL SQL query statement, you must copy the variable data or reassign the pointer to the ESQL variable's data structure.

For example:

/* Method 1, copy the value to esql host variable */ 
func1(int input_emp_id, char *output_telno) 
   { 
   EXEC SQL BEGIN DECLARE SECTION; 
   int emp_id; 
   char telno[15]; 
   EXEC SQL END DECLARE SECTION;

   emp_id = input_emp_id; /* copy the input value */ 
   EXEC SQL SELECT telno FROM emp_tab WHERE emp_id = :emp_id INTO :telno;

   strcpy(output_telno, telno); 
   }

/* Method 2, reassign the buffer pointer */ 
func1(char *input_emp_name, char *output_telno) 
   { 
   EXEC SQL BEGIN DECLARE SECTION; 
   varcptr p_name, p_telno; 
   EXEC SQL END DECLARE SECTION;

   p_name.len = strlen(input_emp_name); /* input string length */ 
   p_name.arr = input_emp_name; 
   p_telno.len = 15; /* output string length */ 
   p_telno.arr = output_telno;

   EXEC SQL SELECT telno FROM emp_tab WHERE emp_name = :p_name INTO :p_telno;    
   }

Indicator Variables

Indicator variables are an optional means to handle null values and truncation for host variables in the application program. When an indicator variable is used, it follows a host variable in the SQL statements. Declare indicator variables as integers only.

For example:

EXEC SQL BEGIN DECLARE SECTION; 
int hoDeptNo, indDeptNo, hoEmpNo; 
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT deptNo FROM Employee 
                       WHERE empNo = :hoEmpNo 
                       INTO :hoDeptNo :indDeptNo;

In the above example, the indicator variable indDeptNo immediately follows the host variable hoDeptNo and is prefixed with a colon. The values in an indicator variable are:

Indicator Value

Value Retrieved From the Database

SQL_NULL_DATA

NULL value returned; output host variable is indeterminate.

0 or greater

The indicator variable will be set as the original output host variable buffer length. If the indicator variable is for getting blob data, the indicator value set to the original output host variable buffer length in the database prior getting the blob data.

To use an indicator variable to input a NULL value into the database, the corresponding host variable is ignored.

For example:

EXEC SQL BEGIN DECLARE SECTION; 
int hoDeptNo, indDeptNo; 
EXEC SQL END DECLARE SECTION;

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

The above example will insert the NULL value into the DeptNo column, and the value of hoDeptNo will be ignored.

Indicator Value

Value Accepted Into the Database

SQL_NULL_DATA

NULL value.

0 or greater

Actual buffer length of input host variable, for CHAR and BINARY types. When an indicator variable is provided, the host variable length set in VARCHAR/VARBINARY data types is ignored.

SQL_NTS

Buffer is null terminated for CHAR and BINARY data types. The host variable length set in VARCHAR/VARBINARY data types is ignored.

Include Variables

There are three kinds of declarations for special data structures required by DBMaker's preprocessor or the runtime application: they are dbenvca, sqlca, and sqlda.

The dbenvca declaration is an environment variable DBMaker uses in your application program. You must declare it in the program.

The syntax is:

EXEC SQL INCLUDE [EXTERN] dbenvca;

For example:

file1.c 
EXEC SQL INCLUDE DBENVCA; 
main() 
   { 
   ... 
   EXEC SQL . 
   ... 
   }

file2.c 
   EXEC SQL INCLUDE EXTERN DBENVCA; 
   func1() 
   { 
   ... 
   }

The syntax for declaring sqlca and sqlda are same as for dbenvca. Sqlca is used to communicate the status code and sqlda is used in dynamic ESQL. Both are discussed in detail later.