[python-sybase] Declaring MS SQL variables as parameters

Charles Bearden Charles.F.Bearden at uth.tmc.edu
Thu, 5 Aug 2004 14:05:23 -0500

I resolved the problem I posted about yesterday--I had incorrectly the
SYBASE environment variable and made one crucial misspelling.  Mea
culpa.  Mea stupida culpa.

So now I'd like to copy some data from PostgreSQL tables to MS SQL
tables.  I've just discovered that I need to declare the variables that
are used in the MS SQL statement as placeholders.  One of the columns in
the destination table is type ntext, but when I try to declare a
variable of type ntext, an exception is raised (originating with SQL
Server, I'm sure) to this effect:

Traceback (most recent call last):
  File "./sybase_test.py", line 33, in ?
    cu_sy.execute('declare @pmid nvarchar(10), @xmldata ntext')
  File "/usr/local/lib/python2.3/site-packages/Sybase.py", line 687, in
    self.description =3D fetcher.start(self.arraysize)
  File "/usr/local/lib/python2.3/site-packages/Sybase.py", line 442, in
    return self._start_results()
  File "/usr/local/lib/python2.3/site-packages/Sybase.py", line 546, in
    status, result =3D self._cmd.ct_results()
  File "/usr/local/lib/python2.3/site-packages/Sybase.py", line 161, in
    raise DatabaseError(_fmt_server(msg))
Sybase.DatabaseError: Msg 2739, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid for local variables.

As you can infer, '@xmldata' is meant to hold an XML record, which in
this application is likely to be larger than the varchar upper limit of
8000 bytes.

Is there a way to use the Sybase (0.36) module + FreeTDS (0.62.4) to
toss large chunks of data back and forth between a Linux client and an
MS SQL Server?  Am I missing something obvious?

Thanks for your help,

Chuck Bearden
Systems Analyst III
School of Health Information Sciences
University of Texas at Houston
713.500.3954 (voice)
713.500.3907 (fax)