Use the database API in Groovy

This article shows how database operations with the Intrexx database API are implemented in processes in the Groovy script.

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 of a data group instead of its name in SQL statements.

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 functioning is also ensured if applications and processes are imported multiple times, as GUIDs that occur within Intrexx are replaced during a multiple import. 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 )
        

With the help of this one line, it is possible to determine the maximum value of the ID of a table and at the same time specify a fallback value with the parameter 0 in the event that no data record was found and therefore "null" is returned as the result.

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. However, it should be noted that close() only needs to 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 over a result set, ensure that the result set is processed correctly. In addition, typed methods such as 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)

}