CODEWITHSUNDEEP
sqlitecasting
preahkumpii
preahkumpii

Reputation: 1301

Sqlite: How to cast(data as TEXT) for BLOB

I have a sqlite database from which I want to extract a column of information with the datatype BLOB. I am trying this:

SELECT cast(data as TEXT) FROM content

This is obviously not working. The output is garbled text like this:

x��Uak�0�>�8�0Ff;I�.��.i׮%�A��s�M

The data in the content column is mostly text, but may also have images (which I recognized could cause a problem if I cast as TEXT). I simply want to extract that data into a usable format. Any ideas?

Upvotes: 24

Views: 52780

Answers (2)

scruss
scruss

Reputation: 1160

You can use

SELECT hex(data) FROM content

or

SELECT quote(data) FROM content

The first will return a hex string (ABCD), the second quoted as an SQL literal (X'ABCD').

Note that there's (currently) no way of converting hexadecimal column information back to a BLOB in SQLite. You will have to use C/Perl/Python/… bindings to convert and import those.

Upvotes: 43

mvp
mvp

Reputation: 116367

You can write some simple script which will save all blobs from your database into files. Later, you can take a look at these files and decide what to do with them.

For example, this Perl script will create lots of files in current directory which will contain your data blob fields. Simply adjust SELECT statement to limit fetched rows as you need:

use DBI;

my $dbh = DBI->connect("dbi:SQLite:mysqlite.db")
    or die DBI::errstr();
my $sth = $dbh->prepare(qq{
    SELECT id, data FROM content
});
$sth->execute();
while (my $row = $sth->fetchrow_hashref()) {
    # Create file with name of $row->{id}:
    open FILE, ">", "$row->{id}";
    # Save blob data into this file:
    print FILE $row->{data};
    close FILE;
}
$dbh->disconnect();

Upvotes: 2

Related Questions