BINARY Columns in MS SQL

[John Hampton]

I have a similar problem to Sam as shown in the following thread:

http://groups.google.com/group/sqlalchemy/browse_thread/thread/3be1df474de602d0

I get the same error regarding conversion of VARCHAR to BINARY. The
difference is that I am using pyodbc and unixODBC instead of pymssql.

Is anyone else using BINARY columns in MS SQL successfully? If so,
could you share some sample code so that I can figure out what I'm doing
wrong? Thanks.

-John

[Rick Morrison]

Hi John,

Is the column you're having issues with really a VARCHAR, or is the message misleading? How did you create the table, pre-existing or via SQLAlchemy? Can you show the schema and the code you're trying to access it with?

Thanks,
Rick

[John Hampton]

Rick Morrison wrote:
> Is the column you're having issues with really a VARCHAR, or is the
> message misleading? How did you create the table, pre-existing or via
> SQLAlchemy? Can you show the schema and the code you're trying to access
> it with?

It's a preexisting table. The column in the DB is of type BINARY. The
type used in the python code is Binary(). I'm trying to insert a Binary
value from a python program into a MS SQL database BINARY column using
SQLAlchemy.

Below is the schema from the table as dumped via the SQL Enterprise
Manager, the declarative SQLAlchemy class used and the sample code used,
plus the traceback:

------------------ MS SQL TABLE -----------------------------
CREATE TABLE [SY00500] (
[GLPOSTDT] [datetime] NOT NULL ,
[BCHSOURC] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BACHNUMB] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SERIES] [smallint] NOT NULL ,
[MKDTOPST] [tinyint] NOT NULL ,
[NUMOFTRX] [int] NOT NULL ,
[RECPSTGS] [smallint] NOT NULL ,
[DELBACH] [tinyint] NOT NULL ,
[MSCBDINC] [smallint] NOT NULL ,
[BACHFREQ] [smallint] NOT NULL ,
[RCLPSTDT] [datetime] NOT NULL ,
[NOFPSTGS] [smallint] NOT NULL ,
[BCHCOMNT] [char] (61) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BRKDNALL] [tinyint] NOT NULL ,
[CHKSPRTD] [tinyint] NOT NULL ,
[RVRSBACH] [tinyint] NOT NULL ,
[USERID] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CHEKBKID] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BCHTOTAL] [numeric](19, 5) NOT NULL ,
[BCHEMSG1] [binary] (4) NOT NULL ,
[BCHEMSG2] [binary] (4) NOT NULL ,
[BACHDATE] [datetime] NOT NULL ,
[BCHSTRG1] [char] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BCHSTRG2] [char] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[POSTTOGL] [tinyint] NOT NULL ,
[MODIFDT] [datetime] NOT NULL ,
[CREATDDT] [datetime] NOT NULL ,
[NOTEINDX] [numeric](19, 5) NOT NULL ,
[CURNCYID] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BCHSTTUS] [smallint] NOT NULL ,
[CNTRLTRX] [int] NOT NULL ,
[CNTRLTOT] [numeric](19, 5) NOT NULL ,
[PETRXCNT] [smallint] NOT NULL ,
[APPROVL] [tinyint] NOT NULL ,
[APPRVLDT] [datetime] NOT NULL ,
[APRVLUSERID] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ORIGIN] [smallint] NOT NULL ,
[ERRSTATE] [int] NOT NULL ,
[GLBCHVAL] [binary] (4) NOT NULL ,
[Computer_Check_Doc_Date] [datetime] NOT NULL ,
[Sort_Checks_By] [smallint] NOT NULL ,
[SEPRMTNC] [tinyint] NOT NULL ,
[REPRNTED] [smallint] NOT NULL ,
[CHKFRMTS] [smallint] NOT NULL ,
[TRXSORCE] [char] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PmtMethod] [smallint] NOT NULL ,
[EFTFileFormat] [smallint] NOT NULL ,
[Workflow_Approval_Status] [smallint] NOT NULL ,
[Workflow_Priority] [smallint] NOT NULL ,
[TIME1] [datetime] NOT NULL ,
[DEX_ROW_ID] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PKSY00500] PRIMARY KEY NONCLUSTERED
(
[BCHSOURC],
[BACHNUMB]
) ON [PRIMARY] ,
CHECK (datepart(hour,[APPRVLDT]) = 0 and datepart(minute,[APPRVLDT]) =
0 and datepart(second,[APPRVLDT]) = 0 and
datepart(millisecond,[APPRVLDT]) = 0),
CHECK (datepart(hour,[BACHDATE]) = 0 and datepart(minute,[BACHDATE]) =
0 and datepart(second,[BACHDATE]) = 0 and
datepart(millisecond,[BACHDATE]) = 0),
CHECK (datepart(hour,[Computer_Check_Doc_Date]) = 0 and
datepart(minute,[Computer_Check_Doc_Date]) = 0 and
datepart(second,[Computer_Check_Doc_Date]) = 0 and
datepart(millisecond,[Computer_Check_Doc_Date]) = 0),
CHECK (datepart(hour,[CREATDDT]) = 0 and datepart(minute,[CREATDDT]) =
0 and datepart(second,[CREATDDT]) = 0 and
datepart(millisecond,[CREATDDT]) = 0),
CHECK (datepart(hour,[GLPOSTDT]) = 0 and datepart(minute,[GLPOSTDT]) =
0 and datepart(second,[GLPOSTDT]) = 0 and
datepart(millisecond,[GLPOSTDT]) = 0),
CHECK (datepart(hour,[MODIFDT]) = 0 and datepart(minute,[MODIFDT]) = 0
and datepart(second,[MODIFDT]) = 0 and datepart(millisecond,[MODIFDT]) = 0),
CHECK (datepart(hour,[RCLPSTDT]) = 0 and datepart(minute,[RCLPSTDT]) =
0 and datepart(second,[RCLPSTDT]) = 0 and
datepart(millisecond,[RCLPSTDT]) = 0),
CHECK (datepart(day,[TIME1]) = 1 and datepart(month,[TIME1]) = 1 and
datepart(year,[TIME1]) = 1900)
) ON [PRIMARY]

-------------------- SQLAlchemy Class -------------------------

class BatchHeader(gp_Base):
__tablename__ = 'sy00500'

GLPOSTDT = Column(DateTime, nullable=False)
BCHSOURC = Column(String(15), nullable=False, primary_key=True)
BACHNUMB = Column(String(15), nullable=False, primary_key=True
SERIES = Column(Integer, nullable=False)
MKDTOPST = Column(Integer, nullable=False)
NUMOFTRX = Column(Integer, nullable=False)
RECPSTGS = Column(Integer, nullable=False)
DELBACH = Column(Integer, nullable=False)
MSCBDINC = Column(Integer, nullable=False)
BACHFREQ = Column(Integer, nullable=False)
RCLPSTDT = Column(DateTime, nullable=False)
NOFPSTGS = Column(Integer, nullable=False)
BCHCOMNT = Column(String(61), nullable=False)
BRKDNALL = Column(Integer, nullable=False)
CHKSPRTD = Column(Integer, nullable=False)
RVRSBACH = Column(Integer, nullable=False)
USERID = Column(String(15), nullable=False)
CHEKBKID = Column(String(15), nullable=False)
BCHTOTAL = Column(Numeric(19, 5), nullable=False)
#Total of all transactions dollars
BCHEMSG1 = Column(Binary(4), nullable=False)
BCHEMSG2 = Column(Binary(4), nullable=False)
BACHDATE = Column(DateTime, nullable=False)
BCHSTRG1 = Column(String(21), nullable=False)
BCHSTRG2 = Column(String(21), nullable=False)
POSTTOGL = Column(Integer, nullable=False)
MODIFDT = Column(DateTime, nullable=False)
CREATDDT = Column(DateTime, nullable=False)
NOTEINDX = Column(Numeric(19, 5), nullable=False)
CURNCYID = Column(String(15), nullable=False)
BCHSTTUS = Column(Integer, nullable=False)
CNTRLTRX = Column(Integer, nullable=False)
CNTRLTOT = Column(Numeric(19, 5), nullable=False)
PETRXCNT = Column(Integer, nullable=False)
APPROVL = Column(Integer, nullable=False)
APPRVLDT = Column(DateTime, nullable=False)
APRVLUSERID = Column(String(15), nullable=False)
ORIGIN = Column(Integer, nullable=False)
ERRSTATE = Column(Integer, nullable=False)
GLBCHVAL = Column(MSBinary(4), nullable=False)
Computer_Check_Doc_Date = Column(DateTime, nullable=False)
Sort_Checks_By = Column(Integer, nullable=False)
SEPRMTNC = Column(Integer, nullable=False)
REPRNTED = Column(Integer, nullable=False)
CHKFRMTS = Column(Integer, nullable=False)
TRXSORCE = Column(String(13), nullable=False)
PmtMethod = Column(Integer, nullable=False)
EFTFileFormat = Column(Integer, nullable=False)
Workflow_Approval_Status = Column(Integer, nullable=False)
Workflow_Priority = Column(Integer, nullable=False)
TIME1 = Column(DateTime, nullable=False)

def __init__(self, bachnum):
currenttime = datetime(*datetime.now().timetuple()[:3])
bchtotal = 0.0
self.GLPOSTDT = currenttime
self.BCHSOURC = "Rcvg Trx Entry"
self.BACHNUMB = bachnum
self.SERIES = 4
self.MKDTOPST = 0
self.NUMOFTRX = 1
self.RECPSTGS = 0
self.DELBACH = 0
self.MSCBDINC = 0
self.BACHFREQ = 1
self.RCLPSTDT = datetime(1900, 1, 1)
self.NOFPSTGS = 0
self.BCHCOMNT = ''
self.BRKDNALL = 0
self.CHKSPRTD = 0
self.RVRSBACH = 0
self.USERID = 'sa'
self.CHEKBKID = 'CB01'
self.BCHTOTAL = bchtotal
self.BCHEMSG1 = '\x00\x00\x00\x00' #bin
self.BCHEMSG2 = '\x00\x00\x00\x00' #bin
self.BACHDATE = datetime(1900, 1, 1)
self.BCHSTRG1 = ''
self.BCHSTRG2 = ''
self.POSTTOGL = 0
self.MODIFDT = currenttime
self.CREATDDT = currenttime
self.NOTEINDX = 0.0
self.CURNCYID = ''
self.BCHSTTUS = 0
self.CNTRLTRX = 0
self.CNTRLTOT = 0
self.PETRXCNT = 0
self.APPROVL = 0
self.APPRVLDT = datetime(1900, 1, 1)
self.APRVLUSERID = ''
self.ORIGIN = 1
self.ERRSTATE = 0
self.GLBCHVAL = '\x00\x00\x00\x00' #bin
self.Computer_Check_Doc_Date = datetime(1900, 1, 1)
self.Sort_Checks_By = 0
self.SEPRMTNC = 0
self.REPRNTED = 0
self.CHKFRMTS = 0
self.TRXSORCE = ''
self.PmtMethod = 0
self.EFTFileFormat = 0
self.Workflow_Approval_Status = 0
self.Workflow_Priority = 0
self.TIME1 = datetime(1900, 1, 1)

------------ Sample Code -------------------------
>>> import sqlalchemy as sa
>>> engine = sa.create_engine('mssql://user:pass@/?dsn=GPTest',
... encoding='latin-1', convert_unicode=True)
>>> from myproj import model
>>> from myproj.model import BatchHeader
>>> model.meta.Session = sa.orm.sessionmaker(bind=engine)
>>> s = model.meta.Session()
>>> s.add(BatchHeader('foo'))
>>> s.commit()

------------------ Traceback ------------------
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/session.py",
line 669, in commit
self.transaction.commit()
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/session.py",
line 375, in commit
self._prepare_impl()
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/session.py",
line 359, in _prepare_impl
self.session.flush()
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/session.py",
line 1367, in flush
self._flush(objects)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/session.py",
line 1437, in _flush
flush_context.execute()
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/unitofwork.py",
line 263, in execute
UOWExecutor().execute(self, tasks)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/unitofwork.py",
line 757, in execute
self.execute_save_steps(trans, task)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/unitofwork.py",
line 772, in execute_save_steps
self.save_objects(trans, task)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/unitofwork.py",
line 763, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/orm/mapper.py",
line 1215, in _save_obj
c = connection.execute(statement.values(value_params), params)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/engine/base.py",
line 848, in execute
return Connection.executors[c](self, object, multiparams, params)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/engine/base.py",
line 899, in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) > 1), distilled_params=params)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/engine/base.py",
line 911, in _execute_compiled
self.__execute_raw(context)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/engine/base.py",
line 920, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/engine/base.py",
line 964, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor)
File
"/srv/gartersnake/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/sqlalchemy/engine/base.py",
line 946, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', "[42000]
[FreeTDS][SQL Server]Disallowed implicit conversion from data type
varchar to data type binary, table 'TWO.dbo.SY00500', column 'BCHEMSG1'.
Use the CONVERT function to run this query. (260) (SQLPrepare)") 'INSERT
INTO sy00500 ([GLPOSTDT], [BCHSOURC], [BACHNUMB], [SERIES], [MKDTOPST],
[NUMOFTRX], [RECPSTGS], [DELBACH], [MSCBDINC], [BACHFREQ], [RCLPSTDT],
[NOFPSTGS], [BCHCOMNT], [BRKDNALL], [CHKSPRTD], [RVRSBACH], [USERID],
[CHEKBKID], [BCHTOTAL], [BCHEMSG1], [BCHEMSG2], [BACHDATE], [BCHSTRG1],
[BCHSTRG2], [POSTTOGL], [MODIFDT], [CREATDDT], [NOTEINDX], [CURNCYID],
[BCHSTTUS], [CNTRLTRX], [CNTRLTOT], [PETRXCNT], [APPROVL], [APPRVLDT],
[APRVLUSERID], [ORIGIN], [ERRSTATE], [GLBCHVAL],
[Computer_Check_Doc_Date], [Sort_Checks_By], [SEPRMTNC], [REPRNTED],
[CHKFRMTS], [TRXSORCE], [PmtMethod], [EFTFileFormat],
[Workflow_Approval_Status], [Workflow_Priority], [TIME1]) VALUES (?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
[datetime.datetime(2008, 9, 19, 0, 0), 'Rcvg Trx Entry', 'foo', 4, 0, 1,
0, 0, 0, 1, datetime.datetime(1900, 1, 1, 0, 0), 0, '', 0, 0, 0, 'sa',
'CB01', '0.0', <read-only buffer for 0xb74738a0, size -1, offset 0 at
0x8387560>, <read-only buffer for 0xb74738a0, size -1, offset 0 at
0x8387580>, datetime.datetime(1900, 1, 1, 0, 0), '', '', 0,
datetime.datetime(2008, 9, 19, 0, 0), datetime.datetime(2008, 9, 19, 0,
0), '0.0', '', 0, 0, '0', 0, 0, datetime.datetime(1900, 1, 1, 0, 0), '',
1, 0, <read-only buffer for 0xb74738a0, size -1, offset 0 at 0x83875c0>,
datetime.datetime(1900, 1, 1, 0, 0), 0, 0, 0, 0, '', 0, 0, 0, 0,
datetime.datetime(1900, 1, 1, 0, 0)]

-John

[Rick Morrison]

Hmmm, looks to me as if SQLA is generating the query correctly, but that the DBAPI passes along the Binary() value encoded in a Python binary string, which MSSQL then interprets as Varchar, and then complains that it can't do an implicit conversion. That's a surprise to me; I had thought that this had been working.

Googling for the appropriate syntax to keep MSSQL happy, most users end up using the CONVERT() syntax, but I think that might be clumsy to wedge into the Dialect compiler. Here's a ticket for a similar issue from the ROR folks, with a interesting and different solution:
 http://dev.rubyonrails.org/ticket/7699

Mike, any perspective on how difficult it might be to get the MSSQL Dialect to emit the CONVERT syntax for binary fields, or would converting the value itself using base64 encoding be the better path?

[John Hampton]

Rick Morrison wrote:
> Hmmm, looks to me as if SQLA is generating the query correctly, but that
> the DBAPI passes along the Binary() value encoded in a Python binary
> string, which MSSQL then interprets as Varchar, and then complains that
> it can't do an implicit conversion. That's a surprise to me; I had
> thought that this had been working.

This is the same conclusion that I had while talking with Mike on IRC.
Mike believes that there are others out there that use MSSQL and
SQLAlchemy that have BINARY columns working fine. I was hoping that
this is true and someone who has had success could share an example of
their code with me so that I could continue to find the source of the
problem.

Thanks for the replies.

-John

[John Hampton]

John Hampton wrote:
> This is the same conclusion that I had while talking with Mike on IRC.
> Mike believes that there are others out there that use MSSQL and
> SQLAlchemy that have BINARY columns working fine. I was hoping that
> this is true and someone who has had success could share an example of
> their code with me so that I could continue to find the source of the
> problem.

For prosperities sake here is a link to the IRC conversation:

http://pylonshq.com/irclogs/%23sqlalchemy/%23sqlalchemy.2008-09-19.log.html#t2008-09-19T10:33:13

-John

[Michael Bayer]

On Sep 19, 2008, at 4:14 PM, Rick Morrison wrote:

>
> Mike, any perspective on how difficult it might be to get the MSSQL
> Dialect to emit the CONVERT syntax for binary fields, or would
> converting the value itself using base64 encoding be the better path?

we've had the notion of TypeEngine's knowing how to emit SQL functions
in the hopper as a result of the PostGIS crowd really wanting that.
Its a straightforward path to produce but its an 0.5 and possibly not
0.5.0 thing. If base64 encoding within the bind_processor() can fix
MS-SQL for now, I'd say that would be the approach for the time
being. I guess you've never gotten testtypes.py BinaryTest to run
with MS-SQL ? I would definitely want some test coverage in test/
dialect/mssql.py for this, I'm pretty shocked nobody has had this
problem before.

[Rick Morrison]

So I'm working a bit on this.....

> If base64 encoding within the bind_processor() can fix
> MS-SQL for now, I'd say that would be the approach for the time
> being. 

turns out base64 encoding is problematic: it requires a corresponding decode for the data retrieval, which makes it effectively useless for legacy databases with extant values stored.

I think an out-only encoding as follows would be all that's needed for now:


class MSBinary(sqltypes.Binary):
    def bind_processor(self, dialect):
        def process(value):
            return '0x' + value.encode('hex')
        return process

...the issue is that the emitted value is getting quoted somewhere after the type conversion:

     INSERT INTO binary_table VALUES(...., '0x6df02da', ...)

but MSSQL's parser can recognize the '0x' prefix and wants the value unquoted:

     INSERT INTO binary_table VALUES(...., 0x6df02da, ...)

So how do I get the Dialect to drop the quoting behavior for the return from bind_processor() ?

> I guess you've never gotten testtypes.py BinaryTest to run with MS-SQL?

Nope. I run so far only with pymssql, which has never had a chance of passing with binary data.

> I would definitely want some test coverage in test/dialect/mssql.py for this, I'm pretty shocked nobody has had this problem before.

If this works, the regular BinaryTest unit test should pass. There's a few other basic tests in typetest.py that still fail for MSSQL. Someday in a dream world where I have time to look at them and fix them, I will.

[Michael Bayer]

On Sep 23, 2008, at 4:24 PM, Rick Morrison wrote:

> class MSBinary(sqltypes.Binary):
> def bind_processor(self, dialect):
> def process(value):
> return '0x' + value.encode('hex')
> return process
>
> ...the issue is that the emitted value is getting quoted somewhere
> after the type conversion:
>
> INSERT INTO binary_table VALUES(...., '0x6df02da', ...)
>
> but MSSQL's parser can recognize the '0x' prefix and wants the value
> unquoted:
>
> INSERT INTO binary_table VALUES(...., 0x6df02da, ...)
>
> So how do I get the Dialect to drop the quoting behavior for the
> return from bind_processor() ?

SQLA doesn't quote bind values. It passes bind parameters, so this is
likely pyodbc assigning quoting.

The first step here is to get a full binary round trip working with
only pyodbc, no SQLA in use. The dialect can then be adjusted to do
whatever is needed in that regard.

[Rick Morrison]

> The first step here is to get a full binary round trip working with
> only pyodbc, no SQLA in use.

Well, that's how I got as far as I did, but that was with straight text, no bind params

> so this is likely pyodbc assigning quoting.

OK then, dead end #2. I'll return to this when I've got a few more minutes.

[Michael Bayer]

other than this seems to be a bug in pyodbc, obviously we're probably
going to have to go with the CONVERT() thing. The path to that,
since I wanted this to work for Postgres GIS functions as well, is a
new method is added to TypeEngine called something like
"bind_converter()", MSBinary provides func.convert() from its
bind_converter() method, and compiler.visit_bindparam() calls upon
this method. A little bit of plumbing would need to exist such that
MSBinary can determine exactly how the func.convert() arguments are
passed. This is something that can be worked out as a patch to be
reviewed, we should add a trac ticket for it.

[John Hampton]

Michael Bayer wrote:
> SQLA doesn't quote bind values. It passes bind parameters, so this is
> likely pyodbc assigning quoting.
>
> The first step here is to get a full binary round trip working with
> only pyodbc, no SQLA in use. The dialect can then be adjusted to do
> whatever is needed in that regard.

Along these lines, I have created a post[1] on the pyodbc Help forum
regarding the unit tests failing for BINARY and a few other types.

I haven't heard anything back yet. I'll create a bug soon if nothing is
forthcoming.

-John

[1] http://sourceforge.net/forum/forum.php?thread_id=2262548&forum_id=550700

[John Hampton]

Rick Morrison wrote:
> > The first step here is to get a full binary round trip working with
> > only pyodbc, no SQLA in use.
>
> Well, that's how I got as far as I did, but that was with straight text,
> no bind params

I did a little more digging into this and found out that pyodbc with
straight hex numbers works fine, but when using a buffer object it borks.

>
> > so this is likely pyodbc assigning quoting.
>
> OK then, dead end #2. I'll return to this when I've got a few more minutes.

It also looks as though this is only a pyodbc issue on linux as all of
the pyodbc tests pass on windows. I'm suspecting some weird behavior
between pyodbc, unixODBC and FreeTDS.

I have opened a ticket on the pyodbc [1] project page and the dev is
going to take a look at it.

Hopefully we'll have an answer soon.

-John

[1]
http://sourceforge.net/tracker/index.php?func=detail&aid=2136938&group_id=162557&atid=824254