|
|
楼主 |
发表于 2007-8-1 08:19:25
|
显示全部楼层
|
【第 3 回】 ADODB
プロローグあなたは、膨大にあった 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 1|require 'win32ole' 2| 3|conn = WIN32OLE.new("ADODB.Connection") 4|connstr = "DRIVER={Microsoft Access Driver (*.mdb)};Dbq=sample1.mdb" 5|conn.Open connstr 6| 7|begin 8| sql = "INSERT INTO earthquake (Name,Day,Magnitude,NumOfDeaths,DeadOrAlive) " + 9| "VALUES ('関東大震災','1923/09/01',7.9,142807,TRUE);" 10| conn.Execute sql 11|rescue 12| STDERR.puts sql 13| STDERR.puts $! 14|end 15| 16|conn.Close
順に説明していきます。
conn = WIN32OLE.new("ADODB.Connection")ProgID が ADODB.Connection の COM オブジェクトを作成する行です。 conn が Connection オブジェクトになります。この Connection オブジェクトを用いて、どのデータソースへの接続するかを指定したり、接続を開いたり、閉じたり、SQL を実行したりします。
cconn.Open connstrConnection オブジェクトの 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 sqlrescue STDERR.puts sql STDERR.puts $!ensure conn.Closeend繰り返しになりますが、この記事では 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 1|require 'win32ole' 2| 3|def startADO filename 4| cn = WIN32OLE.new("ADODB.Connection") 5| connstr = "DRIVER={Microsoft Access Driver (*.mdb)};Dbq=#{filename}" 6| cn.Open connstr 7| begin 8| yield cn 9| ensure 10| cn.Close 11| end 12|end 13| 14|csvfields = %w(Day Name Magnitude NumOfDeaths DeadOrAlive) 15|insertfields = [ 16| ['Name',false], 17| ['Day',false], 18| ['Magnitude',true], 19| ['NumOfDeaths',true], 20| ['DeadOrAlive',true]] 21| 22|startADO("sample1.mdb") do |conn| 23| ARGF.each_line do |line| 24| record = {} 25| csvfields.zip(line.chomp.split(/,/)) do |field,value| 26| record[field] = value 27| end 28| values = insertfields.map do |field,rawvalue| 29| if rawvalue 30| "#{record[field]}" 31| else 32| "'#{record[field]}'" 33| end 34| end 35| fieldStatement = insertfields.map{|f,v| f}.join(',') 36| 37| sql = "INSERT INTO earthquake (#{fieldStatement}) " + 38| "VALUES ( #{values.join(',')} );" 39| begin 40| conn.Execute sql 41| rescue 42| STDERR.puts sql 43| STDERR.puts $! 44| end 45| end 46|end
conn = WIN32OLE.new("ADODB.Connection")cこの 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| ...endARGF というのは、スクリプトに指定した引数をファイル名とみなして、それらのファイルを仮想ファイルとしたオブジェクトです。簡単なスクリプトを作っているときに、引数のファイルを開く処理を記述する手間を省けるので、便利です。
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 endinsertfields は、二つの要素を持つ配列の配列となっています。その二つの要素とは、フィールド名と そのフィールドの値をシングルクオートで囲まず、生の値として出力する必要があるかどうかを示すフラグの二つです。
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,true1994/10/04 00:00:00,北海道東方沖地震,8.1,0.0,false1995/01/17 00:00:00,阪神淡路大震災,7.2,6418.0,true2004/10/23 00:00:00,新潟県中越地震,6.8,37.0,true先ほど、関東大震災の行をすでに追加してしまっているときは、その行を実行するときに除いてください。
ruby csv2rs.rb sample1.csvと、実行すると csv に含まれるレコードをデータベースに追加できます。
データソースのレコードの参照前の章では、データソースに対してデータを追加していく方法について学びました。
それでは、データソースに現在格納されているデータをどのようにして、取得できるのでしょうか？
この章では、次の項目について学びます。
- レコードセットとは
- レコードセットの開き方
- カレントレコードとは
- カレントレコードの特定のフィールドの値の取得
では、具体的なサンプルスクリプトを見ていきましょう。
rs2csv.rb 1|require 'win32ole' 2| 3|def startADO filename 4| cn = WIN32OLE.new("ADODB.Connection") 5| connstr = "DRIVER={Microsoft Access Driver (*.mdb)};Dbq=#{filename}" 6| cn.Open connstr 7| begin 8| yield cn 9| ensure 10| cn.Close 11| end 12|end 13| 14|module Recordset 15| def [] field 16| self.Fields.Item(field).Value 17| end 18| 19| def []= field,value 20| self.Fields.Item(field).Value = value 21| end 22| 23| def each_record 24| if self.EOF or self.BOF 25| return 26| end 27| self.MoveFirst 28| until self.EOF or self.BOF 29| yield self 30| self.MoveNext 31| end 32| end 33|end 34| 35| 36|startADO("sample1.mdb") do |cn| 37| sql = "SELECT * FROM earthquake;" 38| rs = cn.Execute(sql) 39| rs.extend Recordset 40| fields = ["Day","Name","Magnitude","NumOfDeaths","DeadOrAlive"] 41| rs.each_record do |rs| 42| values = fields.map do |field| 43| rs[field] 44| end 45| puts values.join(",") 46| end 47|end
このスクリプトでは、earthquake テーブルの値をカンマ区切りで出力します。
まず、startADO メソッドによって、データソースへの接続を行います。
def startADO filename cn = WIN32OLE.new("ADODB.Connection") c cn.Open connstr begin yield cn ensure cn.Close endendこのメソッドは、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 RecordsetObject#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 endRecordset オブジェクトの Fields プロパティで得られる Fields コレクションは Field オブジェクトの集まりになります。これは複数形の場合は、それの単数形のオブジェクトのコレクションであるという単純な命名規則にしたがっている例です。
Fields コレクションの Item プロパティを用いると引数 field に対応する Field オブジェクトが得られます。 Field オブジェクトの Value プロパティが、そのフィールドの値です。
最後に Array#join メソッドで、","区切りで、値を連結して出力しています。
データソースに対する操作次のスクリプトの例ではデータソースに対して新しいレコードの追加を行っています。
addnew.rb 1|require 'win32ole' 2| 3|module Recordset 4| def [] field 5| self.Fields.Item(field).Value 6| end 7| 8| def []= field,value 9| self.Fields.Item(field).Value = value 10| end 11|end 12| 13|def startRS filename,sql 14| cn = WIN32OLE.new("ADODB.Connection") 15| rs = WIN32OLE.new("ADODB.Recordset") 16| connstr = "DRIVER={Microsoft Access Driver (*.mdb)};Dbq=#{filename}" 17| cn.ConnectionString = connstr 18| cn.Open 19| rs.Open sql,cn,3,3 20| rs.extend Recordset 21| begin 22| yield rs 23| ensure 24| rs.Close 25| cn.Close 26| end 27|end 28| 29|sql = "SELECT * FROM earthquake;" 30|startRS("sample1.mdb",sql) do |rs| 31| newrecords = 32| [["Day" , "2000-10-06"], 33| ["Name","鳥取県西部地震"], 34| ["Magnitude","7.3"], 35| ["NumOfDeaths","0"], 36| ["DeadOrAlive","False"]] 37| rs.AddNew 38| newrecords.each do |field,value| 39| rs[field] = value 40| end 41| rs.Update 42|end
レコードセットの更新・追加においては、まずレコードセットを更新・追加できるような形で開くことが必要です。
それをしているのが、次の行です。
rs.Open sql,cn,3,3第3引数は、レコードセットのカーソルタイプを指定します。第4引数は、レコードセットのロックタイプを指定します。
カーソルタイプとロックタイプを指定するときは、次の表を参考にしてください。この値はオブジェクトブラウザを使うことで参照できます。
カーソルタイプ
| 値 | 定数名 | 説明 | | 0 | adOpenForwardOnly | レコードセットの先頭から後方へ移動できます。順に参照するときに高速に動作します。 (既定値) | | 1 | adOpenKeyset | 自由に移動できます。参照専用です。 | | 2 | adOpenDynamic | 自由に移動できます。他のユーザの更新を参照できます。 | | 3 | adOpenStatic | 自由に移動できます。他のユーザの更新を参照できません。 |
ロックタイプ
| 値 | 定数名 | 説明 | | 1 | adLockReadOnly | 読み取り専用です (既定値) | | 2 | adLockPessimistic | 排他ロックを行います | | 3 | adLockOptimistic | 共有ロックを行います | | 4 | adLockBatchOptimistic | 複数のレコードをバッチ更新処理します |
カーソルタイプを指定することで、カレントレコードをどのように移動するかや、他のユーザの更新を考慮するかを決定できます。
ロックタイプを指定することで、データソースへのロックをどういう形で行うのかを決定できます。
第4引数に 3 を与えれば編集可能になり、レコードごとに共有ロックを行います。
前回の記事のように、WIN32OLE::const_load を使うことで COM オブジェクトで定義された定数を使うこともできます。
Recordset オブジェクトを用いてデータソースに格納されたデータを更新したい場合は、このように rs オブジェクトを作成したのちに、rs.Open メソッドでカーソルタイプやロックタイプを指定して、レコードセットを開きます。
そして、実際に更新するときは次のメソッドを使います。
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 endRecordset モジュールのこのメソッド定義がデータの更新を行うときに重要になります。
前回の 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.Updatenewrecords には、フィールド名とそのフィールドの値が代入されています。
そして、この配列を使って新規レコードの field という名のフィールドの値を value に更新しています。
rs.UpdateUpdate メソッドは、更新した内容で確定するときに必要となります。
実は 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 1|def generateUPDATE tablename,values,constraints 2| fields = values.map{|field,value,flag| field} 3| field_statement = fields.join(",") 4| 5| sql = "UPDATE #{tablename} SET " 6| vs = values.map do |field,value,rawvalue| 7| if rawvalue 8| "#{field} = #{value}" 9| else 10| "#{field} = '#{value}'" 11| end 12| end 13| set_statement = vs.join(" , ") 14| sql.concat set_statement 15| 16| where_statement = constraints.map do |field,value,rawvalue| 17| if rawvalue 18| "#{field} = #{value}" 19| else 20| "#{field} = '#{value}'" 21| end 22| end.join(" AND ") 23| 24| sql.concat " WHERE #{where_statement};" 25| return sql 26|end 27| 28|values = [['Magnitude',7.9,true], 29| ['NumOfDeaths',142807,true], 30| ['DeadOrAlive',"TRUE",true]] 31| 32|constraints = [['Name','関東大震災'],['Day','1923/09/01']] 33| 34|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 1|def generateSELECT tablename,fields,constraints 2| field_statement = fields.join(",") 3| 4| sql = "SELECT #{field_statement} FROM #{tablename} " 5| 6| where_statement = constraints.map do |field,value,rawvalue| 7| if rawvalue 8| "#{field} = #{value}" 9| else 10| "#{field} = '#{value}'" 11| end 12| end.join(" AND ") 13| 14| sql.concat " WHERE #{where_statement};" 15| return sql 16|end 17| 18|fields = %w(Name Magnitude NumOfDeaths DeadOrAlive) 19|constraints = [['Name','関東大震災'],['Day','1923/09/01']] 20| 21|puts generateSELECT("earthquake",fields,constraints)
これはもう分かりますね。しつこく同じ内容を解説することはしません。
よく使うオブジェクトとメソッドここまで、ADO を使ってレコードの参照や更新を行う方法について学んできました。 ADO のオブジェクトの中でも特によく使うオブジェクトとそのメソッドについて簡単にこの章で説明します。
どのような引数をとるかや、実際の使い方については、 MSDN や Google で検索した内容を参照してください。
ADO 関係の COM オブジェクトでよく使うのは次のものです。
| オブジェクト | 説明 | | Connection | データソースへの接続です。 | | Command | データソースに対して実行するコマンドを保持します | | Recordset | テーブルやSQLステートメントを実行して返されたレコードセット | | Field | Recordsetオブジェクトの FIeld オブジェクトを格納します |
Connection オブジェクト| BeginTrans | 新規トランザクションを開始します。 | | Close | 開いている接続とこの接続に関連する Recordset オブジェクトをすべて閉じます。Recordset オブジェクトの保留中の変更はすべてロールバックされます。 | | CommitTrans | すべての変更を保存して現在のトランザクションを終了します。 | | ConnectionString | データソースへの接続のために必要な情報を設定するときに使用します。 | | Execute | クエリや SQL ステートメントを実行します。 | | Open | データソースへの物理的な接続を確立します。 | | RollbackTrans | すべての変更をキャンセルして現在のトランザクションを終了します。 | | State | オブジェクトが開いているか閉じているかを示します。 |
Command オブジェクト| CommandText | 通常は、実行したい SQL ステートメントを設定するときに使用します。 | | Execute | クエリや SQL ステートメントを実行します。 | | State | オブジェクトが開いているか閉じているかを示します。 |
Recordset オブジェクト| AddNew | 新規レコードの作成と初期化を行います | | BOF | カレントレコードの位置が最初のレコードより前にあることを示します | | Cancel | 非同期メソッドの中で保留中のものをキャンセルします。 | | Clone | Recordset オブジェクトの複製を作成します。複数のカレントレコードを保持したいときに使います。 | | Delete | カレントレコードを削除するときに使います。 | | EditMode | カレントレコードに保留中の変更があるかどうかを調べるときに使います。 | | EOF | カレントレコードの位置が最後のレコードより後にあることを示します。 | | Fields | Recordset が保持する Field のコレクション | | Find | 指定した条件を満たす行を検索します。 | | GetRows | 複数のレコードを配列に取り込みます。 | | GetString | Recordset を文字列として返します。 | | Move | カレントレコードの位置を移動します。 | | MoveFirst | 最初のレコードに移動してそのレコードをカレントレコードにします。 | | MoveLast | 最後のレコードに移動してそのレコードをカレントレコードにします。 | | MoveNext | 次のレコードに移動してそのレコードをカレントレコードにします。 | | MovePrevious | 前のレコードに移動してそのレコードをカレントレコードにします。 | | Open | Recordsetを開きます | | RecordCount | Recordsetオブジェクト内のレコード数です。 | | Requery | Recordset を開くときのコマンドを再実行して、データソースからもう一度取得しなおします。 | | Resync | 再同期を行います | | Save | Recordset をファイルまたは Stream オブジェクトに保存します。 | | Seek | Recordset のインデックスを検索して、指定値と一致する行にすばやくカレントレコードを移動します。 | | Supports | レコードの追加、変更などが可能なレコードセットかを調べるときに使います。 |
Field オブジェクト| Name | フィールドの名前を取得します。 | | Type | フィールドの型が何かを取得できます。 | | OriginalValue | 変更が行われる前のこのフィールドの値です。 | | Value | このフィールドの値です。 | | UnderlyingValue | データベース内のこのフィールドの値です。 |
おわりに今回は Win32OLE を利用して ADO を扱う方法について学びました。 ADO については紹介しきれない内容がまだまだあります。例えば、テーブル構造の変更などを行うには、ADOX という COM コンポーネントを必要とします。
しかしながら、今回紹介した内容が分かれば普通にデータベースを扱うには十分でしょう。
今の多くのシステムはデータベースと連携して動作します。 ADO を扱う今回紹介したようなやり方を知っていれば、さまざまなデータベースを扱うときに役に立つでしょう。
次回は、私の記事は一旦お休みして、しむらさんによる記事を掲載していただきます。
cuzic の記事としては次々回に Outlook でメールを読む、メールを送るといった内容を扱います。
どうぞ、お楽しみ。
(アドバイザー：arton、助田 雅紀)
参考文献
著者についてcuzic は、親指シフトキーボードを自在に操る Ruby プログラマです。
風邪を引いて寝込んだときは、フルーツを食べてあったかいものを飲んで過ごします。医者には行きません。 |
|
发表于 2007-8-1 08:14:27
提升卡
变色卡
たとえば海外では非常に人気があるスクリプト言語である Python では、 "Python Object Browser" というある COM の型が持つメソッドの引数などを簡単に調べるためのツールがあります。 
とさまざまなブラウザがありますが、真打はやはり Microsoft が提供しているオブジェクトブラウザでしょう。Visual Basic Editor に含まれるこのツールを使うと、クラスが持つメソッドやプロパティの一覧やメソッドの使い方を見ることができます。オブジェクトブラウザは、MS Office の Visual Basic for Applications のエディタをまず起動して、[F2] キーを押すことで起動できます。これは、無料では手に入りませんが使いやすくて便利です。
Application オブジェクトは Excel のアプリケーション自身を表現します。実行しているアプリケーションに関する情報を提供します。
(先ほどと画像の倍率は変えています) 
例のようなメソッドの連鎖はできないのですが、 doc.TextArtItems.item(1).Contents は、"Hello,World!" という文字列を返します。このような、プロパティ参照の連鎖なら大丈夫です
単位はポイント。
CMYKColorオブジェクトは、WIN32OLE.new で直接生成します。そして各色の値を設定します、値の範囲は、CMYK、Gray は 0～100.0、RGB では 0～255.0 です、この範囲が違うことにも注意します。
今回は関西 Ruby 勉強会のメーリングリストの履歴をメールのサンプルとして使用しています。この勉強会にはこの記事の著者である cuzic も参加しており、 RubyOnRails など勉強会参加者が興味を持つ内容について一緒に勉強しています。この記事を今、読んでいる方も興味がありましたら是非ご参加ください。詳しくは、
これは、プログラムから電子メールアドレスを取得する場合に表示されます。このダイアログボックスが表示されることが、Outlook で電子メールを扱うときに障害になる場合があります。特に、サーバで自動的に行いたいような場合では、致命的でしょう。このことを念頭に置きながら電子メールの送信の扱いを Outlook で行うかどうかを判断してください。
実は筆者はイベントのインタフェース名を調べる方法が分からなくてかなりの時間を費やした経験があります。この記事を読んでいる人はそのような無駄な時間を費やさず効率的に開発を行ってください。
これを使うと次の画面のように、簡単にフィールドに対する検索条件から、生成される文字列がどのようになるのかについて確認できます。
AdvancedSearch の機能を駆使したい場合は、便利な機能です。
ここで、OK をクリックすると、メールの送信が可能になります。