connect to FileMaker Server with pyodbc

Question

I have a FileMaker db running on FileMaker Server 14 on a Mac Mini and I'm trying to get at it with pyodbc. It's not going well.

First, what works:

telnet 192.169.19.3 2399
ssh [email protected]
tsql -H FM-Server -p 2399 -U Name -P pwd

One weird thing about that last one is that it gives me a seconds counter:
1
2
not a prompt, although I can still type commands in. I'm not sure what that means and couldn't find any info about it.

Now, what doesn't work:

tsql -LH 192.169.19.3
tsql -LH FM-Server
isql FM-Server Name pwd

No listed info for the FileMaker Server, isql gives me [ISQL]ERROR: Could not SQLConnect which is just so helpful you know

One issue is that at this point I've sort of forgotten if I should be using FM ODBC or FreeTDS as my driver in pyodbc, luckily neither of them work:

>>> c = pyodbc.connect("DRIVER={FreeTDS};DSN=FM-Server;UID=Name;PWD=pwd")  
pyodbc.Error: ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')  
>>> c = pyodbc.connect("DRIVER={FileMaker ODBC};DSN=FM-Server;UID=Name;PWD=pwd")  
pyodbc.Error: ('08S01', '[08S01] [unixODBC][FileMaker][FileMaker ODBC] Failed to connect to listener (2) (65535) (SQLDriverConnect)')

Giving it just the DSN freezes the window. Here's my configs:

odbc.ini | /usr/local/Cellar/unixodbc/2.3.4/etc/odbc.ini

[FM-Server]
Driver = FreeTDS
Host = 192.169.19.3
ServerName = FM-Server
UID = Name
PWD = pwd
Port = 2399

odbcinst.ini | /usr/local/Cellar/unixodbc/2.3.4/etc/odbcinst.ini

[ODBC Drivers]
FileMaker ODBC = Installed
FreeTDS = Installed 

[FileMaker ODBC]
Driver = /Library/ODBC/FileMaker ODBC.bundle/Contents/MacOS/fmodbc.so
Setup  = 

[FreeTDS]
Description = FreeTDS
Driver = /usr/local/Cellar/freetds/1.00.9/lib/libtdsodbc.0.so
Setup = /usr/local/Cellar/freetds/1.00.9/lib/libtdsodbc.0.so
UsageCount = 1

freetds.conf | /usr/local/Cellar/freetds/1.00.9/etc/freetds.conf

[FM-Server]
    host = 192.169.19.3
    port = 2399
    tds version = 8.0

Any info is much appreciated and shout to the resources here, here, and elsewhere for helping me get even this far.

You definitely need to use the FileMaker ODBC driver. FreeTDS is for working with Microsoft SQL Server and Sybase. — Gord Thompson
Thanks, I'll update this question when I get back to the testing machine, but, in the meantime, do you know how I can point pyodbc to the ODBC Driver Manager application from odbcmanager.net? — brnco
The ODBC Manager application is presumably just a convenient way of updating the contents of the ODBC .ini files. You would use it to create a DSN entry that uses the "FileMaker ODBC" driver, and that DSN would be written to odbc.ini. Then pyodbc would use that DSN to connect, e.g. cnxn = pyodbc.connect("DSN=MyFileMakerDSN;"). Note that when using a DSN for your connection, only the DSN name is required in the connection string since the other properties (Driver, Server, Database, ...) are defined in the DSN entry (in odbc.ini). — Gord Thompson

brnco brnco · Accepted Answer · 2016-08-19T23:32:11

I was able to solve this with the following, slightly abbreviated brew uninstall freeDTS brew uninstall unixODBC pip uninstall pyodbc re-installed pyodbc with ActualTech's installer (not sure if necessary): http://www.actualtech.com/python-osx-odbc.php

switched my odbc.ini to read "Driver=FileMaker ODBC" instead of "Driver=freeTDS" but given the code I actually ran I'm not that that's necessary: python import pyodbc c = pyodbc.connect("DRIVER={FileMaker ODBC};SERVER=192.169.19.3;PORT=2399;UID=Name;PWD=pwd") and magically that worked. idk man

connect to FileMaker Server with pyodbc

1 Answers