New post
Question
· Sep 5, 2017

Dynamic Update SQL Statement

#ObjectScript #SQL #Caché

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.

Discussion (4)0
Log in or sign up to continue
Dan Pasco · Sep 5, 2017

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
2 0
Chris Stewart · Sep 6, 2017

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

0 0