Hello!

Can I somehow call Scriptcode stored in a variable from within a procedure or trigger?
I'm storing code in an variable and executing it:

EXECUTE IMMEDIATE ScriptCodeV;

One line SQL Works like 'INSERT INTO....' but not multiline scripts. Is this possible?

Hoken,

I am not sure I understand your question.

My way of doing what I think you are describing would be to write a FUNCTION or PROCEDURE.

You can then have a SQL script/statement and use the form:

CALL MyProcedure([your input/output parameters listed here]);

MyProcedure can then include

EXECUTE IMMEDIATE
 ' INSERT INTO MyTable ([your fieldnames here]) VALUES ([your values here])';

--

Since 1 procedure can call another and calls to functions & procedures can be included in triggers I _think_  this would do what you want.

Ah Hoken,

I have re-read your post & now maybe understand it better.

I think you mean that you have a function which contains a variable declaration:

DECLARE ScriptVar CLOB DEFAULT 'INSERT INTO MyTable ...';

which is later called in that Function/Procedure.

If this is what you mean then multi-line strings are no problem at all. EDBMgr reads the whole string, even if it goes over multiple lines until it find the next apostrophe. The only pain is when the string itself contains apostrophes, in which case they must be doubled up

' INSERT INTO MyTable (Name) VALUES (''Hoken'') '

Hoken


I don't read your post the same as Adam. What I think you're looking for is for EXECUTE IMMEDIATE to run multiple lines of SQL like an old DBISAM script. If that's right then the answer is no it won't do it itself but you can code it to do it - providing the SQL lines are delimited. The approach is pretty much as it would be in Delphi - extract each SQL command and execute it in a loop. The following is totally untested

DECLARE Holder STRING;
DECLARE StrEnd INTEGER;
DECLARE Cmnd STRING;
DECLARE Delim STRING DEFAULT ';';

SET Holder = string of commands;
SET StrEnd = POS(Delim,Holder);

IF StrEnd > 0 THEN
REPEAT
SET Cmnd SUBSTR(Holder,1,StrEnd-1);
SET Holder = SUBSTR(Holder,StrEnd+1,LENGTH(Holder);
EXECUTE IMMEDIATE Cmnd;
SET StrEnd = POS(Delim,Holder);
UNTIL StrEnd = 0 END REPEAT;
END IF;

Roy Lambert [Team Elevate]

Thank you Adam and Roy!

What I was trying to do was to make "dynamic" triggers that loads ScriptCode from a table and executes it. I'm not sure it was a good idea anyway...