Win32OLE 活用法 【第 3 回】 ADODB

書いた人：cuzic

プロローグ

あなたは、膨大にあった Excel 表を解析して、業務を少しずつ自動化して いきました。 自動化していくにつれて、今までの仕事がとても楽になり、満足していました。

けれど、そういう作業を続けているうちにある壁にぶつかりました。

「どうにかならないかな？」

今やっているやり方では、最新の情報に更新しようと思うたび Excel 表を 毎回パースすることになります。 このパースするのにかかる時間が長くどんどん面倒に思えてきたのです。

「昔かじったデータベースの知識を使って、このデータをなんとか できないかなぁ」

はじめに

前回の記事では、Excel の表に入ったデータを Ruby を用いてどのように 取得するかについて学びました。

Excel 表を単純にパースするだけでも、かなり業務を楽にしていく ことができるでしょう。 Excel 表の値を参照するような仕事は面倒なものです。

しかし、ある程度大規模になると Excel は便利とはいえなくなってきます。 例えば、Excel ではデータの入力と表示の形式に区別がないため、 入力には楽な形式、表示には楽な形式というのができません。 また、分析をするときにもいろいろと不便なことが多くなります。

こういう問題に対処するために、データベースを利用する方法があることは みなさんもご存知でしょう。

今回の記事で私たちは、Ruby の Win32OLE ライブラリを使って、データベースを扱う 方法について学びます。

今回の目的

Microsoft が提供している COM コンポーネントの中には、 ADO (ActiveX Data Object) と呼ばれる一連のコンポーネントがあります。

これらのコンポーネントを使えば、データベースに対するデータの 入力や取得ができます。

ADO にはよく使うコンポーネントとして、ADODB.Connection と呼ばれる コンポーネントと ADODB.Recordset と呼ばれるコンポーネントがあります。 ADODB.Connection というオブジェクトは、データベースへの接続を表現する オブジェクトで、Recordset は、レコードセットを表現するオブジェクトです。 レコードセットとは、SQL の SELECT 文を実行した結果のデータの集まりを 言います。

今回の記事では、これらのオブジェクトを使って次の内容の動作を行います。

• ADO の概要
• SQL を用いたデータの追加
• データベースからのデータの取得
• ADODB.Recordset を用いたデータの更新

まず、ADO ではどのようなオブジェクトが提供されていて、 利用できるのかについて学びます。

次に実用的に CSV 形式のデータをデータベースに入力する スクリプトを例にして、ADO の Connection オブジェクトの 使い方を学びます。

そして、データベースからデータを取得する方法について学びます。

今回の記事では、データベースや SQL が何なのか、ということを すでに知っている人を主な対象としています。 これらについて学びたい方は、別途これらについて解説した 文章を参考にしてください。

ADO の概要

ADO は、データベースにアクセスするための一連の COM コンポーネントです。

ADO を用いることで、データベースに保存されたデータに 直接アクセスして、次の操作ができます。

• レコードとそのデータの取得
• レコードの追加
• レコードの削除
• レコードの検索

ADO のコンポーネントの中でも今回は特に、Connection オブジェクトと Recordset オブジェクトの 2 つのオブジェクトについて学びます。 また、レコードの値の取得や更新を行うときに用いる Fields コレクション、 Field オブジェクトの使い方についても学んでいきます。

具体的な使い方についてはこれから一緒に学んでいきましょう。

ADO でデータを入力

Excel 表でパースしたデータをまずどうしたいかと言えば、 データベースに入力したいですよね。 データベースに入力することで、検索や更新を簡単に 行いやすくなります。

Excel 表でパースしたデータを CSV 形式にすることは 簡単です。 そこでこの章では、CSV 形式のデータをデータベースに 追加していく方法について学んでいきます。

まず、既存のデータベースに対して接続し、そのデータベースの あるテーブルに対して一行ずつデータを追加するスクリプトを 例に次の項目について学んでいきましょう。

• 接続文字列とは何か
• 接続の開き方
• アクションクエリの実行

次に CSV 形式のデータを取得して、データベースに追加 していくスクリプトを紹介します。

前準備

今回のスクリプトを実行するには、まず操作するための データベースを用意してあげる必要があります。

今回は、Microsoft Office に含まれている Access の mdb 形式のデータベースをサンプルのデータベースとして用います。

このデータベースは次のようなテーブルとフィールドを 持ちます。

サンプルとして、次の mdb ファイルを用意します。 ダウンロードして使ってください。 sample1.mdb

一行レコードを追加するスクリプト

insert1record.rb

require 'win32ole'

conn = WIN32OLE.new("ADODB.Connection")
connstr = "DRIVER={Microsoft Access Driver (*.mdb)};Dbq=sample1.mdb"
conn.Open connstr

begin
  sql = "INSERT INTO earthquake (Name,Day,Magnitude,NumOfDeaths,DeadOrAlive) " + 
    "VALUES ('関東大震災','1923/09/01',7.9,142807,TRUE);"
  conn.Execute sql
rescue
  STDERR.puts sql
  STDERR.puts $!
end

conn.Close

順に説明していきます。

conn = WIN32OLE.new("ADODB.Connection")

ProgID が ADODB.Connection の COM オブジェクトを作成する行です。 conn が Connection オブジェクトになります。 この Connection オブジェクトを用いて、どのデータソースへの接続するかを 指定したり、接続を開いたり、閉じたり、SQL を実行したりします。

connstr = "DRIVER={Microsoft Access Driver (*.mdb)};Dbq=sample1.mdb"
conn.Open connstr

Connection オブジェクトの Open メソッドは引数に接続文字列(Connection String) をとります。 接続文字列というのは、引数名=値 のフォーマットの引数列を セミコロンで連結したものです。 この接続文字列の Driver を適切に設定することで Microsoft Access や SQL Server さらには ODBC を経由することで PostgreSQL に対して、 というようにさまざまな DBMS に対して接続できます。

接続文字列について詳しく知りたい方は、接続文字列の作成や、 データ ソースにアクセスする を参考にしてください。

今回は、「Microsoft Access」の mdb ファイルにアクセスします。 そのときは上記のように “DRIVER={Microsoft Access Driver (*.mdb)}” と指定します。 Dbq 以降の引数はデータソースドライバに対して渡される引数となります。 Microsoft Access Driver の場合は、その mdb ファイルがどこにあるのかを指定するために　 dbq という名前付引数を使用します。 パスを指定するのに使う引数はデータソースドライバによって異なります。 詳しくは上記のリンクを参照してください。

begin
  sql = "INSERT INTO earthquake (Name,Day,Magnitude,NumOfDeaths,DeadOrAlive) " +
    "VALUES ('関東大震災','1923/09/01',7.9,142807,TRUE);"
  conn.Execute sql
rescue
  STDERR.puts sql
  STDERR.puts $!
ensure
  conn.Close
end

繰り返しになりますが、この記事では SQL の構文についてはすでに 知っていることを想定します。 SQL について知りたい方は Microsoft Access のヘルプや、Google で 検索して調べてください。

INSERT INTO earthquake (Name,Day,Magnitude,NumOfDeaths,DeadOrAlive) " +
     "VALUES ('関東大震災','1923/09/01',7.9,142807,TRUE);

の SQL ステートメントは、earthquake テーブルに対して、関東大震災のデータを 追加する文になります。 この SQL ステートメントを実際に実行しているのが次の行です。

   conn.Execute sql

この行で conn という Connection オブジェクトが接続しているデータソースに対して SQL ステートメントを実行します。

Connection オブジェクトの Execute メソッドで、アクションクエリを 実行したり、選択クエリを実行してレコードセットを得たりできます。 アクションクエリとは、更新クエリ (UPDATE 〜)、削除クエリ (DELETE 〜)、 追加クエリ (INSERT INTO 〜)、テーブル作成クエリ (SELECT 〜 INTO 〜) の 4種類の SQL ステートメントを指します。 選択クエリは、SELECT ステートメントのようなデータを取得するための SQL ステートメントです。 後で詳しく説明しますが、Connection オブジェクトでは、選択クエリを 実行しても、データの取得はできますが、更新はできません。

この SQL は、アクションクエリの中でも特に「追加クエリ」になります。

begin 〜 rescue 〜 ensure 〜 end は前回も出てきました 始め処理終わり処理のイディオムです。Java なら Before/After パターンと呼ばれたりします。 結城浩さんのデザインパターン紹介 が参考になります。

rescue
  STDERR.puts sql
  STDERR.puts $!

で、エラーが生じたときに実行させた SQL ステートメントを出力しています。 このようにエラーがあったときに SQL を表示すれば、 デバッグの手助けになります。

以上のような簡単なスクリプトで、簡単に SQL ステートメントを実行できます。

CSV からのデータを追加

いよいよ、この節では、CSV からのデータの追加について説明します。

この節で学ぶ項目は次のとおりになります。

• ARGF を使った引数で示されたファイルを扱う
• %w() を使った文字列の配列リテラル
• Enumerable#zip
• Enumerable#map

それでは、サンプルのスクリプトについて見てみましょう。 このスクリプトは Ruby 1.8 以上で動作します。

csv2rs.rb

require 'win32ole'

def startADO filename 
  cn = WIN32OLE.new("ADODB.Connection")
  connstr = "DRIVER={Microsoft Access Driver (*.mdb)};Dbq=#{filename}"
  cn.Open connstr
  begin 
    yield cn
  ensure
    cn.Close
  end
end

csvfields = %w(Day Name Magnitude NumOfDeaths DeadOrAlive)
insertfields =  [
  ['Name',false],
  ['Day',false],
  ['Magnitude',true],
  ['NumOfDeaths',true],
  ['DeadOrAlive',true]]

startADO("sample1.mdb") do |conn|
  ARGF.each_line do |line|
    record = {}
    csvfields.zip(line.chomp.split(/,/)) do |field,value|
      record[field] = value
    end
    values = insertfields.map do |field,rawvalue| 
      if rawvalue
        "#{record[field]}"
      else
        "'#{record[field]}'"
      end
    end
    fieldStatement = insertfields.map{|f,v| f}.join(',')
    
    sql = "INSERT INTO earthquake (#{fieldStatement}) " + 
      "VALUES ( #{values.join(',')} );"
    begin
      conn.Execute sql
    rescue
      STDERR.puts sql
      STDERR.puts $!
    end
  end
end

conn = WIN32OLE.new("ADODB.Connection")
connstr = "DRIVER={Microsoft Access Driver (*.mdb)};Dbq=sample1.mdb"

この 2 行については先ほど学んだところです。 conn が Connection オブジェクトで、connstr が 「Microsoft Access Driver」 を使用する接続文字列です。

conn.Open connstr

この行で Connection を開きます。

csvfields = %w(Day Name Magnitude NumOfDeaths DeadOrAlive)

%w() とすることで、要素が文字列の配列を生成できます。

この行は、入力する CSV が持つフィールドとその順番を与えています。 CSV から入力したいときは、どの列がテーブル上のどのフィールドに 対応するかをあらかじめ与える必要があります。 このスクリプトでは、そのためにこの csvfields という変数を用いています。

ARGF.each_line do |line|
  ...
end

ARGF というのは、 スクリプトに指定した引数をファイル名とみなして、それらのファイルを 仮想ファイルとしたオブジェクトです。 簡単なスクリプトを作っているときに、引数のファイルを開く処理を 記述する手間を省けるので、便利です。

  csvfields.zip(line.chomp.split(/,/)) do |field,value|
    record[field] = value
  end

ここで、先ほど用意した csvfields という文字列の配列を 使って CSV の一行ずつを record というハッシュに フィールド名とその値のペアを格納させていきます。

Enumerable#zip は、Ruby 1.8 以上の場合、存在するメソッドです。 csvfields の各要素と、zip の引数の各要素を組み合わせて、 ブロックに渡します。

line.chomp.split(/,/) は、分かると思います。 カンマ区切りの CSV を配列にしています。

zip を使っているところを初めてみた場合は、 Ruby リファレンスマニュアルの Enumerable の説明 をよく読んでみてください。

Enumerable#zip を使うことで複数の配列を最初の要素や、2 番目の要素という ように順にブロックに渡すことができるので便利です。

ここでは、csvfields のフィールド名が各要素となり、 line.chomp.split(/,/) で、対応するフィールドの値の値となります。 それを |field,value| で受けて、record というハッシュに 格納していきます。

zip についての説明はこれくらいにして、これから 次の SQL を生成する部分について学んでいきましょう。

  values = insertfields.map do |field,rawvalue|
    if rawvalue
      "#{record[field]}"
    else
      "'#{record[field]}'"
    end
  end

insertfields は、二つの要素を持つ配列の配列となっています。 その二つの要素とは、フィールド名と そのフィールドの値を シングルクオートで囲まず、生の値として出力する必要があるか どうかを示すフラグの二つです。

Enumerable#map は、前回も出てきましたが覚えて いただけているでしょうか？

ここでは、INSERT INTO 文の VALUES 以下の句を 生成するために使っています。

if rawvalue

という行で、rawvalue という条件式が真かどうかを評価して、 分岐しています。 C などの言語とは異なり、Ruby では制御構造は式です。 つまり、Ruby の if 文は値を返します。 if 文の返す値は最後に評価した式の結果となります。

なお、Ruby では false または nil だけが偽で、それ以外は 0 や空文字列も含めてすべて真です。

そして、Enumerable#map メソッドでは各要素に対してブロック を評価した結果をすべて含む配列を返します。

説明がまわりくどくなってしまいましたが、結局、 values = insertfields.map do |field,rawvalue| … end という一連の処理では、この if 文を評価した値を各要素と する配列を values に代入することになります。

  fieldStatement = insertfields.map{|f,v| f}.join(',')

また map が出てきましたね。今度は簡単です。 最初の要素の配列を作っているだけです。

  sql = "INSERT INTO earthquake (#{fieldStatement}) " +
    "VALUES ( #{values.join(',')} );"

今まで下準備をしてきましたが、ここで一気に SQL を 組み立てあげます。 こうすることで、フィールドの対応関係などを指折り 数えたりすることなく、SQL を生成できるように なります。

  begin
    conn.Execute sql
  rescue
    STDERR.puts sql
    STDERR.puts $!
  end

ここは先ほどの一行ずつレコードを追加するときのスクリプトと同じ記述です。 conn.Execute で、sql を実行しています。

それではこのスクリプトを実際に実行してみましょう。

サンプルの CSV がここにあります。 sample1.csv

1923/09/01 00:00:00,関東大震災,7.9,142807.0,true
1994/10/04 00:00:00,北海道東方沖地震,8.1,0.0,false
1995/01/17 00:00:00,阪神淡路大震災,7.2,6418.0,true
2004/10/23 00:00:00,新潟県中越地震,6.8,37.0,true

先ほど、関東大震災の行をすでに追加してしまっているときは、 その行を実行するときに除いてください。

ruby csv2rs.rb sample1.csv

と、実行すると csv に含まれるレコードをデータベースに 追加できます。

データソースのレコードの参照

前の章では、データソースに対してデータを追加していく方法について 学びました。

それでは、データソースに現在格納されているデータをどのようにして、 取得できるのでしょうか？

この章では、次の項目について学びます。

• レコードセットとは
• レコードセットの開き方
• カレントレコードとは
• カレントレコードの特定のフィールドの値の取得

では、具体的なサンプルスクリプトを見ていきましょう。

rs2csv.rb

require 'win32ole'

def startADO filename 
  cn = WIN32OLE.new("ADODB.Connection")
  connstr = "DRIVER={Microsoft Access Driver (*.mdb)};Dbq=#{filename}"
  cn.Open connstr
  begin 
    yield cn
  ensure
    cn.Close
  end
end

module Recordset
  def [] field
    self.Fields.Item(field).Value
  end

  def []= field,value
    self.Fields.Item(field).Value = value
  end
  
  def each_record
    if self.EOF or self.BOF
      return 
    end
    self.MoveFirst
    until self.EOF or self.BOF
      yield self
      self.MoveNext
    end
  end
end


startADO("sample1.mdb") do |cn|
  sql = "SELECT * FROM earthquake;"
  rs = cn.Execute(sql)
  rs.extend Recordset
  fields = ["Day","Name","Magnitude","NumOfDeaths","DeadOrAlive"]
  rs.each_record do |rs|
    values = fields.map do |field|
      rs[field]
    end
    puts values.join(",")
  end
end

このスクリプトでは、earthquake テーブルの値をカンマ区切りで 出力します。

まず、startADO メソッドによって、データソースへの接続を行います。

def startADO filename
  cn = WIN32OLE.new("ADODB.Connection")
  connstr = "DRIVER={Microsoft Access Driver (*.mdb)};Dbq=#{filename}"
  cn.Open connstr
  begin
    yield cn
  ensure
    cn.Close
  end
end

このメソッドは、filename を引数とします。 filename は Microsoft Access のデータベースのファイル名です。 すると、引数付きブロックに、Connection オブジェクトを 渡し、ブロック実行後に Connection オブジェクトを閉じます。

SQL ステートメントを実行した結果となるレコードセットを得るには Recordset オブジェクトの Open メソッドを利用します。

begin 〜 ensure 〜 end の構文は今まで何度もでてきた Before/After パターンのイディオムです。もう慣れてきました でしょうか？

   sql = "SELECT * FROM earthquake;"
   rs = cn.Execute(sql)
   rs.extend Recordset

ここでレコードセットを開きます。 Connection オブジェクトの Execute メソッドを用いて Recordset オブジェクトを作成しています。 詳しくは、ADO 入門講座 の 「レコードセットを開く」のページを参考にしてください。

このページにも書かれているのですが、Recordset オブジェクトを 作成するには、次の 3つの方法があります。

• Recordset オブジェクトの Open メソッドの利用
• Connection オブジェクトの Execute メソッドの利用
• Command オブジェクトの Execute メソッドの利用

Connection オブジェクトの Execute メソッドを利用する例について は先ほど説明しましたね。 前の節で実行していた SQL の INSERT 文をSELECT 文に変更すれば、 Execute メソッドでレコードセットを得ることができます。

しかしながら、Recordset オブジェクトの Open メソッド で得られる Recordset オブジェクトと、Connection オブジェクトや Command オブジェクトの Execute メソッドで得られる Recordset オブジェクトとは違う性質のものになります。 Connection オブジェクトや Command オブジェクトから作成すると 読み取り専用の Recordset オブジェクトが得られます。 これに対して、Recordset オブジェクトの Open メソッドを実行する 場合は、レコードの追加、変更、削除も行える Recordset オブジェクトを 得ることができます。

今回のスクリプトの場合は、参照しか行わないのでどちらの方法でも 可能です。 しかしながら、更新・削除を行う場合は、Recordset オブジェクトを 作成してから Open メソッドでレコードセットを開く必要があります。

    rs.extend Recordset

Object#extend メソッドを使うことで、rs オブジェクトに Recordset モジュールで定義しているレコードセットを操作する ためのメソッドを使えるようにしています。

上記スクリプトの Recordset モジュールでは、レコードセットの中の すべてのレコードのデータを取得するために each_record メソッドを 定義しています。

  def each_record
    if self.EOF || self.BOF
      return
    end
    self.MoveFirst
    until self.EOF || self.BOF
      yield self
      self.MoveNext
    end
  end

このモジュールでは、Recordset オブジェクトのプロパティ、 メソッドを利用して、すべてのレコードを順に操作できるような イテレータとして、each_record メソッドを定義しています。

ここで、レコードセットについて簡単に学んでいきましょう。

レコードセットというのは ADO においてデータソースのデータを 取得、変更するのに使うオブジェクトになります。 このレコードセットは、生成するのに使用した SQL ステートメント に対応するすべてのデータの集まりを持っています。

さらに、レコードセットは常に１つのレコードをカレントレコードと して参照します。 そして、そのカレントレコードの値を、Recordset オブジェクトから 取得、変更などを行うことができます。

ところが、場合によっては SQL に対応するレコードが一行も ない場合があります。 SQL に対応するレコードがあるかどうか調べるのに使うプロパティ が BOF や EOF です。 本来、BOF プロパティはカレントレコードが最初のレコードより前にあるか どうかをしらべるときの プロパティで、EOF はカレントレコードが 最後のレコードより後にあるかどうかを調べるプロパティです。

一致するレコードが一行もない場合は、EOF も BOF も true になります。 一致するレコードがあれば、開いた直後 BOF も EOF も両方とも false になります。

そこで、次のようにして、一致するレコードがなければ、処理をやめています。

    if self.EOF || self.BOF
      return
    end

ここで、self が何なのかを思い出しましょう。

rs.extend Recordset

と実行したため、この変数 rs がこの Recordset モジュールで self が指すオブジェクトとなります。

次に、カレントレコードを移動する方法について学んでいきましょう。

    self.MoveFirst
    until self.EOF || self.BOF
      yield self
      self.MoveNext
    end

ここで、Move 系メソッドについて学びましょう。 Move 系メソッドには次の4つがあります。

• MoveFirst 先頭のレコードに移動
• MovePrevious 1つ前のレコードに移動
• MoveNext 次のレコードに移動
• MoveLast 最後のレコードに移動

これらのメソッドを組み合わせることで、カレントレコードを 移動させていくことになります。

今回は MoveFirst と MoveNext を利用して先頭のレコードから 順に次の行へと移動していきます。 そして、カレントレコードを移動すると Recordset オブジェクトを yield して、特定の処理を行えるように引数となっている ブロックに処理を渡しています。

この一連の処理を BOF プロパティか、EOF プロパティが false に なるまで繰り返しています。

次は、each_record を実際に実行している部分の動作について学んで いきましょう。

  fields = ["Day","Name","Magnitude","NumOfDeaths","DeadOrAlive"]
  rs.each_record do |rs|
    values = fields.map do |field|
      rs[field]
    end
    puts values.join(",")
  end

まず、fields にテーブルのフィールド名の配列を代入しています。 そして、rs.each_record ですべての行に処理を行わせています。

    values = fields.map do |field|
      rs[field]
    end

では、配列に含まれた各フィールド名に対して、 そのカレントレコードでのそのフィールドの値を得ています。

     rs[field]

という行で、カレントレコードの field という名のフィールドに対応する 値を取得しています。

Recordset モジュールの定義を見ると [] メソッドは次のようになっています。

   def [] field
     self.Fields.Item(field).Value
   end

Recordset オブジェクトの Fields プロパティで得られる Fields コレクションは Field オブジェクトの集まりになります。これは複数形の場合は、それの単数形の オブジェクトのコレクションであるという単純な命名規則にしたがっている 例です。

Fields コレクションの Item プロパティを用いると引数 field に対応する Field オブジェクトが得られます。 Field オブジェクトの Value プロパティが、そのフィールドの値です。

最後に Array#join メソッドで、”,”区切りで、値を連結して 出力しています。

データソースに対する操作

次のスクリプトの例ではデータソースに対して新しいレコードの追加を 行っています。

addnew.rb

require 'win32ole'

module Recordset
  def [] field
    self.Fields.Item(field).Value
  end

  def []= field,value
    self.Fields.Item(field).Value = value
  end
end

def startRS filename,sql
  cn = WIN32OLE.new("ADODB.Connection")
  rs = WIN32OLE.new("ADODB.Recordset")
  connstr = "DRIVER={Microsoft Access Driver (*.mdb)};Dbq=#{filename}"
  cn.ConnectionString = connstr
  cn.Open
  rs.Open sql,cn,3,3
  rs.extend Recordset
  begin 
    yield rs
  ensure
    rs.Close
    cn.Close
  end
end

sql = "SELECT * FROM earthquake;"
startRS("sample1.mdb",sql) do |rs|
  newrecords = 
    [["Day" , "2000-10-06"],
    ["Name","鳥取県西部地震"],
    ["Magnitude","7.3"],
    ["NumOfDeaths","0"],
    ["DeadOrAlive","False"]]
  rs.AddNew
  newrecords.each do |field,value|
    rs[field] = value
  end
  rs.Update
end

レコードセットの更新・追加においては、まずレコードセットを 更新・追加できるような形で開くことが必要です。

それをしているのが、次の行です。

  rs.Open sql,cn,3,3

第3引数は、レコードセットのカーソルタイプを指定します。 第4引数は、レコードセットのロックタイプを指定します。

カーソルタイプとロックタイプを指定するときは、次の表を 参考にしてください。 この値はオブジェクトブラウザを使うことで参照できます。

カーソルタイプ

ロックタイプ

カーソルタイプを指定することで、カレントレコードをどのように 移動するかや、他のユーザの更新を考慮するかを決定できます。

ロックタイプを指定することで、データソースへのロックを どういう形で行うのかを決定できます。

第4引数に 3 を与えれば編集可能になり、レコードごとに共有ロックを行います。

前回の記事のように、WIN32OLE::const_load を使うことで COM オブジェクトで定義された定数を使うこともできます。

Recordset オブジェクトを用いてデータソースに格納されたデータを更新したい 場合は、このように rs オブジェクトを作成したのちに、rs.Open メソッドで カーソルタイプやロックタイプを指定して、レコードセットを開きます。

そして、実際に更新するときは次のメソッドを使います。

• AddNew メソッド
• Update メソッド

AddNew メソッドはレコードを新規追加するときに 用いるメソッドです。

AddNew メソッドを使うことで、カレントレコードを 新規レコードにできます。 つまり、AddNew メソッドを呼んでから、フィールドの 値を更新することで、新規追加するレコードの 値を設定できます。

SQL の INSERT INTO 文でも新規レコードの追加ができますが、 AddNew メソッドでも新規レコードの追加を行えます。

既存のレコードを更新するには、更新したいレコードに Move 系のメソッドなどで移動して、そしてフィールドの 値を更新することで行えます。

それでは、INSERT INTO 文を用いる新規レコードの追加と Recordset オブジェクトを用いた方法と二つありますが、 どのように使い分けると良いでしょうか？ この使い分けは単なる好みで決めても特に問題がないのですが、 Field オブジェクトのプロパティをみて、条件分岐をしたい 場合は、Recordset オブジェクトと AddNew メソッドを使用 することになります。

詳しくは、最後のよく使うメソッド一覧の章を参照して欲しいのですが、 Field オブジェクトにはフィールド名やフィールドの型を取得する プロパティがあります。

フィールドの型や名をプログラム中で参照したい場合は、 AddNew メソッドを使う方法の方が楽にプログラムを書けます。 なお、実行速度については自分が扱うデータベースで実際に 処理させてみてどちらが速いか時間を測ってみた方がいいでしょう。

   def []= field,value
     self.Fields.Item(field).Value = value
   end

Recordset モジュールのこのメソッド定義がデータの更新を行うときに 重要になります。

前回の Excel シートの使い方を説明するときでも、[]= メソッドの 定義を行いましたね。 今回もそれと同様です。 フィールド名と、その更新する値の二つを引数としてとります。

そして、field という名のフィールドの値 (Value プロパティ) を value に更新します。

そして、ここで定義されたモジュールとメソッドを次のように用いて、 新規レコードの追加を行います。

  rs.AddNew
  newrecords =
    [["Day" , "2000-10-06"],
    ["Name","鳥取県西部地震"],
    ["Magnitude","7.3"],
    ["NumOfDeaths","0"],
    ["DeadOrAlive","False"]]
  newrecords.each do |field,value|
    rs[field] = value
  end
  rs.Update

newrecords には、フィールド名とそのフィールドの値が 代入されています。

そして、この配列を使って新規レコードの field という名のフィールドの値を value に更新しています。

  rs.Update

Update メソッドは、更新した内容で確定するときに 必要となります。

実は Update メソッドを明示的に呼ばなくても 先ほどの Move 系のメソッドは、編集中に別のレコードに 移動すると、暗黙的に Update メソッドを呼び、 更新が保存されます。 そのため Move系メソッドと組み合わせて利用するときは、 Update メソッドを特に記述しなくても 更新された値が保存されます。

しかしながら、Update は明示的に呼ぶような習慣を持ちましょう。 そうすることで、そこで一区切りということが分かり スクリプトが分かりやすくなります。 それに今回のスクリプトのように Move 系メソッドを呼ばない場合には、 Update がないとエラーが発生することになります。 Update は書く習慣がある方がそのようなエラーに悩まされずに 済みます。

SQL ステートメントの生成

この章では、ADO と Win32OLE の組み合わせでも もちろん適用できますが、一般的にデータベースを 扱うときに役に立つ事柄について学んでいきます。

SQL ステートメントを生成することはデータベースを 扱う上では必ず遭遇します。

しかしながら SQL ステートメントを生成するときの コードにクールと感じさせられることは 多くありません。 単純な文字列の連結を連ねて書いて SQL ステートメントの生成を 行っているスクリプトをしばしば見かけます。

たとえば、次のようにして SQl を生成する方法があります。

sql = "INSERT INTO earthquake (Name,Day,Magnitude,NumOfDeaths,DeadOrAlive) " +
    "VALUES ('#{name}','#{day}',#{magnidude},#{death},#{dead});"

このような方法はパッとみて理解できるという点で優れています。

しかしながら、フィールドの数が多くなるにつれて この方法は破綻してしまいます。 フィールドの数があまりに多いと、長くなってしまい、対応関係が 見てもよく分からず、1 つ抜けてバグになってしまったときに 何が抜けているのかを調べるのに時間がかかってしまったりします。

もっと、簡単でいいやり方はないんでしょうか？

これまでにも何度か説明してきましたが、Ruby には Enumerable モジュールに、いくつか便利なメソッドが 定義されています。 map や join です。

これらのメソッドを組み合わせながら、SQL ステートメントを 生成するようなテクニックについてこの章では学んでいきます。

INSERT ステートメントの生成

INSERT ステートメントを生成するためのテクニックについては CSV からのデータの追加のところで、説明しました。 知りたい方についてはそちらを参照してください。

UPDATE ステートメントの生成

UPDATE ステートメントもEnumerable#map や Enumerable#join を使って SQL を生成することができます。

update.rb

def generateUPDATE tablename,values,constraints
  fields = values.map{|field,value,flag| field}
  field_statement = fields.join(",")

  sql = "UPDATE #{tablename} SET "
  vs = values.map do |field,value,rawvalue|
    if rawvalue
      "#{field} = #{value}"
    else
      "#{field} = '#{value}'"
    end
  end
  set_statement = vs.join(" , ")
  sql.concat set_statement
  
  where_statement = constraints.map do |field,value,rawvalue|
    if rawvalue
      "#{field} = #{value}"
    else
      "#{field} = '#{value}'"
    end
  end.join(" AND ")
  
  sql.concat " WHERE #{where_statement};"
  return sql
end

values = [['Magnitude',7.9,true],
  ['NumOfDeaths',142807,true],
  ['DeadOrAlive',"TRUE",true]]

constraints = [['Name','関東大震災'],['Day','1923/09/01']]

puts generateUPDATE("earthquake",values,constraints)

generateUPDATE メソッドは、3 つの引数をとります。

• テーブル名
• 更新するフィールド名とその値
• WHERE 句で指定する条件となるフィールドとその値

テーブル名と更新するフィールド名とその値というのは、 INSERT ステートメントのときと同じになります。

違いは WHERE 句で指定する条件となるフィールドと その値を引数とする点です。

これは、第2引数と同じ形式の配列です。

この WHERE 句用に指定した配列を利用して、WHERE 句を生成していきます。

このメソッドでも先ほどと同様に WHERE 句を利用します。

SET の次に続く文字列は次のように生成します。

  vs = values.map do |field,value,rawvalue|
    if rawvalue
      "#{field} = #{value}"
    else
      "#{field} = '#{value}'"
    end
  end
  set_statement = vs.join(",")

map で使っているブロック引数の代入は多重代入と 同じ規則に従います。 この場合 values の要素が配列のとき、その配列の数が 代入される側の個数、この場合は 2個を超えている場合は そのあまった要素は無視されます。 足りない場合、この場合は 1個しかない場合は、 対応する要素のないブロック引数には nil が代入されます。

この記述は慣れると非常に直感的です。 配列 values の 1 要素が更新したいフィールド 1 つに対応していました。 values から map メソッドを利用して、「フィールド名 = 値」の配列 vs を得ます。 そして配列 vs を join メソッドを利用して、連結することで SET 句を生成します。

同様のことを WHERE 句に対しても行っています。 WHERE 句のときの違いは join メソッドで連結するときにコロン(,) ではなく “ AND “ で連結しているという点です。 そして、次のような表記も目新しいところでしょうか？

  end.join(" AND ")

values.map 〜 end で配列が返されるので、そのまま join を呼ぶことで その配列の各要素を “ AND “ で連結できます。 一旦変数に格納する手間を省けて便利です。

SELECT ステートメントの生成

同様に SELECT 文を生成するスクリプトについても紹介しておきましょう。

select.rb

def generateSELECT tablename,fields,constraints
  field_statement = fields.join(",")

  sql = "SELECT #{field_statement} FROM #{tablename} "
  
  where_statement = constraints.map do |field,value,rawvalue|
    if rawvalue
      "#{field} = #{value}"
    else
      "#{field} = '#{value}'"
    end
  end.join(" AND ")
  
  sql.concat " WHERE #{where_statement};"
  return sql
end

fields = %w(Name Magnitude NumOfDeaths DeadOrAlive)
constraints = [['Name','関東大震災'],['Day','1923/09/01']]

puts generateSELECT("earthquake",fields,constraints)

これはもう分かりますね。 しつこく同じ内容を解説することはしません。

よく使うオブジェクトとメソッド

ここまで、ADO を使ってレコードの参照や更新を行う方法について 学んできました。 ADO のオブジェクトの中でも特によく使うオブジェクトと そのメソッドについて簡単にこの章で説明します。

どのような引数をとるかや、実際の使い方については、 MSDN や Google で検索した内容を参照してください。

ADO 関係の COM オブジェクトでよく使うのは次のものです。

Connection オブジェクト

Command オブジェクト

Recordset オブジェクト

Field オブジェクト

おわりに

今回は Win32OLE を利用して ADO を扱う方法について学びました。 ADO については紹介しきれない内容がまだまだあります。 例えば、テーブル構造の変更などを行うには、ADOX という COM コンポーネントを必要とします。

しかしながら、今回紹介した内容が分かれば普通に データベースを扱うには十分でしょう。

今の多くのシステムはデータベースと連携して動作します。 ADO を扱う今回紹介したようなやり方を知っていれば、さまざまな データベースを扱うときに役に立つでしょう。

次回は、私の記事は一旦お休みして、しむらさんによる 記事を掲載していただきます。

cuzic の記事としては次々回に Outlook でメールを読む、 メールを送るといった内容を扱います。

どうぞ、お楽しみ。

(アドバイザー：arton、助田 雅紀)

参考文献

• How To Open ADO Connection and Recordset Objects
• ADO 入門講座

著者について

cuzic は、親指シフトキーボードを自在に操る Ruby プログラマです。

風邪を引いて寝込んだときは、フルーツを食べてあったかいものを 飲んで過ごします。 医者には行きません。

Win32OLE 活用法 連載一覧

• Win32OLE 活用法 【第 7 回】 ほかの言語での COM

• Win32OLE 活用法 【第 6 回】 Web 自動巡回

• Win32OLE 活用法 【第 5 回】 Outlook

• Win32OLE 活用法 【第 4 回】 Adobe Illustrator

• Win32OLE 活用法 【第 3 回】 ADODB

• Win32OLE 活用法 【第 2 回】 Excel

• Win32OLE 活用法 【第 1 回】 Win32OLE ことはじめ