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