Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Having Troubles with UnixODBC, FreeTDS, and PyODBC

I am having great difficulty getting all three of these to work together in harmony. I guess I'll list all the various configurations, along with the test code to see if a pair of fresh eyes can realize my stupidity.

I'm running 12.04 Ubuntu Server and I'm trying to connect to a MSSQL Server 2008 and end up using it with PyODBC.

However, when just putting in

tsql -S T2 -U Foo -P Bar

I get the

1>
2>
3>
4>
5>
6>
7>
8>
9>
10>
11>

and etc.

Anyway, if anyone would be able to help (and I would be eternally grateful if you can clear me of this haze), here are my current configurations.

This is my /etc/odbc.ini

[ODBC Data Sources]
odbcname     = MySQL
T2           = MSSQL

[odbcname]
Driver       = /usr/lib/odbc/libmyodbc.so
Description  = MyODBC 3.51 Driver DSN
SERVER       = Foobar
PORT         = 3306
USER         = Foo
Password     = Bar
Database     = Foobar
OPTION       = 3
SOCKET       =

[T2]
Driver       = FreeTDS
Description  = ODBC connection via FreeTDS
SERVER       = FOOBAR
PORT         = 1433
USER         = Foo
Password     = Bar
Database     = Foobar
OPTION       = 3
SOCKET       =

[Default]
Driver       = /usr/local/lib/libmyodbc3.so
Description  = MyODBC 3.51 Driver DSN
SERVER       = FOOBAR
PORT         = 3306
USER         = foo
Password     = bar
Database     = FOOBAR
OPTION       = 3
SOCKET       =

The following is my /etc/odbcinst.ini

[FreeTDS]
Description=FreeTDS Driver
Driver=/usr/lib/odbc/libtdsodbc.so
Setup=/usr/lib/odbc/libtdsS.so
CPTimeout=
CPReuse=
FileUsage=1

The following is my freetds.conf

# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".

# Global settings are overridden by those in a database
# server specific section
[global]
        # TDS protocol version
;       tds version = 4.2

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
;       dump file = /tmp/freetds.log
;       debug flags = 0xffff

        # Command and connection timeouts
;       timeout = 10
;       connect timeout = 10

        # If you get out-of-memory errors, it may mean that your client
        # is trying to allocate a huge buffer for a TEXT field.
        # Try setting 'text size' to a more reasonable limit
        #text size = 64512

[T2]
        host = FOOBAR
        port = 1433
        tds version = 7.0
        client charset = UTF-8
        text size = 20971520
[global]
        # TDS protocol version
        tds version = 7.0

And my Python test file just for good measure

import pyodbc
import sys 

try:
    #tempsystrends = pyodbc.connect('DRIVER=FreeTDS;SERVER=FOOBAR;PORT=1433;DATABASE=T2;UID=FOO;PWD=bar;TDS_Version=7.0;')
    cursor = tempsystrends.cursor()
except pyodbc.Error as e:
        print "Error: %s" % (e.args[1])
        sys.exit(1)
like image 385
Anthony Tantillo Avatar asked Jun 04 '13 19:06

Anthony Tantillo


People also ask

What is Pyodbc driver?

Pyodbc is an open source Python module that makes accessing ODBC databases simple. It implements the DB API 2.0 specification. Using pyodbc, you can easily connect Python applications to data sources with an ODBC driver.

What does Pyodbc connect return?

Returns a new Cursor Object using the connection. pyodbc supports multiple cursors per connection but your database may not.


1 Answers

I connect to various databases via PHP using UnixODBC, here is my configuration for a Microsoft SQL Server:

/etc/odbc.ini

# Define a connection to a Microsoft SQL server
# The Description can be whatever we want it to be.
# The Driver value must match what we have defined in /etc/odbcinst.ini
# The Database name must be the name of the database this connection will connect to.
# The ServerName is the name we defined in /etc/freetds/freetds.conf
# The TDS_Version should match what we defined in /etc/freetds/freetds.conf
[mssql]
Description             = MSSQL Server
Driver                  = freetds
Database                = XXXXXX
ServerName              = MSSQL
TDS_Version             = 8.0

/etc/odbcinst.ini

# Define where to find the driver for the Free TDS connections.
[freetds]
Description     = MS SQL database access with Free TDS
Driver          = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
Setup           = /usr/lib/i386-linux-gnu/odbc/libtdsS.so
UsageCount      = 1

/etc/freetds/freetds.conf

# The basics for defining a DSN (Data Source Name)
# [data_source_name]
#       host = <hostname or IP address>
#       port = <port number to connect to - probably 1433>
#       tds version = <TDS version to use - probably 8.0>

# Define a connection to the Microsoft SQL Server
[mssql]
        host = XXXXXX
        port = 1433
        tds version = 8.0

Then test your connection:

isql mssql username password

Depending on your environment your username might have to be in the format: domain\username

After issuing the command you should see something like:

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
like image 142
Benny Hill Avatar answered Oct 12 '22 13:10

Benny Hill