Re: Pro*C PREPARE/EXECUTE help needed
Date: Thu, 13 Jan 2000 08:04:29 -0500
Message-ID: <387DCD5D.AC456AC3_at_Unforgetable.com>
EA wrote:
>
> I've tried to select a single row into host variables using a prepared
> statement WITHOUT using a cursor (Pro*C/C++: Release 8.0.4.0.0 on Solaris
> 2.6):
>
> EXEC SQL PREPARE mystatement FROM "SELECT somefield FROM sometable WHERE
> sometablekey = :mykeyvalue";
> EXEC SQL EXECUTE mystatement USING :mykeyvalue INTO :myvar;
>
> I know that exactly one row should be returned (selection by primary key) so
> I want to avoid the overhead of opening a cursor, fetching, and then
> closing/freeing the cursor for performance reasons, but the precompiler
> doesn't seem to like that:
>
> PCC-S-02201, Encountered the symbol "INTO" when expecting one of the
> following:
> ; , : ( [ . ++ -- -> indicator,
>
> Strangely enough though, doing the same without preparing the statement
> first works just fine:
>
> EXEC SQL SELECT somefield INTO :myvar INDICATOR :myvar_ind FROM sometable
> WHERE sometablekey = :mykeyvalue;
>
> Has anyone managed to do this before? I know this works for other RDBMS's
> like Informix. In essence, why would I go through the trouble of preparing
> my sql statements (which are going to be called thousands of times with
> varying parameters) for performance reasons when I have to open a cursor
> each time to select a single row?
First of all, if you are going to use Pro-C dynamic sql you you must follow the rules as published in Oracle documentation. You can't just decide that you want to do something a particular way and then expect the pre-compiler to accomodate something which it was not designed to do.
Secondly, every time you perform a SELECT it involves a cursor. There is absolutely no way around that because cursors are the only means whereby data can be extracted from an Oracle table. Cursors can be distinguished as either explicit or implicit, but just because an implicit cursor is used (which is what you apparently tried to do), it doesn't mean that the open/fetch/close is not perform - it is just done without the need for granular control in program statements.
Go back to the PRO-C documentation for Dynamic sql and read about how to use the various methods. Your code must conform to what Oracle has published.
.................................................... .................................................... .................................................... .................................................... .................................................... .................................................... .................................................... .................................................... .................................................... .................................................... .................................................... .................................................... .................................................... .................................................... .................................................... .................................................... .................................................... .................................................... .................................................... .................................................... .................................................... ....................................................Received on Thu Jan 13 2000 - 14:04:29 CET