Use the External Database Connector
1 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 how-to 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
2 Prerequisites
Download the External Database Connector into your app. Make sure you have the following details for your external connection:
- Username and Password for signing into the external database
- Connection details: Host, Port, Database name
If additional connection properties are required to connect, you can alternatively use JDBC Connection String.
3 Connect to the External Database
3.1 Establish Connection Between the External Database and Mendix App
-
Right-click the module you want to add the external database document to and click Add other > External database connection.
-
Select the database you want to connect to and add the connection details the Database Connection wizard.
-
Click “Test Connection” to validate the connection to the external database.
![](/attachments/howto/integration/use-the-external-database-connector/2.png)
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.
3.2 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.
![](/attachments/howto/integration/use-the-external-database-connector/3.png)
3.3 Create and Validate SQL Queries with Parameters
-
Create a new query by entering an SQL query in the SQL query field.
-
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.
-
Assign a Test Value to each parameter.
-
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}
![](/attachments/howto/integration/use-the-external-database-connector/4.png)
4 Save Query
4.1 Save Query to Retrieve Data
-
Click Use Response to view the response data and mapping.
-
In the Response Structure tab, you can view the entity.
-
Click Save Query & Create Entity to save the query and the newly created entity in the domain model.
![](/attachments/howto/integration/use-the-external-database-connector/5.png)
4.2 Save DML Query
-
For DML queries, Number of affected rows will be displayed as a response.
For example,
INSERT INTO classicmodels.productlines(productLine, requestedProductRequirement)VALUES({productLine}, {requestedProductRequirement})
-
Click Save Query.
![](/attachments/howto/integration/use-the-external-database-connector/6.png)
5 Call Stored Procedure
To call a stored procedure, do the following:
-
Enter the following syntax:
Call latest_schema.InsertDataIntoTable1()
-
If the stored procedure returns a result set, click Use Response > Save Query & Create Entity to save the query and the newly-created entity in the domain model.
-
If the stored procedure contains DML Queries, Number of affected rows will be displayed as a response. Click Save Query to save the query.
DML commands within a stored procedure are rolled back if they are not committed by a stored procedure, but DDL commands are not.
6 Use the Query External Database Activity
-
Add the Query external database activity into your microflow.
-
Double-click on the activity and select the new external database document.
-
In the Query field, select the required query from the drop-down.
-
Assign values to the parameters using the Expression editor.
-
Output details for the selected query auto-populate.
![](/attachments/howto/integration/use-the-external-database-connector/7.png)
You are now ready to use data from an external database in your Mendix App.