--- 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]
-----------------------------------------------------------------------------
