Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 2 of 2 total |
Are stored procedures support dynamic SQL in the future? |
Mon, Mar 29 2010 10:29 AM | Permanent Link |
durumdara | Dear Support!
I used FB formerly and in this RDBMS the procedures are static. This meaning that on creation the RDBMS is eval the fields, SQL-s, and compile the stored procedure. If some fields modified, I need to recreate the stp, and dynamic SQL/fields/tables are not supported. But in EDB I can create magic STPs: alter PROCEDURE "test" ( IN "tnev" VARCHAR(100) COLLATE ANSI, IN "oldkod" INTEGER, IN "fnev" VARCHAR(100), in "newkod" integer) BEGIN declare tmptablanev varchar(100); declare sql varchar(500); declare rc integer; DECLARE TempCursor CURSOR FOR tstmt; set tmptablanev = 'tmp_copyr_' || cast(oldkod as varchar(12)); /* Same table exists or not? */ /*PREPARE tstmt FROM 'SELECT Name FROM information.TemporaryTables where lower(name)= lower(?)'; */ PREPARE tstmt FROM 'SELECT Name FROM information.Tables where lower(name)= lower(?)'; OPEN TempCursor USING tmptablanev; set rc = ROWCOUNT(TempCursor); CLOSE TempCursor; IF (rc > 0) THEN /* Yes, drop it */ set sql = 'drop table ' || tmptablanev; execute immediate sql; END IF; /* Copy table */ set sql = 'create ' || /*temporary*/ ' table ' || tmptablanev; set sql = sql || ' as '; set sql = sql || ' SELECT * from ' || tnev; set sql = sql || ' where ' || fnev || ' = ' || cast(oldkod as varchar(12)); set sql = sql || ' with data'; execute immediate sql; /* Renew the IDs */ set sql = 'update ' || tmptablanev; set sql = sql || ' set ' || fnev || ' = ' + cast(newkod as varchar(12)); execute immediate sql; /* Recopy to the original table */ set sql = 'insert into ' || tnev; set sql = sql || ' select * from ' || tmptablanev; execute immediate sql; /* Drop it */ set sql = 'drop table ' || tmptablanev; execute immediate sql; END This can copy the data of a table ot itself - with new ids (for child copy). I want to say that I hope you will not change this feature in the future... Or are you want to change this? So we can build our programs to this dynamic SQL or not? Thanks: dd |
Mon, Mar 29 2010 2:50 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << I want to say that I hope you will not change this feature in the future... >> No, absolutely not. The SQL/PSM was designed this way to make it very easy to mix DML and DDL together, and will never change. << So we can build our programs to this dynamic SQL or not? >> Absolutely. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |