Re: Pro*C newbie : need help with fetch on remote DB

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 18 Jul 1998 21:24:12 GMT
Message-ID: <35b111ce.17851919_at_192.86.155.100>


A copy of this was sent to "Pluton" <pluton_at_cybercable.tm.fr> (if that email address didn't require changing) On 15 Jul 1998 12:40:39 +0100, you wrote:

>
> Thanks for the answer but I'm sure this is not a SQL problem.
>The SELECT statement is "select * from table_name where rownum<20"....
>approx. less than 1 second with local DB and more than 20 seconds for the
>same DB and table with remote DB !
>The problem is that I don't know how to use full dynamic SQL with host
>arrays. I can't find a sample to help me.
>
>Thanks again!
>
>
[snip]

Ok, here is an example (and its a useful one at that). I call it 'flat' because it takes any query and creates a flat, comma separated file out of it.

You might run it like:

$ flat userid=scott/tiger 'sqlstmt=select * from emp, dept where.....' arraysize=100

It will fetch arraysize records at a time using the sqlda structure... You control the arraysize but it defaults to 10.

I even tried to add some comments to make it more understandable...

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

#define MAX_VNAME_LEN     30
#define MAX_INAME_LEN     30

static char *     USERID = NULL;

static char * SQLSTMT = NULL;
static char * ARRAY_SIZE = "10";

#define vstrcpy( a, b ) \
(strcpy( a.arr, b ), a.len = strlen( a.arr ), a.arr)  

EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;

extern SQLDA *sqlald();
extern void sqlclu();

static void die( char * msg )
{

    fprintf( stderr, "%s\n", msg );
    exit(1);
}

/*

    this array contains a default mapping I am using to constrain the

       lengths of returned columns.  It is mapping, for example, the Oracle
       NUMBER type (type code = 2) to be 45 characters long in a string.
*/

static int lengths[] =
{ -1, 0, 45, 0, 0, 0, 0, 0, 2000, 0, 0,
 18, 25, 0, 0, 0, 0, 0, 0, 0, 0,
  0, 0, 512, 2000 };

static void process_parms( argc, argv )
int argc;
char * argv[];
{
int i;

    for( i = 1; i < argc; i++ )
    {

        if ( !strncmp( argv[i], "userid=", 7 ) ) USERID = argv[i]+7;
        else
        if ( !strncmp( argv[i], "sqlstmt=", 8 ) ) SQLSTMT = argv[i]+8;
        else
        if ( !strncmp( argv[i], "arraysize=", 10 ) ) ARRAY_SIZE = argv[i]+10;
        else
        {
            printf( "usage: %s userid=xxx/xxx sqlstmt=query arraysize=<NN>\n", 
                     argv[0] );
            exit(1);
        }

    }
    if ( USERID == NULL || SQLSTMT == NULL )     {
        printf( "usage: %s userid=xxx/xxx sqlstmt=query arraysize=<NN>\n", 
                 argv[0] );
        exit(1);

    }
}

static void sqlerror_hard()
{

    EXEC SQL WHENEVER SQLERROR CONTINUE;     printf("\nORACLE error detected:");
    printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

    EXEC SQL ROLLBACK WORK RELEASE;
    exit(1);
}

static int process(char * sqlstmt)
{
SQLDA * select_dp;

int     i;
int     j;
int     null_ok;
int     precision;
int     scale;
int     size = 10;
int     array_size = atoi(ARRAY_SIZE);

short ind_value;
char * char_ptr;
int     last_fetch_count;
int     row_count = 0;

    printf( "Array size = %d\n", array_size );

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

    /*
     * Dynamically prepare the sqlstmt passed in.  Must be a select 
     * statement for this application to work
     */

    EXEC SQL PREPARE S FROM :sqlstmt;
    /* 
     * declare a cursor for the prepared statement
     */

    EXEC SQL DECLARE C CURSOR FOR S;
    /*
     * allocate a select descriptor, have it default to "size" columns
     * wide
     */
    if ((select_dp = sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN)) == NULL )
        die( "Cannot allocate  memory for select descriptor." );
    

    select_dp->N = size;

    /*
     * try to describe into this structure
     */

    EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
    /* 
     * no selected columns, return...
     */

    if ( !select_dp->F ) return 0;
    /*
     * More then "size" selected columns, need to reallocate sqlda and try 
     * again
     */

    if (select_dp->F < 0)
    {
        size = -select_dp->F;
        sqlclu( select_dp );
        if ((select_dp = sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN)) == NULL )
            die( "Cannot allocate  memory for select descriptor." );
        EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
    }
    select_dp->N = select_dp->F;
    /* 
     * Ok, for each column allocate indicator variables.  Each select_dp->I[i]
     * will point to an ARRAY of shorts -- we will fetch array_size rows 
     * hence our array will be array_size shorts in length 
     */

    for (i = 0; i < select_dp->N; i++) 
        select_dp->I[i] = (short *) malloc(sizeof(short) * array_size );

    /*
     * now, lets set up each column.  This program will coerce ALL datatypes
     * into null terminated strings of some length.  Since all we want to 
     * do is print the values, this is OK
     */

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

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

        /* 
         * if the type is in the bounds of my array above, we will 
         * attempt to get a length for that type from the array.  So
         * the oracle datatype 2 (number) will get the length of 45, 45
         * bytes is big enough to hold the largest number Oracle can 
         * hold plus the sign, decimal place, etc
         *
         * if the length is not in the array, we add 5 for good luck 
         * -- add 5 to allow for null termination
         */
        if ( select_dp->T[i] < sizeof(lengths)/sizeof(lengths[0]) )
        {
            if ( lengths[select_dp->T[i]] )
                 select_dp->L[i]  = lengths[select_dp->T[i]];
            else select_dp->L[i] += 5;
        }
        else select_dp->L[i] += 5;

        /*
         * set the datatype for the fetched column to be 5 (null terminated
         * string) 
         *
         * set the pointer to where the data goes to be be a pointer to 
         * some space that is that LENGTH * number of rows to fetch in size
         */
        select_dp->T[i] = 5;
        select_dp->V[i] = (char *)malloc( select_dp->L[i] * array_size );

        /* 
         * print out the column names.....
         */
        for( j = MAX_VNAME_LEN-1; j > 0 && select_dp->S[i][j] == ' '; j--);
        printf ("%s%.*s", i?",":"", j+1, select_dp->S[i]);
    }
    printf( "\n" );
    /* 
     * now we will open the cursor and start fetching rows...
     */

    EXEC SQL OPEN C;
    for ( last_fetch_count = 0; ; last_fetch_count = sqlca.sqlerrd[2] )     {
        /* 
         * for how many rows we have allocated space for, lets try 
         * to fetch that many....
         */
        EXEC SQL FOR :array_size FETCH C USING DESCRIPTOR select_dp;
        printf( "Apparently fetched %d rows this fetch...\n",
                 sqlca.sqlerrd[2]-last_fetch_count );

        /*
         * sqlca.sqlerrd[2], after the fetch, has the cumulative number
         * of rows the cursor has fetched so far.  So we can use that
         * to figure out how many rows the last fetch actually fetched...
         */
        for( j = 0; j < sqlca.sqlerrd[2]-last_fetch_count; j++ )
        {
            printf( "%d ) ", last_fetch_count+j+1 );
            for (i = 0; i < select_dp->F; i++)
            {
                /*
                 * Now we do some 'tricky' pointer accesses.  We have just
                 * fetched some number of rows.  We want to print the i'th
                 * column of the j'th row we fetched.
                 *
                 * the indicator variable is in the array pointed to by 
                 * select_dp->I[i] (the i'th column).  It is in cell j of 
                 * that row.  Hence the indicator variable is:
                 */
                ind_value = *(select_dp->I[i]+j);

                /*
                 * the data pointer of the I'th column of the J'th row
                 * is indexed in a similar fashion but we must add
                 * the 'width' of each column in addition to the row (j)
                 * we want.... so the data pointer is:
                 */
                char_ptr  = select_dp->V[i] + (j*select_dp->L[i]);

                /* 
                 * now print it...
                 */
                printf( "%s%s", i?",":"", ind_value?"(null)":char_ptr );
            }
            row_count++;
            printf( "\n" );
        }
        /*
         * when the sqlcode is positive, that indicates NO MORE DATA, break
         */
        if ( sqlca.sqlcode > 0 ) break;

    }
    printf( "%d rows\n", row_count );

    sqlclu(select_dp);

    EXEC SQL CLOSE C;     EXEC SQL COMMIT WORK;
    return 1;
}  

main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50];
EXEC SQL END DECLARE SECTION;     process_parms( argc, argv );

    /* Connect to ORACLE. */
    vstrcpy( oracleid, USERID );

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

    EXEC SQL CONNECT :oracleid;
    printf("\nConnected to ORACLE as user: %s\n\n", oracleid.arr);

    process( SQLSTMT );

    /* Disconnect from ORACLE. */
    EXEC SQL COMMIT WORK RELEASE;
    exit(0);
}  

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 Sat Jul 18 1998 - 23:24:12 CEST

Original text of this message