New Helper Methods in Groovy to Support Adding Elements in an Array to SQL

Previously, the product provided a new method concatenateGroovyStrings(String... args) to be used to build complex SQL statements for createPreparedStatement. Additional helper methods are now provided for use within the concatenateGroovyStrings method to support dynamically adding information from an array into the SQL statement you are preparing.

The following helper methods are now supported:

• concatenateBindName(String bindName, int index): This method allows you to create one or more distinct bind variables representing different instances of data in an array.
• concatenateSqlInClause(String columnExpr, String bindBase, int start, int count): This method is used to generate an 'in' clause, resulting in distinct bind variables for all the data within an array.
• literalFor(int i) / literalFor(long i): These methods allow you to get the value of the current index entry while you are within a 'for' loop.

IMPORTANT: In a future release, using StringBuilder or StringBuffer in the createPreparedStatement will no longer be supported. The system will validate that if the argument used in the createPreparedStatement is a string itself, a concatenation of strings using "+", or created using the concatenateGroovyStrings method.

The new methods allow for Groovy script writers to safely code a complex SQL statement using elements in an array.

Steps to enable and configure

Per the information provided with the introduction of concatenateGroovyStrings in the previous release, validation on building the createPreparedStatement is tightening up so you should be ensuring that your custom scripts that are using a concatenation technique for building a query are refactored to use the concatenateGroovyStrings method. These additional helper methods are only needed if you are dynamically building the SQL statement using data from an array.

Key resources

Refer to the Using Groovy Within Scripts topic in the Script Options section of the Administrative User Guide for details of the syntax for these new methods.