On Sun, 06 Feb 2011 08:10:49 -0500, Yves Goergen
<nospam.l...@unclassified.de> wrote:
> On 06.02.2011 13:52 CE(S)T, Samuel Adam wrote:
>> For the original poster: To prevent problems like this, if it will
>> always
>> be a BLOB then add a constraint to your table:
>>
>> CHECK (typeof("Data") IS 'blob')
>>
>> Your INSERTs will fail if inserting TEXTual data then.
>
> I don't want my INSERT to fail, I want SQLite to see that I'm inserting
> binary data. There is only one way to insert data with PHP/PDO. I give
> it binary data and it's supposed to do it right.
Incorrect. SQLite has no way to know what you are inserting, unless it is
told. Remember that a computer’s heart and brains are dumb melted sand,
fancily arranged; a userland library such as SQLite can’t add psychic
powers to that.
I not too familiar with PDO; check its docs. Tips on what to look for:
* You should be using bound parameters on INSERT. If you are not,
change
your code. This will eliminate a whole list of potential problems.
* Make sure the binding is done as BLOB and not TEXT. PDO probably has
its own flags defined for this. This is the part that tells SQLite
whether you are inserting TEXT or BLOB.
Moreover:
* Per its docs, SQLite does not validate UTF-(8|16) TEXT. However, a 0
byte is technically valid UTF-8 (and typical UTF-16 is chock-full of 0
bytes). Note to devs: I know that SQLite’s TEXT code relies heavily on
zero-termination; this does mean that length() can mysteriously fail on
valid UTF-8. This is of course irrelevant to the OP’s problem, because
length() counts characters for TEXT and bytes for BLOB (and thus can
sometimes return *very* *subtly* different numbers—Mr. Goergen, take note).
* From an architectural perspective, it is better for an INSERT to fail
than to experience mysterious problems such as length() only reading up to
a 0 byte. If you are inserting a GIF image as TEXT, then failure will
occur at some point; adding the CHECK constraint I suggested forces the
failure to occur (a) early on, (b) in a well-defined manner, and, (c) with
an error (code 19 if memory serves, “constraint failed”) rather than an
incorrect result.
Very truly,
Samuel Adam ◊ <http://certifound.com/>
763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
