Loss of Binary Data in Dump File

[Art Age Software]

Hi All,

New to this list and hoping someone can help. I have a sqlite3
database that contains a couple of tables that have binary data in
BLOB columns. I know the data is in there and stored correctly because
the application accessing the database can retrieve the column data
just fine.

The problem is that the .dump command produces a dump file with
seemingly truncated BLOB columns. Dumping a roughly 9MB database file
produces a 2MB dump file with what seems to be only a few characters
in each BLOB column. All other column data is fine.

Is there some trick to producing a dump file with all BLOB column data
intact? (The goal here is to be able to migrate a database with binary
data to and from MySQL.)

Thanks in advance for any insights.

Sam

[Roger Binns]

Post by Art Age Software
New to this list and hoping someone can help. I have a sqlite3
database that contains a couple of tables that have binary data in
BLOB columns.

Note that there is column affinity, but the data stored for a particular
column can differ per row.

Post by Art Age Software
I know the data is in there and stored correctly because
the application accessing the database can retrieve the column data
just fine.

That doesn't prove it :-)

Post by Art Age Software
The problem is that the .dump command produces a dump file with
seemingly truncated BLOB columns.

The single most likely cause is that the data is actually stored as text not
blobs. The easiest way to check is this:

select distinct typeof(columnname) from tablename;

When you supply "text" to SQLite APIs (the ones ending in _text) they are
expected to be UTF8 encoded bytes. However SQLite does not actually check
that it is valid UTF8 encoding. Consequently it is possible to insert
random binary data using the text APIs and for it appear to all work correctly.

The dump code stops at the first null in any text value but will correctly
output a blob value.

Roger

[Art Age Software]

Thanks Roger. That does seem to be the problem as the type of the
columns is coming back as "text."

I guess the next question is how do I force the affinity to "blob"
using the PHP PDO API...

Sam

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Art Age Software
New to this list and hoping someone can help. I have a sqlite3
database that contains a couple of tables that have binary data in
BLOB columns.

Note that there is column affinity, but the data stored for a particular
column can differ per row.

Post by Art Age Software
I know the data is in there and stored correctly because
the application accessing the database can retrieve the column data
just fine.

That doesn't prove it :-)

Post by Art Age Software
The problem is that the .dump command produces a dump file with
seemingly truncated BLOB columns.

The single most likely cause is that the data is actually stored as text not
  select distinct typeof(columnname) from tablename;
When you supply "text" to SQLite APIs (the ones ending in _text) they are
expected to be UTF8 encoded bytes.  However SQLite does not actually check
that it is valid UTF8 encoding.  Consequently it is possible to insert
random binary data using the text APIs and for it appear to all work correctly.
The dump code stops at the first null in any text value but will correctly
output a blob value.
Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkzIlaYACgkQmOOfHg372QSPDQCfcTuAMFa+9vpmBAJ6WMZtCX5M
opgAoMKc9THDp58KyaWIl9UvOyVQ/Iac
=82WY
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Roger Binns]

Post by Art Age Software
I guess the next question is how do I force the affinity to "blob"

The affinity is the type that SQLite will make a reasonable attemp to
convert to for that column, but leave the value as is if the conversion
can't be made. For example if a column has an affinity of number then when
you insert a string there will be an attempt to convert to number. If the
conversion succeeds then a number value will be stored otherwise the string
will be stored.

Documentation is at http://www.sqlite.org/datatype3.html

There is no conversion affinity for blob, although you can cast a string to
a blob. IIRC the last time I tested that cast would also stop at the first
null.

The correct approach is to insert the blob as a blob. At the C API level
that means using sqlite3_bind_blob, not sqlite3_bind_text as is likely the
case for your situation. At the SQL level you use X prefix and hex:

insert .... values( X'7f3c22005d' )

Post by Art Age Software
using the PHP PDO API...

A Google search found these:

http://bugs.php.net/bug.php?id=41135
http://bugs.php.net/bug.php?id=42443

Roger

[Art Age Software]

With Roger's help I have gotten to the bottom of this issue, and I
wanted to post the resolution to the list in case anyone else bumps
into it.

There is a bug in PHP versions prior to PHP 5.3 that makes it
impossible to reliably create/store BLOB columns. My code ends up
creating TEXT data when run under any version of PHP in the 5.2.x
branch. When run under PHP 5.3.x, BLOB columns are correctly created.

So, the only options are to either use PHP 5.3 or later, or to ditch
PDO and use PHP's native Sqlite3 interface directly.

Thanks.

Sam

[Konrad J Hambrick]

Post by Art Age Software
With Roger's help I have gotten to the bottom of this issue, and I
wanted to post the resolution to the list in case anyone else bumps
into it.
There is a bug in PHP versions prior to PHP 5.3 that makes it
impossible to reliably create/store BLOB columns. My code ends up
creating TEXT data when run under any version of PHP in the 5.2.x
branch. When run under PHP 5.3.x, BLOB columns are correctly created.
So, the only options are to either use PHP 5.3 or later, or to ditch
PDO and use PHP's native Sqlite3 interface directly.

Thanks for the info Sam !

-- kjh( who gave up and used native files, storing only FileNames in sqlite )

[Simon Slavin]

Post by Art Age Software
There is a bug in PHP versions prior to PHP 5.3 that makes it
impossible to reliably create/store BLOB columns. My code ends up
creating TEXT data when run under any version of PHP in the 5.2.x
branch. When run under PHP 5.3.x, BLOB columns are correctly created.

Eek. That's a valuable discovery. Well done.

Simon.

[Stephen Chrzanowski]

Please post how you manage to get PHP to use Sqlite3 without PDO as I've
been wrestling with that for a while now. Any time I use Sqlite commands,
it creates the DB as SQLite2, not 3.

Post by Art Age Software
With Roger's help I have gotten to the bottom of this issue, and I
wanted to post the resolution to the list in case anyone else bumps
into it.
There is a bug in PHP versions prior to PHP 5.3 that makes it
impossible to reliably create/store BLOB columns. My code ends up
creating TEXT data when run under any version of PHP in the 5.2.x
branch. When run under PHP 5.3.x, BLOB columns are correctly created.
So, the only options are to either use PHP 5.3 or later, or to ditch
PDO and use PHP's native Sqlite3 interface directly.
Thanks.
Sam
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin]

Post by Stephen Chrzanowski
Please post how you manage to get PHP to use Sqlite3 without PDO

There is yet another SQLite interface:

http://php.net/manual/en/book.sqlite3.php

It's object-oriented, so of you don't already use OO in PHP you're going to have to learn how.

I find it very amusing that SQLite is so useful to PHP users that there are three different APIs to do it. Yes, I know one's obsolete, but it's still included in the default compilation configuration.

Simon.

[Stephen Chrzanowski]

Thank you. I'll investigate some code change options.

Post by Simon Slavin

Post by Stephen Chrzanowski
Please post how you manage to get PHP to use Sqlite3 without PDO

http://php.net/manual/en/book.sqlite3.php
It's object-oriented, so of you don't already use OO in PHP you're going to
have to learn how.
I find it very amusing that SQLite is so useful to PHP users that there are
three different APIs to do it. Yes, I know one's obsolete, but it's still
included in the default compilation configuration.
Simon.
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users