Skip to content

Martini SQL Functions

The SQL class in Martini offers a suite of functions for SQL-related operations, crucial for database interactions in application development. This documentation provides insights into utilizing these functions for database connections, query executions, and transaction management.

Key Features of the SQL Class

  • Database Connectivity: Facilitates connections to databases through defined connection names in your Martini instance.
  • Query Execution: Enables the execution of SQL queries, including SELECT, INSERT, UPDATE, and DELETE statements.
  • Transaction Management: Provides capabilities to start, commit, and roll back database transactions, ensuring data integrity.

Implementing SQL Functions in Martini

Establishing a Database Connection

To interact with a database, establish a connection using the SQL class. Specify the connection name, which should correspond to a defined connection in Martini.

Executing SQL Queries

  1. Writing Queries: Construct your SQL query according to your data retrieval or manipulation needs.
  2. Executing Queries: Use the SQL class to execute the query. For instance, a SELECT query to retrieve data from a specified table.
  3. Processing Results: Handle the results from the query execution as needed, such as iterating through a result set or updating records.

Example in Groovy

1
2
3
4
5
6
7
// Example code to execute a SELECT statement
"connectionName".sql().query('SELECT * from TABLE_NAME') {
    ResultSet rs -> while (rs.next()) {
        // Process each row in the result set
        println rs.getString('COLUMN_NAME')
    }
}

Managing Transactions

For applications requiring transactional integrity, use the SQL class to manage database transactions. This includes:

  • Starting a Transaction: Initiates a new transaction.
  • Committing a Transaction: Saves the changes made during the current transaction.
  • Rolling Back a Transaction: Undoes changes made in the current transaction in case of errors or other conditions.

Utilizing SQL Functions in Services

  1. Accessing Functions: SQL functions are accessible through the Martini interface. Navigate to the 'Functions' node and locate the SQL folder.
  2. Incorporating Functions: Integrate SQL functions into your service either by direct coding or using the Martini interface for drag-and-drop functionality.
  3. Input Specification: Define the necessary inputs for the chosen SQL function, including database connection details and SQL queries.
  4. Output Configuration: Set up your service's output to align with the results of the SQL operation. Adjust properties like 'allow extra' based on the requirements of your data model.

Generic Use Case: Retrieving Data

  • Objective: Fetch data from a specific table in the database.
  • Implementation:
    • Establish a connection to the database.
    • Use an SQL SELECT query to retrieve data from the desired table.
    • Process the retrieved data as per the application's requirements.