Re: dynamic sql with Pro*C/WinNT 4.0

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/04/11
Message-ID: <33569b49.11292477_at_newshost>


[Quoted] Your missing the allocation of lots of memory for the descriptor. You must allocate one, allocate space for each of the output variables, decide what datatype you want results in and so on.

I'm attaching an example. 2 of the functions referenced by this sample are not included. 'die' simply logs an error and exits. Get_Arglist( bind_variable_name ) is used to return a value for a given name (i had parsed a URL and had an array of VALUE=VARIABLE pairs. Get_Arglist("foo") would return the value for 'foo'.)

Anyway, This piece of code will accept an arbitrary string, could be a pl/sql block , insert/update/delete, select whatever. It prepares it, binds any inputs for it, executes it and

  • if it was Pl/sql, it 'gets the page' from the database. This is used with the Oracle Web Server pl/sql toolkit.
  • if it was a select statement, it prints it out in various formats.

Hope this helps...

On 11 Apr 1997 19:44:28 GMT, nothing_at_cc.gatech.edu (nothing) wrote:

>I'm trying to get some dynamic sql working under Pro*C 2.1.2.0.3 in WinNT
>4.0 -- I'm trying to build a dynamic cursor based on user input from a web
>page. If anyone can help with this snippet of code, I would be very
>grateful. According to the book I have, this is all I need to build the
>cursor and retrieve rows... and of course it doesn't work.
>
>void aFunction()
>{
> EXEC SQL BEGIN DECLARE SECTION;
> char query[101];
> EXEC SQL END DECLARE SECTION;
>
> SQLDA *descr;
>
> /* database routines to connect... already tested and working */
> /* string input gets placed in query */
>
> EXEC SQL PREPARE S1 FROM :query;
> EXEC SQL DECLARE C1 CURSOR FOR S1;
> EXEC SQL OPEN C1 USING DESCRIPTOR descr;
> EXEC SQL DESCRIBE SELECT LIST FOR S1 INTO descr;
> EXEC SQL FETCH C1 USING DESCRIPTOR descr;
> EXEC SQL CLOSE C1;
>}
>
>
>
>--
> | "human junk just words and so much skin
>nothing_at_cc.gatech.edu | stick my hands thru the cage of this endless routine
> | just some flesh caught in this big broken machine"
> | -nin, "happiness in slavery"

#include <stdio.h>
[Quoted] #include <string.h>

#define MAX_VNAME_LEN     30
#define MAX_INAME_LEN     30

#define SQLCA_STORAGE_CLASS extern
EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;

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

EXEC SQL WHENEVER SQLERROR CONTINUE; static int sqlerror_soft( char * stmt, int lineno ) {
char msg[255];
int x = sqlca.sqlcode;

    EXEC SQL WHENEVER SQLERROR CONTINUE;     EXEC SQL ROLLBACK WORK;     printf( "%d<br><pre>%s</pre><br>", lineno, stmt );     printf( "\n<b><i>Oracle Error Detected:\n% .70s \n</i></b>",

            sqlca.sqlerrm.sqlerrmc);

    return sqlca.sqlcode = x;
}

int do_block( char * plsql_block, int display_type ) {

VARCHAR        data[27][256];
int            ndata = 27;
int            i;

    EXEC SQL PREPARE S FROM :plsql_block;     if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block, __LINE__);

    EXEC SQL DECLARE C CURSOR FOR S;
    if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block,__LINE__);

    set_bind_variables(plsql_block);
    if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block,__LINE__);

    if ( !process_select_list(display_type,plsql_block) )     {

        for( ndata = 27; !sqlca.sqlcode; ndata = 27 ) 
        {
            for( i = 0; i < ndata; data[i].len = 256, i++ );
            EXEC SQL EXECUTE 
            BEGIN
                OWA.GET_PAGE( :data, :ndata );
            END;
            END-EXEC;
            if ( sqlca.sqlcode ) return
sqlerror_soft(plsql_block,__LINE__);
            if ( !ndata ) break;
            for( i = 0; i < ndata; i++ )
                printf( "%.*s", data[i].len, data[i].arr );
        }

    }
    EXEC SQL CLOSE C;
    if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block,__LINE__);

    EXEC SQL COMMIT WORK;
    if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block,__LINE__); }

set_bind_variables(char * plsql_block)
{

int        size = 10;
int        i;

char bvname[MAX_VNAME_LEN+5];
SQLDA * bind_dp;

    if ((bind_dp = sqlald(size, MAX_VNAME_LEN, MAX_INAME_LEN)) == NULL)
        die( "Cannot allocate memory for bind descriptor" );

    bind_dp->N = size;
    EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;     if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block,__LINE__);

    if (bind_dp->F < 0)
    {

        size = -bind_dp->F;
        sqlclu(bind_dp);
        if ((bind_dp = sqlald(size, MAX_VNAME_LEN, MAX_INAME_LEN)) ==
NULL)
            die( "Cannot allocate memory for bind descriptor" );
    
        bind_dp->N = size;  /* Initialize count of array elements. */
        EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;
        if ( sqlca.sqlcode ) return

sqlerror_soft(plsql_block,__LINE__);

    }

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

        bind_dp->I[i] = (short *) malloc(sizeof (short));
       

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

        strncpy( bvname, bind_dp->S[i], bind_dp->C[i] );
        bvname[bind_dp->C[i]] = 0;
        bind_dp->V[i] = (char *)get_arglist( bvname );
        bind_dp->L[i] = strlen(bind_dp->V[i]);
        *bind_dp->I[i] = (*bind_dp->V[i]) ? 0 : -1;
        if ( *bind_dp->I[i] )
        {
            bind_dp->V[i] = (char *)malloc(5);
            bind_dp->L[i] = 4;
        }
        bind_dp->T[i] = 1;

    }
    EXEC SQL OPEN C USING DESCRIPTOR bind_dp;     if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block,__LINE__);

    for( i = 0; i < bind_dp->N; i++ ) free( bind_dp->I[i] );     sqlclu( bind_dp );
}

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 };

int process_select_list(int display_type, char * plsql_block) {
SQLDA * select_dp;

int     i;
int        null_ok;
int        precision;
int        scale;
int        size = 10;

    if ((select_dp = sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN)) == NULL )
        die( "Cannot allocate memory for select descriptor." );     

    select_dp->N = size;

    EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;     if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block,__LINE__);     if ( !select_dp->F ) return 0;

    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;
        if ( sqlca.sqlcode ) return
sqlerror_soft(plsql_block,__LINE__);
       } 

    select_dp->N = select_dp->F;

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

        select_dp->I[i] = (short *) malloc(sizeof(short));

    switch(display_type)
    {

        case 'T': printf( "<table border=1>" ); break;
        case 'L': printf( "<ul>" ); break;
    }
    for (i = 0; i < select_dp->F; i++)
    {

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

        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;

        select_dp->T[i] = 5;
        select_dp->V[i] = (char *)malloc( select_dp->L[i] );

        if (display_type == 'T' ) 
            printf ("<th>%.*s</th>", MAX_VNAME_LEN, select_dp->S[i]);
    }

    for (;;)
    {

        EXEC SQL WHENEVER NOT FOUND DO break;
        EXEC SQL FETCH C USING DESCRIPTOR select_dp;
        if ( sqlca.sqlcode ) return
sqlerror_soft(plsql_block,__LINE__);
        if ( display_type == 'T' ) printf( "<tr>" );
        for (i = 0; i < select_dp->F; i++)
            printf( "%s%s%s%s",
                    (display_type=='T')?"<td>":"",
                    (display_type=='L')?(i?", ":"<li>"):"",
                    *(select_dp->I[i])?"(null)":select_dp->V[i],
                    (display_type=='T')?"</td>":"" );
        if ( display_type == 'T' ) printf( "</tr>\n" );
    }
    switch(display_type)
    {
        case 'T': printf( "</table border=1>" ); break;
        case 'L': printf( "</ul>" ); break;
    }
    for( i = 0; i < select_dp->F; i++ )
    {
        free( select_dp->I[i] );
        free( select_dp->V[i] );

    }
    sqlclu(select_dp);

    return 1;
}

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

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



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Apr 11 1997 - 00:00:00 CEST

Original text of this message