Jump to content
Sign in to follow this  
sal88

Execute SQL - Insert and Select

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

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

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

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 TimP
      I have been attempting to connect to a SQL database on my network using Monkeybread Software and each time that I attempt to do it I have encountered the same error, which I have attached here. I have set up an ODBC connection as well as made sure that I had the names correct, however I have gotten the same error message each time. Thank you for your help.

    • By Will_Logic
      Hi, I have been using Base Elements command BE_FileMakerSQL in FileMaker script to SQL select data, and return with '<c>' as field(column) delimiter, and '<r>' as the row delimiter. The below line worked before, I dumped a whole FM database with these delimiters showing in text files. But somehow, now if I run below line, it seems to have changed to ?only allow? 1 character for the delimiter, which of course doesn't make sense anyway with varied characters in the data:
      BE_FileMakerSQL ( $sql_select;  "<c>" ; "<r>" ; $fm_file ) The string this returns now looks like for example: 352265<gwgwh<474848<<wegwgw<65755<gwgw ....
      i.e. it has only taken the first character of the delimiter, I changed delimiter string to test with eg "A≠" instead of "<c>", same problem, it then only inserts "A" as delimiter. Just wonder if anyone can guess what might have happened here?
      Thanks kindly any suggestions!
    • By Will_Logic
      Hi, I am using the Base Elements plugin Execute SQL command to select SQL on a table in another FileMaker file. For a file with many fields and quite a lot data in some fields, this times as about 1 minute just for SQL SELECT command for 1,000 rows. I haven't used MBS (monkeybread software) plugin yet, but I wonder if anyone knows if it is significantly faster?
      I.e. BE_FileMakerSQL vs. MBS(FM.SQL.Execute..) - is one significantly faster?
      Here's the code to select 1,000 rows for reference, $timer_seconds comes as about 60 seconds here. If I export same table internally in FileMaker using the menu export to Excel, it takes 29 minutes for all 41,000 rows, so timing seems similar for the Base Elements SQL. (NB there is some flag going on here in the FileMaker database so internal export gets 41,000 rows but script SQL SELECT COUNT(*) gets 200,000 rows for same table, but I don't think that is relevant to my question).
       
      Set Variable [$time1; Value: Get(CurrentTimeUTCMilliseconds)] Set Variable [$part_ca; Value: BE_FileMakerSQL ( "SELECT * FROM Contact_ACTIVITY OFFSET 100000 ROWS FETCH FIRST 1000 ROWS ONLY" ; "&≠≠&" ; "EOR≠≠EOR" ; "DataContacts11" )] Set Variable [$timer_seconds; Value: (Get(CurrentTimeUTCMilliseconds) - $time1) / 1000] MBS command I think would be something like:
      MBS( "FM.SQL.Execute"; "DataContacts11"; "SELECT * FROM Contact_ACTIVITY OFFSET 100000 ROWS FETCH FIRST 1000 ROWS ONLY" ; ...delimiters here I'm assuming.. ) We only have base FileMaker here on machine I am using, which can run the Base Elements plugin, but to run MBS plugin, needs FileMaker Pro Advanced to install it I think, am getting several databases in roughly same FM format where script workspace disabled, hence need to use SQL select run from script in my own external FileMaker file. Might consider getting FMP Pro Advanced if MBS is faster.
      Tx any suggestions!
    • By Peter Barfield
      Well, I'm new to SQL and have used it in it's basic select  mode. 
      Now I have been asked too create a dashboard using data from different tables to display. Which on the most part is OK and working great.
      I have had a request for a top customer list for the month and previous month (say best 5 ranked from 1-5.) and not together so 2 seperate views
      I am able to pull customers and SUM totals (seperately)for the month using SELECT DISTINCT I am able to get a list of the customers for the month. How would you then get a total or SUM of the invoice amounts for the given month with the customer name and their total ? also is there a similar function to FRACTION in filemaker?
      Listed is my SQL Statement that just returns a ?
      Which I understand to mean a syntax error however, being a SQL "Virgin" any halp would be appreciated. I know I am able to do this natively through filemaker however, as I said this is for a dashboard that uses data from a number of tables.
      Here is my calc that is obviously wrong.
       
      Currently working with Filemaker 15 Advanced.
       
      Let ( [ 
      ReturnSub = "\n" ; 
      SQLResult = ExecuteSQL ( 
      "SELECT DISTINCT (a.\"Customer Name\"), SUM(a.\"InvoiceTotal\")
      FROM \"Invoices \" a
      WHERE a.\"Invoice Sub Total\" > ? AND a.\"InvoiceMonth\" = ? AND a.\"InvoiceYear\" = ? AND a.\"Job Status\" <> ?" 
      GROUP ON a.\"Customer Name\" ASC; 
      "    " ; "|*|" ; 

       "0" ; Month(Get(CurrentDate))-1 ; Year(Get(CurrentDate)); "Giveaway"

       ) ] ; 
      Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  )

      )
    • 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.
  • Who Viewed the Topic

    10 members have viewed this topic:
    The Missing Man  kurucay2000  b.hunt  bcooney  comment  bruceR  Randy Bricker  Greg58  LAIRY  Ocean West 
×
×
  • Create New...

Important Information

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