Transactions in Informix dbaccess here document approach

Question

I am writing a shell script that invokes dbaccess.

I would like to begin a transaction, do some stuff (e.g. call some procedures) and then make a decision and either commit or rollback the current work. Is this possible?

Here's an example of what I am trying to accomplish

#!/bin/bash

v_value

dbaccess $DB - << SQL

unload to "abc.csv"
select value from table1 where id=1;
SQL

IFS=$'|' arr=( $(awk -F, -v OFS='\n' '{$1=$1}1' abc.csv) )
v_value=${arr[0]}

dbaccess $DB - << SQL

begin;

execute procedure progname();

-- here check everything is ok (e.g. using the previously retrieved $v_value) and either commit or rollback
-- commit|rollback
SQL

Answer 1

Maybe you able to commit/rollback inside of a procedure for this.

But the way you wrote your script, I don't consider necessary create a procedure for that , you can solve using shell script :

#!/bin/bash

v_value=""

dbaccess $DB - << SQL

unload to "abc.csv"
select value from table1 where id=1;
SQL

IFS=$'|' arr=( $(awk -F, -v OFS='\n' '{$1=$1}1' abc.csv) )
v_value=${arr[0]}

{
echo "
begin work;
execute procedure progname();
" 
if [ "$v_value" = "1" ] ; then 
  echo "commit ;"
 else
  echo "rollback;"
fi;

} | dbaccess $DB - 

PLUS

About the "unload" , just as suggestion (I don't like use unload for this kind of script) :

v_value=$( echo "select first 1 value from table1 where id=1;" | dbaccess $DB 2>/dev/null | egrep -v '^ *$|^ *value" | awk '{print $1}')

USING PROCEDURE
If you want avoid use shell script and keep all into SQL code, you will need create a specific procedure for that , something like :

create table  test( desc char(10) ) ;
--drop procedure commit_rollback ;
create procedure commit_rollback()
  define x int ;
  select count(*) into x from test ;
  if x > 5  then
     commit work;
  else
     rollback work ;
  end if ;
end procedure ;

begin work ;
insert into  test values ( '111')  ;
insert into  test values ( '222')  ;
insert into  test values ( '333')  ;
insert into  test values ( '444')  ;
insert into  test values ( '555')  ;
execute procedure commit_rollback() ;

select * from test ;
begin work;
insert into  test values ( '111')  ;
insert into  test values ( '222')  ;
insert into  test values ( '333')  ;
insert into  test values ( '444')  ;
insert into  test values ( '555')  ;
insert into  test values ( '666')  ;

execute procedure commit_rollback() ;
select * from test ;

The code above will have this output

desc

desc

111
222
333
444
555
666