Gloop SQL services
A Gloop SQL service is a special type of Gloop service capable of executing SQL statements. This is the type of service you should use if you want to perform SQL statements against a database via Gloop.
Creating a Gloop SQL service
To create a Gloop SQL service, follow the steps below:
- Launch the Gloop SQL service wizard by right-clicking on the target package's
codedirectory or any of the code folders underneath it where you'd like to store the Gloop SQL service, and then select New > Gloop SQL Service.
In the wizard, specify the following details:
- Location - where the Gloop SQL service will reside; its default value will be the folder where you started the wizard from
- Name - the name of the Gloop SQL service
- Connection Name - the name of the database that the service will connect to while developing the service, and by default when executing it
- Query Type - the type of SQL statement the service will perform
Launch the wizard using keyboard shortcuts only
In Coder Studio, you can open the Gloop SQL service wizard by pressing in and typing
sql in the dialog's
search box. After that, press and the wizard should appear.
Editing Gloop SQL services
After creating the Gloop SQL service, Coder will automatically open the service for you, and you will be shown the Gloop SQL service editor.
Here's a breakdown of its primary components:
Connection Name drop-down
The name of the database TORO Integrate will connect to when being executed. By clicking on the drop-down list, you will be able to choose the database you want to execute queries against. Only registered databases will be selectable. You can change the target database at runtime by setting the
$integrateConnectionPoolinput property to the name of another database.
SQL Service Type drop-down
The specific type of operation that the service will execute. Currently, these are the supported types:
Type Description Select Single Used to get the first row of the result set from a
Select Multi Returns an input cursor representing the result set rows from a
Insert Used to insert one row into a table. Insert Batch Used to insert multiple rows into a table. These types of services return an output cursor. Update Used to update a single row. Update Batch Used to update multiple rows in a table. These types of services return an output cursor. Delete Used to delete a single row. Delete Batch Used to delete multiple rows from a table. These types of services return an output cursor.
Displays the target database's schemas, functions, and procedures. You can use this to navigate around the database.
SQL Statement text area
The SQL statement based on the SQL operation you selected. You can edit the service's SQL directly using this component.
Inputs and outputs
Like regular Gloop services, Gloop SQL services have inputs and outputs, too. As the SQL statement changes, the Gloop SQL service's inputs and outputs will change as well.
If the SQL statement is incorrect or the output model could not be generated, then a warning will appear underneath the SQL editor. When you hover your mouse pointer over the warning, a tooltip will show up which will provide you with more information on what is wrong with the statement.
Gloop SQL services also have special input and output properties you can set or fetch:
||Input||All operations||The database name.|
||Output||Insert||The ID(s) of the record(s) that were inserted.|
||Output||All batch operations||Any parent table that needs to have it's batch executed prior to any child batches (to avoid foreign key issues). If you have left this empty, the batch operation will be executed without executing the parent batch first.|
||Output||All single operations||The total number of records that were updated/deleted as a result of the operation.|
||Output||All batch operations||The size the batch will reach before sending the updated data to the database|
Parameterized SQL queries are also supported in Gloop. Parameters allow for flexible SQL commands - arguments don't need to be hard-coded in the SQL statement, you can specify their values by setting the parameters. This functionality is the same as a Java prepared statement.
However, instead of using a question mark in the statement, simply replace the substitutable value in the
command with a placeholder in this format
:<name-of-parameter>. After you have successfully re-structured the SQL
statement, the Gloop SQL service will have additional input properties that have the same name as the SQL
parameters. Below is an example:
1 2 3 4 5 6 7 8
SELECT ID, FIRSTNAME, LASTNAME, EMAIL FROM EMPLOYEES WHERE FIRSTNAME = 'JOHN' AND LASTNAME = 'CITIZEN'
To allow for Gloop to use input properties for this statement, change this to:
1 2 3 4 5 6 7 8
SELECT ID, FIRSTNAME, LASTNAME, EMAIL FROM EMPLOYEES WHERE FIRSTNAME = :firstName AND LASTNAME = :lastName
This SQL statement will allow us to fetch rows from the
EMPLOYEES table provided that the row's
is equal to the service's
firstName property when executed, and the row's
LASTNAME column is equal to the
lastName property. As you modify the SQL and add named parameters, the Input/Output view will change as
You can also use Coder to help write the SQL statements for you; simply right click on the columns you would like to include in the statement, and choose the type of SQL statement you would like to execute against those columns, as shown below:
TORO Integrate includes services that can be used to wrap Gloop SQL services in
JDBC transactions like regular Java JDBC code. These services exist in the
io.toro.integrate.SqlMethods class from the
Example services, meanwhile, exist in the included
examples package, in the
databaseMigration code directory. Similar to Java code:
- A transaction
tryblock is executed, manipulating data in the database (and optionally, JMS destinations)
- If everything worked, a
commitis performed (generally the last line in the
- If anything went wrong, a
rollbackis executed in a
In the corresponding Gloop service below, you can see that the code is very similar:
- A transaction is started on line
- A commit is executed on line
15(at the end of the
tryblock) if everything was executed without error
- A rollback in the corresponding
catchblock on line
17is executed if anything went wrong