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
> {'Test':bytearray(b'a')})
>
> 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
