Jump to content

Execute SQL Insert Into


This topic is 2633 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

 

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

  • Newbies

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

 

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 2 months later...

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 by Todd Dignan
Link to comment
Share on other sites

This topic is 2633 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.