6部 データベース・インタフェイス

DBI クイックリファレンス

2009年7月6日

クイックコマンド集

DBIには数多くののコマンドが用意されていますが、下記のコマンドをおぼえておけば問題ないでしょう。

メソッド 解説
connect データベースサーバと接続する
disconnect データベースサーバとの接続を切る
prepare SQL文を設定する
execute 設定されたSQL文を実行する
do SQL文を設定し、実行する
quote 挿入するためのクォート文字、またはBLOB
fetch フィールドの配列参照として次の行を取り出す。最速。
fetchrow_array フィールドの配列として次の行を取り出す。簡単。
fetchrow_hashref ハッシュテーブルへの参照として次の行を取り出す。便利。
PAGE TOP

アウトライン使用法

Perlからデータベースに接続するには、最初にDBIモジュールを読み込みます。

# DBI モジュールの読み込み
use DBI;

その後に、connectメソッドを使ってデータベースに接続します。connectメソッドは、データソース、ユーザ名、パスワードの引数を受け取ります。データソースには、先頭にDBI 、次にDBMS名(MySQLOracleなど)、データベース名、ホスト名、ポート名をコロン( : )で区切って指定します。

# データソースの指定
$dsn = 'DBI:mysql:mydb:www:3306';

上記は、MySQLにあるmydbというデータベースに、wwwというホストから 3306 ポート(デフォルトのポート番号)で接続しています。ホスト名とポート番号は省略可能です。

# データソース
$dsn = 'DBI:mysql:mydb';
# ユーザ名
$user = 'webuser';
# パスワード
$password = 'mypass';
# データベースへ接続
$dbh = DBI->connect($dsn, $user, $password,
{ RaiseError => 1, AutoCommit => 0 });

connectメソッドの1つ目の引数はデータソースの指定、2つ目と3つ目は、データベース・アカウントのユーザ名と、そのパスワードを指定します。

connectメソッドで接続がすめば、あとはデータを挿入したり変更するメソッドを実行させます。実際には挿入、削除、変更、選択などの命令文をprepareメソッドで準備し 、executeメソッドで用意したSQL文を実行させます。

- SELECT
# prepareが成功した場合、ステートメントハンドルが返されます
my $sth = $dbh->prepare(
"SELECT f2, f3 FROM table WHERE f1=?"
);
# SQL文の実行
$sth->execute;
# データの取得と出力
while(@row = $sth->fetchrow_array) {
print "@row\n";
}
 - プレースホルダの利用

データを検索したり、データに登録する値をユーザの入力や外部ファイルから取得する際は、セキュリティを考慮してプレースホルダを利用することが望ましいです。プレースホルダは値を安全にするための機構で、実行時に受け取った値を検査し、実行時に問題となる文字列などを置換した後に、先に準備していたSQL文に埋め込みます。MySQLではプレースホルダ名をクエスチョン( ? )で表します。

# SQL命令文の準備(プレースホルダの利用)
# クエスチョン( ? )の部分が、実行時に変数で渡された値と置換されます
$sth = $dbh->prepare(
"INSERT INTO table VALUES (?,?,?)"
);
while(<CSV>) {
chop;
my ($foo,$bar,$baz) = split /,/;
# CSVから取得したバインド値を指定してSQL文を実行
$sth->execute($foo,$bar,$baz);
}
 - データベースに変更を確定(AutoCommitがオフの場合)
$dbh->commit;
$dbh->rollback;
 - データソースでの作業を終了
$dbh->disconnect;
PAGE TOP

高レベル取り出しメソッド

DBIにはprepareexecute、取り出しループ、finishを組み合わせた便利なメソッドがあります。 たとえば次のようなメソッド、selectrow_arrayselectcol_arrayrefselectall_arrayrefなどです。

selectrow_arrayは1つの行または列の値を取り出すときに便利です。リストコンテキストで呼ばれると、先頭行が配列で返されます。

@row = $dbh->selectrow_array(
"SELECT * FROM table "
);
print $row[0], $row[1], "\n" if @row;

スカラコンテキストの場合は、先頭行の最初の列の値を返します。

$ref = $dbh->selectrow_array(
"SELECT f1 FROM table "
);
print $ref, "\n" if $ref;

問い合わせの結果が行を返されなければ、どちらのコンテキストで呼ばれたかによって、空リストかundefを返します。

行を返さない問い合わせにはdoメソッドを使います。doメソッドはステートメントを実行し、影響のあった行を返すか、エラーを示すundefを返します。

$count = $dbh->do("INSERT INTO table SET f1='***'");
$count = $dbh->do("DELETE FROM table LIMIT 1);
PAGE TOP

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

プレースホルダ

プレースホルダを解説する前に、その使い方を紹介します。

 - クエスチョン( ? )を利用したステートメント1
INSERT INTO table VALUES (?, ?, ?)
 - クエスチョン( ? )を利用したステートメント2
SELECT f2 FROM table WHERE f1 = ?

上記で使用したクエスチョン( ? )マークをプレースホルダと呼びます。プレースホルダは、そのままでは値を持っていないので、後で実際の値を結びつけます。参照される値をバインド値、実際の値との結びつけをバインドと呼びます。

SQLLIKEと一緒にプレースホルダを使う場合、プレースホルダは文字列全体を置き換えます。そのためLIKE ?とする場合、LIKEの値にワイルドカードが必要な際は、ワイルドカードも含める必要があります。

# プレースホルダを利用したSQL文の用意
my $sth = $dbh->prepare("INSERT INTO table VALUES (?, ?, ?)");
# 3番目の引数でワイルドカードを使用
$sth->execute($f1, $f2, "%$f3");

※いくつかのドライバはプレースホルダとバインド値をサポートしない。

プレースホルダの意義

プレースホルダを使えば、一度だけINSERT文を準備(prepare)します。各行に対応するバインド値はexecuteメソッドを呼び出すときに与えることができます。何度も準備する必要がなく、アプリーケーションは通常、何倍も速くなります。

# プレースホルダを利用したSQL文の用意
my $sth = $dbh->prepare("INSERT INTO table VALUES (?, ?, ?)")
or die $dbh->errstr;
# 何らかのループ処理
.... {
# バインド値を用意
my($product_code, $qty, $price) = split /,/;
# SQL文の実行
$sth->execute($product_code, $qty, $price);
}
# プレースホルダを利用したSQL文の用意
my $sth = $dbh->prepare(
q{ INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?) }
) or die $dbh->errstr;
# 何らかのループ処理
.... {
# バインド値を用意
my($product_code, $qty, $price) = split /,/;
# SQL文の実行
$sth->execute($product_code, $qty, $price);
}

この例の中で使用されたq{}スタイルの引用は、SQLステートメントの中で使用されるかもしれない引用との衝突を回避します。文字列の中に解釈して欲しい変数がある場合、qq{...}を利用すれば、ダブル・クォートで囲んだのと同じ効果が得られます。

Perlの変数をSELECT文の出力カラムに結びつけるために使われるメソッドについてはbind_columnsを参照してください。

NULL値

NULL値は、Perlでいう未定義の値(undef)になります。ただし、SELECT文での修飾詞にNULL値を使う場合には注意が必要です。

select description from products where product_code = ?

バインド値にundefや空文字をセットしても、期待した結果は得られません。NULLを選択するためには、IS NULLを使います。

SELECT * FROM db_name WHERE name IS NULL

Comment

コメントを残す

メールアドレスが公開されることはありません。

関連記事

リズムファクトリーはホームページの制作会社です。
ホームページ制作に関するご要望・ご相談はこちらからどうぞ。

株式会社リズムファクトリーでは現在、下記の職種について人材募集を行っております。
求人をクリックすると「求人情報サイトFind Job!」の求人詳細画面が開きますので、こちらからご応募下さい。

提供 : Webな人の求人情報サイト Find Job!