Conference orarep::nomahs::rdb_wish

809.0. "dcl $status and interactive sql " by M5::JHAYTER () Fri May 30 1997 13:19

Based on the following bug response, customer asked for wish for some type
of symbol to be set in order to tell if the last "verb" was successful
in interactive sql that they could get at from dcl.  sql$status for a name?
This request will allow checking a symbol to determine if an sql statement
was successful when sql is called from dcl.  Currently, the commit done for
the user changes $status to successful.


      BugNo: 379291                                      Component: RDBSQL      
     Client: UNIV OF CALIFORNIA RIVERSIDE                  Version: 6.0         

                  $STATUS NOT SET WHEN RDMS-F-LCKCNFLCT OCCURS                  
                                                                                
see rdb 60 note file entry #4238                                                
to reproduce create command file as follows:                                    

$ on sever_error then continue                                                  
$sql                                                                            
set verify                                                                      
set transaction read write nowait;                                              
create index boo on employees (sex);                                            
$ a = $status                                                                   
$ show symbol a                                                                 

then, with sql$database defined to your favorite personnel database do:         

 set transaction read write reserving employees for exclusive write;            
 $spawn @"the above command file"                                               
--                                                                              
$status will be set to "%X10000001" instead of the appropiate value for a lock  
conflict.                                                                       

bug response..

This is the correct action. When a transaction is active the status returned    
is the status of the attempted commit, not the status of the last statement     
executed. Here is an exerpt from the Guide to SQL Programming:                  
   "In interactive SQL, the completion status is the status of the last         
    statement prior to the EXIT statement, unless a transaction is active.      
    In that case, SQL attempts to commit the transaction and the completion     
    status is the status of the attempted COMMIT statement."                    
If there are further questions please let us know, otherwise this BUG can       
be closed.                                                                      

The correct way to handle this now is to use dynamic SQL and manage the status
yourself.  We even ship a dynamic SQL application which could be modified to
do what you want.

As far as a wish, I think what is wanted is an ON ERROR THEN EXIT statement in
interactive SQL.  This would (as a side effect) set the status of the last
statement executed.  A rollback would probably be done too.  But wishes like
this are always so low on the list that customers should look for different
solutions (such as that suggested above).

Ian