Unicode handling in insertion statements

[Joachim Selke]

Hi,

I get an error when trying to insert unicode strings into tables using
SQLAlchemy. To be more precise, when trying to execute

mytable.insert().execute({'value': unicode('X')})

I get this error:

sqlalchemy.exceptions.DatabaseError: (DatabaseError)
ibm_db_dbi::DatabaseError: Binding Error: 'INSERT INTO test (value)
VALUES (?)' [u'X']

It works when I remove the unicode function. Is this behavior
intended?

I prepared a test case:

============================================
#!/usr/bin/env python

uri = 'MYDBCONFIG'

import sqlalchemy
from sqlalchemy import *

metadata = MetaData()

mytable = Table('test', metadata,
Column('id', Integer, primary_key = True),
Column('value', String(16), nullable = False)
)

db2 = sqlalchemy.create_engine(uri, echo=True)
metadata.bind = db2

mytable.drop(checkfirst=True)
mytable.create(checkfirst=True)

# this works
mytable.insert().execute({'value': 'X'})

# this raises an error
mytable.insert().execute({'value': unicode('Y')})
============================================

Here is the complete output when running the above code:

============================================
2008-11-16 21:14:56,753 INFO sqlalchemy.engine.base.Engine.0x..d0
DROP TABLE test
INFO:sqlalchemy.engine.base.Engine.0x..d0:
DROP TABLE test
2008-11-16 21:14:56,753 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
INFO:sqlalchemy.engine.base.Engine.0x..d0:{}
2008-11-16 21:14:57,171 INFO sqlalchemy.engine.base.Engine.0x..d0
COMMIT
INFO:sqlalchemy.engine.base.Engine.0x..d0:COMMIT
2008-11-16 21:14:57,498 INFO sqlalchemy.engine.base.Engine.0x..d0
CREATE TABLE test (
id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
value VARCHAR(16) NOT NULL,
PRIMARY KEY (id)
)


INFO:sqlalchemy.engine.base.Engine.0x..d0:
CREATE TABLE test (
id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
value VARCHAR(16) NOT NULL,
PRIMARY KEY (id)
)


2008-11-16 21:14:57,499 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
INFO:sqlalchemy.engine.base.Engine.0x..d0:{}
2008-11-16 21:14:57,741 INFO sqlalchemy.engine.base.Engine.0x..d0
COMMIT
INFO:sqlalchemy.engine.base.Engine.0x..d0:COMMIT
2008-11-16 21:14:57,871 INFO sqlalchemy.engine.base.Engine.0x..d0
INSERT INTO test (value) VALUES (?)
INFO:sqlalchemy.engine.base.Engine.0x..d0:INSERT INTO test (value)
VALUES (?)
2008-11-16 21:14:57,871 INFO sqlalchemy.engine.base.Engine.0x..d0
['X']
INFO:sqlalchemy.engine.base.Engine.0x..d0:['X']
2008-11-16 21:14:58,453 INFO sqlalchemy.engine.base.Engine.0x..d0
COMMIT
INFO:sqlalchemy.engine.base.Engine.0x..d0:COMMIT
2008-11-16 21:14:58,584 INFO sqlalchemy.engine.base.Engine.0x..d0
INSERT INTO test (value) VALUES (?)
INFO:sqlalchemy.engine.base.Engine.0x..d0:INSERT INTO test (value)
VALUES (?)
2008-11-16 21:14:58,584 INFO sqlalchemy.engine.base.Engine.0x..d0
[u'Y']
INFO:sqlalchemy.engine.base.Engine.0x..d0:[u'Y']
2008-11-16 21:14:58,649 INFO sqlalchemy.engine.base.Engine.0x..d0
ROLLBACK
INFO:sqlalchemy.engine.base.Engine.0x..d0:ROLLBACK
Traceback (most recent call last):
File "./ibm_db_unicode.py", line 22, in <module>
mytable.insert().execute({'value': unicode('Y')})
File "/usr/lib/python2.5/site-packages/sqlalchemy/sql/
expression.py", line 1087, in execute
return e.execute_clauseelement(self, multiparams, params)
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 1219, in execute_clauseelement
return connection.execute_clauseelement(elem, multiparams, params)
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 895, in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) > 1), distilled_params=params)
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 907, in _execute_compiled
self.__execute_raw(context)
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 916, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 960, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor)
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py",
line 942, in _handle_dbapi_exception
raise exceptions.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exceptions.DatabaseError: (DatabaseError)
ibm_db_dbi::DatabaseError: Binding Error: 'INSERT INTO test (value)
VALUES (?)' [u'Y']
============================================

My database configuration:
DB2 9.5 (Fixpack 2a) running on CentOS 5.2 (comptible to Red Hat
Enterprise Linux 5.2)

My software:
Python 2.5.1
SQLAlchemy 0.4.7
Latest releases of ibm_db packages

Best,
Joachim

[abhigyan...@in.ibm.com]

Hi Joachim,
Unicode is not yet supported on ibm_db_sa. I have it in my
future plans and you will see unicode support in near future.

Thanks,
Abhigyan