Connecting to Filemaker via ODBC 1

[ESquared]

We have a Filemaker Pro 5 Server running.

In the readme for the Filemaker 5 Server install, it says

2. NETWORK CONSIDERATIONS

2.1 FileMaker Server 5 supports only FileMaker Pro 5 guests over the network. Guests running versions of FileMaker Pro earlier than 5.0 will not see FileMaker Server 5 hosts in the Hosts dialog box, and will not be able to open databases hosted by FileMaker Server 5.

Does this mean what I think it means? I won't be able to use ODBC to connect to it and retrieve data?

See a connection string,
and [url=http://www.firebridge.com.au/odbc.htm]Detailed instructions for setting up ODBC to Filemaker Pro

I created an ODBC DSN using the FileMaker Pro driver and tried to use it as a linked server on SQL Server. I don't know how to query it. I tried sp_catalogs and got

OLE DB provider 'UNKNOWN' reported an error. One or more arguments were reported invalid by the provider.
[OLE/DB provider returned message: The parameter is incorrect.]
[OLE/DB provider returned message: [FileMaker][ODBC FileMaker Pro driver]Optional feature not implemented.]
OLE DB error trace [OLE/DB Provider 'UNKNOWN' IDBSchemaRowset::GetRowset returned 0x80070057: One or more arguments were reported invalid by the provider.].

I tried
sp_tables_ex 'cardio2', ''
sp_tables_ex 'cardio2', NULL

and got:

OLE DB provider 'UNKNOWN' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: [FileMaker][ODBC FileMaker Pro driver]An error has occurred while trying to gather a list of available tables.]
[OLE/DB provider returned message: [FileMaker][ODBC FileMaker Pro driver][FileMaker Pro]Connect failed]
OLE DB error trace [OLE/DB Provider 'UNKNOWN' IDBSchemaRowset::GetRowset returned 0x80004005: ].

And none of that may be helpful at all as this is the FileMaker Pro forum, not the SQL Server forum. But I'm willing to try anything. Does anyone have any suggestions?

 

[Airpan]

To answer your first question, "Does this mean what I think it means? I won't be able to use ODBC to connect to it and retrieve data?"

The readme file isn't commenting on ODBC datasources... it is commenting on version types, which I have the following information on... if you are running FileMaker Pro 5 on your users' machines and FileMaker Server 5, then you will have difficulty with users running versions previous to 5. In the FileMaker Pro 8 book that I have,just to give an example, it states that in order to use files from version 7, with version 8, you would have to convert the version 7 files over to version 8's. However, you should be able to go to their web site and find conversion applications. Not to mention, the book goes on to state that you can convert both 7 and 8 versions back to as far as FileMaker 3. The web site is

http://www.filemakerpro.com/support/updaters.html.

To get help with ODBC datasources, I am sure the same site can provide you with information regarding that as well.

 

[Airpan]

To be clearer, the readme file is telling you that your users will not be able to open the database over your network, from their computer if they are running versions of FileMaker earlier than 5.

I was assuming in my last post that you are trying to query this via a web page, but perhaps you are not. What are you using to query the db? SQL in a web page? Can you please be more specific?

 

[ESquared]

I just want to perform queries from other software applications, be it VB, or SQL Server. Generally this is accomplished between databases via ODBC. There IS a filemaker ODBC driver installed on my machine (in fact, three of them, one generic, one for Oracle, and one for SQL Server).

The difficulty I'm having is with the paucity of information online about connecting to FileMaker Pro via TCP/IP from another computer. When I search for ODBC and such, I get loads of information about connecting to other databases from within FileMaker Pro, and not much for the other way around. I was hoping to do my tasks without having to locate the program logic in FileMaker Pro script steps.

If I could just get some known working examples, either T-SQL code for SQL Server or something for MS Access or something for VB or any language using ADODB, I could take it from there!

 

[TStriker]

ESquared,

Version 5 is going back a few years and my memory ain't what it once was so I don't really remember the specific details of how its done. I do however remember quite clearly that the connection is slow, painfully slow, agonizingly slow. If I recall correctly it took maybe 30 seconds to retrieve 100 rows x 5 columns.

It was so slow that I immediately abandoned my plans to use FM as an ODBC data source. I felt that many of my users would think their systems had frozen and would reboot rather than wait for the data transfer to finish.

Could you possibly export the data from FM into an .xls or .csv format and use that as your data source?

-Striker

 

[ESquared]

Striker,

Thanks for the help. Your help has been very... helpful!

It looks like if we want FM data we'll have to schedule a periodic data dump as you suggest.

What do you think about using script steps from inside FM via ODBC to issue update statements to other databases?

Erik

Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.

 

[TStriker]

Again, my memory of version 5 is hazy but I *think* that will work in a limited manner. I wouldn't choose FM as the exclusive front-end to a SQL database but I wouldn't hesitate to use it to make updates in a limited fashion.

One downside is that the ODBC connections are on the FM client workstations so if you have 10 users all needing to update SQL data you will need to establish a DSN at each workstation.

-Striker

 

[ESquared]

Erg, um, ah yes FM is a client-side thingie. So script steps have to run in a client context, they can't be scheduled to run on the server.

I think I'll just let my colleague go with the "print to a virtual printer and scrape the postcript" method he's already planning on using. Sigh.

------------------

Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.

 

[Airpan]

Sorry my information was useless. I understand ODBC datasources as I use them quite often... the version type seemed more prominent to me. No matter.

 

[ESquared]

No problem! Have a star for trying.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]