Help us understand the problem. What is going on with this article?

SQLiteのdbにファイルを保存する方法

More than 3 years have passed since last update.

BLOB型に保存すれば良い。
やり方はDBD::SQLite - search.cpan.org #Blobs ほぼそのまま。

準備

$ sqlite a.db
sqlite> create table image (id integer primary key, data blob);

入力、出力

a.pl
use strict;
use warnings;
use DBI;

# 入力画像データ
my $i_img = `cat meshi.jpg`;

# a.dbを開く
my $dbh = DBI->connect("dbi:SQLite:dbname=a.db") or die $DBI::errstr;

# 画像データを入力
my $i_sth = $dbh->prepare("INSERT INTO image (data) values (?)");
$i_sth->bind_param(1, $i_img, DBI::SQL_BLOB);
$i_sth->execute();
$i_sth->finish();

# 画像データをDBから取得
my $o_sth = $dbh->prepare("SELECT data FROM image WHERE id=1");
$o_sth->execute();
my $row = $o_sth->fetch();
my $o_img = $row->[0];
$o_sth->finish();

# 画像データをファイルに出力
open my $fh, ">:raw", "image.dat";
print $fh $o_img;
close $fh;

$ perl a.pl
$ ll
-rw-r--r-- 1 ymko users 591872  8月 24 00:48 a.db
-rw-r--r-- 1 ymko users    529  8月 24 00:48 a.pl
-rw-r--r-- 1 ymko users  73429  8月 24 00:48 image.dat
-rw-r--r-- 1 ymko users  73429  8月 23 06:34 meshi.jpg

なお、db上のデータを消してもdbのファイル容量は減らない。
vacuumコマンドで減る。

参考

How can I load an image file into a blob in SQLite using Perl? - Stack Overflow
http://stackoverflow.com/questions/997599/how-can-i-load-an-image-file-into-a-blob-in-sqlite-using-perl
意訳:私はDB上に画像データを保存するのは如何なものかと思うけど、DBI::SQLiteのサンプルにあるからこれでできるよ。

DBD::SQLite - search.cpan.org
http://search.cpan.org/~ishigaki/DBD-SQLite-1.48/lib/DBD/SQLite.pm#Blobs

Working with images in SQLite with Perl
http://zetcode.com/db/sqliteperltutorial/images/

空き領域の開放(VACUUM文) - SQLite入門
http://www.dbonline.jp/sqlite/manage/index1.html

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした