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 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.

From the Pro*C guide as well:

<quote>
Method 1

This method lets your program accept or build a dynamic SQL statement, then immediately execute it using the EXECUTE IMMEDIATE command. The SQL statement must not be a query (SELECT statement) and must not contain any placeholders for input host variables. For example, the following host strings qualify:

’DELETE FROM EMP WHERE DEPTNO = 20’ ’GRANT SELECT ON EMP TO scott’

With Method 1, the SQL statement is parsed every time it is executed. </quote>

>
> Thanks,
> Alex
>
>//#########################################################
>//------------------- 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
>
>//---------------------------------------------------------
>
>//#########################################################
>
>
>
>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 Sun Oct 24 1999 - 09:10:09 CDT