Skip to Content

Redwood Scripting - UPDATE Query

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Oct 09, 2019 at 10:21 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.