Using the Database API in Groovy

This post shows how to implement database operations using the Intrexx Database API in Groovy scripts within processes.

Benefits of the Intrexx API

The Intrexx API offers a wide range of useful functions to make it easier to work with database operations in Intrexx. For example, there is a client SQL function called "DATAGROUP" that allows you to specify a data group's GUID in SQL statements instead of its name.

Example

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

Instead of the table name, DATAGROUP('GUID_DATENGRUPPE') is used here. This approach helps avoid hard-coded data group names. Furthermore, proper functionality is ensured even when applications and processes are imported multiple times, since GUIDs within Intrexx are replaced during multiple imports. However, it is not possible to replace names.

Another advantage of using the Intrexx API is that, compared to the standard Java or Groovy API, less code is required to perform database queries and operations in Intrexx.

Example

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

Using this single line, you can determine the maximum ID value in a table and, at the same time, specify a fallback value using the parameter 0 in case no record is found and the result is therefore "null."

Groovy scripts become more robust when the Intrexx database API is used. For example, numeric range overflows result in an ArithmeticException rather than a new, unpredictable assignment of a value to the variable. In addition, by using the Intrexx API, you remain within the Intrexx environment at all times (e.g., with regard to transaction security and access rights management).

Creating and Closing Prepared Statements and Result Sets

When creating prepared statements in conjunction with loops, you should always make sure to create the statements before the loop, populate and execute them within the loop, and then close them again outside the loop. This prevents memory overflows and improves performance.

Wrong

            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()

        

In general, care should be taken to call the close() command as early as possible. It's best to always close statements or result sets as soon as they have been processed and are no longer needed; don't wait until the end of the script to do so. However, it is important to note that close() must only be called on Prepared Statement and Result Set objects. Are closures such as, for example,

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

        

When it is used, close() does not need to be called—nor can it be—because there is no corresponding object at this point that needs to be closed. In this case, Intrexx handles the necessary resource management and approval.

Iterating Over Result Sets

When iterating over a result set, care must be taken to ensure that the result set is processed correctly. In addition, typed methods such as getBooleanValue(index i) should be used to precisely specify the return type. This helps avoid database-specific differences in how data is represented and in the data types used. The following code is incorrect and causes errors during iteration:

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

        

Instead, one of the following options 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)

}