ASH | サーバ | セキュリティ | Linux | FreeBSD | DB | Web | CGI | Perl | Java | XML | プログラム | ネットワーク | 標準 | Tips集

DBI/DBDの使い方

　この文章は、perlでcgi等のプログラムができ、SQLの基本的な使い方を知っているひとを対象に書かれています。また、DBI/DBDモジュールを利用するには、perl5のオブジェクト指向（風）プログラミングの知識もある程度以上は必要です。

DBI/DBD

　DBI/DBDとは、perlとデータベースの間をとりもってくれる汎用インターフェイスです。 DBIモジュールとDBDモジュールからできており、DBDモジュールは、各データベースごとに存在します。プログラマは、DBIモジュールのルールにしたがってプログラミングすることで、どのようなデータベースにも、同じようにアクセスするプログラムを書くことができます。
　ここでは、DBD/pg（PostgreSQL用DBDモジュール）を使った例を提示しますが、基本的には、どのようなDB相手でも同様のことができます。

DBIのオフィシャルページ
http://dbi.symbolstone.org/

　この文章はDBI1.14をベースに書かれています。現在のDBIのバージョンは1.18です。

　なお、DBIのマニュアルは、DBIのソースディレクトリ内、 blib/man3/DBI.3 にあります。
　manをインストールしていないなら、manコマンドで内容を見ればよいでしょう。

# man ./DBI.3

基本的な流れ

　DBI/DBDを使ってperlからDBにアクセスする場合、おおまかには以下のような流れになります。

select 以外

モジュール読み込み
DB接続
ステートメントの準備
ステートメントの実行
DB切断

select

モジュール読み込み
DB接続
ステートメントの準備
ステートメントの実行
｛データの取り出し｝*n
DB切断

　selectの場合は、DBから複数レコードのデータを受け取ることになるので、やや複雑な処理を行う必要があります。ステートメントとは、データベースに渡されるSQL文のことです。

シンプルで基本的なプログラムの解説

　最も基本的なパターンで必要になるオブジェクトやメソッドの解説です。上記の流れと対応しています。

モジュール読み込み

　perlプログラムの最初に、

use DBI;

と書きます。

DB接続とデータベースハンドル（connect：DBIクラスメソッド）

　データベースハンドルとは、データベースとの接続を表すオブジェクトです。

$dbh = DBI->connect($data_source, $username, $auth);

$data_source　DB接続用文字列
$username　DBの接続ユーザ
$auth　DBの接続パスワード

のようにして接続（connect）します。connectは、データベースハンドルオブジェクトへのリファレンスを返します。以後は、データベースハンドルオブジェクト（ここでは$dbh）を使ってDBにアクセスします。

SQLの準備とステートメントハンドル（prepare：データベースハンドルメソッド）

　ステートメントハンドルとは、準備された特定のSQL文を表すオブジェクトです。

$sth = $dbh->prepare($statement);

$statement　実行したいSQL文

$dbh　connect済みのデータベースハンドル

のようにして、ステートメント（SQL文）を準備（prepare）します。prepareは、ステートメントハンドルオブジェクトへのリファレンスを返します。準備だけでは何も起こらず、準備されたステートメントを実行（execute）することで、SQLが実行されます。このとき、SQLの最後にセミコロンをつけてやる必要はありません。

ステートメントの実行（execute：ステートメントハンドルメソッド）

　準備されたステートメントハンドルに対して、executeメソッドを発行してやることで、そのSQLが実行されます。

$rv = $sth->execute;

$sth　prepareされたステートメントハンドル

このようにしてステートメントは実行されます。正常に実行された場合、executeはtrueを返し、エラーの場合はundefを返します。 select文でないステートメントを実行した場合、executeは影響を受けたレコード数を返します。また、レコード数が0の場合は0E0、レコード数が不明の場合は-1を返します。
　select文の場合、executeするだけでは、データを取り出す準備ができただけです。その後、$sthに対して、fetchを実行してやる必要があります。

selectされたデータの取り出し（fetch：ステートメントハンドルメソッド）

　executeされたステートメントハンドル（select文）に対して、fetchメソッドを発行することで、1レコードずつのデータを取り出すことができます。

$ary_ref = $sth->fetch;

$sth　executeされたステートメントハンドル

　これを実行すると、1レコードのデータが取り出され、そのレコードのデータを持った配列のリファレンスを返します。実行するたびに、新しいレコードのデータが返され、もうレコードがなければundefを返します。 fetchは、fetchrow_arrayrefの別名です。

DB切断（disconnect：データベースハンドルメソッド）

$rc = $dbh->disconnect;

$dbh　connect済みのデータベースハンドル

　データベースとの接続を破棄します。通常、プログラムの終了時に実行されます。

覚えておくべき便利なメソッド

　基本的な使い方はあくまでも基本的な使い方です。 DBIには、より使いやすく、便利なメソッドが多くあります。ここでは、覚えておくと便利なものについて抜粋して解説します。

ハンドル共通メソッド

　DBIクラス、データベースハンドル、ステートメントハンドル、全てに共通して存在するメソッドです。

errstr

　最後に呼び出されたメソッドについて、データベースが返すエラー文字列を返します。
$dbh = DBI->connect($data_source, $username, $password) || die $DBI::errstr; $sth = $dbh->prepare($statement) || die $dbh->errstr; $rv = $sth->execute || die $sth->errstr;
　主に、上記のように使います。 CGIで使う場合は、dieの変わりに、エラーをhtml出力するサブルーチン等を用いるとよいでしょう。あるいは、dieとevalを組み合わせるのも有効です。

データベースハンドルメソッド

do

　いきなりステートメントを実行します。
$rc = $dbh->do($statement) || die $dbh->errstr; $statement　実行したいSQL文
　ステートメントをprepareし、executeします。 doは影響を受けたレコード数を返します。また、レコード数が0の場合は0E0、レコード数が不明の場合は-1を返します。データを受け取れないので、select文では使う意味がありません。doは、insert、update、delete等を実行するのにとても便利です。
selectall_arrayref

　selectの結果を配列のリファレンスで返します。
$ary_ref = $dbh->selectall_arrayref($statement); $statement　実行したいSQL文
　ステートメントをprepareし、executeし、全てのレコードについてfetch（fetchall_arryref）します。 selectall_arrayrefは、1レコードぶんのデータが入った配列のリファレンスが全件分入っている配列のリファレンスを返します。要するに、2次元配列を返してきます。
$val = $ary_ref->[$rows][$cols];
のように使います。全てのデータを配列にできるので非常に便利です。これだけ知っていれば、とりあえずselect文を使うことができる。とも言えます。

プレースホルダとバインド

　同じような内容で、パラメータだけが違うようなSQLを何度も実行しなければならないようなとき、その度にprepare、executeを行うのは効率がよくありません。そこで、prepare時には明確な値を与えずにprepareし、execute時にその値を与えてやるという方法があります。テーブルに複数のデータをinsertする場合、たとえば、以下のように書くことができます。

$statement = insert into table (id, name, age) values (?, ?, ?);
$sth = $dbh->prepare($statement);
foreach (@csvdata){
	@bind_values = split(/,/, $_);
	$rv = $sth->execute(@bind_values);
}

　こうすれば、登録するデータ量が多くても、prepareは1回で済むので、おそらくプログラムは格段に早くなります。このとき、　?　をプレースホルダ、後からそれに結び付けられる値をバインド値といいます。ここで、1つ覚えておくとよいことがあります。通常、文字列リテラルをSQLで使う場合、シングルクォートで囲ってやる必要があります。しかしバインド値として指定する場合には、それを考える必要はありません。つまり、プレースホルダを使うことで、文字列のクォート等の手間を省くことができるわけです。なお、doやselectall_arrayrefでも、

$rv = $dbh->do($statement, \%attr, @bind_values);
$ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);

のように、プレースホルダとバインド値を使うことができます。

トランザクション制御

　通常DBI/DBDのドライバは、AutoCommit設定になっており、データベースに対して行った変更は自動的に反映されます。トランザクションを制御するには、AutoCommitをオフにし、commitやrollbackを使う必要があります。なお、データベースによってはトランザクションをサポートしていない場合もありますので、DBDのドキュメントをチェックしてください。

重要関連項目

　関連する重要な項目です。

AutoCommit（データベースハンドルプロパティ）

　boolean（真/偽）型のプロパティであり、そのデータベースハンドルについて、現在のAutoCommitの状況を表しています。
　参照する場合は
... = $dbh->{AutoCommit};
　書きこむ場合は
$dbh->{AutoCommit} = ...;
のように使います。
　デフォルトは1（真）です。トランザクションを制御する場合は0（偽）を設定します。
commit（データベースハンドルメソッド）

　コメットさんにちょっと似ています。データベースに対する変更を確定させたいときに呼びます。
$rc = $dbh->commit;
　これによって、一連の変更がDBに反映されます。
rollback（データベースハンドルメソッド）

　データベースに対する変更を元に戻したいときに呼びます。
$rc = $dbh->rollback;
　これによって、一連の変更がDBに反映されません。

その他

RaiseError（共通プロパティ）

　boolean（真/偽）型で、継承されるプロパティです。トランザクションと直接には関係しませんが、組み合わせて使うと便利です。 RaiseErrorをオンにしておくことで、DBIは、エラーがおこったとき、 die("$class $method failed: $DBI::errstr") （$class はドライバ・クラス、 $method は失敗したメソッド）と同様の処理を行います。つまり、エラーメッセージを出して死ぬわけです。これをeval{}でトラップして以下のように使うと便利です。

$dbh->{AutoCommit} = 0;  # 可能であれば、トランザクションを有効にします
$dbh->{RaiseError} = 1;
eval {
    foo(...)        # INSERTやUPDATEを含む
    bar(...)        # 多くの処理を
    baz(...)        # ここでします。
    $dbh->commit;   # ここまで来たらコミットします。
};
if ($@) {
    warn "Transaction aborted because $@";
    $dbh->rollback; # undo the incomplete changes
    # 他のアプリケーションの後片付けの処理をここに入れます
}

[ASHのホームページへ]