Martini Data Integration SQL Service Editor

In Martini, the SQL Service Editor allows you to create and manage SQL services, enabling you to execute SQL statements against databases seamlessly. This guide will walk you through the key features and functionalities of the SQL Service Editor.

Creating a SQL Service

To create a SQL service in Martini, follow these steps:

Launch the SQL Service Wizard: Right-click on the target package's code directory (or any of its subdirectories) where you want to store the SQL service. Then select New > SQL Service.
Provide Details: In the wizard, specify the following details:
- Location: The folder where the SQL service will reside.
- Name: A descriptive name for the SQL service.
- Connection Name: The name of the database connection the service will use to execute queries.
- Query Type: The type of SQL statement the service will perform.
Complete Wizard: Click Finish to create the SQL service.

Editing SQL Services

After creating a SQL service, Martini will open the service in the SQL Service Editor, where you can perform various tasks:

Connection Name Dropdown: Select the database connection Martini will use to execute queries. Choose from the list of registered databases.
Type Dropdown: Specify the type of SQL operation the service will execute. Supported types include:
- Select Single
- Select Multi
- Insert
- Insert Batch
- Update
- Update Batch
- Delete
- Delete Batch
Database Tree: Displays the schemas, functions, and procedures of the target database. Navigate through the database objects conveniently.
Statement Text Area: Edit the SQL statement directly. The statement is based on the selected SQL operation type.
- Format SQL Query: Right-click on the statement text area and select Format, or use the shortcut Ctrl+Shift+F to format the SQL query.

Inputs and Outputs

In Martini's SQL services, inputs and outputs play a crucial role in defining the behavior and functionality of the service. This section outlines how inputs and outputs are managed in SQL services.

Dynamic Inputs and Outputs

Similar to regular services, SQL services dynamically adjust their inputs and outputs based on the SQL statement being executed. As the SQL statement changes, the inputs and outputs of the SQL service are updated accordingly.

Error Handling

If there are issues with the SQL statement or the output model cannot be generated, a warning will be displayed beneath the SQL editor. Hovering over the warning provides additional information about the problem.

Special Input and Output Properties

SQL services also support special input and output properties, which can be set or fetched as needed:

Property	Type	Applicable Operations	Description
`$martiniConnectionPool`	Input	All operations	Specifies the database name.
`$generatedKeys`	Output	Insert	Provides the ID(s) of the record(s) that were inserted.
`$parentJDBCBatchCursor`	Output	All batch operations	Helps execute the parent table's batch before any child batches, avoiding foreign key issues. If left empty, the batch operation will execute without considering parent batches.
`$updateCount`	Output	All single operations	Indicates the total number of records updated or deleted as a result of the operation.
`$batchSize`	Output	All batch operations	Specifies the size the batch will reach before sending the updated data to the database.

Parameterized Queries

Parameterized SQL queries in Martini provide flexibility by allowing you to specify values for SQL commands without hard-coding them directly into the statement. Instead of using placeholders like question marks, Martini utilizes named parameters in the format :<name-of-parameter> within the SQL statement. This approach resembles Java's prepared statements.

Example

Consider the following SQL statement without parameters:

SELECT
    ID, FIRSTNAME, LASTNAME, EMAIL
FROM
    EMPLOYEES
WHERE
    FIRSTNAME = 'JOHN'
AND
    LASTNAME = 'CITIZEN'

To make it parameterized, modify it as follows:

SELECT
    ID, FIRSTNAME, LASTNAME, EMAIL
FROM
    EMPLOYEES
WHERE
    FIRSTNAME = :firstName
AND
    LASTNAME = :lastName

In this modified statement, :firstName and :lastName are named parameters. When executed, Martini will substitute these parameters with the values provided for firstName and lastName properties of the SQL service.

Input/Output View

As you add named parameters to the SQL statement, Martini dynamically updates the Input/Output view to reflect these changes. This view provides visibility into the input properties associated with the SQL service.

SQL Statement Generation

Martini simplifies the process of writing SQL statements by offering a SQL generator feature. You can right-click on tables or columns in the Navigator view and choose the desired SQL statement type from the context menu. Martini generates the SQL statement automatically, incorporating the selected tables or columns.

Drag-and-Drop Functionality

Furthermore, you can use drag-and-drop functionality to generate SQL statements. Simply drag tables or columns from the Database node in the Navigator view to the SQL editor. Martini will generate a SQL statement based on the selected statement type and provided tables or columns. Holding Alt while dragging and dropping allows you to generate statements with OR conditions instead of AND.

Transactions

Martini provides services for managing JDBC transactions, allowing you to wrap SQL services within transactions, similar to traditional Java JDBC code. These services are available in the io.toro.martini.SqlMethods class from the core package. Example services demonstrating transaction management can be found in the examples package, specifically in the databaseMigration code directory.

The transaction workflow in Martini closely resembles that of Java code:

A transaction is initiated.
Data manipulation operations are performed within a try block.
If the operations succeed, a commit is executed.
If an error occurs, a rollback is performed within a catch block.

Here's a sample SQL service illustrating the transaction management workflow:

io.toro.martini.SqlMethods.beginTransaction();

try {
    // Database operations go here

    io.toro.martini.SqlMethods.commitTransaction();
} catch (Exception e) {
    io.toro.martini.SqlMethods.rollbackTransaction();
    // Handle exception
}

Martini also supports XA transactions when configured correctly, enabling distributed transaction management.

Exporting a Schema Table to a Data Model

You can easily create data models from existing schema tables using the SQL service editor in Martini. Follow these steps to export a schema table to a data model:

Open the SQL service editor for a desired SQL service.
Right-click on the schema table you want to export in the Database tree.
Select "Export to data model" from the context menu.

This process generates a data model based on the selected schema table, facilitating seamless integration with Martini's data modeling capabilities.