/****************************************************************/
/* Dynamic Select Example (Section 3.10.3)                      */
/* Chapter 3; Oracle Programming -- A Primer                    */
/*            by R. Sunderraman                                 */
/****************************************************************/

#include <stdio.h>
#include <string.h>

#define MAX_ITEMS         40 /* max number of select list items*/
#define MAX_VNAME_LEN     35 /* max length for column names*/
#define MAX_INAME_LEN     30 /* max length of indicator names*/

EXEC SQL begin declare section;
  varchar username[20];
  varchar password[20];
  char    stmt[256];
EXEC SQL end declare section;
 
EXEC SQL include sqlca;
EXEC SQL include sqlda;

SQLDA *da;

extern SQLDA *sqlald();
extern void sqlnul();
int process_select_list();

main() {
  int i;

  /* Connect to the database. */
  strcpy(username.arr,"book");
  username.len = strlen(username.arr);
  strcpy(password.arr,"book");
  password.len = strlen(password.arr);
  EXEC SQL connect :username identified by :password;

  /* Allocate memory for the SQLDA da and pointers to indicator
       variables and data. */
  da = sqlald (MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN);
  for (i = 0; i < MAX_ITEMS; i++) {
    da->I[i] = (short *) malloc(sizeof(short));
    da->V[i] = (char *) malloc(1);
  }

  strcpy(stmt,"select eno,ename,hdate from employees where eno>=1");

  EXEC SQL prepare s from :stmt;

  process_select_list();

  /* Free space */
  for (i = 0; i < MAX_ITEMS; i++) {    
    if (da->V[i] != (char *) 0)
      free(da->V[i]);
    free(da->I[i]);
  }
  sqlclu(da);

  EXEC SQL commit work release;
  exit(0);
}


process_select_list() {
  int i, null_ok, precision, scale;
  EXEC SQL declare c cursor for s;

  EXEC SQL open c using descriptor da;

  /* The describe function returns their names, datatypes, 
     lengths (including precision and scale), and 
     null/not null statuses. */
  EXEC SQL describe select list for s into da;

  /* Set the maximum number of array elements in the
     descriptor to the number found. */
  da->N = da->F;

  /* Allocate storage for each select-list item.

     sqlprc() is used to extract precision and scale
     from the length (da->L[i]).

     sqlnul() is used to reset the high-order bit of
     the datatype and to check whether the column
     is NOT NULL.

     CHAR    datatypes have length, but zero precision and
             scale.  The length is defined at CREATE time.

     NUMBER  datatypes have precision and scale only if
             defined at CREATE time.  If the column
             definition was just NUMBER, the precision
             and scale are zero, and you must allocate
             the required maximum length.

     DATE    datatypes return a length of 7 if the default
             format is used.  This should be increased to
             9 to store the actual date character string.
             If you use the TO_CHAR function, the maximum
             length could be 75, but will probably be less
             (you can see the effects of this in SQL*Plus).
  */
    
  printf ("\n");
  for (i = 0; i < da->F; i++) {
    /* Turn off high-order bit of datatype (in this example,
       it does not matter if the column is NOT NULL). */
    sqlnul (&(da->T[i]), &(da->T[i]), &null_ok);

    switch (da->T[i]) {
      case  1 : /* CHAR datatype: no change in length
                   needed, except possibly for TO_CHAR
                   conversions (not handled here). */
          break;
      case  2 : /* NUMBER datatype: use sqlprc() to
                   extract precision and scale. */
          sqlprc (&(da->L[i]), &precision, &scale);
                /* Allow for maximum size of NUMBER. */
          if (precision == 0) precision = 40;
                /* Also allow for decimal point and
                   possible sign. */
          /* convert NUMBER datatype to FLOAT if scale > 0,
             INT otherwise. */
          if (scale > 0) da->L[i] = sizeof(float);
          else da->L[i] = sizeof(int);
          break;
      case 12 : /* DATE datatype */
          da->L[i] = 9;
          break;
    }

      /* Allocate space for the select-list data values.
         sqlald() reserves a pointer location for
         V[i] but does not allocate the full space for
         the pointer.  */

       if (da->T[i] != 2)
         da->V[i] = (char *) realloc(da->V[i],da->L[i] + 1);  
       else
         da->V[i] = (char *) realloc(da->V[i],da->L[i]);  

      /* Print column headings, right-justifying number
          column headings. */
      if (da->T[i] == 2)
         if (scale > 0)
           printf ("%.*s ", da->L[i]+3, da->S[i]);
         else
           printf ("%.*s ", da->L[i], da->S[i]);
      else
        printf ("%-.*s ", da->L[i], da->S[i]);

      /* Coerce ALL datatypes except and NUMBER to
         character. */
      if (da->T[i] != 2) da->T[i] = 1;

      /* Coerce the datatypes of NUMBERs to float or int depending on
         the scale. */
      if (da->T[i] == 2)
        if (scale > 0) da->T[i] = 4;  /* float */
        else da->T[i] = 3;  /* int */
  }
  printf ("\n\n");

  /* FETCH each row selected and print the column values. */
  EXEC SQL whenever not found goto end_select_loop;

  for (;;) {
    EXEC SQL fetch c using descriptor da;

    /* Since each variable returned has been coerced to a
       character string, int, or float very little processing 
       is required here.  This routine just prints out the 
       values on the terminal. */
    for (i = 0; i < da->F; i++) {
      if (*da->I[i] < 0)
        if (da->T[i] == 4) 
          printf ("%-*c ",(int)da->L[i]+3, ' ');
        else
          printf ("%-*c ",(int)da->L[i], ' ');
      else
        if (da->T[i] == 3)     /* int datatype */
          printf ("%*d ", (int)da->L[i],*(int *)da->V[i]);
        else if (da->T[i] == 4)     /* float datatype */
               printf ("%*.2f ", (int)da->L[i], *(float *)da->V[i]);
             else                          /* character string */
               printf ("%-*.*s ", (int)da->L[i],(int)da->L[i], da->V[i]);
    }
        printf ("\n");
  }
end_select_loop:

    EXEC SQL close c;

    return;
}