Re: EXEC SQL <action> and EXEC SQL EXECUTE IMMEDIATE :<host of action>

A copy of this was sent to Alex Vinokur <alexander.vinokur_at_telrad.co.il> (if that email address didn't require changing) On Tue, 26 Oct 1999 07:14:35 GMT, you wrote:

>Thank you very much.
>It works OK.
>
>See more questin below.
> Thanks in advance,
> Alex
>
>In article <m4gUOPPsWblNvXuTFdtMHAdpaM=0_at_4ax.com>,
> tkyte_at_us.oracle.com wrote:
>[snip]
>>
>> static int process( char * tname )
>> {
>> EXEC SQL BEGIN DECLARE SECTION;
>> VARCHAR stmt[255];
>> EXEC SQL END DECLARE SECTION;
>> int rc;
>>
>> EXEC SQL WHENEVER SQLERROR CONTINUE;
>>
>> sprintf( stmt.arr, "select * from %s", tname );
>> stmt.len = strlen(stmt.arr);
>>
>> EXEC SQL PREPARE S FROM :stmt;
>
> //=============================================
> // 1. I suppose S is the S Variable of SQLDA
> //

No, S is just a name i used. I am preparing a statement. S was short for statement.

> // 2. However,
> // EXEC SQL PREPARE zzz FROM :stmt;
> // also works OK
> // (sqlca.sqlcode = -942 : "table or view does not exist")
> // Do we have to use the S Variable of SQLDA
> // in EXEC SQL PREPARE above?
> //

No, use whatever you like.

> // 3.
> // -------------------------------------------------
> // Pro*C/C++ Precompiler Programmer's Guide
> // Release 8.0
> // A58233-01
> // F. Embedded SQL Commands and Directives
> // Page#F-61
> // --------------------------------------------------
> // <quote>
> // This example illustrates the use of a PREPARE statement in
>a // Pro*C/C++ embedded SQL program:
> // EXEC SQL PREPARE my_statement FROM :my_string;
> // EXEC SQL EXECUTE my_statement;
> // </quote>
> //
> // However, using
> // EXEC SQL PREPARE zzz FROM :stmt;
> // EXEC SQL EXECUTE zzz;
> // I have got sqlca.sqlcode = -1003 :
> // "no statement parsed""
> //

you do not show what is in :stmt but it is probably a bad query. consider:

static int process( void )
{
EXEC SQL BEGIN DECLARE SECTION;
    VARCHAR stmt[255];
EXEC SQL END DECLARE SECTION;     EXEC SQL WHENEVER SQLERROR CONTINUE;     sprintf( stmt.arr, "delete from emp where 1=0" );     stmt.len = strlen( stmt.arr );

    exec sql prepare s from :stmt;
    exec sql execute s;
    printf( "executed delete %d\n", sqlca.sqlcode );

    sprintf( stmt.arr, "select * from emp" );     stmt.len = strlen( stmt.arr );

    exec sql prepare s2 from :stmt;
    exec sql execute s2;
    printf( "executed select %d\n", sqlca.sqlcode );

    sprintf( stmt.arr, "total garbage" );     stmt.len = strlen( stmt.arr );

    exec sql prepare s2 from :stmt;
    exec sql execute s2;
    printf( "executed garbage %d\n", sqlca.sqlcode ); }

when executed produces the following output:

executed delete 1403
executed select 0
executed garbage -1003

the delete gets 1403 no data found (where 1=0, no rows deleted). the select gets 0 (but it is MEANINGLESS TO EXECUTE A SELECT without an into!!!) the total garbage gets -1003 cause there is no statement parsed -- it was a bad sql statement to begin with.

> // Using
> // EXEC SQL PREPARE S FROM :stmt;
> // EXEC SQL EXECUTE S;
> // I have got the same result (sqlca.sqlcode = -1003)
> //
> // What is wrong?
> //=============================================
>
>> rc = sqlca.sqlcode;
>>
>> EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
>>
>> return !(rc == 0);
>> }
>[snip]
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... 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 Tue Oct 26 1999 - 08:14:54 CDT