Removing Terminator in SQL job makes process tab un-editable | unexpected error

Products

• CA Automic Workload Automation - Automation Engine
• CA Automic One Automation
• Automic SaaS

Issue/Introduction

In versions 21.0.14 and 24.4.1, when removing the terminator symbol from a SQL_SET_STATEMENT_TERMINATOR, saving the changes, and re-opening, the Process tab is no longer editable and now shows an error:

Error
An unexpected error occurred. Further details are available in the logfiles.

Clicking on this then shows two stack traces:

• Invocation of method attach in com.automic.ecc.ae.sheet.formbuilder.common.extension.FormScriptEditorExtension$$Lambda$2343/0x0000020295b81000 failed.com.vaadin.event.ListenerMethod$MethodException
  com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:533)
  com.vaadin.event.EventRouter.fireEvent(EventRouter.java:200)
  com.vaadin.event.EventRouter.fireEvent(EventRouter.java:163)
  ...

• Cannot invoke "String.length()" because "suffix" is nulljava.lang.NullPointerException
  java.base/java.lang.String.endsWith(String.java:2317)
  com.automic.ecc.plugins.sql.formbuilder.scripteditor.provider.SQLCommandViewFactory.createCodeCommand(SQLCommandViewFactory.java:52)
  ...

Steps to reproduce:

1. Create a JOBS.SQL object
2. Using the "Form" choice in the process tab, add an Automation Engine Command -> Set Statement Termintor (SQL_SET_STATEMENT_TERMINATOR)
3. Add a command -> SQL Statements -> User Defined (USER_DEFINED) and put whatever you'd like in here, f.ex. select sysdate from dual;
4. Save the changes
5. Go to the Set Statement Terminator command and remove the symbol so that it is blank
6. Save the changes
7. Close the object
8. Re-open the object

Environment

Automation Engine and AWI versions 21.0.14 or 24.4.1

Cause

This is a known issue

Resolution

This has been identified as a known issue and is fixed with Automic Automation release 24.4.2.

Possible workarounds:

1) Preferred: Use Version Management to Restore an older version where the symbol was there.  This requires the use of Version Management which must already be in place before this workaround can be used.

2) Unpreferred due to the risk of human error: Edit xml files:

• In the AWI, export the object that was updated
• Open the .xml export in a text editor
• In the line that shows "<MSCRI><![CDATA[SQL_SET_STATEMENT_TERMINATOR TERM=;"  add the desired terminator between the equal sign and the semi-colon (example below with @ as the terminator):
  
  <MSCRI><![CDATA[SQL_SET_STATEMENT_TERMINATOR TERM=@;
  
  
• Save the xml file
• In the AWI, import the updated .xml file, choosing to "Replace existing objects"

 

To find currently affected items:

1. In the AWI, click in the search bar in the upper right, don't enter anything but instead choose "Search for objects"
   
2. In the search pane that comes up, click the "Filter" button and then "Add Filter Criteria" and choose "Object Contents":
   
   
   
3. In the Object Contents text box, add in the following:
   SQL_SET_STATEMENT_TERMINATOR TERM=;
   
4. Click Filter