Skip to content

Toro Cloud Dev Center


SQL services

A SQL service is a special type of 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 SQL service

To create a SQL service, follow the steps below:

Creating a SQL service

Creating a SQL service

  1. Launch the SQL service wizard by right-clicking on the target package's code directory (or any of the code directories underneath it) where you'd like to store the SQL service, and then select New > SQL Service.
  2. In the wizard, specify the following details:

    • Location - where the SQL service will reside; its default value will be the folder where you started the wizard from
    • Name - the name of the 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.

Launch the wizard using keyboard shortcuts only

In Martini, you can open the SQL service wizard by pressing (or in Martini Online) and typing sql in the dialog's search box. After that, press and the wizard will appear.

Editing SQL services

After creating the SQL service, Martini will automatically open the service for you, and you will be shown the SQL service editor.

SQL service editor

SQL service editor

Here's a breakdown of its primary components:

  1. Connection Name drop-down

    The name of the database connection Martini 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 $martiniConnectionPool input property to the name of another database.

  2. 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 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 tree

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

    Export a table to a data model

    You can create a data model from a database table by right clicking the latter in the Database tree, and then choosing Export to data model from the appearing context menu.

  4. Statement text area

    The SQL statement based on the SQL operation you selected. You can edit the service's SQL directly using this component.

    Format your SQL query

    You can format your query by right clicking on the statement text area and selecting Format, or by using the shortcut .

    Formatting SQL query

Export a SQL service to a SQL query

You can create a SQL query from a SQL service file by right clicking the latter from the Navigator, and then choosing Export > SQL Query from the appearing context menu.

Inputs and outputs

Like regular services, SQL services have inputs and outputs, too. As the SQL statement changes, the 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.

SQL services also have special input and output properties you can set or fetch:

Property Type Applicable Operations Description
$martiniConnectionPool 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 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:

Input/Output view changing with SQL statement

Input/Output view changing with SQL statement

You can also use Martini to help write the SQL statements for you. Right click on the table or columns you would like to include in the statement and choose the type of SQL statement you would like to execute from the appearing context menu. The items in the context menu will depend on the selected value for the Type dropdown. In the example below, Update was selected for Type, hence selectable statements are UPDATE statements only.

SQL service, SQL generator

SQL service, SQL generator

Generate statements by dragging and dropping table or column nodes to the editor

You can drag and drop tables or columns displayed on the Database node in the Navigator view to the editor. Doing this will prompt Martini to generate a SQL statement based on the selected statement type and provided tables or columns. By default, an AND statement will be generated. If you want to generate use OR instead, hold while dragging and dropping.

Transactions

Martini includes services that can be used to wrap SQL services in JDBC transactions like regular Java JDBC code. These services exist in the io.toro.martini.SqlMethods class from the core package. Example services, meanwhile, exist in the included examples package, in the databaseMigration code directory. 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 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

SQL service, sample JDBC transaction

SQL service, sample JDBC transaction

XA transactions

If configured correctly, Martini will also handle XA transactions.

Exporting a schema table to a data model

You can create data models from existing schema tables through the SQL service editor. In order to do that, follow the steps below:

Exporting schema table to data model

Exporting schema table to data model

  1. Open a SQL service of your choice.
  2. Under the Database tree, expand the Schemas node.
  3. Expand a schema of your choice.
  4. Expand Tables. This will show you a list of schema tables.
  5. Right click the schema table of your choice and select Export to Gloop Model.
  6. In the dialog that appears, specify the location and name of your model. The Location and Name fields are pre-populated by default, and set to the code directory and the name of the schema table respectively.
  7. Click Finish.