FileMaker ODBC Debugger
A tool to help you write and test SQL queries for a FileMaker database while handling some of the limitations and quirks of the driver. It warns you about things to avoid, and supports syntax highlighting for all of FileMakers' reserved keywords. See the full feature list.
Motivation
FileMaker's ODBC driver has its own flavour of SQL syntax, as well as some lesser-known quirks. In addition, FileMaker ODBC performance can be very slow . To help with performance testing it shows the total execution time split into 3 different times; 'connect', 'execute', and 'stream'. This can sometimes give insight into where the bottleneck is (calculation fields are usually the culprit).
To dig deeper with optimising FileMaker queries, refer to this Stack Overflow thread, and this article about the ExecuteSQL
function.
Installing
Get familiar with the FileMaker ODBC Guide and follow these instructions for sharing your FileMaker database via ODBC.
- Download and install the latest release.
- Install the 64bit FileMaker ODBC driver that comes bundled with FileMaker.
- Run FileMaker ODBC Debugger and ensure the correct
Driver
version has been detected. - Enter values for
Server
(name or ip address),Database
(name), andCredentials
(username/password). - Type your query and then execute it!
Building
- Install the Wix Toolset Visual Studio 2019 Extension.
Limitations
- Works with 64 bit drivers only.
- FileMaker doesn't let you specify a port other than 2399.
- Some versions of FileMaker don't allow executing ODBC queries in parallel. Apparently this has been fixed in version 19.
Features
- Allow comments, single-line
--
and multi-line/*
. - Execute multiple statements as a transaction, separated by a semi-colon
;
. - Convert all line breaks to carriage returns (FileMaker uses
CR
for line breaks). - Field names starting with an underscore will be automatically escaped with double quotes.
- Show how long the query took to execute (separate durations for connect, process, and stream).
- Text editor supports syntax highlighting for all of the supported FileMaker SQL keywords (There are reserved keywords that can clash with your field and table names). Colours are similar to Microsoft SQL Server Management Studio.
- Multiple tabs. Each tab has its own connection details. Tabs are remembered when opening the program again.
- Execute two or more queries in different tabs at the same time.
- Show warnings for several FileMaker-specific syntax issues:
- FileMaker stores empty strings as NULL, so using
WHERE column = ''
orWHERE column <> ''
on aText
field will always return 0 results. UseIS NULL
instead. - FileMaker ODBC does not support the keywords
TRUE
orFALSE
. Use1
or0
instead. - Query contains the keyword
BETWEEN
and FileMaker ODBC is VERY slow when comparing dates this way. Use>=
and<=
instead.
- FileMaker stores empty strings as NULL, so using
- Provide links to the official FileMaker references/guides (in right-click menu).
- Right-click menu allows you to paste common FileMaker SQL functions.
- Right-click on query results and
Copy Selected Column(s) As CSV
(handy for executing a follow-upWHERE IN (x,y,z)
query). - Quickly search query results for a keyword.
- Limit query results to a maximum number of rows.
- Highlight NULL values yellow in the query results.
- Show what version of the FileMaker ODBC driver is installed.
- Option to manually specify a driver name and connection string (you can use any installed 64bit ODBC driver).
Acknowledgements
- Icon by David Vignoni, GNU Lesser General Public License (LGPL).
- Tab control code adapted from MDI tab control by Eduardo Oliveira, The Code Project Open License (CPOL).
- CsvHelper, Apache License, Version 2.0.
- Json.NET, MIT license.
- FastColoredTextBox, GNU Lesser General Public License (LGPLv3).