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

In article <pRATOASEIFsGIPvoGDsKuv9w50Bs@4ax.com>, tkyte@us.oracle.com wrote: > A copy of this was sent to Alex Vinokur <alexander.vinokur@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.

Hi, I have got several questions concerning - sqlca.sqlcode - EXEC SQL <action> and - EXEC SQL EXECUTE IMMEDIATE <action> ============================================== EXEC SQL <action> // for instance, SELECT sqlca.sqlcode detects both errors and exceptions (negative and positive return codes) of SELECT action. ============================================== EXEC SQL EXECUTE IMMEDIATE <action, for instance, the same SELECT> 1. sqlca.sqlcode detects errors (negative return codes) of IMMEDIATE (IMMEDIATE, not SELECT - is it true?) action 2. Can sqlca.sqlcode detect errors of SELECT action which are not errors of IMMEDIATE action? 3. Can sqlca.sqlcode detect exceptions (positive return codes) of IMMEDIATE action? ---------------------------- 4. Can sqlca.sqlcode detect exceptions of SELECT action which are not exceptions of IMMEDIATE action? (That is what I want to get). ---------------------------- Thanks in advance, Alex ################################################# Pro*C/C++ Precompiler Programmer's Guide Release 8.0 A58233-01 ################################################# 11 Handling Runtime Errors [snip] Oracle updates the SQLCA after every executable SQL statement. (SQLCA values are unchanged after a declarative statement.) By checking Oracle return codes stored in the SQLCA, your program can determine the outcome of a SQL statement. This can be done in the following two ways: * implicit checking with the WHENEVER statement * explicit checking of SQLCA components You can use WHENEVER statements, code explicit checks on SQLCA components, or do both. The most frequently-used components in the SQLCA are the status variable (sqlca.sqlcode), and the text associated with the error code (sqlca.sqlerrm.sqlerrmc). [snip] When more information is needed about runtime errors than the SQLCA provides, you can use the ORACA. The ORACA is a C struct that handles Oracle communication. It contains cursor statistics, information about the current SQL statement, option settings, and system statistics. [snip] Status Codes Every executable SQL statement returns a status code to the SQLCA variable sqlcode, which you can check implicitly with the WHENEVER statement or explicitly with your own code. A zero status code means that Oracle executed the statement without detecting an error or exception. A positive status code means that Oracle executed the statement but detected an exception. A negative status code means that Oracle did not execute the SQL statement because of an error. [snip] sqlcode This integer component holds the status code of the most recently executed SQL statement. The status code, which indicates the outcome of the SQL operation, can be any of the following numbers : 0 Means that Oracle executed the statement without detecting an error or exception. >0 Means that Oracle executed the statement but detected an exception. This occurs when Oracle cannot find a row that meets your WHERE-clause search condition or when a SELECT INTO or FETCH returns no rows. When MODE=ANSI, +100 is returned to sqlcode after an INSERT of no rows. This can happen when a subquery returns no rows to process. <0 Means that Oracle did not execute the statement because of a database, system, network, or application error. Such errors can be fatal. When they occur, the current transaction should, in most cases, be rolled back. Negative return codes correspond to error codes listed in Oracle8 Error Messages. [snip] You code the WHENEVER statement using the following syntax: EXEC SQL WHENEVER <condition> <action>; Conditions You can have Oracle automatically check the SQLCA for any of the following conditions. SQLWARNING sqlwarn[0] is set because Oracle returned a warning (one of the warning flags, sqlwarn[1] through sqlwarn[7], is also set) or SQLCODE has a positive value other than +1403. For example, sqlwarn[0] is set when Oracle assigns a truncated column value to an output host variable. Declaring the SQLCA is optional when MODE=ANSI. To use WHENEVER SQLWARNING, however, you must declare the SQLCA. SQLERROR SQLCODE has a negative value because Oracle returned an error. NOT FOUND SQLCODE has a value of +1403 (+100 when MODE=ANSI) because Oracle could not find a row that meets your WHERE-clause search condition, or a SELECT INTO or FETCH returned no rows. When MODE=ANSI, +100 is returned to SQLCODE after an INSERT of no rows. [snip] ################################################# Sent via Deja.com http://www.deja.com/ Before you buy.