0.5.3 and mssql

[Lukasz Szybalski]

Hello,

Is this still proper connection string for mssql where I specify the
tds version and a connection driver name?

e = sqlalchemy.create_engine("mssql://user:pass@server:1433/db_name?driver=TDS&odbc_options='TDS_Version=8.0'")

What is the syntax for driver, and how can I pass "TDS_Version=8.0" to
the end of connection string?

Thanks,
Lucas

File "/home/lucas/tmp/saENV/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/pool.py",
line 198, in __init__
self.connection = self.__connect()
File "/home/lucas/tmp/saENV/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/pool.py",
line 261, in __connect
connection = self.__pool._creator()
File "/home/lucas/tmp/saENV/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/engine/strategies.py",
line 80, in connect
raise exc.DBAPIError.instance(None, None, e)
sqlalchemy.exc.DBAPIError: (Error) ('08001', '[08001]
[unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0)
(SQLDriverConnectW)') None None

--
How to create python package?
http://lucasmanual.com/mywiki/PythonPaste
DataHub - create a package that gets, parses, loads, visualizes data
http://lucasmanual.com/mywiki/DataHub

[Lukasz Szybalski]

On Tue, Apr 7, 2009 at 2:58 PM, Lukasz Szybalski <szyb...@gmail.com> wrote:
> Hello,
>
> Is this still proper connection string for mssql where I specify the
> tds version and a connection driver name?
>
>  e = sqlalchemy.create_engine("mssql://user:pass@server:1433/db_name?driver=TDS&odbc_options='TDS_Version=8.0'")
>

1. Just to let you know... the dsn connection works as long as
"TDS_Version = 8.0" is provided in the dsn settings of freetds for
mssql 2000.

2. I went over your sampeles at :
http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/lib/sqlalchemy/databases/mssql.py

and using the mssql:///?odbc_connect=dsn%3Dmydsn%3BDatabase%3Ddb
I was able to get proper dns string.

The syntax is weird because the code needs to look like this and needs
to use "%s" to substitute the string. I don't know if you run into
unicode problems or escaping issues. Is there a better way to do this?

import urllib
url= urllib.quote_plus('DRIVER={TDS};Server=servername;Database=dbname;UID=user;PWD=pass;port=123;TDS_Version=8.0;')
e = sqlalchemy.create_engine("mssql:///?odbc_connect=%s" % url)

I wish you guys did this in a code so I could only provide something like this:
e = sqlalchemy.create_engine("mssql:///?odbc_connect='DRIVER={TDS};Server=servername;Database=dbname;UID=user;PWD=pass;port=123;TDS_Version=8.0;''")

Notice the single quotes after odbc_connect='
If not could you copy above 3 lines of code I've put as an example and
add it to your docs for mssql. I would never know how to get here
without being subscribed to this list and know about the changes and
know to look at the source code file.

3. With dsn-less connection there seems to be some kind of issue with
pyodbc, but I can't figure out what the problem is.

import pyodbc
pyodbc.connect('DRIVER={TDS};Server=servername;Database=dbname;UID=user;PWD=pass;port=123;')

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
pyodbc.Error: ('08S01', '[08S01] [unixODBC][FreeTDS][SQL Server]Unable
to connect: Adaptive Server is unavailable or does not exist (20009)
(SQLDriverConnectW)')

Anybody might know how to solve this?

Thanks,
Lucas

[mdoar]

This is what's working for me with 0.5.3

raw_cs = "SERVER=%s;DATABASE=%s;UID=%s;PWD=%s" % (server, database,
userid, password)
connection_string = "%s:///?odbc_connect=%s" % (databasetype,
urllib.quote_plus(raw_cs))
if databasetype in ['mssql']:
connection_string += urllib.quote_plus(";DRIVER={SQL
Server};TDS_Version=8.0")

echoOn = True # For debugging the SQL statements
engine = create_engine(connection_string, echo=echoOn)


If TDSVER is not seen and the default version is used, you may get
SystemError: 'finally' pops bad exception

Now that said I have also had to force the version with

./configure --with-tdsver=8.0
make
sudo make install

or

export TDSVER=8.0

or if you can't redeploy freetds copy freetds.conf to ~/.freetds.conf
and change the version in that file to 8.0

FreeTS, ODBC, pyODBC and then SQLAlchemy: what a house of cards!

~Matt

On Apr 7, 12:58 pm, Lukasz Szybalski <szybal...@gmail.com> wrote:
> Hello,
>
> Is this still proper connection string for mssql where I specify the
> tds version and a connection driver name?
>
>  e = sqlalchemy.create_engine("mssql://user:pass@server:1433/db_name?driver=TDS&odbc_options='TDS_Version=8.0'")
>
> What is the syntax for driver, and how can I pass "TDS_Version=8.0" to
> the end of connection string?
>
> Thanks,
> Lucas
>
> File "/home/lucas/tmp/saENV/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/pool.py",
> line 198, in __init__
>     self.connection = self.__connect()
>   File "/home/lucas/tmp/saENV/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/pool.py",
> line 261, in __connect
>     connection = self.__pool._creator()
>   File "/home/lucas/tmp/saENV/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/engine/strategies.py",
> line 80, in connect
>     raise exc.DBAPIError.instance(None, None, e)
> sqlalchemy.exc.DBAPIError: (Error) ('08001', '[08001]
> [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0)
> (SQLDriverConnectW)') None None
>
> --

> How to create python package?http://lucasmanual.com/mywiki/PythonPaste

[Lukasz Szybalski]

On Wed, Apr 8, 2009 at 11:36 PM, mdoar <md...@pobox.com> wrote:
>
> This is what's working for me with 0.5.3
>
> raw_cs = "SERVER=%s;DATABASE=%s;UID=%s;PWD=%s" % (server, database,
> userid, password)
> connection_string = "%s:///?odbc_connect=%s" % (databasetype,
> urllib.quote_plus(raw_cs))
> if databasetype in ['mssql']:
>    connection_string += urllib.quote_plus(";DRIVER={SQL
> Server};TDS_Version=8.0")
>
> echoOn = True  # For debugging the SQL statements
> engine = create_engine(connection_string, echo=echoOn)
>

What version of pyodbc are you using?

Thanks,
Lucas

[mdoar]

On Apr 9, 8:20 am, Lukasz Szybalski <szybal...@gmail.com> wrote:
> On Wed, Apr 8, 2009 at 11:36 PM, mdoar <md...@pobox.com> wrote:
>
> > This is what's working for me with 0.5.3
>
> > raw_cs = "SERVER=%s;DATABASE=%s;UID=%s;PWD=%s" % (server, database,
> > userid, password)
> > connection_string = "%s:///?odbc_connect=%s" % (databasetype,
> > urllib.quote_plus(raw_cs))
> > if databasetype in ['mssql']:
> >    connection_string += urllib.quote_plus(";DRIVER={SQL
> > Server};TDS_Version=8.0")
>
> > echoOn = True  # For debugging the SQL statements
> > engine = create_engine(connection_string, echo=echoOn)
>
> What version of pyodbc are you using?
>

Latest and greatest stable

~Matt