Creating a SQL service
To create a SQL service, follow the steps below:
- Launch the SQL service wizard by right-clicking on the target package's
codedirectory (or any of the code directories underneath it) where you'd like to store the SQL service, and then select New > SQL Service.
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
Launch the wizard using keyboard shortcuts only
In Martini, you can open the SQL service wizard by pressing (or in Martini Online) and
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.
Here's a breakdown of its primary components:
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
$martiniConnectionPoolinput property to the name of another database.
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.
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.
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 .
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:
||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 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 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.
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
instead, hold while dragging and dropping.
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
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 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
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:
- Open a SQL service of your choice.
- Under the Database tree, expand the Schemas node.
- Expand a schema of your choice.
- Expand Tables. This will show you a list of schema tables.
- Right click the schema table of your choice and select Export to Gloop Model.
- 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
codedirectory and the name of the schema table respectively.
- Click Finish.