Process workflow scripts, the very mentioning can make a developer shudder. However, scripts are the unsung heroes of the Process workflow and have been an integral part of ImageSource’s Customer Solutions. While the Oracle IPM Process workflow now supports using .NET dlls as script events, that was not always the case. VBScript code was originally used to handle workflow events and that option for such is still around. There are a few reasons why you might still need to code a process script in VBScript, either because it is an upgrade of legacy code, the system is a pre 7.7 version of IPM or if the required custom functionality is so small that writing a script is much quicker than a full blown .NET Module.
Below are some helpful tips and tricks if you find yourself writing a VBScript workflow event.
Write Logs That Make Sense
While flagging parts of the code as A, B and C might make sense to you while developing the script, trying to remember what they meant years down the line (like for an enhancement or bug fix) will be a futile exercise. Better yet, try explaining cryptic log messages to the Systems Engineer or another developer. After they’ve finished giving you annoyed looks, it might be time to make the logging messages understandable to another human being. When writing logs, output something useful like current field values, sql parameters or the result of conditional operations. Just make sure not to log sensitive data and also provide an option to turn logging off if it is not needed.
Error Handling Is Key
By default, IPM scripts use the following option: On Error Resume Next. This means that if an error occurs in the code, it will continue operating until the error is handled or the code finishes. Normally, error handling code is put after code like SQL calls, file system operations and usage of the IPM SDK. However, sometimes developers can get lazy and forget to put error handling in the code where it would be useful, only to have it handled later on with an error log message that does not make sense. For example take a look at the following code:
oCon.Open "File Name=C:\process\connection.udl"
oRS.Open "select * from DATA_TABLE_1 where ID=" & iId, oCon, Readonly, Readonly
If Not oRS.EOF Then
iValue = oRS("VALUE_1")
End If
oRS.Close
oCon.Execute "insert into DATA_TABLE_2 (VALUE_1) values (" & iValue & ")"
If Eval("Err.Number <> 0") Then
objExecutionContext.ErrorDescription = objExecutionContext.ErrorDescription & _
"Example Script: Error inserting data into DATA_TABLE_2 - " & Err.Description & vbCrLf
oCon.Close
Set oCon = Nothing
Exit Sub
End If
If an error were to occur at either the open connection or select data steps, the error log would still report that there was an error inserting data into DATA_TABLE_2. A better solution would have error handling checks after the open connection and sql select lines of code.
Remember To Set Objects
Working in the .NET environment, you tend to forget some of the nuances of writing unmanaged code. One of these is remembering to set objects when they are assigned in VBScript. This can be the source of much unneeded frustration when the code continually errors out without a very helpful reason why. Always keep in mind that when not assigning a primitive variable, always use Set.
Clean Up After Yourself
Another thing to keep in mind when writing scripts is to clean up your variables when you are done using them. This is especially important for preventing memory leaks in the script, which might have to run 24/7. Always remember to set your objects to nothing, to close all sql connections and recordsets, and even clear string values when they are not needed.
Be Careful With String Concatenation
VBScript string concatenation has an N-Squared cost. Repeatedly concatenating a string within a loop is a very expensive operation. While okay for small strings, it is not recommended for building out long strings, like from database information. The suggested alternative to the built in concatenation is to preallocate memory for the concatenation operation. A more detailed solution can be found at Microsoft’s support site.
Hopefully this advice will help you in the world of IPM Process script writing.
Richard Franzen
Developer
ImageSource, Inc.
