The following procedure definition fails with a #700 error hinting at the
DROP statement:

create procedure sp_ATable
(
 rows integer,
 trans integer
)
/*modifies sql data*/
begin
 declare id, t, i integer;

 if exists (
   select 1
   from information.tables
   where upper(name) = 'ATABLE'
 ) then
   drop table ATable;
 end if;

 create table ATable
 (
   ID integer generated by default as identity,
   SField1 character varying(20) collate enu,
   SField2 character varying(20) collate enu,
   SField3 character varying(20) collate enu,
   SField4 character varying(20) collate enu,
   SField5 character varying(20) collate enu,
   SField6 character varying(20) collate enu,
   SField7 character varying(20) collate enu,
   SField8 character varying(20) collate enu,
   Stamp timestamp default current_timestamp,
   constraint pk_ATable primary key (ID)
 );
 create index ix_ATable_SField1 on ATable (SField1);

 set id = 0;
 repeat
   if (rows-id) > trans then
     set t = trans;
   else
     set t = (rows-id);
   end if;
   set i = 0;
   start transaction;
   while (i < t) do
     if (id = rows) then
       leave;
     end if;
     set id = id+1;
     insert into ATable values
     (
       default,
       'SField1 row ' || cast(id as varchar(10)),
       'SField2 row ' || cast(id as varchar(10)),
       'SField3 row ' || cast(id as varchar(10)),
       'SField4 row ' || cast(id as varchar(10)),
       'SField5 row ' || cast(id as varchar(10)),
       'SField6 row ' || cast(id as varchar(10)),
       'SField7 row ' || cast(id as varchar(10)),
       'SField8 row ' || cast(id as varchar(10)),
       current_timestamp
     );
     set i = i+1;
   end while;
   commit;
 until (id=rows)
 end repeat;
end

Is the syntax valid ElevateDB SQL ?

Ole

Ole,

<< The following procedure definition fails with a #700 error hinting at the
DROP statement: >>

All SQL statements must be executely dynamically in EDB stored
procedures/functions/jobs:

DECLARE stmt STATEMENT;

PREPARE stmt FROM 'CREATE TEMPORARY TABLE "TestTable"
                 (
                 "FirstColumn" INTEGER,
                 "SecondColumn" VARCHAR(30),
                 "ThirdColumn" CLOB,
                 PRIMARY KEY ("FirstColumn")
                 )

                 DESCRIPTION ''Performance Test Table''';

EXECUTE stmt;

And yes, I understand that this is non-standard and that you have a problem
with it.  However, it is done for a very specific reason - to avoid forcing
the engine to recompile the procedure/function midstream (SQL Server) or
simply not support DDL in stored procedures/functions (Interbase).  You can
prepare a statement if you want to execute it many times, just like with
client code, and the stored procedure code will port to other non-SQL
languages without changes in the future.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< And yes, I understand that this is non-standard and that you have a
problem with it. >>

Any chance you could translate my procedure to valid ElevateDB SQL, so that
I have a working example ?

Ole

Ole,

<< Any chance you could translate my procedure to valid ElevateDB SQL, so
that I have a working example ? >>

Sorry, the correct syntax would be to this:

create procedure sp_ATable
(
 rows integer,
 trans integer
)
/*modifies sql data*/
begin
 declare id, t, i integer;
 DECLARE InfoStmt STATEMENT;
 DECLARE InsertStmt STATEMENT;

 PREPARE InfoStmt FROM 'select name from information.tables where name =
''ATABLE''';
 EXECUTE InfoStmt;

 if (ROWSAFFECTED(InfoStmt) > 0) THEN
   EXECUTE IMMEDIATE 'drop table ATable';
 end if;

 EXECUTE IMMEDIATE 'create table ATable
 (
   ID integer generated by default as identity,
   SField1 character varying(20) collate ANSI,
   SField2 character varying(20) collate ANSI,
   SField3 character varying(20) collate ANSI,
   SField4 character varying(20) collate ANSI,
   SField5 character varying(20) collate ANSI,
   SField6 character varying(20) collate ANSI,
   SField7 character varying(20) collate ANSI,
   SField8 character varying(20) collate ANSI,
   Stamp timestamp default current_timestamp,
   constraint pk_ATable primary key (ID)
 )';

 EXECUTE IMMEDIATE 'create index ix_ATable_SField1 on ATable (SField1)';

 PREPARE InsertStmt FROM 'insert into ATable values  (?, ?, ?, ?, ?, ?, ?,
?, ?, ?)';

 set id = 0;
 repeat
   if (rows-id) > trans then
     set t = trans;
   else
     set t = (rows-id);
   end if;
   set i = 0;
   start transaction;
   while (i < t) do
     if (id = rows) then
       leave;
     end if;
     set id = id+1;
     EXECUTE InsertStmt USING default,  << Not sure what this is
       'SField1 row ' || cast(id as varchar(10)),
       'SField2 row ' || cast(id as varchar(10)),
       'SField3 row ' || cast(id as varchar(10)),
       'SField4 row ' || cast(id as varchar(10)),
       'SField5 row ' || cast(id as varchar(10)),
       'SField6 row ' || cast(id as varchar(10)),
       'SField7 row ' || cast(id as varchar(10)),
       'SField8 row ' || cast(id as varchar(10)),
       current_timestamp;
     );
     set i = i+1;
   end while;
   commit;
 until (id=rows)
 end repeat;
end

I didn't get a chance to test this, so I may have missed something, but it
should give you the general idea of how things work.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< I didn't get a chance to test this, so I may have missed something, but
it should give you the general idea of how things work. >>

I'm getting an error #700 at the ELSE part of the following IF statement:

   if (rows-id) > trans then
     set t = trans;
   else
     set t = (rows-id);
   end if;

Ole

Ole,

<< I'm getting an error #700 at the ELSE part of the following IF statement:
>>

It's now fixed.

--
Tim Young
Elevate Software
www.elevatesoft.com