Obtaining SQL Identifiers from User Input

If a prepared statement requires identifiers, but the identifiers are unknown when you write the prepared statement, you can construct a statement that receives SQL identifiers from user input.

The following ESQL/C example prompts the user for the name of a table and uses that name in a SELECT statement. Because this name is unknown until runtime, the number and data types of the table columns are also unknown. Therefore, the program cannot allocate host variables to receive data from each row in advance. Instead, this program fragment describes the statement into an sqlda descriptor and fetches each row with the descriptor. The fetch puts each row into memory locations that the program provides dynamically.

If a program retrieves all the rows in the active set, the FETCH statement would be placed in a loop that fetched each row. If the FETCH statement retrieves more than one data value (column), another loop exists after the FETCH, which performs some action on each data value:

#include <stdio.h>
EXEC SQL include sqlda;
EXEC SQL include sqltypes;
char *malloc( );

main()
{
   struct sqlda *demodesc;
   char tablename[19];
   int i;
EXEC SQL BEGIN DECLARE SECTION;
   char demoselect[200];
EXEC SQL END DECLARE SECTION;

/*  This program selects all the columns of a given tablename.
       The tablename is supplied interactively. */

EXEC SQL connect to 'stores_demo';
printf( "This program does a select * on a table\n" );
printf( "Enter table name: " );
scanf( "%s", tablename );
sprintf(demoselect, "select * from %s", tablename );

EXEC SQL prepare iid from :demoselect;
EXEC SQL describe iid into demodesc;

/* Print what describe returns */

for ( i = 0;  i < demodesc->sqld; i++ )
   prsqlda (demodesc->sqlvar + i);

/* Assign the data pointers. */

for ( i = 0;  i < demodesc->sqld; i++ ) 
   {
   switch (demodesc->sqlvar[i].sqltype & SQLTYPE) 
      {
      case SQLCHAR:
         demodesc->sqlvar[i].sqltype = CCHARTYPE;
         /* make room for null terminator */
         demodesc->sqlvar[i].sqllen++;
         demodesc->sqlvar[i].sqldata = 
            malloc( demodesc->sqlvar[i].sqllen );
         break;

      case SQLSMINT:    /* fall through */
      case SQLINT:      /* fall through */
      case SQLSERIAL:
         demodesc->sqlvar[i].sqltype = CINTTYPE;
         demodesc->sqlvar[i].sqldata = 
            malloc( sizeof( int ) );
         break;
      /*  And so on for each type.  */
      }
   }

/* Declare and open cursor for select . */
EXEC SQL declare d_curs cursor for iid;
EXEC SQL open d_curs;

/* Fetch selected rows one at a time into demodesc. */
for( ; ; ) 
   {
   printf( "\n" );
   EXEC SQL fetch d_curs using descriptor demodesc;
   if ( sqlca.sqlcode != 0 )
       break;
   for ( i = 0;  i < demodesc->sqld; i++ ) 
      {
      switch (demodesc->sqlvar[i].sqltype) 
         {
         case CCHARTYPE:
            printf( "%s: \"%s\n", demodesc->sqlvar[i].sqlname,
               demodesc->sqlvar[i].sqldata );
            break;
         case CINTTYPE:
            printf( "%s: %d\n", demodesc->sqlvar[i].sqlname,
               *((int *) demodesc->sqlvar[i].sqldata) );
            break;
         /* And so forth for each type... */
            }
      }
   }
EXEC SQL close d_curs;
EXEC SQL free d_curs;
/*  Free the data memory.  */

for ( i = 0;  i < demodesc->sqld; i++ )
   free( demodesc->sqlvar[i].sqldata );
free( demodesc );

printf ("Program Over.\n");
}

prsqlda(sp)
   struct sqlvar_struct *sp;
   
   {
   printf ("type = %d\n", sp->sqltype);
   printf ("len = %d\n", sp->sqllen);
   printf ("data = %lx\n", sp->sqldata);
   printf ("ind = %lx\n", sp->sqlind);
   printf ("name = %s\n", sp->sqlname);
   }

Preparing Multiple SQL Statements

sprintf (query,  "%s %s %s %s %s %s %s",
   "update account set balance = balance + ? ",
      "where acct_number = ?;",
   "update teller set balance = balance + ? ",
      "where teller_number = ?;",
   "update branch set balance = balance + ? ",
      "where branch_number = ?;",
   "insert into history values (?, ?);";
EXEC SQL prepare qid from :query;

EXEC SQL begin work;
EXEC SQL execute qid using
      :delta, :acct_number, :delta, :teller_number,
      :delta, :branch_number, :timestamp, :values;
EXEC SQL commit work;