Preprocessing an ESQL Program

This chapter introduces how to use the ESQL/C preprocessor to preprocess the ESQL and C code mixed source program into a compilable C program.

How to Use dmppcc

The DBMaker preprocessor command is dmppcc. The input ESQL/C file has the suffix .ec and the output of dmppcc is a C language file. During the precompiling, dmppcc creates a stored command for each ESQL DML statement and converts the ESQL statement in the original source to function calls to invoke the stored command. The dmppcc command supports the following options:

Option

Comment

-d database name Required

-u user name Required

-p password Required

-o output_file The output filename. The default is input_file.c.

-l log filename The dmppcc error message log. The default is stderr.

-j project name If not specified, will use module name as project name.

-m module name If not specified, will use input file name as module name.

-s Set singleton select error checking on (if not set, then the default is off).

-cl Set sql check level = limit. The default is FULL. (User can reference a non-existed table in SQL syntax)

-cs Set sql check level = syntax. The default is FULL. (dmppcc check SQL syntax only, without checking table or column information.)

-n Dmppcc won't store SQL command and execution plan on database server.

-v Display version.

-h Shows help information.

-sp Compile stored procedure source.

SINGLETON SELECT check option: (-s)

For example:

dmppcc -s ex1.ec

When it preprocesses the following syntax, it will turn on the singleton select check for run time checking.

EXEC SQL SELECT salary FROM emp_table WHERE emp_id = 1000 INTO :var_salary;

If singleton select error checking is on, and if the number of the resulting tuple is more than one, then it will return an error:

singleton SELECT can only retrieve at most one row

If this check is not turned on, the singleton select query will only get the first tuple column into the host parameter, without checking if there are more resulting tuples from this query.

SQLCHECK level option: FULL(default)/LIMIT(-cl)/SYNTAX(-cs)

When SQLCHECK option set to LIMIT (set -cl), this will enable the ESQL preprocessor to continue preprocess ESQL/C program without return error when the table in EXEC SQL statement is not existed.

When SQLCHECK option set to SYNTAX (set -cs), this will enable the ESQL preprocessor to continue preprocess ESQL/C program without return error when user input correct SQL syntax in the EXEC SQL statement, without checking the related semantic information on the SQL statement about table, column or security. When this option check on, dmppcc won't connect to database, but user still need to provide database name for dmppcc to check database related information in DBMaker's config file dmconfig.ini.

Don't create stored command option: (-n)

When turn on this option, dmppcc will not store precompiled plan in the database. But dmppcc will still do the semantic checking as usual, this option was mainly for user's environment have many people preprocessing different file and link to one executable and other people testing the executable with a function that have been preprocessed again but not linked yet. User may find there's "executable maybe out of date, please rebuild it" kind of error. You may use this option if you often find this error message, and you are sure the file is being preprocessed by other people, or you often find there's a "lock timeout" kind of error message.

The following is an example of the option must set when precompiling the ESQL/C program.
For example:

dmppcc -d test_db -u db_user_id -p db_user_passwd esql_source.ec

The database name, user name, and password are mandatory parameters. You must provide them. If you don't provide them in the command line, dmppcc will try to open a local dmppc.ini file to look for these parameters. You can also put dmppcc parameters in the dmppc.ini file.

DATABASE = database_name 
USER = user_id 
PASSWORD = password 
SELECT_ERROR = yes/no  
OUTFILE = output_filename 
LOGFILE = log_filename 
PROJECT = project_name 
MODULE = module_name 
SQLCHECK = FULL/LIMIT/SYNTAX

The .c file created by dmppcc is an ordinary C source code file. You can combine other .c or .o files to create the final executable file for example, if you have a pre-source program ex1.ec and you want to access the database "TESTDB" with the user "john" and the password "johnspwd".

dmppcc -d TESTDB -u john -p johnspwd example.ec

Then you can use the C compiler to compile the output file ex1.c and link it with the esql library and other DBMaker libraries as an executable.

cc -I. -I$INCDIR -c example.c

For example, if the file example.c is preprocessed by dmppcc compiler and compiled to example.o, then we may use the following statement to link it with other object files as an executable.

acc -o driver example.o otherap.o -L$LIBDIR -ldmapic -lm

You can reference the Makefile file in the DBMaker's samples directory.

~dbmaker/$VERSION/samples/ESQLC.