Trouble inserting Binary into MSSQL DB's

[widme...@gmail.com]

I am having a problem inserting binary data into an existing MS DB.

A very simple example is like this

class FooTable:
__tablename__ = 'mytable'
keycol = Column(String(15), nullable=False, primary_key=True)
bincol = Column(Binary(4), nullable=False)

def __init__(self, keyval, binval):
self.keycol = keyval
self.bincol = binval

s (this is my session)

new = FooTable('row1')
s.add(new)
s.commit()

At this point I get a server error like so:
"""
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.
"""
I have tried everything I can think of ["",0,0x0,'0x0',hex(0)] as
binval for this col and always come back to the same error.

For my setup, I am on SA .5 with pymssql on MacOSX 10.5. I did see
the notice about some non-specific binary issues with pymssql, but
nothing indicated it flat out didn't work.

I am hoping someone can tell me what I'm missing here.

Thanks,

Sam Widmer

[Michael Bayer]

I dont use MS-SQL, but I'm going to go out on a limb here based on
what I've read and say that you should really be using pyodbc.
pymssql is apparently very old and unmaintained and appears to have
poor support for non-ASCII streams.

to the list: perhaps mssql.py should emit a warning when pymssql is
being used ?

[Rick Morrison]

There's no doubt that pyodbc is the better-supported option; the pymssql module hasn't been updated in two years, and it relies on the Microsoft DB lib, which has been deprecated for a long, long time and is no longer supported, and may not even work with MSSQL 2008. Here's the deprecation notice from MSSQL 2000 docs, and I remember earlier warnings from MS way back in the MSSQL 7.0 days

 (http://msdn.microsoft.com/en-us/library/aa936940.aspx).

That said, pymssql does have some things going in its favor: it's simpler to set up on *nix OS's than an iODBC/FreeTDS setup, and it's arguably the best performing option for MSSQL.

> appears to have poor support for non-ASCII streams.

Pretty much, yeah. It is ignorant of the high-order bit on 8-bit characters and just passes it through, much like a C memcpy() might, but there is certainly no support at all for unicode: The MS-DBlib is essentially an MS riff of the old Sybase DBlib, which was floating around well before the first published Unicode standard in 1991.

Anyway, back to the issue at hand - the original SQLalchemy MSSQL module was written against a pretty old version of pymssql (prior to .7.2) and that old version had pretty weak support for Binary columns. The SQLA MSSQL module dealt with that by monkeypatching a handler that replaces a Binary column with a String column, which is almost certainly the cause of the problem at hand here.

It may be possible to fix that issue, but I'm not sure I want to invest any more effort into pymssql as it's clearly a dead duck moving forward.  Should we? I'm not sure why we continue to see list users trying out pymssql - is there still interest in that path for a reason, or was it just the first hit on a Google search for python+mssql?

Rick

[Sam Widmer]

Thanks for the responses so far. 

The reason we tried pymssql is because it is much easier to get running on OSX.  At the moment our efforts to get pyodbc working on Macs have met with alot of frustration.

However, we would have done things differently if we had seen different wording in the SqlAlchemy docs.  For instance, a notice that binary fields do not work with pymssql.  This would have put us down the pyodbc path right away.  Instead, I found the non-specific statement in the MSSQL Known Issues section.  I then searched for what those issues were and found plenty of Unicode Type issues but nothing really relating to Binary types.

So, with all the above in mind we are proceeding down the pyodbc path. 

For the project, I am not asking for any work to be done on pymssql but rather that the Known Issues section be clarified to state that Binary Types do not work.

[Michael Bayer]

the quickest path here would be for you to edit the wiki directly -
log in as "guest/guest". We put the database issues there
specfically so it could be a collaborative document drawing upon the
communities' experience directly.

[Sam Widmer]

Thanks I'll go ahead and do that.