Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: EXEC SQL <action> and EXEC SQL EXECUTE IMMEDIATE :<host of action>

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

From: Alex Vinokur <alexander.vinokur_at_telrad.co.il>
Date: Thu, 28 Oct 1999 08:52:47 GMT
Message-ID: <7v92su$f44$1@nnrp1.deja.com> 





In article <6aYVOMioILgFvS0vpl4JxUewWzDd_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 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;


[snip]


> >  // 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:



[snip]



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





[snip]




    
  
  1.   Thanks for your answers.
   Things became clearer.

  
  2.   (I think) Now I know why sqlca.sqlcode = -1003 was generated
   in my program.







Here are







Using the process1 function causes irrelevant indication.

        It was something like process1
        that I used to get response {"exists", "does not exist"}

        EXEC SQL WHENEVER SQLERROR CONTINUE;    // because of CONTINUE
        EXEC SQL PREPARE S1 FROM :stmt;
        EXEC SQL EXECUTE S1;
        rc = sqlca.sqlcode;

-------------------------
Using the process2 function causes relevant indication.
        If we want only to get answer {"exists", "does not exist"}
        (without selecting), we don't have to use
        EXEC SQL EXECUTE.

-------------------------
If we want to get answer {"exists", "does not exist"} and
        to use EXEC SQL EXECUTE
        we have to use the process3 (not process1) function.


Using the process3 function causes relevant indication.
However, if we want to use EXEC SQL EXECUTE we have to be aware that


        "it is MEANINGLESS TO EXECUTE A SELECT without an into!!!"
P.S. In this example the process3 function is used only

        to get answer {"exists", "does not exist"}.
        So executes a SELECT was executed without an into.


===================================

        Any corrections and comments would be appreciated.

        Thanks in advance,
        Alex


//#########################################################
//------------------- Pro*C++ code : BEGIN ----------------





#include <iostream.h>



#include <rw/cstring.h>



#include <sqlca.h>


#include <oraca.h>


//=========================================


void sql_error_action ()


{

        cout << "ERROR : sqlca.sqlcode = "
             << sqlca.sqlcode
             << endl;




} // void sql_error_action ()



//=========================================


int process1 (const RWCString& tname_i)
{


const RWCString funcName = "process1";



RWCString tmp_str = "SELECT * FROM " + tname_i;



EXEC SQL BEGIN DECLARE SECTION;



const char      *stmt = ((const char*)tmp_str);
EXEC SQL END DECLARE SECTION;


int     rc;



        EXEC SQL WHENEVER SQLERROR CONTINUE;



        //=======================================
        EXEC SQL PREPARE S1 FROM :stmt;
        cout << "\t" << funcName << " -> ";
        cout << "AFTER PREPARE : sqlca.sqlcode = "
             << sqlca.sqlcode
             << endl;

        EXEC SQL EXECUTE S1;
        cout << "\t" << funcName << " -> ";
        cout << "AFTER EXECUTE : sqlca.sqlcode = "
             << sqlca.sqlcode
             << endl;

        rc = sqlca.sqlcode;
        //=======================================

        EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

        return (rc == 0);




} // int process1


//=========================================


int process2 (const RWCString& tname_i)
{


const RWCString funcName = "process2";



RWCString tmp_str = "SELECT * FROM " + tname_i;



EXEC SQL BEGIN DECLARE SECTION;



const char      *stmt = ((const char*)tmp_str);
EXEC SQL END DECLARE SECTION;


int     rc;



        EXEC SQL WHENEVER SQLERROR CONTINUE;



        //=======================================
        EXEC SQL PREPARE S2 FROM :stmt;
        cout << "\t" << funcName << " -> ";
        cout << "AFTER PREPARE : sqlca.sqlcode = "
             << sqlca.sqlcode
             << endl;

        rc = sqlca.sqlcode;
        //=======================================

        EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

        return (rc == 0);




} // int process2


//=========================================


int process3 (const RWCString& tname_i)
{


const RWCString funcName = "process3";



RWCString tmp_str = "SELECT * FROM " + tname_i;



EXEC SQL BEGIN DECLARE SECTION;



const char      *stmt = ((const char*)tmp_str);
EXEC SQL END DECLARE SECTION;


        EXEC SQL WHENEVER SQLERROR GOTO error_label;


        //=======================================
        EXEC SQL PREPARE S3 FROM :stmt;
        cout << "\t" << funcName << " -> ";
        cout << "AFTER PREPARE : sqlca.sqlcode = "
             << sqlca.sqlcode
             << endl;

        EXEC SQL EXECUTE S3;
        cout << "\t" << funcName << " -> ";
        cout << "AFTER EXECUTE : sqlca.sqlcode = "
             << sqlca.sqlcode
             << endl;

        //=======================================

        EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

        return 1;       // true


        //=======================================
        error_label :
                EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
                return 0;       // false


} // int process3



//===============================
#define CHECK_IT(ps, name) \
                result = ps (name); \
                cout << name \
                     << " " \
                     << (result ? "exists" : "does not exist") \
                     << "  [ sqlca.sqlcode = " \
                     << sqlca.sqlcode \
                     << " ]" \
                     << endl \
                     << endl

//===============================




int main ()


{


EXEC SQL BEGIN DECLARE SECTION;


char    *username       = "aaa";
char    *password       = "bbb";




EXEC SQL END DECLARE SECTION;


int     result;


        //===========================
        EXEC SQL WHENEVER SQLERROR DO sql_error_action ();
        EXEC SQL CONNECT :username IDENTIFIED BY :password;
        cout << endl << "Connected to ORACLE." << endl;

        //===========================
        cout << endl << "###################" << endl;
        CHECK_IT (process1, "Emp");
        CHECK_IT (process1, "Dept");
        CHECK_IT (process1, "Foobar");
        //===========================

        //===========================
        cout << endl << "###################" << endl;
        CHECK_IT (process2, "Emp");
        CHECK_IT (process2, "Dept");
        CHECK_IT (process2, "Foobar");
        //===========================


        //===========================
        cout << endl << "###################" << endl;
        CHECK_IT (process3, "Emp");
        CHECK_IT (process3, "Dept");
        CHECK_IT (process3, "Foobar");
        //===========================

        /* Disconnect from ORACLE. */
        EXEC SQL COMMIT WORK RELEASE;
        return 0;




}



//------------------- Pro*C++ code : END ------------------





//#########################################################


//-------------- Results of the Running : BEGIN -----------




Connected to ORACLE.


###################
        process1 -> AFTER PREPARE : sqlca.sqlcode = 0
        process1 -> AFTER EXECUTE : sqlca.sqlcode = 0


Emp exists  [ sqlca.sqlcode = 0 ]


        process1 -> AFTER PREPARE : sqlca.sqlcode = 0
        process1 -> AFTER EXECUTE : sqlca.sqlcode = 0


Dept exists  [ sqlca.sqlcode = 0 ]


        process1 -> AFTER PREPARE : sqlca.sqlcode = -942
        process1 -> AFTER EXECUTE : sqlca.sqlcode = -1003
Foobar does not exist  [ sqlca.sqlcode = -1003 ]
        //============
        // ATTENTION :  result is valid (not exist),
        //              sqlcode (1003 : "no statement parsed")
        //              sqlcode doesn't indicate real reason
        //============



###################
        process2 -> AFTER PREPARE : sqlca.sqlcode = 0


Emp exists  [ sqlca.sqlcode = 0 ]



        process2 -> AFTER PREPARE : sqlca.sqlcode = 0
Dept exists  [ sqlca.sqlcode = 0 ]



        process2 -> AFTER PREPARE : sqlca.sqlcode = -942
Foobar does not exist  [ sqlca.sqlcode = -942 ]

        //============
        // ATTENTION :  result is valid (not exist),
        //              sqlcode (942 : "table or view does not exist")
        //              sqlcode indicates real reason
        //============



###################
        process3 -> AFTER PREPARE : sqlca.sqlcode = 0
        process3 -> AFTER EXECUTE : sqlca.sqlcode = 0


Emp exists  [ sqlca.sqlcode = 0 ]


        process3 -> AFTER PREPARE : sqlca.sqlcode = 0
        process3 -> AFTER EXECUTE : sqlca.sqlcode = 0


Dept exists  [ sqlca.sqlcode = 0 ]



Foobar does not exist  [ sqlca.sqlcode = -942 ]

        //============
        // ATTENTION :  result is valid (not exist),
        //              sqlcode (942 : "table or view does not exist")
        //              sqlcode indicates real reason
        //============






//-------------- 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.
Received on Thu Oct 28 1999 - 03:52:47 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US