[egenix-users] Max length of (N)VARCHAR; min length of TEXT

[Charles Bearden]

> -----Original Message-----
> From: M.-A. Lemburg [mailto:mal at egenix.com]
> Sent: Wednesday, August 18, 2004 7:13 AM
> 
> Charles Bearden wrote:
> > Egenix-mx-commercial-2.0.6, libiodbc-3.51.2, and freetds-0.62.4 on
RH 9,
> > and egenix-mx-commercial-2.0.6 with Windows ODBC driver on WinXP,
both
> > talking to MS SQL Server 2000.
> >
> > When I try to insert strings longer than 255 characters into a MS
SQL
> > column defined as nvarchar(1024) through FreeTDS/iODBC from Linux, I
get
> > the following exception:
> >
> > Traceback (most recent call last):
> >   File "./convtest.py", line 32, in ?
> >     try: mx_cur.execute(ins, t)
> > mxODBC.OperationalError: ('', 260, "[FreeTDS][SQL Server]Disallowed
> > implicit conversion from data type text to data type nvarchar, table
> > 'IfSH_Test.dbo.pmCite',  column 'ArticleTitle'. Use the CONVERT
function
> > to run this query.", 4579)
> 
> The problem here is that FreeTDS tries to convert the Python
> 8-bit string input into a UTF-16 string. Obviously SQL Server
> doesn't want to do this conversion implicitly (probably because
> it doesn't know the encoding of the input string).
> 
> Have you tried passing in the data as Unicode string ?

Thanks! That's extremely helpful to know.  I don't think I have tried
passing in a Unicode string, or if I have there was some other place in
the program where it caused a UnicodeDecodeError or something.  I may
retool the script to implement your suggestion.

The solution I came up with is a bit more kludgy.  I wrote a function
that takes the data from the '.description' attribute of the source
database cursor, after executing a 'select * from <tablename> limit 1'
to populate it correctly, in order to build an INSERT statmemt with
'CONVERT(?)' instead of simply '?' for those columns that might have
data longer than 255 bytes:

------------------------------------------------------
def mkIns(tname, cur):
  cur.execute('select * from %s limit 1' % (tname,))
  colinfo_lst = map(lambda t: (t[0],t[1],t[3]), cur.description)
  clist = [] ; phlist = []  ## column name list and placeholder list
  for colinfo in colinfo_lst:
    colname, coltype, colsize = colinfo
    clist.append(colname)
    #-- 1043 is the PostgreSQL object id for type VARCHAR
    if coltype == 1043 and colsize > 250:
      phlist.append('CONVERT(nvarchar, ?)')
    else:
      phlist.append('?')
  #-- put it all together and return
  return '''
    INSERT INTO %s
    (%s)
    VALUES
    (%s)
  ''' % (tname, ', '.join(clist), ', '.join(phlist))
------------------------------------------------------


It's an ugly hack, but it seems to do the trick. 

Thanks again for your response.

Chuck