Many thanks for the prompt reply. if you are getting that error on INSERT it sounds like there are driver > issues involved (e.g. pymssql, pyodbc). For an INSERT you should not > get that error; using the VARBINARY SQLAlchemy type, SQLAlchemy calls > upon a construct provided by your DBAPI (e.g., the driver) called > Binary(). This is a wrapper that is intended to signal to the driver > that this value is not a character string, it's bytes. Internally the > driver should be doing this step. > > For here we'd look to see what driver you're using, if changing drivers > resolves, and as always a small and self-contained reproducing test > case. For a driver issue I'd then look to convert the test case to be > using the DB driver alone, then we send a bug report to that driver. >
OK in brief I'm using pymssql. I've never used pyodbc but if it's worth investigating whether it produces the same error I'll find the time to figure out how to use it. In the meantime I have produced a small test script that demonstrates the problem. You need a database already created with something like: CREATE DATABASE test > USE test > CREATE USER user FROM LOGIN user > GRANT CONTROL TO USER > CREATE TABLE Test(Id UNIQUEIDENTIFIER PRIMARY KEY, Test VARBINARY(MAX) not > null) > Then if you run the attached script testvarbinary.py with a single argument that references the database, for example: .\testvarbinary.py mssql+pymssql://user:password@localhost/test > you'll get the following output: Test 1 works > Test 2 fails > Traceback (most recent call last): > File "./testvarbinary.py", line 25, in <module> > {'Test':bytearray(b'a')}) > <snip> > File "pymssql.pyx", line 467, in pymssql.Cursor.execute (pymssql.c:7561) > sqlalchemy.exc.OperationalError: (pymssql.OperationalError) (257, > 'Implicit conversion from data type varchar to varbinary(max) is not > allowed. Use the CONVERT function to run this query.DB-Lib error message > 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL > Server\n') [SQL: 'INSERT INTO [Test] ([Test]) OUTPUT inserted.[Id] VALUES > (%(Test)s)'] [parameters: {'Test': 'a'}] > What I find curious is that although I have tried to cast the value to insert as a bytearray, it seems to have been cast back to a string in the error message. However in some other cases where I obtain a value via SQLAlchemy from a BLOB column in an SQLite DB, which appears to be a byte array, I am able to insert it into an MSSQL VARBINARY(MAX) column without calling CONVERT. Hope that's of some use in tracking down any issue, or perhaps you can see where I am going wrong. Thanks again... Jonathan -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
#!/usr/bin/python # -*- coding: utf-8 -*- import argparse from sqlalchemy import * from sqlalchemy import exc from array import array try: parser = argparse.ArgumentParser(description='Test VARBINARY(MAX) insert operation.') parser.add_argument('db', type=str, help='SQLAlchemy path of database.') args = parser.parse_args() engine = create_engine(args.db) metadata = MetaData(bind=engine) metadata.reflect(engine) table = metadata.tables.get('Test') print('Test 1 works') engine.execute(table.insert().values({'Test':func.CONVERT(literal_column('VARBINARY(MAX)'), bindparam('Test'))}), {'Test':bytearray(b'a')}) print('Test 2 fails') engine.execute(table.insert().values({'Test':bindparam('Test')}), {'Test':bytearray(b'a')}) except exc.SQLAlchemyError: raise