Advanced Techniques - 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.
False
for (i in 1..1000)
{
def stmt = g_dbQuery.prepare(conn, "UPDATE DATAGROUP('DAF7CECF66481FCABE50E529828116EAFE906962') SET TEXT = ? WHERE LID = ?")
stmt.setString(1, "Nummer ${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, "Nummer ${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.
The 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)
}
Example: Processing individual rows of a result set
rs.each {row ->
def strVal1 = row.getStringValue(1)
def iVal2 = row.getIntValue(2)
def bVal3 = row.getBooleanValue(3)
}