yado.rbの使い方（１）

ADO｜ADOXによるAccessおよびExcelファイルの処理
〜rubyによる制御〜　part １／２

最終更新日: 2009/11/03

• 1.　はじめに
• (1) yado.rb作成に際して重視したこと
• (2) 開発環境
• (3) 動作環境
• (4) yado.rbが処理対象とするファイル
• 2.　簡単なデータベースの作成、テーブルの作成、レコードの挿入と参照
• (1) yado.rbの置き場所
• (2) クラスオブジェクトの作成
• (3) SQL命令の実行
• (4) データベースを閉じる
• ［補足１］　sample01.rb の実行の仕方
• ［補足２］　SQLの insert によるレコードの挿入とデータ型
• 3.　レコードの挿入(データベースへのデータの書き込み)
• (1) レコード挿入用メソッド insert_records と addnew
• ［補足］　レコード挿入時のデータ型
• (2) CSVまたはタブ区切りテキストを読み込んでレコード挿入
• (3) レコード挿入時のバイナリ型データの扱い方、および別ファイルの取り込み
• ［補足１］　バイナリデータの大きさの制限
• ［補足２］　バイナリデータの参照(読み込み)の技述情報
• ［補足３］　生のバイナリデータとOLEオブジェクトの違い
• (4) レコード挿入時のID番号等の自動的生成：オートインクリメント
• ［補足］　データ型の yesno
• 4.　レコードの更新および削除
• (1) レコードの更新: SQL命令による方法
• (2) レコードの更新: Yadoのupdate_records, update_pairメソッドによる方法
• (3) レコードの削除: SQL命令による方法
• (4) レコードの削除: Yadoのdelete_recordsメソッドによる方法
• (5) その他のレコード更新用メソッド: update_array, update_hash
• 5.　データベース参照用のYadoのメソッド
• (1) テーブル＆フィールド情報を書き出す print_field_info
• ［補足］　mdbファイルにおけるテーブルの種類
• (2) とりあえず総てのデータを出力する print_all_values
• (3) 指定テーブルのバイナリ型データをファイルに書き出す print_binary_values
• 6.　SQL命令によるデータベース参照の例
• (1) 特定のフィールドによる整列 order by
• (2) 検索条件を指定する where
• (3) 仮装テーブル VIEW の利用
• (4) テーブルの結合

　Windowsに用意されている ADO(注1)およびADOX(注2)の仕組を利用すれば、データベースソフトのAccessのファイルを作成・参照・更新できます。Excelのファイルも同じ仕組によって処理できます。Access, Excelがインストールされていないパソコンでも大丈夫です。

　そうしたADOによる処理をrubyで実現するために作ったのが yado.rb です。総てrubyスクリプトで書かれています。

　Windows上で ruby.exe を実行できる環境があれば、yado.rbを用いて、AccessやExcelのファイルを操作するrubyスクリプトを簡単に作成できます。

• 注1　ADO: ActiveX Data Objects
  　ADOの仕組みを使えば、SQL(Structured Query Language: 構造化照会言語)の命令文を実行できるほか、SQL以外でのデータベース処理も行えます。
• 注2　ADOX: ActiveX Data Objects Extensions for DDL and Security
  　ADOXは、ADOに不足しているデータ定義(DDL: Data Definition Language)などの機能を提供します。

(1) yado.rb作成に際して重視したこと

　Accessを使わずに、なるべく簡単なrubyスクリプトで、そのファイルを作成・参照・更新できるようにすることを目標にしました。

　なにより「簡単なrubyスクリプトで」が第一で、例えば、下に掲げる実質４行のスクリプトで、Access付属のNorthwind.mdb(注)の標準テーブルの中身を総てCSVファイルに書き出すことができます。

　SQL命令の実行で可能なことであっても、「より簡単に」をめざして、レコードの挿入・削除・更新を行うための独自のメソッドを設けました。

　特にAccessのファイルの中身をCSV・タブ区切りテキストの形で書き出すことを重視しました。

 −−−− Northwind.mdbの中身をCSVに書き出すスクリプトここから
 #! ruby.exe -KS
 require 'yado'
 db = Yado.new  'Northwind.mdb'
 db.print_all_values  'OUTPUT.CSV'  # OUTPUT.XLSだとExcelファイルとして書き出す
 db.close
 −−−− ここまで

(注)　Northwind.mdbは、下の愛媛大学のサイトからダウンロードできます。
「コンピュータ演習 Excel2000 & Access2000」

(2) 開発環境

　Windows XP, ADO ver 2.7, MSWin版 ruby ver 1.8.7

　　CPU：インテル(x86)・2800MHz, メモリー：190MB

　　2002年に購入した今となっては旧いパソコンですが、ADOは最初からインストールされていました。

(3) 動作環境

　当ドキュメントで紹介しているサンプルスクリプトを試したところ、上の開発環境のほか、Windows VISTA, ADO ver 6.0 でも動きました。

　ruby.exeについては、Windows上で動くものであって win32ole.so が用意されていれば、その種類は問わないと思います。バージョンは、1.8.x であれば大丈夫だと思います。1.9.x では試していません。

(4) yado.rbが処理対象とするファイル

• Accessファイル：　拡張子が .mdb のファイル。yado.rbが主な対象としているのはこのmdbファイルです。なお、Office2007がインストールされていれば、Access2007用ファイル（＊．accdb）も扱えます。Access2007はインストールされていなくてもＯＫです。
• Excelファイル：　拡張子が .xls のファイル。mdbファイルに比べると、使えない機能や独特の仕様があります。

　ADOの仕組を通して、Lotus、Paradox、あるいはmysqlやpostgresqlなどのデータベースファイルを扱えるようですが、yado.rbではそこまで拡張していません。

　以下では、ADOやSQLに関して私なりに調べた情報を交じえながら、yado.rbの解説を記します。

　mdbファイルを基にして説明します。xlsファイルの扱いについては最後の方で触れます。

　なお、以降で提示するスクリプトやデータファイルは、別途 付属させてありますので、必要に応じて参照して下さい。

　ここでは、とりあえず、データベースの作成、テーブルの作成、レコードの挿入と参照の簡単な例を一通り取り上げます。

　テーブルは、行と列からなる表です。レコードは行に相当し、フィールドが列に相当します。そうしたテーブルがいくつか含まれているのがデータベースです。

　ところで、yado.rbで定義されているクラスの名前は Yado です。

　先ずは、これを使用するrubyスクリプトの例を下に掲げてみます。

△ sample01.rbの概要：　「ID, 氏名, 身長, 誕生日」の４項目からなるテーブルを作成し、２人分のデータを書き込む。最後にそれらを出力。それら総てSQL命令により実行。
△ 用いるSQL命令：　create table, insert, select

 −−−− ここから
 # sample01.rb: 簡単なデータベースの作成、テーブルの作成、レコードの挿入と参照
 require 'yado'
 
 db = Yado.new("TEST.MDB")  # TEST.MDBを開く。存在しなければ新規作成。
 
 sql = <<EOS1
 create table テーブル01
    (ID int PRIMARY KEY,
     氏名 varchar(30) NOT NULL,
     身長 float,
     誕生日 date);
 EOS1
 db.query(sql)  # クエリの実行：テーブル01の作成
 
 sql = <<EOS2
 insert into テーブル01 (ID, 氏名, 身長, 誕生日)
     values(1, '高橋', 172.3, #1994/10/15#);
 EOS2
 db.query(sql)  # テーブル01にレコード挿入
 
 sql = <<EOS3  # フィールド名の列記を省略する例
 insert into テーブル01
     values(2, '鈴木', 180.6, #1994/1/9#);
 EOS3
 db.query(sql)
 
 puts 'sample01.rb: 簡単なデータベースの作成、テーブルの作成、レコードの挿入と参照'
 sql = "select * from テーブル01;"
 db.query(sql) {|rs|
   p rs  # レコード(各フィールド)の値を出力
 }
 db.close  # データベースを閉じる
 −−−− ここまで

(1) yado.rbの置き場所

　yado.rbのハードディスク上での置き場所ですが、カレントディレクトリ(現在のフォルダ)、または、rubyライブラリのパスが通っているディレクトリに置きます。

　ruby.exeが C:\ruby\bin\ の下にある場合でいうと

C:\ruby\lib\ruby\site_ruby\1.8\
の下に yado.rb を置くと、どのディレクトリからでもyado.rbを呼び出せます。

(2) クラスオブジェクトの作成

db = Yado.new("TEST.MDB")
というのは、データベース(サンプルでは TEST.MDB)を取り扱うためのクラスオブジェクトを作成するものです。以後、このオプジェクト db を使って処理を行います。

　もし TEST.MDB がまだ存在していなければ新たに作成します。

　内部的には ADODB.Connection オブジェクトを生成して、データベースをオープンし、取扱い可能な状態にします。

　なお、データベースの名前を指定する際、パス名を付けて、C:\database\test.mdb のように指定することができます。こうすると、指定したパスにデータベースが作られます。

(3) SQL命令の実行

　queryは、SQL命令を実行するためのメソッドです。SQL命令は文字列で与えます。

　実行に失敗した時は false を返します。

　実行に成功した時は、レコードが得られればその結果を配列で返し、そうでなければ空配列を返します。また、ブロック付きで呼び出すこともできます。

　selectの実行で得られたレコードは、ブロック処理によって、各レコードを標準出力に出力しています。

(4) データベースを閉じる

　データベースを閉じる場合、close を記述します。スクリプトの最後にこれを書くのを忘れても、おそらく大丈夫です。

［補足１］　sample01.rb の実行の仕方

　sample01.rb と yado.rb を同じディレクトリ(フォルダ)に置いて、そのディレクトリの中で、コマンドライン(DOSプロンプト)で

ruby.exe -KS sample01.rb [ENTER]
と実行すれば、そのディレクトリ内に TEST.MDB が作成されます。

　-KSオプションは、文字コードとしてShift-JISを仮定するとの指定です。yado.rbは、Shift-JISで書かれています。

　なお、標準出力と標準エラー出力をファイル化して後で読みたい場合は

ruby.exe -KS sample01.rb >STDOUT.TXT 2>STDERR.TXT [ENTER]
のように実行します。

［補足２］　SQLの insert によるレコードの挿入とデータ型

　テーブル01 は、ID(整数・主キー)、氏名(可変長の文字列・空欄禁止)、身長(浮動小数点)、誕生日(日付)のフィールドを持ちます。

　insertでレコードを挿入する場合、IDと身長は数値型なので 1 とか 172.3 などのように、引用符で囲んだりせずそのまま記述します。

　氏名は文字列なので '……' のようにシングルクォートで囲みます。

　誕生日は日付型で、 #……# のようにシャープ記号で囲みます。

　もし、何も入力せず空欄にしたい時は、文字列の '' ではなく NULL の４文字を記述します。

(1) レコード挿入用メソッド insert_records と addnew

　データベースへのレコード挿入は、SQL命令 insert を利用すれば可能です。

　しかし、各フィールドの値をカンマ区切りで列記してSQL命令を記述するのは大変です。特に複数のレコードを挿入するとなると手間がかかります。

　そこで、レコードを配列の形で与えることができるメソッドを設けました。insert_records または addnew がそれです。

　例えば、次のように用います。

 −−−−
 ary = [%w(ID 氏名 身長  誕生日),
        %w(1  高橋 172.3 1994/10/15),
        %w(2  鈴木 180.6 1994/1/9),
        %w(3  佐藤 167.4 1992/3/4)]
 db.insert_records(tblname, ary)
 −−−−

　insert_recordsの第１引数は、テーブル名です。どのテーブルに挿入するかを指定します。

　第２引数は、挿入するレコードを配列の形にしたものですが、この配列は２次元配列です。そして、最初の１行目はフィールド名を列記したものとし、その次からデータがくるようにします。

　もう一つの addnew も同じ形式で用います。機能も同じです。ただ、内部の処理が異なります。

　insert_recordsの方は、配列をSQL命令の形に変換し、そのSQL命令を実行することによってレコードを挿入します。

　それに対し、addnewは、ADODB.Recordsetを用いてレコードの挿入を行います。

　どちらを使っても同じ結果になりますが、処理速度が違う感じです。ちゃんと計測したわけではありませんが、私の環境ではaddnewの方が速いような感じがします。

　第２引数(配列)の最初の１行目でフィールド名を列記する場合、create table の定義の時と同じ順番で列べる必要はありません。２行目以降のデータの列びと合致していれば、どの順番でもかまいません。また、必ずしも総てのフィールド名を列べる必要もありません。入力しないフィールドは、列記から除きます。例えば、IDを自動的に値が設定されるようにしてあれば、入力しなくて済むので除きます。

　最初の１行目にフィールド名に当たるものが一つもなければ、最初からデータが書かれているものとみなします。つまり、フィールド名は省略されているとみなし、定義されている総てのフィールドが指定されていると仮定します。また、最初の行に、フィールド名とそうでないものが混在していると、エラーメッセージを出して処理を中断します。

［補足］　レコード挿入時のデータ型

　上で示した配列の例では、データが総て文字列です(見掛けがどうであれ、いずれも数値ではなく文字列です)。

　一方、create table でテーブルを定義した時は、IDと身長は数値型、誕生日は日付型として定義しました。

　「総て文字列の形で与えて不都合が生じないのか？」と思われた方もいると思いますが、大丈夫です。文字列の形で与えても、データベースにはそれがちゃんと数値や日付として記録されるようです。

(2) CSVまたはタブ区切りテキストを読み込んでレコード挿入

　前項で、データを配列の形で与える例を示しました。しかし、実際のデータは、CSVやタブ区切りテキストのファイルになっていることが多いだろうと思います。CSVをわざわざrubyの配列に書き換えなければデータベースに登録できないとなると面倒です。

　そこで、CSVやタブ区切りテキストを読み込んで、rubyの配列にするメソッドを設けました。scan_arrayメソッド(注)がそれです。指定ファイル名の拡張子が .csv ならCSV形式のデータとして、それ以外はタブ区切りテキストとして取り込みます。

　Excelをはじめとして表データを扱うソフトの多くは、CSV形式のファイルを出力する機能を持っていますので、それらソフトで蓄積したデータをデータベースに記録することができます。

(注)　scan_arrayメソッドは、yado.rbに含まれている Ycsvtab モジュールの中で定義されています。このモジュールは、require 'yado' とすると include されます。

△ sample02.rbの概要：　CSVファイルを読み込んで、データベースに挿入。最後にテーブルの内容を出力。
△ 用いる主なメソッド：　scan_array, addnew, print_all_values

 −−−−
 ary = scan_array("SAMPLE02.CSV")
 db.addnew('テーブル02', ary)
 db.print_all_values("DATA02.TXT", "テーブル02")
 −−−−

　データとして利用するCSVファイルの例は下のとおりです。

 −−−−
 ID,氏名,身長,誕生日
 1,高橋,172.3,1994/10/15
 2,鈴木,180.6,1994/1/9
 3,佐藤,167.4,1992/3/4
 −−−−

　sample02.rbの最後の db.print_all_values("DATA02.TXT", "テーブル02") は、現在注目中のデータベースの「テーブル02」の中身を DATA02.TXT というファイルに総て出力するものです。ただし、バイナリ型のデータは除きます。

　第１引数は出力先ファイル名の指定です。上の例では DATA02.TXT になっているのでタブ区切りテキストの形で出力しますが、DATA02.CSV だとCSV形式で出力します。DATA02.XLS ならExcelファイルです。拡張子で区分します。

　第２引数は、データベースの中の何をターゲットにするかを指定するものです。テーブルの種類、または名前を指定します。ここでは「テーブル02」というテーブル名を指定しています。

(3) レコード挿入時のバイナリ型データの扱い方、および別ファイルの取り込み

　mdbファイルではバイナリ型のデータを記録できます。デジカメで写した画像データやWord文書をそのまま挿入することができます。

　Yadoクラスの insert_records または addnew メソッドを用いてレコードを挿入する場合、中にバイナリ型のフィールドがあると、そのデータをmdbに記録できる形に変換して挿入を行います。なので、バイナリ型かどうかを意識する必要はありません。ただし、queryメソッドを用いてSQL命令によりレコードを挿入する時は、その扱い方を知っていなければなりません。

　そこで、バイナリ型の扱い方に関して少々技術的な説明を書いておきます。

　バイナリデータをデータベースに挿入する場合は、１バイトづつを２桁の16進数(0〜9およびA〜Fで表現したもの)で与えますが、SQL命令で行う時と、RecordsetのAddnewで行う時とでは若干異なります。

　前者は先頭に 0x を置いて16進数形式にしたものとして与えますが、後者は16進数形式に変換したものを 0x なしの文字列として与えます。

　例えば、SQL命令の例を示すと下のようになります。「ID, 氏名, 暗号パスワード」の三つのフィールドがあり、そのうち暗号パスワードがバイナリ型です。付属のbinary01.rbから該当の箇所のみ抜き出します。

   −−−−
   sql = "insert into テーブル01(ID, 氏名, 暗号パスワード) " +
       "values(1, '高橋', 0x0102050c1b1c1f);"  # 16進数での指定
   cn.Execute(sql)
   −−−−

　ちなみに、RecordsetのAddnewでの例は下のような形です。付属のbinary02.rbから該当箇所を示します。

   −−−−
   rs = WIN32OLE.new("ADODB.Recordset")
   sql = "SELECT ID,氏名,暗号パスワード FROM テーブル01;"
   rs.Open sql,cn,3,3
   rs.AddNew
   rs.Fields.Item('ID').Value = 1
   rs.Fields.Item('氏名').Value = '高橋'
   rs.Fields.Item('暗号パスワード').Value = '0102050c1b1c1f'  # 16進数形式
   rs.Update
   −−−−

　ところで、バイナリデータは、実際には画像ファイルやWord文書など他のファイルをそのまま取り込むのが大半だと思いますが、そんな時に便利なように、Yadoクラスの insert_records, addnew メソッドでは、該当欄において「<<photo.jpg」のように、先頭に '<<' を付けると、指定ファイルをそのまま取り込むようになっています。例えば、次のようにします。

 −−−−
 ary = [%w(ID 氏名 写真),
        %w(1  高橋 <<takahashi.jpg),
        %w(2  鈴木 <<suzuki.jpg),
        %w(3  佐藤 <<sato.jpg)]
 db.addnew(tblname, ary)
 −−−−

　上を実行すると、写真の画像データがデータベースに挿入されます。

　この '<<' によるファイルの取り込みは、バイナリ型フィールド以外にも使えます。例えば、長いテキストファイルを取り込む時にも便利です。

［補足１］　バイナリデータの大きさの制限

　フィールド情報のDefinedSize(定義された大きさ)の値をみると、mdbファイルに長いバイナリデータを収納する場合、約１ギガバイトまでは可能なようです。バイナリではありませんが、長いテキストでも同じ容量くらい収納できるようです。

　とはいえ、ほんとにメガバイト単位のファイルを収納できるのかと思い、３メガ強(3,600,384バイト)のWord文書を試しに収納してみました。

　結果、insert_recordsメソッドだとエラーが発生して収納できませんでしたが、addnewメソッドの方では問題なく収納できました(私の環境では32秒かかりました)。前者はSQL命令の insert into を用いる方法、後者はRecordsetのAddnewを用いる方法です。

　大きなデータの収納には、insert_recordsでなく addnewを使うのが無難ということになります。

［補足２］　バイナリデータの参照(読み込み)の技述情報

　バイナリデータを挿入する時は16進数形式にしますが、参照する(読み込む)時は、rubyでは配列形式で得られます。

　配列の要素には、各々のバイトのコード(文字コード)が数値として入っています。なので pack を用いて復元します。

　参照の該当部分だけ記すと下のとおりです。

fldval = rs.Fields.Item('暗号パスワード').Value.pack("C*")
こうすると、fldval にバイナリデータが入ります。

　ちなみに、Yadoで16進数の形式に変換する場合は、data.unpack("H*")[0] を用いています。

　ADOの仕組みを呼び出してバイナリを扱う場合、本来は GetChunk, AppendChunk というメソッドを用いるべきかもしれませんが、rubyでは pack, unpack の方が簡潔に書けるのでそうしました。

［補足３］　生のバイナリデータとOLEオブジェクトの違い

　ここで生のバイナリデータというのは、ハードディスクなどに保存されている画像ファイル、Word文書、Excelファイルなどのことです。よく見掛ける一般的なファイルです。

　ところで、Accessでバイナリデータをデータベースに挿入すると、それはOLEオブジェクトという特殊な形態で取り込まれます。したがって、Accessで作成されたmdbファイルからYadoでバイナリデータを抽出しても、そのままでは使えません。

　例えば、Access付続のサンブルデータベース Northwind.mdb には商品の画像データがビットマップパターンで収納されていますが、それを単純にバイナリデータとして書き出したとしても、Paintなどの画像処理ソフトで開くことができません。WordやExcelなどのデータについても同様のことがいえます。

　OLEオブジェクトは、生のバイナリデータに、それをどのアプリケーションで扱うかなどの情報を付加した特別な形態です。残念なことに、OLEオブジェクトのフォーマットは非公開なので、生のバイナリデータに変換することは難しいようです。

　ただ、Yadoではビットマップ(BMPファイル)には対応しています。OLEオブジェクト化されているBMPファイルを生のバイナリデータに変換して書き出すことができます。

　もちろん、mdbファイルであっても、生のバイナリデータとして収納されているものは、Yadoで抽出して通常どおり使えます。

(4) レコード挿入時のID番号等の自動的生成：オートインクリメント

　多くのデータベースでは、あるレコードを他のレコードと必ず区別できる識別子として、ID番号を付けるのが一般的です。住所録でいえば、ほとんどの場合は氏名で識別できるものの、稀に同姓同名の人がいるので、各人を必ず区別できるようにするためID番号を割り当てます。

　このID番号を自動的に付加する機能があります。それをオートインクリメント、あるいはオートナンバーと呼んだりします。

　この機能を使うには、テーブルを作成する時に、フィールドの定義でオートインクリメントを指定しておく必要があります。データ型として counter とか identity を用います。以下にそのサンプルを示してみます。

△ sample03.rbの概要：　IDをcounter型で定義し、レコード挿入時は ID以外のデータ(氏名等)を書き込んで、IDが自動的に設定されることを確認。

 −−−−
 sql = <<EOS1
 create table #{tblname}
    (ID counter(10,5) PRIMARY KEY,
     氏名 varchar(30) NOT NULL,
     身長 float,
     誕生日 date,
     資格あり yesno);
 EOS1
 db.query(sql)
 
 ary = [%w(氏名 身長  誕生日     資格あり),
        %w(高橋 172.3 1994/10/15 0),
        %w(鈴木 180.6 1994/1/9   1),
        %w(佐藤 167.4 1992/3/4   0)]
 db.addnew(tblname, ary)
 −−−−

　前のsample01.rbでは、create table において ID を int 型で定義していました。今回、この int を counter(10,5) としています。

　これは、IDの初期値を10、増分値を5に設定するものです。つまり ID は 10, 15, 20, 25, …… と自動的にセットされることになります。

　(10,5) を省略して counter とだけ書くと、counter(1,1) が指定されたものとみなされます。つまり 1, 2, 3, 4, …… となります。

　また、counter(10) とすると、初期値が10、増分値は1になります。10, 11, 12, 13, …… となります。

　今回の例では、テーブルの定義の時は ID があるのに対し、レコード挿入時には ID がありません。こうすると、IDが自動的にセットされるわけです。

　もちろん counter(10,5) などと定義していたとしても、レコード挿入時に意図的にIDを指定してやればそれがセットされます。自動的な値より、意図的な指定の方が優先されます。

　データ型の counter や identity は、int と同じ整数型です。取り得る値の範囲も同じです。４バイトなので約±21億。

　ですが、だからといって counter(10,5) を int(10,5) と書くことはできません。エラーになってしまいます。counter, identity は、オートインクリメント型といった方がいいのかもしれません。

　ここまでの説明に対し、「counter はデータ型でなく、NOT NULL などと同様のオプションではないのか？」と思われた方もいるかと思います。しかし、少なくとも私の環境 ADO ver 2.7 では

ID int counter PRIMARY KEY,
のように書くとエラーになります。intを取り除けば問題なく動きます。

［補足］　データ型の yesno

　今回のサンプルには「資格あり」のフィールドを yesno というデータ型で入れ込んであります。これは「yesかnoか」　「有るか無いか」あるいは「真か偽か」を記録するデータ型です。

　レコードを挿入する時は、yes なら数値の 1、no なら 0 を指定します。

　ただし、レコードを参照する(読み込む)時に得られる値は、1, 0 でなく true, false です。

　この欄に何も入力せず空欄にすると、値として false がセットされます。

　少しややこしくなりますが、yesno型のデータをセットする場合の話を追記します。

　レコード挿入時には数字の 1, 0 をセットすると書きましたが、SQL命令を記述する時は yes, no を書くことができます。'yes', 'no' ではありません。引用符で囲まない形です。例えば

 −−−−
 sql = "insert into テーブル01 values('高橋', 172.3, #1994/10/15#, yes);"
 db.query(sql)
 −−−−

のように書けます。しかし、ADODB.RecordsetのAddnewではこの yes, no が使えません。

　yes, no 以外に true, false を使うこともできます。こちらはSQLとADODB.Recordsetの両方で大丈夫です。ただし、SQLでは文字列で与えることができません。

　ということは、つまり %w(……) の配列、CSVから読み込んだもの、そうしたデータをそのままではレコード挿入できないことになります。

　その点、数字の 1, 0 は、数値と文字の両方とも大丈夫です。また、SQLとADODB.Recordsetのどちらでも問題ありません。

(1) レコードの更新: SQL命令による方法

　レコードの更新、つまりデータの書き換えは、SQL命令の update … set … where で行えます。

△ sample04.rbの概要：　SQL命令の update によって、佐藤さんのデータを吉田さんのものに書き換える。

 −−−−
 sql = <<EOS2
 update #{tblname}
   set
     氏名 = '吉田',
     身長 = 169.8,
     誕生日 = #1972/2/5#
   WHERE
     氏名 = '佐藤' and
     身長 = 167.4;
 EOS2
 db.query(sql)
 −−−−

　update の where 以降には検索条件を書きます。その検索条件にマッチするレコードを更新することになります。マッチするレコードが複数あると、それらが総て更新されるので、意図しない更新が起こらないよう注意が必要です。

　どのように更新するかは、set 以降に記述します。更新したいフィールドが複数ある時は、カンマで区切って記します。例では「佐藤」さんを「吉田」さんに変更し、併せて身長と誕生日も書き換えています。

　なお、サンプルでは update … set … where を複数行で記述していますが、その方が分かりやすいと思ってそうしただけで、１行で書いてもかまいません。

(2) レコードの更新: Yadoのupdate_records, update_pairメソッドによる方法

　Yadoに、レコードを更新するための update_records および update_pair メソッドがあります。

　update_recordsは、主キーが一致する場合に更新を行います。また、update_pairの方は、「旧」と「新」の組を与えることで更新を行います。

　例えば、IDが3番の「佐藤」さんを「吉田」さんに書き換える場合を単純化して書くと次のようになります。

   db.update_records(tblname, [%w(ID  氏名),
                               %w(3   吉田)])

あるいは

   db.update_pair(tblname, [%w(ID  氏名),
                            %w(3   佐藤),
                            %w(3   吉田)])

　update_recordsは、書き換えた後の新しい状態を配列で与えます。ただし、主キーは書き換えません。

　update_pairの方は、旧と新が２行１組になっていて、旧にマッチしたレコードが新に更新されます。２行１組を複数組盛り込むこともできます。空白行に相当する [] は無視されるので、旧と新の組を盛り込む時は、適当に [] を入れておくと分かりやすくなります。

　なお、第２引数の配列の最初の行にフィールド名に当たるものが一つもなければ、最初から更新データが書かれているものとみなします。つまり、フィールド名の列記は省略されているとみなし、定義されている総てのフィールドが指定されていると仮定します。また、最初の行に、フィールド名とそうでないものが混在していると、エラーメッセージを出して処理を中断します。

　これら２つのメソッドの戻り値は共通で、[実際に更新されたレコードの個数, 更新しようとしたレコードの個数] の２つの要素からなる配列です。

　また、第２引数として与えた更新用の配列の中身ですが、実際に更新できたものは取り除かれ、更新できなかったものが残ります。メソッドを実行した後でその配列の中身を確認すれば、何が更新できなかったのか分かります。

△ sample05.rbの概要：　update_recordsによって佐藤さんを吉田さんに、update_pairによって若林さんを佐々木さんに書き換える。また、あえて存在しない人のデータを盛り込んでおいて、処理後、それが残ることを確認。

 −−−−
 aa = [%w(ID  氏名  身長  誕生日),  # 更新用データ
     %w(3  修正_吉田 169.8 1972/2/5),
     %w(7  遠藤  169.3  1989/9/19)]
 count = db.update_records(tblname, aa)
 printf("実際に更新できた個数:%d, 更新しようとした個数:%d\n", count[0], count[1])
 puts '＊更新用に与えた配列 aa の残りデータ(更新できなかったもの)'
 aa.each {|a|  p a}
     （中略）
 aa = [%w(氏名  身長  誕生日),  # 更新用データ
     %w(若林  165.1  1984/11/13),
     %w(修正_佐々木  165.1  1985/12/4),
     [],
     %w(遠藤  169.3  1989/9/19),
     %w(修正_近藤  169.3  1989/9/19)]
 count = db.update_pair(tblname, aa)
     （後略）
 −−−−

(3) レコードの削除: SQL命令による方法

　レコードの削除は、SQL命令の delete from … where で行えます。その例を下に掲げます。

△ sample06.rbの概要：　SQL命令の delete を用いて、身長が 170.0 より低い人のレコードを削除。

 −−−−
 sql = <<EOS2
 delete from #{tblname}
   WHERE  身長 < 170.0;
 EOS2
 db.query(sql)
 −−−−

　「WHERE 身長 < 170.0」の箇所を
「WHERE 誕生日 < #1993/01/01#」とすれば、1993年1月1日よりも前に生まれた人のレコードを削除します。それ以降に生まれた若い人のデータが残ります。

　また
「WHERE 氏名 = '高橋' or 氏名 = '佐藤'」とすれば、高橋さんと佐藤さんのデータを削除することになります。

(4) レコードの削除: Yadoのdelete_recordsメソッドによる方法

　Yadoクラスに、レコードを削除するための delete_records メソッドがあります。

　削除したいレコードを配列で与えます。例えば下のような形です。

 −−−−
 ary = [%w(氏名 身長  誕生日),
        %w(佐藤 167.4 1992/3/4),
        %w(久保 173.7 1979/8/7)]
 db.delete_records(tblname, ary)
 −−−−

　上のようにすると、佐藤さんと久保さんのデータを削除することになります。

　delete_recordsメソッドでは、SQL命令の delete from … where のような条件指定は行えません。削除するレコードを一つ一つ列挙する形で指定するだけです。

　なお、第２引数の配列の最初の行にフィールド名に当たるものが一つもなければ、最初から削除データが書かれているものとみなします。つまり、フィールド名の列記は省略されているとみなし、定義されている総てのフィールドが指定されていると仮定します。また、最初の行に、フィールド名とそうでないものが混在していると、エラーメッセージを出して処理を中断します。

　delete_recordsの戻り値は、[実際に削除されたレコードの個数, 削除しようとしたレコードの個数] の２つの要素からなる配列です。

　また、第２引数として与えた削除用の配列の中身ですが、実際に削除できたものは取り除かれ、削除できなかったものが残ります。メソッドを実行した後でその配列の中身を確認すれば、何が削除できなかったのか分かります。

△ sample07.rbの概要：　delete_recordsによって、５人分のデータから３人分を削除しようとするが、うち１人はデータベース中に存在しない人のデータなので削除できない。２人分が削除される。

 −−−−
 aa = [  # 削除するレコードのデータ
     %w(氏名  身長  誕生日),
     %w(佐藤 167.4 1992/3/4),
     %w(久保 173.7 1979/8/7),
     %w(遠藤  169.3  1989/9/19)]
 count = db.delete_records(tblname, aa)
     （中略）
 printf("□ 実際に削除した個数:%d, 削除しようとした個数:%d\n", count[0], count[1])
 puts '□ 削除しようとしたデータ aa の残りデータ(削除されなかったもの)'
 aa.each {|a|  p a}
 −−−−

(5) その他のレコード更新用メソッド: update_array, update_hash

　SQL命令の「update … set … where」のように、検索条件を設定して更新を行いたいこともあると思います。そこで、update_array および update_hash というメソッドを設けてあります。

　この両メソッドは、必ずブロック付きで呼び出します。ブロックの中で、レコード一つづつを処理します。処理の書き方は、もちろん rubyスクリプトです。

　一つのレコードは複数のフィールドからなる配列として受け取りますが、それが通常の配列になっているのが update_array、連想配列になっているのが update_hash です。

　下に、付属の update01.rb, update02.rb から該当部分を掲げておきます。

 −−−−
 count = db.update_array(tblname, %w(氏名  身長  ID)) {|rs|
   if rs[0] == '鈴木' or rs[0] == '若林'
     rs[0] = 'Arrayで修正_' + rs[0]  # 氏明の書き換え
     rs[1] = rs[1] + 100.0  # 身長の書き換え
   end
 }
 printf("%d個のレコードを更新しました.\n", count)
 −−−−

 −−−−
 count = db.update_hash(tblname) {|rs|
   name = rs['氏名']
   height = rs['身長']
   if name == '鈴木' or name == '若林'
     rs['氏名'] = 'Hashで修正_' + name
     rs['身長'] = height + 100.0
   end
 }
 printf("%d個のレコードを更新しました.\n", count)
 −−−−

　両メソッドとも、第１引数はテーブル名、第２引数はフィールド名を列べた配列です。その列びの順番でフィールドの値が得られます。

　第２引数を省略すると、全部のフィールドが定義の時の順番で指定されたものとみなされます。

　データベースを使う目的の大半は、その参照にあります。これまで掲げたサンプルでも

   sql = "select * from テーブル01;"

db.query(sql) {|rs| p rs}
のような記述を最後の方に置き、データベースの中身を出力していました。最も基本的な参照の仕方です。

　SQL命令による参照の詳細については、SQLの解説書等を参考にしていただくとして、ここではYadoクラスに用意されている参照用メソッドについて述べます。

(1) テーブル＆フィールド情報を書き出す print_field_info

　print_field_infoは、テーブル＆フィールド情報をタブ区切り亦はCSVの文字列で出力します。データベースの中身を見るよりも、先ずどんな情報が入っているかを知るのに利用できます。

db.print_field_info("OUTPUT.TXT", "SQL", ['TABLE', 'VIEW'])
のように呼び出します。第１引数は出力先ファイル名、第２引数はフィールド情報の提示形式、第３引数は対象とするテーブルの種類です。第２、３は省略可能です。

　出力先ファイル名の拡張子が .csv かそれ以外かによって、出力される形式がCSVかタブ区切りテキストかに分かれます。

　最後の引数つまり第３引数の「テーブルの種類」の指定を省略すると、TABLE(標準テーブル)のみを対象とします。VIEWなどは対象としません。そこで、VIEWも対象とする時は ['TABLE', 'VIEW'] あるいは %w(TABLE VIEW) のように指定します。

　第２引数は、フィールド情報の提示の仕方で、SQL形式か、マイクロソフト社の形式かの指定です。前者なら SQL, 後者なら MS を指定します。省略すると SQL とみなされます。

　例えば、SQL形式を指定すれば下のように書き出されます。

　１行目はテーブルに関する情報で、２行目以降がフィールド名とそのデータ型です。

　テーブルの情報は、名称、種類、作成日時、更新日時の４つです。

　フィールドの情報は、名称、データ型、そして桁数指定がある時はその数です。オプションがあればそれも出力します。

　行頭に '!' が付いているのは、それが主キー(Primary Key)であることを示しています。Yado独自の表現です。

　ちなみに、「区分コード」のデータ型が counter(9,1) になっていますが、これは、テーブルを定義した初期段階では counter(1,1) だったはずです。その後、８件のレコードが追加され、次に追加されるレコードは９番目になることからこの値になっているものです。

　SQL形式に対しマイクロソフト社の形式の場合は下のように書き出されます。

　データ型の adInteger や adVarWChar などは、マイクロソフト社のプログラミング言語 Visual BASIC とか C# における定数の名前です。それら言語では、この定数名を用いてデータ型を指定します。

　定数の実態は数値で、例えば adInteger は 3, adVarWChar は 202 です。

［補足］　mdbファイルにおけるテーブルの種類

　「Microsoft Access Club」の「ADOXを用いてテーブル名を取得する ： ADOX入門講座」によると、Typeプロパティの一覧は次のとおりです。

表１　テーブルの種類

　Access付属のサンプルデータベース Northwind.mdb を調べると、テーブルの種類として次の４種類があります。

　TABLE, VIEW, ACCESS TABLE, SYSTEM TABLE

　最後の「SYSTEM TABLE」のテーブルを読み込もうとすると、「読み取り権限がないので、レコードを読み取ることができません。」というエラーが発生します。

(2) とりあえず総てのデータを出力する print_all_values

　print_all_valuesは、sample02.rbのところで既に述べましたが、現在注目中のデータベースの中身をファイルに総て出力するものです。ただし、バイナリ型のデータは除きます。

db.print_all_values("OUTPUT.TXT", ['TABLE', 'VIEW'])
のように用います。第１引数は出力先ファイル名、第２引数は対象とするテーブルの種類もしくは名前です。第２引数は省略可能です。

　出力先ファイルとして OUTPUT.CSV のように拡張子 .csv が指定された場合は、CSV形式で出力します。.xls ならExcelファイルに書き出します。それ以外の時はタブ区切りテキストとして出力します。

　第２引数は、テーブルの種類・名前を配列で与えます。一つだけの時は文字列で与えてもかまいません。

　これを省略すると、標準テーブル(TABLE)だけを対象とします。VIEWなどは対象としません。VIEWも対象とする時は ['TABLE', 'VIEW'] あるいは %w(TABLE VIEW) のように指定します。

　第２引数に、テーブルの種類を示す文字列がなければ、テーブルの名前とみなしてそれを対象とします。ただし、テーブルの種類を示すものが一つ以上あれば、その種類を対象とし、テーブル名の方は無視します。つまり、テーブルの種類と名前を混在させると、種類の方だけが取り上げられます。

　Access付属の Northwind.mdb の中身を出力する例を改めて示します。

 −−−−
 #! ruby.exe -KS
 require 'yado'
 db = Yado.new 'Northwind.mdb'
 db.print_all_values  'OUTPUT.CSV',  %w(TABLE VIEW)
 db.close
 −−−−

　上の例では print_all_values メソッドに渡す引数を (……) で囲んでいませんが、rubyではこうした書き方も可能です。

　なお、出力先ファイルの名前として、"OUTPUT.CSV"などでなく、":::.CSV"のようなオープン不可能な名前を指定すると、ファイルでなく標準出力に出力します。":::.TXT" とか ":::"ならタブ区切りテキストを出力します。

(3) 指定テーブルのバイナリ型データをファイルに書き出す print_binary_values

　print_binary_valuesは、指定テーブルのバイナリ型のデータをファイルに書き出します。

　db.print_binary_values("テーブル01", "写真", ".jpg") のように用います。

　第１引数はテーブルの名前、第２引数はバイナリ型フィールドの名前、第３引数は出力する時の拡張子です。第２、３の引数は省略可能ですが、第１のテーブル名は省略できません。

　データベースにバイナリ型のデータを記録する場合、そのデータのファイル名が一緒に記録されるわけではありません。なので、バイナリデータを取り出せたとしても、それがWord文書なのか、Excelのファイルなのか、画像データなのかが分かりません。そのため当メソッドでは第３引数で出力ファイル名の拡張子を指定するようになっています。これが省略されると、.binという拡張子で書き出されます。ただし、そのバイナリがBMPファイルであると判明した時は自動的に .bmp になります。

　第２引数は、バイナリ型フィールドの名前です。これを省略すると、最も左にある欄のバイナリ型フィールドを対象とします。

　各々の出力ファイル名は「テーブル名 _ 第１フィールドの値 _ 第２フィールドの値 拡張子」の形になります。

　例えば、「テーブル01」が「ID, 氏名, 写真」から構成されており、「1, 高橋, ……」のレコードがあるとすると、その高橋さんの写真データが

テーブル01_1_高橋.jpg
という名前のファイルで書き出されます(第３引数に ".jpg" を指定しておく必要があります。)。

　print_binary_valuesを呼び出すと、こうしたファイルがレコードの数だけ書き出されることになります。

　Accessのサンプルデータベースにあるバイナリデータを出力する例は下のとおりです。binary03.rb として付属させてあります。カレントディレクトリに Northwind.mdb があれば正しく動作すると思います。

 −−−−
 # binary03.rb: Northwind.mdbのバイナリデータをファイルに書き出す
 require 'yado'
 db = Yado.new 'Northwind.mdb'
 db.print_binary_values  '商品区分',  '図'
 db.close
 −−−−

　上を実行すると、私の手元にあるデータベースでは下の８つのBMPファイルが出力されます。

   商品区分_1_飲料.bmp
   商品区分_2_調味料.bmp
   商品区分_3_菓子類.bmp
   商品区分_4_乳製品.bmp
   商品区分_5_穀類、シリアル.bmp
   商品区分_6_肉類.bmp
   商品区分_7_加工食品.bmp
   商品区分_8_魚介類.bmp

　以上がYadoクラスの参照用メソッドです。より細かくいえば、他にもテーブルのフィールド名を得るための list_field_names などいくつかありますが、それらについてはZIPアーカイブに同梱されている yado_references.txt を参照して下さい。

　豊富なSQLの参照機能について言及しないままというのも何なので、簡単なレコード参照の例を記します。

(1) 特定のフィールドによる整列 order by

　身長の高い順とか、誕生日の早い順(年輩者から若い方への順番)でレコードを列べる時は、order by を用います。その例が付属の sample08.rb ですが、下にその該当箇所を掲げます。

 −−−−
 puts '身長の高い順'
 sql = "select * from #{tblname} order by 身長 desc;"
 db.query(sql) {|rs|  p rs}
     printf("\n")
 puts '誕生日の早い順(年輩者から若い方への順番)'
 sql = "select * from #{tblname} order by 誕生日 asc;"
 db.query(sql) {|rs|  p rs}
 −−−−

　ascは昇順(値の小さい方から大きい方へ)、descは降順(値の大きい方から小さい方へ)を意味します。

　誕生日の数値としての実態は、過去のある時点から誕生日までの日数と時間ですから、年輩の人ほど値は小さく、若いほど大きくなります。年齢と逆です。なので「誕生日 asc」は、年輩者から若い方への順番になります。

(2) 検索条件を指定する where

　whereは、「氏名」が「鈴木」であるとか、「身長」が170.0以上であるなどの検索条件を指定するものです。既に update と delete のところで述べましたが、同じように select と組み合わせて使うことができます。

select * from テーブル01 where 身長 > 170.0;
とすれば、身長が170.0を超える人のデータを抽出できます。ここで使っている '>' を演算子といいますが、おおよそ次のようなものがあります。

表２　SQLのwhere検索条件で用いる演算子

　付属の sample09.rb に、上の演算子の例が含まれています。

○　あいまい検索の like、ワイルドカードの％(パーセント)と＿(アンダーライン)

　LIKEは、一種のあいまい検索の指定で、Windowsにおいてファイルやフォルダをワイルドカードで指定するのに似ています。ただし、ワイルドカードの '*' (任意の長さの文字列)は '%'、また、'?' (任意の１文字)は '_' で表現します。例えば、

where 氏名 like '_木'
とすると、「鈴木」　「青木」のように、何か１文字＋「木」にマッチします。「佐々木」は「木」の前に２文字あるのでマッチしません。

where 氏名 like '%木'
とすれば、「木」の前が何文字でもいいので「鈴木」　「青木」　「佐々木」のいずれにもマッチします。

　Webで調べると、'*', '?' が使えるように書かれているところが多いですが、私の環境では使えません。'%', '_' であれば大丈夫です。

　likeで使えるマッチングのための表現は、正規表現に似たところもありますが、それほど豊富ではないようです。参考までlikeで使える表現形態を下に掲げておきます。

表３　あいまい検索likeで使える表現形態

○　該当する値を列記する記法 in (x, y, z)

　値を配列のように列記して、そのうちの一つに該当するものがあるかどうかを見る時に用いる「in (……)」というのがあります。例えば、

where 氏名 in ('鈴木', '若林')
とすると、氏名が「鈴木」または「若林」の時に select が実行されます。or でだらだらと連ねるより簡潔に記述できます。

　括弧の中は、文字列以外に、数値や true / false あるいは日付型の #……# などを入れてもかまいません。

○　値の範囲(x以上・y以下)を表現する between x and y

　値の範囲を指定する簡潔な表現として between があります。例えば

where 今月の出勤日数 between 15 and 30
とすると、「今月の出勤日数」が15以上・30以下の該当者を抽出することになります。これは

where 今月の出勤日数 >= 15 and 今月の出勤日数 <= 30
と書いたのと同じですが、between を使う方が簡潔です。

(3) 仮装テーブル VIEW の利用

　先ずは1990年代に生まれた人を抽出し、その中から身長や資格の有無の条件で更に絞り込むといった作業を行いたい場合、「とりあえずの1990年代生まれの人のデータ」をVIEWという形式の仮装テーブルにすることがあります。次のSQL命令を実行すると VIEW を作成できます。

 create view birth90 as select * from テーブル01
     where 誕生日 between #1990/01/01# and #1999/12/31#;

　上を実行すると、「birth90」という VIEW が作成されます。

　この VIEW は、あたかも TABLE (標準テーブル：実テーブル)であるかのように扱うことができ、それを対象としていろいろな形で select を実行できます。「birth90」に対して

 select * from birth90
     where 身長 >= 180.0;

を実行すれば、結果的に「1990年代生まれで、かつ、身長が180.0以上の人」を抽出できます。

　ただし、VIEW は、あくまで仮装的なテーブルです。実テーブルである標準テーブルの中身を書き換えると、それに連動して VIEW の中身も変化します(この言い方は正確ではありませんが、イメージとしてはそんな感じです)。

　sample10.rb では、最初、７人(うち２人は誕生日が不明)のデータからなる「テーブル10」を作成し、そこから1990年代生まれの人を抽出し、「birth90」というVIEWを作成しています。その時点で VIEW の中身をみると、該当者が３人いることが分かります。

　そして、その後、「テーブル10」の方に、不明だった２人の誕生日データを書き入れます。その２人とも 1990年代生まれであるため、書き入れた後に「birth90」の中身をみると、該当者が５人に増えているのが分かります。つまり「テーブル10」を書き換えると、それが「birth90」に反映されます。

　このように、VIEW の中身は、実テーブルに連動して常に自動的に変化します。

　なお、VIEWが不必要になった時は drop table でそれを削除できます。標準テーブルを削除する時と同じSQL命令です。

(4) テーブルの結合

　リレーショナルデータベースでは、複数のテーブルをいろいろな形で組み合わせて扱えます。その一番簡単な例として、テーブルの結合があります。

　「ID、氏名」からなるテーブルと、「ID、月当たりの小遣い」からなるテーブルがあるとします。IDは両テーブルで共通です。この二つを結合して「ID、氏名、月当たりの小遣い」の３項目のVIEWを作るのが sample11.rb です。あまりに安直な例ですが、簡単でいいかなと思ってこれにしました。

　２種類の結合の仕方を取り上げています。where で結合する方法、inner join … on で行う方法です。説明は省略します。

　以上、SQL命令での参照の仕方をいつくか書きましたが、他にもいろいろあります。サンプルでは取り上げていませんが、unionも使えます。詳しくは別途Webサイトや図書を参考にして下さい。

Copyright (C) T. Yoshiizumi, 2008-2009 All rights reserved.

• yado.rbの使い方（２）
• yado紹介ページ
• トップページ