Execution of the SQL commands block at once
Is there any way to execute many SQL commands at once as an (anonymous)block?
for example in PostgreSQL there is
DO $$
BEGIN
--here is Inserts/Updates/Deletes
END $$Or in Oracle
BEGIN
END; Thanks for the answers!
Discussion (3)0
Comments
Check $SYSTEM.SQL.DDLImport().
@Eduard Lebedyuk Thanks for the answer, but it not what I wanted, but you give me an idea; there is an $SYSTEM.SQL.EXECUTE function, so I add ClassMethod
ClassMethod EXECUTEBLOCK(aScript As %Library.String(MAXLEN="")) As %Library.String [ SqlName = EXECUTEBLOCK, SqlProc ]{
//for case String value sended from the SQL context like "value"
//e.g. SQLUser.EXECUTEBLOCK( 'INSERT INTO A(b,c,d)VALUES("d","e","f");INSERT INTO A(b,c,d)VALUES("g","h","h");')
SET replacedScript= $Replace(aScript,$CHAR(34),$CHAR(39))TRY{
SET retval = $SYSTEM.SQL.Execute(replacedScript,2,"MSSQL")
RETURN retval.%Message} CATCH err {RETURN err.Name}}, which I later called as a regular stored procedure, by
EXEC(CALL) EXECUTEBLOCK( 'DELETE FROM A WHERE A.Col=1;DELETE FROM A WHERE A.Col=2;DELETE FROM A WHERE A.Col=3;' )
So it will be three DELETE statements at once.
Anyway, thanks for the idea.
PostgreSQL might support multiple line Execute for sure, in DBeaver you can use Alt+X by highlighting the lines to be executed, please make sure you have the semi colon at the end of each SQL.