T. Yoshiizumi - yado_guide02 Diff
- Added parts are displayed like this.
- Deleted parts are displayed
like this.
'''ADO｜ADOXによるAccessおよびExcelファイルの処理'''
{{br}}
'''〜rubyによる制御〜 part ２／２'''
最終更新日:2009/04/012009/11/03
{{toc_here}}
!7. テーブルの作成
これまでのサンプルスクリプトで、既に create table によってテーブルを作成しています。IDの番号を自動的にセットするオートインクリメントにも言及しました。しかし、テーブルの作成をめぐっては他にもいろいろな事柄があります。
ここでは、SQL命令の create table でテーブルを作成する場合のデータ型、PRIMARY KEYなどのオプション、使えない文字(半角のピリオドやスペースなどの扱い方)の話を紹介し、その後でADOXでのテーブル作成の技術的事項について説明したいと思います。
!!(1) データ型
mdbファイルでテーブルを作成する場合、主に次のようなデータ型が使えます。SQL命令で使えるデータ型を示します。
表４ SQL命令において使えるデータ型
||!型名||!SQL指定例||!説明
||整数||smallint integer2 short||2バイトの符号付き整数
||整数||int integer integer4 long||4バイトの符号付き整数
||オートインクリメント||counter(10,5) identity||自動インクリメント
||単精度浮動小数点||real Float4 ieeesingle||単精度浮動小数点値
||倍精度浮動小数点||float double float8 ieeedouble||倍精度浮動小数点値
||通貨||money||小数点以下4桁の固定小数点。8バイトの符号付き整数
||日付||date time datetime smalldatetime||倍精度浮動小数点数。整数部分は1899年12月30日からの日数
||Yes｜No||bit yesno logical||ブール値。trueまたはfalse
||整数||tinyint||1バイトの符号なし整数
||グローバル識別子||guid UNIQUEidentifier||固有のグローバル識別子
||固定長文字列||char(35) nchar||最長255文字、指定文字数未満時は半角空白を補充
||固定精度数値||decimal(20,4) dec numeric||固定精度およびスケールの正確な数値
||可変長文字列||varchar(50) nvarchar||最長255文字
||長文字列||text memo ntext note longchar longtext||最長約512メガ
||バイナリ||binary(80) varbinary||最長510バイト。指定バイト未満時は\000を補充
||長バイナリ||longbinary image general oleobject||最長約1ギガバイト
上の表で、例えば、倍精度浮動小数点のところに「float double float8 ieeedouble」とあります。この４つの記述のどれを書いても同じ倍精度浮動小数点型になります。
create table テーブル01
(身長 float,
体重 double);
{{br}}
と書いた場合、身長も体重も同じデータ型です。同じデータ型を複数の書き方で記述できるものが結構あるので注意して下さい。
以下、上に掲げた表に沿って少し注釈を加えます。
''○ 日付型の date''
日付型といっても、mdbでは日付と時刻がセットになっています。
rubyスクリプトでこの日付型の値を取り出すと、"2009/02/22 00:00:00" のように文字列で返ってきます。これを数値として取り扱いたい時は、自前でそれ用のメソッドを作るしかないのかもしれません。
''○ Yes｜No型の yesno''
Yes|No型は、「イエスかノーか」 「有るか無いか」 「真か偽か」を表現する時に用います。Accessで操作している時は「チェックあり、チェックなし」です。
rubyスクリプトでこのデータ型の値を取り出すと、true, false のどちらかです。
値をセットする時は、true, false を使うのが本来かもしれませんが、数字の 1 か 0 で与えるのも便利です。1, 0 であれば、数値、文字のどちらで与えても大丈夫です。
''○ 固定長文字列の char(35)''
char(35)とすると最大長が35文字となり、それを超えるデータを挿入しようとすると、「指定されたデータ量がフィールド サイズを超えています。」とエラーが発生して、レコードの挿入は行われません。
また、実際のデータが35文字に満たないとき、例えば20文字だった場合、残りの15文字分が半角スペースになります。
バイトではなく、あくまで文字単位です。全角も半角も同じ１文字として扱われます。
なお、文字数を指定せずに char と指定すると char(255) とみなされます。
char で扱える最大長は 255文字です。
''○ 可変長文字列の varchar''
charと異なり、データを取り出した時に末尾に余分な空白が補充されません。データを書き込んだ時と同じ状態のものを取り出せます。
それ以外は char と同じです。
''○ 固定精度数値の decimal(20,4)''
このデータ型は、小数点以下の最大桁数と整数部の最大桁数を指定したい時に用います。
decimal(20,4)とした場合、小数点以下の桁が最大で4桁、整数部分の桁が最大で20-4の16桁となります。この桁数にはマイナス記号も含まれます。
桁数を指定せずに decimal とすると、decimal(18,0) になります。decimal(12) とすれば decimal(12,0) と同じです。
このデータ型は数値ですが、rubyでは、値が文字列として得られます。取り出した値を数値として扱うためには data.to_f のように変換する必要があります。データ型の money も同じです。
''○ バイナリ型の binary(80)''
比較的容量の小さいバイナリデータを入れる時は binary を用います。
binary(80) と宣言すると、最大サイズが80バイトになり、それを超えるデータを書き込もうとするとエラーが発生して、レコードが挿入されません。
また、80バイトに満たないデータを書き込んだ場合は、満たない分だけ末尾に "\000" が補充されます。
なお、桁数を指定せずに binary と指定すると binary(510) とみなされます。
このデータ型で扱える最大長は 510バイトです。
''○ 長バイナリ型の longbinary''
このデータ型は、画像等の長いバイナリデータを収納するのに使います。最大、約１ギガバイトまで収納可能なようです。
''○ 長文字列型の longtext''
このデータ型は、長いテキストデータを収納するのに使います。最大、約512メガ文字まで収納可能だと思います。512メガ「バイト」でなく、「文字」だと思いますが、本当にそうなのか よく分かりません。
!!(2) データ型指定時のオプション
△ 取り上げるオプション： 主キーの PRIMARY KEY、重複禁止の UNIQUE、空欄禁止の NOT NULL、入力省略時規定値の DEFAULT
データ型を指定する時のオプションで、よくあるのが「PRIMARY KEY」だと思います。これは主キーといわれるもので、このオプションが指定されたフィールドにデータを書き込む時は、下に掲げる条件を満たしていないとエラーになります。
例えば、ID番号が主キーである場合、その条件は次のとおりです。
*(a) 重複禁止：同じテーブル内に同じ値のものが存在しないようにします。既にIDが4番の人のデータが登録されていれば、他に4番の人を登録できません。必ず別の番号にする必要があります。
*(b) 空欄禁止：空欄(NULL)にしておくことが認められません。必ず何か書き込まなければなりません。
これらの条件が守られていると、IDがレコードを代表する項目になります。テーブルと他のテーブルを結びつける時も、この主キーを手がかりにします。
主キーは、先に上げた２つの条件を両方とも満たすわけですが、それぞれ個別にオプション指定することができます。
*(a) 重複禁止： UNIQUE
*(b) 空欄禁止： NOT NULL
例えば、テーブルを定義する時に次のように指定します。
−−−−
create table テーブル12
(ID counter PRIMARY KEY,
氏名 varchar(15) NOT NULL,
社員番号 int UNIQUE,
担当業務 varchar(20) DEFAULT 営業,
月間売上げ money DEFAULT 0);
−−−−
「氏名」は、空欄にしておくことはできませんが、同姓同名の人がいてもちゃんと登録できます。重複しても問題ありません。
「社員番号」の方は、空欄にしておくことはできますが、他と重複するものであってはいけません。「空欄の人が何人かいても大丈夫なのか？」と疑問に思われるかもしれませんが、それは大丈夫です。
そのほか、入力がなかった時の規定値を設定することができます。
月間売上げ money DEFAULT 0{{br}}のように設定できます。「月間売上げ」として何も入力されない時は、その欄に 0 が自動的にセットされます。
担当業務 varchar(20) DEFAULT 営業{{br}}のように規定値を文字列で与える場合、シングルクォートで囲んで '営業' としても問題ないようですが、規定値にシングルクォートが含まれる形で記録されます。囲まない方がいいような気がします。
!!(3) SQL命令においてテーブル名やフィールド名に使えない文字
テーブルの作成の時にかぎりませんが、SQL命令を記述する際、テーブル名やフィールド名として使えない文字があります。
△ 要点：
*(a)半角１バイトの記号類は使わないのが無難。ただし '_' は大丈夫。
*(b)半角１バイトの英字類を使う時は [……] で囲むと良い。
ある年賀状印刷用の住所録ソフトのデータをCSVにしたところ、「No.」とか「TEL.」というフィールド名がありました。しかし、これをそのままmdbファイルに登録しようとしても拒否されます。それは、半角ピリオドがSQL命令において特別な意味を持つため、テーブル名やフィールド名として使えないからです。
半角ピリオドほどではないにしても、普通の記述方法だと拒否されるものがあります。例えば、半角スペースがそうです。仮に「book title」というフィールド名を用いたいとき、
create table テーブル01 (ID int, book title varchar(50));{{br}}と書くと、「フィールド定義の構文エラー」になります。おそらく、最初に現れる単語(空白は含まない)をフィールドの名前、それに続く単語をデータ型とみなすのが原則になっているためだと思います。
この場合、「book title」が一まとまりのものであることを示すため、ADOのSQL記述では [book title] と書きます。半角の [……] で囲みます。
create table テーブル01 (ID int, [book title] varchar(50));{{br}}とすればエラーなくテーブルを作成できます。
ただ、このやり方を採っても半角ピリオドは受けつけてもらえません。エラーになります。
半角の英語関係の記号で、[……] で囲まずに使えるのは、私が試した限りでは '_' (アンダーライン)だけでした。
基本的に、半角の記号は用いないのが無難だと思いますが、一応、私の試験結果では、[……] で囲んだとしても使えないのは次のものでした。
！：感嘆符 ．：ピリオド
［：大括弧 ］：閉じ大括弧
‘：アクサングラーブ("\140")
これ以外は、[……] で囲むと使えます。ただし、”：ダブルクォートおよび ’：シングルクォートは最初から対象外としました。
上では例としてフィールド名を上げましたが、テーブル名についても同じです。
また、半角の場合、記号でなければ大丈夫かというとそうではありません。アルファベットの単語で、そのままでは拒否されるものがあります。例えば、SQL命令として用いられる create とか insert などの予約語がそうです。
ADOのバージョンが上がるとこの辺が改善されているのかもしれませんが、ver 2.7 では「No」も駄目でした。
ただ、このアルファベットの場合は [……] で囲むことで大半が大丈夫です。
そのほか、全角文字であれば、テーブル名・フィールド名として問題なく使えるようです。
半角１バイトのカナを試してみたところ、それも大丈夫でした。
SQL命令を書いて query メソッドで実行する場合は、上の「使えない文字」に留意する必要があります。
ただし、他の Yado で定義されているメソッドを用いる場合は、かってながら「使えない文字」を削除したり、[……] で囲んだりして対応します。先に紹介した insert_records など総てそうです。それらメソッドを使う分には「使えない文字」をあまり意識しなくて大丈夫です。
!!(4) ADOXによるテーブルの作成(SQL命令に依らない方法)
少し技術的な話になりますが、ADOXでテーブルを作成する時の手順について書きます。
SQL命令の create table のスタイルで書くと下のようになるケースをADOX独自スタイルで書いてみたのが sample13.rb です。なお、SQLで書いたものを参考まで sample12.rb として付属させてあります。
−−−−
create table テーブル13
(ID counter PRIMARY KEY,
氏名 varchar(15) NOT NULL,
今期出勤比率 decimal(5,2) NOT NULL,
所属部署 varchar(30) DEFAULT 営業部,
今期売上げ money DEFAULT 0,
自社株所有組合番号 int UNIQUE);
−−−−
その sample13.rb のポイントについて書きます。
''○ データ型を示す定数の使用''
Visual BASIC などでは、データ型を指定するのに adInteger とか adVarWChar などの定数を使えます。
実は、rubyでもそれら定数を使うことができます。
ただ、rubyでは定数名の最初の文字は大文字にする規則なので、AdInteger のように書きます。
また、いきなり使えるわけではなく、使えるようにするための手続きが必要です。下に定数の値を表示する簡単なスクリプトを掲げておきます。
−−−−
require 'win32ole'
module Adoc # 定数を収納するためのモジュール(空っぽでかまわない)
end
cn = WIN32OLE.new("ADODB.Connection")
WIN32OLE.const_load(cn, Adoc)
include Adoc
puts AdInteger # → 3
puts AdVarWChar # → 202
puts Adoc.constants # 定数名の一覧を表示
−−−−
sample13.rbでは、上記の定数群を取り込んで使っています。
なお、Yadoではこの定数群を導入していません。
''○ テーブル、カラム(フィールド)、インデックスの３つのオブジェクト''
ADOXでテーブルを定義する時は
−−−−
tbl = WIN32OLE.new("ADOX.Table")
col = WIN32OLE.new("ADOX.Column")
idx = WIN32OLE.new("ADOX.Index")
−−−−
の三つのオブジェクトを用います。
フィールドの定義には col を、インデックスの定義には idx をそれぞれ必要な個数だけ使います。それらをテーブルを示す tbl にセットします。
インデックスは、主キー(PRIMARY KEY)や重複禁止(UNIQUE)の指定に用います。そうしたものがなければ省略してかまいません。
横道に逸れますが、インデックスをなぜ設けるのかについて少し書きます。
データベースの利用者がレコードを挿入しようとする際、重複禁止のフィールドに重複データをセットしようとすると、エラーが発生して挿入できません。それは、システムの側が重複かどうかをチェックしているから可能になることです。
このシステム側のチェッくが、既存のレコード１つづつとの照合で行っているとすれば、レコード数が多くなるのに比例してチェックに要する時間も増えます。レコードの挿入にどんどん時間がかかるようになってしまいます。
それを防ぐため、インデックス(索引)を設けて、重複チェックを効率よく行えるようにするわけです。そこで、重複禁止のフィールドである PRIMARY KEY, UNIQUE のフィールドをインデックスに登録しておきます。
インデックスに登録できるのは、PRIMARY KEY と UNIQUE に限りません。それ以外のフィールドであっても、よく手がかりにするフィールドをインデックスに登録しておけば、スピーディーに探索できることになります。
''○ カラム(フィールド)の定義''
フィールドの定義では、その名前・データ型の２つ、あるいは、名前・データ型・桁数の３つで足りる場合は、わざわざ col を使わず、直接
−−−−
tbl.Columns.Append 'ID', 3 # → ID int に相当する。
tbl.Columns.Append '氏名', 202, 50 # → 氏名 varchar(50) に相当する。
−−−−
{{br}}
のように直接 Append を呼び出してtblにセットできます。
しかし、decimal(20,4) の場合は、フィールドの名前・データ型・その他２つの桁数の、合計４つのパラメータになります。このような時は、col に４つのパラメータをセットしてから{{br}}
tbl.Columns.Append col{{br}}とする必要があります。
オートインクリメントの counter(10,5) の場合も col を設定する必要があります。下にその該当部分を示します。
−−−−
col = WIN32OLE.new("ADOX.Column")
col.Name = 'ID'
col.Type = AdInteger
col.ParentCatalog = cat
col.Properties("Autoincrement").Value = true
col.Properties("Seed").Value = 1 # 初期値
col.Properties("Increment").Value = 1 # 増分値
tbl.Columns.Append col
−−−−
「col.ParentCatalog = cat」の意味はよく分かりませんが、Properties("……")の値をセットする時は、これを設定しておかないとエラーになるようです。
''○ フィールドの NOT NULL および DEFAULT オプションの指定''
ADOXでのフィールド定義では、特に指定しないかぎり、その欄が空欄禁止になります。SQL命令における NOT NULL オプションが指定された状態です。SQLとは逆で、空欄を許可したい時に意図的に次の設定を行います。{{br}}
col.Properties("NULLable").Value = true
次に、規定値のDEFAULTを設定する場合は次のようにします。{{br}}
col.Properties("Default").Value = "営業部"
なお、Properties("Jet OLEDB:Allow Zero Length") というのもあります。長さ０を許可する時は true に、許可しない時は false にします。
最初、これがSQLの NOT NULL に連動しているのではないかと思いました。しかし、あれこれ試してみると違うようです。入力を省略する(NULL)ことと、「""」のような長さ０の文字列をセットすること、この２つは別物という考え方なのだろうと推測します。
この辺の仕様は、正直なところ よく分かりません。SQLの NOT NULL に対応するのが Properties("NULLable") であるらしいというのは、私のあれこれの試験の結果から得た感触です。本当にそれで間違いないのかと言われると、「たぶん」という程度です。
他に col.Attributes というのがあります。これが取り得る値は、基本的に adColFixed(数値の1)または adColNullable(数値の2)ですが、この２つの組合せ 0〜3 のどれかになるようです。
adColNullable という定数名からして、空欄許可に関連するのではないかと思い、col.Attributes が2以上であることと、col.Properties("Nullable").Value が true であることとが等価であるか調べたところ、Attributes が 1 であっても Properties の方が true であるケースがいくつかみつかりました。なので、私の単純な推測は間違いということになります。
また、col.Attributes に値をセットしようとするとエラーが発生することが結構あって、確実に値をセットするための方法が分かりません。なので Yado では col.Properties("Nullable") の方に着目することにしました。
''○ インデックスの定義''
PRIMARY KEY と UNIQUE の指定はインデックスに記録します。
−−−−
idx.PrimaryKey = true
idx.Unique = true
idx.IndexNULLs = 1 # インデックスにおける空欄禁止
−−−−
{{br}}
とすると、SQL命令において PRIMARY KEY を指定したことに相当します。
−−−−
idx.PrimaryKey = false
idx.Unique = true
idx.IndexNULLs = 0 # インデックスにおける空欄許可
−−−−
{{br}}
とした場合は、UNIQUE を指定したことに相当します。
インデックスの名前 idx.Name は、主キーであれば PrimaryKey にするようです。それ以外はフィールド名と同じでいいと思います。この辺について、何か規則があるのかどうかよく分かりませんが、Accessに付属の Northwind.mdb ではそうなっています。
また、これとは別に idx.Columns.Append によって、該当するフィールドの名前を登録します。サンプルの場合、主キーでは「ID」、UNIQUEでは「自社株所有組合番号」を登録します。
そして、１つのインデックスを設定し終える度に
tbl.Indexes.Append idx{{br}}を実行して、インデックスをテーブルにセットします。
''○ 最後の登録処理''
このように、フィールド col と、インデックス idx を必要な個数だけtblにセットしてから、最後に
cat.Tables.Append tbl{{br}}とすればテーブルの定義が完了です。
!!［補足］ Propertiesメソッドのパラメータと値
Yadoでは、Propertiesメソッドが持つパラメータの一部にしか着目していません。Autoincrement関係、Nullable、Default くらいです。しかし、それ以外にもいろいろあります。私が試したところでは''15個のパラメータ''があります(注)。
残念ながら、各々のパラメータとその値の意味が私にはよく分かりませんが、それらを表示させることは可能です。そのサンプル・スクリプトを下に掲げます。
「TEST.MDB」というデータベースと、その中に「テーブル01」という簡単なテーブルを設け、そのPropertiesを表示するものです。
dbnameという変数にデータベース名を代入すれば、それに含まれる標準テーブル総てについて Properties の情報を表示します。
−−−− ここから
# Propertiesメソッドのパラメータと値を表示するサンプル
$KCODE = 'S'
require 'yado'
dbname = "TEST.MDB"
db = Yado.new dbname
tblname = "テーブル01"
sql = "create table #{tblname} (可変長文字列 varchar(30) NOT NULL," +
"固定長文字列 char(5) DEFAULT あいうえお," +
"オートインクリメント counter(100,10));"
db.query(sql)
db.close
cn = WIN32OLE.new("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=#{dbname};"
cat = WIN32OLE.new("ADOX.Catalog")
cat.ActiveConnection = cn
puts 'データベース名：' + dbname
cat.Tables.each {|tbl|
next unless tbl.Type == 'TABLE' # 標準テーブル以外はスキップ
puts 'テーブル名：' + tbl.Name
tbl.Columns.each {|col|
puts 'フィールド名：' + col.Name
col.Properties.each {|prop|
p [prop.Name, prop.Value]
}
printf("\n")
}
}
cn.Close
−−−− ここまで
上のスクリプトは、rubyのeachメソッドを使っているので Visual BASIC などには置き換えにくいかもしれません。eachを使わずに書くと下のようになります(該当箇所のみ)。
−−−−
（前略）
for tbl_i in 0..(cat.Tables.Count-1)
tbl = cat.Tables.Item(tbl_i)
if tbl.Type != 'TABLE' # 標準テーブル以外はスキップ
next
end
puts 'テーブル名：' + tbl.Name
for col_i in 0..(tbl.Columns.Count-1)
col = tbl.Columns.Item(col_i)
puts 'フィールド名：' + col.Name
for i in 0..(col.Properties.Count-1)
p [col.Properties.Item(i).Name, col.Properties.Item(i).Value]
end
printf("\n")
end
end
cn.Close
−−−−
(注) Propertiesのパラメータとして使える文字列は、下の15個です。これらを使う時は、アルファベットの大文字・小文字の区別も行うのが無難だと思います。
−−−−
Autoincrement
Default
Description
Nullable
Fixed Length
Seed
Increment
Jet OLEDB:Column Validation Text
Jet OLEDB:Column Validation Rule
Jet OLEDB:IISAM Not Last Column
Jet OLEDB:AutoGenerate
Jet OLEDB:One BLOB per Page
Jet OLEDB:Compressed UNICODE Strings
Jet OLEDB:Allow Zero Length
Jet OLEDB:Hyperlink
−−−−
以上、ADOXの独自スタイルでテーブルを作成する方法について書きましたが、これは、Propertiesを駆使した細かな設定が可能とはいえ、かなり面倒です。SQLで行う方がずっと楽だというのが率直な感想です。
----
!8. Excelファイルを扱う時の注意点およびサンプル
YadoでExcelファイルを扱う場合は、データベースの名前として TEST.XLS のように、拡張子 .xls のファイルを指定します。
db = Yado.new "TEST.XLS"{{br}}のようにすると、dbオブジェクトを用いてExcelファイルを操作できます。
ただし、Accessのmdbファイルと異なり、いくつか制限があります。また、独特の仕様もあります。
!!(1) xlsファイルを扱う時の制限事項
xlsファイルを扱う場合にどのような制限があるか、十分には把握していませんが、次のような点があるようです。いずれも ADO ver 2.7 で確認しました。
(a) テーブルを作成するとき、PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT といったオプションを指定することができません。{{br}}
主キーを指定できないので、例えば、Yadoの update_records メソッド(主キーを手がかりにするレコードの更新)を使うことができません。update_pair の方は大丈夫です。
(b) オートインクリメント機能を使うことができません。テーブル作成時に、データ型として counter を指定できません。{{br}}
前述のオプション指定が行えないことと考え併せると、columns.Properties("……") に関わる設定が行えないのだと思います。
(c) 長いバイナリを扱うためのデータ型 longbinary を使うことができません。{{br}}
また、短いバイナリ binary 型の方は、結局、可変長文字列の varchar 型になってしまいます。xlsファイルではバイナリの処理を避けるのが無難です。
(d) レコードを削除しようとすると、「この ISAM では、リンク テーブル内のデータを削除することはできません。」とエラーが発生し、レコードの削除を行えません。{{br}}
SQL命令の delete も、ADODB.Recordset の rs.Delete も、どちらも実行できません。
(e) テーブル削除の DROP TABLE を実行しても、テーブルは削除されません。ただし、テーブルの中身は消去されます。
(f) 仮装テーブルの VIEW は使えません。「この操作は、このタイプのオブジェクトには実行できません。」となります。
ざっと確認しただけですが、以上のような制限があります。
!!(2) xlsファイルに関する独特の仕様
ADOでxlsファイルを扱う場合、以下のような独特の仕様があるようです。
(a) 扱うテーブルの名前を書き時に注意が必要です。
*既に存在しているワークシート全体をテーブルとして扱う時は、その名前に半角のドル記号を付加します。例えば、table01 なら table01$ とします。{{br}} SQLの記述中で、半角ドル記号をテーブル名に用いる時は、[……] で囲まないとエラーになるので、結局 [table01$] と書く必要があります。
*ワークシートの一部をテーブルとして使う時は、セルの番地を指定して [table01$A1:D10] のように書くことができます。
*ワークシートの一部(または全部)が名前付き範囲として設定されている時に、その範囲をテーブルとして使う場合は、その名前をそのまま書きます。ドル記号を付けたりしません。
*存在しないワークシートを新たに作成する時は、ドル記号を付けずに、素直に table01 のように書きます。{{br}}create table table01 (……);{{br}}のようにします。ADOで新たにテーブルを作成すると、ワークシートと併せて名前付き範囲も一緒に設けられます。この名前付き範囲は、シート全体を指し示していると思われるので、テーブル名にわざわざドル記号を付けるのが面倒なら、付けなくても問題ないと推測します。{{br}} なお、テーブル名にドル記号を付けてレコードを挿入すると、table01$ にはレコードが挿入されますが、table01 には挿入されません。一方、テーブル名にドルを付けないでレコードを挿入した場合は、table01, table01$ の両方にレコードが挿入されます。
(b) ADOでxlsファイルに接続する場合、テーブルの第１行目をフィールド名の列記とみるのか、データが書かれているとみるのか、どちらかを選択しなければなりません。{{br}}
Yadoでは、クラスオブジェクトを最初に生成するとき、{{br}}
db = Yado.new('TEST.XLS', 'admin', '', 'Yes'){{br}}のように第４引数を 'Yes' にすれば、テーブルの第１行目をフィールド名とみなします。{{br}}
'No' にすれば、第１行目からデータが書かれているものとみなします。その場合のフィールド名は、F1, F2, F3 …… となります。{{br}}
'Yes' や 'No' を省略すると 'Yes' が指定されたものとみなされます。{{br}}
なお、'Yes' の前にある 'admin', '' は、ユーザー名とパスワードの指定です。
(c) 文字列型の桁数は、指定にかかわらず 255 になります。テーブル作成時に varchar(30) としても、作成されたものをみると varchar(255) になります。要するに、桁数の指定は意味を持ちません。
(d) 半角スペースを含むテーブルの名前を使おうとすると、'_' (アンダーライン)に自動的に置き換えられるようです。Excelのワークシートの名前として半角スペースが使えるのかどうか分かりませんが、ADOのSQL命令を経由する時は使えないようです。
(e) ADOXでxlsファイルを作成する場合、必ずワークシートを１つは設けなければなりません。そのため Yado では、ダミーのワークシート Sheet1 を設けます。中身が空っぽの Sheet1 ができてしまいますが無視して下さい。
(f) xlsファイルでは、数値は総て浮動小数点型になります。テーブル作成時に整数型の int を指定しても、1.0 とか 2.0 などの float 型になります。decimal も float 型になります。{{br}}
また、固定長文字列と可変長文字列の区別がなく、可変長文字列に統合されます。{{br}}
SQL命令でテーブル作成時に指定したデータ型が、xlsファイルでは結局どの型になるかの対応関係を表５に示しておきます。
表５ Excelファイルの場合のデータ型
||!型名||!SQL指定例||!Excelでの型
||整数||smallint||float
||整数||int||float
||オートインクリメント||counter||×
||単精度浮動小数点||real||float
||倍精度浮動小数点||float||float
||通貨||money||money
||日付||date||date
||Yes｜No||yesno||yesno
||整数||tinyint||float
||グローバル識別子||guid||varchar(255)
||固定長文字列||char(35)||varchar(255)
||固定精度数値||decimal(20,4)||float
||可変長文字列||varchar(50)||varchar(255)
||長文字列||longtext||longtext
||バイナリ||binary(80)||varchar(255)
||長バイナリ||longbinary||×
(注) 「×」は、Excelの側に対応する型がない、つまり使えないことを示す。
!!(3) xlsファイルを操作するサンプル
xlsファイルを扱う場合、先に書いたようにいくつか制限や独特の仕様がありますが、SQL命令の select はかなり使えます。where の検索条件指定、order by による整列、複数のワークシートを参照しながらの処理、サブクエリ等々いろいろ可能です。
''○ 条件を満たすレコードの抽出''
一定の条件を満たすレコード群を新しいテーブルに書き出すことは、次のようなSQL命令で簡単に行えます。1990年代生まれの人を抜き出して、新しいワークシートを作成する例です(sample14.rb参照)。
select * into 新しいワークシート from [既存のワークシート$]
where 誕生日 between #1990/01/01# and #1999/12/31#;
''○ 複数のテーブルの結合''
複数のテーブルを結合することもできます。
例えば、次のようなSQL命令が実行可能です。「ID、氏名」および「ID、月あたり小遣い」の２つのテーブルを結合して、「ID、氏名、月あたり小遣い」の３項目のテーブルを作成します(sample15.rb参照)。
select [テーブル01$].ID, [テーブル01$].氏名, [テーブル02$].月あたり小遣い
into 結合テーブル
from [テーブル01$], [テーブル02$]
where [テーブル01$].ID = [テーブル02$].ID;
''○ サブクエリの例・その１''
xlsファイルの場合は、一度作ったテーブルを削除できません。一時的にテーブルを作って、用が済んだら削除するという技は使いにくい状況です。そこで、サブクエリを使うなど工夫した方がいいことになります。
サブクエリは、selectコマンドの結果を取り込んで利用する形です。
ここでも「ID、氏名」および「ID、月あたり小遣い」の２つのテーブルを用いて、小遣いが１万円未満の人のIDと氏名を出力する例を示します。sample16.rbから該当箇所を掲げます。
−−−− ここから
# ↓ 月あたり小遣いが１万円未満のIDを得るためのクエリ
sub_sql = "select ID from [金額帳$] where 月あたり小遣い < 10000"
puts '月あたり小遣いが１万円未満のIDを念のため出力'
db.query(sub_sql) {|rs| puts rs}
printf("\n")
puts 'サブクエリを利用して、月あたり小遣いが１万円未満の人のIDと氏名を出力'
sql = <<EOS3
select * from [名簿$]
where ID in (#{sub_sql});
EOS3
db.query(sql) {|rs| puts rs.join("\t")}
−−−− ここまで
sub_sqlは、小遣いが１万円未満の人のIDを得るためのSQL命令です。最終的な目的を達成するための第１ステップです。
その後の sql の方が最終目的実現のためのもので、sub_sqlで得られたIDと同じ番号の人を名簿から抽出します。他のSQL命令の実行結果をサブクエリとして内包する形になっています。
これは非常に単純な例ですが、このようなサブクエリを利用すれば、無駄に一時的なテーブルを作らずに済みます。
''○ サブクエリの例・その２(平均額との差を求める)''
サブクエリの例をもう一つ上げてみます。使うテーブルは同じです。各人の小遣いと平均額との差を算出します(sample16.rbの後半参照)。
SQLで使える関数の一つに avg があります。指定フィールドの平均額を求めるものです。他に合計の sum、最大値の max などいろいろあります。
平均額を求めるSQL命令は、具体的には次のようになります。
select Avg(月あたり小遣い) from [金額帳$];
これをサブクエリとして取り込んで、各人の小遣いと平均額との差を出すSQL命令は、次のとおりです。
select 月あたり小遣い - (select Avg(月あたり小遣い) from [金額帳$])
from [金額帳$];
似たような select の命令文が入れ湖になっているので分かりにくいですが、rubyスクリプトで書く時は、平均額を求める部分を予め変数に代入して
avg_sql = "select Avg(月あたり小遣い) from [金額帳$]"{{br}}としておいてから、
sql = "select 月あたり小遣い - (#{avg_sql}) from [金額帳$];"
db.query(sql)
{{br}}
とすれば、少しは分かりやすくなるかもしれません。
sample16.rbの後半では、こうした平均額との差を求めながら、ワークシートを結合し、「ID、氏名、月あたり小遣い、平均額との差」の４項目からなる新たなワークシートを作成しています。
集計はExcelの得意技ですが、SQLでは、複数のワークシートを参照しながらの集計が比較的簡単に行える、というのが特徴だと思います。
----
!9. Yadoの各種メソッドに関する補足
ここでは、これまであまり言及していないYadoのメソッドに関する情報を記します。
!!(1) クラスオブジェクトの生成
db = Yado.new 'TEST.MDB'{{br}}によって Yado のクラスオブジェクトが生成されますが、この時に指定できる引数は次のとおりです。
Yado.new(データベース名, ユーザー名, パスワード,
xlsの１行目の扱い, xlsの最初のワークシート名, xlsの最初のフィールド定義)
(a) データベース名: 'test.mdb' とか 'test.xls' などと指定します。それが存在しない時は新たに作成します。
(b) ユーザー名: デフォルトは 'admin' です。もし特定のユーザー名とパスワードでセキュリティがかけられたデータベースを受け取った時は指定します。
(c) パスワード: デフォルトは空文字列の '' です。
(d) xlsの１行目の扱い: 'Yes' だと１行目をフィールド名とみなし、'No' ならデータとみなします。デフォルトは 'Yes' です。
(e) xlsの最初のテーブル名: xlsファイルを新規に作成する時に設けるテーブルの名前。デフォルトは 'Sheet1' です。
(f) xlsの最初のフィールド定義: xlsファイルを新規に作成する時に設けるテーブルのフィールド定義。デフォルトは ['F1 varchar'] です。{{br}}
つまり、(e)と組み合わせて、次の形で最初のテーブルが作成されます。{{br}}
create table Sheet1 (F1 varchar);{{br}}
もし xlsファイルに作るべきテーブルの名前やフィールドが予め分かっているのであれば、それを指定します。例えば{{br}}
db = Yado.new('TEST.XLS', 'admin', '', 'Yes', '名簿',
['ID int', '氏名 varchar', '電話 varchar', '住所 varchar'])
{{br}}
のようにします。
引数を何も指定せず db = Yado.new とした場合は、データベースに接続されていないオブジェクトが生成されます。これをデータベースに接続するためには{{br}}
db.use 'TEST.MDB'{{br}}のようにします。ただし、これは TEST.MDB が既に存在している場合です。存在していないデータベースの時は{{br}}
db.create_db 'TEST.MDB'{{br}}
db.use 'TEST.MDB'{{br}}のようにします。
!!(2) データベースの作成メソッド
まだ存在していないデータベースを新たに作成する時は、
db.create_db 'TEST.MDB'{{br}}のようにします。これでデータベースが作成されますが、接続はしません。接続するためには{{br}}
db.use 'TEST.MDB'{{br}}とします。
create_db の引数は、前述の new と同じです。
その戻り値は、成功すれば 'SUCCESS'、失敗の時は 'LOSE'、既にそれが存在していたなら 'EXIST' です。
なお、この create_db でパスワード付きのセキュリティがかけられたデータベースを作ることはできません。その意味で、引数にユーザー名とパスワードがあるのはナンセンスですが、一応 new と同じ引数にしてあります。ユーザー名は 'admin'、パスワードは '' でないとエラーになります。
!!(3) データベースへの接続と切断
上に書いたように、接続の時は useメソッドを用います。open_db でも結構です。
切断する(閉じる)時は close または close_db メソッドを用います。
!!(4) データベースの削除
データベースの削除は、Windowsではファイルの削除です。Exploreなどを使って削除できます。
一応、drop_dbメソッドを設けてありますので db.drop_db 'TEST.MDB' のようにして削除できます。ただし、該当のデータベースが閉じられていないと削除できません。
!!(5) テーブルの削除
drop_tableメソッドでテーブルを削除できます。
場合によっては、「そのテーブルが使用中」のようなエラーメッセージが出て削除できないことがあります。その時は、一度データベースを閉じて、再オープンした直後に drop_table を実行すれば大丈夫だと思います。
なお、xlsファイルにおいてはテーブルの削除は行われません。drop_tableを実行すると、テーブル(多くの場合ワークシート)の中身が全部消去されますが、テーブルそのものは残ります。
!!(6) エラーメッセージに関するメソッド
Yado利用中に発生したエラーのメッセージは、配列 @YadoErrors に蓄積されます。また、最新のメッセージは、文字列 @YadoLastError に記録されます。これらエラーメッセージを管理するメソッドとして次のものがあります。
*error_stock(message): 最新のエラーメッセージを記録・蓄積する。
*error_last: 最新のエラーメッセージを文字列で返す。
*error_all: 蓄積されているエラーメッセージ全体を配列で返す。
*error_print(filename): 蓄積されているエラーメッセージをファイルに出力。
*error_switch(sw): エラーメッセージの出力の有無を切り替える。sw=0|1{{br}} error_stockメソッドでメッセージが記録される際、それが標準エラー出力に出力されます。その出力を抑制したい時は、db.error_switch(0) とします。
!!(7) ADOのバージョン
ado_versionメソットは、ADOのバージョンを文字列で返します。2.7 とか 6.0 などを返します。
本来ならクラスメソッドに含めるべきではないでしょうが、含めてあるので db.ado_version のようにして呼び出します。
----
!10. yado.rbの内部情報
!!(1) yado.rbに含まれているクラスとモジュール
yado.rbには、２つのクラスと２つのモジュールが含まれています。
*class Yadobase: ADO | ADOX を扱うための基本クラス
*module Ycsvtab: 「CSV | タブ区切りテキスト」とrubyの配列の相互変換
*module Ybin: OLEオブジェクト化されたファイルから生のバイナリを抽出
*class Yado: 上の class, module を使った応用クラス
Yadobase は、モジュール Ycsvtab, Ybin を用いていません。これだけを独立したものとして利用できます。
Yado は、Yadobase の派生クラスです。また、Ycsvtab, Ybin を用いています。
Ybin には、BMPファイルの抽出メソッドがあります。残念ながら、WordやExcelなど他のバイナリファイルには対応していません。
!!(2) テーブル、フィールド、インデックスの内部記録
Yadobase におけるテーブル、フィールド、インデックスの記録は、クラスでなく構造体で行っています。
(a) テーブルの構造体 Table_s のメンバー{{br}}
Name:名前, Type:種類(TABLE, VIEW など),{{br}}
DateCreated:作成日時, DateModified:更新日時
(b) フィールドの構造体 Field_s のメンバー{{br}}
Name:名前, Type:データ型(int, char などを数値で示す), DefinedSize:最大桁数,{{br}}
Precision:decimal型の全体の最大桁数, NumericScale:→小数部の最大桁数,{{br}}
Attributes:属性, Properties:プロパティ(各種オプション){{br}}
なお、上記 Properties は、構造体 FldProperties_s として記録します。
(c) インデックスの構造体 Indexes_s のメンバー{{br}}
Name:名前, PrimaryKey:主キーならtrue, Unique:重複禁止ならtrue,{{br}}
IndexNulls:空欄禁止なら1・許可なら0, Columns:インデックスに登録するフィールド{{br}}
なお、上記Columnsは、構造体 IdxColumns_s(Name, SortOrder)として記録します。
----
!11. ADOに関する補足
!!(1) レコードオープン時のパラメータ
yado.rbの中をみていただくと、
rs.Open sql,@current_conn,0,1{{br}}のような記述がいくつか出てきます。これは、ADODB.Recordset によってレコードを操作する時に行う記述です。その最後の２つのパラメータ 0,1 について記します。
前者の 0 は、注目レコードの移動方式(カーソルタイプ)です。前から後ろに向かって順番どおりに移すタイプか、後ろから前に戻るなど自由に移せるタイプかなどを指定するものです。次の値を指定できます。
表６ レコードセットオープン時のカーソルタイプ
||!MS定数名||!値||!説明
||adOpenForwardOnly||0||レコードセットの先頭から後方へ移動。高速に動作(既定値)
||adOpenKeyset||1||自由に移動。参照専用
||adOpenDynamic||2||自由に移動。他のユーザの更新を参照可能
||adOpenStatic||3||自由に移動。他のユーザの更新を参照不可能
後者の 1 は、ロックタイプです。レコードの更新を行う形でオープンするか否か、行う場合には他の利用者の更新との兼ね合いをどうするかなどを指定します。次の値を指定できます。
表７ レコードセットオープン時のロックタイプ
||!MS定数名||!値||!説明
||adLockReadOnly||1||読み取り専用(既定値)
||adLockPessimistic||2||排他ロックを行う
||adLockOptimistic||3||共有ロックを行う
||adLockBatchOptimistic||4||複数のレコードを一括更新処理
!!(2) インデックスを参照する形のレコード操作
主キー(PRIMARY KEY)や重複禁止(UNIQUE)以外のフィールドもインデックスに登録できます。
sample17.rbは、「身長」フィールドをインデックスに登録し、身長の低い順にレコードを取り出すサンプルです。得られる結果は、SQL命令の「select …… order by 身長」と同じになります。SQLを用いずに、ADO独自スタイルで書いたものです。細かな説明は省略します。
なお、SQL命令で「身長」フィールドをインデックスに登録するためには、下のような命令文を実行します。
create index 身長 on テーブル01 (身長);
!!(3) インデックスにおける空欄の取扱い指定
インデックスを登録する際に設定する値として、PrimaryKey, Unique, IndexNulls などがありますが、IndexNullsは、インデックスにおける空欄の取扱いを指定するものです。次のような値を取ります。
表８ インデックスにおける空欄の取扱い指定
||!MS定数名||!値||!説明
||adIndexNullsAllow||0||空欄を許可。該当欄が空欄であっても挿入される
||adIndexNullsDisallow||1||空欄を許可しない。該当欄が空欄だとエラー発生
||adIndexNullsIgnore||2||空欄だと無視。挿入されない。エラー発生しない
||adIndexNullsIgnoreAny||4||空欄があると無視。挿入されない。エラー発生しない
上の adIndexNullsIgnoreAny は、ちゃんと理解していないのですが、１つのインデックスに複数のフィールドが登録されている場合に、そのうちのどれか１つでも空欄があると無視するとの意味ではないかと思います。
!!(4) 接続文字列
yado.rbにおいて、mdbファイルに接続するための接続文字列は、定数 CnsMdb にセットしてあります。これは sprintf 用のフォーマット文字列になっており、%s のところに適当な文字列を当てはめます。
CnsMdb = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;" +
"User ID=%s;Jet OLEDB:Database Password=%s;"
実際には、ユーザー名とパスワードを使うことはあまりないので省略してかまいません。具体的な例を上げると次のようになります。
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\database\test.mdb;
Excelファイルに接続する場合は、例えば次のようにします。
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\database\test.xls;Extended Properties="Excel 8.0;HDR=Yes;"{{br}} Excel 8.0 は、Excelのバージョンを表すもので、Excel2000|2002 の形式の時にこの 8.0 にします。Excel95 なら 5.0 です。
HDR=Yes は、第１行目をフィールド名とみなすとの指定です。
接続文字列については多様な指定の仕方があります。その設定によって mysql, postgresql などへの接続も可能なようですが、試していません。
----
!12. 参考Webサイト
yado.rbを作ることができたのは、言うまでもなく ruby の開発に関わった皆さんのおかげです。ソフトウェアそのものもさることながら、豊富なオンラインドキュメントの存在は、紙の図書を読むことが難しい視覚障害の私にとって大きな助けです。rubyを利用できることに感謝します。
ここでは、rubyそのものの機能に関するもの以外の、ADO や SQL について参考にさせていただいた主なサイトを上げます。
*[[Rubyist Magazine - Win32OLE 活用法 【第 3 回】 ADODB|http://jp.rubyist.net/magazine/?0005-Win32OLE]]
*[[ActiveX Data Object : ADO入門講座 (Microsoft Access Club )|http://www.accessclub.jp/ado/]]
*[[Structured Query Language ： SQL入門講座 (25回連載)|http://www.accessclub.jp/sql/]]
*[[SQLの基礎「SELECT」文を覚えよう (28回連載の第１回)|http://www.atmarkit.co.jp/fnetwork/rensai/sql01/sql1.html]]
他にも多くのWebサイトを参考にさせていただきましたが、省略します。
〜 以上 〜
yado.rb, yado.txt のライセンスは、「GNU一般公衆利用許諾契約書(GNU General Public License)」に従います。複製や改変は基本的に自由です。著作権は T. Yoshiizumi が有します。
参考サイト： http://www.gnu.org/licenses/gpl-faq.ja.html
Copyright (C) T. Yoshiizumi, 2008-2009 All rights reserved.
----
*[[yado_guide01]]
*[[yado紹介ページ|http://cup.sakura.ne.jp/yado.htm]]
*[[トップページ|http://cup.sakura.ne.jp/index.htm]]
{{br}}
'''〜rubyによる制御〜 part ２／２'''
最終更新日:
{{toc_here}}
!7. テーブルの作成
これまでのサンプルスクリプトで、既に create table によってテーブルを作成しています。IDの番号を自動的にセットするオートインクリメントにも言及しました。しかし、テーブルの作成をめぐっては他にもいろいろな事柄があります。
ここでは、SQL命令の create table でテーブルを作成する場合のデータ型、PRIMARY KEYなどのオプション、使えない文字(半角のピリオドやスペースなどの扱い方)の話を紹介し、その後でADOXでのテーブル作成の技術的事項について説明したいと思います。
!!(1) データ型
mdbファイルでテーブルを作成する場合、主に次のようなデータ型が使えます。SQL命令で使えるデータ型を示します。
表４ SQL命令において使えるデータ型
||!型名||!SQL指定例||!説明
||整数||smallint integer2 short||2バイトの符号付き整数
||整数||int integer integer4 long||4バイトの符号付き整数
||オートインクリメント||counter(10,5) identity||自動インクリメント
||単精度浮動小数点||real Float4 ieeesingle||単精度浮動小数点値
||倍精度浮動小数点||float double float8 ieeedouble||倍精度浮動小数点値
||通貨||money||小数点以下4桁の固定小数点。8バイトの符号付き整数
||日付||date time datetime smalldatetime||倍精度浮動小数点数。整数部分は1899年12月30日からの日数
||Yes｜No||bit yesno logical||ブール値。trueまたはfalse
||整数||tinyint||1バイトの符号なし整数
||グローバル識別子||guid UNIQUEidentifier||固有のグローバル識別子
||固定長文字列||char(35) nchar||最長255文字、指定文字数未満時は半角空白を補充
||固定精度数値||decimal(20,4) dec numeric||固定精度およびスケールの正確な数値
||可変長文字列||varchar(50) nvarchar||最長255文字
||長文字列||text memo ntext note longchar longtext||最長約512メガ
||バイナリ||binary(80) varbinary||最長510バイト。指定バイト未満時は\000を補充
||長バイナリ||longbinary image general oleobject||最長約1ギガバイト
上の表で、例えば、倍精度浮動小数点のところに「float double float8 ieeedouble」とあります。この４つの記述のどれを書いても同じ倍精度浮動小数点型になります。
create table テーブル01
(身長 float,
体重 double);
{{br}}
と書いた場合、身長も体重も同じデータ型です。同じデータ型を複数の書き方で記述できるものが結構あるので注意して下さい。
以下、上に掲げた表に沿って少し注釈を加えます。
''○ 日付型の date''
日付型といっても、mdbでは日付と時刻がセットになっています。
rubyスクリプトでこの日付型の値を取り出すと、"2009/02/22 00:00:00" のように文字列で返ってきます。これを数値として取り扱いたい時は、自前でそれ用のメソッドを作るしかないのかもしれません。
''○ Yes｜No型の yesno''
Yes|No型は、「イエスかノーか」 「有るか無いか」 「真か偽か」を表現する時に用います。Accessで操作している時は「チェックあり、チェックなし」です。
rubyスクリプトでこのデータ型の値を取り出すと、true, false のどちらかです。
値をセットする時は、true, false を使うのが本来かもしれませんが、数字の 1 か 0 で与えるのも便利です。1, 0 であれば、数値、文字のどちらで与えても大丈夫です。
''○ 固定長文字列の char(35)''
char(35)とすると最大長が35文字となり、それを超えるデータを挿入しようとすると、「指定されたデータ量がフィールド サイズを超えています。」とエラーが発生して、レコードの挿入は行われません。
また、実際のデータが35文字に満たないとき、例えば20文字だった場合、残りの15文字分が半角スペースになります。
バイトではなく、あくまで文字単位です。全角も半角も同じ１文字として扱われます。
なお、文字数を指定せずに char と指定すると char(255) とみなされます。
char で扱える最大長は 255文字です。
''○ 可変長文字列の varchar''
charと異なり、データを取り出した時に末尾に余分な空白が補充されません。データを書き込んだ時と同じ状態のものを取り出せます。
それ以外は char と同じです。
''○ 固定精度数値の decimal(20,4)''
このデータ型は、小数点以下の最大桁数と整数部の最大桁数を指定したい時に用います。
decimal(20,4)とした場合、小数点以下の桁が最大で4桁、整数部分の桁が最大で20-4の16桁となります。この桁数にはマイナス記号も含まれます。
桁数を指定せずに decimal とすると、decimal(18,0) になります。decimal(12) とすれば decimal(12,0) と同じです。
このデータ型は数値ですが、rubyでは、値が文字列として得られます。取り出した値を数値として扱うためには data.to_f のように変換する必要があります。データ型の money も同じです。
''○ バイナリ型の binary(80)''
比較的容量の小さいバイナリデータを入れる時は binary を用います。
binary(80) と宣言すると、最大サイズが80バイトになり、それを超えるデータを書き込もうとするとエラーが発生して、レコードが挿入されません。
また、80バイトに満たないデータを書き込んだ場合は、満たない分だけ末尾に "\000" が補充されます。
なお、桁数を指定せずに binary と指定すると binary(510) とみなされます。
このデータ型で扱える最大長は 510バイトです。
''○ 長バイナリ型の longbinary''
このデータ型は、画像等の長いバイナリデータを収納するのに使います。最大、約１ギガバイトまで収納可能なようです。
''○ 長文字列型の longtext''
このデータ型は、長いテキストデータを収納するのに使います。最大、約512メガ文字まで収納可能だと思います。512メガ「バイト」でなく、「文字」だと思いますが、本当にそうなのか よく分かりません。
!!(2) データ型指定時のオプション
△ 取り上げるオプション： 主キーの PRIMARY KEY、重複禁止の UNIQUE、空欄禁止の NOT NULL、入力省略時規定値の DEFAULT
データ型を指定する時のオプションで、よくあるのが「PRIMARY KEY」だと思います。これは主キーといわれるもので、このオプションが指定されたフィールドにデータを書き込む時は、下に掲げる条件を満たしていないとエラーになります。
例えば、ID番号が主キーである場合、その条件は次のとおりです。
*(a) 重複禁止：同じテーブル内に同じ値のものが存在しないようにします。既にIDが4番の人のデータが登録されていれば、他に4番の人を登録できません。必ず別の番号にする必要があります。
*(b) 空欄禁止：空欄(NULL)にしておくことが認められません。必ず何か書き込まなければなりません。
これらの条件が守られていると、IDがレコードを代表する項目になります。テーブルと他のテーブルを結びつける時も、この主キーを手がかりにします。
主キーは、先に上げた２つの条件を両方とも満たすわけですが、それぞれ個別にオプション指定することができます。
*(a) 重複禁止： UNIQUE
*(b) 空欄禁止： NOT NULL
例えば、テーブルを定義する時に次のように指定します。
−−−−
create table テーブル12
(ID counter PRIMARY KEY,
氏名 varchar(15) NOT NULL,
社員番号 int UNIQUE,
担当業務 varchar(20) DEFAULT 営業,
月間売上げ money DEFAULT 0);
−−−−
「氏名」は、空欄にしておくことはできませんが、同姓同名の人がいてもちゃんと登録できます。重複しても問題ありません。
「社員番号」の方は、空欄にしておくことはできますが、他と重複するものであってはいけません。「空欄の人が何人かいても大丈夫なのか？」と疑問に思われるかもしれませんが、それは大丈夫です。
そのほか、入力がなかった時の規定値を設定することができます。
月間売上げ money DEFAULT 0{{br}}のように設定できます。「月間売上げ」として何も入力されない時は、その欄に 0 が自動的にセットされます。
担当業務 varchar(20) DEFAULT 営業{{br}}のように規定値を文字列で与える場合、シングルクォートで囲んで '営業' としても問題ないようですが、規定値にシングルクォートが含まれる形で記録されます。囲まない方がいいような気がします。
!!(3) SQL命令においてテーブル名やフィールド名に使えない文字
テーブルの作成の時にかぎりませんが、SQL命令を記述する際、テーブル名やフィールド名として使えない文字があります。
△ 要点：
*(a)半角１バイトの記号類は使わないのが無難。ただし '_' は大丈夫。
*(b)半角１バイトの英字類を使う時は [……] で囲むと良い。
ある年賀状印刷用の住所録ソフトのデータをCSVにしたところ、「No.」とか「TEL.」というフィールド名がありました。しかし、これをそのままmdbファイルに登録しようとしても拒否されます。それは、半角ピリオドがSQL命令において特別な意味を持つため、テーブル名やフィールド名として使えないからです。
半角ピリオドほどではないにしても、普通の記述方法だと拒否されるものがあります。例えば、半角スペースがそうです。仮に「book title」というフィールド名を用いたいとき、
create table テーブル01 (ID int, book title varchar(50));{{br}}と書くと、「フィールド定義の構文エラー」になります。おそらく、最初に現れる単語(空白は含まない)をフィールドの名前、それに続く単語をデータ型とみなすのが原則になっているためだと思います。
この場合、「book title」が一まとまりのものであることを示すため、ADOのSQL記述では [book title] と書きます。半角の [……] で囲みます。
create table テーブル01 (ID int, [book title] varchar(50));{{br}}とすればエラーなくテーブルを作成できます。
ただ、このやり方を採っても半角ピリオドは受けつけてもらえません。エラーになります。
半角の英語関係の記号で、[……] で囲まずに使えるのは、私が試した限りでは '_' (アンダーライン)だけでした。
基本的に、半角の記号は用いないのが無難だと思いますが、一応、私の試験結果では、[……] で囲んだとしても使えないのは次のものでした。
！：感嘆符 ．：ピリオド
［：大括弧 ］：閉じ大括弧
‘：アクサングラーブ("\140")
これ以外は、[……] で囲むと使えます。ただし、”：ダブルクォートおよび ’：シングルクォートは最初から対象外としました。
上では例としてフィールド名を上げましたが、テーブル名についても同じです。
また、半角の場合、記号でなければ大丈夫かというとそうではありません。アルファベットの単語で、そのままでは拒否されるものがあります。例えば、SQL命令として用いられる create とか insert などの予約語がそうです。
ADOのバージョンが上がるとこの辺が改善されているのかもしれませんが、ver 2.7 では「No」も駄目でした。
ただ、このアルファベットの場合は [……] で囲むことで大半が大丈夫です。
そのほか、全角文字であれば、テーブル名・フィールド名として問題なく使えるようです。
半角１バイトのカナを試してみたところ、それも大丈夫でした。
SQL命令を書いて query メソッドで実行する場合は、上の「使えない文字」に留意する必要があります。
ただし、他の Yado で定義されているメソッドを用いる場合は、かってながら「使えない文字」を削除したり、[……] で囲んだりして対応します。先に紹介した insert_records など総てそうです。それらメソッドを使う分には「使えない文字」をあまり意識しなくて大丈夫です。
!!(4) ADOXによるテーブルの作成(SQL命令に依らない方法)
少し技術的な話になりますが、ADOXでテーブルを作成する時の手順について書きます。
SQL命令の create table のスタイルで書くと下のようになるケースをADOX独自スタイルで書いてみたのが sample13.rb です。なお、SQLで書いたものを参考まで sample12.rb として付属させてあります。
−−−−
create table テーブル13
(ID counter PRIMARY KEY,
氏名 varchar(15) NOT NULL,
今期出勤比率 decimal(5,2) NOT NULL,
所属部署 varchar(30) DEFAULT 営業部,
今期売上げ money DEFAULT 0,
自社株所有組合番号 int UNIQUE);
−−−−
その sample13.rb のポイントについて書きます。
''○ データ型を示す定数の使用''
Visual BASIC などでは、データ型を指定するのに adInteger とか adVarWChar などの定数を使えます。
実は、rubyでもそれら定数を使うことができます。
ただ、rubyでは定数名の最初の文字は大文字にする規則なので、AdInteger のように書きます。
また、いきなり使えるわけではなく、使えるようにするための手続きが必要です。下に定数の値を表示する簡単なスクリプトを掲げておきます。
−−−−
require 'win32ole'
module Adoc # 定数を収納するためのモジュール(空っぽでかまわない)
end
cn = WIN32OLE.new("ADODB.Connection")
WIN32OLE.const_load(cn, Adoc)
include Adoc
puts AdInteger # → 3
puts AdVarWChar # → 202
puts Adoc.constants # 定数名の一覧を表示
−−−−
sample13.rbでは、上記の定数群を取り込んで使っています。
なお、Yadoではこの定数群を導入していません。
''○ テーブル、カラム(フィールド)、インデックスの３つのオブジェクト''
ADOXでテーブルを定義する時は
−−−−
tbl = WIN32OLE.new("ADOX.Table")
col = WIN32OLE.new("ADOX.Column")
idx = WIN32OLE.new("ADOX.Index")
−−−−
の三つのオブジェクトを用います。
フィールドの定義には col を、インデックスの定義には idx をそれぞれ必要な個数だけ使います。それらをテーブルを示す tbl にセットします。
インデックスは、主キー(PRIMARY KEY)や重複禁止(UNIQUE)の指定に用います。そうしたものがなければ省略してかまいません。
横道に逸れますが、インデックスをなぜ設けるのかについて少し書きます。
データベースの利用者がレコードを挿入しようとする際、重複禁止のフィールドに重複データをセットしようとすると、エラーが発生して挿入できません。それは、システムの側が重複かどうかをチェックしているから可能になることです。
このシステム側のチェッくが、既存のレコード１つづつとの照合で行っているとすれば、レコード数が多くなるのに比例してチェックに要する時間も増えます。レコードの挿入にどんどん時間がかかるようになってしまいます。
それを防ぐため、インデックス(索引)を設けて、重複チェックを効率よく行えるようにするわけです。そこで、重複禁止のフィールドである PRIMARY KEY, UNIQUE のフィールドをインデックスに登録しておきます。
インデックスに登録できるのは、PRIMARY KEY と UNIQUE に限りません。それ以外のフィールドであっても、よく手がかりにするフィールドをインデックスに登録しておけば、スピーディーに探索できることになります。
''○ カラム(フィールド)の定義''
フィールドの定義では、その名前・データ型の２つ、あるいは、名前・データ型・桁数の３つで足りる場合は、わざわざ col を使わず、直接
−−−−
tbl.Columns.Append 'ID', 3 # → ID int に相当する。
tbl.Columns.Append '氏名', 202, 50 # → 氏名 varchar(50) に相当する。
−−−−
{{br}}
のように直接 Append を呼び出してtblにセットできます。
しかし、decimal(20,4) の場合は、フィールドの名前・データ型・その他２つの桁数の、合計４つのパラメータになります。このような時は、col に４つのパラメータをセットしてから{{br}}
tbl.Columns.Append col{{br}}とする必要があります。
オートインクリメントの counter(10,5) の場合も col を設定する必要があります。下にその該当部分を示します。
−−−−
col = WIN32OLE.new("ADOX.Column")
col.Name = 'ID'
col.Type = AdInteger
col.ParentCatalog = cat
col.Properties("Autoincrement").Value = true
col.Properties("Seed").Value = 1 # 初期値
col.Properties("Increment").Value = 1 # 増分値
tbl.Columns.Append col
−−−−
「col.ParentCatalog = cat」の意味はよく分かりませんが、Properties("……")の値をセットする時は、これを設定しておかないとエラーになるようです。
''○ フィールドの NOT NULL および DEFAULT オプションの指定''
ADOXでのフィールド定義では、特に指定しないかぎり、その欄が空欄禁止になります。SQL命令における NOT NULL オプションが指定された状態です。SQLとは逆で、空欄を許可したい時に意図的に次の設定を行います。{{br}}
col.Properties("NULLable").Value = true
次に、規定値のDEFAULTを設定する場合は次のようにします。{{br}}
col.Properties("Default").Value = "営業部"
なお、Properties("Jet OLEDB:Allow Zero Length") というのもあります。長さ０を許可する時は true に、許可しない時は false にします。
最初、これがSQLの NOT NULL に連動しているのではないかと思いました。しかし、あれこれ試してみると違うようです。入力を省略する(NULL)ことと、「""」のような長さ０の文字列をセットすること、この２つは別物という考え方なのだろうと推測します。
この辺の仕様は、正直なところ よく分かりません。SQLの NOT NULL に対応するのが Properties("NULLable") であるらしいというのは、私のあれこれの試験の結果から得た感触です。本当にそれで間違いないのかと言われると、「たぶん」という程度です。
他に col.Attributes というのがあります。これが取り得る値は、基本的に adColFixed(数値の1)または adColNullable(数値の2)ですが、この２つの組合せ 0〜3 のどれかになるようです。
adColNullable という定数名からして、空欄許可に関連するのではないかと思い、col.Attributes が2以上であることと、col.Properties("Nullable").Value が true であることとが等価であるか調べたところ、Attributes が 1 であっても Properties の方が true であるケースがいくつかみつかりました。なので、私の単純な推測は間違いということになります。
また、col.Attributes に値をセットしようとするとエラーが発生することが結構あって、確実に値をセットするための方法が分かりません。なので Yado では col.Properties("Nullable") の方に着目することにしました。
''○ インデックスの定義''
PRIMARY KEY と UNIQUE の指定はインデックスに記録します。
−−−−
idx.PrimaryKey = true
idx.Unique = true
idx.IndexNULLs = 1 # インデックスにおける空欄禁止
−−−−
{{br}}
とすると、SQL命令において PRIMARY KEY を指定したことに相当します。
−−−−
idx.PrimaryKey = false
idx.Unique = true
idx.IndexNULLs = 0 # インデックスにおける空欄許可
−−−−
{{br}}
とした場合は、UNIQUE を指定したことに相当します。
インデックスの名前 idx.Name は、主キーであれば PrimaryKey にするようです。それ以外はフィールド名と同じでいいと思います。この辺について、何か規則があるのかどうかよく分かりませんが、Accessに付属の Northwind.mdb ではそうなっています。
また、これとは別に idx.Columns.Append によって、該当するフィールドの名前を登録します。サンプルの場合、主キーでは「ID」、UNIQUEでは「自社株所有組合番号」を登録します。
そして、１つのインデックスを設定し終える度に
tbl.Indexes.Append idx{{br}}を実行して、インデックスをテーブルにセットします。
''○ 最後の登録処理''
このように、フィールド col と、インデックス idx を必要な個数だけtblにセットしてから、最後に
cat.Tables.Append tbl{{br}}とすればテーブルの定義が完了です。
!!［補足］ Propertiesメソッドのパラメータと値
Yadoでは、Propertiesメソッドが持つパラメータの一部にしか着目していません。Autoincrement関係、Nullable、Default くらいです。しかし、それ以外にもいろいろあります。私が試したところでは''15個のパラメータ''があります(注)。
残念ながら、各々のパラメータとその値の意味が私にはよく分かりませんが、それらを表示させることは可能です。そのサンプル・スクリプトを下に掲げます。
「TEST.MDB」というデータベースと、その中に「テーブル01」という簡単なテーブルを設け、そのPropertiesを表示するものです。
dbnameという変数にデータベース名を代入すれば、それに含まれる標準テーブル総てについて Properties の情報を表示します。
−−−− ここから
# Propertiesメソッドのパラメータと値を表示するサンプル
$KCODE = 'S'
require 'yado'
dbname = "TEST.MDB"
db = Yado.new dbname
tblname = "テーブル01"
sql = "create table #{tblname} (可変長文字列 varchar(30) NOT NULL," +
"固定長文字列 char(5) DEFAULT あいうえお," +
"オートインクリメント counter(100,10));"
db.query(sql)
db.close
cn = WIN32OLE.new("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=#{dbname};"
cat = WIN32OLE.new("ADOX.Catalog")
cat.ActiveConnection = cn
puts 'データベース名：' + dbname
cat.Tables.each {|tbl|
next unless tbl.Type == 'TABLE' # 標準テーブル以外はスキップ
puts 'テーブル名：' + tbl.Name
tbl.Columns.each {|col|
puts 'フィールド名：' + col.Name
col.Properties.each {|prop|
p [prop.Name, prop.Value]
}
printf("\n")
}
}
cn.Close
−−−− ここまで
上のスクリプトは、rubyのeachメソッドを使っているので Visual BASIC などには置き換えにくいかもしれません。eachを使わずに書くと下のようになります(該当箇所のみ)。
−−−−
（前略）
for tbl_i in 0..(cat.Tables.Count-1)
tbl = cat.Tables.Item(tbl_i)
if tbl.Type != 'TABLE' # 標準テーブル以外はスキップ
next
end
puts 'テーブル名：' + tbl.Name
for col_i in 0..(tbl.Columns.Count-1)
col = tbl.Columns.Item(col_i)
puts 'フィールド名：' + col.Name
for i in 0..(col.Properties.Count-1)
p [col.Properties.Item(i).Name, col.Properties.Item(i).Value]
end
printf("\n")
end
end
cn.Close
−−−−
(注) Propertiesのパラメータとして使える文字列は、下の15個です。これらを使う時は、アルファベットの大文字・小文字の区別も行うのが無難だと思います。
−−−−
Autoincrement
Default
Description
Nullable
Fixed Length
Seed
Increment
Jet OLEDB:Column Validation Text
Jet OLEDB:Column Validation Rule
Jet OLEDB:IISAM Not Last Column
Jet OLEDB:AutoGenerate
Jet OLEDB:One BLOB per Page
Jet OLEDB:Compressed UNICODE Strings
Jet OLEDB:Allow Zero Length
Jet OLEDB:Hyperlink
−−−−
以上、ADOXの独自スタイルでテーブルを作成する方法について書きましたが、これは、Propertiesを駆使した細かな設定が可能とはいえ、かなり面倒です。SQLで行う方がずっと楽だというのが率直な感想です。
----
!8. Excelファイルを扱う時の注意点およびサンプル
YadoでExcelファイルを扱う場合は、データベースの名前として TEST.XLS のように、拡張子 .xls のファイルを指定します。
db = Yado.new "TEST.XLS"{{br}}のようにすると、dbオブジェクトを用いてExcelファイルを操作できます。
ただし、Accessのmdbファイルと異なり、いくつか制限があります。また、独特の仕様もあります。
!!(1) xlsファイルを扱う時の制限事項
xlsファイルを扱う場合にどのような制限があるか、十分には把握していませんが、次のような点があるようです。いずれも ADO ver 2.7 で確認しました。
(a) テーブルを作成するとき、PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT といったオプションを指定することができません。{{br}}
主キーを指定できないので、例えば、Yadoの update_records メソッド(主キーを手がかりにするレコードの更新)を使うことができません。update_pair の方は大丈夫です。
(b) オートインクリメント機能を使うことができません。テーブル作成時に、データ型として counter を指定できません。{{br}}
前述のオプション指定が行えないことと考え併せると、columns.Properties("……") に関わる設定が行えないのだと思います。
(c) 長いバイナリを扱うためのデータ型 longbinary を使うことができません。{{br}}
また、短いバイナリ binary 型の方は、結局、可変長文字列の varchar 型になってしまいます。xlsファイルではバイナリの処理を避けるのが無難です。
(d) レコードを削除しようとすると、「この ISAM では、リンク テーブル内のデータを削除することはできません。」とエラーが発生し、レコードの削除を行えません。{{br}}
SQL命令の delete も、ADODB.Recordset の rs.Delete も、どちらも実行できません。
(e) テーブル削除の DROP TABLE を実行しても、テーブルは削除されません。ただし、テーブルの中身は消去されます。
(f) 仮装テーブルの VIEW は使えません。「この操作は、このタイプのオブジェクトには実行できません。」となります。
ざっと確認しただけですが、以上のような制限があります。
!!(2) xlsファイルに関する独特の仕様
ADOでxlsファイルを扱う場合、以下のような独特の仕様があるようです。
(a) 扱うテーブルの名前を書き時に注意が必要です。
*既に存在しているワークシート全体をテーブルとして扱う時は、その名前に半角のドル記号を付加します。例えば、table01 なら table01$ とします。{{br}} SQLの記述中で、半角ドル記号をテーブル名に用いる時は、[……] で囲まないとエラーになるので、結局 [table01$] と書く必要があります。
*ワークシートの一部をテーブルとして使う時は、セルの番地を指定して [table01$A1:D10] のように書くことができます。
*ワークシートの一部(または全部)が名前付き範囲として設定されている時に、その範囲をテーブルとして使う場合は、その名前をそのまま書きます。ドル記号を付けたりしません。
*存在しないワークシートを新たに作成する時は、ドル記号を付けずに、素直に table01 のように書きます。{{br}}create table table01 (……);{{br}}のようにします。ADOで新たにテーブルを作成すると、ワークシートと併せて名前付き範囲も一緒に設けられます。この名前付き範囲は、シート全体を指し示していると思われるので、テーブル名にわざわざドル記号を付けるのが面倒なら、付けなくても問題ないと推測します。{{br}} なお、テーブル名にドル記号を付けてレコードを挿入すると、table01$ にはレコードが挿入されますが、table01 には挿入されません。一方、テーブル名にドルを付けないでレコードを挿入した場合は、table01, table01$ の両方にレコードが挿入されます。
(b) ADOでxlsファイルに接続する場合、テーブルの第１行目をフィールド名の列記とみるのか、データが書かれているとみるのか、どちらかを選択しなければなりません。{{br}}
Yadoでは、クラスオブジェクトを最初に生成するとき、{{br}}
db = Yado.new('TEST.XLS', 'admin', '', 'Yes'){{br}}のように第４引数を 'Yes' にすれば、テーブルの第１行目をフィールド名とみなします。{{br}}
'No' にすれば、第１行目からデータが書かれているものとみなします。その場合のフィールド名は、F1, F2, F3 …… となります。{{br}}
'Yes' や 'No' を省略すると 'Yes' が指定されたものとみなされます。{{br}}
なお、'Yes' の前にある 'admin', '' は、ユーザー名とパスワードの指定です。
(c) 文字列型の桁数は、指定にかかわらず 255 になります。テーブル作成時に varchar(30) としても、作成されたものをみると varchar(255) になります。要するに、桁数の指定は意味を持ちません。
(d) 半角スペースを含むテーブルの名前を使おうとすると、'_' (アンダーライン)に自動的に置き換えられるようです。Excelのワークシートの名前として半角スペースが使えるのかどうか分かりませんが、ADOのSQL命令を経由する時は使えないようです。
(e) ADOXでxlsファイルを作成する場合、必ずワークシートを１つは設けなければなりません。そのため Yado では、ダミーのワークシート Sheet1 を設けます。中身が空っぽの Sheet1 ができてしまいますが無視して下さい。
(f) xlsファイルでは、数値は総て浮動小数点型になります。テーブル作成時に整数型の int を指定しても、1.0 とか 2.0 などの float 型になります。decimal も float 型になります。{{br}}
また、固定長文字列と可変長文字列の区別がなく、可変長文字列に統合されます。{{br}}
SQL命令でテーブル作成時に指定したデータ型が、xlsファイルでは結局どの型になるかの対応関係を表５に示しておきます。
表５ Excelファイルの場合のデータ型
||!型名||!SQL指定例||!Excelでの型
||整数||smallint||float
||整数||int||float
||オートインクリメント||counter||×
||単精度浮動小数点||real||float
||倍精度浮動小数点||float||float
||通貨||money||money
||日付||date||date
||Yes｜No||yesno||yesno
||整数||tinyint||float
||グローバル識別子||guid||varchar(255)
||固定長文字列||char(35)||varchar(255)
||固定精度数値||decimal(20,4)||float
||可変長文字列||varchar(50)||varchar(255)
||長文字列||longtext||longtext
||バイナリ||binary(80)||varchar(255)
||長バイナリ||longbinary||×
(注) 「×」は、Excelの側に対応する型がない、つまり使えないことを示す。
!!(3) xlsファイルを操作するサンプル
xlsファイルを扱う場合、先に書いたようにいくつか制限や独特の仕様がありますが、SQL命令の select はかなり使えます。where の検索条件指定、order by による整列、複数のワークシートを参照しながらの処理、サブクエリ等々いろいろ可能です。
''○ 条件を満たすレコードの抽出''
一定の条件を満たすレコード群を新しいテーブルに書き出すことは、次のようなSQL命令で簡単に行えます。1990年代生まれの人を抜き出して、新しいワークシートを作成する例です(sample14.rb参照)。
select * into 新しいワークシート from [既存のワークシート$]
where 誕生日 between #1990/01/01# and #1999/12/31#;
''○ 複数のテーブルの結合''
複数のテーブルを結合することもできます。
例えば、次のようなSQL命令が実行可能です。「ID、氏名」および「ID、月あたり小遣い」の２つのテーブルを結合して、「ID、氏名、月あたり小遣い」の３項目のテーブルを作成します(sample15.rb参照)。
select [テーブル01$].ID, [テーブル01$].氏名, [テーブル02$].月あたり小遣い
into 結合テーブル
from [テーブル01$], [テーブル02$]
where [テーブル01$].ID = [テーブル02$].ID;
''○ サブクエリの例・その１''
xlsファイルの場合は、一度作ったテーブルを削除できません。一時的にテーブルを作って、用が済んだら削除するという技は使いにくい状況です。そこで、サブクエリを使うなど工夫した方がいいことになります。
サブクエリは、selectコマンドの結果を取り込んで利用する形です。
ここでも「ID、氏名」および「ID、月あたり小遣い」の２つのテーブルを用いて、小遣いが１万円未満の人のIDと氏名を出力する例を示します。sample16.rbから該当箇所を掲げます。
−−−− ここから
# ↓ 月あたり小遣いが１万円未満のIDを得るためのクエリ
sub_sql = "select ID from [金額帳$] where 月あたり小遣い < 10000"
puts '月あたり小遣いが１万円未満のIDを念のため出力'
db.query(sub_sql) {|rs| puts rs}
printf("\n")
puts 'サブクエリを利用して、月あたり小遣いが１万円未満の人のIDと氏名を出力'
sql = <<EOS3
select * from [名簿$]
where ID in (#{sub_sql});
EOS3
db.query(sql) {|rs| puts rs.join("\t")}
−−−− ここまで
sub_sqlは、小遣いが１万円未満の人のIDを得るためのSQL命令です。最終的な目的を達成するための第１ステップです。
その後の sql の方が最終目的実現のためのもので、sub_sqlで得られたIDと同じ番号の人を名簿から抽出します。他のSQL命令の実行結果をサブクエリとして内包する形になっています。
これは非常に単純な例ですが、このようなサブクエリを利用すれば、無駄に一時的なテーブルを作らずに済みます。
''○ サブクエリの例・その２(平均額との差を求める)''
サブクエリの例をもう一つ上げてみます。使うテーブルは同じです。各人の小遣いと平均額との差を算出します(sample16.rbの後半参照)。
SQLで使える関数の一つに avg があります。指定フィールドの平均額を求めるものです。他に合計の sum、最大値の max などいろいろあります。
平均額を求めるSQL命令は、具体的には次のようになります。
select Avg(月あたり小遣い) from [金額帳$];
これをサブクエリとして取り込んで、各人の小遣いと平均額との差を出すSQL命令は、次のとおりです。
select 月あたり小遣い - (select Avg(月あたり小遣い) from [金額帳$])
from [金額帳$];
似たような select の命令文が入れ湖になっているので分かりにくいですが、rubyスクリプトで書く時は、平均額を求める部分を予め変数に代入して
avg_sql = "select Avg(月あたり小遣い) from [金額帳$]"{{br}}としておいてから、
sql = "select 月あたり小遣い - (#{avg_sql}) from [金額帳$];"
db.query(sql)
{{br}}
とすれば、少しは分かりやすくなるかもしれません。
sample16.rbの後半では、こうした平均額との差を求めながら、ワークシートを結合し、「ID、氏名、月あたり小遣い、平均額との差」の４項目からなる新たなワークシートを作成しています。
集計はExcelの得意技ですが、SQLでは、複数のワークシートを参照しながらの集計が比較的簡単に行える、というのが特徴だと思います。
----
!9. Yadoの各種メソッドに関する補足
ここでは、これまであまり言及していないYadoのメソッドに関する情報を記します。
!!(1) クラスオブジェクトの生成
db = Yado.new 'TEST.MDB'{{br}}によって Yado のクラスオブジェクトが生成されますが、この時に指定できる引数は次のとおりです。
Yado.new(データベース名, ユーザー名, パスワード,
xlsの１行目の扱い, xlsの最初のワークシート名, xlsの最初のフィールド定義)
(a) データベース名: 'test.mdb' とか 'test.xls' などと指定します。それが存在しない時は新たに作成します。
(b) ユーザー名: デフォルトは 'admin' です。もし特定のユーザー名とパスワードでセキュリティがかけられたデータベースを受け取った時は指定します。
(c) パスワード: デフォルトは空文字列の '' です。
(d) xlsの１行目の扱い: 'Yes' だと１行目をフィールド名とみなし、'No' ならデータとみなします。デフォルトは 'Yes' です。
(e) xlsの最初のテーブル名: xlsファイルを新規に作成する時に設けるテーブルの名前。デフォルトは 'Sheet1' です。
(f) xlsの最初のフィールド定義: xlsファイルを新規に作成する時に設けるテーブルのフィールド定義。デフォルトは ['F1 varchar'] です。{{br}}
つまり、(e)と組み合わせて、次の形で最初のテーブルが作成されます。{{br}}
create table Sheet1 (F1 varchar);{{br}}
もし xlsファイルに作るべきテーブルの名前やフィールドが予め分かっているのであれば、それを指定します。例えば{{br}}
db = Yado.new('TEST.XLS', 'admin', '', 'Yes', '名簿',
['ID int', '氏名 varchar', '電話 varchar', '住所 varchar'])
{{br}}
のようにします。
引数を何も指定せず db = Yado.new とした場合は、データベースに接続されていないオブジェクトが生成されます。これをデータベースに接続するためには{{br}}
db.use 'TEST.MDB'{{br}}のようにします。ただし、これは TEST.MDB が既に存在している場合です。存在していないデータベースの時は{{br}}
db.create_db 'TEST.MDB'{{br}}
db.use 'TEST.MDB'{{br}}のようにします。
!!(2) データベースの作成メソッド
まだ存在していないデータベースを新たに作成する時は、
db.create_db 'TEST.MDB'{{br}}のようにします。これでデータベースが作成されますが、接続はしません。接続するためには{{br}}
db.use 'TEST.MDB'{{br}}とします。
create_db の引数は、前述の new と同じです。
その戻り値は、成功すれば 'SUCCESS'、失敗の時は 'LOSE'、既にそれが存在していたなら 'EXIST' です。
なお、この create_db でパスワード付きのセキュリティがかけられたデータベースを作ることはできません。その意味で、引数にユーザー名とパスワードがあるのはナンセンスですが、一応 new と同じ引数にしてあります。ユーザー名は 'admin'、パスワードは '' でないとエラーになります。
!!(3) データベースへの接続と切断
上に書いたように、接続の時は useメソッドを用います。open_db でも結構です。
切断する(閉じる)時は close または close_db メソッドを用います。
!!(4) データベースの削除
データベースの削除は、Windowsではファイルの削除です。Exploreなどを使って削除できます。
一応、drop_dbメソッドを設けてありますので db.drop_db 'TEST.MDB' のようにして削除できます。ただし、該当のデータベースが閉じられていないと削除できません。
!!(5) テーブルの削除
drop_tableメソッドでテーブルを削除できます。
場合によっては、「そのテーブルが使用中」のようなエラーメッセージが出て削除できないことがあります。その時は、一度データベースを閉じて、再オープンした直後に drop_table を実行すれば大丈夫だと思います。
なお、xlsファイルにおいてはテーブルの削除は行われません。drop_tableを実行すると、テーブル(多くの場合ワークシート)の中身が全部消去されますが、テーブルそのものは残ります。
!!(6) エラーメッセージに関するメソッド
Yado利用中に発生したエラーのメッセージは、配列 @YadoErrors に蓄積されます。また、最新のメッセージは、文字列 @YadoLastError に記録されます。これらエラーメッセージを管理するメソッドとして次のものがあります。
*error_stock(message): 最新のエラーメッセージを記録・蓄積する。
*error_last: 最新のエラーメッセージを文字列で返す。
*error_all: 蓄積されているエラーメッセージ全体を配列で返す。
*error_print(filename): 蓄積されているエラーメッセージをファイルに出力。
*error_switch(sw): エラーメッセージの出力の有無を切り替える。sw=0|1{{br}} error_stockメソッドでメッセージが記録される際、それが標準エラー出力に出力されます。その出力を抑制したい時は、db.error_switch(0) とします。
!!(7) ADOのバージョン
ado_versionメソットは、ADOのバージョンを文字列で返します。2.7 とか 6.0 などを返します。
本来ならクラスメソッドに含めるべきではないでしょうが、含めてあるので db.ado_version のようにして呼び出します。
----
!10. yado.rbの内部情報
!!(1) yado.rbに含まれているクラスとモジュール
yado.rbには、２つのクラスと２つのモジュールが含まれています。
*class Yadobase: ADO | ADOX を扱うための基本クラス
*module Ycsvtab: 「CSV | タブ区切りテキスト」とrubyの配列の相互変換
*module Ybin: OLEオブジェクト化されたファイルから生のバイナリを抽出
*class Yado: 上の class, module を使った応用クラス
Yadobase は、モジュール Ycsvtab, Ybin を用いていません。これだけを独立したものとして利用できます。
Yado は、Yadobase の派生クラスです。また、Ycsvtab, Ybin を用いています。
Ybin には、BMPファイルの抽出メソッドがあります。残念ながら、WordやExcelなど他のバイナリファイルには対応していません。
!!(2) テーブル、フィールド、インデックスの内部記録
Yadobase におけるテーブル、フィールド、インデックスの記録は、クラスでなく構造体で行っています。
(a) テーブルの構造体 Table_s のメンバー{{br}}
Name:名前, Type:種類(TABLE, VIEW など),{{br}}
DateCreated:作成日時, DateModified:更新日時
(b) フィールドの構造体 Field_s のメンバー{{br}}
Name:名前, Type:データ型(int, char などを数値で示す), DefinedSize:最大桁数,{{br}}
Precision:decimal型の全体の最大桁数, NumericScale:→小数部の最大桁数,{{br}}
Attributes:属性, Properties:プロパティ(各種オプション){{br}}
なお、上記 Properties は、構造体 FldProperties_s として記録します。
(c) インデックスの構造体 Indexes_s のメンバー{{br}}
Name:名前, PrimaryKey:主キーならtrue, Unique:重複禁止ならtrue,{{br}}
IndexNulls:空欄禁止なら1・許可なら0, Columns:インデックスに登録するフィールド{{br}}
なお、上記Columnsは、構造体 IdxColumns_s(Name, SortOrder)として記録します。
----
!11. ADOに関する補足
!!(1) レコードオープン時のパラメータ
yado.rbの中をみていただくと、
rs.Open sql,@current_conn,0,1{{br}}のような記述がいくつか出てきます。これは、ADODB.Recordset によってレコードを操作する時に行う記述です。その最後の２つのパラメータ 0,1 について記します。
前者の 0 は、注目レコードの移動方式(カーソルタイプ)です。前から後ろに向かって順番どおりに移すタイプか、後ろから前に戻るなど自由に移せるタイプかなどを指定するものです。次の値を指定できます。
表６ レコードセットオープン時のカーソルタイプ
||!MS定数名||!値||!説明
||adOpenForwardOnly||0||レコードセットの先頭から後方へ移動。高速に動作(既定値)
||adOpenKeyset||1||自由に移動。参照専用
||adOpenDynamic||2||自由に移動。他のユーザの更新を参照可能
||adOpenStatic||3||自由に移動。他のユーザの更新を参照不可能
後者の 1 は、ロックタイプです。レコードの更新を行う形でオープンするか否か、行う場合には他の利用者の更新との兼ね合いをどうするかなどを指定します。次の値を指定できます。
表７ レコードセットオープン時のロックタイプ
||!MS定数名||!値||!説明
||adLockReadOnly||1||読み取り専用(既定値)
||adLockPessimistic||2||排他ロックを行う
||adLockOptimistic||3||共有ロックを行う
||adLockBatchOptimistic||4||複数のレコードを一括更新処理
!!(2) インデックスを参照する形のレコード操作
主キー(PRIMARY KEY)や重複禁止(UNIQUE)以外のフィールドもインデックスに登録できます。
sample17.rbは、「身長」フィールドをインデックスに登録し、身長の低い順にレコードを取り出すサンプルです。得られる結果は、SQL命令の「select …… order by 身長」と同じになります。SQLを用いずに、ADO独自スタイルで書いたものです。細かな説明は省略します。
なお、SQL命令で「身長」フィールドをインデックスに登録するためには、下のような命令文を実行します。
create index 身長 on テーブル01 (身長);
!!(3) インデックスにおける空欄の取扱い指定
インデックスを登録する際に設定する値として、PrimaryKey, Unique, IndexNulls などがありますが、IndexNullsは、インデックスにおける空欄の取扱いを指定するものです。次のような値を取ります。
表８ インデックスにおける空欄の取扱い指定
||!MS定数名||!値||!説明
||adIndexNullsAllow||0||空欄を許可。該当欄が空欄であっても挿入される
||adIndexNullsDisallow||1||空欄を許可しない。該当欄が空欄だとエラー発生
||adIndexNullsIgnore||2||空欄だと無視。挿入されない。エラー発生しない
||adIndexNullsIgnoreAny||4||空欄があると無視。挿入されない。エラー発生しない
上の adIndexNullsIgnoreAny は、ちゃんと理解していないのですが、１つのインデックスに複数のフィールドが登録されている場合に、そのうちのどれか１つでも空欄があると無視するとの意味ではないかと思います。
!!(4) 接続文字列
yado.rbにおいて、mdbファイルに接続するための接続文字列は、定数 CnsMdb にセットしてあります。これは sprintf 用のフォーマット文字列になっており、%s のところに適当な文字列を当てはめます。
CnsMdb = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;" +
"User ID=%s;Jet OLEDB:Database Password=%s;"
実際には、ユーザー名とパスワードを使うことはあまりないので省略してかまいません。具体的な例を上げると次のようになります。
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\database\test.mdb;
Excelファイルに接続する場合は、例えば次のようにします。
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\database\test.xls;Extended Properties="Excel 8.0;HDR=Yes;"{{br}} Excel 8.0 は、Excelのバージョンを表すもので、Excel2000|2002 の形式の時にこの 8.0 にします。Excel95 なら 5.0 です。
HDR=Yes は、第１行目をフィールド名とみなすとの指定です。
接続文字列については多様な指定の仕方があります。その設定によって mysql, postgresql などへの接続も可能なようですが、試していません。
----
!12. 参考Webサイト
yado.rbを作ることができたのは、言うまでもなく ruby の開発に関わった皆さんのおかげです。ソフトウェアそのものもさることながら、豊富なオンラインドキュメントの存在は、紙の図書を読むことが難しい視覚障害の私にとって大きな助けです。rubyを利用できることに感謝します。
ここでは、rubyそのものの機能に関するもの以外の、ADO や SQL について参考にさせていただいた主なサイトを上げます。
*[[Rubyist Magazine - Win32OLE 活用法 【第 3 回】 ADODB|http://jp.rubyist.net/magazine/?0005-Win32OLE]]
*[[ActiveX Data Object : ADO入門講座 (Microsoft Access Club )|http://www.accessclub.jp/ado/]]
*[[Structured Query Language ： SQL入門講座 (25回連載)|http://www.accessclub.jp/sql/]]
*[[SQLの基礎「SELECT」文を覚えよう (28回連載の第１回)|http://www.atmarkit.co.jp/fnetwork/rensai/sql01/sql1.html]]
他にも多くのWebサイトを参考にさせていただきましたが、省略します。
〜 以上 〜
yado.rb, yado.txt のライセンスは、「GNU一般公衆利用許諾契約書(GNU General Public License)」に従います。複製や改変は基本的に自由です。著作権は T. Yoshiizumi が有します。
参考サイト： http://www.gnu.org/licenses/gpl-faq.ja.html
Copyright (C) T. Yoshiizumi, 2008-2009 All rights reserved.
----
*[[yado_guide01]]
*[[yado紹介ページ|http://cup.sakura.ne.jp/yado.htm]]
*[[トップページ|http://cup.sakura.ne.jp/index.htm]]