On Sun, 06 Feb 2011 07:15:42 -0500, Yves Goergen <nospam.l...@unclassified.de> 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users