binary inserts of medium-sized blobs fail with sqlalchemy, mssql, pyodbc

I get the following exception when trying to insert a binary value who's size is between 4001 bytes and 100 x 2^12^==409600 bytes (inclusive):

  File "G:\projects\CFS\tg\cfs\binary_insert_fails.py", line 58, in tryTable
    i.execute( name = name, content_type = content_type, payload = v )
  File "f:\python25\lib\site-packages\SQLAlchemy-0.3.9dev_r2866-py2.5.egg\sqlalchemy\sql.py", line 1198, in execute
    return self.compile(engine=self.engine, parameters=compile_params).execute(*multiparams, **params)
  File "f:\python25\lib\site-packages\SQLAlchemy-0.3.9dev_r2866-py2.5.egg\sqlalchemy\sql.py", line 1087, in execute
    return e.execute_compiled(self, *multiparams, **params)
  File "f:\python25\lib\site-packages\SQLAlchemy-0.3.9dev_r2866-py2.5.egg\sqlalchemy\engine\base.py", line 783, in execute_compiled
    return connection.execute_compiled(compiled, *multiparams, **params)
  File "f:\python25\lib\site-packages\SQLAlchemy-0.3.9dev_r2866-py2.5.egg\sqlalchemy\engine\base.py", line 571, in execute_compiled
    self._execute_raw(context)
  File "f:\python25\lib\site-packages\SQLAlchemy-0.3.9dev_r2866-py2.5.egg\sqlalchemy\engine\base.py", line 584, in _execute_raw
    self._execute(context)
  File "f:\python25\lib\site-packages\SQLAlchemy-0.3.9dev_r2866-py2.5.egg\sqlalchemy\engine\base.py", line 602, in _execute
    raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (Error) ('HY007', '[HY007](HY007) [Microsoft](Microsoft)[SQL Server Driver](ODBC)Associated statement is not prepared (0)') u'INSERT INTO [#files](#files) (name, payload, content_type) VALUES (?, ?, ?)' [<read-only buffer for 0x00EFDF28, size -1, offset 0 at 0x00EA2BC0>, 'type']('name',)

As you can see, I'm using python 2.5.1, sqlalchemy 0.3.9dev_r2866 (with ticket ~~#634~~ patch), pyodbc 2.0.36, and SQL Server 2000 (8.00.194). I do not have pymssql nor adodbapi installed. I cannot test adodbapi due to bug ~~#635~~. I have not tried pymssql.

I'm attaching two files that replicate this behavior. The first demonstrates that the problem occurs in sqlalchemy with or without ORM. The second demonstrates that the problem does not occur in pyodbc directly. All that is required to run these scripts is pyodbc, sqlalchemy, and a connection to a MSSQL database.

Comments (5)