Here's the flow I would try. Let's say you have an object called "MyVehicle" in your microflow which has attributes such as:
Then, your flow should be:
'INSERT INTO tbl_Vehicles VName, VColor, VType VALUES ( "' + $MyVehicle/Name + '" , "' + $MyVehicle/Color + '" , "' + $MyBehicle/Type + '" )'
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.
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.
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.
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:
(no tick marks)
Here are some screen shots of confirmed working strings:
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.