Jump to content
Sign in to follow this  
sal88

Execute SQL - Insert and Select

Recommended Posts

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 by sal88

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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/

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By schotja
      Looking for someone to help setup a simple connection to a sql database on cpanel for our Ranch to connect to cattle listings on our website..  I'll be using fmp12 server or content for now just establishing a connection via fmp12 client on windows 7 pro machines.
      Specifically I am having trouble establishing a DSN connection from any of my machines and have opened ports / given access in cpanel to my static IP.
      I have also updated MS ODBC drivers on one of the machines to see if that made a difference.  It may be something quick for an expert with experience or it might end up being a gremlin.
      I have set up some test databases for troubleshooting purposes and willing to do alot of the legwork if i'm able but also willing to pay someone for their expertise as well.
      thx.
    • By Scott Pon
      Using MS Access 2010. Filemaker 13 Pro, Filemaker 13 Server.
      I have a MS Access database that that I programmed to do a lot of calculations and creates data. Now I want that data saved in a Filemaker database.  I was able to create ODBC connection to the Filemaker database.  I am able to view the Filemaker tables/data in MS Access.  
      At 8AM daily I want the Access database to do the calculations, and then insert the data to Filemaker . However every time I try this, the Filemaker Login Screen comes up.  And I would have to manually enter the username and password.
      Is there any way to bypass the log on screen?
      Thanks in advance.
    • By trevix
      On windows 7 (VMware on OSX), using FMP17 and Microsoft Access 2016
      Trying to do a ODBC import and selecting the datasource, I get asked for Username and password, even if the Access DB is not password protected.
      The data sources are correct (tested from Livecode) and should be working without user name and password.
      On the properties of the ODBC pane setup the path seems correct (not pointing to 32): %windir%\SysWOW64\odbcad32.exe
      What am I missing?
    • By jchallenw
      Does anyone have experience and/or success using a 4D database as an external datasource through ODBC. I can add the system DSN and add it to FMP. Once I attempt to view the tables in the 4D database, I receive an error that the "ODBC connection is not compatible". Does anyone have a work around or suggestions? Thanks!
    • By mike13
      I am in the process of connecting an existing FileMaker database to a SQL server that is behind a web interface.  I wanted to know if there is a way to send a command from the web interface to kick off a sync.
      For example, a user opens a record on the web, makes some changes, hits a save button.  
       How do I get that "Save" to trigger a sync?
       
      And can I get it to sync that single record or do I need it to sync all records that have changed?
      Thanks in advance,
      Mike
       
×
×
  • Create New...

Important Information

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