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 jim.weeda
      For about 12 years, we've had a FileMaker database import external data from a .mdb  (Access) database that has data that is downloaded from some equipment.  The process has survived several computer upgrades (Windows XP and Windows 7) and FileMaker version upgrades (created in 7 and currently running in 16).  It broke when we moved into the Windows 10 environment.  The script is set up to retrieve the data from the .mdb file and it says it can't access the file.  So when I dive into the script and try to specify the ODBC connection it now asks for a username and password.  We've never had a user name or password tied to either the .mdb file, the ODBC connection, or the FileMaker file.  But no matter what we try, we can't get past this step.  Is this a bug?  What am I missing here?
       
      Thanks in advance.
    • By sal88
      Hi all
      Apologies in advance if this is a vague question, or if it's been extensively documented, but I could do with some basic advice on moving filemaker tables to SQL. In terms of database development, I have only really known FileMaker.
      We have a 60 table, 25 user FileMaker database which takes care of most if not all of the company's operations. 
      There is now talk of allowing other platforms (such as PowerApps/PowerBI) access to the data. One such reason is other people are interested in making apps/applets that serve specific functions. This would be simple if the data were held on a SQL database.
      So if all the FileMaker tables were to be moved to SQL, what sort of a task is that in terms of keeping the existing FileMaker gui functioning?
      Or. is there a simpler way to grant other platforms access to filemaker tables?
      And of course, what are the downsides?
      Thanks
    • 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?
×
×
  • Create New...

Important Information

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