How To Define A Driver For Tclodbc?

I want to use tclodbc from Linux environment to connect to a MS SQL server. I have the driver (freeTDS) and the connection string. But I don't know how to configure the driver to b

Solution 1:

OK, here's my take based on theseguides about how to make a DSN-less connection using the FreeTDS driver.

I've tested it a Debian Lenny system having tclodbc 2.5-5, unixodbc 2.2.11 and libdbd-freetds 0.8.2-1-4.1 and tcl 8.4.16-2 installed against an instance of Microsoft SQL Server 2005.

packagerequire tclodbc

proc cs_append {varName args} {
    set alen [llength $args]
    if {$alen < 2 || $alen % 2 != 0} {
        return -code error "Wrong # args: should be varName key value ?key value?"
    }

    upvar 1 $varName qs

    foreach {key value} $args {
        if {$qs ne""} {
            append qs \;
        }
        append qs $key = \{ [string map {\{ \\\{} $value] \}
    }
}

set user test
set pass secret

set cs ""
cs_append cs DRIVER FreeTDS UID $user PWD $pass \
    Server myserver.domain.local \
    ClientCharset UTF-8 \
    APP "My test app"

database connect db $cs
foreach row [db {select * from MyDatabase..MyTable}] {
    puts $row
}
db disconnect

Some notes:

• The FreeTDS driver must be known to the ODBC subsystem by means of it being registeded in the /etc/odbcinst.ini file. I suppose that at least on my system appropriate packages take care of this by themselves but you'd better verify if you have FreeTDS registered in that file, otherwise that DRIVER=FreeTDS bit in the connection string won't work as ODBC will have no idea how to load the named driver library.

• The ClientCharset and APP connection string parameters do not work in my case. While I can live with the second, the first one sucks big time because in this case the character data is returned in some botched encoding.

  But there's no such problem when I use named server from the /etc/freetds/freetds.conf file using the ServerName=THAT_SERVER instead of Server=SERVER_HOST in the connection string. Unfortunately, this kind of defeats half of the purpose of using DSN-less setup.

  Quite possibly it's a bug in my version of the FreeTDS driver, and I have a really outdated system here, so YMMV and you better check yourself on your system.

Solution 2:

If we look at the documentation, we see that there are 6 operations, of which the one you probably want is add_dsn. An example is included (below, with a small correction):

set driver "Microsoft Access Driver (*.mdb)"set attributes [list "DSN=mydsn""DBQ=c:\mydb.mdb""FIL=MS Access"] 
database configure add_dsn $driver$attributes

I'm afraid you'll have to consult the FreeTDS documentation to get the right collection of attributes, but I think (based on this evidence) that you'll have the driver being FreeTDS and the attributes might be OK if it is an empty list (or with just TDS_Version=5.0 in it). I really don't know too much about configuring ODBC…