Mendix has been the pioneer in the world of low-code development throughout the time it has provided great yet simple digital transformation methods. MXTechies is a digital transformation leader in the Mendix Low Code space. Mendix has so many methods by which one can migrate/extend their current database from any legacy system to Mendix and Database Connector is just the best and works like a charm. This article will cover everything you need when you start working with the Database connector for Mendix.
Why Database Connector?
The first reason is that you can simply connect with all the databases that the JDBC supports. Similar to all other Mendix components Database connector works well as a plug-and-play activity no customization or setup is necessary. The big part is writing the SQL statements which is a little different from how we usually do it once getting the hang of it becomes very simple.
1. How to secure your database credentials from attack
When we want to reuse the database configuration in multiple requests it is recommended no go for a configuration page instead of going for constants. Always make sure to store your password in an encrypted format and upon saving move it to another attribute allowing the configuration page to display an empty value whenever opened. The save microflow would look something like this.
2. How to execute and SQL Statement
I. Store your SQL statement in a Variable
II. Add the action activity Execute Query when you want to fetch data from database and Execute Statement for any other operations.
III. Execute Query will expect an object to create a list of the same objects fetched form database.
Note: In the above image the jdbc url has a prefix “jdbc:mysql” which will differ based on the database you want to use. For example “jdbc:postgresql” for postgreSQL.
Example for executing Query in a microflow.
3. How to pass your values to a SQL statement
When you want to pass inputs to an SQL statement Mendix uses single quotes to define a String which is similar to SQL this makes writing an SQL statement tricky. So here’s what we need to keep in mind
I. To pass a String instead of using single quote once we have to use it thrice
For example: '''+$NewEmployee/Firstname+'''
II. To pass a date It has to be parsed to String
Example: '+formatDateTime($NewEmployee/JoiningDate, 'dd-MM-yyyy')+'
Example for a complete statement
4. Fixes and precautions to avoid a few common errors
1. To fix the issue with the column name and Mendix attribute mismatch The best practice is to write a query that returns the column names exact to Mendix attributes Example:
select empCode as EmpId from employee.
And the second way is to rename the attribute in Mendix which for existing attributes when renamed can lead to data loss.
2. Always make sure to check the String length allowed in a table in the legacy system when executing the insert statement.
3. Boolean is often stored as BIT so make sure to set your input as 0 or 1 based on the value in Mendix.
These are all the key steps to keep in mind when you want to work with Database Connector for Mendix. Hope these tips would help you to achieve robust development.
