Execute SQL Insert Into

[PELE]

I have a table enlist_load_dtl, I want to use the Execute SQL script step to push data into a MySQL database through ESS.
 using values from a global variable. Can anyone help me, if my query is correct, if not appreciate the help .

INSERT INTO enlist_load_dtl   (studid, schedcrsid, coursecode, fromtime, totime, days, room)
   values ($$StudID, $$schedcrsid, $$Coursecode, $$Fromtime, $$Totime, $$Day, $$Room);

Thanks in advance,

Pele

 

[Wim Decorte]

You're mixing up a few things here:

- if you are using ESS you don't need the 'execute sql' script step, you can treat the MySQL table like a native FM table and use regular script steps to create and update records

- the 'execute sql' script step can work with any ODBC data sources whereas ESS works with only a few.

As to the syntax: you'll need to make that a calculation where you work your variables in.  Something like:

"INSERT INTO enlist_load_dtl   (studid, schedcrsid, coursecode, fromtime, totime, days, room)
   values (" & $$StudID & "," & $$schedcrsid"," & $$Coursecode"," & $$Fromtime"," & $$Totime"," & $$Day"," &  $$Room");"

In addition to that you'll have to add the proper quoting for text fields on the MySQL side and make sure that the date and time variables are in the right SQL format.

[beverly]

the query is a text result and as Wim says will need to calculate out correctly for the SQL db (and the driver) to understand it! 

 

 

[PELE]

Greetings from the Philippines

Thank you very much for the insight. Now, I understand how it works.
Based on the above example, pushing one active record to the obdc source.
In the case of a found set, using the same  INSERT INTO query how do I push multiple records into the obdc source?
 

Best regards,

Pele

 

[webko]

One at a time - use a looping script.

[Wim Decorte]

Or collect all the data up front and do one massive INSERT query.

As @webko mentions you can loop through your records to collect the data, and there are other ways to collect the data (google HyperList for a good overview of the possible approaches).

[beverly]

yep on webko and wim! as I said, a query is TEXT. however you can calculate (or script gather) the text to make the query work for single or multiple records is ok. I have used both methods (even before there was "ESS")!

beverly

[Todd Dignan]

Hi All,

With an Insert sql statement, I using the OUTPUT Clause.  Example:  

insert into MyTable2(fname)
output inserted.ID into @ID
values ('Jim')

I'm trying to grab the ID as a result.  Will FileMaker return a result with the execute sql script step?

Or is there a plugin like BE or MonkeyBread that will do this?

Thanks for your Help

Edited January 26, 2017 by Todd Dignan