On Tue, 2011-01-04 at 08:06 +0000, James Henstridge wrote:
> If I edit the test program to insert the test program to insert the row
> as:
>
> db.execute("INSERT INTO foo (name) VALUES (?)", (buffer("bla"), ))
>
> Then the assertion doesn't fire. If I poke around in the resulting
> database, I can't select the row by string comparison:
>
> sqlite> select * from foo;
> 1|bla
> sqlite> select * from foo where name = 'bla';
> sqlite>
>
> I guess sqlite is treating blobs and text strings as distinct here. If
> you insert the data as blobs, then you can't match it against an
> equivalent text string and vice versa.
Yeah, that's what I found later as well. I'm currently using Cast() to
work around this issue, basically:
SELECT * from foo WHERE CAST(name AS BLOB) = CAST('bla' AS BLOB);
The dynamic typing in SQLite is a bit annoying here, and as far as I
know there is no way to disable it. :-/
On Tue, 2011-01-04 at 08:06 +0000, James Henstridge wrote:
> If I edit the test program to insert the test program to insert the row
> as:
>
> db.execute("INSERT INTO foo (name) VALUES (?)", (buffer("bla"), ))
>
> Then the assertion doesn't fire. If I poke around in the resulting
> database, I can't select the row by string comparison:
>
> sqlite> select * from foo;
> 1|bla
> sqlite> select * from foo where name = 'bla';
> sqlite>
>
> I guess sqlite is treating blobs and text strings as distinct here. If
> you insert the data as blobs, then you can't match it against an
> equivalent text string and vice versa.
Yeah, that's what I found later as well. I'm currently using Cast() to
work around this issue, basically:
SELECT * from foo WHERE CAST(name AS BLOB) = CAST('bla' AS BLOB);
The dynamic typing in SQLite is a bit annoying here, and as far as I
know there is no way to disable it. :-/
Cheers,
Jelmer