Re: Using Host Arrays with Dynamic SQL Method 4

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 12 Nov 1998 21:56:02 GMT
Message-ID: <365857b1.30237799@192.86.155.100>

A copy of this was sent to bryankim_at_netspace.org (if that email address didn't require changing) On Wed, 11 Nov 1998 17:30:45 GMT, you wrote:

>Hi! Has anyone had problems when trying to use host arrays
>with dynamic SQL method 4? The following scenario seems to
>strike me as a major shortcoming to using method 4 with host
>arrays...
>
>I am currently writing a program that INSERTs multiple rows
>of data at a time into a table where the number of columns
>are unknown until runtime. Descriptor entries are thus made
>for V[i] to the host array address and L[i] to the size of
>each element within the host array.
>
>The problem is when I want to insert host arrays of char *s.
>The pro*C/C++ compiler seems to expect the creation of a 2D
>array (ie char blah[10][20]) that is statically declared and
>thus is allocated as sequential units in memory. This poses a

no, it does not expect it to be statically declared. The examples do that for readability but you can just malloc the data at will.

If you wanted to dynamically insert 100 strings, the maximum width of which was 25 bytes, you would simply malloc( 100*25 ) bytes of data and then put each of the elements in there one at a time. For example, lets say you had an array:

char * strings[100];

and if string[i] was not NULL, it had data. You could write a 'bind' routine that looked something like:

    ....

    for( i = 0, maxl = 0, cnt = 0; i < 100; i++ )     {

        if ( strings[i] != NULL ) 
        {
           cnt++;
           n = strlen( strings[i] ) + 1;  
           if ( n > maxl ) maxl = n;
        }

    }

    bind_dp->V[column] = malloc( cnt * maxl );
    bind_dp->L[column] = maxl;
    bind_dp->T[column] = 5; /* null terminated string */

    for( cp = bind_dp->V[i], i = 0; i < 100; i++ )     {

       if ( strings[i] != NULL )
       {
          strcpy( cp, strings[i] );
          cp += maxl;
       }

    }

    ....

All pro*c expects is a pointer to a contigous segment of memory where the data is layed out. Both:

char data[100][100];

and

char * data = malloc( 100*100 );

do that, they are identical to a subroutine called with a pointer, both point to 10000 bytes of data.

>problem if the number of columns I expect to insert is
>indeterminate until runtime because dynamically allocated 2D
>arrays are not sequential in memory (an array of char *s
>pointing to an array of chars). This seems to defeat the
>entire point of dynamic SQL method 4 since the number of
>statically declared 2D arrays needs to be determined at
>precompile time!
>
>The oracle manuals have an example of this shortly before
>it's listing of sample10.pc. Unfortunately, it still
>illustrates the same shortcoming: the number of 2D arrays
>have been locally declared off the stack at compile time even
>though the SQL statement itself is dynamically created at
>runtime.
>
>The only workaround I can think of is if the V[i] descriptor
>entry treats a malloced array of chars in the same manner as
>a statically declared two dimensional array of chars. In
>other words, if:
>
>char blah[10][20] (stack) = char blah[200] (heap)?
>
>Does the descriptor entry V[i] treat those two host arrays as
>functionally equivalent?
>
>Any feedback is greatly appreciated.
>-bk
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Nov 12 1998 - 15:56:02 CST