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.

Configuration

The SQL component has the following 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.

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 third example below).
• Possible errors that could occur will be written down in the output XML (see fourth example below).
• We only support the following SQL operations:
  • SELECT
  • DELETE
  • UPDATE
  • INSERT
  • BEGIN

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 - 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>

4 - Syntax error in query

Query:

INSERT INTO products (name description, price) values('Product 5', 'Description 5', 59.99)

Result:

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