Are you able to successfully configure a system DSN that accesses RedShift on your filemaker server? That's the first step. From that point it's pretty much the same thing to access that data from filemaker regardless of the platform you are using. However only specific data sources are supported via ESS for placement in filemaker's relationship graph, and redshift isn't one of them. That means you may have to use one of the other methods of accessing ODBC in filemaker, such as the Execute SQL script step.
RedShift has both 32 and 64 bit ODBC drivers. You didn't mention what OS or bit version of filemaker you are running.
Having filemaker pro installed on the same server hosting filemaker makes troubleshooting and setting up ODBC connections a lot simpler.
Thanks, Mike. My strategy had been to establish the connection first on a client computer running FileMaker Pro 14 Advanced. Then take my learning there and move it to the FileMaker Server 14 box. I don't have Advanced running on the Server box (per FileMaker's recommendation) -- thus the reason for the strategy.
I'm running both the Server box and the client boxes using Mac OS X 10.11.2 (El Capitan).
The fact that I can't place a Redshift file on the relationship graph is disappointing, of course. That would have been easier. However, I can probably use the Execute SQL script step (but I'll have to bone up on my SQL -- it's been a while).
Thanks for the advice. If I get stumped, I'll post another response here.
I'd recommend getting an ODBC manager, this one is standard:
Then from the redshift documentation, you will need the OSX driver for ODBC:
You will install the driver according to the directions there.
After that, you need to create a DSN, what your system uses to store the connection details to your redshift database. Follow the redshift documentation for setting that up:
The ODBC manager should have a test button that allows you to test your DSN connection to redshift successfully.
Once you have established a successful DSN, THEN you can start connecting to that DSN as a data source from FileMaker.
However, I can probably use the Execute SQL script step (but I'll have to bone up on my SQL -- it's been a while).
Keep in mind that there are two relevant script steps to use:
- Execute SQL is for pushing data into the other database (can't use it to retrieve data)
- Import (from ODBC source) is for pulling data into FM
Thanks, Mike. I had found most of that documentation, but had missed the crucial first step, i.e. getting the Redshift driver into the ODBC manager. I'm not there yet, but continue to feel like I'm making progress.
I appreciate the advice.
Thanks, Wim. Good reminder. In this particular app, I'll be doing 100% read only, so I'll use the Import command. That will actually mean fewer changes to my system. Right now I'm using the Import command on data I download via SFTP, so my sense is that the flow of the system won't change all that much once I get it connected to Redshift.
Just to update everyone on the Redshift integration into FileMaker saga:
The standard ODBC Manager most Mac users use is not supported by AWS Redshift.
One must use, instead, iODBC Administrator (from iodbc.org). However, iODBC Administrator crashes with El Capitan.
The workaround is to manually place the configuration files (there are three: amazon.redshiftodbc.ini, odbcinst.ini, and odbc.ini) in locations not documented by AWS Redshift (~/Library/ODBC/ and ~/user/<username>/Library/ODBC/). The files must also be manually edited -- iODBC Administrator will mess up the files and crash if you try to use its user interface. (I had to buy AWS support to find all of this out, although the folks that have been helping have worked hard to figure all this out.)
I have finally been able to successfully connect to Redshift via iODBC Administrator. I got a successful test message.
However, I am now trying to attach via FileMaker and am coming up empty. I get a message from FileMaker that this ODBC data source is not supported. As wimdecorte notes above, this is not unexpected (although disappointing). So now I'll be working on using the Import command to see what I wan work out.