Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Stephan Beal]

On Thu, Feb 10, 2011 at 11:22 PM, Samuel Adam  wrote:

> Thanks for actually looking this up.


i didn't look THAT closely, as you found out:


> Worse, PDO::PARAM_LOB is for binding a stream and not a regular variable:
> http://www.php.net/manual/en/pdo.lobs.php


Doh!

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Samuel Adam]

On Thu, 10 Feb 2011 16:38:40 -0500, Stephan Beal   
wrote:

> On Sun, Feb 6, 2011 at 2:36 PM, Samuel Adam  wrote:
>
>>
>>* 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.
>>
>
> http://www.php.net/manual/en/pdostatement.bindparam.php
>
> When inserting the GIF, the 3rd argument to bindParam() must be
> PDO::PARAM_LOB  (not BLOB, interestingly). The default is PDO::PARAM_STR,
> which almost certainly results in the OP's frustration.

Thanks for actually looking this up.  But on Sun, 06 Feb 2011 10:53:05  
-0500, Yves Goergen  actually said he was  
using “a PDO method to execute a prepared statement with an array of  
values to be used as parameters”.  That appears to be this:

http://www.php.net/manual/en/pdostatement.execute.php

input_parameters

An array of values with as many elements as there are bound
parameters in the SQL statement being executed. All values are
treated as PDO::PARAM_STR.

In other words, he is using a PHPism which hands off only one type and  
then blaming SQLite for treating his data as the wrong type.  (That  
interface is bizarre, by the way:  Either you can bind the parameters  
yourself and then call it with no arguments, or bind nothing yourself and  
pass it an array of values which will be treated as text.  Mr. Goergen  
appears to be doing the latter.)

Worse, PDO::PARAM_LOB is for binding a stream and not a regular variable:
http://www.php.net/manual/en/pdo.lobs.php
And it is for “objects [] either textual or binary in nature” (Id.).  As  
usual with PHP, the docs are very imprecise on what the database will  
actually get.  “Example #14[:] Inserting an image into a database” thereby  
(based on an fopen() stream) looks promising, but still does not document  
the type told to the database.  If there is a way anywhere in PDO to  
explicitly hand SQLite a BLOB type and/or so do from a variable, the PDO  
constants list
http://www.php.net/manual/en/pdo.constants.php
fails to disclose it.

PHP’s SQLite3 class does have a SQLITE3_BLOB flag.  But that is not what  
Mr. Goergen is using.

Easiest solution:  The UPDATE and triggers suggested on Mon, 07 Feb 2011  
03:16:54 -0500 by Philip Graham Willoughby  
.  Best solution:  Reading and  
understanding the docs, particularly as to SQLite’s type system, and then  
making a few thoughtful decisions as to architecture.

Very truly,

Samuel Adam ◊ 
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

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Stephan Beal]

On Sun, Feb 6, 2011 at 2:36 PM, Samuel Adam  wrote:

>
>* 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.
>

http://www.php.net/manual/en/pdostatement.bindparam.php

When inserting the GIF, the 3rd argument to bindParam() must be
PDO::PARAM_LOB  (not BLOB, interestingly). The default is PDO::PARAM_STR,
which almost certainly results in the OP's frustration.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Samuel Adam]

On Thu, 10 Feb 2011 15:21:57 -0500, Yves Goergen  
 wrote:

> On 07.02.2011 23:47 CE(S)T, Samuel Adam wrote:
>> On Mon, 07 Feb 2011 03:16:54 -0500, Philip Graham Willoughby
>>  wrote:
>>> What about:
>>>
>>> UPDATE "message_revision" SET "Data" = CAST ("Data" AS BLOB);
>>
>> Y’know the urban legend about the folks at the restaurant who design a
>> complicated plan for switching the contents of salt and pepper shakers
>> which have their hats juxtaposed, and then a passer-by suggests just
>> switching the caps?
>
> I don't know that story nor do I understand it. But would that trigger
> thing work? That would be something I could set up easily.

Off-topic not-quite-analogy by way of jest at self.  I suggested a  
temporary workaround which was a hair’s breadth from permanently fixing  
your existing data; Mr. Willoughby pointed that out with the above-quoted  
UPDATE line.  As to Mr. Willoughby’s triggers (which you snipped), yes,  
they look quite correct and you should try them.

> It's not that important issue for my anymore. I've simply decided to not
> store binary data in the SQLite database in this application anymore but
> instead write it to disk. Storing files in the database should only be
> of interest here if you cannot write to disk, but when using SQLite you
> obviously can do that.

For the archives, I emphasize hereby again that SQLite is not 8-bit  
crippled.  I insert and retrieve ordinary-sized images, audio, PDFs,   
 from SQLite on a regular basis; I don’t have problems, because I bind such  
things as BLOB.  I also oft use core function length() on BLOB values,  
including those containing NUL bytes, again without problems.

If I saw a bug in SQLite, I’d call it that point-blank.  Indeed, I did  
take the opportunity to raise what I argue is a documented design flaw  
(which only affects corner cases and is absolutely irrelevant to your  
problem).  But your problem simply was not caused by any incorrectness in  
SQLite.  As amply explained with reference to docs, you told SQLite you  
were inserting TEXT; so length() complied with its documented behavior for  
measuring the length of TEXT:  It counted Unicode characters, *not bytes*,  
up to and not including the first U+.

Very truly,

Samuel Adam ◊ 
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

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Yves Goergen]

On 07.02.2011 23:47 CE(S)T, Samuel Adam wrote:
> On Mon, 07 Feb 2011 03:16:54 -0500, Philip Graham Willoughby  
>  wrote:
>> What about:
>>
>> UPDATE "message_revision" SET "Data" = CAST ("Data" AS BLOB);
> 
> Y’know the urban legend about the folks at the restaurant who design a  
> complicated plan for switching the contents of salt and pepper shakers  
> which have their hats juxtaposed, and then a passer-by suggests just  
> switching the caps?

I don't know that story nor do I understand it. But would that trigger
thing work? That would be something I could set up easily.

It's not that important issue for my anymore. I've simply decided to not
store binary data in the SQLite database in this application anymore but
instead write it to disk. Storing files in the database should only be
of interest here if you cannot write to disk, but when using SQLite you
obviously can do that.

-- 
Yves Goergen "LonelyPixel" 
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

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Samuel Adam]

On Mon, 07 Feb 2011 03:16:54 -0500, Philip Graham Willoughby  
 wrote:

>>SELECT length(CAST ("Data" AS BLOB)) FROM "message_revision";
>
> What about:
>
> UPDATE "message_revision" SET "Data" = CAST ("Data" AS BLOB);

Y’know the urban legend about the folks at the restaurant who design a  
complicated plan for switching the contents of salt and pepper shakers  
which have their hats juxtaposed, and then a passer-by suggests just  
switching the caps?

Thank you, Mr. Willoughby!

Very truly,

Samuel Adam ◊ 
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


> As a one-time command to correct the table. There is also the option of  
> using triggers to ensure future "Data" values are kept as BLOB if the  
> INSERT code cannot be corrected conveniently:
>
> CREATE TRIGGER trigName AFTER INSERT ON "message_revision" WHEN  
> TYPEOF("Data")!='blob' BEGIN UPDATE "message_revision" SET "Data" = CAST  
> ("Data" AS BLOB) WHERE "message_revision".rowid = NEW.rowid; END;
>
> CREATE TRIGGER trigName2 AFTER UPDATE OF "Data" ON "message_revision"  
> WHEN TYPEOF("Data")!='blob' BEGIN UPDATE "message_revision" SET "Data" =  
> CAST ("Data" AS BLOB) WHERE "message_revision".rowid = NEW.rowid; END;
> ___
> 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

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Philip Graham Willoughby]

>SELECT length(CAST ("Data" AS BLOB)) FROM "message_revision";

What about:

UPDATE "message_revision" SET "Data" = CAST ("Data" AS BLOB);

As a one-time command to correct the table. There is also the option of using 
triggers to ensure future "Data" values are kept as BLOB if the INSERT code 
cannot be corrected conveniently:

CREATE TRIGGER trigName AFTER INSERT ON "message_revision" WHEN 
TYPEOF("Data")!='blob' BEGIN UPDATE "message_revision" SET "Data" = CAST 
("Data" AS BLOB) WHERE "message_revision".rowid = NEW.rowid; END;

CREATE TRIGGER trigName2 AFTER UPDATE OF "Data" ON "message_revision" WHEN 
TYPEOF("Data")!='blob' BEGIN UPDATE "message_revision" SET "Data" = CAST 
("Data" AS BLOB) WHERE "message_revision".rowid = NEW.rowid; END;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Samuel Adam]

On Sun, 06 Feb 2011 11:54:37 -0500, Samuel Adam   
wrote:

> On Sun, 06 Feb 2011 11:41:03 -0500, Teg  wrote:
>
>> Hello Yves,
>>
>> You could alway mime/uu/yenc encode it into text before insert, and do
>> the reverse when you retrieve it. Then the problem goes away.
>
> No, it doesn’t:  Then SQLite *really* has no way of telling the byte
> length of the value,

I take that back:  Those desiring the Rube Goldberg route to data bliss  
may code up mime/uu/yenc encode/decode SQLite extensions, compile as  
dynamic libraries, load on the database connection using appropriate PHP  
calls (does PDO support this? SQLite3 class does), and proceed thenceforth  
as such:

SELECT length(uudecode(mousetrap("Data"))) FROM "message_revision";

Or, take the easier route:  Bind as a BLOB, and use the constraint I  
suggested earlier to prevent future mistyping of data.

For a quick workaround on the existing database, Mr. Goergen may also try  
this:

SELECT length(CAST ("Data" AS BLOB)) FROM "message_revision";

Tested:

SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE "" ("");
sqlite> INSERT INTO "" VALUES (CAST (X'61006263' AS TEXT));
sqlite> SELECT typeof("") FROM "";
text
sqlite> SELECT length("") FROM "";
1
sqlite> SELECT length(CAST ("" AS BLOB)) FROM "";
4
sqlite>

Very truly,

Samuel Adam ◊ 
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


> which SQL core function length() does perfectly well
> for properly-stored BLOBs (and which is what Mr. Goergen actually  
> wanted).
>
> SQLite handles 8-bit BLOBs just fine, at that; there is never any need
> thereby to bend, fold, spindle, or mutilate data with ugly 7-bit
> workarounds.
>
> Very truly,
>
> Samuel Adam ◊ 
> 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
>
>
>> C
>>
>> Sunday, February 6, 2011, 10:53:05 AM, you wrote:
>>
>> YG> On 06.02.2011 14:36 CE(S)T, Samuel Adam wrote:
   * You should be using bound parameters on INSERT.  If you are
 not, change
 your code.  This will eliminate a whole list of potential problems.
>>
>> YG> I already do that.
>>
   * 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.
>>
>> YG> There is a PDO method to execute a prepared statement with an array
>> of
>> YG> values to be used as parameters. There is no way to specify
>> additional
>> YG> information about how to interpret these values in this method. But
>> YG> there is another method to bind each value separately, and it has
>> YG> another argument to pass some data type. I'd need to change the way  
>> I
>> YG> execute my SQL statements to make use of it.
>>
>> YG> I'd expect that SQLite known on its own what data type a column is
>> and
>> YG> respect it. Seems like SQLite is sometimes more type-agnostic than
>> PHP,
>> YG> where I take great care of data types in this special application.
>>
>> YG> For now, I just won't save files to the database with SQLite but
>> instead
>> YG> on disk. I won't get to rewriting the database class anytime soon  
>> but
>> YG> I'll look into it then.
>>
>> YG> I'm wondering why I get all the data back but SQLite can't count its
>> YG> characters... And the image I get back from SQLite looks error-free
>> so
>> YG> it probably didn't make a single mistake handling it as text data.
>>
>>
>>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Samuel Adam]

On Sun, 06 Feb 2011 11:41:03 -0500, Teg  wrote:

> Hello Yves,
>
> You could alway mime/uu/yenc encode it into text before insert, and do
> the reverse when you retrieve it. Then the problem goes away.

No, it doesn’t:  Then SQLite *really* has no way of telling the byte  
length of the value, which SQL core function length() does perfectly well  
for properly-stored BLOBs (and which is what Mr. Goergen actually wanted).

SQLite handles 8-bit BLOBs just fine, at that; there is never any need  
thereby to bend, fold, spindle, or mutilate data with ugly 7-bit  
workarounds.

Very truly,

Samuel Adam ◊ 
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


> C
>
> Sunday, February 6, 2011, 10:53:05 AM, you wrote:
>
> YG> On 06.02.2011 14:36 CE(S)T, Samuel Adam wrote:
>>>   * You should be using bound parameters on INSERT.  If you are  
>>> not, change
>>> your code.  This will eliminate a whole list of potential problems.
>
> YG> I already do that.
>
>>>   * 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.
>
> YG> There is a PDO method to execute a prepared statement with an array  
> of
> YG> values to be used as parameters. There is no way to specify  
> additional
> YG> information about how to interpret these values in this method. But
> YG> there is another method to bind each value separately, and it has
> YG> another argument to pass some data type. I'd need to change the way I
> YG> execute my SQL statements to make use of it.
>
> YG> I'd expect that SQLite known on its own what data type a column is  
> and
> YG> respect it. Seems like SQLite is sometimes more type-agnostic than  
> PHP,
> YG> where I take great care of data types in this special application.
>
> YG> For now, I just won't save files to the database with SQLite but  
> instead
> YG> on disk. I won't get to rewriting the database class anytime soon but
> YG> I'll look into it then.
>
> YG> I'm wondering why I get all the data back but SQLite can't count its
> YG> characters... And the image I get back from SQLite looks error-free  
> so
> YG> it probably didn't make a single mistake handling it as text data.
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Teg]

Hello Yves,

You could alway mime/uu/yenc encode it into text before insert, and do
the reverse when you retrieve it. Then the problem goes away.


C

Sunday, February 6, 2011, 10:53:05 AM, you wrote:

YG> On 06.02.2011 14:36 CE(S)T, Samuel Adam wrote:
>>   * You should be using bound parameters on INSERT.  If you are not, 
>> change  
>> your code.  This will eliminate a whole list of potential problems.

YG> I already do that.

>>   * 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.

YG> There is a PDO method to execute a prepared statement with an array of
YG> values to be used as parameters. There is no way to specify additional
YG> information about how to interpret these values in this method. But
YG> there is another method to bind each value separately, and it has
YG> another argument to pass some data type. I'd need to change the way I
YG> execute my SQL statements to make use of it.

YG> I'd expect that SQLite known on its own what data type a column is and
YG> respect it. Seems like SQLite is sometimes more type-agnostic than PHP,
YG> where I take great care of data types in this special application.

YG> For now, I just won't save files to the database with SQLite but instead
YG> on disk. I won't get to rewriting the database class anytime soon but
YG> I'll look into it then.

YG> I'm wondering why I get all the data back but SQLite can't count its
YG> characters... And the image I get back from SQLite looks error-free so
YG> it probably didn't make a single mistake handling it as text data.




-- 
Best regards,
 Tegmailto:t...@djii.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Samuel Adam]

On Sun, 06 Feb 2011 10:53:05 -0500, Yves Goergen  
 wrote:

> On 06.02.2011 14:36 CE(S)T, Samuel Adam wrote:
[snip]
>>  * 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.
>
> There is a PDO method to execute a prepared statement with an array of
> values to be used as parameters. There is no way to specify additional
> information about how to interpret these values in this method. But
> there is another method to bind each value separately, and it has
> another argument to pass some data type. I'd need to change the way I
> execute my SQL statements to make use of it.

Depending on your PHP version, a (binary) cast may help; if you are  
writing the GIF image as a string-literal in your source code (probably  
not), b"" will specify the correct type directly; if you are opening an  
external file, check your fopen() flags or whatnot.  N.b., I cannot  
predict whether PDO will use this type information or ignore it.

> I'd expect that SQLite known on its own what data type a column is and
> respect it. Seems like SQLite is sometimes more type-agnostic than PHP,
> where I take great care of data types in this special application.

SQLite also cares for types:

http://www.sqlite.org/datatype3.html

PHP has somewhat similar “duck typing”, and its types are much more  
complicated; thus you should already be familiar with the concept.

> For now, I just won't save files to the database with SQLite but instead
> on disk. I won't get to rewriting the database class anytime soon but
> I'll look into it then.
>
> I'm wondering why I get all the data back but SQLite can't count its
> characters... And the image I get back from SQLite looks error-free so
> it probably didn't make a single mistake handling it as text data.

What you are wondering has been previously explained in this thread, in  
precise terms and with reference to docs.

Very truly,

Samuel Adam ◊ 
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

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Yves Goergen]

On 06.02.2011 14:36 CE(S)T, Samuel Adam wrote:
>   * You should be using bound parameters on INSERT.  If you are not, 
> change  
> your code.  This will eliminate a whole list of potential problems.

I already do that.

>   * 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.

There is a PDO method to execute a prepared statement with an array of
values to be used as parameters. There is no way to specify additional
information about how to interpret these values in this method. But
there is another method to bind each value separately, and it has
another argument to pass some data type. I'd need to change the way I
execute my SQL statements to make use of it.

I'd expect that SQLite known on its own what data type a column is and
respect it. Seems like SQLite is sometimes more type-agnostic than PHP,
where I take great care of data types in this special application.

For now, I just won't save files to the database with SQLite but instead
on disk. I won't get to rewriting the database class anytime soon but
I'll look into it then.

I'm wondering why I get all the data back but SQLite can't count its
characters... And the image I get back from SQLite looks error-free so
it probably didn't make a single mistake handling it as text data.

-- 
Yves Goergen "LonelyPixel" 
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

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Black, Michael (IS)]

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.

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

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Samuel Adam]

On Sun, 06 Feb 2011 08:22:58 -0500, Black, Michael (IS)  
 wrote:

> http://www.sqlite.org/c3ref/blob_open.html
> http://www.sqlite.org/c3ref/blob_bytes.html

Mr. Black, is that a tactful hint that the original poster should rewrite  
his app in C rather than PHP?

(Not that I’d disagree—not at all!—just wanted to clarify, since o.p.’s  
problem originated with SQL core function length() which “returns the  
length of X in characters if X is a string, or in bytes if X is a  
blob”[1].)

[1] http://www.sqlite.org/lang_corefunc.html

(P.S.—For precision, the quoted doc should say “text value” rather than  
“string”.)

Very truly,

Samuel Adam ◊ 
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


>
> 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 4:40 AM
> To: General Discussion of SQLite Database
> Subject: EXT :[sqlite] LENGTH on a BLOB field stops at NUL byte
>
> Hi,
>
> I'm storing small files in a BLOB field in the database. I'd like to
> determine the size of such a file with an SQL query without fetching the
> data. I tried using the LENGTH() function on that field but it stops
> counting at the first NUL byte. A 3.2 KiB GIF image is reported to be
> only 7 bytes long, that's just the file header length.
>
> Is this a bug or should I use another function on binary data?
>
> The SQLite version is 3.6.20 from PHP 5.3.1 on Windows XP.
>
> --
> Yves Goergen "LonelyPixel" 
> 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

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Samuel Adam]

On Sun, 06 Feb 2011 08:10:49 -0500, Yves Goergen  
 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 ◊ 
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

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Black, Michael (IS)]

http://www.sqlite.org/c3ref/blob_open.html
http://www.sqlite.org/c3ref/blob_bytes.html

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 4:40 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] LENGTH on a BLOB field stops at NUL byte

Hi,

I'm storing small files in a BLOB field in the database. I'd like to
determine the size of such a file with an SQL query without fetching the
data. I tried using the LENGTH() function on that field but it stops
counting at the first NUL byte. A 3.2 KiB GIF image is reported to be
only 7 bytes long, that's just the file header length.

Is this a bug or should I use another function on binary data?

The SQLite version is 3.6.20 from PHP 5.3.1 on Windows XP.

--
Yves Goergen "LonelyPixel" 
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

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Yves Goergen]

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.

-- 
Yves Goergen "LonelyPixel" 
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

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Samuel Adam]

On Sun, 06 Feb 2011 07:15:42 -0500, Yves Goergen  
 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 ◊ 
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

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Yves Goergen]

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" 
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

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Drake Wilson]

Quoth Yves Goergen , on 2011-02-06 11:40:17 +0100:
> I'm storing small files in a BLOB field in the database. I'd like to
> determine the size of such a file with an SQL query without fetching the
> data. I tried using the LENGTH() function on that field but it stops
> counting at the first NUL byte. A 3.2 KiB GIF image is reported to be
> only 7 bytes long, that's just the file header length.
> 
> Is this a bug or should I use another function on binary data?

I can't reproduce this with SQLite 3.7.4 from Debian GNU/Linux:

  SQLite version 3.7.4
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite> create table foo (x);
  sqlite> insert into foo (x) values (x'123412340012341234');
  sqlite> select length(x) from foo;
  length(x)
  9

Silly question: are you sure you're actually _storing_ all the data?
Can you verify that you can get all the bytes out in any way at all?
Information about the schema in use would be helpful, in general.

   ---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Simon Davies]

On 6 February 2011 10:40, Yves Goergen  wrote:
> Hi,
>
> I'm storing small files in a BLOB field in the database. I'd like to
> determine the size of such a file with an SQL query without fetching the
> data. I tried using the LENGTH() function on that field but it stops
> counting at the first NUL byte. A 3.2 KiB GIF image is reported to be
> only 7 bytes long, that's just the file header length.

This not the case for me:

SQLite version 3.6.19
Enter ".help" for instructions
sqlite>
sqlite> create table tst( id integer primary key, data text );
sqlite>
sqlite> insert into tst values( null, 'line 1' );
sqlite> insert into tst values( null, x'6565' );
sqlite> insert into tst values( null, x'656500656565656667686970'  );
sqlite>
sqlite> select *, typeof( data ), length( data ) from tst;
1|line 1|text|6
2|ee|blob|2
3|ee|blob|12
sqlite>

>
> Is this a bug or should I use another function on binary data?

length() should return the number of bytes for a blob.
What does typeof() tell you about the data?

>
> The SQLite version is 3.6.20 from PHP 5.3.1 on Windows XP.
>
> --
> Yves Goergen "LonelyPixel" 

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users