Use Mendix Variable in SQL Statement to External Database

0
Hi, New to Mendix and cannot find specific examples of what I am trying to do with my external database. I have connected to my database using the documentation here (https://docs.mendix.com/howtogeneral/bestpractices/how-to-execute-an-sql-statement-on-an-external-database) I have successfully created a microflow that updates my database with static data that I have entered into a string variable. (I'm doing a basic Insert statement) How can I pass variables/data from my Mendix object into my SQL statement? It seems that the SQL needs to be formatted as a string, and in doing so I lose the ability to use the stored data from the variable.   Could someone show me a screen shot of a proper microflow to pass mendix data through to a SQL statement? (Or advise if I am looking at this the wrong way completely....)   Thank you,   Jeff
asked
4 answers
1

Here's the flow I would try. Let's say you have an object called "MyVehicle" in your microflow which has attributes such as:

  • Name
  • Color
  • Type

 

Then, your flow should be:

  • Add a Create variable activity
  • Name the variable updateSQL
  • Set up your value like this:
'INSERT INTO tbl_Vehicles
VName, VColor, VType
VALUES
( "' + $MyVehicle/Name + '"
, "' + $MyVehicle/Color + '" 
, "' + $MyBehicle/Type + '"
)'
  • Use this variable ($updateSQL) in your execute statement
  • If any of your data types are not strings, you may need to use the toString() or formatDateTime() functions around the variable to get it into the right format

 

I haven't actually tested this query, so it may have some syntax issues.

Note this design is potentially vulnerable to SQL injection, so you should be validating your inputs before executing this query. Hopefully in the near future, we will be able to use parameterized queries with the database connector.

answered
1

Hi Eric,

 

Mendix support confirmed that using a webservice is the best method, but there are plenty of production environments using direct SQL (typically with local deployments). Security issues should still be acknowledged and accounted for.

I've used your initial response and worked on the syntax. I would like to post some examples here for the forum.

 

Syntax Examples

To pass a Mendix Variable to SQL using the Execute Statement string:

' ' ' +$MendixVariable+ ' ' '

(3 tick marks, + sign, $Variable, + sign, 3 tick marks)

 

To use a SQL Variable (on remote database) using the Execute Statement string:

@SQLVariable

(no tick marks)

 

Here are some screen shots of confirmed working strings:

 

 

 

___

answered
0

Hi Eric,

Thanks for your reply. From your answer, it seems like this method may not be the best way to achieve my goal.  How is it recommended to update an external database with values that are set within the Mendix application? It seems to me that this would be a common task.

Thank You,

 

Jeff

answered
0

Guys I found a way..  its so simple… set sql query error handling to “continue” thats it !  ;) it will allow you to insert/update and continue what you want to do.

 

answered