On Sun, 06 Feb 2011 11:54:37 -0500, Samuel Adam <a...@certifound.com>
wrote:
> On Sun, 06 Feb 2011 11:41:03 -0500, Teg <t...@djii.com> 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 ◊ <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
> 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 ◊ <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
>
>
>> 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
