Touch Oracle Question regarding METHOD 4

From: John Fico <jfico_at_atso.com>
Date: 8 Oct 2001 07:07:48 -0700
Message-ID: <66609969.0110080607.686c70ac@posting.google.com>

I'm trying to create an algorthm based on sample10.pc.

I don't need to process dynamic select statements, only insert.

Here's the catch. I need to insert LARGE amounts of data so I'd like to use host arrays, however, I don't know the number of columns until runtime. I already have code that dynamically creates the table. And I have code that does the inserts one row at a time but it's too slow.

I'm attaching the attempt at filling the sqlda structure. It compiles and exectues but I only get one row. I understand that there is a little bit of hardcoding that will be taken out once I get the foundation built.

Obviously any help would be greatly appreciated.

...
short TA[5];
short TB[5];
char TC[5];
void *T_Addr[3];
int nNumItems;
...

  TA[0]= 1;
  TA[1]= 1;
  TA[2]= 1;
  TB[0]= 2;
  TB[1]= 2;
  TB[2]= 2;
  TC[0]= '3';
  TC[1]= '3';
  TC[2]= '3';

  T_Addr[0] = TA;

  T_Addr[1] = TB;
  T_Addr[2] = TC;
...
strcpy ((char*)s.arr,"insert into foo values (:x,:y,:z)"); s.len=strlen((char *) s.arr);
EXEC SQL PREPARE BLOCK_INSERT FROM :s;
EXEC SQL DECLARE C CURSOR FOR BLOCK_INSERT; ...

    /* Describe any bind variables (input host variables) */     EXEC SQL WHENEVER SQLERROR DO sql_error();     bind_dp->N = nNumItems; /* Initialize count of array elements. */

    EXEC SQL DESCRIBE BIND VARIABLES FOR BLOCK_INSERT INTO bind_dp;

    /* If F is negative, there were more bind variables

       than originally allocated by sqlald(). */     if (bind_dp->F < 0)
    {

        printf ("\nToo many bind variables (%d), maximum is %d\n.",
                    -bind_dp->F, MAX_ITEMS);
        return;

    }
    for (i = 0; i < bind_dp->F; i++)
    {

/* Get length and remove the new line character. */
/*n = strlen(bind_var) - 1;*/

        n=1;

/* Set it in the descriptor. */

        if (i==0) bind_dp->L[i] = 2;    /* short */
        if (i==1) bind_dp->L[i] = 2;    /* short */
        if (i==2) bind_dp->L[i] = 1;    /* char */



        /* (re-)allocate the buffer for the value.

           sqlald() reserves a pointer location for
           V[i] but does not allocate the full space for
           the pointer. */

         bind_dp->V[i] = (char *) realloc(bind_dp->V[i],
                         (bind_dp->L[i] + 1));



        /* And copy it in. */

        /* strncpy(bind_dp->V[i], T_Addr[i], 1); */

        bind_dp->V[i] =  T_Addr[i];



        /* Set the indicator variable's value. */

        /*

        if ((strncmp(bind_dp->V[i], "NULL", 4) == 0) ||
                (strncmp(bind_dp->V[i], "null", 4) == 0))
            *bind_dp->I[i] = -1;
        else
        */
        *bind_dp->I[i] = 0;



        /* Set the bind datatype to 1 for CHAR. */

        if (i == 0) bind_dp->T[i] = 3;       /* number */
        if (i == 1) bind_dp->T[i] = 3;       /* number */
        if (i == 2) bind_dp->T[i] = 1;       /* char */

    } return; Received on Mon Oct 08 2001 - 09:07:48 CDT