Re: [sqlalchemy] Inserting VARBINARY(MAX) columns with MSSQL/SQLAlchemy

[Mike Bayer]

On 10/05/2016 12:38 AM, Jonathan Schultz wrote:

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.


pymssql is overall a much better driver these days as it is actively 
maintained, pyodbc seems like it isn't maintained very often.


However, for this one, pymssql is failing and pyodbc isn't, so I'd 
advise reporting this upstream to the pymssql driver.  See attached.  As 
a workaround, you can supply CONVERT yourself as part of the datatype:


from sqlalchemy import *

e = create_engine("mssql+pymssql://scott:tiger@192.168.122.135:1213", 
echo=True)



class MyVarBinary(TypeDecorator):
impl = VARBINARY

def bind_expression(self, bindvalue):
return cast(bindvalue, VARBINARY)

m = MetaData()

t = Table(
'test', m,
Column('id', Integer, primary_key=True),
Column('x', MyVarBinary())
)

conn = e.connect()
trans = conn.begin()

t.create(conn)
conn.execute(
t.insert().values({'x':bindparam('x')}),
{'x': b'a'}
)


output:

INSERT INTO test (x) OUTPUT inserted.id VALUES (CAST(%(x)s AS 
VARBINARY(max))









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.


--
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.

def run_test(dbapi, conn):
cursor = conn.cursor()

cursor.execute(
"CREATE TABLE t(id INTEGER PRIMARY KEY, x VARBINARY(MAX) not null)"
)

try:
if dbapi.paramstyle == 'qmark':
cursor.execute(
"insert into t (id, x) values (?, ?)",
(1, dbapi.Binary(b'a'))
)
else:
   

Re: [sqlalchemy] Inserting VARBINARY(MAX) columns with MSSQL/SQLAlchemy

[Jonathan Schultz]

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

Re: [sqlalchemy] Inserting VARBINARY(MAX) columns with MSSQL/SQLAlchemy

[Mike Bayer]

On 09/28/2016 02:32 AM, jonathan.schu...@gmail.com wrote:

Usual apologies for newbie question...

I'm trying to use SQLAlchemy to insert data into a MSSQL DB with columns
of type VARBINARY(MAX). The only way I could find to avoid the error message

 Implicit conversion from data type varchar to varbinary(max) is not
allowed. Use the CONVERT function to run this query.

wais to wrap the bound column in CONVERT(VARBINARY(MAX), ...). This
however creates problems when I want to use the same code on other
database types. I guess I could work around this issue by manually
checking the database dialect, but since one purpose of SQLAlchemy
appears to be to help write DB agnostic code, I would hope that this
problem could be solved in a cleaner way.

Many thanks for any suggestions.


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.





--
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.


--
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.

[sqlalchemy] Inserting VARBINARY(MAX) columns with MSSQL/SQLAlchemy

[jonathan . schultz]

Usual apologies for newbie question...

I'm trying to use SQLAlchemy to insert data into a MSSQL DB with columns of 
type VARBINARY(MAX). The only way I could find to avoid the error message

>  Implicit conversion from data type varchar to varbinary(max) is not 
> allowed. Use the CONVERT function to run this query.
>
wais to wrap the bound column in CONVERT(VARBINARY(MAX), ...). This however 
creates problems when I want to use the same code on other database types. 
I guess I could work around this issue by manually checking the database 
dialect, but since one purpose of SQLAlchemy appears to be to help write DB 
agnostic code, I would hope that this problem could be solved in a cleaner 
way.

Many thanks for any suggestions.

-- 
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.