Use DatabaseReplication with MySQL database

5
I needed to connect to a MySQL database. I could find similar questions on the forum, but not with a solution. It took me a little to get it to work so I share it here. Officially, the DatabaseReplication module does not support MySQL, but it is possible. Even in a sandbox, so I hope that it will work in the cloud too. Install and configure MxModelReflection and DatabaseReplication modules first. You need the JDBC driver. It is included in the MySQL installation: c:\Program Files (x86)\MySQL\Connector.J 5.1\mysql-connector-java-5.1.33-bin.jar Of course, version numbers may differ from this example. Copy the JAR to the userlib folder of your Mendix project. To configure the database: Database type: custom user ID / password as configured in the database Connection string: jdbc:mysql://<server>:<port>/<schema> Driver class: com.mysql.jdbc.Driver Escape characters: back tick ` You can check the escape options that are necessary for proper functioning. I left Escape the schema and table name separately unchecked. Save Sync the database Now you can create mappings as described in the documentation. When importing a foreign key relation as a reference between Mendix objects results in SQL errors from MySQL: Just create the relation after importing the data. In my test I have a Delivery entity and a DeliveryItem entity. In MySQL, the DeliveryItem table has a DeliveryId attribute for the foreign key relation. I added that attribute to the Mendix object as well so I can create the reference using a microflow that retrieves all DeliveryItem objects and links them to their parent Delivery object. I also tested this in the sandbox. I used http://www.db4free.net/ they allow anyone to sign up and get a MySQL database on their site for testing purposes. Edit: I changed the configuration steps to reflect the answer/comments below. The reference works now too. I left the paragraph about the reference in, in case it does not work in some situations, where the microflow can be used to create the references after the import.
asked
2 answers
3

Marcel, Thank you for sharing. That is indeed the best way to do it. In addition to that you might want to include escape characters in the configuration. Otherwise if you would have an 'Order' table this could give you errors:
Escape character open `
Escape character close `

The reason MySQL, and for example MS Access are not available by default is licensing. Last time I checked it wasn't allowed to redistribute the library.
The library is free to use and download, however at that time, it wasn't allowed to include the libraries in any form of re-distribution. In other words we are not allowed to include it in the appstore .mpk files.


We have use MySQL and MS Access integration for several customers, this is possible using the custom settings as specified in your question.
The implementation has been tested, and if you have any ideas on improving support for these 'custom' types please let us know and we can change that.

answered
0

I connected a postgreSQL database and this works fine (although very slow, 1000 objects per 3 minutes). Also the associations are set and created as set in the mappings. Now I want to connect to a MySQL database and still use the associations. Is there any solution to make the association in the mapping work for a mySQL database?

answered