Re: [sqlite] Inserting images (gif) in Blob field (with php)

[Artur Reilin]

> Using base64 will allow you to take binary data (arbitrary 1s and 0s) and
> store it in a text field.  However, SQLite has BLOB fields, which can be
> used to store data in its original binary form, and will indeed take up
> fewer bytes than encoding it in base64.
>
> So you need to look at the PHP functions for SQLite which mention BLOB
> fields, or do a relevant Google search.  They're a little more complicated
> to use than other fields because they can be so long, but they work fine.
>
> If you want more details you need to tell us whether you're using the PDO
> interface to SQLite or the SQLite3 interface directly.  If you're not
> sure, just give us the URL of the page you're using for reference.
>
> Simon.

I forgot to write, that i used a blob field, but the problem was with the
query not seem to work right.

And your question about the way i used it: I use $db = new SQLite3. I
think that is the interface then, right? Just with oop.

But see my answer under the other quote.

> Why are you using addslashes?  This sounds like you're trying to embed
> the whole thing as an SQL literal.  Don't do that.  Use a parameter in
> the SQL, then bind it with type SQLITE3_BLOB, ideally using a column
> with NONE affinity (e.g., of type BLOB NULL) underneath.
>
> (The addslashes function is often a red flag in the general case.)
>
> See http://us2.php.net/manual/en/sqlite3stmt.bindvalue.php if you
> haven't already.
>
>---> Drake Wilson

That addslashes was just in the code. I don't get the idea, why the
creator of the code example used it. Perhaps it was more compatible with
mysql then.

Anyways, the way you gave me worked like charm. I got all the images (386
as number) in the database.

That was a significant file change from 2.87 mb to 1.82 mb. Like I
expected it.

I just don't get it back then, then i saw about that bindvalue thing,
what's the idea behind this. I never run in any problems. So, now I have
an idea why this is good.

That's how it worked for me:

$prepare_image = $db->prepare('insert or replace into thumbs
(image_name,image_datetime,image_data)
values("'.$filename.'","'.$filedate.'",:fileblob)');

$prepare_image->bindValue(':fileblob',$image,SQLITE3_BLOB);

$prepare_image->execute();

I used that for a foreach loop to get the whole folder in database.

So thanks to you both for the information. Now we have an image example
input in the archives. ;)

With best wishes.

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

Re: [sqlite] Inserting images (gif) in Blob field (with php)

[Drake Wilson]

Quoth Artur Reilin , on 2010-10-05 15:06:57 +0200:
> What I'm currently using is get the file contents and then use
> base64_encode to store them in database. But that just blows the size up.
> I saw that the firefox database (places.sqlite) store the favicons
> directly in database. I searched around and cannot found a doc how they do
> this.
> 
> Some days ago I found the following code:
> 
> $image_handle = fopen($file,"rb");
> $image = addslashes(fread($image_handle,filesize($file)));

Why are you using addslashes?  This sounds like you're trying to embed
the whole thing as an SQL literal.  Don't do that.  Use a parameter in
the SQL, then bind it with type SQLITE3_BLOB, ideally using a column
with NONE affinity (e.g., of type BLOB NULL) underneath.

(The addslashes function is often a red flag in the general case.)

See http://us2.php.net/manual/en/sqlite3stmt.bindvalue.php if you
haven't already.

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

Re: [sqlite] Inserting images (gif) in Blob field (with php)

[Simon Slavin]

On 5 Oct 2010, at 2:06pm, Artur Reilin wrote:

> I searched quiet around the internet and cannot found an solution.
> 
> What I'm currently using is get the file contents and then use
> base64_encode to store them in database. But that just blows the size up.
> I saw that the firefox database (places.sqlite) store the favicons
> directly in database. I searched around and cannot found a doc how they do
> this.

Using base64 will allow you to take binary data (arbitrary 1s and 0s) and store 
it in a text field.  However, SQLite has BLOB fields, which can be used to 
store data in its original binary form, and will indeed take up fewer bytes 
than encoding it in base64.

So you need to look at the PHP functions for SQLite which mention BLOB fields, 
or do a relevant Google search.  They're a little more complicated to use than 
other fields because they can be so long, but they work fine.

If you want more details you need to tell us whether you're using the PDO 
interface to SQLite or the SQLite3 interface directly.  If you're not sure, 
just give us the URL of the page you're using for reference.

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

[sqlite] Inserting images (gif) in Blob field (with php)

[Artur Reilin]

I searched quiet around the internet and cannot found an solution.

What I'm currently using is get the file contents and then use
base64_encode to store them in database. But that just blows the size up.
I saw that the firefox database (places.sqlite) store the favicons
directly in database. I searched around and cannot found a doc how they do
this.

Some days ago I found the following code:

$image_handle = fopen($file,"rb");
$image = addslashes(fread($image_handle,filesize($file)));

but this doesn't work, because I get the "unrecognized token" error at the
very first line of the image.

My current code:

$image = base64_encode(file_get_contents($file));

makes it bigger than I need it. I'm trying to get a smaller filesize than
a program who use one file per folder for thumbnails.

And with no search function on the user list, it's a pain to go through
all the months. (nothing found anyways)

With best wishes

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