New post
Question
· Apr 25, 2022

Run multiple Update sql queries

#ObjectScript #SQL #InterSystems IRIS

Hi everyone, how do you run multiple quires?

I have tried couple of different ways, but not working.

 SET sql = 2

    Set sql(1) = "UPDATE QUERY"

    Set sql(2) = "UPDATE QUERY"

    Set sqlStatement=##class(%SQL.Statement).%New()

    Set sc1=sqlStatement.%Prepare(.sql)

    If $$$ISOK(sc1)     {

       Set tResult = sqlStatement.%Execute()    

    }

    else{

        $$$LOGERROR("Failed")

    }

Product version: IRIS 2022.1
Discussion (4)0
Log in or sign up to continue
FELIPE MATTA · Apr 25, 2022

The %Statement class doesn't apply to your idea of ​​multiple update executions because the code uses a "sql" variable with its subscripts 1 and 2 as code snippets for concatenation of the full command.

According to your needs, I used the %ResultSet class in the example below and in the "sql" variable assigns a complete update command to each subscriber.
 

ClassMethod MultipleUpdate() As %Status
{
   Try 
   {
     Set sql = 2,tSC = $$$OK
     Set sql(1) = "UPDATE Cinema.Theater SET TheaterName = 'Theater A',AdultPrice=3 WHERE ID = 10"
     Set sql(2) = "UPDATE Cinema.Theater SET TheaterName = 'Theater B',AdultPrice=9 WHERE ID = 11"      
     Set key=$Order(sql(""))
     While (key'="") 
     { 
        Set tResult = ##class(%ResultSet).%New()
        Do tResult.Prepare(sql(key))
        Set tSC = tResult.Execute()  
        
        Set key = $ORDER(sql(key)) 
     }
   } 
   Catch(tException)
   {
      #DIM tException As %Exception.AbstractException;
      Set tSC = tException.AsStatus()
   }
   
   Quit tSC
}

Documentation References:

%SQL.Statement - Intersystems Documentation
%ResultSet - Intersystems Documentation

3 0
Mihoko Iijima · Apr 26, 2022

ImportDDL() method in %SYSTEM.SQL.Schema class can run multiple queries.

Documentation is https://docs.intersystems.com/irislatest/csp/docbook/Doc.View.cls?KEY=GSQL_import#GSQL_import_cache

Step1: preparing import file like below:

You need to use "go" in end of each query.  (c:\temp\updatequeries.txt)

UPDATE Test2.Person Set Name='abc' where ID=1
go
UPDATE Test2.Person Set Name='efg' where ID=2
go
UPDATE Test2.Person Set Name='hij' where ID=3
go

Step2: running  ImportDDL() method like below:

USER>do $system.SQL.Schema.ImportDDL("c:\temp\updatequeries.txt","c:\temp\import.log","IRIS")
 
 
Importing SQL Statements from file: c:\temp\updatequeries.txt
 
 
Recording any errors to principal device and log file: c:\temp\import.log
 
 
  SQL statement to process (number 1):
     UPDATE Test2.Person Set Name='abc' where ID=1
      Preparing SQL statement...
      Executing SQL statement...
  DONE
 
  SQL statement to process (number 2):
     UPDATE Test2.Person Set Name='efg' where ID=2
      Preparing SQL statement...
      Executing SQL statement...
  DONE
 
  SQL statement to process (number 3):
     UPDATE Test2.Person Set Name='hij' where ID=3
      Preparing SQL statement...
      Executing SQL statement...
  DONE
 
Elapsed time: .01458 seconds
USER>
0 0
Vitaliy Serdtsev · Apr 26, 2022

Try this (%Dialect):

Set sqlStatement=##class(%SQL.Statement).%New(,,"MSSQL")
 
Simple example
0 0