Executing SQL Update

The SQL update query inserts, updates, or deletes database records, but does not return any data to the workflow rule. You can execute an SQL update using the SqlUtils.executeUpdate() static method:

SqlUtils.executeUpdate("pmpsum", "DELETE FROM pmpsum");

The SQL query can contain binding expressions:

SqlUtils.executeUpdate("site", "UPDATE site SET area_st_comn_nocup = (SELECT ${sql.isNull('SUM(rm.area)', 0)} FROM bl, rm WHERE ...");

The SQL query can contain expressions that use workflow rule parameters passed from the client. In this case you must use a DataSource, and explicitly define the DataSource parameters using the DataSource.addParameter() method:

String sql = "UPDATE wo SET priority = " +
"(SELECT MAX(wr.priority) FROM wr WHERE wr.wo_id = ${parameters['woId']}) " +
" WHERE wo.wo_id = ${parameters['woId']}";

DataSource ds = DataSourceFactory.createDataSourceForFields("wo", new String[] {"wo_id"});
ds.addQuery(sql);
ds.addParameter("woId", workOrderId, DataSource.DATA_TYPE_INTEGER));
ds.executeUpdate();

In general, avoid using direct SQL except for bulk update statements that must happen entirely in the data tier for performance reasons, and as such are not appropriate for the dataSource object. Single record or looping manipulations should always use the dataSource object.

The dataSource object also always applies the Virtual Private Archibus restrictions of the current users; however, SqlUtils.executeUpdate() always executes the statement literally. If the action you are performing is sensitive, you will want to add this restriction in your own code.