ADO
ADO(ActiveX Data Object)メモの目次
使用しているADOのバージョンは?
ConnectionオブジェクトのVersionプロパティの値を参照します。以下を拡張子.vbsの適当なファイルに保存してダブルクリックしてください。
Option Explicit ' adover.vbs - 使用しているADOのバージョンを調べる Dim objCon Set objCon = CreateObject("ADODB.Connection") WScript.Echo "ADO Ver." & objCon.Version Set objCon = Nothing
DBへの接続
一番お手軽な VBS+MDBでの接続例
Dim objCon Set objCon = WScript.CreateObject("ADODB.Connection") With objCon .Provider = "Microsoft.Jet.OLEDB.4.0" .Open "Northwind.mdb" End With
こんな感じで接続できます。接続されると.ldbというテンポラリファイルができるので確認してみましょう。
ADOXでMDBを作成する
日常の開発でこれが必要な状況ですが、
- プログラム内でどうしてもMDBを生成させる必要がある時
- Accessを持っていないがMDBを利用したい時
くらいでしょうか。私は後者の事情でたまに利用しますが、普段はあまり必要性は感じません。さておき、サンプルコードです。Catalogオブジェクトを使います。
Dim objCat Set objCat = CreateObject("ADOX.Catalog") objCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=hoge.mdb;" Set objCat = Nothing
これだけです。この例ではカレントにhoge.mdbを作ります。Data Sourceで絶対パスを指定してもいいし、相対アドレスでもOKです。
テーブル作成
上記で作成したmdbにテーブルはありませんので、テーブルも生成してあげる必要がありますよね。幾通りかやり方がありますが、ADOXで作成する方法をとりあえず書いておきます。上で作成したhoge.mdbにTサンプルというテーブルを作成することを想定しています。
Const adVarWChar = 202 Dim objCat, objTbl Set objCat = CreateObject("ADOX.Catalog") objCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=hoge.mdb;" Set objTbl = CreateObject("ADOX.Table") objTbl.Name = "Tサンプル" With objTbl.Columns .Append "本名", adVarWChar .Append "ふりがな", adVarWChar .Append "性別", adVarWChar End With objCat.Tables.Append objTbl Set objTbl = Nothing Set objCat = Nothing
オートナンバー型フィールドの作成
オートナンバー型フィールドを作るのは少々面倒です。ADOX.Column でオートナンバー用のフィールドを定義してから、前述のAppendでこのオブジェクトを渡してあげるとうまくいきます。
以下はT_LogテーブルにLogIdというオートナンバー型フィールドを定義しています。
Set objCol = CreateObject("ADOX.Column") With objCol .Name = "LogId" .Type = adInteger Set .ParentCatalog = objCat .Properties("AutoIncrement") = True End With Set objTbl = CreateObject("ADOX.Table") objTbl.Name = "T_Log" With objTbl.Columns .Append objCol .Append "Time", 7 .Append "Kind", 202, 10 .Append "Mess", 202, 255 End With
主キーの設定
ADOXのKeyオブジェクトを使う方法をのせておきます。LogIdを主キーにします。adKeyPrimary はADOX定数です。
Dim objKey Set objKey = CreateObject("ADOX.Key") objTbl.Keys.Append "PrimaryKey", adKeyPrimary, "LogId" Set objKey = Nothing
テーブル削除
Option Explicit Const DSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=hoge.mdb;" Dim objCat Set objCat = CreateObject("ADOX.Catalog") objCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=hoge.mdb;" objCat.Tables.Delete "Tサンプル" Set objCat = Nothing
レコードセットのページング処理について
ASPなどでDBを扱う場合重宝する技ですよね。1ページに何レコード表示するとか、検索結果は何ページ存在するとか、そんな感じの処理がADOでは簡単に利用できます。
肝となるのは、
- PageSize で 1ページあたりに表示するレコード数を設定
- AbsolutePage で指定ページの先頭にポインタを移動
です。
下のサンプルはNorthwind.mdbの仕入先テーブルをページング処理しています。(10レコードづつ区切った4ページ目を表示します)
タイプライブラリのことはVBSのページを参考にしてください。
Option Explicit ' ページング処理のサンプル Dim objRec Set objRec = CreateObject("ADODB.RecordSet") objRec.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Northwind.mdb;" objRec.CursorType = 3 ' adOpenStatic objRec.Open "SELECT * FROM 仕入先" objRec.PageSize = 10 objRec.AbsolutePage = 4 Dim strRes, intCnt, objFld intCnt = 1 Do While (Not objRec.EOF) and (intCnt <= objRec.PageSize) For Each objFld in objRec.Fields strRes = strRes & objFld.Value & "," Next strRes = strRes & vbCrLf objRec.MoveNext intCnt = intCnt + 1 Loop MsgBox strRes objRec.Close Set objRec = Nothing
もっと詳しい情報がMSDNに記載されています。参考にしてください。
JROによるMDBの最適化について
非常に簡単です。VBSのサンプルを載せておきます。サンプルは古い(元の)hoge.mdbを最適化して、新しいhogehoge.mdbを作成します。
動作には、Microsoft Jet and Replication Objects 2.1 Library が必要です。
Option Explicit Dim strOld, strNew Dim objJRO strOld = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=hoge.mdb" strNew = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=hogehoge.mdb" Set objJRO = CreateObject("JRO.JetEngine") objJRO.CompactDatabase strOld, strNew Set objJRO = Nothing
テキストデータへのアクセス方法
CSV形式などテキストデータで保存されたファイルへADOからアクセスするやりかたです。テキストをデータベースとして扱う時は、
- ディレクトリをデータベースとみなす
- ファイルをテーブルとみなす
- テーブルのスキーマはschema.iniで定義する
のように構成します。
schema.iniについて
schema.iniはテキストファイルをデータベースとして扱うときの各テーブルのスキーマを定義しておくファイルです。テキストデータベースと同じディレクトリに置きます。例えば、
schema.ini
[会員マスタ.txt] ColNameHeader=True Format=Delimited(,) MaxScanRows=0 CharacterSet=OEM Col1=ID Integer Col2=氏名 Char Width 20 Col3=年齢 Integer Col4=性別 Char Width 2 Col5=入会日 Date(yyyy/mm/dd) Width 8
のようになります。複数テーブルがあるときは、続けて記入します。指定方法などMSDNにschema.iniの仕様が掲載されているので参考にしてください。
サンプル
接続文字列には、Extended Properties=TEXT; がつきます。Data Sourceはディレクトリ名(データベース名)です。下記サンプルではカレントにあるtextdbというディレクトリにアクセスしています。テーブル名は[会員マスタ#TXT] というように、.が#に置き換わり[]で囲むのがお約束です。下記サンプルは、textdbディレクトリ下の会員マスタ.txtの内容を表示します。
textado.vbs
Option Explicit ' CSVをJetで読む Dim objRec Set objRec = CreateObject("ADODB.RecordSet") With objRec .ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=textdb;Extended Properties=TEXT;" .Open "SELECT * FROM [会員マスタ#TXT]" WScript.Echo .GetString(,,", ") .Close End With Set objRec = Nothing
会員マスタ.txt
ID,氏名,年齢,性別,入会日 10001,穂下山風太郎,33,男,2002/12/12 10002,明日来臓,22,男,2002/9/9 10003,春日未来,18,女,2000/8/25 10004,鈴木りんご,42,女,2003/6/18
メモリ内でRecordsetを作成してオープンする
これはADO独自の仕様ですが、ようするにデータベースが無くてもRecordsetを作成、利用できます。やり方はいたってシンプルで、単純にRecordsetオブジェクトを作成するだけです。作成後は通常の接続と同様にFieldオブジェクトで操作することができます。
下記サンプルは、メモリ内にRecordsetを作成し、3レコードのデータを追加し、追加したデータをXML形式でカレントフォルダに書き出します。
<job id="inmemrs"> <reference object="ADODB.Recordset" /> <script language="VBScript"> '-------------------------------------------------------------------- Option Explicit ' ' inmemrs.wsf - インメモリでrecordsetを作成してオープンする ' Dim objRec, varFields Set objRec = CreateObject("ADODB.Recordset") varFields = Array("BookID", "Title", "Price") With objRec With .Fields .Append varFields(0), adInteger .Append varFields(1), adChar, 100 .Append varFields(2), adCurrency End With .Open .AddNew varFields, Array(100, "HogeHoge", 1200) .AddNew varFields, Array(101, "FugaFuga", 1400) .AddNew varFields, Array(102, "FooVar", 900) .Update End With objRec.Save "sample.xml", adPersistXML Set objRec = Nothing '-------------------------------------------------------------------- </script> </job>
レコードセットの切断
スタンドアローンのアプリケーションでは有難みを感じませんが、ASPなどのクライアント、サーバアプリケーションを作成するなら、このようなテクニックを持っていると有利です。一見すると何も変わったことをしていないようですが、レコードセットをオープン後すぐに接続を切断しています。
Set objRec.ActiveConnection = Nothing
しかし既にクライアントメモリ内にレコードセットが取得されているので、切断後も表示することができます。(更新以外ならなんでもできる)
ASPなどでレコードセットを利用する場合、このテクニックを使わないと(レコードセットを使用中接続を保ったままにする)と同時アクセスが多い場合はアウトです。接続中レコードセットをサーバで保持しなければならなくなりサーバリソースを消費するからです。
Option Explicit ' disconrs.vbs - レコードセットの切断 Dim objRec Set objRec = CreateObject("ADODB.Recordset") objRec.CursorLocation = 3 ' adUseClient objRec.Open "運送会社", "File Name=nw.udl" Set objRec.ActiveConnection = Nothing WScript.Echo objRec.GetString objRec.Close Set objRec = Nothing
テーブルの存在をチェック
存在チェックは一時テーブル作成時などによく使います。
テーブルが存在しているかどうかをチェックするには、OpenSchemaでデータベースのスキーマ情報をレコードセットで取得し、取得したレコードセットを調べます。OpenSchemaは引数によって、殆どのDBスキーマ情報を取得できます。テーブル情報の取得には、adSchemaTable = 20 を使用します。
下記サンプルは、チェック部分を関数化したものです。TABLE_TYPEの制約を使用して効率的に処理する方法です。テーブルが存在するならTrueをしないならFalseを返します。
Option Explicit ' tblchk.vbs - テーブルの存在をチェックする Const adSchemaTable = 20 Const CONN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=hoge.mdb;" Const TableName = "Tサンプル" If isExistsTable(TableName) Then WScript.Echo "テーブル「" & TableName & "」は存在します。" Else WScript.Echo "テーブル「" & TableName & "」は存在しません。" End If ' テーブルの存在をチェック Function isExistsTable(strTableName) Dim objCon, objRec Set objCon = CreateObject("ADODB.Connection") objCon.Open CONN ' TABLE_TYPE制約を使用 Set objRec = objCon.OpenSchema(adSchemaTable, _ Array(Empty, Empty, Empty, "TABLE")) isExistsTable = False Do Until objRec.EOF If strTableName = objRec("TABLE_NAME") Then isExistsTable = True objRec.MoveNext Loop objRec.Close : Set objRec = Nothing objCon.Close : Set objCon = Nothing End Function
同じことをADOXを使ってやるとこうなります。
' ADOXを使ったテーブルの存在チェック Function isExistsTable(strTableName) Dim objCat, objTable Set objCat = CreateObject("ADOX.Catalog") objCat.ActiveConnection = CONN isExistsTable = False For Each objTable In objCat.Tables If objTable.Type = "TABLE" Then If objTable.Name = strTableName Then isExistsTable = True Exit For End If End If Next Set objCat = Nothing End Function
バイナリデータの読み書き
ADOはDB操作しか出来ないと思われ勝ちですが、ADO Streamオブジェクトを使うと、テキストデータやバイナリデータの入出力を行うことができます。VBScriptでのテキストデータ処理はFileSystemObjectでもサポートしているので、あまりありがたみを感じませんが、バイナリデータを扱えるのは非常にありがたいことです。
サンプルはカレントの hoge.jpg を backup_hoge.jpg というファイル名でコピーします。
Option Explicit ' バイナリファイルの読み書きテスト Const adTypeBinary = 1 Const adSaveCreateOverWrite = 2 Const FILE = "hoge.jpg" Dim objStream Set objStream = CreateObject("ADODB.Stream") objStream.Open objStream.Type = adTypeBinary objStream.LoadFromFile FILE objStream.SaveToFile "backup_" & FILE, adSaveCreateOverWrite objStream.Close Set objStream = Nothing
このサンプルはちょっとズルっこで、バイナリデータを読み込んでそのまま別名で保存してます。実際にバイナリデータを読み書きするには、read, writeというメソッドを使います。
レコードセットの永続化
読み出したレコードセットをファイルに保存し、後で保存したレコードセットを読み出して使ったりします。結構便利。下記サンプルは、運送会社テーブルの内容を表示し、それをADTG形式でファイルに保存します。そして一度レコードセットを閉じインスタンスを廃棄します。その後、再びレコードセットのインスタンスを作成し保存したレコードセットを読込み、表示します。
<job> <reference object="ADODB.Recordset" /> <script language="VBScript"> Option Explicit ' レコードセットの保存と保存したレコードセットの読込み Const SAVENAME = "data.adtg" Dim objRec Set objRec = CreateObject("ADODB.Recordset") objRec.Open "運送会社", "File Name=nw.udl", _ adOpenForwardOnly, adLockReadOnly, adCmdTableDirect WScript.Echo objRec.GetString On Error Resume Next CreateObject("Scripting.FileSystemObject").GetFile(SAVENAME).Delete On Error Goto 0 'レコードセットをADTG形式で保存 objRec.Save SAVENAME, adPersistADTG objRec.Close Set objRec = Nothing ' 時が過ぎ・・・ Set objRec = CreateObject("ADODB.Recordset") '保存したレコードセットを開く objRec.Open SAVENAME WScript.Echo objRec.GetString objRec.Close Set objRec = Nothing </script> </job>
非同期接続
データベースに非同期で接続する技です。通常デフォルトの同期接続になりますが、データベースに接続している間に別の作業をすることができるので、非同期にするだけでアプリケーションが劇的に速くなることもあります。やり方は簡単で、ConnectionオブジェクトのOpenメソッドで、第4引数に adAsyncConnect を指定することで非同期接続になります。非同期接続にすると接続を待たずに即次行からの処理を行います。
さて、非同期な(接続を待たない)ので繋がったかどうかの確認が必要となります。VC++やVB、VBAなら、ADOのイベントをトリガして接続を確認できます。=VBSではADOイベントを利用できないので、ConnectionオブジェクトのStateプロパティの値を読んで接続したかどうかを判断すればよいでしょう。= (実はVBSでも利用できます。やり方はVBSのページで)
サンプルはASPです。nw.udlで指定したDBに非同期接続を試みます。接続中に .... と表示していき、接続成功すると"接続しました!"と表示します。
<!-- METADATA TYPE="TypeLib" UUID="00000205-0000-0010-8000-00AA006D2EA4"--> <% @LANGUAGE="VBSCRIPT" %> <% Option Explicit %> <html> <head> <title>非同期接続サンプル</title> </head> <body> <% Dim objCon Set objCon = Server.CreateObject("ADODB.Connection") objCon.Open "File Name=" & Server.MapPath("nw.udl"), , , adAsyncConnect Response.Write "接続中" While Not objCon.State = adStateOpen Response.Write "." Wend Response.Write "接続しました!" objCon.Close Set objCon = Nothing %> </body> </html>