On Jun 15, 2008, at 8:55 AM, Daniel White wrote: > Cheers both of you, it seems this problem is indeed > linked with the "no such collation sequence: iunicode" > error as Dan mentioned. > > After some research, I found out that the root of > the problem is unsurmountable at present. I quote from: > http://www.mediamonkey.com/forum/viewtopic.php?p=84197 > > "It's a real shame that simple queries like "select * > from Songs where SongTitle = 'ABC'" aren't viable. > However, SQLite doesn't have good collation included, > there's absolutely no support for Unicode sorting or > case insensitive comparisons. We will try to do something > about it, but to be honest, I don't know if there's any > easy fix..." > > ...and... > > "IUNICODE is our collation that's there in order to > support Unicode sorting - which SQLite can't do internally." > > Oh well, there are one or two 'hacks' around it. The first is > to use COLLATE BINARY, or COLLATE NOCASE after the SQL query. > This appears okay on the surface, but probably ignores > unicode chars or something. It may also slow down the query (?) > > The other idea is to simply use "LIKE 'xyz'" instead of > "= 'xyz'". To my limited knowledge, apart from the case > sensivity of the latter, these don't differ in the outcome, > because there are no % signs around the former statement. > Although it would be nice, I'm not too bothered about case > sensitivity for my purposes. > > Which solution would you guys recommend?
Just using 'COLLATE BINARY' is a good idea. There is a pretty good chance that memcmp() and whatever is being used for IUNICODE are the same for the '=' operator. If you don't have any non-ASCII characters in the song names, this will almost certainly work. Using COLLATE BINARY will prevent SQLite from using any index created on the song_title column (as the index will have been created using IUNICODE). Or you could copy all the data into a new table - one that uses only the default available collation sequences: CREATE TEMP TABLE my_songs AS SELECT * FROM songs; then query my_songs instead of songs. That wouldn't help you any more than using COLLATE binary explicitly in every query though. Or you could ask the vendor for the source code to the IUNICODE collation function. Using SQLite's ICU extension to try to create an equivalent collation sequence is also possible, but a bit dangerous. If the collation sequence you create turns out to be "mostly compatible" instead of "completely compatible", then you might wind up with segfaults or a corrupted database at some point in the future. Dan. > Cheers, Dan > > > >> And you are saying the statement >> >> SELECT SongTitle FROM songs WHERE SongTitle='Hexion'; >> >> doesn't return any rows? > > Correct. It's weird I know. I also tried with different > names in different fields (Artist etc.), and I get the > same problem. "like" is okay, but = doesn't work. > > Here are the files again: > http://www.skytopia.com/stuff/MMdatabase.zip (1.6 M) > http://www.skytopia.com/stuff/sqlite.cpp (1k) > > Dan > > > On Sat, 14 Jun 2008 15:54:50 +0100, Igor Tandetnik > <[EMAIL PROTECTED]> > wrote: > >> "Daniel White" <[EMAIL PROTECTED]> >> wrote in message news:[EMAIL PROTECTED] >>>> Which way is it stored in the database? Show the output of this >>>> statement: >>>> >>>> SELECT SongTitle FROM songs WHERE SongTitle like 'hexion'; >>> >>> There are 8 records of Hexion in the database, so after a printout >>> to the console with a carriage return after each value, I basically >>> get: >>> Hexion >>> Hexion >>> Hexion >>> Hexion >>> Hexion >>> Hexion >>> Hexion >>> Hexion >> >> And you are saying the statement >> >> SELECT SongTitle FROM songs WHERE SongTitle='Hexion'; >> >> doesn't return any rows? With all due respect, I find it difficult to >> believe. Would it be possible for you to email a copy of the database >> file to me at [EMAIL PROTECTED] ? Perhaps a stripped-down version, >> with just enough data to reproduce the problem. >> >> Igor Tandetnik >> >> >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > www.skytopia.com > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
