Use the database API in Groovy

This workshop demonstrates how database operations can be implemented using the database API in Groovy script in processes.

Advantages of the Intrexx API

The Intrexx API has a number of useful features to simplify working with database operations in Intrexx. For example, there is a client SQL function "DATAGROUP" that allows you to specify the GUID in SQL statements instead of the name of a data group.

Example

g_dbQuery.executeAndGetScalarValue(conn, "SELECT COUNT(LID) FROM DATAGROUP('DAF7CECF66481FCABE50E529828116EAFE906962')")

Instead of the table name, DATAGROUP('DATA_GROUP_GUID') is used here. In this way, hard-coded data group names can be avoided. In addition, correct operation is guaranteed even if applications and processes are imported multiple times, as existing GUIDs will be replaced in this case. However, replacing names is not possible.

Another advantage of using the Intrexx API is that less code will be needed to implement database queries and operations within Intrexx in comparison to standard Java or Groovy API.

Example

def iMax = g_dbQuery.executeAndGetScalarValue(g_dbConnections.systemConnection, "SELECT MAX(LID) FROM DATAGROUP('DAF7CECF66481FCABE50E529828116EAFE906962')", 0 )

Using this one line it is possible to determine the maximum value of the ID field of a table, and at the same time provide a fallback value with the parameter 0 if a data record cannot be found; thus "null" is returned.

In addition, scripts will have a greater degree of robustness by using the Intrexx database API. For example, overflows from number ranges result in an ArithmeticException rather than a new, unpredictable value assignment to the variable. In addition, by using the Intrexx API you never leave the Intrexx environment (e.g. for transaction security and permissions management).

Generating and closing prepared statements and result sets

When generating prepared statements in conjunction with loops, you should always ensure that the statements are generated before the loop, populated and executed within the loop, and subsequently closed after the loop. In this manner, buffer overflows can be avoided and a higher level of performance achieved.

Incorrect

for (i in 1..1000)
{
    def stmt = g_dbQuery.prepare(conn, "UPDATE DATAGROUP('DAF7CECF66481FCABE50E529828116EAFE906962') SET TEXT = ? WHERE LID = ?")

    stmt.setString(1, "Number ${i}")
    stmt.setInt(2, i)
    stmt.executeUpdate()
    stmt.close()
}

Correct

def stmt = g_dbQuery.prepare(conn, "UPDATE DATAGROUP('DAF7CECF66481FCABE50E529828116EAFE906962') SET TEXT = ? WHERE LID = ?")

for (i in 1..1000)
{
    stmt.setString(1, "Number ${i}")
    stmt.setInt(2, i)
    stmt.executeUpdate()
}
stmt.close()

Generally, you should ensure that the close() command is called as early as possible. It is always best to close statements or result sets as soon as they have been processed and are no longer needed – do not wait until the end of the script. It is also important to note that close() must only be called for prepared statement and result set objects. If closures such as

g_dbQuery.executeUpdate(conn, "UPDATE DATAGROUP('DAF7CECF66481FCABE50E529828116EAFE906962') SET TEXT = ? WHERE LID = ?")
{
        setString(1, "Hello World")
        setInt(2, 1)
}

are used, close() must not and cannot be called, as, at this point, there is no corresponding object available that needs to be closed. In this case, Intrexx assumes responsibility for the required resource management and sharing.

Iterating through result sets

When iterating through a result set it is important to ensure that the result sets are processed correctly. Moreover, typed methods like "getBooleanValue(index i)" should be used to precisely specify the data type of the return. Thus database-specific differences in the mapping of data and the data types used can be avoided. He following code is incorrect and will lead to errors during the iteration:

rs.each {
    def strVal1 = rs.getStringValue(1)
    def iVal2   = rs.getIntValue(2)
    def bVal3   = rs.getBooleanValue(3)
}

Instead, one of the following variants should be used to ensure correct iteration. Example: Direct processing of the result set

while(rs.next())
{
    def strVal1 = rs.getStringValue(1)
    def iVal2   = rs.getIntValue(2)
    def bVal3   = rs.getBooleanValue(3)
}

Beispiel: Verarbeitung einzelner Zeilen eines Result Sets

rs.each {row ->
    def strVal1 = row.getStringValue(1)
    def iVal2   = row.getIntValue(2)
    def bVal3   = row.getBooleanValue(3)

}