Use the External Database Connector

Introduction

Use the External Database Connector to connect, retrieve, and insert data into your Mendix app.

The External Database Connector supports connections to the following databases:

• MSSQL
• MySQL
• PostgreSQL
• Oracle
• Snowflake (GA support from Studio Pro 10.12 – Beta versions are available from Studio Pro 10.10)

This document teaches you how to do the following:

• Connect your Mendix App to an external database
• Create and validate SQL queries
• Use created queries in the Query External Database activity

For information on how to configure the connector, see External Database Connector. For information on the database wizard and how to connect using the External Database Connection document, see External Database Connection.

Prerequisites

Download the External Database Connector into your app. Make sure you have the following details for your external connection:

• Username
• Password
• Host
• Port
• Database name

If additional connection properties are required to connect, you can alternatively use JDBC Connection String.

If Using the Flag: --enable-live-preview (available for Studio Pro 10.18)

• Download the External Database Connector.
• If certificate-based authentication is required for PostgreSQL connections, ensure that all necessary certificates are added before running the app.
• To test the connection and execute queries during design time, run your app locally.

Connect to the External Database

Establish Connection Between the External Database and Mendix App

1. Right-click the module you want to add the external database document to and click Add other > External database connection.

2. Select the database you want to connect to and add the connection details in the Database Connection wizard.

   

3. Click “Test Connection” to validate the connection to the external database.

Click Save to save the connection details, which are stored in 3 constants:

• \<Document Name\>_DBSource
• \<Document Name\>_DBUsername
• \<Document Name\>_DBPassword

For example: *Database*_DBsource.

Explore Schemas of a Connected Database

When the connection is successful and saved, you can search the Browse database tab for Tables, Views, Procedures, and Functions.

Create and Validate SQL Queries with Parameters

1. Create a new query by entering an SQL query in the SQL query field.

2. Open the Parameters tab and click Add Parameter if you want to use constants or variables in your query during runtime. Use curly braces to include a parameter in the query.

3. Assign a Test Value to each parameter.

4. Click Run Query to validate the query and view the response.

For example, the query below retrieves a list of RequestedProductRequirement where the ProductLine is Planes.

SQL Query: Select requestedProductRequirement from productlines where productLine = {productLine}

Typecast Parameter Data Type

You can typecast String into UUID, as shown below:

Save Query

Save Query to Retrieve Data

1. Click Use Response to view the response data and mapping.

2. In the Response Structure tab, you can choose New Entity or Reuse Entity.

   a. If New Entity is selected, you can view the entity in the Response structure tab. Click Save Query & Create Entity to save the query and the newly created entity in the domain model.

   

   b. If Reuse Entity is selected, all entities mapped to other queries of same document are listed in the drop-down list. Select the entity you want to reuse and click Save Query.

   

Save DML Query

1. For DML queries, Number of affected rows will be displayed as a response.

   For example, INSERT INTO classicmodels.productlines(productLine, requestedProductRequirement)VALUES({productLine}, {requestedProductRequirement})

2. Click Save Query.

   

Update Existing Query

You can use the existing entity when updating a existing query.

For example, you can modify the query below to retrieve a list of productLine, textDescription, and htmlDescription columns from productLines where the productLine is Planes.

SQL Query: Existing Query: Select requestedProductRequirement from productlines where productLine = {productLine} Modified Query: Select productLine, textDescription, htmlDescription from productlines where productLine = {productLine}

Do the following:

1. Rerun the query to see the Response data.

2. Click Use Response to see the entity preview.

3. Use the existing entity or create a new entity.

   a. If New Entity is selected, you can view the entity in the Response structure tab. Click Save Query & Create Entity to save the query and the newly created entity in the domain model.

   b. If Update Entity is selected, you can see changes that will be made to the existing entity. Click Update Entity to save the query and the changes made to the entity in the domain model.

Call Stored Procedure

To call a stored procedure, do the following:

1. Select the Stored procedure checkbox.

2. Enter the query to call a stored procedure. Add a schema name with the stored procedure name. For example, use the following syntax, where latest_schema is the schema name, and InsertDataIntoTable1 is the stored procedure: Call latest_schema.InsertDataIntoTable1({1},{2}).

   

3. Create IN, OUT, and INOUT parameters for all parameters present in the stored procedure. Make sure the Name in DB is the same as the name of parameter in the stored procedure.

   

4. Click Run Query. This returns an entity with the number of affected rows and all INOUT and OUT parameters. If the stored procedure returns a Result set, an associated entity is created.

   

5. Click Use Response > Save Query & Create Entity to save the query and the newly-created entities in the domain model.

   

For Postgres, Mendix supports the following parameters:

• Decimal/numeric
• Real
• Double Precision
• Big Serial
• Small Serial
• Serial
• Bpchar
• Char
• Varchar
• Text
• Integer
• Smallint
• Bigint
• Timestamp with timezone
• Date only
• Time without timezone
• Time with timezone

For MSSQL, for INOUT and OUT parameters of type Decimal, test values (in design time) are rounded off.

Use the Query External Database Activity

1. Add the Query external database activity into your microflow.

2. Double-click on the activity and select the new external database document.

3. In the Query field, select the required query from the drop-down.

4. Assign values to the parameters using the Expression editor.

5. Output details for the selected query auto-populate.

   

You are now ready to use data from an external database in your Mendix App.