Skip to end of metadata
Go to start of metadata

Groovy supports a few neat ways to work with SQL more easily and to make SQL more Groovy. You can perform queries and SQL statements, passing in variables easily with proper handling of statements, connections and exception handling thanks to closures.

In the above example, you can refer to the various columns by name, using the property syntax on the row variable (e.g. it.name) or you can refer to the columns by their index (e.g. it[0]) For example:

Or you can create a DataSet which allows you to query SQL using familar closure syntax so that the same query could work easily on in memory objects or via SQL. e.g.

Advanced Usage

In this example, we create a table, make changes to it and confirm the changes worked.

Combining with MarkupBuilder Example

Here's an example of using Groovy SQL along with GroovyMarkup

This could generate, dynamically something like

There's an example test case which demonstrates all of these query mechanisms in action.

Stored procedure support

An example of calling a FullName stored procedure which takes a param ('Sam' in the example) and returns a VARCHAR.

The same example again but with a GString variation:

Here is an example of a stored procedure with an out parameter:

Some additional advanced stored procedure examples:

Clob Notes

CLOB are objects that cannot be extracted with a piece of code like:

because later

can fail. This happens cause the object is not reachable any more since the connection might be already closed: note I say might cause at least on Oracle the errors are random (and hard to understand).

A possible way to act on CLOB data is to use eachRow

Further Information

There is also an additional GSQL module you might want to check out.

  • No labels