Document ID:                100579.84
Title:                      Array Fetch With Dynamic SQL Method 4 
Creation Date:              01 July 1991
Last Revision Date:         17 February 1995
Revision Number:            1
Product:                    Precomp
Product Version:            Generic 
Platform:                   Generic
Information Type:           Instruction
Impact:                     Medium
Abstract:                   This bulletin describes how to use Dynamic SQL
                            Method 4 to do array fetching. It also includes 
                            sample code. 

Keywords:                   PRECOMPILER;PRO*C;PCC;ARRAY;FETCH;DYNAMIC;SQL
__________________________________________________________________________

 
                     ARRAY FETCH WITH DYNAMIC SQL METHOD 4 
  
This article shows you how to use array fetching with Dynamic SQL Method 4.
If you are unfamiliar with Method 4, please refer to the appropriate chapter 
of the Pro*C Supplement to the Oracle Precopilers Guide for complete details.  
The sample program included below is a modified version of the SAMPLE9.PC 
provided in the Pro*C Supplement manual. 
 
There are just a few things you must do differently.
 
  
 
1.  Allocate enough space for an array of select-list item.
 
    select_dp->V[i]=(char *)calloc(array_size,select_dp->L[i]);
 
2.  Allocate enough space for an array of indicator variable, if any.
 
    select_dp->I[i]=(short *)calloc(array_size,sizeof(short));
 
3.  Use the FOR clause in the FETCH statement to tell ORACLE how many
    elements to process.  Since the arrays are dynamically allocated,
    ORACLE has no way of knowing the size of the arrays it is fetching
    into.

    EXEC SQL FOR :array_size FETCH C USING DESCRIPTOR select_dp;
 
/*  Revised Sample9.pc                        */
/*  Dynamic Method 4 using output host arrays */
 
#include <stdio.h>
#include <ctype.h>
 
#ifndef MSDOS
extern char *malloc();
extern char *calloc();
#else
extern unsigned char *malloc();
extern unsigned char *calloc();
#endif
 
char *nullstring=" ";

EXEC SQL BEGIN DECLARE SECTION;
VARCHAR        username[21];
VARCHAR        password[21];
VARCHAR        statement[240];
int            array_size=100;  /* size of arrays to be allocated */
EXEC SQL END DECLARE SECTION;

EXEC SQL INCLUDE sqlca.h;
EXEC SQL INCLUDE sqlda.h;
 
SQLDA *bind_dp;
SQLDA *select_dp;

extern SQLDA *sqlald();
extern void sqlprc();
int precision, scale;
extern void sqlnul();
short null_ok;
short *indicator;

/**************************************************************/
/*  This function was added for this revised program to print */
/*  records from arrays.                                      */
/**************************************************************/
 
void print_rows();

main ()
{
 int i, j, k;
 char variable[30]; /* String for bind descriptor variable(s) value */
 int num_ret = 0;

 EXEC SQL WHENEVER SQLERROR GOTO sqlerror;

 strcpy (username.arr, "SCOTT");
 username.len = strlen(username.arr);
 strcpy (password.arr, "TIGER");
 password.len = strlen(password.arr);

 EXEC SQL CONNECT :username IDENTIFIED BY :password;

 /*   Allocate storage for Select/Bind descriptors: */
 bind_dp = sqlald (40, 30, 30);
 bind_dp->N = 40;    /* initialize count of array elements */
 select_dp = sqlald (40, 30, 30);
 select_dp->N = 40;  /* initialize count of array elements */

 /*   Get the SQL statement from the operator.
      Note: the "statement" variable is a VARCHAR, so you must
      set the length. */

 printf ("\n\nEnter SQL statement: ");
 gets (statement.arr);
 statement.len = strlen(statement.arr);

 /* Prepare the statement and Declare a cursor */
 EXEC SQL PREPARE S FROM :statement;
 EXEC SQL DECLARE C CURSOR FOR S;

 /*

    Describe Bind Variables, if any.
    This step is optional if you know there will be
    no bind variables in the input statement.
*/

 EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;

 /* If "F" is negative, there were more bind variables
    than was originally allocated using "sqlald" */

 if (bind_dp->F < 0)
  {
  printf ("\nToo many bind variables for descriptor.");
  goto sqlerror;
  }

 /* Set the max number of array elements to the number found */
 bind_dp->N = bind_dp->F;
 
 /* Now go get each bind variable's data value as a char string.*/

 for (i=0; i<bind_dp->F; i++)
  {
   printf ("\nEnter value for bind variable %.*s:\n? ",
                            (int)bind_dp->C[i], bind_dp->S[i]);

   gets (variable);
   bind_dp->L[i] = strlen (variable);
   bind_dp->V[i] = malloc(bind_dp->L[i] + 1);
   /* ^ -- for null term */

   strcpy (bind_dp->V[i], variable);
   bind_dp->I[i] = 0;
   bind_dp->T[i] = 1;
  }

 /* Open the cursor and define the "active set". */

 EXEC SQL OPEN C USING DESCRIPTOR bind_dp;

 /*
    If the SQL statement is a SELECT then, describe the Select
    List variables.  This function returns the column names,
    datatypes, length (inc. precision and scale) and whether the
    column has been defined as NOT NULL.
 */

 EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;

 /* If "F" is negative, there were more select list variables
    than was originally allocated using "sqlald" */

 if (select_dp->F < 0)
  {
   printf ("\nToo many select list variables for descriptor: %d",
             -(select_dp->F));
   goto sqlerror;
  }

 /* Set the max number of elements in descriptor to that found */
 select_dp->N = select_dp->F;

 /*
    Now allocate storage for each of the SELECT list variables.
 */

 for (i=0; i<select_dp->F; i++)
  {
   /* Turn off high order bit of datatype - this program
      doesn't care if the column is NOT NULL */

   sqlnul (&(select_dp->T[i]), &(select_dp->T[i]), &null_ok);

   switch (select_dp->T[i])
    {
     case  1 : /* CHAR datatype - no change in length needed, except
                  maybe for TO_CHAR conversions */
         break;

     case  2 : /* NUMBER datatype - use "sqlprc" to extract
                  precision and scale  */

         sqlprc (&(select_dp->L[i]), &precision, &scale);
         if (precision == 0) precision = 40; /* Max. size of NUMBER */

         /* Also allow for decimal point and possible sign */
         select_dp->L[i] = precision + 2;

         if (scale < 0)
            select_dp->L[i] += -scale; /* allow for negative scale */

         break;

     case  8 : /* LONG datatype */
         select_dp->L[i] = 240;
         break;

     case 12 : /* DATE datatype */
         select_dp->L[i] = 9;
         break;

     case 23 : /* RAW datatype */
         break;

     case 24 : /* LONG RAW datatype */
         select_dp->L[i] = 240;
         break;
    }

   /*************************************************************/
   /* Allocate space for an array of select-list item           */
   /*************************************************************/

   if ((select_dp->V[i] =
        (char *)calloc(array_size, select_dp->L[i])) == NULL)
   {
     printf("** error in calloc() for select-list\n");
     exit(1);
   }

   /*************************************************************/
   /* Allocate space for an array of indicator variable, if any */
   /*************************************************************/

#ifndef MSDOS
   if ((select_dp->I[i] =
        (short *)calloc(array_size,sizeof(short))) == NULL)
   {
     printf("** error in calloc() for indicator variables\n");
     exit(1);
   }

#else
   if ((select_dp->I[i] =
        (unsigned short *)calloc(array_size,sizeof(short))) == NULL)
   {
     printf("** error in calloc() for indicator variables\n");
     exit(1);
   }
#endif
 
   /* Print column headings
      (right justifying number column headings) */

   if (select_dp->T[i] == 2)
     printf ("%*.*s ", (int)select_dp->L[i], (int)select_dp->L[i],
                                                  select_dp->S[i]);

   else
     printf ("%-*.*s ", (int)select_dp->L[i], (int)select_dp->L[i],
                                                  select_dp->S[i]);

   /* Coerce ALL datatypes except for LONG RAW's to character */
   if (select_dp->T[i] != 24)
        select_dp->T[i] = 1;
  }
 printf ("\n\n");

 /*
   If the SQL statement was not a SELECT then we are finished
 */
 if ((strncmp(statement.arr, "SELECT", 6) != 0) &&
     (strncmp(statement.arr, "select", 6) != 0))
      goto end_main;

 /*
    Now fetch each row selected and print the column values
 */

 EXEC SQL WHENEVER NOT FOUND GOTO end_main;
 for (;;)
  {
   /*********************************************************/
   /* use the FOR clause to specify how many array elements */
   /* to process.                                           */
   /*********************************************************/

   EXEC SQL FOR :array_size FETCH C USING DESCRIPTOR select_dp;
   print_rows(sqlca.sqlerrd[2] - num_ret);
   num_ret = sqlca.sqlerrd[2];
  }

end_main:

 /****************************************************/
 /* print remaining records, if any, from the arrays */
 /* after a NOT FOUND condition is detected          */
 /****************************************************/

 if ((sqlca.sqlerrd[2] - num_ret) 0)
   print_rows(sqlca.sqlerrd[2] - num_ret);

 printf ("\n\n# of rows processed = %d", sqlca.sqlerrd[2]);

 /* Free up space used by descriptor (gracefull exit)           */
 /* 1. Free the "malloc"'ed space for the select list variables */

 for (i=0; i<select_dp->F; i++)
  {
   free (select_dp->V[i]);
   free (select_dp->I[i]);
  }

 /* 2. Free up space used by the descriptors themselves */
 sqlclu (bind_dp);
 sqlclu (select_dp);

 EXEC SQL CLOSE C;
 EXEC SQL COMMIT WORK RELEASE;
 return;

/* Oracle Error Handler */
sqlerror:
 printf ("\n%.70s",sqlca.sqlerrm.sqlerrmc);
 EXEC SQL WHENEVER SQLERROR CONTINUE;
 EXEC SQL ROLLBACK WORK RELEASE;
 return;
}

/**************************************************************/
/*  This function was added for this revised program to print */
/*  records from the arrays.                                  */
/**************************************************************/

void print_rows(n)
  int n;
{
  /* i is used as an index to loop through selected columns
     j is used as an index to loop through rows retrieved */

  int i, j;

  /* iptr[i] will point to the starting address of I[] and
     cptr[i] will point to the starting address of V[].  To
     accommodate the maximum number of columns can be selected,
     iptr and cptr are defined with 255 elements */

  short *iptr[255];
  char *cptr[255];

/* initalize cptr and iptr to the starting address of array
   pointed to by V[] and I[] respectively */

  for (i=0; i<select_dp->F; i++)
  {
    cptr[i] = select_dp->V[i];
    iptr[i] = select_dp->I[i];
  }

  /* print all the rows that have been fetched into the arrays */
  for (j=0; j<n; j++)
  {
    /*  print each column */
    for (i=0; i<select_dp->F; i++)
    {
      if (*iptr[i] < 0)
        printf ("%-*.*s ",
           (int)select_dp->L[i], (int)select_dp->L[i], nullstring);

      else
        printf ("%-*.*s ",
           (int)select_dp->L[i], (int)select_dp->L[i], cptr[i]);

      /* increment pointer to get next column & indicator variable */
      cptr[i] += select_dp->L[i];
      iptr[i]++;
    }
    printf ("\n");
  }
  return;
}