Re: mssql, Linux, unixODBC - Could not locate column in row for column

[Rick Morrison]

> you can mix both freely.  any class that has max_identifier_length on it, if you set self.max_identifier_length, that overrides it.

Oh ok, nice.

Alright, this is in trunk r4429 as a keyword parameter named "max_identifier_length"

Lukasz: to use it, add the "max_identifier_length" as a keyword to create_engine() or as a db-uri keyword. Should be set to 30 for now.


Rick

On Wed, Apr 2, 2008 at 6:51 PM, Michael Bayer <zzz...@gmail.com> wrote:

On Apr 2, 2008, at 6:46 PM, Rick Morrison wrote:

you're already hitting some limit of 30 for your cursor.description.   This is a pyodbc debugging issue.  A fix for now would be to change the max identifier length in MSSQL_pyodbc to 30 - perhaps we need to add this as a configurational option somehow for MS-SQL ?

That would be a lot easier if the max_identifier_length was an instance attribute instead of a class attribute on the Dialect. Anybody using multiple connections is going to be hamstrung to the smaller limit. Any ideas around that?

[Lukasz Szybalski]

On Wed, Apr 2, 2008 at 6:06 PM, Rick Morrison <rickmo...@gmail.com> wrote:
> > you can mix both freely. any class that has max_identifier_length on it,
> if you set self.max_identifier_length, that overrides it.
>
> Oh ok, nice.
>
> Alright, this is in trunk r4429 as a keyword parameter named
> "max_identifier_length"

Like:
sqlalchemy.create_engine('mssql://xxx:xxx@xxxx:1433/xxxxx?driver=TDS',connect_args
= {'max_identifier_length':30})
or
sqlalchemy.create_engine('mssql://xxx:xxx@xxxx:1433/xxxxx?driver=TDS',max_identifier_length=30)

[Michael Bayer]

On Apr 2, 2008, at 7:06 PM, Rick Morrison wrote:

> > you can mix both freely. any class that has max_identifier_length
> on it, if you set self.max_identifier_length, that overrides it.
>
> Oh ok, nice.
>
> Alright, this is in trunk r4429 as a keyword parameter named
> "max_identifier_length"
>
> Lukasz: to use it, add the "max_identifier_length" as a keyword to
> create_engine() or as a db-uri keyword. Should be set to 30 for now.

also of concern is that, nobodys ever going to know they need to use
this parameter when this issue arises. its only because I narrowed
the issue down to where I knew we needed those names to be that it was
identified.

[Rick Morrison]

> also of concern is that, nobodys ever going to know they need to use this parameter when this issue arises. 

Well the idea is that this is a workaround for what I suspect is a broken Unix + pyodbc configuration, not a long-term solution.

its only because I narrowed the issue down to where I knew we needed those names to be that it was identified.

> hey not to be impolite, but I burned a little oil on this one too, ya know.

[Lukasz Szybalski]

On Wed, Apr 2, 2008 at 6:22 PM, Rick Morrison <rickmo...@gmail.com> wrote:
> > also of concern is that, nobodys ever going to know they need to use this
> parameter when this issue arises.
>
> Well the idea is that this is a workaround for what I suspect is a broken
> Unix + pyodbc configuration, not a long-term solution.
>
>
>
> > its only because I narrowed the issue down to where I knew we needed those
> names to be that it was identified.

So how would I find out if this is pyodbc or unixodbc?

[Rick Morrison]

> So how would I find out if this is pyodbc or unixodbc?

On Unix, it's both.

pyodbc is the Python DB-API module that provides the DB-API2 interface for sqlalchemy it in turn relies on an underlying ODBC layer.

unixodbc is the ODBC-for-Unix implementation that provides an ODBC interface (the ODBC layer) and manages various database drivers. There is an alternative to unixodbc called iodbc that does pretty much the same thing.

Settings for things like identifier length, the appropriate driver to use, etc. are going to be in the domain of the ODBC implementation, e.g. unixodbc. You'll most likely find them in a file like /etc/odbc.conf , and documented in the unixodbc docs.

[Michael Bayer]

On Apr 2, 2008, at 7:22 PM, Rick Morrison wrote:

>
> > hey not to be impolite, but I burned a little oil on this one too,
> ya know.
>

absolutely ! oil burned all around.

[Lukasz Szybalski]

Does the size of 30 apply to pyodbc on windows when connection to mssql?
If not then its probably unixodbc, or vice versa.

Lucas

--
Automotive Recall Database. Cars, Trucks, etc.
http://www.lucasmanual.com/recall/
Install Broadcom wireless card on Linux:
http://lucasmanual.com/mywiki/bcm43xx

[Michael Bayer]

I hope we're putting a note on "Database Notes" with this bug...use
the blink tag

[Lukasz Szybalski]

On Wed, Apr 2, 2008 at 4:24 PM, Michael Bayer <zzz...@gmail.com> wrote:
> Gottit. Notice, pyodbc is truncating all the column names to 30 characters.
> anyway, thats why its broken. Is this a bug in pyodbc ? I thought MS-SQL
> has a much bigger limit than this ?

I don't know if I mentioned it but using max identifier length allows
me to get my data... from svn code (4430)
Thanks.

sqlalchemy.create_engine('mssql://xxx:xxx@xxxx:1433/xxxxx?driver=TDS&max_identifier_length=30')

I will play around with pyodbc and see where if the limit is coming from there.

Lucas

[Lukasz Szybalski]

FYI.
http://www.freetds.org/userguide/choosingtdsprotocol.htm

On unix freeTDS is supporting various TDS protocols.
Current default is 4.2

Sybase before System 10, Microsoft SQL Server 6.x;
4.2
Still works with all products, subject to its limitations.

The limitation here I guess is 30 char identifier limit but I will
need to test it.

In order to use more characters the FreeTDS should be configured to
use TDS protocol 7.0 which:
Includes support for the extended datatypes in SQL Server 7.0 (such as
char/varchar fields of more than 255 characters), and support for
Unicode.

In freetds.conf the following section should be changed from:
[global]
# TDS protocol version
tds version = 4.2
to
tds version = 7.0

Version 4.2 has limitation such as:
TDS 4.2 has limitations
ASCII only, of course.
RPC is not supported.
BCP is not supported.
varchar fields are limited to 255 characters. If your table
defines longer fields, they'll be truncated.
dynamic queries (also called prepared statements) are not supported.

I'll try to test it tomorrow and see if I don't have to use max identifier.
Lucas

[Rick Morrison]

> The limitation here I guess is 30 char identifier limit but I will
> need to test it.

Ah yeah, you're going to have bigger problems than 30 char identifiers with the Sybase TDS settings. MSSQL uses a different set of wire-protocol representations of datetime types, and your dates are going to be off.

> In order to use more characters the FreeTDS should be configured to
> use TDS protocol 7.0 which:

Even that one is old: MSSQL switched to TDSv7 back in MSSQL 7.0 (circa 1997)
They're now on TDSv8 as of MSSQL-2000

Here's a chart:

http://www.freetds.org/tds.html

It beats me why FreeTDS still defaults to Sybase instead of MSSQL, the installed base has to run at least 100 to 1 in favor of MSSQL. Oh well.

When you get all this set up correctly, you may want to update the sqlalchemy wiki with all this stuff.

[Lukasz Szybalski]

What I have found out is that in dsn less connection is not going
through the freetds.conf.
So me settings things there has no point.

So the way we passed DRIVER={TDS} I would also have to pass TDS_Version

cnxn = pyodbc.connect("SERVER=xxx;UID=xxx;PWD=xxx;DRIVER={TDS};TDS_Version=7.0")

this actually returns results that are longer then 30.
Lucas

[Lukasz Szybalski]

On Thu, Apr 17, 2008 at 11:09 AM, Rick Morrison <rickmo...@gmail.com> wrote:

> Two points:
>
> - Unless you're running Sybase, it's possible to set the default TDS version
> in the TDS config file, which would eliminate the need for this.
>
> - The end point of all of these parms is to simply build an ODBC connection
> string. Wouldn't a parm that just allowed the specification of the string
> as-is be just as useful but a lot more flexible/simpler?
>

Here are the options as specified by free TDS. What you are talking
about is setting it in conf file which is used only for dsn
connection.
pyodbc takes this TDS_Version parameter with no problems.

here is what freetds said:
to use TDS 7.0 in a DSN-less connection, your options are:

1. Rebuild FreeTDS --with-tdsver=7.0, or
2. Set the environment variable TDSVER=7.0 before starting Python, or
3. Add "TDS_Version=7.0;" to your connection string.
4. Use "Servername" in your connection string.

options 3 seems the easies to me.
Let me know
Lucas

[Rick Morrison]

Here are the options as specified by free TDS. What you are talking
about is setting it in conf file which is used only for dsn
connection.

No, I meant as the *default* TDS version here. See here:

   http://www.freetds.org/userguide/freetdsconf.htm

I'm talking about the [global] setting, which is the default used unless overridden in a different [dataserver] config.

 

here is what freetds said:
to use TDS 7.0 in a DSN-less connection, your options are:

1.  Rebuild FreeTDS --with-tdsver=7.0, or
2.  Set the environment variable TDSVER=7.0 before starting Python, or
3.  Add "TDS_Version=7.0;" to your connection string.
4.  Use "Servername" in your connection string.

or 5. Change the default version to the desired version

options 3 seems the easies to me.

OK. I'm just suggesting that if you'd rather specify the version in the dburi instead of changing it on the server, that we allow the specification of the ODBC connect string directly, rather than provide a bunch of separate parameters that are in turn only used to build an ODBC connection string anyway.

Finally, as I mentioned in an earlier thread, you should most likely be using TDS version 8.0, not 7.0. 

7.0 is for SQL Server 7,
8.0 is for SQL Server 2000 and 2005

Rick

[Lukasz Szybalski]

On Thu, Apr 17, 2008 at 12:14 PM, Rick Morrison <rickmo...@gmail.com> wrote:
>
>
>
> > Here are the options as specified by free TDS. What you are talking
> > about is setting it in conf file which is used only for dsn
> > connection.
>
> No, I meant as the *default* TDS version here. See here:
>
> http://www.freetds.org/userguide/freetdsconf.htm

The way I read the comment from freetds

http://lists.ibiblio.org/pipermail/freetds/2008q2/023126.html

connection string:

cnxn = pyodbc.connect("SERVER=xxx;UID=xxx;PWD=xxx;DRIVER={TDS};TDS_Version=7.0")

Connecting this way does not read the freetds.conf, it goes directly
to the driver.

>
> I'm talking about the [global] setting, which is the default used unless
> overridden in a different [dataserver] config.
>

I have changed it in the global settings and it made no difference. It
was only when I emailed them I have found out that the way we connect
is not using freetds.conf. So in order to get the higher version I
would have to recompile freetds.

> > here is what freetds said:
> > to use TDS 7.0 in a DSN-less connection, your options are:
> >
> > 1. Rebuild FreeTDS --with-tdsver=7.0, or
> > 2. Set the environment variable TDSVER=7.0 before starting Python, or
> > 3. Add "TDS_Version=7.0;" to your connection string.
> > 4. Use "Servername" in your connection string.
> >
>
> or 5. Change the default version to the desired version

Not if you use direct connection. It would if we used ?dsn=....

> >
>
> OK. I'm just suggesting that if you'd rather specify the version in the
> dburi instead of changing it on the server, that we allow the specification
> of the ODBC connect string directly, rather than provide a bunch of separate
> parameters that are in turn only used to build an ODBC connection string
> anyway.

I don't see an easy option to change it. If it was as easy as changing
freetds.conf then I would do it, but its not.

>
> Finally, as I mentioned in an earlier thread, you should most likely be
> using TDS version 8.0, not 7.0.

Thanks,
I'll use 8.0

Lucas

[Rick Morrison]

ok, ok, assuming that dsn-less connections actually do ignore the .conf file and require all that stuff to be specified.

here's the question that I'm trying to ask:

    instead of  something like this:
          create_engine('mssql://user:pwd@server/database', odbc_driver='TDS', odbc_autotranslate='No', odbc_tds_ver='8.0')

   how about this:
          create_engine('mssql://user:pwd@server/database', odbc_connect='DRIVER=TDS; TDS_Version=8.0; OtherODBCStuff=goes here')


do you see the difference? Instead of providing a hundred and one ODBC-specific keyword options, we just allow you to specify part of the ODBC connect string directly.

[Lukasz Szybalski]

On Thu, Apr 17, 2008 at 1:22 PM, Rick Morrison <rickmo...@gmail.com> wrote:
> ok, ok, assuming that dsn-less connections actually do ignore the .conf file
> and require all that stuff to be specified.
>
> here's the question that I'm trying to ask:
>
> instead of something like this:
> create_engine('mssql://user:pwd@server/database',
> odbc_driver='TDS', odbc_autotranslate='No', odbc_tds_ver='8.0')
>
> how about this:
> create_engine('mssql://user:pwd@server/database',
> odbc_connect='DRIVER=TDS; TDS_Version=8.0; OtherODBCStuff=goes here')
>

how about:
odbc_options='DRIVER=TDS; TDS_Version=8.0; OtherODBCStuff=goes here'

>
> do you see the difference? Instead of providing a hundred and one
> ODBC-specific keyword options, we just allow you to specify part of the ODBC
> connect string directly.

This would be perfect, since who knows what other options might need
to be specified as things progress.
Let me know when I would be able to try this out.
Lucas

[Rick Morrison]

It's in trunk r4518. Take 'er for a spin and let me know how it works out.

[Lukasz Szybalski]

On Thu, Apr 17, 2008 at 2:07 PM, Rick Morrison <rickmo...@gmail.com> wrote:
> It's in trunk r4518. Take 'er for a spin and let me know how it works out.

got an error:

e = sqlalchemy.create_engine('mssql://xxx:xxx@xxx:1433/xxxx',odbc_options='DRIVER={TDS};TDS_Version=8.0')
Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "sqlalchemy/engine/__init__.py", line 160, in create_engine
return strategy.create(*args, **kwargs)
File "sqlalchemy/engine/strategies.py", line 114, in create
raise TypeError(
TypeError: Invalid argument(s) 'odbc_options' sent to create_engine(),
using configuration MSSQLDialect_pyodbc/QueuePool/Engine. Please
check that the keyword arguments are appropriate for this combination
of components.

Does it matter what case are the parameters? DRIVER in pyodbc, we used
'driver' in previous connection strings etc...
Lucas

[Rick Morrison]

Does it matter what case are the parameters? DRIVER in pyodbc, we used
'driver' in previous connection strings etc...

No the parameters are a straight pass-through, that traceback is complaining about the 'odbc_options' keyword itself. Are you sure you're running the current trunk?

[Lukasz Szybalski]

svn update
At revision 4518.
lucas@debianlaptop:~/tmp/sqlalchemy/sqlalchemy/lib$ python
Python 2.4.4 (#2, Apr 5 2007, 20:11:18)
[GCC 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy
>>> sqlalchemy.__version__
'svn'
>>> e = sqlalchemy.create_engine('mssql://xxx:xxx@xxx:1433/xxx',odbc_options='DRIVER={TDS};TDS_Version=8.0')

Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "sqlalchemy/engine/__init__.py", line 160, in create_engine
return strategy.create(*args, **kwargs)
File "sqlalchemy/engine/strategies.py", line 114, in create
raise TypeError(
TypeError: Invalid argument(s) 'odbc_options' sent to create_engine(),
using configuration MSSQLDialect_pyodbc/QueuePool/Engine. Please
check that the keyword arguments are appropriate for this combination
of components.

not sure why strategies.py would complain?

Are you converting:
'mssql://xxx:xxx@xxx:1433/xxx',odbc_options='DRIVER={TDS};TDS_Version=8.0'
to
SERVER=xxx;UID=xx;PWD=xxx;DRIVER={TDS};TDS_Version=7.0

I have added the print line that we had used before that outputs the
connection string:

import sqlalchemy
>>> e = sqlalchemy.create_engine('mssql://xx:xxx@xxxx:1433/xxx',odbc_options='Driver=TDS;TDS_Version=8.0')
DRIVER={SQL Server};Server=xxx;Database=xxx;Port=1433;UID=xxx;PWD=xxx

The driver is not changed and tds_version is not added
Lucas

[Rick Morrison]

It's a two-line change that pops the new keyword out of the config dict just like the others that were added.

Mike, can you take a quick look at mssql.py line 804 and see why this might be complaining? I've got to run out. 

[Lukasz Szybalski]

On Thu, Apr 17, 2008 at 3:04 PM, Rick Morrison <rickmo...@gmail.com> wrote:
> It's a two-line change that pops the new keyword out of the config dict just
> like the others that were added.
>
> Mike, can you take a quick look at mssql.py line 804 and see why this might
> be complaining? I've got to run out.
>

well I don't know if that is a right place to add that?

from the code this what it would expect
e = sqlalchemy.create_engine("mssql://xxx:xxx@xxx:1433/xxx?odbc_options=Driver=TDS;TDS_Version=8.0")
vs you said you wanted:
sqlalchemy.create_engine('mssql://xxx:xxx@xxx:1433/xxxx',odbc_options='DRIVER={TDS};TDS_Version=8.0')

to create
Server=xx;Database=xx;Port=1433;UID=xx;PWD=xxx;Driver={TDS};TDS_Version=8.0

Lucas

[M.-A. Lemburg]

Reading this thread, I keep wondering why you are trying to put
all that connection setup configuration into the connection string...

Such setting are normally configured in the odbc.ini file and then
you just reference data source name in the connection string.

That's the standard way of using ODBC and the reason why you
have ODBC managers with nice setup GUIs. A DSN-less setup
like the one created by SA bypasses the ODBC manager
configuration.

SA should really adapt to the ODBC standard of using data source
names, as it moves the connection configuration where it should be:
Into the scope of the ODBC manager you are using to configure your
ODBC drivers.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Apr 18 2008)
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::

eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611

[Rick Morrison]

Reading this thread, I keep wondering why you are trying to put
all that connection setup configuration into the connection string...

Such setting are normally configured in the odbc.ini file and then
you just reference data source name in the connection string.

That's the standard way of using ODBC and the reason why you
have ODBC managers with nice setup GUIs. A DSN-less setup
like the one created by SA bypasses the ODBC manager
configuration.

Only via an option: DSN connections have been supported for some time via the 'dsn' keyword, the OP seems to either not want that or can't get it to work.

 

SA should really adapt to the ODBC standard of using data source
names, as it moves the connection configuration where it should be:
Into the scope of the ODBC manager you are using to configure your
ODBC drivers.

SA already has a de-facto standard using a db-uri scheme that works with non-ODBC datasources as well. It makes sense for ODBC compliant SA database drivers to conform to that form, not to just displace it and force a config-file style of setup.
 

[Rick Morrison]

Yeah, I was under the impression that config args passed in via create_engine() ctor and via dburi were treated the same, but looking over engine/strategies.py, it looks as if they have two separate injection points. I'll see if I can get it to allow either, stay tuned.

[jason kirtland]

Rick Morrison wrote:
> Yeah, I was under the impression that config args passed in via
> create_engine() ctor and via dburi were treated the same, but looking
> over engine/strategies.py, it looks as if they have two separate
> injection points. I'll see if I can get it to allow either, stay tuned.

create_engine('mssql://h/db', connect_args=dict(odbc_options='bar'))
create_engine('mssql://h/db?odbc_options=bar')

[Rick Morrison]

hey thanks Jason, that's a nice shortcut.

Lukasz, can you please give that a try?

[Lukasz Szybalski]

so to make it clear.

> create_engine('mssql://h/db', connect_args=dict(odbc_options='DRIVER={TDS};TDS_Version=8.0'))

This doesn't append driver and tds version to connection string. Isn't
connect_args for sqlalchemy specific actions and not for pyodbc
connector string.

create_engine('mssql://h/db?odbc_options=bar')
this will work if I had a single option, but I will supply multiple
options with '=' operator.

Example: This string need to be appended to connection string when
passed to pyodbc.

'DRIVER={TDS};TDS_Version=8.0'

so If you guys don't mind making the whole expression double quotes we
can move on to:
create_engine("mssql://h/db?odbc_options='DRIVER={TDS};TDS_Version=8.0' ")

but now the driver that I just supplied in (line 804), was previously
added in 783 so now you have 2 drivers.

which leads me to try:
e = sqlalchemy.create_engine("mssql://xxx:xxx@xxx:1433/xxx?driver=TDS?odbc_options='TDS_Version=8.0'")
this only takes the first argument "driver" and skips odbc_options

solution :

option 1:
make it so mssql://xxx:xxx@xxx:1433/xxx?driver=TDS?odbc_options='TDS_Version=8.0'
handles both parameters driver and odbc_options

option 2:
We either move line 804 above line 783 and then check if it includes
driver already?

line 784
if 'dsn' in keys:
connectors = ['dsn=%s' % keys['dsn']]
else:
connectors = ["DRIVER={%s}" % keys.pop('driver', 'SQL Server'),
'Server=%s' % keys['host'],
'Database=%s' % keys['database'] ]

line 804:
if 'odbc_options' in keys:
connectors.append(keys.pop('odbc_options'))

or option3?

Lucas

[Rick Morrison]

Err, on a second look, that's no good. The connect_args are passed directly through to connect().

This thing needs to construct an ODBC connection string from some fragments provided by the dburi, and from some engine options. So we'll either need some parsing tricks for the URL to allow strings with embedded equals signs, or some way to get that parameter to Dialect.create_connect_args() so it can be combined with the username, etc.

[Lukasz Szybalski]

On Fri, Apr 18, 2008 at 1:36 PM, Rick Morrison <rickmo...@gmail.com> wrote:
> Err, on a second look, that's no good. The connect_args are passed directly
> through to connect().
>
> This thing needs to construct an ODBC connection string from some fragments
> provided by the dburi, and from some engine options. So we'll either need
> some parsing tricks for the URL to allow strings with embedded equals signs,
> or some way to get that parameter to Dialect.create_connect_args() so it can
> be combined with the username, etc.
>

I've looked up how to pass these arguments and this will work:

e = sqlalchemy.create_engine("mssql://xxx:xx@xxx:1433/xx?driver=TDS&odbc_options='TDS_Version=8.0'")

I'll test this and see if it works.
Lucas

>
>
>
> >
> >
> > create_engine('mssql://h/db', connect_args=dict(odbc_options='bar'))
> > create_engine('mssql://h/db?odbc_options=bar')
> >
> >
> >
> >
> >
> >
>
>
> >
>

--

Automotive Recall Database. Cars, Trucks, etc.
http://www.lucasmanual.com/recall/

TurboGears Manual-Howto
http://lucasmanual.com/pdf/TurboGears-Manual-Howto.pdf

[Michael Bayer]

nope. Seems like the SQLA connection is still not using the
appropriate magical incantation to get all that ODBC stuff to behave
reasonably.

might I suggest just circumventing the URL entirely and just using
creator=lambda:
pyodbc
.connect
("DRIVER
={TDS};SERVER=xxxxxxxx;UID=xxxx;PWD=xxxx;TDS_Version=7.0") ? for
something this incredibly hacked and specific, it seems like the right
way to go.

On Apr 18, 2008, at 3:35 PM, Lukasz Szybalski wrote:

> On Wed, Apr 2, 2008 at 4:24 PM, Michael Bayer <zzz...@gmail.com>
> wrote:
>> Gottit. Notice, pyodbc is truncating all the column names to 30
>> characters.
>> anyway, thats why its broken. Is this a bug in pyodbc ? I
>> thought MS-SQL
>> has a much bigger limit than this ?
>>
>
>

> I finally got the driver name and tds version into sqlalchemy and it
> should be passing it on to pyodbc but it doesn't seem to work.
>
> I'm attaching a file. I have query to mssql via pyodbc and tds 7/8 and
> it returns long field description.
>
> but same thing in sqlalchemy doesn't.
>
> Is there some other processing done on it?
>
> Here is the file.
> Thanks,
> Lucas
> <sqlalchemy3.txt>

[Lukasz Szybalski]

>
> e = sqlalchemy.create_engine("mssql://xxx:xx@xxx:1433/xx?driver=TDS&odbc_options='TDS_Version=8.0'")

here is a patch to mssql.py that makes above line work.

805c805,808
< connectors.append(keys.pop('odbc_options'))
---
> odbc_options=keys.pop('odbc_options')
> if odbc_options[0]=="'" and odbc_options[-1]=="'":
> odbc_options=odbc_options[1:-1]
> connectors.append(odbc_options)

Could you guys add it in to svn.

Thanks,
Lucas

[M.-A. Lemburg]

On 2008-04-18 18:01, Rick Morrison wrote:
>> Reading this thread, I keep wondering why you are trying to put
>> all that connection setup configuration into the connection string...
>>
>> Such setting are normally configured in the odbc.ini file and then
>> you just reference data source name in the connection string.
>>
>> That's the standard way of using ODBC and the reason why you
>> have ODBC managers with nice setup GUIs. A DSN-less setup
>> like the one created by SA bypasses the ODBC manager
>> configuration.
>>
>
> Only via an option: DSN connections have been supported for some time via
> the 'dsn' keyword, the OP seems to either not want that or can't get it to
> work.

I know, but this should be the default rather than an option.

>> SA should really adapt to the ODBC standard of using data source
>> names, as it moves the connection configuration where it should be:
>> Into the scope of the ODBC manager you are using to configure your
>> ODBC drivers.
>
>
> SA already has a de-facto standard using a db-uri scheme that works with
> non-ODBC datasources as well. It makes sense for ODBC compliant SA database
> drivers to conform to that form, not to just displace it and force a
> config-file style of setup.

The DSN style setup fits in nicely with the URI scheme... just use
the host part of the URI as data source name - after all, the
ODBC manager will "connect" to the data source and not the
database itself.

Setting up an ODBC driver via the existing GUI tools is a lot
easier and less error prone than trying to figure out all
the different options and trying to put them into your
connection string. The direct approach should really only
be the last resort for very special cases.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Apr 23 2008)

[Michael Bayer]

On Apr 23, 2008, at 8:07 AM, M.-A. Lemburg wrote:

>
> The DSN style setup fits in nicely with the URI scheme... just use
> the host part of the URI as data source name - after all, the
> ODBC manager will "connect" to the data source and not the
> database itself.
>
> Setting up an ODBC driver via the existing GUI tools is a lot
> easier and less error prone than trying to figure out all
> the different options and trying to put them into your
> connection string. The direct approach should really only
> be the last resort for very special cases.

Hi Marc -

for those of us without access to ODBC toolsets, can you illustrate an
example of how this URL would look ? Using DSN within the "host"
section seems fine to me, as long as it can be disambiguated from
other non-DSN tokens that might be present there (i.e., when using
pyodbc or mxODBC, is a DSN the *only* kind of host identifier you'd
ever use?)

- mike

[Lukasz Szybalski]

Well,
Based on :
http://www.4guysfromrolla.com/webtech/070399-1.shtml

"These tests showed that DSN-less connections were slightly faster
than System DSN connections. The increase in performance was nothing
monumental; the greatest performance boost was a mere 13% faster with
64 concurrent requests. For one, two, or four concurrent requests,
there was virtually no performance improvement. In fact, no noticeable
improvement is seen in a DSN-less connection over a System DSN until
there are 10 or more concurrent connections."

Also, I don't know how things work in hosted environments but if they
charge for setting up system dsn then that might be another reason to
use dsn-less connection.

Also porting an application that that uses dsn-less connection is
easier then porting an application that requires system dsn to be
setup.

Lucas

[Rick Morrison]

Look, relax:

No one is suggesting that we *eliminate* DSN-less connections, only to come up with a reasonable *default* for ODBC connection specifications. A mechanism for non-DSN connections will certainly be provided.

[Lukasz Szybalski]

Any news on this? Should I create a ticket so we don't forget about this?

Lucas

[Rick Morrison]

There's a Dialect refactor underway for 0.5.0 that will likely change the way that options are fed to db engines:
 
  http://groups.google.com/group/sqlalchemy/browse_thread/thread/36fd2e935b165d70

Part of that work will probably have some influence on the dburi and create_engine(**kwargs) option specification, as both ODBC and JDBC have a lot of options that can be specified.

So the "odbc_options=" keyword is likely to be short-lived anyway.

For now, I've applied your patch verbatim, since you're the only one using it. r4621

-rick

[jason kirtland]

Rick Morrison wrote:
> There's a Dialect refactor underway for 0.5.0 that will likely change
> the way that options are fed to db engines:

Currently the munging of url params and connect_args into a connect()
lambda is happening outside of the dialect's control. In 0.5 that's all
moving into dialect-land and the dialects will be able to receive
connect_args (e.g. processing odbc_options, if specified there).
There'll be opportunities for dialects to set up pool events as well.

[Rick Morrison]

Sounds nice, thanks for the heads-up.

> There'll be opportunities for dialects to set up pool events as well.

One of the things I'm looking to see is better reconnect support for dead database connections from network partitions, sql server restarts, etc.

Is that going to be fully Dialect controlled, or is there some coming support for auto-reconnect as well?

[jason kirtland]

I was thinking of a user-level option for liveliness checking on pool
checkout, with dialect-specific implementations (e.g. execute a 'SELECT
1', or something more efficient if the driver allows). Is that in line
with what you were thinking?

[Rick Morrison]

I was thinking of a user-level option for liveliness checking on pool
checkout, with dialect-specific implementations (e.g. execute a 'SELECT
1', or something more efficient if the driver allows).  Is that in line
with what you were thinking?

I had in mind something more of a optimistic / reactive nature, like a retry on a cursor failure. But this could work equally well and could be much simpler, albeit at some round-trip time on every pool checkout.

What's the recovery strategy if the connection is found to be dead? An auto-reconnect with some retry count limit, or would it just notify the dialect and that's it?

[jason kirtland]

I believe right now it's a limited # of retries. (The basic support for
ping-on-checkout is already in the pool as of 0.4, but no dialect hooks
yet.)

[M.-A. Lemburg]

Yes. A typical ODBC connection string looks like this:

DSN=<data source name>;UID=<user name>;PWD=<password>

The DSN is then mapped to the configuration of the data source by
the ODBC manager. The configuration includes all the other bits
of information that may be needed to access a local or remote
database.

The URI would look like this:

mssql://username:password@mysqlserver/

PS: The Reply-To setting on this list is a bit confusing...

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, May 27 2008)

>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________

2008-07-07: EuroPython 2008, Vilnius, Lithuania 40 days to go

[Michael Bayer]

On May 27, 2008, at 9:10 AM, M.-A. Lemburg wrote:
>
> Yes. A typical ODBC connection string looks like this:
>
> DSN=<data source name>;UID=<user name>;PWD=<password>
>
> The DSN is then mapped to the configuration of the data source by
> the ODBC manager. The configuration includes all the other bits
> of information that may be needed to access a local or remote
> database.
>
> The URI would look like this:
>
> mssql://username:password@mysqlserver/
>

To be honest I thought this was how we were doing it, but
(incredulously) looking at the source I see theres a "dsn" keyword
argument in there ?!? Rick, what on earth are we using the <host>
field for then with pyodbc ? I always thought ODBC used dsn
exclusively for server identification ?

> PS: The Reply-To setting on this list is a bit confusing...

Theres no "setting" for this, thats Google groups.

[Rick Morrison]

> To be honest I thought this was how we were doing it, but
> (incredulously) looking at the source I see theres a "dsn" keyword
> argument in there ?!? 

Well, the odbc support grew rather organically, and was following the in-place SQLA conventions for host and database specs. I suppose we could make the host map to dsn as the default case, and add a "host=" keyword argument instead, but that (at least to me) runs counter to the current dburi conventions. Also don't forget that the MSSQL module supports other connection options than odbc, where the dsn concept doesn't exist at all. We could end with a module that operates one way with odbc, and something different with pymssql & etc. It's kind of confusing enough already.

> Rick, what on earth are we using the <host>
> field for then with pyodbc ?   I always thought ODBC used dsn
> exclusively for server identification ?

No, you can specify host, port, database and other settings on an ad-hoc basis, and much to my surprise, there's been a fair amount of demand for these "dsn-less" connection options recently.

This is kind of a re-think of the dburi conventions, at least on a small scale.

[Michael Bayer]

OK, so we can have host *or* DSN.

So Marc -

What's the argument for "DSN" as the official keeper of the "host"
part as opposed to an actual "host" ? Would it be because in ODBC,
"DSN" is *supposed* to be the primary identifier for how to get to a
host ? That's a fairly strong argument for me.

With the ADO module, there isnt a TCP host option, is my
recollection. The source seems to be sending <host> along to be
interpreted as "Data Source", so this appears to be consistent with
the "host=DSN" idea (and I believe this is the MS module I actually
played with on the occasion that I actually tried out the MS-SQL
dialect).

We are at the 0.5 stage and I haven't yet put out an alpha, so we'd
make this switch right there, if we decide upon it. I don't see any
way to make this a graceful switch, except that we provide the
"server" keyword attribute in 0.4 so that people can migrate their
URLs to a forwards-compatible keyword-based format.

[Paul Johnston]

Hi,

> > To be honest I thought this was how we were doing it, but
> > (incredulously) looking at the source I see theres a "dsn" keyword
> > argument in there ?!?

The way it works now suits me. I don't usually use DSNs, that avoids having to set them up when you move code to a new machine. But if you need to, the option is there.

Paul

[Michael Bayer]

Marc -

is it the case that mxODBC only accepts DSNs and not TCP hosts as an
argument, but PyODBC accepts both ?

On May 27, 2008, at 9:10 AM, M.-A. Lemburg wrote:

[M.-A. Lemburg]

On 2008-05-27 20:35, Michael Bayer wrote:
> Marc -
>
> is it the case that mxODBC only accepts DSNs and not TCP hosts as an
> argument, but PyODBC accepts both ?

mxODBC can connect to DSNs setup in ODBC manager, file DSNs and
also create DSN-less connections (which is what the current
SQL Server code in SA does). It also supports talking directly
to ODBC drivers, which usually provide a DSN-like syntax for
setting up the connection.

The standard for ODBC is to setup connections in the ODBC manager
as data sources, which are then referenced by name, the so-called
data source name or DSN.

The reason is fairly obvious: using DSNs you manage and control
your database connections in the OS rather than the various
applications.

Professional Python Services directly from the Source (#1, Jul 23 2008)

>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/

>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::

[M.-A. Lemburg]

On 2008-05-27 17:28, Michael Bayer wrote:
>
> On May 27, 2008, at 11:19 AM, Rick Morrison wrote:
>
>>> To be honest I thought this was how we were doing it, but
>>> (incredulously) looking at the source I see theres a "dsn" keyword
>>> argument in there ?!?

> ...

> What's the argument for "DSN" as the official keeper of the "host"
> part as opposed to an actual "host" ? Would it be because in ODBC,
> "DSN" is *supposed* to be the primary identifier for how to get to a
> host ? That's a fairly strong argument for me.

Yes.

DSNs refer to logical database connections in the ODBC manager
configuration. They don't necessarily map to hosts on the network -
indeed, it's well possible that the database runs on the same machine
and you connect to it via shared memory or pipes. OTOH, it's also
possible to have to connection setups for the same host, e.g. one
setup as read-only connection and the other as read-write connection.

> With the ADO module, there isnt a TCP host option, is my
> recollection. The source seems to be sending <host> along to be
> interpreted as "Data Source", so this appears to be consistent with
> the "host=DSN" idea (and I believe this is the MS module I actually
> played with on the occasion that I actually tried out the MS-SQL
> dialect).
>
> We are at the 0.5 stage and I haven't yet put out an alpha, so we'd
> make this switch right there, if we decide upon it. I don't see any
> way to make this a graceful switch, except that we provide the
> "server" keyword attribute in 0.4 so that people can migrate their
> URLs to a forwards-compatible keyword-based format.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Jul 23 2008)

>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::

[Michael Bayer]

On Jul 22, 2008, at 6:14 PM, M.-A. Lemburg wrote:

>
> The standard for ODBC is to setup connections in the ODBC manager
> as data sources, which are then referenced by name, the so-called
> data source name or DSN.
>
> The reason is fairly obvious: using DSNs you manage and control
> your database connections in the OS rather than the various
> applications.

This is how I always did it as well, years ago when I developed for
Windows platforms. I don't think there was even a way to specify a
direct hostname back then.

ODBC users here, how come we're opposed to following the standard
usage ? Are DSNs just not that popular anymore ?

[Rick Morrison]

> ODBC users here, how come we're opposed to following the standard
> usage  ?  Are DSNs just not that popular anymore ?

I think you're mischaracterizing things a bit by saying that the current state of affairs is "opposed" to the standard usage. Rather, the current scheme follows the SQLAlchemy DB-URL conventions, and doesn't break that convention for an ODBC connection. DSNs are supported: there's an connection keyword "dsn" where a dsn can be specified, it's just that the DSN doesn't dovetail nicely with the SQLAlchemy dburi concept, that's all.

To bring some background to this discussion, there's been a lot of interest on the list in non-dsn connections (rumour has it that non-dsn connections are faster on Win32 platforms, though I have yet to see hard numbers on this), others have chimed in saying that the current scheme works OK for them. I have no strong feelings on the matter one way or another, and I posted a patch off-list (probably bitrotted by now) that will modify the dburi parse for ODBC connections to interpret "host" as being a "dsn". There seems to be a lot of heat and smoke on this topic -- is it really that big of a deal?

[M.-A. Lemburg]

I don't see any heat in the discussion, really.

I just wanted to point out that the SA way of dealing with ODBC
connection setups is non-standard since DSN-less connections bypass
the OS ODBC manager's administration facilities. They don't bypass
the manager itself and thus there's no performance gain in doing this.
In the end, it only complicates things.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Jul 24 2008)

[Michael Bayer]

On Jul 24, 2008, at 9:08 AM, M.-A. Lemburg wrote:

>
> I don't see any heat in the discussion, really.
>
> I just wanted to point out that the SA way of dealing with ODBC
> connection setups is non-standard since DSN-less connections bypass
> the OS ODBC manager's administration facilities. They don't bypass
> the manager itself and thus there's no performance gain in doing this.
> In the end, it only complicates things.

as we're right at the cusp of a major 0.5 release, I'm still +1 on
having DSN take the place of "host" in the URL, when an ODBC driver is
in use. 0.5 has to be released fairly soon so we'd need to do it now.

[Rick Morrison]

I'm +0 on it, so unless someone barks soon, I'm going to add it (or Mike, if you're free time is better than mine, go ahead and apply the patch I sent some time ago)...

[Lukasz Szybalski]

Both of the connections will be available after this change? (dsn,dsn-less)

What will be the syntax for dsn connections?

dsn-less is:
driver://username:password@host:port/database

dsn:
dsn://username:password@dsname/database ???

Lucas

[Rick Morrison]

I see it like this (comments welcome):

dsn:
mssql://username:password@dsname

dsn-less:
mssql://username:password@host:port/database,  use_dsn=False

The use_dsn=False keyword would be required for a dsn-less connection.
In both cases, various odbc configuration parameters can be passed in as a string via the 'odbc_options' keyword

[Lukasz Szybalski]

On Thu, Jul 24, 2008 at 1:06 PM, Rick Morrison <rickmo...@gmail.com> wrote:
> I see it like this (comments welcome):
>
> dsn:
> mssql://username:password@dsname
>
> dsn-less:
> mssql://username:password@host:port/database, use_dsn=False

Sounds good.

>
> The use_dsn=False keyword would be required for a dsn-less connection.
> In both cases, various odbc configuration parameters can be passed in as a
> string via the 'odbc_options' keyword

In dsn-less connection I just want to make sure that following line
will still work(odbc_options accepts a string with "=" in it):
mssql://username:password@hostname:1433/databasename?driver=TDS&odbc_options='TDS_Version=8.0'

Also, when I used the dsn connection to mssql, and used openoffice the
only way I was able to query something was if I passed: "SET
QUOTED_IDENTIFIER ON" before select. Sine I never got a response from
freeTDS about how to set it in config file, I assume that is something
one would need to pass as part of the initialization of the
connection. Would that be something sqlalchemy would allow to pass
somehow? or?

Other then that, I'll be more then happy to test how it works with
mssql and unixodbc when you check it it in.

Lucas

[Michael Bayer]

On Jul 24, 2008, at 2:37 PM, Lukasz Szybalski wrote:

>
> Also, when I used the dsn connection to mssql, and used openoffice the
> only way I was able to query something was if I passed: "SET
> QUOTED_IDENTIFIER ON" before select. Sine I never got a response from
> freeTDS about how to set it in config file, I assume that is something
> one would need to pass as part of the initialization of the
> connection. Would that be something sqlalchemy would allow to pass
> somehow? or?

we have two systems of "setting things on connections", one of which
is the "creator" kwarg to create_engine() (which pretty much replaces
the whole URL issue), and the other is the "pool events" framework
which allows you to execute on the connection at various stages,
including creation, checkin and checkout. http://www.sqlalchemy.org/docs/04/sqlalchemy_interfaces.html#docstrings_sqlalchemy.interfaces_PoolListener