PDO SQLite support just doesn't work fully?

Current Projects: PHP on XP Guide — NFO Viewer — Easy Reflections — Photon Storm — HotWire — FileGlider

Wednesday, April 18. 2007

PDO SQLite support just doesn't work fully?

After wasting hours and hours on this, I've come to the conclusion that the PDO SQLite blob support is totally borked, and just doesn't appear to work properly. I really don't know why, or what is going on, but after spending hours trying various code permutations I'm just not getting anywhere. I tried asking on php_general to no avail, so I consider this blog entry my last ditch attempt to get someone to shed some light on this issue before I give-in and ditch PDO for good.

I'm trying to store an image in a SQLite database using PDO. Yes yes, we all know that storing images in database files is a bad thing but none-the-less, it should technically be able to handle it. The source code given on http://uk2.php.net/manual/en/ref.pdo.php under Example 1669 appears to be just plain wrong. For example you can't beginTransaction() on a statement! So I had to re-write it a little, but no matter what it just doesn't play ball.

The problem appears to be the PDO LOB type. It will only ever send the first 21 bytes of the image to the database. Here's the code:

$stmt = $db->prepare('INSERT INTO Test (name, data) VALUES (?,?)');
$stmt->bindParam(1, $name, PDO::PARAM_STR, 60);
$stmt->bindParam(2, $pic_data, PDO::PARAM_LOB);
$stmt->execute();

The database is alive and well, and can be written to just fine, as is proven by the fact that I do get SOME data in there, just not all of it! I have tried a variety of files to no end.

According to the PHP docs PDO LOB should work on streams as well, so I tried adding:

$fp = fopen($filename, 'rb');

To my script, and then changing the LOB line to:

$stmt->bindParam(2, $fp, PDO::PARAM_LOB);

.. thinking it would work out what to do with the newly found stream. But no, yet again all I get are the first 21 bytes.

Using SQLite Maestro I can view the inserted data, and it's always too short. If I use the SQLite Maestro interface to insert an image, it works fine. If I use the SQLite command-line tools to do so, it works fine too.

There appears to be similar issues with PDO SQLite failing when extracting LOB data for use.

This is on PHP 5.2.1. I did notice some posts from other people who have run into this problem, and a PHP bug report reflecting the same thing. A comment from the PHP online manual states the following:

On a different note, it seems that the developers got a little lazy and decided not to add any support for integer, real, or blob data types. When using PDO's bindParam or bindValue methods, the driver only uses either sqlite3_bind_null, or sqlite3_bind_text. That means that if you think you're storing ints, even with PDO::PARAM_INT specified, it is still stored as string, taking up a lot more space than it needs to and effectively eliminating all data-type support for SQLite (you are only left with nulls or text). In my opinion, this is a rather lousy implementation. I've submitted a feature/change request to the bug tracking system, so I guess we can wait and see if full data type support is added in the future. What I don't understand is why this was done in the first place. SQLite's API could not be any easier to use, simply check the type of the variable passed, and if it's an int use sqlite3_bind_int, if it's a float use sqlite3_bind_double. Why the forced conversion into strings?

This comment was posted back in August 2006, so I had hoped it wasn't still relevant, but I fear it still is.

If you've any ideas (or working code) then I'd love to hear them.

Posted by Richard Davey in Code at 15:25 | Comments (8) | Trackbacks (0)

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

My suggestion is to step back and try something simple:

$stmt = $db->prepare('INSERT INTO Test (name, data) VALUES (?,?)');
$stmt->execute(array($name, file_get_contents($pic_data_file)));

You get no performance benefit from the LOB APIs if the underlying database doesn't actually have LOB APIs, as is the case here.

You should also note that sqlite is typeless in the same way that PHP is, so inserting data as strings is no different from inserting it any other way.

Hope that helps, and I also hope that you take a few extra minutes to file or update bug reports on this, because the people that go to fix things don't read your blog on a daily basis to find out what needs to be done. (google for "don't blog bugs").

#1 Wez Furlong (Homepage) on 2007-04-18 17:06 (Reply)

I didn't want to post it as a bug, because I believed the error to be BTUATK! But as your code given above doesn't work either I have indeed put in a fresh bug report for this, with as much information as possible: http://bugs.php.net/bug.php?id=41135

#1.1 Richard Davey (Homepage) on 2007-04-18 17:52 (Reply)

Hmm...
The very same code works for me on Windows XP, PHP5.2.1 and SQLite 3:

$stmt = $conn->prepare("UPDATE books SET coverMime=?, coverImage=? WHERE id=$book[id]");
$cover = fopen($_FILES['cover']['tmp_name'], 'rb');
$stmt->bindValue(1, $_FILES['cover']['type']);
$stmt->bindParam(2, $cover, PDO::PARAM_LOB);

The only thing that does not work es expected is this bug: http://bugs.php.net/bug.php?id=40913

#2 Dennis (Homepage) on 2007-04-19 20:58 (Reply)

Would you mind emailing me the SQLite database file? It has to be an inconsistency between the SQLite files themselves if that code works for you, on an exact same set-up, but not here.

#2.1 Richard Davey (Homepage) on 2007-04-19 23:06 (Reply)

I had a LAMP stack a month or two ago (ubuntu) where everytime I'd perform an insert with PDO, it would just spit in garbage data. That was a lot of fun.

#3 Marcel on 2007-04-19 21:35 (Reply)

I believe it crops the data to the first \0 byte.

The ugly workaround is not to use bound parameters ...

$pic_data = 'X'.$db->quote(bin2hex($pic_data));

$stmt = $db->prepare('INSERT INTO Test (name, data) VALUES (?,'.$pic_data.')');
$stmt->bindParam(1, $name, PDO::PARAM_STR, 60);
$stmt->execute();

#4 Ren on 2007-04-20 19:02 (Reply)

Brilliant idea. Thanks for very interesting article. btw. I really enjoyed reading all of your articles. It’s interesting to read ideas, and observations from someone else’s point of view… makes you think more. Keep up the good work. Greetings

#5 tomek (Homepage) on 2007-05-11 14:13 (Reply)

Interesting ... it remembers me at: "is it a bug or a feature?". is there any new informations about that topic?

#6 schreibtisch hengst (Homepage) on 2008-04-20 18:11 (Reply)

Add Comment

Name
Email
Homepage
In reply to
Comment	HTML-Tags will be converted to Entities. Standard emoticons like :-) and ;-) are converted to images. To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly. Enter the string from the spam-prevention image above:
	Remember Information? Subscribe to this entry