2005-06-11

Active PerlでのMySQLの利用

Active Perl Windows版でMySQLが利用できる環境を作ります。MySQLのPerl用のデータベース・インターフェースとしてDBIモジュールとDBD::mysqlモジュールを使用します。

DBIとDBD::mysqlのインストール

本編で使用するActivePerlと、インストールするPerlモジュールのバージョンは以下の通りです。

ActivePerl 5.8.6.811 Windows版
DBI 1.48
DBD:mysql 2.9005_3

※DBIでトランザクションを使うにはDBD:: mysqlのバージョン1.2216以上が必要で、推奨は2.9003以上です。

DBD:mysqlのPPMリポジトリには以下を使用します。このリポジトリはMySQL(4.1.10)用DBDのPPMパッケージを公開しています。

http://theoryx5.uwinnipeg.ca/ppms/  ウィニペグ大学(カナダ)

インストールの実行は、コマンドプロンプトから行います。

C:\>ppm install DBI
C:\>ppm install http://theoryx5.uwinnipeg.ca/ppms/DBD-mysql.ppd

C:\>ppm install DBI
====================
Install 'DBI' version 1.48 in ActivePerl 5.8.6.811.
====================
・・・
Installing C:\usr\site\lib\DBI.pm
・・・
Successfully installed DBI version 1.48 in ActivePerl 5.8.6.811.

C:\>ppm install http://theoryx5.uwinnipeg.ca/ppms/DBD-mysql.ppd
====================
Install 'DBD-mysql' version 2.9005_3 in ActivePerl 5.8.6.811.
====================
・・・
Installing C:\usr\site\lib\DBD\mysql.pm
・・・
Successfully installed DBD-mysql version 2.9005_3 in ActivePerl 5.8.6.811.

DBD:mysql についての日本語ドキュメントは以下のURLを参照して下さい。

http://dev.mysql.com/doc/mysql/ja/perl.html
http://perldoc.jp/docs/modules/DBD-mysql-2.1026/DBD/mysql.pod

DBIによるMySQLの利用

perl-dbi01.gif

DBIは各種データベースドライバ(DBD)に対して統一的なインターフェースを提供します。 DBIの仕組みを右図に示します。この仕組みでは、データベース毎に違うインターフェースをDBIが包み込み、Perlプログラムからは統一されたインターフェースでデータベースにアクセスできます。従って、DBIにより実装されたPerlプログラムは移植性に優れています。また、データソース名を変えるだけで、多様なデータベースに対応したプログラムを作る事ができます。

データソース名とは、DBI経由でデータベースに接続する時の文字列で、一般に「ドライバ名:データベース名:ホスト名:ポート番号」として指定します。

以下では、CGIからMySQLを利用するサンプルプログラムを作成し、例題を通してDBIの紹介を行います。

サンプルプログラム(sqlfile.cgi)の仕様

(1)プログラムはSQLスクリプトファイル(テキストファイル)を読み込みます。

(2)SQLスクリプトファイルにはSQL文、EVAL文、コメントを含みます。文は;で区切り、コメントは#で始まります。

(3)SQL文がSELECT文(SHOWを含む)の場合は検索結果を表示し、非SELECT文の場合は実行のみ行います。

(4)EVAL文はperlのeval関数で実行します。但し複文の実行はできません。例えば:EVAL sleep(1);
  ※クォート処理をしていないので、EVAL print 'Hellow;'; などは途中で文が区切られエラーになります。

(5)検索結果及び実行結果はHTMLで出力します。

(6)SQLスクリプトファイルはURLのクエリ文字列で指定します。
  例えば:http://localhost/mysql/sqlfile.cgi?sample.sql

サンプルプログラムを使った例題

ブラウザからサンプルプログラム(sqlfile.cgi)を実行します。

http://localhost/mysql/sqlfile.cgi?sample.sql

sample.sql

# テーブル作成
DROP TABLE IF EXISTS syain;
CREATE TABLE syain (
  syain_no int(10) NOT NULL,
  syain_name varchar(50),
  bumon_no int(10),
  PRIMARY KEY (syain_no)
);

# テーブルにデータを挿入
INSERT INTO syain VALUES(1,'Suzuki',3);
INSERT INTO syain VALUES(2,'Yamamoto',1);
INSERT INTO syain VALUES(3,'Tanaka',2);

# テーブルの検索
SELECT * FROM syain;

MySQLのカラム属性は数値型でも syain_no int(10) のように 精度を記述できますが、これは格納精度ではなくて表示精度を指定する目的で使用されます。詳しくは以下を参照下さい。
http://dev.mysql.com/doc/refman/4.1/ja/numeric-types.html

実行結果

perl-dbi02.gif


以下、サンプルプログラム(sqlfile.cgi)に従ってDBIの説明をします。サンプルプログラムの全ソースコードは本章の最後に掲げています。

接続と切断

use DBI;

### データベースへの接続
$dsn  = "DBI:mysql:test:localhost:3306";
$user = "sunlight";
$pass = "password";
%attr = (RaiseError=>0, PrintError=>1,AutoCommit=>1);

$dbh = DBI->connect($dsn,$user,$pass,\%attr)
       || CgiError("connect",$DBI::errstr);

### データベースの切断
$dbh->disconnect();

connectメソッド

$dbh = DBI->connect($dsn,$user,$pass,\%attr);

connectはDBIクラスのクラスメソッドで、データソース$dsnに接続します。接続時にユーザ認証が必要な場合は$userと$passで指定します。connectは接続に成功した場合、データベースハンドルオブジェクトを返します。失敗した場合は、undefを返し、DBI::errにエラーコードをDBI::errstrにエラーメッセージを設定します。

DBD::mysqlでの$dsnの一般的な指定方法は、

$dsn = "DBI:mysql:$databese:$host:$port";
$dsn = "DBI:mysql:database=$database;host=$hostn;port=$port";

です。DBI::ドライバ名に続く文字列に標準的な形式は無く各ドライバの仕様に従います。DBD::mysqlでは$databeseは必須です。$hostを省略するローカルホストに接続します。$portを省略すると標準のポートが使用されます。サンプルプログラムではlocahhostに標準のポート3306で接続していますが、これらを省略しても接続できます。

connectは属性%attrを伴った接続ができます。上例では3つの属性を設定していますが、これらの設定は全てデフォルト値です。これはらの属性は接続後にハンドル属性としてもアクセスできます。

RaiseError

デフォルトは偽です。RaiseErrorが真の場合、エラーが発生するとdie()が使用されプログラムが終了します。従って、この場合のエラー処理はevalで行う事ができます。RaiseErrorが偽の場合、エラー処理はDBIメソッドの呼び出し側で行う必要があります。サンプルプログラムでのエラー処理は後者による方法を採用しています。一方、前者の例としては、重要なトランザクション処理の例外をevalで補足する場合などです。この場合は次にようにします。

$dbh->{RaiseError} = 1;
$dbh->{AutoCommit} = 0;
PrintError

デフォルトは真です。PrintErrorが真の場合、 エラーが発生するとwarn()が使用されエラーメッセージを出力します。PrintError、RaiseError共に真の場合、 PrintErrorはスキップされます。

AutoCommit

デフォルトは真です(将来のDBIでは変更になる可能性が示唆されています)。AutoCommitが真の場合、自動トランザクション機能が有効になります。但し、データベースシステムがトランザクション機能に対応している必要があります。トランザクション機能の無いデータベースでは、AutoCommitは常に真として扱われます。本編でインストールしたDBD:: mysqlはDBIの仕様に従ったトランザクション機能がサポートされてます。

MySQLでは、AutoCommit属性はサーバー変数autocommitに対応しています。AutoCommitが偽の場合、トランザクションのコミットとロールバックは、以下のメソッドで行う事ができます。

$dbh->commit();
$dbh->rollback();
また、AutoCommitが0から1に変更されると未解決のデータベース変更がコミットされます。

disconnect メソッド

$dbh->disconnect();

データベースの切断にはデータベースハンドルオブジェクトのdisconnectメソッドを使用します。

非SELECT文の実行

unless( $dbh->do($sql) ){
  SqlError("DoSqlScript", $dbh->errstr);
}

サンプルプログラムでは全ての非SELECT文をdoメソッドで処理しています。単一の非SELECT文に対してはdoメソッドが最も使い勝手が良く、SQL文($sql)をdoメソッドに与えるだけです。

doメソッド

$dbh->do($sql,\%attr,@bind_values);

doメソッドは単一のSQL文($sql)を実行します。実行に失敗した場合にundefを返します。また、引数として、属性(%attr)とバインド値のリスト(@bind_values)を与える事ができます。doメソッドは、原理的に一対のprepare/executeメソッドと同じ動作をします。

$sth = $dbh->prepare($sql,\%attr);
$sth->execute(@bind_values);

%attrと@bind_valuesについては以下を参照して下さい。

prepareメソッド

$sth = $dbh->prepare($sql,\%attr);

prepareメソッドは単一のSQL文($sql)に対する準備を行い、ステートメントハンドルオブジェクトを返します。準備に失敗した場合はundefを返します。prepareにはステートメントハンドルオブジェクトの属性%attrを指定する事ができます。例えば、RaiseErrorやPrintErrorなどです。これらの属性は、親のデータベースハンドルから値を引き継ぎますが、ステートメントハンドルの属性を変更しても親の属性には影響ありません。

executeメソッド

$sth->execute(@bind_values);

executeメソッドは、準備されたSQL文を実行します。成功した場合はなんらかの真になる値を、失敗した場合はundefを返します。@bind_valuesが指定された場合は、プレースホルダにバインド値を与える為にbind_paramメソッドが呼び出されます。但し、executeメソッドによるデータバインドでは、型情報をドライバに渡す事はできません。型情報をドライバに渡すには、明示的にbind_paramメソッドを使用します。

プレースホルダ

例えば以下のSQL文を見てください。

insert into syain (syain_no,syain_name) value ( ?, ? )
select * from syain where syain_no = ?
select * from syain where syain_name like ?

文中の文字?はプレースホルダと呼ばれます。実際の値をバインドするには、executeやbind_paramメソッドが使われます。プレースフォルダの型(数か文字列か)を指定するにはbind_paramメソッドの方を使用します。

プレースホルダ使用時の注意点は、それが文字列であってもクォートしないと言う点です。クォートの有無は値をバインドする時に決定されます。従って、likeで使用するワイルドカードはバインド値に含める必要があります。また、undefをバインドするとSQLのNULLになりますが、次の場合は上手くいきません。

SELECT * FROM syain WHERE bumon_no = ?  ⇒ SELECT * FROM syain WHERE bumon_no = null (誤り)

bind_paramメソッド

$sth->bind_param($p_num,$bind_value,$bind_type);

bind_paramメソッドは、SQL文の$p_num番目のプレースホルダに値($bind_value)をバインドします。$p_numは1から数えます。引数$bind_typeはプレースフォルダのデータ型に対するヒントを与えます。バインド時の型に関する興味は、それが「数か文字列か」です。文字列をバインドした場合、quoteメソッドのようにエスケープ処理が行われます。

$bind_typeで使用できる値は、標準のSQL型で、DBI::SQL_VARCHARやDBI::SQL_INTEGERなどです。これらは実際には関数で実装されていて、SQL_VARCHARは12をSQL_INTEGERは4を返します。SQL型名をインポートして使う場合には以下のようにします。

use DBI qw(:sql_types);

$sth = $dbh->prepare("insert into syain (syain_no,syain_name) value ( ?, ? )");
$sth->bind_param(1, $syain_no, SQL_INTEGER);
$sth->bind_param(2, $syain_name, SQL_VARCHAR);
$sth->execute() || die $sth->errstr;

quoteメソッド

$dbh->quote($value);

quoteメソッドは、文字列($value)の外側をクォート文字で囲み、文字列に含まれる特殊文字(クォート文字など)をエスケープします。$valueがundefの場合、文字列 'NULL' を返します。以下に使用例を示します。

$sql = sprintf "insert into syain (syain_no,syain_name) value ( %s, %s )",
               $syain_no,
               $dbh->quote($syain_name);
$dbh->do($sql);


以上が、非SELECT文をDBIで実行する為の簡単な説明です。この説明の大部分はSELECT文を実行する場合でも同じです。但し、SELECT文をdoメソッドで実行しても無意味です。なぜなら、検索結果の行フェッチができないからです。SELECT文の実行は一対のprepare/executeメソッドで行います。

SELECT文の実行

### 検索実行
unless( $sth = $dbh->prepare($sql)){
  SqlError("DoSelect", $dbh->errstr);
}
unless( $rv = $sth->execute){
  SqlError("DoSelect", $sth->errstr);
}

# カラム名の取得
$colnum = $sth->{NUM_OF_FIELDS};
print "<tr>";
for( my $i = 0; $i<$colnum; $i++){
  push @colname, $sth->{NAME}->[$i];
}

# 行のフェッチ
while (my $row = $sth->fetchrow_hashref) {
  for( my $i = 0; $i<$colnum; $i++){
    my $field = $row->{$colname[$i]};
  }
}

$sth->finish;

サンプルプログラムではSELECT文(とSHOW文)をprepare/execメソッドで処理しています。prepare/executeメソッドの使い方は非SELECT文の場合と同じです。SELECTの場合は、executeメソッド実行後、ステートメントハンドル($sth)を使って、検索したカラム数($sth->{NUM_OF_FIELDES})やカラム名($sth->{NAME})の取得、行フェッチ($sth->fetchrow_hashrefなど)を行っています。全ての行フェッチが終了した後はfinishメソッドを呼び出します。

NUM_OF_FIELDS属性

$colnum = $sth->{NUM_OF_FIELDS};

NUM_OF_FIELDS属性はSELECT文によって返されるカラムの数を返します。

NAME属性

$colname = $sth->{NAME};

NAME属性 はSELECT文によって返されるカラム名のリファレンスを返します。他のデータベースへの移植性を考慮する場合は、NAME_lcまたはNAME_ucを使います。NAME_lcは常に小文字のカラム名を、NAME_ucは常に大文字のカラム名を返します。

fetchrow_hashrefメソッド

$row = $sth->fetchrow_hashref($name);

fetchrow_hashrefメソッドはexecuteされた1行をフェッチします。戻り値はカラム名をキーにしたハッシュのリファレンスです。次の行が存在しない(EOF)かエラーの場合はundefを返します。EOF かエラーかの判定を行う場合は、$sth->errをチェックします。実際のフィールド値を参照する場合は、$row->{カラム名}で行います。フィールド値がNULLの場合はundefを返します。

オプションの引数$nameには'NAME','NAME_lc' または'NAME_uc'を使用します。デフォルトは'NAME'です。返されるハッシュキーは $sth->{$name} と同じで、'NAME_lc' の場合は常に小文字のカラム名に、'NAME_uc' の場合は常に大文字のカラム名になります。移植性による理由から'NAME_lc'または'NAME_uc'の使用が推奨されています。

fetchrow_hashrefメソッドと同じ系統のメソッドにfetchrow_arrayrefとfetchrow_arrayがあります。これらのメソッドは、fetchrow_hashrefより効率の良い行フェッチを行うと言われています。

fetchrow_arrayrefメソッド

$row = $sth->fetchrow_arrayref;

fetchrow_arrayrefメソッドはexecuteされた1行をフェッチします。戻り値は配列のリファレンスです。次の行が存在しない(EOF)かエラーの場合はundefを返します。EOF かエラーかの判定を行う場合は、$sth->errをチェックします。実際のフィールド値を参照する場合は、$row->[$i]で行います。フィールド値がNULLの場合はundefを返します。返されるフィールドの順はSELECT句で指定した順です。

サンプルプログラムは、fetchrow_arrayrefメソッドを使用した場合、以下のようになります。

while (my $row = $sth->fetchrow_arrayref) {
  for (my $i = 0; $i<$colnum; $i++){
    my $field = $row->[$i];
  }
}

fetchrow_arrayメソッド

@row = $sth->fetchrow_array;

fetchrow_arrayメソッドはexecuteされた1行をフェッチします。戻り値はフィールド値からなるリストです。フィールド値がNULLの場合はundefを返します。次の行が存在しない(EOF)かエラーの場合は空リストを返します。EOF かエラーかの判定を行う場合は、$sth->errをチェックします。返されるフィールドの順はSELECT句で指定した順です。

スカラコンテキストで使用すると検索結果の第1フィールドの値を返します。この場合は、EOFかエラーか、またはフィールド値がNULLかの判定ができません。

サンプルプログラムは、fetchrow_arrayメソッドを使用した場合、以下のようになります。

while (my @row = $sth->fetchrow_array) {
  for (my $i = 0; $i<$colnum; $i++){
    my $field = $row[$i];
  }
}

finish メソッド

finishメソッドはこれ以上行フェッチしない事をDBIに知らせます。finishメソッドは全ての行がフェッチされると自動的に呼び出されます。

サンプルプログラムのソースコード

#!/usr/bin/perl -w
use strict;
use DBI;

#-----------------------------------------------------------
# Main
#-----------------------------------------------------------
### SQLスクリプトファイルの読み込み
my ($sql_file, @sql_text);

$sql_file = $ENV{QUERY_STRING};
CgiError("URL","Unknown script file name.") unless $sql_file;
@sql_text = ReadScriptFile($sql_file);

### データベースへの接続
my ($dbh,$dsn,$user,$pass,%attr);

$dsn  = "DBI:mysql:test:localhost:3306";
$user = "sunlight";
$pass = "password";
%attr = (RaiseError=>0, PrintError=>1,AutoCommit=>1);

$dbh = DBI->connect($dsn,$user,$pass,\%attr)
       || CgiError("connect",$DBI::errstr);

### レスポンス処理
HTML_Begin();              # HTMLの開始
DoSqlScript( @sql_text );  # SQLスクリプトの実行
HTML_End();                # HTMLの終了

### データベースの切断
$dbh->disconnect();

#-----------------------------------------------------------
# SQLスクリプトの実行
#-----------------------------------------------------------
sub DoSqlScript{
  my @sql_text = @_;

  $|=1; # STDOUTのAUTOFLUSHをONにする
  foreach (@sql_text){
    my $sql = $_;

    my $out = HTML_Escape(length($sql)<60 ? $sql : substr($sql,0,60)." ...");
    print "$out<br />\n";

    next unless ($sql =~ /\S/);   # 空行は飛ばす(<br/>出力のみ)
    if ($sql =~ /^eval\s+(.+)/i){ # 特別なEVAL文の実行
      eval $1;
      if ($@) { SqlError("eval", $@); last; }
      next;
    }

    if ($sql =~ /^(select|show)\s/i){ #SELECT文と非SELECT文で処理を分ける
      unless(DoSelect($dbh, $sql)){
        last;
      }
    }else{
      unless( $dbh->do($sql) ){
        SqlError("DoSqlScript", $dbh->errstr);
        last;
      }
    }
  }
}

#-----------------------------------------------------------
# SELECT文の実行
#-----------------------------------------------------------
sub DoSelect{
  my $dbh = shift;
  my $sql = shift;
  my ($sth,$rv,$colnum,@colname);

  ### 検索実行
  unless( $sth = $dbh->prepare($sql)){
    SqlError("DoSelect", $dbh->errstr);
    return undef;
  }
  unless( $rv = $sth->execute){
    SqlError("DoSelect", $sth->errstr);
    return undef;
  }

  ### 検索結果の表示
  print "<table>\n";
  # カラム名の取得
  $colnum = $sth->{NUM_OF_FIELDS};
  print "<tr>";
  for( my $i = 0; $i<$colnum; $i++){
    push @colname, $sth->{NAME}->[$i];
    print "<th>$colname[$#colname]</th>\n"
  }
  print "</tr>\n";

  # 行のフェッチ
  while (my $row = $sth->fetchrow_hashref) {
    print "<tr>";
    for( my $i = 0; $i<$colnum; $i++){
      my $field = $row->{$colname[$i]};
      $field = HTML_Escape($field);
      print "<td>$field</td>\n";
    }
    print "</tr>\n";
  }
  print "</table><br />\n";

  $sth->finish;
  return $rv;
}

sub HTML_End{
  print "</body></html>\n";
}

# HTMLのエスケープ処理
sub HTML_Escape{
  my $str = shift || return '';
  $str =~ s/&/&amp;/g;
  $str =~ s/</&lt;/g;
  $str =~ s/>/&gt;/g;
  $str =~ s/"/&quot/g;
  $str =~ s/'/&#39;/g;
  $str =~ s/ /&nbsp;/g;
  $str =~ s/\n/<br \/>\n/g;
  return $str;
}

#-----------------------------------------------------------
# HTMLの開始と終了
#-----------------------------------------------------------
sub HTML_Begin{
  print "Content-type: text/html; charset=shift_jis\n\n";
  print <<EOF;
<html>
<head>
<style type="text/css">
body  {font:10pt monospace;}
table {font:10pt monospace;border-collapse:collapse;}
th,td {border:solid 1px #000000;}
th    {background-color:#eeeeee;}
</style>
</head>
<body>
EOF
}

#-----------------------------------------------------------
# SQLスクリプトファイルの読み込み
#-----------------------------------------------------------
sub ReadScriptFile{
  my $sql_file = shift;
  CgiError("URL","Unknown script file name.") unless $sql_file;

  open(FH, $sql_file) || CgiError("open","Can't open $sql_file.");

  my $sql_text='';
  while(<FH>){
    s/#.*//; s/^\s*//; s/\s*$//; #コメントと前後の空白を除去
    $sql_text .= $_;
  }
  close FH;
  return split(/;/, $sql_text);  #;で区切る(空行は許す)
}

#-----------------------------------------------------------
# CGIエラー処理
#-----------------------------------------------------------
sub CgiError{
  print "Content-type: text/plain; charset=shift_jis\n\n";
  print "[Error]$_[0]: $_[1]";
  exit;
}

#-----------------------------------------------------------
# SQLエラー処理
#-----------------------------------------------------------
sub SqlError{
  print "<span style='color:red'>[Error]$_[0]: $_[1]</span>";
}


添付ファイル: filesqlfile.cgi 3743件 [詳細]

最終更新のRSS Last-modified: Thu, 03 Aug 2006 16:43:30 JST (4128d)