Redwood Scripting - UPDATE Query

[former_member561790]

Hello,

Within Redwood Script inside my Job session context, is it possible to use an SQL "UPDATE" statement when updating a custom table value?
Am I limited only to "SELECT" statements?

If I look through the admin guide, I can see in multiple places it says that SQL 92 is supported etc etc, but I can't see anything that says "only SELECT".

I would like to do something like:

final String SQL_UPDATE_COLUMN_VALUE =
      "UPDATE TableValue " +
      "   SET TableValue.ColumnValue = ? " + 
      " WHERE TableValue.ColumnName = ? " +
      "AND    TableValue.Table =  " +
      "       ( " +
      "       SELECT t.UniqueId " +
      "       FROM   Table t " +
      "       WHERE  t.Name = ? " +
      "       ) " +
      "AND    TableValue.Key = ? ";

/** create objParams and assign my param values 
?1 = VALUE1
?2 = COL1
?3 = MYTABLE
?4 = KEY1
**/

session.executeQuery(SQL_UPDATE_COLUMN_VALUE, objParams, callback);

Using the example above, when I try and run the query, I don't expect any output.
It comes back with a message saying "JCS-122035: Unable to persist: JCS-XXXXX: @locale2@:(com.redwood.scheduler.exception.Class.persistence.api.PersistenceException$RecoveryFailedException 'Not a select statement')"

Thanks in advance.

Darryl

[gmblom · Accepted solution]

Hello Darryl,

Updates you perform through the API. Using the executeObjectQuery you can fetch database objects directly into API Objects and start working with them. Don't forget to persist your changes when you want to do updates!

So to take your example:

{
  String sql = "select tv.* from TableValue tv, Table t where t.UniqueId = tv.Table and tv.ColumnName = ? and t.Name = ? and tv.Key = ?";
  for (TableValue tv : jcsSession.executeObjectQuery(TableValue.TYPE, sql, new Object [] {"<COL1>", "<MYTABLE>", "<KEY1>"}))
  {
    tv.setColumnValue("VALUE1");
  }
  jcsSession.persist();
}

Regards Gerben