It all starts with setting up a DSN (Data Source Name) on your OS using the right driver. Are you on Mac or Windows. If on Windows, what version.
Also, do you intend to use this from the individual FM clients for an ODBC import / Execute SQL script step or use the ESS feature?
My experience setting up an FMP-to-SQL via ODBC was cobbled together by a combination of official step-by-steps at mysql.com, various postings, and a certain amount of trial and error. In other words, no one source proved particularly authoritative.
Here's what I did to get it working for a MySQL connection, FMP on mac/unix:
You need a driver: eg libmyodbc5a.so from MySQL :: Download Connector/ODBC (Requires a free account with Oracle)
Goes in folder /usr/local/lib/
Set it's permissions via Terminal as follows: sudo chown root:admin '/usr/local/lib/libmyodbc5a.so'
Get an ODBC Utility for Mac
There are two I'm aware of: ODBC Administrator.app or ODBC Manager.app. Mysteriously the former didn't work. Only the latter (ODBC Manager.app) did. Free from Mac OS X: ODBC Manager
Launch ODBC Utility
Launch the app.
Under "Drivers" tab the driver either appears automatically, or you have to add it. If you have to add it, point to /usr/local/lib/libmyodbc5a.so
Under the "System DNS" tab, you'll need to add an entry for each SQL database you want to connect to, so "Add..." button
You'll get a window prompting you to choose a driver. You should be able to choose the driver you just installed.
Next window pops up:
Under "Data Source Name (DSN)": [Decide on a name for this System DSN, typically the SQL Database, but it doesn't have to be spelled perfectly]
Description: [Your choice]
Note there's another "Add" button at the bottom of the window. Use it to create 4 Keyword/Value pairs:
- Database/[Actual name of your SQL database, spelled accurately]
- Server/[ip address of web server hosting SQL database]
- User/[Database User credential you use to, say, log on to phpMyAdmin or access via PHP]
- Port/[Typically 3306. If not, ask your hosting provider]
You can, but probably shouldn't include a Password key/value pair. Security risk.
Extra credit: Check out the folder /Library/ODBC/ Two files in there are what the ODBC Utility is effectively managing. Supposedly you can write these yourself, and avoid ODBC Utility altogether, but I had no luck doing so.
Create and/or launch an FMP DB you intend to connect to your MySQL DB
Menu > File > Manage > External Data Sources... > New... > ODBC > Specify...
Subsequent window should list any and all SQL databases you established in your ODBC Utility. Choose one. "Ok" that
Back in "Edit Data Sources"
- Name it, typically to match the choice you just selected.
- Read over the Authentication and Filter Tables ares of the "Edit Data Sources". They're somewhat self explanatory.
- "OK" out of "Edit Data Sources" window.
Menu > File > Manage > Database... In the Relationships grid the Add an Table Occurrence from Data Source: > Select ODBC Data Source
The name you established in External Data Sources should be available. Select it. If you haven't entered a password in any prior steps, you'll have to enter your SQL database credentials now.
Hope that helps. Took forever to figure out.
Now I have used Microsoft SQL Sever.
And I can't see any list from Menu > File > Manage > External Data Sources... > New... > ODBC > Specify...
What about the problem? or I should use MySQL by following you Mr.databoom?
I have intend to use ESS feature.
I'd suggest MySQL only because a) It's free and available from most Hosting services, b) That's the one I successfully set up using the above steps.
There's no reason it shouldn't work for any SQL database and your screen grabs seem promising, but because you're on MS SQL + a PC/Windows-basd FMP, I can imagine some of the details might be slightly different.
Your screen grabs aren't immediately showing whether or not under "System DSN" you added the 4 key/value pairs (database, server, user, and port) for the database you're trying to connect with. If not, it could explain why you're not getting the list under External Data Sources... > ODBC > Specify...
You are using very old driver. What version of SQL Server and FileMaker do you use?
And, if your file is hosted, you need to setup DSN on host for ESS.
Someone please correct me if I'm wrong - I played around with this over a year ago on my work computer, and I'm at home now, so I can't check what exactly I did....
Another thing to be considered is whether your version of Filemaker is 32 or 64 bit - FMP 13 was (on Windows) 32 bit, which meant that I needed to use a 32 bit ODBC driver. (This link goes to another thread that helped me when I was trying to set things up.) I also needed to install the FMP ODBC files from the FMP installer, and I needed to manage the ODBC connections using odbcad32.exe (that's in the Windows/System32 folder). Once I started using 32 bit drivers, my frustration level went way down.
Sorry I can't give the type of detail that databoom did.
MS SQL Server 2008 R
FileMaker Pro Adv 13.0v1
I have installed couple in my laptop.
What should I do?
As mentioned before:
- install the supported MS SQL Server driver that ESS needs for your version of FMP/FMS
- if your Windows is 64-bit, make sure to set up the DSN in the 32-bit ODBC control panel, not the 64-bit one if you are using FMP12 or 13.
If you have a FileMaker Server, then install the driver and DSN on the FMS box, not the client.
Now I have already done !
I have done.