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 dB.rstudio.com 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,
Drew