Re: [pysqlite] Trouble with create_function interface to sqlite

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Raul Garcia Garcia wrote:
> Greetings,
> 
> I've got a problem with the create_function interface in pysqlite2 2.4.1
> 
> I've got a SQLite3 database which have strings encoded in iso-8859-1 rather
> than in utf8. I manage them via a text_factory, but the problem is that if
> I define a function with create_function to apply over a string column, the
> value received by the function is None if the string is not a proper utf8 one.
> [...]

Yes, this is not the nicest part of pysqlite ...

> I've delved into the pysqlite source and seen that pysqlite sets the
> "preferred types"
> parameter of sqlite_create_function to SQLITE_UTF8 instead of SQLITE_ANY. Does
> exist a way to change this parameter? [...]

This would not help at all, because this parameter tells nothing about
encoding, but only about Unicode representation.

The problematic spot in the pysqlite sources is

            case SQLITE_TEXT:
                val_str = (const char*)sqlite3_value_text(cur_value);
                cur_py_value = PyUnicode_DecodeUTF8(val_str,
strlen(val_str), NULL);
                /* TODO: have a way to show errors here */
                if (!cur_py_value) {
                    PyErr_Clear();
                    Py_INCREF(Py_None);
                    cur_py_value = Py_None;
                }
                break;

in connection.c. This always expects that the string we get from SQLite
s UTF-8 encoded, and otherwise it clears the error and silently uses
None instead (urg!). Unfortunately, there is no *easy* way to fix this.

So the only way out here is to make sure the function does not get a
string in the first place. Let it operate on a BLOB (buffer in Python)
instead.

The function would then look like this:

def to_ascii(buf):
    s = str(buf)
    ...


And you'd have to change your SQL call so that the function gets a BLOB
parameter, something like:

select to_ascii(cast(? as blob)) ...

- -- Gerhard
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFIPbh5dIO4ozGCH14RAh0bAJ9WQWHtM2QbSN8ObZ0tE4m4vVW1hwCgg9i4
DK7MC26AAfVKoEUmmH5QZeU=
=J18A
-----END PGP SIGNATURE-----