Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Embedded SQL


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

The preprocessor for C/C++ is installed when you chose the Development and sample files option during installation.

Note: Embedded SQL is currently supported only on Pocket PC devices.

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 inserts data into the table:

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

The table used can easily be created using the following statements:

 CREATE TABLE tab (col1 VARCHAR(30),
                   col2 NCHAR VARYING(50),
                   col3 VARCHAR(10));
 
 INSERT INTO tab VALUES ('Some', 'sample', 'text');
 INSERT INTO tab VALUES ('ABC1', 'ABC', 'ABC');
 INSERT INTO tab VALUES ('DEF', 'GHIJ', 'KLMN');
 INSERT INTO tab VALUES ('OPQ2', 'rst', 'ABC');
 INSERT INTO tab VALUES ('xyYZ', 'last', 'row');
 

The example looks as follows:

 exec sql begin declare section;
 varwchar_t c2[51];
 varwchar_t st[100];
 exec sql end declare section;
 exec sql connect to ' ' user 'JOE' using 'Secret';
 wcscpy(c2, L"C2VAL");
 wcscpy(st, L"execute statement INS1");
 exec sql prepare STAT2 from :st;
 /*
  * The execute statement actually performs the
  * insert
  */
 exec sql execute STAT2 using :c2;
 exec sql commit work;
 exec sql disconnect;
 

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 (Unicode character string) by the preprocessor.

Example 2

This example, a select statement, 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
  * preprocessor
  */
 varchar c1[31], c3[11];
 varwchar_t c2[51];
 varwchar_t st[100];
 exec sql end declare section;
 /*
  * Connect user Joe with password Secret to the default database
  */
 exec sql connect to ' ' user 'JOE' using 'Secret';
 /*
  * Load the precompiled statement
  */
 wcscpy(st, L"EXECUTE STATEMENT SEL1");
 exec sql prepare STAT1 from :st';
 /*
  * 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;
     printout("COL1: %s, COL2: %s\n", c1, c2);
 }
 end_of_table:
 exec sql close CURSOR1;
 exec sql commit work;
 exec sql disconnect;

Example 3

This example demonstrates how to call a procedure with both input and output host variables:

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

The procedure used is created by:

 CREATE PROCEDURE proc1 (IN    par1 VARCHAR(100),
                         INOUT par2 INTEGER,
                         IN    par3 INTEGER)
 BEGIN
     SET par2 = par2 * par3 + character_length(par1);
 END
 

The precompiled statement PROC1's first parameter is both input and output and the second parameter is input only.

 exec sql begin declare section;
 int i1, i2;
 varwchar_t st[100];
 exec sql end declare section;
 exec sql connect to ' ' user 'JOE' using 'Secret';
 wcscpy(st, L"execute statement PROC1");
 exec sql prepare STAT3 from :st;
 i1 = 22;
 i2 = 30;
 exec sql execute STAT3 using :i1, i2;
 exec sql commit work;
 printout("InOutVar = %d\n", i1);
 exec sql disconnect;
 
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.

Extending the Program

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

Read about the following statements in the Mimer SQL Mobile Reference Manual if you want to construct a dynamic SQL application: PREPARE, DESCRIBE, and SET/GET DESCRIPTOR.

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


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX