Embedded SQL

Dynamic SQL

A protocol for using SQL within another language is dynamic if it enable sarbitrary SQL source code to be constructed and executed at run-time.

EXECUTE IMMEDIATE

To execute a non-parametric statement, use the command

EXEC SQL EXECUTE IMMEDIATE :string;

where :string is a host variable containing the ASCII representation of the command.

Run-Time Compilation

The SQL PREPARE command is used at run-time to compile SQL commands and to provide a handle to the generated code.

EXEC SQL PREPARE stmt FROM :string;

For SQL cursors in Embedded SQL:

EXEC SQL DECLARE cname CURSOR FOR stmt;
EXEC SQL OPEN cname
	USING :var1 [,...,:vark];
EXEC SQL FETCH cname
	INTO :out1 [,...,:outn];
EXEC SQL CLOSE cname;

When parameters are results are not known: DESCRIPTOR An SQL descriptor is used to communicate information about parameters and results of a prepared SQL command.

SQL commands for descriptor use:

ALLOCATE DESCRIPTOR descr

GET DESCRIPTOR descr <what>
SET DESCRIPTOR descr <what>

where <what> indicates

1. GET/SET a value for COUNT, or
2. GET/SET properties for the i-th attribute: VALUE :i <prop> where <prop> can be DATA, TYPE, INDICATOR, ...

• DESCRIBE [INPUT | OUTPUT] stmt INTO descr

In practice, one uses an explicit host language sqlda data structure.

SQLDA

With normal Embedded SQL, we didn’t need to include SQLDA, only SQLCA. The sqlda data structure is an SQL description area that defines what attributes that are parameters and query answers look like, e.g., where the data is located.

Example of adhoc program.

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "util.h"
 
EXEC SQL INCLUDE SQLCA; 
EXEC SQL INCLUDE SQLDA; 
 
EXEC SQL BEGIN DECLARE SECTION;
   char  db[6] = "cs348";
   char  sqlstmt[1000];
EXEC SQL END DECLARE SECTION;
 
struct sqlda *slct;
 
int main(int argc, char *argv[]) {
   int i, isnull; short type;
   
   printf("Sample C program : ADHOC interactive SQL\n");
 
   /* bail out on error */
   EXEC SQL WHENEVER SQLERROR  GO TO error;
 
   /* connect to the database */
   EXEC SQL CONNECT TO :db;
   printf("Connected to DB2\n");
 
   strncpy(sqlstmt,argv[1],1000);
   printf("Processing <%s>\n",sqlstmt);
 
   /* compile the sql statement */
   EXEC SQL PREPARE stmt FROM :sqlstmt;
 
   init_da(&slct,1);
 
   /* now we find out what it is */
   EXEC SQL DESCRIBE stmt INTO :*slct;
 
   i= slct->sqld; 
   if (i>0) {
     printf("      ... looks like a query\n");
 
     /* new SQLDA to hold enough descriptors for answer */
     init_da(&slct,i);
 
     /* get the names, types, etc... */
     EXEC SQL DESCRIBE stmt INTO :*slct;
 
     printf("Number of slct variables <%d>\n",slct->sqld);
     for (i=0; i<slct->sqld; i++ ) {
       printf("  variable %d <%.*s (%d%s [%d])>\n",
                    i,
                    slct->sqlvar[i].sqlname.length,
                    slct->sqlvar[i].sqlname.data,
                    slct->sqlvar[i].sqltype, 
                    ( (slct->sqlvar[i].sqltype&1)==1 ? "": " not null"),
                    slct->sqlvar[i].sqllen);
     }
     printf("\n");
     
     /* allocate buffers for the returned tuples */
     for (i=0; i<slct->sqld; i++ ) {
       slct->sqlvar[i].sqldata = malloc(slct->sqlvar[i].sqllen);
       slct->sqlvar[i].sqlind = malloc(sizeof(short));
       *slct->sqlvar[i].sqlind = 0;
     }
 
     /* and now process the query */
     EXEC SQL DECLARE cstmt CURSOR FOR stmt;
     EXEC SQL OPEN cstmt;
     EXEC SQL WHENEVER NOT FOUND GO TO end;
 
     /* print the header */
     for (i=0; i<slct->sqld; i++ ) 
       printf("%-*.*s ",slct->sqlvar[i].sqllen, 
                        slct->sqlvar[i].sqlname.length,
                        slct->sqlvar[i].sqlname.data);
     printf("\n");
    
     for (;;) {
       /* fetch next tuple into the prepared buffers */
       EXEC SQL FETCH cstmt USING DESCRIPTOR :*slct;
       for (i=0; i<slct->sqld; i++ ) 
         if ( *(slct->sqlvar[i].sqlind) < 0 ) 
           print_var("NULL",
                     slct->sqlvar[i].sqltype,
                     slct->sqlvar[i].sqlname.length,
                     slct->sqlvar[i].sqllen);
         else
           print_var(slct->sqlvar[i].sqldata,
                     slct->sqlvar[i].sqltype,
                     slct->sqlvar[i].sqlname.length,
                     slct->sqlvar[i].sqllen);
       printf("\n");
       };
   end:
     printf("\n");
   } else {
     printf("      ... looks like an update\n");
     
     EXEC SQL EXECUTE stmt;
   };
   //printf("Rows processed: %d\n",sqlca.sqlerrd[2]);
 
   /* and get out of here */
   EXEC SQL COMMIT;
   EXEC SQL CONNECT reset;
   exit(0);
 
error:
   check_error("My error",&sqlca);
   EXEC SQL WHENEVER SQLERROR CONTINUE;
 
   EXEC SQL ROLLBACK;
   EXEC SQL CONNECT reset;
   exit(1);
}