Column name mapping problem in 0.3.7

[Graham Stratton]

Hi,

I've just upgraded to 0.3.7, and when the combined table/column name
is at least 30 characters I get an error from the mapper like this:

sqlalchemy.exceptions.NoSuchColumnError: "Could not locate column in
row for column 'Column(u'EventLastCancellationDate',MSDate())'"

Is this a problem with the changes in 0.3.7 ?

I'm using pymssql 0.8.0 with SQL server 2000.

Thanks,

Graham

[Graham Stratton]

To add a little more info, 0.3.6 generates:

SELECT [T_Event].[EventLastCancellationDate] AS
[T_Event_EventLastCancell_8ab9]

whereas 0.3.7 generates

[T_Event].[EventLastCancellationDate] AS
[T_Event_EventLastCancellationDate]

>From what I can find, column names can be up to 128 chars in SQL
server, but maybe pymssql is truncating at 30?

Graham

[Michael Bayer]

SA is now doing no column truncation for MS-SQL, as we have not
placed any limit within the MS-SQL dialects. previously, SA was
truncating all identifiers to 30 characters for all dialects
(including those that had much larger limitations), and it would
search for these truncated names in result sets.

so this seems to be something happening with the DBAPI itself. if
you want to do us a favor and attempt your query with the raw DBAPI,
and then look at cursor.description, we can confirm that it is
truncating to 30 characters and we can place this number within the
corresponding dialect so that both sides agree.

if it is the case that MS-SQL overall has a 30 character restriction
you might want to consider shortening your column names.

[Michael Bayer]

On May 1, 2007, at 7:50 AM, Graham Stratton wrote:

>
> To add a little more info, 0.3.6 generates:
>
> SELECT [T_Event].[EventLastCancellationDate] AS
> [T_Event_EventLastCancell_8ab9]
>
> whereas 0.3.7 generates
>
> [T_Event].[EventLastCancellationDate] AS
> [T_Event_EventLastCancellationDate]
>
>> From what I can find, column names can be up to 128 chars in SQL
> server, but maybe pymssql is truncating at 30?
>

if its the case that MS-SQL allows 128-character column names, but
only 30 character label names, that would be unfortunate. i might
have to rewrite the whole label-truncation code (since it cant
currently differentiate between physical column names and label names).

[Rick Morrison]

The label-truncation code is fine. The issue isn't SA. It's the DBAPI that pymssql rides on top of...identifier limit is 30 chars, is deprecated by Microsoft, it will never be fixed.

Try pyodbc, which has no such limitation.

[Rick Morrison]

s/DBAPI/DBlib/

[Michael Bayer]

On May 1, 2007, at 11:18 AM, Rick Morrison wrote:

> The label-truncation code is fine. The issue isn't SA. It's the
> DBAPI that pymssql rides on top of...identifier limit is 30 chars,
> is deprecated by Microsoft, it will never be fixed.
>
> Try pyodbc, which has no such limitation.
>

OK well, we should put the 30-char limit into pymssql's dialect.
however, the way the truncation works right now, its going to chop
off all the column names too...which means unless i fix that, pymssql
cant be used with any columns over 30 chars in size.

[Rick Morrison]

The underlying DBlib limits *all* identifier names, including column names to 30 chars anyway, so no issue there.

Where does the character limit go in the dialect? Can I follow Oracle as an example?

On 5/1/07, Michael Bayer <mik...@zzzcomputing.com> wrote:

[Michael Bayer]

it is max_identifier_length() on Dialect.

ive also gone and figured out why it is hard to separate the max length of columns vs. that of labels...its because of some issues that arise with some auto-labeling that happens inside of ansisql.py....so its fortunate i dont have to get into that.

[Graham Stratton]

I'm bringing this old thread up because I'm still having the same
issue with 0.3.8. In order to use mssql I have to add

> def max_identifier_length(self):
> return 30

to the pymssql dialect.

I also find that I need to set has_sane_rowcount=False (as I have had
to with every release).

Is anyone else using pymssql? Do you have the same problems? Should
these changes be made on the trunk?

Thanks,

Graham

On May 1, 7:13 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> it is max_identifier_length() on Dialect.
>
> ive also gone and figured out why it is hard to separate the max
> length of columns vs. that of labels...its because of some issues
> that arise with some auto-labeling that happens inside of
> ansisql.py....so its fortunate i dont have to get into that.
>
> On May 1, 2007, at 12:57 PM, Rick Morrison wrote:
>
> > The underlying DBlib limits *all* identifier names, including
> > column names to 30 chars anyway, so no issue there.
>
> > Where does the character limit go in the dialect? Can I follow
> > Oracle as an example?
>

[Rick Morrison]

Hi Graham,

There's a good chance that only you and I are using pymssql, and I don't have have the long identifiers problem, so it kind of dropped throught the cracks, sorry.

I've checked in the 30-character thing, but I've left off the sane_rowcount for now. I had run into issues with that back in March, and I ended up patching pymssql to fix the problem rather than set sane_rowcount to False. Can't remember why now, I'm currently running our local test suite which should remind me.

Rick

[Graham Stratton]

Hi Rick,

I still have rowcount issues on the latest svn. Also, is 'money' a
native type? I'm currently setting
ischema_names['money'] = MSNumeric

Thanks,

Graham

[Rick Morrison]

<replying to the list, I think your email came only to me>

On Tue, Feb 26, 2008 at 4:45 AM, duhblah <bcla.wi...@googlemail.com> wrote:

Hi All,

I'm a bit late to this discussion but I've just run into this problem
so it's still out there. I found a way round for now by using
'include_properties = ...' and only using the (fortunately short
named) columns that I need, but this could be a real pain. I'm using
python-sqlalchemy-0.4.2 and pymssql-0.8.0. Is this a problem with
pymssql or sqlalchemy - I couldn't work it out from the previous post.
It looks like I have to edit pymssql.

The 30 character identifier limit here does not originate with pymssql, but with the underlying Microsoft/Sybase DB-API library that it is built upon. MS deprecated their DB-API long ago. (Note that MSDBAPI is an old name, and has nothing to do with the Python DB-API spec). As far as I know, source is unavailable, and so that is pretty much that.

The main reason that SA supports pymssql at all is because up until recently, it has been the only reliable option for connection to MSSQL from *nix OSes.  That is in the process of changing:  there are recent reports of pyodbc on Unix working. The MSSQL module already supports pyodbc for Windows, so whatever tweaks that might be needed to get it working with Unix are hoped to be small. That effort is just beginning, so if you have some time to spend on it, I would urge you to follow the recent thread on that subject and give that direction a try.

Anyway, I'm just glad to find out it's not just me with this problem.
sqlalchemy has been great to use so far (apart from this).

Connecting to MSSQL from Python+UNIX can be problematic, theres not a lot of vendor support from MS for any Python DBAPI module. Maybe that will change when ironpython adoption picks up. For now, an ODBC connector looks to be the best route. As I mentioned, work for MSSQL + pyodbc is beginning, and I also know Jason is working on a generic ODBC SA driver, but I don't know if he plans to include an option for MSSQL support or not in that.....

Rick