1--
2-- ### Verification of several modifying queries
3--
4--
5
6
7create table a_sysres ( resid int, instid int);
8create table a_sapinst (  instid int, instname string(10));
9create table a_sysinst ( instid int, sysid int);
10create table a_system ( sysid int, sysname string(10));
11
12
13drop if exists procedure loadData;
14@
15create procedure loadData()
16begin
17
18   delete from a_system;
19   insert into a_system values ( 1, 'SYSA');
20   insert into a_system values ( 2, 'SYSB');
21
22   delete from a_sysinst;
23   insert into a_sysinst values ( 1, 1);
24   insert into a_sysinst values ( 2, 1);
25   insert into a_sysinst values ( 3, 1);
26   insert into a_sysinst values ( 4, 1);
27
28   delete from a_sapinst;
29   insert into a_sapinst values ( 1, 'SAP1');
30   insert into a_sapinst values ( 2, 'SAP2');
31   insert into a_sapinst values ( 3, 'SAP3');
32   insert into a_sapinst values ( 4, 'SAP3');
33
34   delete from a_sysres;
35   insert into a_sysres values ( 1, 1 );
36end;
37@
38
39drop if exists procedure checkMod001;
40@
41create procedure checkMod001(msg out string(20)) return string(10)
42begin
43
44   var res string(10) = 'ERROR';
45   var sysid int;
46
47   var numinst int;
48   var numsapinst int;
49   var numsysres int;
50   var numsys int;
51
52   :sysid = 1;
53
54   -- delete with nested subselect, since one var reference, should be NOT cached
55   delete from a_sysres where instid in ( select instid from a_sysinst where sysid = :sysid );
56   -- delete with nested static subselect, should be cached ( not really useful, just for cache testing )
57   delete from a_sapinst where instid in ( select instid from a_sysinst where sysid = 1 );
58   delete from a_sysinst where sysid = :sysid;
59   delete from a_system where sysid = :sysid;
60
61   :numinst = ( select count(*) from a_sysinst );
62   :numsapinst = ( select count(*) from a_sapinst );
63   :numsysres = ( select count(*) from a_sysres );
64   :numsys = ( select count(*) from a_system );
65
66   if :numinst = 0 and :numsapinst = 0 and :numsysres = 0 and :numsys = 1
67   then
68	:res = 'ok';
69   end;
70
71   :msg = 'Delete with sub select in condition';
72   return :res;
73end;
74@
75
76drop if exists procedure checkMod002;
77@
78create procedure checkMod002(msg out string(20)) return string(10)
79begin
80
81   var res string(10) = 'ERROR';
82   var sysid int;
83
84   :sysid = 1;
85
86   update a_system set sysid = sysid + 1 where sysid = 1 return :sysid = sysid;
87
88   if :sysid = 2
89   then
90	:res = 'ok';
91   end;
92
93   :msg = 'Update with return';
94   return :res;
95end;
96@
97
98
99call loadData();
100:r = call checkMod001(:msg);
101insert into checklog values ('GATE-D', :msg, :r);
102
103call loadData();
104:r = call checkMod002(:msg);
105insert into checklog values ('GATE-D', :msg, :r);
106
107
108drop procedure loadData;
109drop procedure checkMod001;
110drop procedure checkMod002;
111
112drop if exists table a_sysres;
113drop if exists table a_sapinst;
114drop if exists table a_sysinst;
115drop if exists table a_system;
116
117
118
119
120