Setting up ODBC on Mac - idiot’s guide wanted!

Hi all,

This conversation moved over from Twitter where Hadley and Edgar Ruiz were kindly participating but the character limit is insufficient!

Basically I have a database of >1M records and 400 columns that sits on a remote SQL Express Server. It contains patient data so it is well-protected. I am steadily replacing a manual data analysis process for a national audit where this was exported to CSV and run through Stata with some VBA macro glue to make outputs.

Having developed the toolset using CSV - replacing the Stata and VBA code I’m ready to start the next phase of removing the CSV file and talking to the database. I use RStudio on the Mac.

My development plan was to use SQLite via ODBC to test the whole ‘talking to a database’ thing with the code (to check my code is equally happy - and efficient - using a database compared to CSV), before then switching it to use the real data (which may involve some firewall config etc - so having it ‘known working locally’ makes sense to me). SQLite only chosen because it is present on a Mac by default - no commitment at this stage.

On first opening in RStudio preview 1.2 the ODBC option was present in ‘open connection’. However on clicking it it disappeared - never to be seen again.

I’ve so far followed the brew install bits for ODBC from the pages. I suspect however I am not getting the .ini file configuration right.

I am using Mojave which may not help matters (access settings etc were changed so instructions may be out of date or read/write areas now locked down).

Does anyone have any idiot-proof ways to set up ‘basic’ open source ODBC drivers for the Mac?

Best wishes,


1 Like

So - followup to my own post ( apologies).

I've been on the Microsoft site and followed the links for the SQLExpress driver. Appears I've successfully installed the ODBC Driver - that is listed as ODBC Driver 13 for SQL Server in Connections. However, no joy with the other drivers. As above - whilst that's helpful that I might be able to connect to SQL Server to help towards the eventual goal, it's not helpful in that setting up an SQL Server to test with is not really an option for me from the Mac...

And a third followup:

Having installed the SQL Server ODBC Driver, I've found I could theoretically install SQL Server using Docker.

Problem: The MacBook Pro I want to do the majority of testing on is running on 8GB of RAM; Docker wants 3.5-4GB for SQL Server...

So I think the 'local testing with SQLite' option still looks preferable.

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.