SQL component

The SQL component is able to connect to a remote database using a JDBC connection. When connected, it can execute SQL and SQL-like queries on the database (see remarks for more information). As a result you get an XML file in the body with the results of the query you executed on the database.

Basic Configuration

The SQL component has the following basic configuration options:

Property	Description
Database Type	The type of database you want to connect to.
Username	Username of the user that has access to the database and the specified table.
Password	Password of the user.
Database	The database where you want to run your queries on.
Host	URL or IP of the machine where the database is running on.
Port	The port where the database is exposed on
Query	Query you want to run on your target database.

Advanced Configuration

The SQL component has the following advanced configuration options:

Property	Description
Use SSL?	Enable when the host uses SSL connection.
TLS version	TLS version (all lower protocols, starting from TLS v1.0 are enabled). It's available when Use SSL? is enabled and Database Type is MySQL.
Escape characters	Enable when it's necessary to escape characters on the Query.

Remarks

• Different database types can have different SQL languages, for example Microsoft SQL Server uses T-SQL which is a bit different from normal SQL. Therefor your queries should use the language for the specified database type.
• This component connects to the target database using JDBC. This means that the target database should support this type of connection.
• You can use ${header.<headername>} and ${headers.<headername>} variables in the query (see fourth example below).
• You can use ${body} or ${bodyAs(String)} variable in the query (see fifth example below).
• Possible errors that could occur will be written down in the output XML (see seventh example below).

Examples

1 - Executing a SELECT query on the database.

Query:

SELECT * FROM products;

Result:

<?xml version="1.0" encoding="UTF-8"?>
<ResultSet>
   <ResultSize>2</ResultSize>
   <Results>
      <Result>
         <name>Product 1</name>
         <description>Description 1</description>
         <price>19.99</price>
         <ID>1</ID>
      </Result>
      <Result>
         <name>Product 2</name>
         <description>Description 2</description>
         <price>29.99</price>
         <ID>2</ID>
      </Result>
   </Results>
</ResultSet>

2 - Executing an INSERT query on the database;

Query:

INSERT INTO products (name, description, price) values('Product 3', 'Description 3', 39.99)

Result:

<?xml version="1.0" encoding="UTF-8"?>
<ResultSet>
   <HasErrors>false</HasErrors>
   <Results>
      <Result>
         <generatedKey>3</generatedKey>
      </Result>
   </Results>
   <ResultSize>1</ResultSize>
</ResultSet>

3 - Call a StoreProcedure on the database;

Query:

CALL getProducts;

Result:

<?xml version="1.0" encoding="UTF-8"?>
<ResultSet>
   <ResultSize>2</ResultSize>
   <Results>
      <Result>
         <id>1</id>
         <name>Product 1</name>
         <description>Description 1</description>
         <price>10.5</price>
      </Result>
      <Result>
         <id>2</id>
         <description>Description 2</description>
         <price>22.75</price>
      </Result>
   </Results>
</ResultSet>

4 - Using header variables in the query

Query:

INSERT INTO products (name, description, price) values('${header.name}', 'Description 4', 49.99)

Result:

<?xml version="1.0" encoding="UTF-8"?>
<ResultSet>
   <HasErrors>false</HasErrors>
   <Results>
      <Result>
         <generatedKey>4</generatedKey>
      </Result>
   </Results>
   <ResultSize>1</ResultSize>
</ResultSet>

5 - Using body variable in the query

Query:

INSERT INTO products (name, description, price) values('Product5', '${body}', 59.99)

Result:

<?xml version="1.0" encoding="UTF-8"?>
<ResultSet>
   <HasErrors>false</HasErrors>
   <Results>
      <Result>
         <generatedKey>4</generatedKey>
      </Result>
   </Results>
   <ResultSize>1</ResultSize>
</ResultSet>

6 - Enable/Disable Escape Characters option

Query:

INSERT INTO products (name, description, price) values('Product6', '{"body":"abc \n def"}', 69.99)

Query with Disabled Escape Characters will be transform into:

INSERT INTO products (name, description, price) values('Product6', '{"body":"abc  def"}', 69.99)

Query with Enabled Escape Characters will be transform into:

INSERT INTO products (name, description, price) values('Product6', '{\"body\":\"abc \n def\"}', 69.99)

7 - Syntax error in query

Query:

INSERT INTO products (name description, price) values('Product 7', 'Description 7', 79.99)

Result:

<?xml version="1.0" encoding="UTF-8"?>
<ResultSet>
   <HasErrors>true</HasErrors>
   <ErrorMessage>Incorrect syntax near 'description'.</ErrorMessage>
</ResultSet>