Sample c program with fully dynamic SQL


#include 
#include 
#include 
EXEC SQL
  INCLUDE SQLCA;
EXEC SQL
  INCLUDE SQLDA;
struct sqlda *localptr;
short numvars = 10;
char *placeholder[100];
char dstring[60];
char tnumber[5];
int main(void)
{
  EXEC SQL
    WHENEVER SQLERROR goto error_routine;
  strcpy(tnumber,"10000");
  strcpy(dstring,
    "SELECT S#, SNAME FROM CS461DB/STUDENT WHERE S# > ? ");

  /* Set up the space for SQLDA for the estimated size
     using the malloc function.  Note that the function
     SQLDASIZE comes as part of the SQLDA INCLUDE. */

  localptr = (struct sqlda *) malloc(SQLDASIZE(numvars));

  /* Set the sqln value in your SQLDA area to your estimate. */

  localptr->sqln = numvars;
  EXEC SQL
    PREPARE S1 FROM :dstring;

  /* Place the query values in the SQLDA area using the SQL
     DESCRIBE statement. */

  EXEC SQL
    DESCRIBE S1 INTO :*localptr;

  /* Check to see if the estimate used in sqln is bigger than
     the actual number described (sqld).  If the value of sqld
     is bigger than sqln (the size of SQLDA defined), then
     reallocate and redescribe. */

  if (localptr->sqln <  localptr->sqld)
    {numvars = localptr->sqld;
    localptr = (struct sqlda *) malloc(SQLDASIZE(numvars));
    localptr->sqln = numvars;
    EXEC SQL
      DESCRIBE S1 INTO :*localptr;}

  /* Note that you have to place the address of where you
     want SQL/400 to place the data for each of the variables
     that appears in the query.  Here I have chosen to simply
     use predefined variables.  In a more general program
     you could use more complex C/400 code to generalize where
     you place the data and avoid having to know the types of
     the variables when you code the program.  Note, that the
     type of each variable is encoded in localptr->sqlvar[i].sqltype.
     In addition the length of each variable is given in
     localptr->sqlvar[i].sqllen.  The two data values could be
     used to generalize this part of the code. */

  localptr->sqlvar[0].sqldata = (unsigned char *) placeholder;
  localptr->sqlvar[1].sqldata = (unsigned char *) placeholder +6;
  EXEC SQL
    DECLARE C1 CURSOR FOR S1;
  EXEC SQL
    OPEN C1 USING :tnumber;
 while (SQLCODE != 100)
   {
      EXEC SQL
        FETCH C1 USING DESCRIPTOR :*localptr;

 /* You then can use the values in the variables zip1
    and zip2.  */

      if (SQLCODE != 100) printf(" %s       %s\n",
       localptr->sqlvar[0].sqldata ,  localptr->sqlvar[1].sqldata);
   }
  EXEC SQL
   CLOSE C1;
 return;
error_routine: if (SQLCODE < 0) printf("error is %i  %s\n", SQLCODE,
                           sqlca.sqlerrmc );

 }