Database Connect Creating Multiple Inserts

[justinzastrow]

I have a database connect insert statement in my flow and when the file reaches the database connect object, it creates 3 lines in the database. I have the insert statement listed below. Has anyone ran into this issue or see something wrong with the insert statement?



[Database.Boolean:SQL="INSERT dataCollect SET jobNumber = '[Job.NameProper:Before=F]',material = 'SM74 Proof'",Connection="Switch Data"]

[dkelly]

Switch should call the Database element only once for each job. The SQL statement appears to be correct. Can you post a screen shot of your flow?

[justinzastrow]

Here is a link to a screenshot of the flow.





Database Flow



Thanks,

Justin Zastrow

[dkelly]

That's pretty simple. What does the messages say?

[freddyp]

[Database.Boolean:SQL="INSERT dataCollect SET jobNumber = '[Job.NameProper:Before=F]',material = 'SM74 Proof'",Connection="Switch Data"] is the form of a database query as a variable in a condition or a single-line text with variables.



In "Database connect" you write this as:



INSERT dataCollect SET jobNumber = '[Job.NameProper:Before=F]',material = 'SM74 Proof'"



The data source is selected in the property "Select data source" of the "Database connect" element and therefore has no place in the text area where you define the SQL statement.



Note that you cannot have more than 1 SQL statement. What you should do when you have to do some complex SQL stuff, is to create a stored procedure in the database. There you can have a field day in terms of complexity and flexibility and in Switch all you do is call the stored procedure with the required parameters. That keeps it very simple in Switch and any changes in the database and the stored procedure do not affect Switch.



Freddy