Icon View Incident Report

Serious Serious
Reported By: Lucian Radulescu
Reported On: 1/28/2011
For: Version 2.05 Build 2
# 3375 INOUT Parameters Not Working Properly with Recursive Procedures

The follwoing script should create a simple demo database called TEST, with few rows of data. I was expecting when running the procedure GetWorkingFolder with the parameters 4, 2, ‘ATLANT’ to obtain a TargetFolder such as P:\PROJECTS\EZDELPHI\DAN. However it returns only DAN. I’ve been fighting a whole day with this problem, tried numerous things in vain.

/************************************************************
*
* ElevateDB Reverse-Engineered script for the
* TEST database
*
* Generated on 28/01/2011 08:50:38
* By the user Administrator
*
************************************************************/

SCRIPT
BEGIN

/************************************************************
* Tables
************************************************************/

EXECUTE IMMEDIATE 'CREATE TABLE "users"
(
"RecRef" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"Name" VARCHAR(24) COLLATE "UNI_CI" NOT NULL
)
VERSION 1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';

EXECUTE IMMEDIATE 'CREATE TABLE "projects"
(
"RecRef" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"ParentRef" INTEGER DEFAULT 0  NOT NULL,
"Name" VARCHAR(48) COLLATE "UNI_CI" NOT NULL
)
VERSION 1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';

EXECUTE IMMEDIATE 'CREATE TABLE "workfolders"
(
"RecRef" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"ProjectRef" INTEGER NOT NULL,
"UserRef" INTEGER NOT NULL,
"ComputerName" VARCHAR(15) COLLATE "UNI_CI" NOT NULL,
"WorkingFolder" VARCHAR(260) COLLATE "UNI_CI"
)
VERSION 1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';


/************************************************************
* Table Rows
************************************************************/

EXECUTE IMMEDIATE 'INSERT INTO "users" VALUES (1,
                 ''ADMIN'')';

EXECUTE IMMEDIATE 'INSERT INTO "users" VALUES (2,
                 ''LUCIAN'')';

EXECUTE IMMEDIATE 'INSERT INTO "projects" VALUES (1,
                 0,
                 ''EZDELPHI'')';

EXECUTE IMMEDIATE 'INSERT INTO "projects" VALUES (2,
                 1,
                 ''PROJECTS'')';

EXECUTE IMMEDIATE 'INSERT INTO "projects" VALUES (3,
                 2,
                 ''EZDELPHI'')';

EXECUTE IMMEDIATE 'INSERT INTO "projects" VALUES (4,
                 3,
                 ''DAN'')';

EXECUTE IMMEDIATE 'INSERT INTO "workfolders" VALUES (1,
                 2,
                 2,
                 ''ATLANT'',
                 ''P:\PROJECTS'')';


/************************************************************
* Views
************************************************************/


/************************************************************
* Functions
************************************************************/

EXECUTE IMMEDIATE 'CREATE FUNCTION "AddToken" (IN "InputStr" VARCHAR COLLATE "UNI_CI", IN "DelimStr" VARCHAR COLLATE "UNI_CI", IN "TokenStr" VARCHAR COLLATE "UNI_CI")
RETURNS VARCHAR(510) COLLATE "UNI_CI"
BEGIN
 DECLARE Result VARCHAR DEFAULT '''';

 IF InputStr = '''' THEN
   SET Result = TokenStr;
 ELSE
   IF TokenStr = '''' THEN
     SET Result = InputStr;
   ELSE
     IF DelimStr = '''' THEN
       SET Result = InputStr + TokenStr;
     ELSE
       SET Result = InputStr + DelimStr + TokenStr;
     END IF;
   END IF;
 END IF;

 RETURN Result;
END

VERSION 1';


/************************************************************
* Procedures
************************************************************/

EXECUTE IMMEDIATE 'CREATE PROCEDURE "GetWorkingFolder" (IN "ProjectRef" INTEGER, IN "UserRef" INTEGER, IN "ComputerName" VARCHAR(15) COLLATE "UNI_CI", INOUT "TargetFolder" VARCHAR(260) COLLATE "UNI_CI")
BEGIN
 DECLARE ParentRef INTEGER DEFAULT 0;
 DECLARE ProjectName VARCHAR DEFAULT '''';
 DECLARE WorkingFolder VARCHAR DEFAULT '''';

 DECLARE ProjectCursor CURSOR FOR ProjectStatement;
 DECLARE WFCursor CURSOR FOR WFStatement;

 PREPARE WFStatement FROM ''SELECT WorkingFolder FROM workfolders WHERE ProjectRef=? AND UserRef=? AND UPPER(ComputerName)=UPPER(''''?'''')'';
 OPEN WFCursor USING ProjectRef, UserRef, ComputerName;
 FETCH FIRST FROM WFCursor(''WorkingFolder'') INTO WorkingFolder;
 CLOSE WFCursor;
 UNPREPARE WFStatement;

 SET WorkingFolder=TRIM(BOTH '' '' FROM COALESCE(WorkingFolder, ''''));
 IF WorkingFolder<>'''' THEN
   SET TargetFolder=WorkingFolder;
 ELSE
   PREPARE ProjectStatement FROM ''SELECT ParentRef, Name FROM projects WHERE RecRef=?'';
   OPEN ProjectCursor USING ProjectRef;
   FETCH FIRST FROM ProjectCursor(''ParentRef'', ''Name'') INTO ParentRef, ProjectName;
   CLOSE ProjectCursor;
   UNPREPARE ProjectStatement;
   IF ParentRef>0 THEN
     CALL GetWorkingFolder(ParentRef, UserRef, ComputerName, TargetFolder);
     SET TargetFolder=AddToken(TargetFolder, ''\'', ProjectName);
   END IF;
 END IF;
END

VERSION 1';


/************************************************************
* Table triggers, indexes, and constraints
************************************************************/

-- Creating indexes for projects table

EXECUTE IMMEDIATE 'CREATE INDEX "ixJustParent" ON "projects" ("ParentRef")';

-- Creating indexes for workfolders table

EXECUTE IMMEDIATE 'CREATE INDEX "ixComp" ON "workfolders" ("ComputerName" COLLATE "UNI_CI")';

-- Creating non-foreign key constraints for users table

EXECUTE IMMEDIATE 'ALTER TABLE "users"
ADD CONSTRAINT "PKey" PRIMARY KEY ("RecRef"),
ADD CONSTRAINT "ixName" UNIQUE ("Name", "RecRef")';

-- Creating non-foreign key constraints for projects table

EXECUTE IMMEDIATE 'ALTER TABLE "projects"
ADD CONSTRAINT "PKey" PRIMARY KEY ("RecRef"),
ADD CONSTRAINT "ixParent" UNIQUE ("ParentRef", "Name")';

-- Creating non-foreign key constraints for workfolders table

EXECUTE IMMEDIATE 'ALTER TABLE "workfolders"
ADD CONSTRAINT "PKey" PRIMARY KEY ("RecRef"),
ADD CONSTRAINT "uqKey" UNIQUE ("UserRef", "ProjectRef", "ComputerName")';

-- Creating foreign key constraints for workfolders table

EXECUTE IMMEDIATE 'ALTER TABLE "workfolders"
ADD CONSTRAINT "fkProject" FOREIGN KEY ("ProjectRef")
REFERENCES "projects" ("RecRef")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "fkUser" FOREIGN KEY ("UserRef")
REFERENCES "users" ("RecRef")
ON UPDATE NO ACTION
ON DELETE NO ACTION';

END

/************************************************************
* End of generated SQL
************************************************************/



Comments Comments and Workarounds
The workaround would be to code the procedure as a function instead.


Resolution Resolution
Fixed Problem on 1/30/2011 in version 2.05 build 3


Products Affected Products Affected
ElevateDB 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 LCL Standard with Source
ElevateDB PHP Standard
ElevateDB PHP Standard with Source
ElevateDB PHP Trial
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

Image