準備と実行
Description
目的
prepare() および execute() によって、より強力で柔軟性のあるクエリを実行することができます。 準備/実行 モードが有用なのは、 値が違うだけの同じクエリを何度も実行する場合です。 例えば、住所の一覧をデータベースに登録する場合などがこれにあたります。
それ以外に 準備/実行 が有用となる場面としては、 異なる SQL 構文を持つ複数データベースをサポートする場合が考えられます。 二種類のデータベースをサポートする必要があり、それらはそれぞれ INSERT の構文が異なっているとしましょう。
db1: INSERT INTO tbl_name (col1, col2) VALUES (expr1, expr2)
db2: INSERT INTO tbl_name SET col1=expr1, col2=expr2
それぞれに対応するスクリプトを作成するには、 このような形式でクエリを配列にします。
<?php
$statement['db1']['INSERT_PERSON'] = 'INSERT INTO person
(surname, name, age) VALUES (?, ?, ?)';
$statement['db2']['INSERT_PERSON'] = 'INSERT INTO person
SET surname=?, name=?, age=?';
?>
さらに、Oracle 風の名前つきプレースホルダを使用することも可能です。 名前つきプレースホルダを使用すると、ひとつの文の中で 同じプレースホルダを複数回使用することができます。
<?php
$statement['db1']['INSERT_PERSON'] = 'INSERT INTO person
(surname, name, age) VALUES (:surname, :lastname, :age)';
$statement['db2']['INSERT_PERSON'] = 'INSERT INTO person
SET surname=:surname, name=:lastname, age=:age';
?>
注意: 標準的でない SQL 構文の使用はお勧めしません。 上の例は、単に「プリペアドステートメントではこんなこともできますよ」 ということを示すためだけのものです。可搬性が高いからという理由で MDB2 を採用されるのなら、標準的な SQL 構文を使うようにしましょう (ヒント: この例の場合、db1 の INSERT のほうが正解です)。
準備 (Prepare)
上の機能を使用するには、二段階の手続きが必要です。まず最初に、 MDB2_Statement_Common クラスのインスタンスを返す文を 準備 (prepare) します。 次に、それを 実行 (execute) します。
最初にすることは、 prepare() を使用して汎用的な SQL 文を準備することです。 手始めに、まず普通の SQL を書いてみます。
SELECT surname, name, age
FROM person
WHERE name = 'name_to_find' AND age < age_limit
そして、実行時に指定するリテラル値にあたる部分を "プレースホルダ" に置き換えます。
SELECT surname, name, age
FROM person
WHERE name = ? AND age < ?
それから、この SQL 文を prepare() に渡します。 このメソッドはステートメントクラスのインスタンスを返すので、これを使用して execute() をコールします。
prepare() は、さまざまな形式のプレースホルダ (あるいはワイルドカード) をサポートしています。デフォルトでは、すべてのプレースホルダは 文字列として扱われます。しかし、二番目のパラメータとして データ型 の配列を渡すと、 各プレースホルダの型を指定できるようになります。
DML (data manipulation language - INSERT, UPDATE, DELETE) 文は、 データの取得に比べて異なる返り値を持っています。そのため、 prepare() は三番目のパラメータを受け付けます。 DML 文の場合は、これを MDB2_PREPARE_MANIP に設定しなければなりません。データを読み込む際には、 これを MDB2_PREPARE_RESULT あるいは TRUE のいずれかに設定しなければなりません。 MDB2_PREPARE_RESULT の場合は、結果セットの各カラムの データ型 を配列で指定します。 TRUE の場合には、結果セットのデータ型を自動判定させます。
実行 (Execute)
文を準備したら、次に実行します。実行とは、 プリペアドステートメントの変数に値を代入することです。 execute() へ渡す引数として、代入するスカラー値あるいは代入する値の配列を指定します。
execute() にスカラーを渡す
<?php
// すでに $mdb2 という名前の MDB2 オブジェクトが存在するものとします
$sth = $mdb2->prepare('INSERT INTO numbers (number) VALUES (?)', array('integer'), MDB2_PREPARE_MANIP);
$sth->execute(1);
$sth->execute(8);
?>
プリペアドステートメントに複数のプレースホルダが含まれる場合は、 値の配列を execute() に渡さなければなりません。 配列の最初のエントリが最初のプレースホルダ、 二番目のエントリが二番目のプレースホルダ、…… というように対応します。この順番は、使用するプレースホルダの型に依存しません。
execute() に配列を渡す
<?php
// すでに $mdb2 という名前の MDB2 オブジェクトが存在するものとします
$types = array('integer', 'text', 'text');
$sth = $mdb2->prepare('INSERT INTO numbers VALUES (?, ?, ?)', $types, MDB2_PREPARE_MANIP);
$data = array(1, 'one', 'en');
$affectedRows = $sth->execute($data);
?>
名前つきプレースホルダを使用する場合は、データの配列を 連想配列にしなければなりません。 連想配列のキーがプレースホルダの名前に対応します。
execute() に配列を渡す
<?php
// すでに $mdb2 という名前の MDB2 オブジェクトが存在するものとします
$types = array('integer', 'text', 'text');
$sth = $mdb2->prepare('INSERT INTO numbers VALUES (:id, :name, :lang)', $types);
$data = array('id' => 1, 'name' => 'one', 'lang' => 'en');
$affectedRows = $sth->execute($data);
?>
名前つきプレースホルダを使用する場合は、データの配列を 連想配列にしなければなりません。 連想配列のキーがプレースホルダの名前に対応します。
execute() に配列を渡す
<?php
// すでに $mdb2 という名前の MDB2 オブジェクトが存在するものとします
$sth = $mdb2->prepare('SELECT name, lang FROM numbers WHERE id = ?', array('integer'), array('text', 'text'));
$result = $sth->execute(1);
?>
$data に渡す値はリテラルでなければなりません。 SQL の関数 (例えば CURDATE() など) を使用しないでください。 実行時に使用する SQL 関数は、プリペアドステートメントに 埋め込んでおく必要があります。同様に、識別子 (テーブル名やカラム名) も使用できません。なぜなら、これらは準備 (prepare) の段階で処理されるものだからです。
ExecuteMultiple
MDB2 には、複数のクエリを一度に実行する仕組みがあります。 つまり、複数のクエリをひとつひとつ手動で実行するのではなく、このようにします。
execute() に配列を渡す
<?php
// すでに $mdb2 という名前の MDB2 オブジェクトが存在するものとします
$alldata = array(array(1, 'one', 'en'),
array(2, 'two', 'to'),
array(3, 'three', 'tre'),
array(4, 'four', 'fire'));
$sth = $mdb2->prepare('INSERT INTO numbers VALUES (?, ?, ?)');
foreach ($alldata as $row) {
$sth->execute($row);
}
?>
これは、次の 4 つのクエリを発行します。
INSERT INTO numbers VALUES ('1', 'one', 'en')
INSERT INTO numbers VALUES ('2', 'two', 'to')
INSERT INTO numbers VALUES ('3', 'three', 'tre')
INSERT INTO numbers VALUES ('4', 'four', 'fire')
executeMultiple() を使用すると、上の例のようにわざわざ foreach を使用する必要がなくなります。
Extended モジュールの executeMultiple() を execute() の代わりに使用する
<?php
// すでに $mdb2 という名前の MDB2 オブジェクトが存在するものとします
$mdb2->loadModule('Extended', null, false);
$alldata = array(array(1, 'one', 'en'),
array(2, 'two', 'to'),
array(3, 'three', 'tre'),
array(4, 'four', 'fire'));
$sth = $mdb2->prepare('INSERT INTO numbers VALUES (?, ?, ?)');
$mdb2->extended->executeMultiple($sth, $alldata);
?>
結果は同じです。途中のクエリが失敗すると、 それ以降の未処理のクエリは実行されません。
execute() の返り値には三通りの可能性があります。 結果を返すクエリ (SELECT など) の場合に新しい MDB2_Result_Common オブジェクト、 データを操作するクエリ (INSERT など) の場合に整数値、そして失敗した場合には MDB2_Error オブジェクトが返されます。
データ型
MDB2 は、すべてのドライバにまたがる多くのデータ型をサポートしています。 プレースホルダを準備する際や結果セットに関連するメソッドなどで、 これを設定することができます。 サポートされるデータ型とその書式についての概要は こちら を参照ください。
プリペアドステートメントの開放
プリペアドステートメントの使用を終えた後にもしスクリプトの処理が続くのなら、 メモリを節約するためにプリペアドステートメントを開放するとよいでしょう。 そのために使用するのが free() です。
開放
<?php
// すでに $mdb2 という名前の MDB2 オブジェクトが存在するものとします
$sth = $mdb2->prepare('SELECT name, lang FROM numbers WHERE id = ?', array('integer'), array('text', 'text'));
$result = $sth->execute(1);
$sth->free();
?>
結果の行数の制限および途中の行からの読み込み
結果セットの限定された行数のみを読み書きしたい場合、あるいは 結果セットの途中の行から読み込みを開始したい場合などは setLimit() をコールしてから prepare() をコールします。 limit および offset は、その次に実行するクエリ発行メソッド あるいはプリペアドステートメントメソッドに対してのみ有効となり、 その後は設定がリセットされます。 これは、MDB2 が内部的に発行するクエリに対しても適用されます。 limit 機能をエミュレートで実現している RDBMS については、 DML 文では limit が動作しないことに注意しましょう。また、 この場合に何のエラーも発生しないことにも注意が必要です。
setLimit を使用した prepare
<?php
// すでに $mdb2 という名前の MDB2 オブジェクトが存在するものとします
// 10 行目から始め、20 行読み込みます
$mdb2->setLimit(20, 10);
$sth = $mdb2->prepare('SELECT name, lang FROM numbers WHERE group_id = ?', array('integer'), array('text', 'text'));
?>
忘却曲線を使ってこの知識を確実に記憶に残す
