Writing into FM database from within MySQL - How?

[gczychi]

Hi,

accessing external SQL resources from within FileMaker is quite common. But how do I go about the exact opposite?

I have an import_nwde:: table in a fmp12 file hosted on FMS 15.

I have switched on ODBC in FMS.

I can access the tables with Windows and Mac (using the Filemaker ODBC drivers specifically installed to do this).

But how can I write into this table from within a MySQL database running on a LAMP machine? Whenever something happens on the website, I need to process the data in FileMaker.

 

Thanks a lot for any help,

Gary

[webko]

With ESS, you don't - the external data source is effectively a FileMaker table anyway... So, you treat it as such, and set fields to run updates into the FileMaker only tables

 

[gczychi]

5 hours ago, webko said:

So, you treat it as such, and set fields to run updates into the FileMaker only tables

What do you mean by that? In order to run mysql [update, etc] queries on my filemaker database I need to make it available to mysql, first. But how do I do that? Do I need a FileMaker ODBC driver for the mysql LAMP installation? Probably, but which? What do you use?

Thanks,

Gary

[Wim Decorte]

You have a couple of different ways to interface with FM data from the MySQL side:

ODBC/JDBC: you need to turn on xDBC in FMS and you need the FM xDBC driver installed on your MySQL box.

The FMS XML API (or PHP API): you can treat FMS as a web server and push/get data through XML API urls or through PHP pages.

[beverly]

+ wim and the third option by webko. ESS is a "two-way sync" if you will with FM and the MySQL. you can push or pull data between the two. If the data is in a FileMaker table (TO), then Set Field with it into the MySQL table (TO). ESS makes the data in the external SQL source behave much like any other "native" table in FileMaker. if you can move data from one native table to another in FileMaker, you can do the same with ESS (provided you have the proper permissions, of course!) NO SQL commands (via xDBC) or XML/PHP needed.

in addition, should you wish, you can use Import and Execute SQL script steps to exchange data between FM tables and SQL tables (not ESS, but SQL queries)

beverly

[gczychi]

10 hours ago, Wim Decorte said:

…you need the FM xDBC driver installed on your MySQL box.

Is there one? In my distribution, there is only a Mac and a Windows (32 & 64 bit) version. And I tested them both (they work fine).  But I haven't seen any driver for a LAMP box.

Thanks for any hints, there.

Gary

Edited November 17, 2016 by gczychi

[gczychi]

8 hours ago, beverly said:

ESS is a "two-way sync" if you will

Yes, but in my scenario, this would mean too much unnecessary communication:  I could script FileMaker to check for any changes in the external mysql tables. But 95% of the time searching these external tables would be useless, because nothing would have changed — too much overhead. Instead I just want to check for changes in a local table. And for that to happen, the mysql database needs to directly write into my Filemaker table. It has several advantages in my scenario.

So that's the why I need mysql to write into a remote FileMaker table.

Do you have any suggestion for a LAMP driver?

Thanks a lot,

Gary

[gczychi]

 

Is there anyone who has installed ODBC drivers on a LAMP machine?

Grateful for any information.

Thx!

[webko]

You're not getting the beauty of ESS - it is *both* an SQL table, and a local FileMaker table.

It happens in real time, so you can check whatever you need, as you need. 

Try it... 

[gczychi]

I can't fully understand what you're saying, yet:

 

•  The remote sql server writes a record into it's local table

According to you:

•  At the same time the FileMaker instance of this table is updated.  As if it were local? How is that possible?

 

According to my experience, the following happens:

Whenever I scan through the (local shadow copy of the sql) Filemaker table (with ESS) to find new or updated records, the query always goes through the internet and is acted upon on the remote (sql) machine. This takes significantly more time to execute (than walking through a really local table). The user only has the illusion that it's local, because this whole process is (cleverly) transparent.

And this is the crux: Because I cannot know, exactly when a new remote record is created or an existing remote record is updated, I need to constantly query the remote database in order to pull the information that I need. That's a waste, because 95% of my queries (likely more) are in vain, because nothing might have changed.

That's the reason why I want to push new or updated records from the webserver into my local Filemaker. Because then, I can scan through the (really local) Filemaker table without a speed penalty and without unnecessary wasting bandwidth of my internet connection.

Does that make sense?

 

Gary

 

[_ian]

Given what you want to do, you might consider looking at MirrorSync by 360Works. It's a synchronisation production that allows you to sync record creation, deletions, updates between two databases. In your scenario you could configure it to sync record changes from the MySQL tables into your FileMaker system. We use it to read from our FileMaker system into a data warehouse in SQL Server. 

[gczychi]

@_ian:  Thanks for the info. I looked at it and it looks very good. However, $1800 just for syncing one table is quite a statement.

If I only would know how to access the Filemaker database from within MySQL, then it would be just a couple of minutes to write the code.

MirrorSync uses JDBC. Can MySQL talk directly over JDBC connection?

Thanks,

 

Gary

 

[Wim Decorte]

When you use the LAMP stack you're pretty much limited to JDBC... how much experience do you have with JDBC?

[gczychi]

Not directly, but I know C and don't need to code it, I just need to know the how.

Actual coding will be done by the web agency who are managing the webserver. It's a question of accountability, that's also part of the reason why I want the other side to push the data instead of us pulling the data.

If it's something like this, it shouldn't be a problem:

try (Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery( "SELECT * FROM MyTable" )
) {
    while ( rs.next() ) {
        int numColumns = rs.getMetaData().getColumnCount();
        for ( int i = 1 ; i <= numColumns ; i++ ) {
           // Column numbers start at 1.
           // Also there are many methods on the result set to return
           //  the column as a particular type. Refer to the Sun documentation
           //  for the list of valid conversions.
           System.out.println( "COLUMN " + i + " = " + rs.getObject(i) );
        }
    }
}

I would only need to know if there is anything else needed on the LAMP side for this to work, apart from just opening the connection.

Thanks, Wim.

Gary