On Sun, 06 Feb 2011 08:53:54 -0500, Black, Michael (IS)
<michael.bla...@ngc.com> wrote:
> It sounds like you did an sqlite_bind_text instead of sqlite_bind_blob.
> So you'll get the correct # of bytes back but length() thinks its text
> so it truncates at NULL.
Irrelevant to Mr. Goergen’s problem—but to elaborate on what I said
earlier, plenty of SQLite TEXT-handling code will produce incorrect
results for this:
{ 'V', 'a', 'l', 'i', 'd', 0, 'U', 'T', 'F', '-', '8' }
…whereas SQLite will merrily accept this invalid UTF-8 string:
{ 0xEF, 0xBF, 0xBF, 0x3F, 0xC0 }
And not just per the docs:
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE "Test" ("" TEXT);
sqlite> INSERT INTO "Test" VALUES (CAST (X'EFBFBF3FC0' AS TEXT));
sqlite> INSERT INTO "Test" VALUES (CAST (X'6100620063' AS TEXT));
sqlite> SELECT "" FROM "Test";
[snip illegal garbage]
a
sqlite> SELECT length("") FROM "Test";
3
1
sqlite> SELECT typeof("") FROM "Test";
text
text
sqlite>
I understand that both the reliance on zero-termination and the lack of
validation are architectural decisions, and thus “features” rather than
“bugs”. Yet it does bear pointing out that this puts additional burden on
the app to avoid incorrect query results in corner cases.
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 version 3.7.4
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> 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);
> sqlite> insert into message_revision values( null,
> 0,0,0,'sub','cont','html','summary',0,0,'content',x'656500656565656667686970'
>
> );
> sqlite> insert into message_revision values( null,
> 0,0,0,'sub','cont','html','summary',0,0,'content','656500656565656667686970'
>
> );
> sqlite> select *,typeof(Data),length(Data) from message_revision;
> 1|0|0|0|sub|cont|html|summary|0|0|content|ee|blob|12
> 2|0|0|0|sub|cont|html|summary|0|0|content|656500656565656667686970|text|24
>
>
> Michael D. Black
> Senior Scientist
> NG Information Systems
> Advanced Analytics Directorate
>
>
>
> ________________________________________
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Yves Goergen [nospam.l...@unclassified.de]
> Sent: Sunday, February 06, 2011 6:15 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] LENGTH on a BLOB field stops at NUL byte
>
> 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
> 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.
>
> --
> Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de>
> Visit my web laboratory at http://beta.unclassified.de
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
