Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Public Beta Tests » View Thread |
Messages 1 to 6 of 6 total |
Procedure definition fails |
Wed, Dec 20 2006 1:04 PM | Permanent Link |
"Ole Willy Tuv" | 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 |
Wed, Dec 20 2006 9:02 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Dec 21 2006 5:07 AM | Permanent Link |
"Ole Willy Tuv" | 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 |
Thu, Dec 21 2006 3:32 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Dec 21 2006 3:55 PM | Permanent Link |
"Ole Willy Tuv" | 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 |
Thu, Dec 21 2006 4:08 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |