pyodbc doesn't handle Unicode strings on Ubuntu's Python build

[Adam Seering]

This version of pyodbc assumes that it is running on a version of Python that uses UCS-2 Unicode encoding internally. Ubuntu's Python uses UCS-4.

As a result, the last line of the following code snippet typically fails with some sort of strange error from the host database:

>>> import pyodbc
>>> conn=pyodbc.connect(DSN='MyDSN')
>>> cur=conn.cursor()
>>> cur.execute("insert into t values (?);", ['a'])
>>> cur.execute(u"insert into t values (?);", ['a'])

(Expected: The value is inserted into the table. Specifically, the last two lines should produce exactly the same result. Actual: The second-to-last line executes without error; the last line throws some driver-dependent error indicating that the query was invalid.)

The underlying issue is, pyodbc doesn't transcode; unixODBC is handed a raw UCS-4 string when it expects a UCS-2 string, and unfortunateness ensues. This particular bug is due to the implementation of PrepareAndBind() in pyodbc's src/params.cpp. There are other related bugs due to the same programming design pattern used elsewhere in the source.

This issue is fixed upstream in pyodbc 3.0.2. Git commit c278e74e appears to be important. (Also large.)

I don't know if a backport is feasible? I figure I should at least post the bug somewhere so that it's known.

It could be argued that this is a bug in Ubuntu's Python packaging; that Ubuntu's Python package should use UCS-2. I don't think that's a winning argument; I think this issue belongs with pyodbc.

Bug info:

$ lsb_release -rd
Description: Ubuntu 12.04.4 LTS
Release: 12.04

$ apt-cache policy python-pyodbc
python-pyodbc:
  Installed: 2.1.7-1build2
  Candidate: 2.1.7-1build2
  Version table:
 *** 2.1.7-1build2 0
        500 http://us.archive.ubuntu.com/ubuntu/ precise/universe amd64 Packages
        100 /var/lib/dpkg/status

$ apt-cache policy python
python:
  Installed: 2.7.3-0ubuntu2.2
  Candidate: 2.7.3-0ubuntu2.2
  Version table:
 *** 2.7.3-0ubuntu2.2 0
        500 http://us.archive.ubuntu.com/ubuntu/ precise-updates/main amd64 Packages
        100 /var/lib/dpkg/status
     2.7.3-0ubuntu2 0
        500 http://us.archive.ubuntu.com/ubuntu/ precise/main amd64 Packages

$ apt-cache policy unixodbc
unixodbc:
  Installed: 2.2.14p2-5ubuntu3
  Candidate: 2.2.14p2-5ubuntu3
  Version table:
 *** 2.2.14p2-5ubuntu3 0
        500 http://us.archive.ubuntu.com/ubuntu/ precise/main amd64 Packages
        100 /var/lib/dpkg/status