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

From: Alex Vinokur <alexander.vinokur_at_telrad.co.il>
Date: Mon, 25 Oct 1999 05:48:37 GMT
Message-ID: <7v0qvl$gcq$1@nnrp1.deja.com>

In article <pRATOASEIFsGIPvoGDsKuv9w50Bs_at_4ax.com>,   tkyte_at_us.oracle.com wrote:
> A copy of this was sent to Alex Vinokur
<alexander.vinokur_at_telrad.co.il>
> (if that email address didn't require changing)
> On Sun, 24 Oct 1999 10:32:21 GMT, you wrote:
>
> >Hi,
> >
> >I have got the different results of the running
> >when using
> > 1. EXEC SQL <action>
> > (see test1)
> >
> > and
> >
> > 2. EXEC SQL BEGIN DECLARE SECTION;
> > const char *host_action_line = "<action>"
> > EXEC SQL END DECLARE SECTION;
> >
> > EXEC SQL EXECUTE IMMEDIATE :host_action_line;
> >
> >
> > Is anything wrong?
> >
> > Do both EXEC SQL <action> EXEC SQL and EXECUTE IMMEDIATE
> >:host_action_line
> > have to produce the same result?
> >
>
> They are different AND you are using them to do a select which does
not make any
> sense.
>
> You would "EXEC SQL INSERT INTO T values ...." and that would make
sense because
> an insert doesn't 'return' anything.
>
> You could:
> strcpy( host_var, "creat table t ( x int )" );
> exec sql execute immediate :host_var;
>
> and that would make sense for the same reason.
>
> It doesn't make 'sense' to "EXEC SQL SELECT * FROM T" -- where does
the output
> go?
>
> you would:
>
> EXEC SQL SELECT X INTO :my_var FROM T;
>
> and that would make sense.
>

[snip]

---

I dont't need any return value im my example. I would like only to check sqlca.sqlcode   to know if my database contains table "KKK123". I don't neeed any return value except sqlca.sqlcode. However behavior of my function (test1 and test2) is different:

   EXEC SQL SELECT <action> causes sqlca.sqlcode to be 1403 (not found)    EXEC SQL EXECUTE IMMEDIATE <the same action> causes sqlca.sqlcode to be 0 (no errors).

How can we get the same result using

   EXEC SQL SELECT <action> and
   EXEC SQL EXECUTE IMMEDIATE <the same action>

> >
> >//#########################################################
> >//------------------- Pro*C++ code : BEGIN ----------------
> >
> >//==========================
> >#include <assert.h>
> >#include <iostream.h>
> >//==========================
> >#include <sqlca.h>
> >#include <oraca.h>
> >
> >//=========================================
> >void sql_error_action ()
> >{
> > assert (0);
> >}
> >
> >//=========================================
> >void test1()
> >{
> > cout << endl << "Test#1" << endl;
> > EXEC SQL WHENEVER NOT FOUND GOTO case_error;
> > EXEC SQL SELECT TABLE_NAME FROM USER_TABLES WHERE
> >TABLE_NAME='KKK123';
> > cout << "OK : sqlca.sqlcode == " << sqlca.sqlcode << endl;
> > return;
> >
> > case_error :
> > cout << "FAULT : sqlca.sqlcode == " << sqlca.sqlcode
<<
> >endl;
> > return;
> >
> >
> >} // void test1()
> >
> >//=========================================
> >void test2()
> >{
> > cout << endl << "Test#2" << endl;
> >
> >EXEC SQL BEGIN DECLARE SECTION;
> >const char *host_action_line = "SELECT TABLE_NAME FROM
USER_TABLES
> >WHERE TABLE_NAME='KKK123'";
> >EXEC SQL END DECLARE SECTION;
> >
> > EXEC SQL WHENEVER NOT FOUND GOTO case_error;
> > EXEC SQL EXECUTE IMMEDIATE :host_action_line;
> > cout << "OK : sqlca.sqlcode == " << sqlca.sqlcode << endl;
> > return;
> >
> > case_error :
> > cout << "FAULT : sqlca.sqlcode == " << sqlca.sqlcode
<<
> >endl;
> > return;
> >
> >
> >} // void test2()
> >
> >//===============================
> >int main ()
> >{
> >EXEC SQL BEGIN DECLARE SECTION;
> >char *username = "aaa";
> >char *password = "bbb";
> >EXEC SQL END DECLARE SECTION;
> >
> > //===========================
> > EXEC SQL WHENEVER SQLERROR DO sql_error_action ();
> > EXEC SQL CONNECT :username IDENTIFIED BY :password;
> >
> > //===========================
> > test1 ();
> > test2 ();
> >
> > return 0;
> >}
> >
> >
> >//------------------- Pro*C++ code : END ------------------
> >
> >
> >
> >
> >
> >//#########################################################
> >//-------------- Results of the Running : BEGIN -----------
> >
> >Test#1
> >FAULT : sqlca.sqlcode == 1403
> >
> >Test#2
> >OK : sqlca.sqlcode == 0
> >
> >
> >//-------------- Results of the Running : END -------------
> >
> >
> >//#########################################################
> >//------------------- Environment -------------------------
> >
> >=== Oracle 8.0.5
> >=== Pro*C/C++ : Release 8.0.5.0.0
> >=== CC: WorkShop Compilers 4.2 30 Oct 1996 C++ 4.2
> >=== SunOS 5.6
> >
> >//---------------------------------------------------------
> >
> >//#########################################################

[snip]

        Alex

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Oct 25 1999 - 00:48:37 CDT