One of my very favorite database libraries is SQLAPI++ because it's very well designed, works with a huge number of databases, is thread-safe, and doesn't require you to link in the underlying database libraries when you build your code. It's nice. So when it came time to hit a database from linux here at The Shop, I naturally turned to an old friend. But there were some ugly truths lurking there for me, and I had to spend quite a bit of time getting things sorted out.
First, off, SQLAPI++ on linux doesn't talk to MS SQL Server through any libraries like FreeTDS. Nope, you have to go through iODBC. Ick. Thankfully, FreeTDS has the ability to work with iODBC, but getting things set up and tested was a pain, so here's what I had to do.
Get everything installed:
then start to configure things.
Get FreeTDS going by editing /etc/freetds/freetds.conf to look something like this:
[devSQL]
host = dbhost
port = 1433
tds version = 7.0
Now you can put in the default .sqshrc and prove that you can talk to the database with sqsh:
$ sqsh -S devSQL -U me -P secret
1>
Success. That's a good first step. Now let's configure ODBC. Edit /etc/odbc.ini to look something like:
[ODBC Data Sources]
devSQL = Devel SQL Database
[devSQL]
Description = Development database
Driver = /usr/lib/odbc/libtdsodbc.so
Trace = No
Server = dbhost.yoyo.net
Database = master
Port = 1433
TDS_Version = 8.0
and then edit /etc/odbcinit.ini to look something like this:
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout =
CPReuse =
At this point, we can run the iODBC admin utility to see that it was set up properly:
$ iodbcadm-gtk &
and the driver should be right there. Use this to "Test" the connection, and you should see that it's there and working.
Finally, use the SQLAPI++ test client to see that everything is working there as well:
$ test64
1. Oracle
2. SQL Server
3. DB2
4. Informix
5. Sybase
6. InterBase
7. SQLBase
8. MySQL
9. PostrgeSQL
0. ODBC
0
Client version: unknown before connection
Database name (connection string): devSQL
User name: me
Password: secret
Server: Microsoft SQL Server Release 10.00.2714
Server version: 10.0
Client version: 0.82
$