Dynamic Update SQL Statement
Is it possible to execute a sql update statement from objectscript? This code isn't working for me.
Set tStatement = ##class(%SQL.Statement).%New()
Set tSC = tStatement.%Prepare(tSQL)
If I write my dynamic sql to the event log, copy and execute it in the Management Portal, it works fine.
Yes, this should work but you are missing a step - you must execute the statement. Dynamic SQL allows for a statement to be prepared once and executed many times. A prepared statement is executed, returning a statement result. You can provide different parameter values for each execution.
Alternatively, you can do this in a single comment -
set result = $system.SQL.Execute("UPDATE table Set Status = 'Completed' WHERE ID in (1,2,3,4)") -DanIf I'm iterating through my dynamic SQL, do I need to clear my variables when I re-use them?
If you're iterating that would typically be done with a CURSOR. Each fetch cycle would pop the new state of the variables in, in line with each row (make sure you check for SQLCODE while doing this, to determine if there is a row to fetch).
More documentation on this is at
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...
EDIT:
Mixed my dynamic SQL methods there. You would use rset.%Next to do this iteration in %SQL.Statement, but the general theory still holds
http://localhost:57773/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_dynsql#G...
Hi Paul
You will need to execute it after Preparing it