Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 951
  • Last Modified:

Accessing FileMaker data with FileMaker server 7.0

hi,

I can set up ODBC when using FileMaker pro and everything is fine.  With FileMaker Server running, I cannot use ODBC anymore; unless I get FileMaker Advanced Server apparently.  

is there any way (ODBC or other) to access FileMaker data when using FileMaker Server 7.0?

thanks.
0
pcarrier
Asked:
pcarrier
  • 8
  • 4
  • 4
2 Solutions
 
billmercerCommented:
If you mean pulling data directly from the FileMaker server itself, you can't.  

If you use your local copy of FileMaker to open a file hosted on a separate FM server, can you create an ODBC connection to the application? I know this works with earlier versions, but I have not tried it with FM 7 yet. I'll have to give it a try.

If that doesn't work anymore, you can still use the FileMaker client to share data via the web. You can import data from an XML or HTML report presented by FileMaker's web publishing features.

0
 
pcarrierAuthor Commented:
hi billmercer,

I have tried using the local copy of FileMaker to make an ODBC connection to the hosted file; didn't work.

I will try to make an export via the web.

thanks.
0
 
lesouefCommented:
According to filemaker, with the advanced server v7 should be able to make a direct ODBC connection to the server itself, as long as web access.
http://www.filemaker.com/products/fmsa_overview.html
(not tested, there is no trial version of the advanced server, only normal server)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
billmercerCommented:
While it's disappointing to hear that FileMaker has removed this ability, it's not that surprising. They've done similar things in the past, removing popular features and making you pay extra to get them back.

One advantage to using web publishing is it's usually pretty fast. Although FileMaker 7's ODBC is a lot better than previous versions, from what I've seen it's still not as fast as using web publishing.

Lesouef, he doesn't want to pay for FM Server Advanced, that's why I suggested the web publishing approach.
0
 
pcarrierAuthor Commented:
Can you give me a bit more information on the web publishing idea?
0
 
lesouefCommented:
Bill,
no problem mate, I just read his question too quick!

Pcarrier,
web stuff is the same as using the web companion on FM6 except that the web companion is now on the server.
That's definitly different from ODBC, you have to generate the queries as URL's, and you get XML data in return. So you must be fluent in these languages to use it, not the easiest compared to ODBC!
0
 
pcarrierAuthor Commented:
From the FileMaker manual:

"Note  Instant Web Publishing is not supported in the basic FileMaker Server product."

It's the same idea as ODBC.  You have it on FileMaker Pro; you lose it with FileMaker Server; and you get it back with FileMaker Advanced Server.

My solution right now is to make export script in FM and use ActiveX Automation to run them and then get the data to use it.

Any other idea?
0
 
billmercerCommented:
Well after doing a quick experiment, it does appear that FileMaker ODBC does NOT work when connected to an FMServer hosted database.

Greed is with us always...





0
 
billmercerCommented:
OK, I can't try this from home, but it seems like it ought to work.

Create a local file that has the same field names as the existing table you want to export. Except for the primary key, the fields are all calculations that look up the desired fields from the database on the server. Make a relationship between the local file's primary key and that in the server-hosted file. Import the values of the primary key from the server-based file, and if my theory's right, you now have a "shadow" copy of the server-based data. Share just this file via ODBC, and use a script to automatically create new records in the local file on a regular basis.

I'll be working this weekend, I will give it a try and report back what I find.
0
 
lesouefCommented:
I am quite happy to stick with fm6 and fm5server so far! Gosh, I thought the difference between the server and the advanced server was only the amount of users as far as the web companion is concerned...
Duplicating (as bill said) the base is a way though more work than strait export, and still not real time access.
What are the apps needing to access to FM data?
0
 
billmercerCommented:
Of course doing an export would be much simpler, and probably faster than using ODBC, so if that option will work, it's definitely the way to go. I assumed pcarrier was looking for a live update sort of thing.

Actually, I bet you can use web publishing to allow almost realtime updates. If you create a local FM file that publishes a simple web page, and have it call a script that in turn runs exports from the server-hosted files to XML or maybe HTML tables, which can then be served by your desktop's web publishing. Then just grab them via the application of your preference. That's probably as close to realtime as you're going to get without shelling out for Server Advanced. You'd still have to tie up a client all the time, but...

HOLY COW! they want $1500 to upgrade Server 7 to Server Advanced, and yet I can get Lasso for under $700??? Methinks someone at FileMaker is smoking one of those funny little pipes...

Why do companies have to behave so greedily? It's not like FileMaker is a cheap program to begin with...
0
 
billmercerCommented:
There's one other thing, probably not worth mentioning, but I'm mentioning it anyway. If you have Filemaker Developer 7, you can get  a special developer's version of Server Advanced for just ten dollars. It only allows three simultaneous connections of each type, so it's pretty useless for a production database.
0
 
lesouefCommented:
You are right, their price policy is disappointing. As usual, the lack of success on the market will regulate this.
They have already abused a bit by launching fake versions like 5 + 5.5 + 6 which should have been a single version.
And a lot of fucntionnalities are sexy on paper while they are gimmicks which you test, but never use. And they still lack very basic things like saving a file of which the name is in a field (probably to protect Troi?). And the web companion is still not up to professional standards, I mean like Lotus Notes intranet interfaces; you need an extra tool to design the web part. Same thing for images handling which becomes unusable over 100 images, very few formats supported, CMYK unsupported, which makes it a tool for stamp collectors, not more, while they pretend to ne a multimedia database...
Back to our pb, I asked him what are the apps to get the data, because my idea is to use FM as a SQL client and put data in another database like mysql. That would make FM server useless, but for internet purposes, I think it is still too much work for what it does...
0
 
billmercerCommented:
Pcarrier, as I understand it, you already have FM Server, and are trying to find a way to get data from it to an external application via ODBC, JDBC, web interface, or something similar, without having to pay for FM Server Advanced.

I think you really only have two options. Either you do some sort of export/update via a constantly connected FM client, which ties up a client license, or you use a third-party product like Lasso that lets you pull data directly from the FM server as needed, which costs money.

Lasso is much cheaper than Server Advanced, and can be used for a variety of database platforms, so it might be worth looking at. They have a 30 day demo version so you can try it and see if it will work without having to invest any money first.

Lesouef's suggestion that you look at a different back-end database has a lot of advantages. You're basically unlimited in what you can accomplish. The down side to this is you end up wasting the FM Server, which I assume you've already paid for, and if you're not familiar with the products, the learning curve can be steep.

If you like LeSouef's suggestion, there are many excellent SQL databases you can use free of charge, including MySQL, PostGRESQL, FireBird, SAPBDB, the list goes on. Even Microsloth's got one, the Desktop Database Engine, which is free though limited in performance.

All are powerful, stable, and can easily communicate with ODBC, JDBC, XML, PHP, Perl, whatever. I like PostGRESQL because it's more reliable, and a bit more like Oracle, but MySQL has better performance and is probably the most popular. All are very widely supported. And they're available for pretty much any platform.

Lesouef, I've found the custom function feature of 7 goes a long way towards solving the filename via field issue. You can create a custom function that acts as a "wrapper" for one of the free plugins, such as the Protolight file plugin, which you can store in a container field, and then drop into the user's application folder as needed via script. From the end user's perspective it's pretty seamless. Yes, it's still a pain in the butt, however it's MUCH better than having to write a bunch of subscripts that do various cryptic external function calls, and then asking the user to go download something. And if you want to change plugins, you can just recode the function and leave all your scripts intact. It's not perfect, but it's a big improvement over v6.

The real problem I see is FileMaker's lack of a real API. Mac users are in better shape, because they can use Applescript, but as long as everything must go through the limited plugin API, FileMaker is going to be very restrictive to any sort of interoperability with other applications.

0
 
pcarrierAuthor Commented:
Hi guys,

thanks for all this info.

I am basically new to working with FM.  The job is for a client that uses FM and want to have his data transfered to his accounting system(Simply Accounting) with no human intervention.  It does not need to be real time, but it need to be updated at least a few times a day.  The key is no human intervention.

I did have a hard time explaining this FM pricing and feature thing to my client.  They are thinking about getting the Advanced Server; I should get an anwer today on that.  If they don't, I need to do this with the least amount of work, while keeping it easily maintainable.  The whole thing is part of a larger project run by another consultant, so although I love the other backend solution (that how I would have done things in the first place), it's really feasible at this moment; not within budget anyway.

So, I think my option will be to have export scripts in FM.  As far as the API goes, I think their Automation server is quite minimal, but I can run a FM script from my application, so that should be enough.

Any last comment?

thank you very much billmercer and lesouef for helping me get the jist of FM.
0
 
billmercerCommented:
Generating export files from FM is very fast and easy. I have a system where I generate exports from a number of files on a scheduled basis, and transfer the files to a remote server where they are automatically imported into a SQL-based system. It runs several times a day, and works quite well. I could run it every half-hour if I wanted to.

As long as the accounting software has a way to automatically import a file, this should be very easy to do. The best way to schedule the export is to create a local FM file with an autorun script which then calls the export script from the server-based file, sends the file to the other server, and then exits itself. You can then use the at service or cron or something to schedule this file to open on a regular basis.

Something you might find helpful when working with export files is the Protolight File Plugin. It allows you to manipulate files and folders from within FileMaker scripts, and is free.
Do a Google search for Protolight Plugin to find the site.
 
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 8
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now