[egenix-users] sql server right truncation for long column names

[Charlie Clark]

On 2004-06-11 at 23:11:36 [+0200], Joseph Kocherhans 
<Joseph.Kocherhans at hsc.utah.edu> wrote:
> I kept getting the following error from while trying to execute a
> query:
> 
> mxODBC.Warning: ('01004', 0, '[Microsoft][ODBC SQL Server Driver]String
> data, right truncation', 2394)
> 
> and after adding each column in the query one by one, it looks like I
> get the error only when the name of the column is over 38 chars. Can
> anyone confirm this? If so is there a chance of getting it changed?
> Unfortunately I'm not in charge of the db structure or I'd just rename
> the offending columns.
> 
> SQL Server 2000 SP3
> egenix-mx-commercial-2.0.6.win32-py2.3.exe
> python 2.3.3

Hi Joseph,

this is a pretty common scenario.

This is a comment in our source:
"""
/* ANSI SQL defines that column and table names may have at most 18
    characters (a column may be described using <table>.<column> which
    makes 37 characters in total + 1 for the terminating 0-byte).  Some
    databases also allow longer names, so this limit is an adjustable
	parameter. Set the value to 1 + max. length in characters. */
"""

The reason for this default limit is that in our experience it is *much* 
safer to stick to the standards.

You can adjust the max limit by adjusting mxCOMMERCIAL.py and recompiling.
...
         mx_Extension('mx.ODBC.Windows.mxODBC',
                      ['mx/ODBC/Windows/mxODBC.c',
                       'mx/ODBC/Windows/mxSQLCodes.c'],
                      include_dirs=['mx/ODBC/Windows'],
                      define_macros=[('MS_ODBC_MANAGER', None),
                                     ('WANT_UNICODE_SUPPORT', None),
                                     ('MAX_COLUMN_NAME_LENGTH', '50')],
...

To recompile you need Visual C++ 6 and some experience with Python 
extensions. We can also provide you with a build, if you require.

Sincerely

Charlie Clark
-- 
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
 >>> 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,FreeBSD for free ! ::::