On Sun, 06 Feb 2011 07:15:42 -0500, Yves Goergen
<[email protected]> wrote:
> On 06.02.2011 12:01 CE(S)T, Simon Davies wrote:
>> length() should return the number of bytes for a blob.
>> What does typeof() tell you about the data?
>
> It says "text". Now tested with SQLite 3.7.4, this is the only command
Question for SQLite devs: Any plans to support strict affinity? I seem
to recollect a mention of this in the docs at some point. If not, then
perhaps add a tip like the following to the FAQ.
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. (This usage of the
IS operator is relatively recent SQLite; use = otherwise. I use IS in
case you accidentally reference an invalid column name or whatnot; I am
not absolutely sure this is necessary.) N.b., as a side effect it will
render the column NOT NULL. If the column can be NULL, do the constraint
as such:
CHECK (typeof("Data") IN ('blob', 'null'))
Yes, it will slow down INSERTs a bit; data integrity is more important, as
you have just seen.
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
> line client I have here.
>
> Here's the statement how the table was created:
>
>> CREATE TABLE "message_revision" (
>> "MessageRevisionId" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
>> "MessageId" INTEGER NOT NULL REFERENCES "message" ("MessageId") ON
>> DELETE CASCADE,
>> "CreatedTime" DATETIME NOT NULL,
>> "Author" INTEGER NOT NULL REFERENCES "user" ("UserId"),
>> "Subject" VARCHAR(255),
>> "Content" MEDIUMTEXT,
>> "HtmlContent" MEDIUMTEXT,
>> "Summary" VARCHAR(255),
>> "ModerationState" TINYINT NOT NULL DEFAULT 0,
>> "Draft" BOOLEAN NOT NULL DEFAULT FALSE,
>> "ContentType" VARCHAR(255),
>> "Data" BLOB);
>
> The column in question is "Data".
>
> I can verify that the entire image file has been stored as it is
> entirely returned in a SELECT query and displayed in the web browser.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
