by Sproaticus » Mon Apr 21, 2008 7:50 pm
I haven't tested this against the behavior of MM's IUNICODE collation, but this seems to work in
Python 2.5 using the sqlite3 module:
Code: Select all
import sqlite3
# define IUNICODE collation function
def iUnicodeCollate(s1, s2):
return cmp(s1.lower(), s2.lower())
# connect to database
conn = sqlite3.connect('MM.DB')
# register our custom IUNICODE collation function
conn.create_collation('IUNICODE', iUnicodeCollate)
# run your query, business as usual
cursor = conn.execute('select * from Songs')
# ...etc...
The important bits are the definition of the
iUnicodeCollate() function, and using
conn.create_collation() to apply it to the db connection.
And like I said, I don't know if this behavior matches that of the one implemented in MediaMonkey. It does work without any errors, and behaves how I think it should behave, which is good enough for me.
If the Sqlite API in VB lets you define custom collations, this trick may work there as well. Unfortunately, I'm not well-versed in VB, and since my technique doesn't use ODBC I'm not even sure it'll translate to VB.
Some relevant links:
Sqlite docs on defining new collating sequences
Python API docs for Sqlite connection objects
Forum post by jiri in "sqlite db query error" which pointed me to the SQLite3_CreateCollation16() function.
I haven't tested this against the behavior of MM's IUNICODE collation, but this seems to work in [url=http://www.python.org/]Python 2.5[/url] using the sqlite3 module:
[code]import sqlite3
# define IUNICODE collation function
def iUnicodeCollate(s1, s2):
return cmp(s1.lower(), s2.lower())
# connect to database
conn = sqlite3.connect('MM.DB')
# register our custom IUNICODE collation function
conn.create_collation('IUNICODE', iUnicodeCollate)
# run your query, business as usual
cursor = conn.execute('select * from Songs')
# ...etc...
[/code]
The important bits are the definition of the [b]iUnicodeCollate()[/b] function, and using [b]conn.create_collation()[/b] to apply it to the db connection.
And like I said, I don't know if this behavior matches that of the one implemented in MediaMonkey. It does work without any errors, and behaves how I think it should behave, which is good enough for me. :)
If the Sqlite API in VB lets you define custom collations, this trick may work there as well. Unfortunately, I'm not well-versed in VB, and since my technique doesn't use ODBC I'm not even sure it'll translate to VB.
Some relevant links:
[url=http://www.sqlite.org/c3ref/create_collation.html]Sqlite docs on defining new collating sequences[/url]
[url=http://docs.python.org/lib/sqlite3-Connection-Objects.html]Python API docs for Sqlite connection objects[/url]
[url=http://www.mediamonkey.com/forum/viewtopic.php?t=15952&highlight=iunicode]Forum post by [b]jiri[/b] in "sqlite db query error"[/url] which pointed me to the SQLite3_CreateCollation16() function.