--- Brett Keating <[EMAIL PROTECTED]> wrote: > Actually this wasn't the issue after all... Indices have nothing to do > with it. > > The genre was being inserted from two different sources. It is a UTF-16 > string, and in one case it was being inserted with a null terminator, > and in another case it was not. Since I used "sqlite3_bind_text16" and > specified a length that included the null terminator, it was stored in > the database with that null terminator. > > Unfortunately when I do this, the string that I get back from the > database is of length -1 compared to what I inserted. So for example if > genre is "Rock" and I inserted "Rock0" where 0 is null terminator, I get > "Rock" back and not "Rock0." > > Note below that POP is reported as 3 characters long, but was inserted > as 4 with a null terminator. > > Interestingly enough, sqlite3 will give me two copies of POP when I ask > for unique genres, if I insert a value as "POP" and another value as > "POP0." > > So in a sense this was merely user error but also an interesting > idiosyncracy of the sqlite3 database.
If you had inserted the text with the null terminator as a blob, then sqlite would have reported it as follows: SQLite version 3.3.17 Enter ".help" for instructions sqlite> create table t1(a text); sqlite> insert into t1 values(x'504F5000'); sqlite> select * from t1; POP sqlite> select a, typeof(a), hex(a), length(a) from t1 where a LIKE '%POP'; POP|blob|504F5000|4 But I guess sqlite has to take your word for it for UTF strings when you supply a length that is wrong. The other option is that sqlite could convert UTF strings with embedded nulls to blobs. Not sure what is the better option. ____________________________________________________________________________________Get the free Yahoo! toolbar and rest assured with the added security of spyware protection. http://new.toolbar.yahoo.com/toolbar/features/norton/index.php ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------