In a pgc code, I do a kind of:
exec sql begin work;
exec sql prepare my_rqt from :rqt_insert ;
exec sql execute my_rqt using :foo, :bar ;
if ( sqlca.sqlcode == 0 ){
exec sql deallocate prepare my_rqt ;
exec sql commit ;
} else {
exec sql deallocate prepare my_rqt ;
exec sql rollback ;
}
when insert works, everything is fine, but, in the othercase, I get an error on the 'deallocate' statement :
sqlca.sqlstate : 26000 : invalid statement "my_rqt" on line xx
If I do deallocate after rollback, I don't get this message. I can't find any sample code of this in any documentation.
Thanks for any help on the right order to do such things.
Once you get an error, the transaction is aborted and you cannot do anything before you roll back.