Execution of the SQL commands block at once

Primary tabs

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!

Replies

Reply by Eduard Lebedyuk 12 days ago

Check $SYSTEM.SQL.DDLImport().

0 0

Reply by DMITRIY SOSNOVIСН 11 days ago

@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.

0 0

Accepted Answer

Reply by Ihsan Ahmed 11 days ago

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.

0 0