SQL Data import

[Harry]

Hello Board!

I hope you're all well into the swing of 2015 by now!

 

I have an External SQL Data source. There is an instance in my RG and I can duplicate it into other instances and read/write to it no problems. But, charting summarised calc fields from it doesn't work. If I import all of the records into a native Filemaker table, these summaries (Not Summary fields, but calc fields) do work.

 

So I'm trying to find a way to automate/script this import. I do not want to go File|Export To|....zzzzz.... process every hour of every day. If I can build a script to do it, that'd be great.

 

I can Copy All Records, but I can't paste them. I can set up an Export script step, but it wants me to specify an external data source to export and it's messy. Is there a better way?

 

I will go to the SQL table instance, copy all records, go to my table instance, select all records, delete all current records then import the new updated set.

 

Thanks!

 

Harry

[Wim Decorte]

You can use the Import Records script step and point it to the SQL database, that saves you from having to export from the ESS table first.

 

If the summarized calcs themselves work though on the ESS table, why not just set a table with the summaries instead of grabbing ALL the records to redo the summaries?

[Harry]

Hi WIm,

Thanks for answering. I'm not sure what an ESS table is, so please excuse my ignorance; I may have misunderstood.

 

The summarised calcs only work when exported to native FM tables, not when they are External. I showed that in a quick test I did, using duplicated table/field values from the External SQL.

 

Presuming here that this script should be run server-side? I haven't done any scripting from the server yet.

 

I have access to the SQL tables via the Relationship Graph and in Layout mode, but when I got to the script step 'Import Records' and then 'Specify a data source' the SQL ODBC connection isn't listed; should it be?

 

Thanks again, Wim!

 

Harry

[Wim Decorte]

ESS = External SQL Source,

the feature in FM that allows you to connect to a supported SQL source and treat its tables almost as native FM tables, including adding a table occurrence on the graph

 

The "import records [from ODBC source]" can be run server-side yes.

 

When you create a script and use the Import script step you don't see the DSN because it is on the server.  For development purposes you need to create a DSN by the same name on your development machine.

When you then schedule the script on server, FMS will look for a DSN by that same name, find it and use it.

[Harry]

Fantastic, thank you for the clarity.

 

I still don't get why i can't see the DSN's, though. I'm on a local Pro Advanced seat and I can see the DSN's, but while building that script, I can't.

 

Where you say 'you don't see (it) because it is on the server.' has confused me a bit! My apologies.

 

Harry

[Wim Decorte]

When you say "I can see the DSNs", what dialog/window are you in?

If it is the window to add an external data source, then what you are seeing really is the server's DSNs, not the ones on your machine.

 

But if you configuring the "import records" script steps, FM only shows you your local DSNs, not the ones on the server.

[Harry]

Ahhh. OK ok! So when I am selecting the fields (Layout Mode|Add Field etc.) that is the server DSN?

 

Is this 'Show/No Show' a bit of a bug type thing or is there a reason for it?

 

Cheers and thanks again for your time.

 

Harry

[Wim Decorte]

Ahhh. OK ok! So when I am selecting the fields (Layout Mode|Add Field etc.) that is the server DSN?

 

Not sure what you mean here.  But if it is a layout based on the external database TO (table occurrence) then: yes.

[Wim Decorte]

 

 

Is this 'Show/No Show' a bit of a bug type thing or is there a reason for it?

 

 

No bug, entirely by design.

 

The beauty of ESS is that you only need one DSN on the FileMaker Server and all client interaction with that ESS table will flow through the server.

With "Import Records" however you would need a DSN on each client workstation if the client needs to use the script.

 

So the show/no show comes from that: some things are done on the server (so it shows the server DSN), some tasks are done on the client (so it shows the client DSN).

The water gets muddy when the server acts as a client (like doing a server-side "import records").

[Harry]

Yes, that's what I meant.

 

OK, great. Thanks again. I have logged on to our server and built an import SQL script and it's working perfectly, thank you!

 

Cheers!

 

Harry

[Harry]

OK, I need some help with this after all.....

 

I have the script -

 

Go To Layout (Orders_FM)

Show All Records

Delete All Records [No Dialog]

Import Records

<Here's the SQL Script I built in FM>

 

[sELECT `sage_SALES_ORDER`.`ACCOUNT_REF`, `sage_SALES_ORDER`.`DESPATCH_DATE`, `sage_SALES_ORDER`.`INVOICE_NET`
FROM `sage_SALES_ORDER` WHERE `sage_SALES_ORDER`.`ORDER_DATE` >= '2014-01-06 00:00:00']

 

Exit Script.

 

This is set in a Schedule to run every 5 mins.

 

So - I'm on the Server with a  copy of FMP open working on that script. Within the 'Manage Scripts' window, I can click on the Run button and the script works. But, when it runs on the schedule it only deletes all records and leaves the table blank, skipping over the SQL bit.

 

I'm running it with full access privileges.

 

Thanks for any advice you can give - this is my first server-side script

 

Harry

[Harry]

I can see a 'Filemaker Script Error' in the Server Admin panel.

[Wim Decorte]

First off: capture right after the import script step and write the error off in a table somewhere so that you can see it.

(The FMS event log will also tell you what the error was).

 

Assuming that you have FMS13 and that your server is 64-bit.  FMP is still 32-bit you it uses the 32-bit DSN and ODBC drivers.  FMS however requires a 64-bit DSN and driver.  You need to create that and give it exactly the same name as the 32-bit one.

[Harry]

Wim?! WIIIIIIIIMMMMMM!!!

 

I made a new DSN on the server, using the same 64bit MySQL drivers, naming it 'DSN32bit' (etc. etc.) and it works.

 

You did it, Sir! Bowing, bowing oh Great Leader. 

[Wim Decorte]

Glad it worked & thanks for posting back to close the loop.