Hi!

I am evaluating ElevateDB in order to replace DBISAM 3 we use at the moment. Having
problems with stored procedures. I have 1.04 Build 4 installed.

Simple database:

CREATE PROCEDURE Create_test3cs()
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE "test1"
                    (
                    "id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY
1) NOT NULL,
                    "name" CHAR(20) COLLATE "ANSI" NOT NULL
                    )
                    VERSION 1
                    UNENCRYPTED
                    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 'CREATE PROCEDURE "testsp1" (
                    IN pname VARCHAR
                    )
                    BEGIN
                    execute immediate ''INSERT INTO test1 VALUES (50, pname)'';
                    END
                    ';

  EXECUTE IMMEDIATE 'CREATE PROCEDURE "testsp2" ()
                    BEGIN
                    DECLARE mycursor CURSOR WITH RETURN FOR mysql ;
                    PREPARE mysql FROM ''SELECT * FROM test1'';
                    OPEN mycursor ;
                    UNPREPARE mysql ;
                    END
                    ';
END

Problem 1:
When I execute testsp1 from ElevateDB Manager it generates the following error:
Error #700 An error was found in the statement at line 1 and column 31 (Expected column
name but instead found "pname")

Ok, if I replace pname with a constant, it works.
execute immediate 'INSERT INTO test1 VALUES (50, ''asdasdf'')'; //mind the two single
quotes around the constant, not a double
If I use double quotes, it does not.

Problem 2:
When I run testsp2 from either ElevateDB or Delphi, it does not return a result set, not
even an empty one. The table definitely has records.
BTW, initially I did not do unprepare in testsp2, it resulted in AV and unfortunately help
article on SPs does not say unprepare is mandatory.

Could anyone please point me to a right direction?

Thanks in advance,
Joseph

Joseph,

<< When I execute testsp1 from ElevateDB Manager it generates the following
error:
Error #700 An error was found in the statement at line 1 and column 31
(Expected column name but instead found "pname") >>

You can't use a parameter name in a dynamic SQL statement without passing it
in as a dynamic parameter, like this:

EXECUTE IMMEDIATE 'CREATE PROCEDURE "testsp1" (
                    IN pname VARCHAR
                    )
                    BEGIN
                    execute immediate ''INSERT INTO test1 VALUES (50, ?)''
using pname;
                    END
                    ';

<< When I run testsp2 from either ElevateDB or Delphi, it does not return a
result set, not even an empty one. The table definitely has records. BTW,
initially I did not do unprepare in testsp2, it resulted in AV and
unfortunately help
article on SPs does not say unprepare is mandatory. >>

You can't manually UNPREPARE a statement when the statement is responsible
for returning the result set.  The AV is a bug, of course, so I'll make sure
that is corrected in the next build 5.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Thank you very much for your quick response. I suspected there was something very simple
with the first question. As for the second one, the only reason I added this unprepare
(after banging my head against the table about a dozen times) is that it is present in
http://www.elevatesoft.com/scripts/newsgrp.dll?action=openmsg&group=16&msg=1018&page=1#msg1018.
That was the only thread I have found with a remotely similar problem. Surprised no
one have noticed AV before. Will be waiting calmly for the next build now.

Thanks again.

Joseph

Joseph,

<< As for the second one, the only reason I added this unprepare (after
banging my head against the table about a dozen times) is that it is present
in>>

It should work either way, provided that you don't re-use the statement
after the prepare.  The build 5 fix will allow for the UNPREPARE to be there
or not.

<< Surprised no one have noticed AV before. >>

It was just introduced in one of the last builds, and due to the nature of
the bug and the memory manager being used, it may not always cause an AV.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

>It was just introduced in one of the last builds, and due to the nature of the bug and
the memory manager being used, it may not always cause an AV.

That sounds scary familiar . Great you are fixing it. We hope EDB and SPs will give us
the answer to "big tables problem" we now have with the old system. Probably, it is just
too close to its limits - couple of our tables are 1.5gb, 5.5m records. SPs will work
faster I am certain and we are all set for the stress testing already

Thanks again,

Joseph

Joseph,

<< That sounds scary familiar . Great you are fixing it. We hope EDB and
SPs will give us the answer to "big tables problem" we now have with the old
system. Probably, it is just too close to its limits - couple of our tables
are 1.5gb, 5.5m records. SPs will work faster I am certain and we are all
set for the stress testing already >>

Are you using DBISAM currently ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

>Are you using DBISAM currently ?
Yes, we are on DMISAM 3.23.

Joseph

Joseph,

<< Yes, we are on DMISAM 3.23. >>

Yes, ElevateDB will improve the performance quite a bit over 3.23.

--
Tim Young
Elevate Software
www.elevatesoft.com