Unable to connect to FileMaker Pro

[macfreek]

The pyodbc.Error

I don't seem to be able to connect to a local FileMaker Pro database running on the same computer. I'm using pyodbc with unixODBC on MacOS:

>>> import pyodbc
>>> dsn = "Driver=/Library/ODBC/FileMaker ODBC.bundle/Contents/MacOS/fmodbc.so;\
Server=localhost;Port=2399;Database=MyDatabase;UID=Admin;PWD="
>>> pyodbc.connect(dsn)
Traceback (most recent call last):
  File "<python-input-2>", line 1, in <module>
    pyodbc.connect(dsn)
    ~~~~~~~~~~~~~~^^^^^
pyodbc.Error: ('0000', '[0000] [ (1) (SQLDriverConnect)')

unixODBC does work (and so did pyodbc)...

There should be a way to get this to work: the unixODBC-provided isql tool seems to work just fine (-k ensures it is using SQLDriverConnect instead of SQLConnect):

isql  -k -v "Driver=/Library/ODBC/FileMaker ODBC.bundle/Contents/MacOS/fmodbc.so;Server=localhost;Port=2399;Database=MyDatabase;UID=Admin;PWD="
SQL> help

This does list the tables in my database. So at least I know the connection through unixODBC works as intended.

Even more annoyingly, the combination of pyodbc with unixODBC worked for me in the past (at least 2018-2022, perhaps later), as long as I set the encoding after establishing a connection:

connection = pyodbc.connect(dsn)
connection.setencoding(encoding='utf-8')
connection.setdecoding(pyodbc.SQL_CHAR, encoding='macroman')

However, today (end 2025), it already fails during the pyodbc.connect() call.

I strongly suspect this to be a encoding issue (perhaps sprinkled with some bugs in the FileMaker ODBC implementation), but can't seem to get to find a working solution with pyodbc.

My Questions

Before I list more technical details, my main questions:

1. Is there a known solution or work-around?
2. Would it be possible to add an entry for FileMaker Pro on https://github.com/mkleehammer/pyodbc/wiki ? (even if it is just an entry stating that FileMaker is too buggy, and is not supported)

---

Technical details of my setup

• Running MacOS 15.7.3 (Sequia) on Apple silicon (M2 Pro processor)
• FileMaker Pro 20 (2023)
• Installed FileMaker ODBC Client Driver, as downloaded from the Claris (FileMaker) support website: https://support.claris.com/s/answerview?language=en_US&anum=12921
• Installed the ODBC Manager by Actual Technologies, found on www.odbcmanager.net (and linked from the above support website).
• Installed unixODBC using MacPorts: port install unixODBC
• Installed pyodbc using pip: pip install pyodbc --no-binary :all: (build from source, because the binary distribution hardcoded a link to unixODBC which is Homebrew-specific)

The version that worked for me in the past also used the same install process, only earlier versions of MacOS, and perhaps also an older version of FileMaker Pro.

Attempts to debug the situation

I did try with FileMaker Pro 22 (2025) as well, but that seems to have the same problem. I have not extensively tested that.

I did try to set the encoding with the pyodbc.connect() call, and got some different results:

Command (with unixODBC)	Result
pyodbc.connect(dsn)	pyodbc.Error: ('0000', '[0000] [ (1) (SQLDriverConnect)')
pyodbc.connect(dsn, encoding='UTF-16LE')	pyodbc.Error: ('0000', '[0000] [ (1) (SQLDriverConnect)')
pyodbc.connect(dsn, encoding='UTF-16BE'))	pyodbc.InterfaceError: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')
pyodbc.connect(dsn, encoding='UTF-8'))	pyodbc.InterfaceError: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')
pyodbc.connect(dsn, encoding='UTF-32'))	pyodbc.InterfaceError: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')
pyodbc.connect(dsn, encoding='UTF-32LE')	pyodbc.InterfaceError: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')
pyodbc.connect(dsn, encoding='non_existant'))`	SystemError: <built-in function connect> returned NULL without setting an exception

I did not get a good trace log. I suspect that I configured the wrong file (ODBC Manager told me to use ~/Library/ODBC/odbcinst.ini; odbcinst -j told me to use /opt/local/etc/odbcinst.ini)

I tried to use iODBC instead of unixODBC, as explained on https://www.actualtech.com/python-osx-odbc.php

Command (with iODBC)	Result
pyodbc.connect(dsn)	pyodbc.Error: ('IM0', '[IM0] [\x00i\x00O\x00D\x00B\x00C\x00]\x00[\x00D\x00r\x00i\x00v\x00e\x00r\x00 \x00M\x00a\x00n\x00a\x00g\x00e\x00r\x00]\x00N\x00o\x00 \x00d\x00a\x00t\x00a\x00 \x00s\x00o\x00u\x00r\x00c\x00e\x00 \x00 (0) (SQLDriverConnect)')
pyodbc.connect(dsn, encoding='UTF-16LE')	pyodbc.Error: ('IM0', '[IM0] [\x00i\x00O\x00D\x00B\x00C\x00]\x00[\x00D\x00r\x00i\x00v\x00e\x00r\x00 \x00M\x00a\x00n\x00a\x00g\x00e\x00r\x00]\x00N\x00o\x00 \x00d\x00a\x00t\x00a\x00 \x00s\x00o\x00u\x00r\x00c\x00e\x00 \x00 (0) (SQLDriverConnect)')
pyodbc.connect(dsn, encoding='UTF-16BE')	pyodbc.Error: ('IM0', '[IM0] [\x00i\x00O\x00D\x00B\x00C\x00]\x00[\x00D\x00r\x00i\x00v\x00e\x00r\x00 \x00M\x00a\x00n\x00a\x00g\x00e\x00r\x00]\x00N\x00o\x00 \x00d\x00a\x00t\x00a\x00 \x00s\x00o\x00u\x00r\x00c\x00e\x00 \x00 (0) (SQLDriverConnect)')
pyodbc.connect(dsn, encoding='UTF-8')	pyodbc.Error: ('IM0', '[IM0] [\x00i\x00O\x00D\x00B\x00C\x00]\x00[\x00D\x00r\x00i\x00v\x00e\x00r\x00 \x00M\x00a\x00n\x00a\x00g\x00e\x00r\x00]\x00N\x00o\x00 \x00d\x00a\x00t\x00a\x00 \x00s\x00o\x00u\x00r\x00c\x00e\x00 \x00 (0) (SQLDriverConnect)')
pyodbc.connect(dsn, encoding='UTF-32')	pyodbc.Error: ('IM0', '[IM0] [\x00i\x00O\x00D\x00B\x00C\x00]\x00[\x00D\x00r\x00i\x00v\x00e\x00r\x00 \x00M\x00a\x00n\x00a\x00g\x00e\x00r\x00]\x00N\x00o\x00 \x00d\x00a\x00t\x00a\x00 \x00s\x00o\x00u\x00r\x00c\x00e\x00 \x00 (0) (SQLDriverConnect)')
pyodbc.connect(dsn, encoding='UTF-32LE')	No error, valid connection
pyodbc.connect(dsn, encoding='non_existant')`	SystemError: <built-in function connect> returned NULL without setting an exception

Clearly, it wants to tell me [IM0] [iODBC][Driver Manager]No data source (0) (SQLDriverConnect), but adds a few NULL characters. This also indicates some encoding issue to me.

Edit: Apparently pyodbc with iODBC does work, when I set the encoding to UTF-32LE.

[macfreek]

Edit: after posting the above, I found that for me pyodbc with unixODBC still always fails (even though the iqsl tool from unixODBC works). Furthermore, pyodbc with iODBC almost always fails. It only works if I explicitly set the encoding to 'UTF-32LE'. I updated my post accordingly. My questions still stand.

This whole encoding still feels awfully finicky.

For the quick and dirty, this worked for me:

pip install pyodbc --extra-index-url https://actualtech.com/python

connection = pyodbc.connect(dsn, encoding='UTF-32LE')
connection.setencoding(encoding='utf-8')
connection.setdecoding(pyodbc.SQL_CHAR, encoding='macroman')

[macfreek]

Well, the good news, I can interact with the FileMaker database.
The bad news, it is as stable as a jenga tower build on quicksand.

I wrote a little test script, and Python crashed about 50% of the time.

Exit code	Runs	Result
0	31×	Success
134	22×	Abort after malloc failure while looping cursor.fetchone()
139	9×	Segfault during/after connection.close()
139	4×	Segfault during/after cursor.execute()

Results.. The good, the bad and the ugly

The good: success:

freek@laptop:~/scripts % date +'%H:%M:%S.%N'; python test_odbc_filemaker.py; echo $?
00:57:17.249009000
DEBUG    establish conection
DEBUG    set encoding
DEBUG    get cursor
DEBUG    query database
DEBUG    query database
DEBUG    close cursor
DEBUG    close connection
0

The bad: script aborts due to a malloc failure

freek@laptop:~/scripts % date +'%H:%M:%S.%N'; python test_odbc_filemaker.py; echo $?
00:57:20.650651000
DEBUG    establish conection
DEBUG    set encoding
DEBUG    get cursor
DEBUG    query database
DEBUG    query database
Python(85504,0x20a66e240) malloc: Corruption of tiny freelist 0x1526a3b40: size too small (0/26)
Python(85504,0x20a66e240) malloc: *** set a breakpoint in malloc_error_break to debug
[1]    85504 abort      python test_odbc_filemaker.py
134

The ugly: Python segfaults:

freek@laptop:~/scripts % date +'%H:%M:%S.%N'; python test_odbc_filemaker.py; echo $?
00:57:19.884768000
DEBUG    establish conection
DEBUG    set encoding
DEBUG    get cursor
DEBUG    query database
DEBUG    query database
DEBUG    close cursor
DEBUG    close connection
[1]    85494 segmentation fault  python test_odbc_filemaker.py
139

And it seems I have FileMaker ODBC to blame for this ordeal. I captured a few of the segfaults, and all were in the FileMaker ODBC code. Not in the pyodbc code.

A few sample segfaults

Segfault 1:

Thread 0 Crashed::  Dispatch queue: com.apple.main-thread
0   FileMaker ODBC                	       0x103792fd4 0x103524000 + 2551764
1   FileMaker ODBC                	       0x1037c88a0 SQLDescribeColW + 1928
2   pyodbc.cpython-313-darwin.so  	       0x1034cfc20 SQLDescribeCol_Internal + 408
3   pyodbc.cpython-313-darwin.so  	       0x1034d01d8 SQLDescribeColW + 392
4   pyodbc.cpython-313-darwin.so  	       0x1034ac80c create_name_map(Cursor*, short, bool) + 212
5   pyodbc.cpython-313-darwin.so  	       0x1034abc10 execute(Cursor*, _object*, _object*, bool) + 800
6   Python                        	       0x102f468c4 method_vectorcall_VARARGS + 124
7   Python                        	       0x102f3a64c PyObject_Vectorcall + 92
8   Python                        	       0x103060fb0 _PyEval_EvalFrameDefault + 5096
9   Python                        	       0x10305f94c PyEval_EvalCode + 200
10  Python                        	       0x1030cf550 run_eval_code_obj + 104
11  Python                        	       0x1030ceea4 run_mod + 168
12  Python                        	       0x1030cd54c pyrun_file + 164
13  Python                        	       0x1030cc824 _PyRun_SimpleFileObject + 256
14  Python                        	       0x1030cc45c _PyRun_AnyFileObject + 80
15  Python                        	       0x1030f443c pymain_run_file_obj + 164
16  Python                        	       0x1030f4084 pymain_run_file + 76
17  Python                        	       0x1030f32f4 Py_RunMain + 968
18  Python                        	       0x1030f38f0 pymain_main + 304
19  Python                        	       0x1030f3990 Py_BytesMain + 40
20  dyld                          	       0x19c336b98 start + 6076

Segfault 2:

Thread 0 Crashed::  Dispatch queue: com.apple.main-thread
0   libsystem_kernel.dylib        	       0x19c69e388 __pthread_kill + 8
1   libsystem_pthread.dylib       	       0x19c6d7848 pthread_kill + 296
2   libsystem_c.dylib             	       0x19c5e09e4 abort + 124
3   libsystem_malloc.dylib        	       0x19c4e4174 malloc_vreport + 892
4   libsystem_malloc.dylib        	       0x19c50f954 malloc_zone_error + 100
5   libsystem_malloc.dylib        	       0x19c4dc784 tiny_free_list_remove_ptr + 500
6   libsystem_malloc.dylib        	       0x19c4dbe34 tiny_free_no_lock + 792
7   libsystem_malloc.dylib        	       0x19c4db99c free_tiny + 460
8   FileMaker ODBC                	       0x1055d50a8 0x105358000 + 2609320
9   FileMaker ODBC                	       0x1055d511c 0x105358000 + 2609436
10  FileMaker ODBC                	       0x1055c6254 0x105358000 + 2548308
11  FileMaker ODBC                	       0x10538651c 0x105358000 + 189724
12  FileMaker ODBC                	       0x1055c33a4 SQLFreeHandle + 1640
13  pyodbc.cpython-313-darwin.so  	       0x10448077c SQLFreeStmt_Internal + 240
14  pyodbc.cpython-313-darwin.so  	       0x10449c084 SQLFreeHandle + 736
15  pyodbc.cpython-313-darwin.so  	       0x104474c60 closeimpl(Cursor*) + 92
16  pyodbc.cpython-313-darwin.so  	       0x104474ef0 Cursor_close(_object*, _object*) + 96
17  Python                        	       0x104df6d08 method_vectorcall_NOARGS + 116
18  Python                        	       0x104dea64c PyObject_Vectorcall + 92
19  Python                        	       0x104f10fb0 _PyEval_EvalFrameDefault + 5096
20  Python                        	       0x104f0f94c PyEval_EvalCode + 200
21  Python                        	       0x104f7f550 run_eval_code_obj + 104
22  Python                        	       0x104f7eea4 run_mod + 168
23  Python                        	       0x104f7d54c pyrun_file + 164
24  Python                        	       0x104f7c824 _PyRun_SimpleFileObject + 256
25  Python                        	       0x104f7c45c _PyRun_AnyFileObject + 80
26  Python                        	       0x104fa443c pymain_run_file_obj + 164
27  Python                        	       0x104fa4084 pymain_run_file + 76
28  Python                        	       0x104fa32f4 Py_RunMain + 968
29  Python                        	       0x104fa38f0 pymain_main + 304
30  Python                        	       0x104fa3990 Py_BytesMain + 40
31  dyld                          	       0x19c336b98 start + 6076

Segault 3:

Thread 0 Crashed::  Dispatch queue: com.apple.main-thread
0   libsystem_kernel.dylib        	       0x19c69e388 __pthread_kill + 8
1   libsystem_pthread.dylib       	       0x19c6d7848 pthread_kill + 296
2   libsystem_c.dylib             	       0x19c5e09e4 abort + 124
3   libsystem_malloc.dylib        	       0x19c4e4174 malloc_vreport + 892
4   libsystem_malloc.dylib        	       0x19c50f954 malloc_zone_error + 100
5   libsystem_malloc.dylib        	       0x19c4d9ce8 tiny_malloc_from_free_list + 1988
6   libsystem_malloc.dylib        	       0x19c4d8dc4 tiny_malloc_should_clear + 272
7   libsystem_malloc.dylib        	       0x19c4d7be8 szone_malloc_should_clear + 84
8   libc++abi.dylib               	       0x19c6913e0 operator new(unsigned long) + 52
9   FileMaker ODBC                	       0x105b99fc8 0x10591c000 + 2613192
10  FileMaker ODBC                	       0x105954298 0x10591c000 + 230040
11  FileMaker ODBC                	       0x105950c44 0x10591c000 + 216132
12  FileMaker ODBC                	       0x10594fa34 0x10591c000 + 211508
13  FileMaker ODBC                	       0x105b8be1c 0x10591c000 + 2555420
14  FileMaker ODBC                	       0x105bc5904 SQLExecDirectW + 1772
15  pyodbc.cpython-313-darwin.so  	       0x1049866b4 SQLExecDirect_Internal + 360
16  pyodbc.cpython-313-darwin.so  	       0x1049869bc SQLExecDirect + 264
17  pyodbc.cpython-313-darwin.so  	       0x104977a94 execute(Cursor*, _object*, _object*, bool) + 420
18  Python                        	       0x1053ba8c4 method_vectorcall_VARARGS + 124
19  Python                        	       0x1053ae64c PyObject_Vectorcall + 92
20  Python                        	       0x1054d4fb0 _PyEval_EvalFrameDefault + 5096
21  Python                        	       0x1054d394c PyEval_EvalCode + 200
22  Python                        	       0x105543550 run_eval_code_obj + 104
23  Python                        	       0x105542ea4 run_mod + 168
24  Python                        	       0x10554154c pyrun_file + 164
25  Python                        	       0x105540824 _PyRun_SimpleFileObject + 256
26  Python                        	       0x10554045c _PyRun_AnyFileObject + 80
27  Python                        	       0x10556843c pymain_run_file_obj + 164
28  Python                        	       0x105568084 pymain_run_file + 76
29  Python                        	       0x1055672f4 Py_RunMain + 968
30  Python                        	       0x1055678f0 pymain_main + 304
31  Python                        	       0x105567990 Py_BytesMain + 40
32  dyld                          	       0x19c336b98 start + 6076

I kind of suspect that it worked fine with my previous Intel Mac (2018-2022), and that the FileMaker ODBC has bugs that manifest themselves with Apple Silicon. It is also clear that I don't use this very often. To be precise, I'm using this for a personal database with purchases to keep track of warranties and the like -- I use Python to fetch some external sources, or clean the data every once in a while). Perhaps I should move to another tool (LibreOffice Base perhaps?)

---

To the author of pyodbc

It is annoying that isql ↔︎ unixODBC ↔︎ FileMaker ODBC client driver ↔︎ FileMaker Pro works fine, but pyodbc ↔︎ unixODBC ↔︎ FileMaker ODBC client driver ↔︎ FileMaker Pro fails to work. Still makes me wonder if a few encoding tweaks may fix that. Unfortunately, it is unlikely I am able to create a patch myself, and do understand if this is low priority, given the (perceived?) instability of the FileMaker ODBC client driver. So perhaps the best action is to point users to these pitfalls (feel free to use this ticket for that).

To users who want to get pyodbc to work with FileMaker Pro

If you came here because you experienced the same issues as I did: in the comment above you will find a way how I got it to work using iODBC and setting the encoding. That said, you are likely to experience more issues. For me that was a lot of crashes of Python due to bugs in the FileMaker ODBC client driver. Unless you convince Claris to fix their bugs, you will have to accept that this is not a stable solution.