SQL component

The SQL component allows you to connect to remote databases using a JDBC connection. You can execute SQL and SQL-like queries on the database, which will return an XML file in the body containing the results of your query.

Basic Configuration

The SQL component has the following basic configuration options:

Database Type

Specifies the type of database you want to connect to.

Options

• IBM DB2 (default)
• IBM Informix
• MySQL up to version 5.7
• MySQL from version 8
• SQL Server
• Oracle 12c
• PostgreSQL

note

Different database types may use different SQL languages. Ensure your queries are compatible with the language of the database type you specified.

JDBC connection

The SQL component uses JDBC to connect to the remote database. Ensure your database supports this type of connection.

Username

Defines the username used to log in to the database (or specified table).

Password

Defines the password used to log in to the database.

Database

Defines the name of the database you want to connect to.

Host

Defines the host on which the database is running.

Port

Defines the port the database is running on.

Query

Defines the query you want to run on your database.

query errors

Errors that occur are shown in the output XML body, see 7. Errors in query below.

tip

Refer to the Using SQL section on this page for some examples and tips, including using headers and/or the body in your queries.

Advanced Configuration

The SQL component has the following advanced configuration options:

Use SSL?

Enable this option when the host uses an SSL connection.

SSL Certificates

If the SQL server you are connecting to uses an SSL Certificate, it should work out of the box as long as it is signed by a default trusted certificate authority. If the SQL server uses a self-signed certificate or a certificate not signed by a default trusted certificate authority, it must be imported manually. Please contact your Dovetail provider for support.

Escape characters

Enable this option to escape characters in the Query.

example escaping characters

Refer to 6. Using Escape characters to see how this setting alters the query.

Using SQL

1. Execute a SELECT query

Use the tabs below to see the query and an example response XML.

Query

SELECT * FROM products;

Response XML

<?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. Execute an INSERT query

Use the tabs below to see the query and an example response XML.

Query

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

Response XML

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

Use the tabs below to see the query and an example response XML.

Query

CALL getProducts;

Response XML

<?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. Use headers in a query

Use the tabs below to see the query and an example response XML.

Query

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

Response XML

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

5. Use the body in the query

Use the tabs below to see the query and an example response XML.

Query

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

Response XML

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

6. Using Escape characters

Use the tabs below to see the original query and the resulting (non) escaped characters query.

Original Query

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

Escape characters Disabled

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

Escape characters Enabled

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

7. Errors in query

Use the tabs below to see the query and an example response XML.

Query

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

Response XML

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

Last update on Feb 18, 2025