Re: Pro*C - confusion about Dynamic SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 20 Dec 1999 08:07:58 -0500
Message-ID: <kt9s5sstmvld2qegs6ihi14onkhc7ob8fi_at_4ax.com>


A copy of this was sent to cjeastwd_at_powerup.com.au (Clint Eastwood) (if that email address didn't require changing) On Mon, 20 Dec 1999 06:46:40 GMT, you wrote:

>HiYa
>
>I was wanting to declare a cursor differently depending on passed input,
>and so I thought that I would prepare and declare. I was reading the
>manual to confrim my syntax, and I found this statment, which I don't
>understand, although I don't doubt.
>
>
>If you declare two cursors using the same statement name, the
>precompiler considers the two cursor names synonymous.
>
>For example, if you execute the statements
>
>EXEC SQL PREPARE sql_stmt FROM :select_stmt;
>EXEC SQL DECLARE emp_cursor FOR sql_stmt;
>
>EXEC SQL PREPARE sql_stmt FROM :delete_stmt;
>EXEC SQL DECLARE dept_cursor FOR sql_stmt;
>
>I would have thought that this would be fine, and keep the 2 seperate
>strings, as 2 seperate cursor defintions.
>
>My questions are
>
>1 is this because the precompiler happens (as the name suggests) pre run
>time, that it sets up the same sqlca pointers for each, we can't
>consider that these are done dynamicaly?
>
>2 does this have reprocussions for other prepare/declare statements
>within different functions??
>
In the same source code file -- yes.
Across files -- no.

Consider this single source code file example:

#include <stdio.h>

#define SQLCA_INIT
EXEC SQL INCLUDE sqlca;

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 void sqlerror_soft()
{

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

static void process1_open()
{
char sqlstmt[255];
exec sql var sqlstmt is string(255);

    strcpy( sqlstmt, "select 'A' from dual" );     EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     EXEC SQL PREPARE STMT FROM :sqlstmt;     EXEC SQL DECLARE A_CURS CURSOR FOR STMT;     EXEC SQL OPEN A_CURS;
    printf( "opened %s\n", sqlstmt );
}

static void process1_fetch()
{
char data[40];
exec sql var data is string(40);

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     EXEC SQL FETCH A_CURS INTO :data;
    printf( "process1: %s\n", data );
}

static void process1_close()
{

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     EXEC SQL CLOSE A_CURS;
    printf( "closed in process1\n" );
}

static void process2_open()
{
char sqlstmt[255];
exec sql var sqlstmt is string(255);

    strcpy( sqlstmt, "select 'B' from dual" );     EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     EXEC SQL PREPARE STMT FROM :sqlstmt;     EXEC SQL DECLARE B_CURS CURSOR FOR STMT;     EXEC SQL OPEN B_CURS;
    printf( "opened %s\n", sqlstmt );
}

static void process2_fetch()
{
char data[40];
exec sql var data is string(40);

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     EXEC SQL FETCH B_CURS INTO :data;
    printf( "process2: %s\n", data );
}

static void process2_close()
{

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     EXEC SQL CLOSE B_CURS;
    printf( "closed in process2\n" );
}
main( argc, argv )
int argc;
char * argv[];
{
char * oracleid = "tkyte/tkyte";
exec sql var oracleid is string(50);

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     EXEC SQL CONNECT :oracleid;

    printf( "Calling process1 and then process2 opens...\n" );     process1_open();
    process2_open();

    process1_fetch();
    process2_fetch();

    process1_close();
    process2_close();

    printf( "\n\n\nCalling process2 and then process1 opens...\n" );     process2_open();
    process1_open();

    process1_fetch();
    process2_fetch();

    process1_close();
    process2_close();

    EXEC SQL COMMIT WORK RELEASE;
    exit(0);
}

It will produce as output:

$ ./single_file

Calling process1 and then process2 opens... opened select 'A' from dual
opened select 'B' from dual
process1: B
process2: B
closed in process1
closed in process2

Calling process2 and then process1 opens... opened select 'B' from dual
opened select 'A' from dual
process1: A
process2: A
closed in process1
closed in process2

the cursors are the 'same' -- you in effect have a copy of each cursor. OTOH, if you create three source code files

  • main.pc with the main (remove the static from sqlerror_hard)
  • process1.pc with the process1_* functions (remove the static)
  • process2.pc with the process2_* functions (remove the static)

and rebuild it, you'll get:

$ ./separate
Calling process1 and then process2 opens... opened select 'A' from dual
opened select 'B' from dual
process1: A
process2: B
closed in process1
closed in process2

Calling process2 and then process1 opens... opened select 'B' from dual
opened select 'A' from dual
process1: A
process2: B
closed in process1
closed in process2

which shows the statements are not shared across files.

In general, in a source code file (the scope of pro*c is global to a FILE, never across files and it never obeys C's scope with respect to EXEC SQL directives) make sure your cursor names, statement names, etc are unique.

>
>
>thanks :-)

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
[Quoted] Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Mon Dec 20 1999 - 14:07:58 CET

Original text of this message