External Database Connector

1 Introduction

Connect to Microsoft SQL, MySQL, PostgreSQL, and Oracle databases with the External Database Connector.

1.1 Typical Use Cases

Use this module if you need to connect to databases and select data to use in your app. This connector allows you to directly test connections and queries during configuration in Studio Pro (design time).

If you need to connect to other database types, check out the Database Connector. Keep in mind that design time support is not available for the older version of the connector.

1.2 Features

This connector supports connections to the following database types:

• Microsoft SQL
• MySQL
• PostgreSQL
• Oracle

If you are looking for another database type, follow the prompt to request support your database when you open the database connection wizard.

This connector supports the following statements:

• SELECT
• INSERT
• UPDATE
• DELETE

1.3 Limitations

• SELECT queries can be saved only if they are successfully executed and a response structure is created
• The connector supports columns with primitive data types only
• If column names contain special characters, use an alias for the column name
• Parameters are only supported for filter values (prepared statements)
• Parameters with empty test values are not supported in the database connection document
• Stored procedures with no parameters are only supported in Studio Pro 10.9 and above.

1.4 Prerequisites

• Studio Pro 10.6 or above
• External database connection details, including the following:
  • Login credentials
  • Database type
  • Hostname, port, and database name; or, instead, the JDBC connection string

2 Installation

Download the External Database Connector and add it to your app.

3 Configuration in Design Time

With this connector, you can test database connections and add queries and parameters during design time before your app is running. This allows you to make sure everything works before deploying your app.

3.1 Getting Started: Connecting to a Database

After installing the connector, get started by doing the following:

1. Right-click the module you would like to add the connection to and click Add other > External database connection. This opens the Database Connection wizard:

   

2. Select the database to which you would like to connect and enter the required information.

3. Click Test Connection to see if the connection works. If you do not see a green Connection Successful text confirmation, try checking your database details again.

4. Click Save to open the external database document for this database.

Now you can start querying the database to select data to use in your app.

3.2 Querying a Database

To query the database, do the following:

1. Enter a query Name so you can access the same query later.

2. Enter your SQL Query to select data from your database for use in your app. For example, the query SELECT * from customers selects all rows in the Customers table:

   

3. Click Run Query to move to the Response data tab and view the queried data.

3.2.1 Adding Parameters

Click Add Parameter to add parameters to your SQL queries to pass dynamic values to the query at runtime.

The example database in Querying a Database is a table of customer details with information such as customer name, address, and phone number. Let’s say you want to specify a specific customer while your app is running. You can add the following parameter:

Then, use the parameter in the query:

select * from customers where contactFirstName like {paramFirstName}

3.3 Using Query Response

After querying the database, you can view the response in the Response screen.

Click Use Response if you want to create an entity from the response.

3.4 Creating an Entity from the Response

In the Response Structure tab, there is a preview of the queried data in an entity. You can adjust the entity name, though one is suggested for you:

Click Save Query & Create Entity to create the entity and add it to your domain model:

3.5 Using the Entity in a Microflow

Use the Query External Database activity to call the database in a microflow. Do the following:

1. Create a new microflow and drag the Query external database activity into it.

   

2. Double-click the activity and in the Database field, click Select to choose the database you want to query.

3. Select the Query you want to include in the activity (that you saved while querying the database).

4. Include any parameters.

5. In the Output field, choose if you want to Use return value.

   

6. Click OK.

7. Configure the end event (such as displaying a list, if you are selecting data to appear in a list).

You can now use the microflow in your app. Below is an example of a configured microflow:

See the Integration Activities entry in the Studio Pro guide for further explanation of the properties in this activity.