Actian X/Ingres - Working around the limitations of the GLOBAL TEMPORARY/SESSION TABLES

session_table_with_rule.sql

/* An attempt to work around the 
"Limitations of Database Procedures when used with Global Temporary Tables"
https://communities.actian.com/s/article/Limitations-of-Database-Procedures-when-used-with-Global-Temporary-Tables
*/

/* Successfully tested on II 9.2.0 (int.lnx/118)NPTL */
/* On II 10.0.1 (a64.lnx/100)NPTL   returns error
E_SC0206 An internal error prevents further processing of this query.
    Associated error messages which provide more detailed information about
    the problem can be found in the error log, II_CONFIG:errlog.log
*/

/*ISQL START OF SESSION 1*/
create table prosoplog (
		id integer not null default 0,
        d_login ingresdate not null default ' ',
        tty char(20) not null default ' ',
        am_ypal integer not null default 0,
        uxuser char(20) not null default ' ',
        d_now ingresdate not null default ' ',
        c_program char(15) not null default ' ',
        c_frskey char(30) not null default ' ',
        last_year ingresdate not null default ' ',
        c_entyp char(5) not null default ' ',
        last_aa integer not null default 0
);

create table test (
last_year ingresdate not null,
c_entyp char(5) not null,
last_aa integer not null
);

DECLARE GLOBAL TEMPORARY TABLE
     prosoplog_session
     (
             d_login ingresdate not null,
             tty char(20) not null,
             am_ypal integer not null,
             uxuser char(20) not null,
             dbuser char(20) not null,
             client char(20) not null,
             host char(20) not null ,
             sxolia char(80) not null
     )
     ON COMMIT PRESERVE ROWS WITH NORECOVERY;

create procedure insert_prosoplog2 
(
session_table set of (
d_login ingresdate not null,
tty char(20) not null,
am_ypal integer not null,
uxuser char(20) not null,
dbuser char(20) not null,
client char(20) not null,
host char(20) not null ,
sxolia char(80) not null)
)
AS
DECLARE
id integer not null;
d_login ingresdate not null;
tty char(20) not null;
am_ypal integer not null;
uxuser char(20) not null;
dbuser char(20) not null;
client char(20) not null;
host char(20) not null;
sxolia char(80) not null;
BEGIN
select d_login,tty,am_ypal,uxuser,dbuser,client,host,sxolia
into :d_login,:tty,:am_ypal,:uxuser,:dbuser,:client,:host,:sxolia
from session_table;

select random() into :id;

insert into prosoplog (id,d_login,      tty,          am_ypal,     uxuser,
                          d_now,        c_program,    c_frskey,
                          last_year,    c_entyp,      last_aa)
                values  (:id,:d_login,   :tty,       :am_ypal,  :uxuser,
                         date('now'),     '', '',
                         '', '',   0);
						 
return :id;						 
END;

create procedure insert_prosoplog1 
(
last_year = ingresdate not null, 
c_entyp   = varchar(5) not null, 
last_aa   = integer not null
)
AS
DECLARE
id integer4 not null;
d_login ingresdate not null;
tty char(20) not null;
am_ypal integer not null;
uxuser char(20) not null;
dbuser char(20) not null;
client char(20) not null;
host char(20) not null;
sxolia char(80) not null;
BEGIN
execute procedure insert_prosoplog2(session_table=session.prosoplog_session) 
into :id;
update prosoplog
set last_aa=:last_aa,
last_year=:last_year,
c_entyp=:c_entyp
where id=:id;
END;

create rule insert_test after insert on test
          execute procedure insert_prosoplog1(
            last_year=new.last_year,
			c_entyp=new.c_entyp,
			last_aa=new.last_aa
);

commit;
/* END OF SESSION 1 */

/*ISQL START OF SESSION 2*/
/* VERY IMPORTANT: run DECLARE GLOBAL TEMPORARY TABLE in the new session again!!! or you'll get 
E_OP0004 request for relation
    information failed - possibly because table was modified or deleted
*/
DECLARE GLOBAL TEMPORARY TABLE
     prosoplog_session
     (
             d_login ingresdate not null,
             tty char(20) not null,
             am_ypal integer not null,
             uxuser char(20) not null,
             dbuser char(20) not null,
             client char(20) not null,
             host char(20) not null ,
             sxolia char(80) not null
     )
     ON COMMIT PRESERVE ROWS WITH NORECOVERY;

SET RANDOM_SEED 1000;
	 
insert into prosoplog_session 
values  ('14/06/2018','tty1',12,'userX','db',
'192.168.4.59','dpsn','whatever');

/*Fire rule */
insert into test (last_year,c_entyp,last_aa)
values ('15/06/2018','receipt',144);

/*OR execute procedure directly*/
execute procedure insert_prosoplog1 
(last_year='14/06/2018',c_entyp='invoice',last_aa=3446);	

commit;
/*ISQL END OF SESSION 2*/

/*ISQL START OF SESSION 3*/
select * from prosoplog

commit;
/*ISQL END OF SESSION 3*/