title | url | weight | description |
---|---|---|---|
Use the External Database Connector |
/howto/integration/use-the-external-database-connector |
21 |
Overview of the External Database Connector in Studio Pro |
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 (Beta support 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
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.
-
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.
{{< figure src="/attachments/howto/integration/use-the-external-database-connector/1.png" class="no-border" >}}
-
Click “Test Connection” to validate the connection to the external database.
{{< figure src="/attachments/howto/integration/use-the-external-database-connector/2.png" class="no-border" >}}
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.
When the connection is successful and saved, you can search the Browse database tab for Tables, Views, Procedures, and Functions.
{{< figure src="/attachments/howto/integration/use-the-external-database-connector/3.png" class="no-border" >}}
-
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}
{{< figure src="/attachments/howto/integration/use-the-external-database-connector/4.png" class="no-border" >}}
-
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.
{{< figure src="/attachments/howto/integration/use-the-external-database-connector/5.png" class="no-border" >}}
-
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.
{{< figure src="/attachments/howto/integration/use-the-external-database-connector/6.png" class="no-border" >}}
{{% alert color="info" %}} For all DML Queries, changes made to database in the Mendix Design phase are automatically rolled back. {{% /alert %}}
{{% alert color="info" %}} Calling stored procedures with no parameters is supported in Studio Pro 10.9 and above. {{% /alert %}}
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.
{{% alert color="info" %}}DML commands within a stored procedure are rolled back if they are not committed by a stored procedure, but DDL commands are not.{{% /alert %}}
-
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.
{{< figure src="/attachments/howto/integration/use-the-external-database-connector/7.png" class="no-border" >}}
You are now ready to use data from an external database in your Mendix App.
{{% alert color="warning" %}} Make sure to use secure measures, as this action can allow for SQL injection into your app. Do not use user-supplied or environment-supplied variables in your SQL statement; if possible, they should be static. {{% /alert %}}