[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
Rick Morrison wrote: 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? 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? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
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? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
Rick Morrison wrote: 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? 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.) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
On Tue, Apr 22, 2008 at 9:35 AM, Lukasz Szybalski [EMAIL PROTECTED] wrote:
e = sqlalchemy.create_engine(mssql://xxx:[EMAIL
PROTECTED]:1433/xx?driver=TDSodbc_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.
Any news on this? Should I create a ticket so we don't forget about this?
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
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 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
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. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
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? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
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. 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 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
e = sqlalchemy.create_engine(mssql://xxx:[EMAIL
PROTECTED]:1433/xx?driver=TDSodbc_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
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
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. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
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')
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
hey thanks Jason, that's a nice shortcut.
Lukasz, can you please give that a try?
On Fri, Apr 18, 2008 at 12:07 PM, jason kirtland [EMAIL PROTECTED]
wrote:
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')
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
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.
On Thu, Apr 17, 2008 at 4:24 PM, Lukasz Szybalski [EMAIL PROTECTED]
wrote:
On Thu, Apr 17, 2008 at 3:04 PM, Rick Morrison [EMAIL PROTECTED]
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:[EMAIL PROTECTED]
:1433/xxx?odbc_options=Driver=TDS;TDS_Version=8.0)
vs you said you wanted:
sqlalchemy.create_engine('mssql://xxx:[EMAIL PROTECTED]
:1433/',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
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
On Fri, Apr 18, 2008 at 11:07 AM, jason kirtland [EMAIL PROTECTED] wrote:
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')
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:[EMAIL
PROTECTED]: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:[EMAIL
PROTECTED]: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
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
On Fri, Apr 18, 2008 at 1:36 PM, Rick Morrison [EMAIL PROTECTED] 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:[EMAIL
PROTECTED]:1433/xx?driver=TDSodbc_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
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
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=;UID=;PWD=;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 [EMAIL PROTECTED]
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
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
On Mon, Apr 7, 2008 at 4:37 PM, Rick Morrison [EMAIL PROTECTED] wrote:
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.
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
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
On Thu, Apr 17, 2008 at 10:02 AM, Lukasz Szybalski [EMAIL PROTECTED] wrote:
On Mon, Apr 7, 2008 at 4:37 PM, Rick Morrison [EMAIL PROTECTED] wrote:
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.
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
On Thu, Apr 17, 2008 at 11:09 AM, Rick Morrison [EMAIL PROTECTED] 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
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
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 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
On Thu, Apr 17, 2008 at 12:14 PM, Rick Morrison [EMAIL PROTECTED] 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
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
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:[EMAIL PROTECTED]/database',
odbc_driver='TDS', odbc_autotranslate='No', odbc_tds_ver='8.0')
how about this:
create_engine('mssql://user:[EMAIL PROTECTED]/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.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
It's in trunk r4518. Take 'er for a spin and let me know how it works out.
On Thu, Apr 17, 2008 at 2:54 PM, Lukasz Szybalski [EMAIL PROTECTED]
wrote:
On Thu, Apr 17, 2008 at 1:22 PM, Rick Morrison [EMAIL PROTECTED]
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:[EMAIL PROTECTED]/database',
odbc_driver='TDS', odbc_autotranslate='No', odbc_tds_ver='8.0')
how about this:
create_engine('mssql://user:[EMAIL PROTECTED]/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
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
On Thu, Apr 17, 2008 at 2:07 PM, Rick Morrison [EMAIL PROTECTED] 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:[EMAIL
PROTECTED]:1433/',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
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
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? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
On Thu, Apr 17, 2008 at 2:35 PM, Rick Morrison [EMAIL PROTECTED] wrote:
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?
svn update
At revision 4518.
[EMAIL PROTECTED]:~/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:[EMAIL
PROTECTED]: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:[EMAIL
PROTECTED]: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:[EMAIL
PROTECTED]: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
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
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.
On Thu, Apr 17, 2008 at 3:58 PM, Lukasz Szybalski [EMAIL PROTECTED]
wrote:
On Thu, Apr 17, 2008 at 2:35 PM, Rick Morrison [EMAIL PROTECTED]
wrote:
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?
svn update
At revision 4518.
[EMAIL PROTECTED]:~/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:[EMAIL PROTECTED]
: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:[EMAIL
PROTECTED]: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:[EMAIL PROTECTED]
: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
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
On Thu, Apr 17, 2008 at 3:04 PM, Rick Morrison [EMAIL PROTECTED] 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:[EMAIL
PROTECTED]:1433/xxx?odbc_options=Driver=TDS;TDS_Version=8.0)
vs you said you wanted:
sqlalchemy.create_engine('mssql://xxx:[EMAIL
PROTECTED]:1433/',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
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
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. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
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. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
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 [EMAIL PROTECTED] 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? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
On Wed, Apr 2, 2008 at 6:22 PM, Rick Morrison [EMAIL PROTECTED] 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? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
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. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
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. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
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. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
On Wed, Apr 2, 2008 at 6:35 PM, Rick Morrison [EMAIL PROTECTED] wrote: 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. 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 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
