What's it all about, Alfie?

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:

• iodbc
• libiodbc2
• sqsh

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
  $

This entry was posted on Thursday, March 24th, 2011 at 1:01 pm and is filed under Coding, Open Source Software. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.