Dynamic Update SQL Statement

Is it possible to execute a sql update statement from objectscript? This code isn't working for me.

 

Set tSQL = "UPDATE table Set Status = 'Completed' WHERE ID in (1,2,3,4)"
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.

 

  • 0
  • 0
  • 361
  • 3
  • 2

Answers

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.

set result = tStatement.%Execute()

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)")

-Dan

If 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

  SET rset = tStatement.%Execute()

Comments