Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

http://developer.mimer.com


Mimer SQL Mobile and ESQL


Support for database applications is currently provided for C and C++ programs with SQL statements embedded in the code.

The pre-processor for C/C++ is installed when you chose the Development and sample program option during installation.

Sample Program

The following examples are extracts from a small C sample program. The program demonstrates how to execute different types of statements.

Example 1

This example, a select statement, is the most complex example as it involves using a cursor in the application program:

 CREATE STATEMENT SEL1 SELECT COL1, COL2 FROM TAB 
 WHERE COL3 = :SELECT_COL3;
 

The example is written in C and looks as follows:

 exec sql begin declare section;
 /*
  * The varchar type is converted to char by the ESQL
  * program
  */
 varchar c1[30], c2[50], c3[10];
 exec sql end declare section;
 /*
  * Compile the statement
  */
 exec sql prepare STAT1 from 'EXECUTE STATEMENT SEL1';
 /*
  * Associate a cursor with the statement
  */
 exec sql declare CURSOR1 for STAT1;
 strcpy(c3, "ABC");
 /*
  *  Variable c3 is used where :SELECT_COL3 is
  *  specified in statement
  */
 exec sql open CURSOR1 using :c3;
 exec sql whenever not found goto end_of_table;
 for (;;) {
       exec sql fetch CURSOR1 into :c1, :c2;
       printf("COL1: %s, COL2: %s\n", c1, c2);
 }
 end_of_table:
 exec sql close CURSOR1;
 exec sql commit work;

Example 2

This example is simpler than the first as no cursor is needed for statements that insert, update, or delete data from the database:

 CREATE STATEMENT INS1 INSERT INTO TAB(COL1, COL2) VALUES('ABC', :COL2_VALUE)
 

The example looks as follows:

 exec sql begin declare section;
 varwchar_t c2[50];
 exec sql end declare section;
 wcscpy(c2, L"C2VAL");
 exec sql prepare STAT2 from 'execute statement INS1';
 /*
  * The execute statement actually perform the
  * insert
  */
 exec sql execute STAT2 using :c2;
 exec sql commit work;
 

The program inserts a row into the table TAB and COL1 is set to ABC and COL2 is set to C2VAL. The varwchar_t is converted to a wchar_t (multi-byte character string) by the preprocessor.

Example 3

This example is slightly more complex as a procedure with both input and output host variables are used:

 CREATE STATEMENT PROC1 CALL PROC('ABC', :inoutvar, :invar)
 

The first parameter is both input and output and the second parameter is only input.

 exec sql begin declare section;
 int i1, i2;
 exec sql end declare section;
 exec sql prepare STAT3 from 'execute statement PROC1';
 i1 = 22;
 i2 = 30;
 exec sql execute STAT3 into :i1 using :i1, i2;
 exec sql commit work;
 printf("InOutVar = %d\n", i1);
 
Note: The names used for host variables in the CREATE STATEMENT are not significant. It is the order the host variables appear in the statement that is significant.

For the call statement, it is the order of INOUT and OUT variables that are significant in the INTO clause and the order of INOUT and IN parameters that are significant for the USING clause.

Extending the Program

You can extend the program to work with statements that are not known beforehand as for any dynamic SQL program.

First, the statement is PREPARED, thereafter you DESCRIBE the number of output columns. If more than zero output columns, the statement is SELECT and a cursor is used to get the result set. Otherwise the statement is executed.

In both cases input and/or output host variables are managed through calls to SET DESCRIPTOR and GET DESCRIPTOR.

For more information on preprocessing embedded SQL, see the Mimer SQL Programmer's Manual, chapter 4, Processing ESQL.



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Documentation TOC PREV NEXT INDEX