 |  LoginProducts  SalesSupportDownloadsAbout |
| Home » Technical Support » ElevateDB Technical Support » Incident Reports » Incident Reports Reported for Version 2.01 » View Incident Report |
View Incident Report Serious |
| Reported By: Ulrich Doewich Reported On: 7/28/2008 For: Version 2.01 Build 1 |
I've encountered an access violation with the EDB server. I'll attach a script to create the tables and procedures you'll need to recreate it.
1) create a new session + database and run the attached script
2) locate the ProcLogin procedure and execute with the following parameters
username: test
password: qUqP5cyxm6YcTAhz05Hph5gvu9M=
3) that should get you a result set
4) close the result set
5) execute the same procedure again, changing parameters to:
username: udoewich
password: qUqP5cyxm6YcTAhz05Hph5gvu9M= (stays the same)
6) this will result in an AV and ElevateDB manager says "Invalid Pointer
Operation"
/************************************************************
*
* ElevateDB Reverse-Engineered script for the RCS database
*
* Generated on 28/07/2008 5:09:29 PM
* By the user Administrator
*
************************************************************/
SCRIPT
BEGIN
/************************************************************
* Tables
************************************************************/
EXECUTE IMMEDIATE 'CREATE TABLE "device"
(
"id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1),
"created" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
"modified" TIMESTAMP GENERATED ALWAYS AS CURRENT_TIMESTAMP,
"ip" BYTE(4),
"model" INTEGER,
"name" VARCHAR(32) COLLATE "ANSI_CI",
"location" VARCHAR(32) COLLATE "ANSI_CI",
"tree_lvl" INTEGER,
"tree_pid" INTEGER,
CONSTRAINT "id_pk" PRIMARY KEY ("id"),
CONSTRAINT "ip_con" UNIQUE ("ip")
)
VERSION 1
ENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (1,
TIMESTAMP ''2008-07-28 11:26:13.0843'',
TIMESTAMP ''2008-07-28 17:04:48.0890'',
NULL,
NULL,
''\0'',
NULL,
0,
0)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (2,
TIMESTAMP ''2008-07-28 11:26:13.0875'',
TIMESTAMP ''2008-07-28 17:04:48.0953'',
X''FA00000A'',
7,
''OTI R&D TestT:LAN O04/3'',
''OTI R&D O04_3'',
1,
1)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (3,
TIMESTAMP ''2008-07-28 11:26:13.0875'',
TIMESTAMP ''2008-07-28 17:04:48.0984'',
X''FB00000A'',
20,
''10.0.0.251'',
'''',
1,
1)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (4,
TIMESTAMP ''2008-07-28 11:26:13.0875'',
TIMESTAMP ''2008-07-28 17:04:49.0031'',
X''FC00000A'',
7,
''OTI R&D TestT:LAN O04/2'',
''OTI R&D O04_2'',
1,
1)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (5,
TIMESTAMP ''2008-07-28 11:26:13.0875'',
TIMESTAMP ''2008-07-28 17:04:49.0062'',
NULL,
NULL,
''\11'',
NULL,
0,
0)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (6,
TIMESTAMP ''2008-07-28 11:26:13.0890'',
TIMESTAMP ''2008-07-28 17:04:49.0093'',
X''030B000A'',
7,
''OTI Lab Test T:LAN R11/03'',
''OTI Lab R1103'',
1,
5)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (7,
TIMESTAMP ''2008-07-28 11:26:13.0890'',
TIMESTAMP ''2008-07-28 17:04:49.0140'',
X''010B000A'',
7,
''OTI Lab Test T:LAN R11/01'',
''OTI Lab R1101'',
2,
6)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (8,
TIMESTAMP ''2008-07-28 11:26:13.0906'',
TIMESTAMP ''2008-07-28 17:04:49.0171'',
X''040B000A'',
7,
''OTI Lab Test T:LAN R11/04'',
''OTI Lab R1104'',
3,
7)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (9,
TIMESTAMP ''2008-07-28 11:26:13.0906'',
TIMESTAMP ''2008-07-28 17:04:49.0203'',
X''020B000A'',
7,
''OTI Lab Test T:LAN R11/02'',
''OTI Lab R1102'',
2,
6)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (10,
TIMESTAMP ''2008-07-28 11:26:13.0906'',
TIMESTAMP ''2008-07-28 17:04:49.0218'',
NULL,
NULL,
''\12'',
NULL,
0,
0)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (11,
TIMESTAMP ''2008-07-28 11:26:13.0906'',
TIMESTAMP ''2008-07-28 17:04:49.0234'',
X''050C000A'',
7,
''OTI Lab Test T:LAN R12/05'',
''OTI Lab R1205 (Ring)'',
1,
10)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (12,
TIMESTAMP ''2008-07-28 11:26:13.0906'',
TIMESTAMP ''2008-07-28 17:04:49.0250'',
X''040C000A'',
7,
''OTI Lab TestT:LAN R12/04'',
''OTI Lab R1204 (Ring)'',
2,
11)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (13,
TIMESTAMP ''2008-07-28 11:26:13.0921'',
TIMESTAMP ''2008-07-28 17:04:49.0265'',
X''030C000A'',
7,
''OTI Lab Test T:LAN R12/03'',
''OTI Lab R1203 (Ring)'',
3,
12)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (14,
TIMESTAMP ''2008-07-28 11:26:13.0921'',
TIMESTAMP ''2008-07-28 17:04:49.0281'',
X''020C000A'',
7,
''OTI Lab Test T:LAN R12/02'',
''OTI Lab R1202 (Ring)'',
4,
13)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (15,
TIMESTAMP ''2008-07-28 11:26:13.0921'',
TIMESTAMP ''2008-07-28 17:04:49.0296'',
X''010C000A'',
7,
''OTI Lab Test T:LAN R12/01'',
''OTI Lab R1201 (Ring)'',
5,
14)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (16,
TIMESTAMP ''2008-07-28 11:26:13.0937'',
TIMESTAMP ''2008-07-28 17:04:49.0312'',
X''060C000A'',
7,
''OTI Lab Test T:LAN R12/06'',
''OTI Lab R1206 (Linear)'',
2,
11)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (17,
TIMESTAMP ''2008-07-28 11:26:13.0937'',
TIMESTAMP ''2008-07-28 17:04:49.0328'',
X''070C000A'',
7,
''OTI Lab Test T:LAN R12/07'',
''OTI Lab R1207 (Linear)'',
3,
16)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (18,
TIMESTAMP ''2008-07-28 11:26:13.0937'',
TIMESTAMP ''2008-07-28 17:04:49.0375'',
X''080C000A'',
7,
''TIP (Test In Progress)-DND OW'',
''OTI Lab R1208 (Linear)'',
4,
17)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (19,
TIMESTAMP ''2008-07-28 11:26:13.0937'',
TIMESTAMP ''2008-07-28 17:04:49.0390'',
NULL,
NULL,
''\13'',
NULL,
0,
0)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (20,
TIMESTAMP ''2008-07-28 11:26:13.0953'',
TIMESTAMP ''2008-07-28 17:04:49.0406'',
X''0A0D000A'',
5,
''NBPL'',
''Mandan Spare'',
1,
19)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (21,
TIMESTAMP ''2008-07-28 11:26:13.0953'',
TIMESTAMP ''2008-07-28 17:04:49.0421'',
X''090D000A'',
5,
''OTI Lab TestT:LAN R13/09'',
''OTI Lab R1309 (Ring)'',
2,
20)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (22,
TIMESTAMP ''2008-07-28 11:26:13.0953'',
TIMESTAMP ''2008-07-28 17:04:49.0437'',
X''080D000A'',
5,
''OTI Lab TestT:LAN R13/08'',
''OTI Lab R1308 (Ring)'',
3,
21)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (23,
TIMESTAMP ''2008-07-28 11:26:13.0953'',
TIMESTAMP ''2008-07-28 17:04:49.0453'',
X''070D000A'',
5,
''OTI Lab TestT:LAN R13/07'',
''OTI Lab R1307 (Ring)'',
4,
22)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (24,
TIMESTAMP ''2008-07-28 11:26:13.0953'',
TIMESTAMP ''2008-07-28 17:04:49.0468'',
X''060D000A'',
5,
''OTI Lab TestT:LAN R13/06'',
''OTI Lab R1306 (Ring)'',
5,
23)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (25,
TIMESTAMP ''2008-07-28 11:26:13.0968'',
TIMESTAMP ''2008-07-28 17:04:49.0484'',
X''050D000A'',
5,
''OTI Lab TestT:LAN R13/05'',
''OTI Lab R1305 (Ring)'',
6,
24)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (26,
TIMESTAMP ''2008-07-28 11:26:13.0968'',
TIMESTAMP ''2008-07-28 17:04:49.0500'',
X''040D000A'',
5,
''OTI Lab TestT:LAN R13/04'',
''OTI Lab R1304 (Ring)'',
7,
25)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (27,
TIMESTAMP ''2008-07-28 11:26:13.0968'',
TIMESTAMP ''2008-07-28 17:04:49.0515'',
X''0B0D000A'',
5,
''OTI Lab TestT:LAN R13/11'',
''OTI Lab R1311'',
2,
20)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (28,
TIMESTAMP ''2008-07-28 11:26:13.0968'',
TIMESTAMP ''2008-07-28 17:04:49.0546'',
X''0C0D000A'',
5,
''OTI Lab TestT:LAN R13/12'',
''OTI Lab R1312'',
3,
27)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (29,
TIMESTAMP ''2008-07-28 11:26:13.0984'',
TIMESTAMP ''2008-07-28 17:04:49.0578'',
X''0D0D000A'',
3,
''OTI Lab TestT:LAN R13/13'',
''OTI Lab R1313'',
4,
28)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (30,
TIMESTAMP ''2008-07-28 11:26:13.0984'',
TIMESTAMP ''2008-07-28 17:04:49.0593'',
NULL,
NULL,
''\20'',
NULL,
0,
0)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (31,
TIMESTAMP ''2008-07-28 11:26:13.0984'',
TIMESTAMP ''2008-07-28 17:04:49.0609'',
X''0114000A'',
20,
''OCP_1'',
''Esna Park 2'',
1,
30)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (32,
TIMESTAMP ''2008-07-28 11:26:13.0984'',
TIMESTAMP ''2008-07-28 17:04:49.0625'',
X''0214000A'',
7,
''Markham TNO'',
''CLLI 2002'',
2,
31)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (33,
TIMESTAMP ''2008-07-28 11:26:14'',
TIMESTAMP ''2008-07-28 17:04:49.0640'',
X''0314000A'',
7,
''MajorMackenzie'',
''CLLI 2003'',
2,
31)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (34,
TIMESTAMP ''2008-07-28 11:26:14'',
TIMESTAMP ''2008-07-28 17:04:49.0656'',
X''0414000A'',
7,
''Markham TJ'',
''CLLI 2004'',
2,
31)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (35,
TIMESTAMP ''2008-07-28 11:26:14.0015'',
TIMESTAMP ''2008-07-28 17:04:49.0671'',
X''0514000A'',
7,
''Stoufville'',
''CLLI 2005'',
3,
34)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (36,
TIMESTAMP ''2008-07-28 11:26:14.0015'',
TIMESTAMP ''2008-07-28 17:04:49.0703'',
X''0614000A'',
7,
''Uxbridge'',
''CLLI 2006'',
4,
35)';
EXECUTE IMMEDIATE 'INSERT INTO "device" VALUES (37,
TIMESTAMP ''2008-07-28 11:26:14.0015'',
TIMESTAMP ''2008-07-28 17:04:49.0718'',
X''0714000A'',
7,
''Sunderland'',
''CLLI 2007'',
5,
36)';
EXECUTE IMMEDIATE 'CREATE INDEX "tlvl_idx" ON "device"
("tree_lvl")';
EXECUTE IMMEDIATE 'CREATE TABLE "user"
(
"id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1),
"created" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
"modified" TIMESTAMP GENERATED ALWAYS AS CURRENT_TIMESTAMP,
"alias" VARCHAR(32) COLLATE "ANSI",
"password" VARCHAR(32) COLLATE "ANSI",
"fst_name" VARCHAR(32) COLLATE "ANSI_CI",
"surname" VARCHAR(32) COLLATE "ANSI_CI",
"tel" VARCHAR(24) COLLATE "ANSI_CI",
"email" VARCHAR(64) COLLATE "ANSI_CI",
"tlan_cal" INTEGER,
"tree_lvl" INTEGER,
"tree_pid" INTEGER,
CONSTRAINT "id_pk" PRIMARY KEY ("id"),
CONSTRAINT "alias_con" UNIQUE ("alias")
)
VERSION 1
ENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'INSERT INTO "user" VALUES (1,
TIMESTAMP ''2008-07-28 11:26:24.0468'',
TIMESTAMP ''2008-07-28 17:04:49.0734'',
''test'',
''qUqP5cyxm6YcTAhz05Hph5gvu9M='',
''Test'',
''Guy'',
'''',
'''',
3,
0,
0)';
EXECUTE IMMEDIATE 'INSERT INTO "user" VALUES (2,
TIMESTAMP ''2008-07-28 11:44:38.0187'',
TIMESTAMP ''2008-07-28 17:04:49.0750'',
''udoewich'',
''qUqP5cyxm6YcTAhz05Hph5gvu9M='',
''Ulrich'',
''Doewich'',
'''',
'''',
5,
0,
0)';
EXECUTE IMMEDIATE 'CREATE INDEX "tlvl_idx" ON "user"
("tree_lvl")';
EXECUTE IMMEDIATE 'CREATE TABLE "user_acl"
(
"user_id" INTEGER,
"dev_id" INTEGER,
"enabled" INTEGER,
"tlan_cal" INTEGER,
"mask_pri" BYTE(4),
"mask_wan" BYTE(4),
"mask_eth" BYTE(4),
"mask_ser" BYTE(4),
"mask_rio" BYTE(4),
CONSTRAINT "user_id_fk" FOREIGN KEY ("user_id") REFERENCES "user" ("id"),
CONSTRAINT "dev_id_fk" FOREIGN KEY ("dev_id") REFERENCES "device" ("id")
)
VERSION 1
ENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'INSERT INTO "user_acl" VALUES (1,
6,
1,
3,
X''01000000'',
X''03000000'',
X''0F000000'',
X''3F000000'',
X''0F000000'')';
EXECUTE IMMEDIATE 'INSERT INTO "user_acl" VALUES (1,
7,
1,
3,
X''01000000'',
X''03000000'',
X''0F000000'',
X''3F000000'',
X''0F000000'')';
EXECUTE IMMEDIATE 'INSERT INTO "user_acl" VALUES (1,
8,
1,
3,
X''01000000'',
X''03000000'',
X''0F000000'',
X''3F000000'',
X''0F000000'')';
EXECUTE IMMEDIATE 'INSERT INTO "user_acl" VALUES (1,
9,
1,
3,
X''01000000'',
X''03000000'',
X''0F000000'',
X''3F000000'',
X''0F000000'')';
EXECUTE IMMEDIATE 'INSERT INTO "user_acl" VALUES (1,
20,
1,
3,
X''01000000'',
X''03000000'',
X''0F000000'',
X''3F000000'',
X''0F000000'')';
EXECUTE IMMEDIATE 'INSERT INTO "user_acl" VALUES (1,
21,
1,
3,
X''01000000'',
X''03000000'',
X''0F000000'',
X''3F000000'',
X''0F000000'')';
EXECUTE IMMEDIATE 'INSERT INTO "user_acl" VALUES (1,
22,
1,
3,
X''01000000'',
X''03000000'',
X''0F000000'',
X''3F000000'',
X''0F000000'')';
EXECUTE IMMEDIATE 'INSERT INTO "user_acl" VALUES (1,
23,
1,
3,
X''01000000'',
X''03000000'',
X''0F000000'',
X''3F000000'',
X''0F000000'')';
EXECUTE IMMEDIATE 'INSERT INTO "user_acl" VALUES (1,
24,
1,
3,
X''01000000'',
X''03000000'',
X''0F000000'',
X''3F000000'',
X''0F000000'')';
EXECUTE IMMEDIATE 'INSERT INTO "user_acl" VALUES (1,
25,
1,
3,
X''01000000'',
X''03000000'',
X''0F000000'',
X''3F000000'',
X''0F000000'')';
EXECUTE IMMEDIATE 'INSERT INTO "user_acl" VALUES (1,
26,
1,
3,
X''01000000'',
X''03000000'',
X''0F000000'',
X''3F000000'',
X''0F000000'')';
EXECUTE IMMEDIATE 'INSERT INTO "user_acl" VALUES (1,
27,
1,
3,
X''01000000'',
X''03000000'',
X''0F000000'',
X''3F000000'',
X''0F000000'')';
EXECUTE IMMEDIATE 'INSERT INTO "user_acl" VALUES (1,
28,
1,
3,
X''01000000'',
X''03000000'',
X''0F000000'',
X''3F000000'',
X''0F000000'')';
EXECUTE IMMEDIATE 'INSERT INTO "user_acl" VALUES (1,
29,
1,
3,
X''01000000'',
X''01000000'',
X''0F000000'',
X''3F000000'',
X''0F000000'')';
EXECUTE IMMEDIATE 'INSERT INTO "user_acl" VALUES (2,
2,
1,
5,
X''07000000'',
X''03000000'',
X''0F000000'',
X''3F000000'',
X''0F000000'')';
EXECUTE IMMEDIATE 'INSERT INTO "user_acl" VALUES (2,
3,
1,
5,
X''07000000'',
X''03000000'',
X''0F000000'',
X''21000000'',
X''00000000'')';
EXECUTE IMMEDIATE 'INSERT INTO "user_acl" VALUES (2,
4,
1,
5,
X''07000000'',
X''03000000'',
X''0F000000'',
X''3F000000'',
X''0F000000'')';
EXECUTE IMMEDIATE 'CREATE INDEX "userdev_idx" ON "user_acl"
("user_id", "dev_id")';
EXECUTE IMMEDIATE 'CREATE TABLE "log"
(
"user_id" INTEGER,
"dev_id" INTEGER,
"date_code" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
"msg" CLOB COLLATE "ANSI_CI",
"kind" INTEGER
)
VERSION 1
ENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 128
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE INDEX "userdev_idx" ON "log"
("user_id", "dev_id", "date_code")';
EXECUTE IMMEDIATE 'CREATE TABLE "config"
(
"key" VARCHAR(32) COLLATE "ANSI_CI" NOT NULL,
"value" VARCHAR(256) COLLATE "ANSI_CI",
CONSTRAINT "key_pk" PRIMARY KEY ("key")
)
VERSION 1
ENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'INSERT INTO "config" VALUES (''BakFileStore'',
''c:\'')';
EXECUTE IMMEDIATE 'INSERT INTO "config" VALUES (''GenAccessListExpiration'',
''48'')';
EXECUTE IMMEDIATE 'INSERT INTO "config" VALUES (''MaintLogRetention'',
''14'')';
/************************************************************
* Views
************************************************************/
/************************************************************
* Functions
************************************************************/
/************************************************************
* Procedures
************************************************************/
EXECUTE IMMEDIATE 'CREATE PROCEDURE "ProcGenerateUpdates" ()
BEGIN
DECLARE UpdateFile VARCHAR DEFAULT '''';
SET UpdateFile = ''RCSUPD_'' + REPLACE('':'', ''-'',
CAST(CURRENT_TIMESTAMP AS VARCHAR));
EXECUTE IMMEDIATE ''SAVE UPDATES FOR DATABASE "RCS" AS "'' + UpdateFile +
''" TO STORE "Updates"'';
END
';
EXECUTE IMMEDIATE 'CREATE PROCEDURE "ProcLogin" (IN "ParAlias" VARCHAR(32)
COLLATE ANSI, IN "ParPassword" VARCHAR(32) COLLATE ANSI)
BEGIN
DECLARE UserCursor INSENSITIVE CURSOR FOR UserLookup;
DECLARE UserId INT;
DECLARE ParentCursor INSENSITIVE CURSOR FOR ParentLookup;
DECLARE ParentId INT;
DECLARE DuplicateCursor INSENSITIVE CURSOR FOR DuplicateLookup;
DECLARE InsertParent STATEMENT;
DECLARE ConfigCursor CURSOR FOR ConfigLookup;
DECLARE ExpirationHours INT;
DECLARE ResultCursor INSENSITIVE CURSOR WITH RETURN FOR AclLookup;
PREPARE UserLookup FROM ''SELECT id FROM user WHERE alias=? AND password=?'';
OPEN UserCursor USING ParAlias, ParPassword;
IF ROWCOUNT(UserCursor) > 0 THEN
FETCH FIRST FROM UserCursor (id) INTO UserId;
BEGIN
EXECUTE IMMEDIATE ''DROP TABLE temp'';
EXCEPTION
END;
EXECUTE IMMEDIATE ''CREATE TEMPORARY TABLE temp AS '' +
''SELECT dev_id,ip,name,location,tree_lvl,tree_pid,
tlan_cal,mask_pri,mask_wan,mask_eth,mask_ser,mask_rio '' +
''FROM user_acl '' +
''JOIN device ON user_acl.dev_id=device.id '' +
''WHERE user_id='' + CAST(UserId AS VARCHAR) +
'' AND enabled=1 WITH DATA'';
PREPARE ParentLookup FROM ''SELECT DISTINCT tree_pid FROM temp'';
PREPARE DuplicateLookup FROM ''SELECT dev_id FROM temp WHERE dev_id=?'';
PREPARE InsertParent FROM ''INSERT INTO temp '' +
''(dev_id,ip,name,location,tree_lvl,tree_pid) '' +
''SELECT dev_id,ip,name,location,tree_lvl,tree_pid '' +
''FROM device '' +
''WHERE device.id=?'';
OPEN ParentCursor;
FETCH FIRST FROM ParentCursor (tree_pid) INTO ParentId;
WHILE NOT EOF(ParentCursor) DO
OPEN DuplicateCursor USING ParentId;
IF EOF(DuplicateCursor) THEN
EXECUTE InsertParent USING ParentId;
END IF;
CLOSE DuplicateCursor;
FETCH NEXT FROM ParentCursor (tree_pid) INTO ParentId;
END WHILE;
CLOSE ParentCursor;
PREPARE ConfigLookup FROM ''SELECT value FROM config WHERE key=?'';
OPEN ConfigCursor USING ''GenAccessListExpiration'';
FETCH FIRST FROM ConfigCursor (value) INTO ExpirationHours;
EXECUTE IMMEDIATE ''INSERT INTO temp (dev_id,tree_lvl,tree_pid)
VALUES ('' + CAST(UserId AS VARCHAR) + '',-1,'' +
CAST(ExpirationHours AS VARCHAR) + '')'';
PREPARE AclLookup FROM ''SELECT * FROM temp ORDER BY tree_lvl,name'';
OPEN ResultCursor;
END IF;
CLOSE UserCursor;
END
';
EXECUTE IMMEDIATE 'CREATE PROCEDURE "ProcPurgeLog" ()
BEGIN
DECLARE ConfigCursor CURSOR FOR ConfigLookup;
DECLARE LogRetention INT;
PREPARE ConfigLookup FROM ''SELECT value FROM config
WHERE key=''''MaintLogRetention'''''';
OPEN ConfigCursor;
IF ROWCOUNT(ConfigCursor) > 0 THEN
FETCH FIRST FROM ConfigCursor (value) INTO LogRetention;
EXECUTE IMMEDIATE ''DELETE FROM log WHERE date_code <
(CURRENT_TIMESTAMP-INTERVAL '''''' +
CAST(LogRetention AS VARCHAR) + '''''' DAY)'';
END IF;
CLOSE ConfigCursor;
END
';
END
/************************************************************
* End of generated SQL
************************************************************/
CommentsThe issue was related to the fact that the result set from the procedure was using the temporary table, which was also being dropped and recreated for each execution.
ResolutionFixed Problem on 7/29/2008 in version 2.01 build 2
Products AffectedElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial
This web page was last updated on Monday, August 19, 2024 at 05:55 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ?  E-mail us at info@elevatesoft.com |
