Skip to content

Overview

Concepts and Principles

Development

Overview

IDEs

API Explorer

Releases

Release Notes

TORO Integrate

Coder Studio

Coder Cloud

Bug Reports

Search

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 Gloop SQL Services

To create a Gloop SQL Service, follow the steps below:

  1. Launch the Gloop SQL Service wizard by right-clicking on the target package's code directory 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.
  2. In the wizard, specify the following details:

    • Location - this is 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
  3. Click Finish.

Here's a short GIF showing you the process:

Creating a Gloop SQL Service

Shortcut for launching the wizard

In Coder Studio, you can open the Gloop SQL Service wizard by pressing ^N in macOS, and typing sql in the dialog's search box. After that, you can press Enter/Return.

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.

*Gloop SQL Service* editor

*Gloop SQL Service* editor

Here's a breakdown of its primary components:

  1. Connection Name drop-down list

    The name of the database TORO Integrate will connect to by default 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 $integrateConnectionPool input property to the name of another database name.

  2. Type drop-down list

    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 statement.
    Select Multi Returns an Input cursor representing the result set rows from a Select statement
    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
  3. Database metadata panel

    Displays the target database's schemas, functions, and procedures. You can use this to navigate around the database.

  4. 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:

Property Type Applicable Operations Description
$integrateConnectionPool Input All Operations The database name.
$generatedKeys Output Insert The ID(s) of the record(s) that were inserted.
$parentJDBCBatchCursor 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.
$updateCount Output All Single Operations The total number of records that were updated/deleted as a result of the operation.
$batchSize Output All Batch Operations The size the batch will reach before sending the updated data to the database

Parameterized Queries

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 FIRSTNAME column is equal to the service's firstName property when executed, and the row's LASTNAME column is equal to the service's lastName property. As you modify the SQL and add named parameters, the Input/Output view will change as shown below:

Coder Studio I/O View changing with SQL Statement

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:

*Gloop SQL Service* sql generator

*Gloop SQL Service* sql generator

Transactions

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.integrateSQLMethodsclass (in the core package), and example services exist in the included examples TORO Integrate package, in the databaseMigration package. Similar to Java code:

  • A transaction start is executed
  • A try block is executed, manipulating data in the database (and optionally, JMS destinations)
  • If everything worked, a commit is performed (generally the last line in the try block )
  • If anything went wrong, a rollback is executed in a catch block

In the corresponding Gloop Service below you can see that the code is very similar:

  • A transaction is started on line 3 (before the try block)
  • A commit is executed on line 15 (at the end of the try block) if everything was executed without error
  • A rollback in the corresponding catch block on line 17 is executed if anything went wrong

*Gloop SQL Service* JDBC Transctions

XA Transactions

If configured correctly, TORO Integrate will also handle XA Transactions.