Run multiple Update sql queries
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")
}
Comments
Please check out this documentation, where there is an example of using an array holding the sql statement:
%SQL.Statement - InterSystems IRIS Data Platform 2021.2 - including private class members
According to that document, the query your code builds and passes to %Prepare is "UPDATE QUERY UPDATE QUERY", which is not valid.
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
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
goStep2: 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>
Try this (%Dialect):
Set sqlStatement=##class(%SQL.Statement).%New(,,"MSSQL")
Simple example
Class dc.test Extends %Persistent
{
Property t As %Integer;
ClassMethod Test()
{
d ..%KillExtent()
&sql(insert into dc.test(t) values(30))
&sql(insert into dc.test(t) values(303))
zw ^dc.testD
w !
s sql=2
s sql(1)="update dc.test set t=t+1 where ID=1"
s sql(2)="update dc.test set t=t+2 where ID=2"
s st=##class(%SQL.Statement).%New(,,"MSSQL")
w "SQLCODE=",st.%ExecDirect(.st,.sql).%SQLCODE,!!
zw ^dc.testD
}
}
Output:
USER>d ##class(dc.test).Test()
^dc.testD=2
^dc.testD(1)=$lb("",30)
^dc.testD(2)=$lb("",303)
SQLCODE=0
^dc.testD=2
^dc.testD(1)=$lb("",31)
^dc.testD(2)=$lb("",305)