Instantly share code, notes, and snippets.

Embed
What would you like to do?
Actian X/Ingres - Working around the limitations of the GLOBAL TEMPORARY/SESSION TABLES
/* 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*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment