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

2011-02-10 Thread 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! --

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

2011-02-10 Thread 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

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

2011-02-10 Thread 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. >

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

2011-02-10 Thread 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"

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

2011-02-10 Thread 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

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

2011-02-07 Thread 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

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

2011-02-07 Thread 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

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

2011-02-06 Thread 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

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

2011-02-06 Thread 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

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

2011-02-06 Thread 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

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

2011-02-06 Thread 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 >>

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

2011-02-06 Thread 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

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

2011-02-06 Thread Black, Michael (IS)
_ 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:

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

2011-02-06 Thread Samuel Adam
oun...@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 BLO

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

2011-02-06 Thread 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

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

2011-02-06 Thread Black, Michael (IS)
] 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

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

2011-02-06 Thread 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

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

2011-02-06 Thread 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

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

2011-02-06 Thread 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:

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

2011-02-06 Thread 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 >

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

2011-02-06 Thread 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

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

2011-02-06 Thread Yves Goergen
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