Execute SQL - Insert and Select

[sal88]

Hi all

I'm trying to export my filemaker records to my external sql database via the execute sql script step but am getting a number of errors.

I can see my external ODBC source and can generally write to it (I can go to the respective layout and click 'new record'). My query basically as follows:

"INSERT INTO dbo.Log (
dbo.Log.log_id,
dbo.Log.log_case_idf,
dbo.Log.Labour_TOTALS_Labour_Cost,
dbo.Log.Labour_TOTALS_Item_Sale)

SELECT 
Log_Log.log_id,
Log_Log.log_case_idf,
Log_Log.Labour_TOTALS_Labour_Cost,
Log_Log.Labour_TOTALS_Item_Sale,
FROM Log
WHERE
Log_Log.Log_Type = '2'"

My first question is: is it even possible to export to an odbc source with a 'insert INTO SELECT' query?

Many thanks

Edited November 6, 2017 by sal88

[sal88]

I'm still stuck on this - I can't work out if ESS is even meant for my purposes. Any help whatsoever would be much appreciated.

Many thanks

[bcooney]

I do believe that you can do this, but it's not something with which I have a ton of experience. Does sql "like" the string, "duo.log.log_id"? seems like the extra dot could be an issue.

Have you thought of setting up ESS?

[sal88]

Hi bcooney

I don't suppose you could clarify what the difference is between ESS and what I am doing here?

This looks promising in terms of doing INSERT from SELECT: https://www.soliantconsulting.com/blog/executesql-filemaker

However I have a few hurdles before I get to that point. Here is what I have done so far:

• I have my ODBC source set on the server which hosts our filemaker solution.
• On my local machine I have to locally add the ODBC source in order to use the "Execute SQL" script step. It does not see the ODBC source that I have set up on the server. I also cannot add the local ODBC source to 'external data sources'.
• Now I can create a new record in the external SQL source using "Execute SQL" (and the locally added ODBC source) however it takes a very long time for the new record to appear. I am viewing the record from a layout that is based on the server's ODBC connection (not my local ODBC connection). The new record definitely gets created however as if I try to perform the same operation with the same ID it complains that the ID is already in use. Also if I connect to the ODBC source from MS Access then I see the record straight away.
• The idea is that a script will run on the server every hour that will delete the records in the external SQL table and then populate it with records from the FileMaker database. Will this be possible given that I can only select my locally added ODBC source in the "Execute SQL" script step.

If someone could point me in the right direction that would be great!

Many thanks

[bcooney]

Sal,

Where are you with this? We never did clarify what ODBC source you're using. FM only supports a few. http://www.filemaker.com/support/technologies/odbc.html

https://support.filemaker.com/s/answerview?language=en_US&anum=6420

Found a great article by Fabrice Nordmann that brings it all together. Since FM13 you can use PSOS to avoid local driver installation and use Execute SQL script step to INSERT.

https://www.1-more-thing.com/odbc-import-technique/

[sal88]

Thanks I'll take a look! it was on the back burner, but now it looks like we may be embarking on the journey again. Needs a bit of a rethink as to what they're actually after. I'll let you know.

Cheers!