この項目のより詳しい情報検索のために
キーワード:「データアクセス」,「ADO」,「ADODB.Command」など
資料:MSDN,VBAヘルプ,プログラミングを扱っているHP,データアクセスを扱っているHP
ADOはMicrosoft社が提供する,データ(主としてリレーショナルデータベースのデータ)にプログラムからアクセスするためのオブジェクトです。
接続,レコードセット及びコマンドなどのオブジェクトを利用して,効果的にデータにアクセスすることができます。
現在では.NET Frameworkに統合されたADO.NETがメインステージになっていますが,VBA環境やASP環境ではまだまだADOのステージが残っていきます。
このテキストでは,ADO コードを最適化する Tips について解説します。
[Execute] メソッドは [ADODB.Connection] または [ADODB.Command] オブジェクトに対して呼び出せる,
クエリー,SQL ステートメントまたはストアドプロシージャ([Connection]に対してはプロバイダ固有のテキストも)を実行するメソッドです。
[Execute] メソッドはレコードセットを返す形式でも,返さない形式でも呼び出せます。
ここでは [Command] オブジェクトのレコードセットを返す形式を扱います。
(1) 構文
[Command] オブジェクトの [CommandText] プロパティに指定したコマンドを実行します。
あらかじめ [CommandText] プロパティにコマンドが指定されている必要があります。
Set recordset =command.Execute( [RecordsAffected], [Parameters], [Options])
(2) 戻り値
(3) 指定項目
| 指定項目 | 内容(全て省略可能です) |
| RecordsAffected |
Long 型の変数を指定します。 実行されたコマンドにより影響を受けたレコード数が格納されます。 |
| Parameters |
バリアント型の配列にパラメーターを格納して指定します。 SQL ステートメントにパラメーターとして引き渡されます。 |
| Options |
Long 型の定数を指定します。 CommandTextプロパティを評価する方法とメソッドの実行を制約する項目を指定できます。 |
[Options] 引数には2系統の定数を組み合わせて指定できます。
[CommandText] を評価するための [CommandTypeEnum] から1つと [Execute] の動作を制御する [ExecuteOptionEnum] から必要なだけ選択して,全ての値を足した値を指定します。
ア CommandTypeEnum
| 指定項目 | 内容 |
| adCmdText | プロバイダが [CommandText] に指定された値を SQL クエリなどのコマンド文字列として評価します。 |
| adCmdTable | ADO が指定された名前のテーブルから全行を返すクエリを自動生成します。 |
| adCmdTableDirect | プロバイダが指定された名前のテーブルから全行を取得します。 |
| adCmdStoredProc | プロバイダが [CommandText] をストアドプロシージャとして評価します。 既に登録されたストアドプロシージャ名を指定します。 |
| adCmdUnknown | コマンドタイプが不明であることを明示します。 |
| adCmdFile | 指定された値をファイル名として,ファイルに保存されたレコードセットを復元します。 |
イ ExecuteOptionEnum
| 指定項目 | 内容 |
| adAsyncExecute | コマンドを非同期に実行させます。 |
| adAsyncFetch | [CacheSize] に指定された初期行より後の残りの行を非同期で取得させます。 |
| adExecuteNoRecords | 戻り値のレコードセットを返さないように実行させます。 |
[Execute] メソッドは SELECT ステートメントのように複数行を返す場合も,COUNT ステートメントのように単一の結果を返す場合も同じようにレコードセットの参照を返します。
このレコードセットはコマンドオブジェクトまたはコネクションオブジェクトのコネクション文字列を継承する,前方専用カーソル(ForwardOnly)・読取専用ロック(LockReadOnly)のレコードセットとなります。
よって,より高度なカーソルタイプ・ロックタイプを必要とする場合にはRecoedsetオブジェクトのOpenメソッドが必要となります。
ただし,[Recordset] オブジェクトの通常の機能を利用して,カーソルタイプ・ロックタイプを変更して開きなおすことは可能です。
この点については次項で解説します。
一方,INSERT 文のような結果を返さないコマンドの実行に対しても閉じたレコードセットが返されます。
この閉じたレコードセットは各種プロパティの上書きが可能で,Open メソッドの呼び出しも受付けますが(エラーは起こりません)レコードセットのステータスは [Closed](adStateClosed=0)のままです。
また,この閉じたレコードセットに [Open] メソッドを実行した後でもステータスが閉じたままであるため,[Close] メソッドを実行するとエラーが発生します。
さらに,ステータスが閉じた状態に固定されているために,レコードセットの内部を参照するようなプロパティ(EOFプロパティなど)の実行はエラーを起こします。
このように動作が不自然なレコードセット参照が返されてしまうため,結果を返さないコマンドには後述するオプションを指定してレコードセットを返させないようにする方がが安全です。
コマンドの実行によって返されたレコードセットのカーソルタイプ・ロックタイプを変更して開きなおすことは可能です。
コマンドの実行によって返されたレコードセットを一旦閉じて,[CursorType],[LockType] プロパティを変更した上で,もう一度引数を指定しない [Open] メソッドを実行することで,高度なカーソルタイプ・ロックタイプを利用することができます。
Set rsSQL=cmdSQL.Execute
rsSQL.Close ・・・@
rsSQL.CursorType=adOpenKeySet ・・・A
rsSQL.LockType=adLockOptimistic
rsSQL.Open ・・・B
- @ レコードセットを一旦閉じます。
閉じることによって属性変更が可能になります。
- A タイプ変更をセットします。
- B レコードセットを開きなおします。
しかし,最初から [Open] メソッドでレコードセットを取得するのに対してオーバーヘッドが生じるため,現実的にはあまり利用価値はありません。
(1) [Connection.Execute] と [Command.Execute] の使い分け
[Connection.Execute] と [Command.Execute] は,引数構成が多少異なりますが提供する機能は基本的に同等です。
プロバイダに固有なコマンドタイプを使用する場合には [Connection.Execute] メソッドを使用する必要があります。
一方,出力パラメーターを含む高度なコマンドを実行するには [Command.Execute] を利用する必要があります。
パフォーマンスの観点からは,1回きりしか実行しないコマンドはコマンドの状態情報を保持しない [Connection.Execute] メソッドを利用するべきで,
複数回実行または幾つかのコマンドを連続的に実行する場合は再利用可能な [Command] オブジェクトを利用することによりオーバーヘッドを防げます。
| 項目 | Connection.Execute | Command.Execute |
| サポートするコマンドタイプ |
クエリ テーブル ファイル SQLステートメント ストアドプロシージャ プロバイダ固有コマンド |
クエリ テーブル ファイル SQLステートメント ストアドプロシージャ |
| 最適実行回数 |
単発コマンド |
複数回実行コマンド 多種類コマンドの連続実行 |
| パラメーター使用条件 |
入力パラメーターのみ可能 |
出力パラメーターを含む高度パラメーターを使用可能 |
(2) 結果を返さないコマンド実行による高速化
[Connection.Execute] と [Command.Execute] 共に結果を返さないコマンドに対しても閉じたレコードセットを返します。これはSetステートメントによって明示的に戻り値を格納した場合だけでなく,行を返さない構文を指定している場合でも暗黙にレコードセットが作成されています。返却レコードセットが用意されると,プロバイダがレコードセットプロパティを評価するために無用なオーバーヘッドが生じます。メソッド実行時のOption項目にadExecuteNoRecordsを指定することによって,レコードセットを作成しない制約をかけることができパフォーマンスが向上します。
cmdSQL.CommandText="INSERT INTO [TBL] VALUES(1,2,3)" ・・・@
cmdSQL.Execute ,,adCmdText + adExecuteNoRecords ・・・A
- @ 結果を返さないクエリをセットします。
- A コマンドをコマンド文字列として評価させ,結果を返さないよう指定して実行します
他にadCmdStoredProc + adExecuteNoRecordsも使用できます。
(3) [RecordsAffected] を利用した影響レコード数のカウント
[Connection.Execute] の第2引数,[Command.Execute] の第1引数に指定する Long 型の変数 [RecordsAffected] には,[Execute] メソッドの実行後にそのコマンドによって影響を受けたレコード数が格納されます。
| RecordsAffected | 状態 |
| -1 | レコードの読み取りなど,レコードに影響を及ぼさないコマンドが実行されたときの値 |
| 0 | レコードに影響を及ぼすコマンドが実行されたものの,結果的にレコードが更新されなかったときの値 |
| 有効値 | レコードに影響を及ぼすコマンドが実行されて,実際に影響を受けたレコードの数 |
cmdSQL.CommandText="SELECT * FROM [TBL] " ・・・A
cmdSQL.CommandText="UPDATE [TBL] SET [Field1]='B'WHERE [Field1]='A'" ・・・B
cmdSQL.CommandText="INSERT INTO [TBL] VALUES(1,2,3)" ・・・C
cmdSQL.Execute lngAffected,,adCmdText・・・@
- @ [Execute] メソッドが実行されると指定した [lngAffected] 変数に影響を受けたレコード数が格納されます。
- A SELECT 文の様にレコードに影響を与えないコマンドを実行すると [lngAffected] に -1 が格納されます。
- B UPDATE 文の影響を受けたレコードがあれば整数が格納されますが,[Filed1] に 'A' という値が存在しない場合は,[lngAffected] に 0 が格納されます。
- C INSERT 文などテーブルを変更するコマンドを実行すると,正常に完了した場合影響を受けたレコード数が格納されます。
この場合は 1 レコード追加したので,[lngAffected] には 1 が格納されます。
プロバイダが全てのカーソルタイプ・ロックタイプの組み合わせをサポートしているとは限りません。
プロバイダがサポートしないカーソルタイプ・ロックタイプが指定されると,それらは自動的に他のタイプに設定しなおされます。
サポートされるタイプはカーソルをサーバー側で使用するか,クライアント側で使用するかによっても変化します。
以下で自動的に再設定されてしまう組み合わせを解説します。
(タイプ,Supportsの詳細については別表CusorType,LockType別Supportsパラメーター表を参照)
(1) SQL OLE Provider.1
SQL Server 用の OLE DB プロバイダです。SQL DB Engine にアクセスします。
A サーバーカーソル
サーバーカーソル(UseServer)では静的カーソル(OpenStatic)が読取専用(ReadOnly)でしか使用できません。
静的カーソルを他のロックタイプと併用すると,カーソルタイプがキーセットカーソル(OpenKeyset)に自動的に置き換えられます。
B クライアントカーソル
クライアントカーソル(UseCliant)では静的カーソルのみ使用できます。他のカーソルタイプを指定しても全て静的カーソルに置き換えられます。
また,排他的ロック(LockPessimistic)は最初から静的カーソルと組み合わせて指定したときのみ使用できます。
置き換わる他のカーソルタイプと組み合わせて指定した場合は,全て共有的バッチロック(LockBatchOptimistic)に同時に置き換えられます。
(2) Jet OLE Provider.4
Access用のOLE DBプロバイダです。Jet DB Engineにアクセスします。
A サーバーカーソル
動的カーソルが使用できません。
読取専用ロックと組み合わせた場合は静的カーソルに,それ以外はキーセットカーソルに置き換えられます。
排他的ロックが使用できません。
全て共有的バッチロックに置き換えられます。
静的カーソルは読取専用でのみ使用できます。
それ以外は全てキーセットカーソルに置き換えられます。
B クライアントカーソル
クライアントカーソルでは静的カーソルのみ使用できます。
他のカーソルタイプを指定しても全て静的カーソルに置き換えられます。
排他的ロックが使用できません。
全て共有的バッチロックに置き換えられます。
(3) [Supports]
レコードセットがサポートする機能を調べるためのメソッドです。
[Supports] メソッドは,プロバイダが該当する機能を提供できるかどうかを返すので,特殊な条件下では,サポートすると示された機能が実際には実行できない場合があります。
(ODBC 制御下で複数テーブル結合を使用した場合,[Supports] メソッドは更新可能を示しますが,ODBC クラスでは更新をサポートしていません)
また,前項で解説したタイプの強制変更の際も,強制変更された場合と,強制変更先をはじめから指定していた場合とでは [Supports] の結果が異なる場合が出てきます。
以下に,Supportsが示す内容を解説します。
A [AddNew],[Find],[Update],[Delete] 及び [Bookmark]
それぞれ該当する ADO メソッドが使用可能かどうかを示します。
B [MovePrevious and Move]
[MoveFirst],[MovePrevious],[Move] 及び [GetRows] メソッドを使ってカレントカーソルを後方に移動できるかどうかを示します。
[Bookmark] による後方移動は含まれません。
C [AbsolutePosition and AbsolutePage]
[AbsolutePosition] と [AbsolutePage] のプロパティが使用できるかどうかを示します。
D [Holding Records]
確定されていない全ての変更をコミットせずに,レコードを追加したり別の位置のレコードを変更できるかを示します。
E [Notifications]
プロバイダがレコードセットイベントをサポートできるかを示します。
F [Resyncing data]
[Resync] メソッドを使って,レコードセットの元になる値を [UnderlyingValue] に格納しなおせるかどうかを示します。
G [Batch Updating]
[UpdateBatch] と [CancelBatch] を利用してバッチ更新が可能かどうかを示します。
(1) 非接続レコードセットを使用する利点
非接続レコードセットは,有効化されているデータベースとの接続を持たないレコードセットです。
非接続レコードセットは,データベースとの接続に拘束されず,データベースが置かれているサーバー上のリソースを消費することもありません。
このため,クライアント/サーバーアプリケーションもしくは3階層アプリケーションにおいて,長時間使用されるデータをクライアントに渡す場合にサーバーリソースの消費を抑えることができます。
また,非接続レコードセットは主としてバッチ更新モードとセットで使用され,クライアント側で更新をプールしている際にデータベースを保持しつづけたり,更新ごとにデータベースに更新を送信したりしないために無用なリソースの消費を回避できます。
非接続レコードセットはクライアントカーソルで使用するため,レコードセットのコピーがクライアント側に一時保存されます。
(2) 非接続レコードセットの作成
非接続レコードセットはクライアントカーソルでのみ作成できます。
よって,カーソルタイプは静的カーソルに固定されます。
また,非接続レコードセットの利点を生かすためにはバッチ更新モードにする必要があります。
クライアント/静的カーソル,バッチ更新のレコードセットを作成したら,そのレコードセットの [ActiveConnection] プロパティを明示的に [Nothing] に設定します。
これでレコードセットは現在の接続から切断されます。
データベースに再接続するには,有効な [Connection] オブジェクトを [ActiveConnection] プロパティに設定しなおせば再接続が行われます
(再接続を行う際の [Connection] オブジェクトの検証については以降で詳説します)。
Dim cnSQL As ADODB.Connection
Dim rsSQL As ADODB.Recordset
Const strCN_STRING As String = "Provider=SQLOLEDB.1;" & _
"Data Source=SRVNAME;Initial Catalog=DBNAME;"
Const strQUERY As String = "SELECT * FROM [TBL]"
Set cnSQL = New ADODB.Connection
Set rsSQL = New ADODB.Recordset
cnSQL.Open strCN_STRING
rsSQL.CursorLocation = adUseClient ・・・@
rsSQL.Open strQUERY,cnSQL,adOpenStatic,adLockBatchOptimistic,adCmdText ・・・A
Set rsSQL.ActiveConnection = Nothing ・・・B
- @ クライアントカーソル使用を指定します。
レコードセットを開く前に指定します。
- A 静的カーソル,バッチ更新モードでレコードセットを開きます。
- B [ActiveConnection] に [Nothing] を設定します。
この時点で接続が解除されます。
(3) [BatchUpdate] の実行
接続を解除しても,レコードセットは開いたままになります。
よって,フィールド値の上書き,レコードの追加,削除を行ってレコードセットを編集することができます。
また,バッチ更新モードを設定しているので1レコードの編集ごとに変更をデータベースに送信する必要はありません。
[UpdateBatch] が自動的にローカルキャッシュの変更を全て確定します。
必要な更新を全てローカルコピーのレコードセットに格納します。
全ての変更を格納してデータベースに送信する準備が整ったら,非接続レコードセットを再接続します。
[ActiveConnectiopn] プロパティに,新たに有効化された接続情報を引き渡すことによって再接続が行われます。
再接続したレコードセットで [UpdateBatch] メソッドを呼び出すことによって,レコードセットに加えられた全ての変更がデータベースに送信されます。
Dim cnSQL As ADODB.Connection
Const strCN_STRING As String = "Provider=SQLOLEDB.1;" & _
"Data Source=SRVNAME;Initial Catalog=DBNAME;"
・・・(必要な変更を全て格納します)・・・
Set cnSQL = New ADODB.Connection
cnSQL.Open strCN_STRING ・・・@
Set rsSQL.ActiveConnection = cnSQL ・・・A
rsSQL.UpdateBatch ・・・B
- @ 再接続のための有効コネクションを用意します。
- A 有効コネクションを [ActiveConnection] プロパティに渡して再接続します。
- B [UpdateBatch] メソッドを呼び出して変更をデータベースに送信します。
(4) バッチ処理の競合と評価
非接続レコードセットはデータベースとの接続を解除してバッチ更新モードを利用しているため,変更しているレコードの動的なロック制御を行っていません。
よって,接続を解除している間に他のユーザーがデータベースの値を変更することが可能です。
レコードセットの接続が解除されてから他のユーザーが変更を行ったレコードに対して [UpdateBatch] が更新を送信すると,競合が発生します。
バッチ更新モードではレコードセットを作成した時点でのレコードの値を保持しておいて,現在のデータベース上のレコードの値と比較することによって他のユーザーによる更新を検知します。
競合が発生した場合,[UpdateBatch] メソッドは更新可能な全てのレコードを更新してからエラー −2147217864を起こします。
また,競合の情報を [Connection] オブジェクトの [Errors] オブジェクトに格納し,各レコードの [Status] プロパティのステータス情報を更新します。
(ADO のヘルプでは,プロバイダは送信する全ての変更で競合が発生したときのみプログラムの実行を停止すると記述されていますが,実際には一部レコードのみが競合した場合にもプログラムは停止されてしまいます)
競合が発生した場合は,競合解決のための方針に従って適切にレコードセットを再評価しなければなりません。
競合を評価するために,レコードセットの [Filter] プロパティを使用します。
競合を起こしたレコードのみに絞り込む場合は [Filter] プロパティに [adFilterConflictingRecords] を指定します。
直前に実行された [UpdateBatch] によって影響を受けたレコード全てを評価する場合は [adFilterAffectedRecords] を使用します。
A 競合したレコードをそのまま上書きする場合
[UpdateBatch] は競合の評価を [UnderlyingValue] とデータベースの値とを比較することによって行っています。
よって,[UnderlyingValue] を現在のデータベースの値と同期させる [Resync] メソッドを呼び出し,続けてもう一度 [UpdateBatch] を実行することによって,競合した更新を全てデータベースに書き込むことができます。
Private Sub SubmitChange()
On Error Goto Catch ・・・@
・・・(必要な変更を全て格納します)・・・
Set rsSQL.ActiveConnection = cnSQL
rsSQL.UpdateBatch ・・・A
Finally:
・・・ ※ 資源解放などの終了処理は省略してあります
Exit Sub
Catch:
Select Case Err.Number
Case −2147217864 '←実際には定数にすべきです ・・・B
rsSQL.Filter = adFilterConflictingRecords ・・・C
・・・(競合したレコードの情報を取得します)
rsSQL.Resync adAffectGroup, adResyncUnderlyingValues ・・・D
rsSQL.UpdateBatch ・・・E
rsSQL.Filter = adFilterNone ・・・F
Case Else
'その他のエラー処理を行います
End Select
Goto Finally
End Sub
- @ 競合はエラーを起こすのでエラー処理に記述します。
- A ここで競合エラーが発生します。
- B 競合エラーが起こっている場合に処理をスイッチします。
- C レコードセットを競合したレコードに絞り込みます。
- D [UnderlyingValue] に現在のデータベースの値を読み込みます。
- E さらにUpdateBatchを呼び出して変更を上書きします。
- F フィルターを破棄しておきます。
B 競合した変更を破棄して,競合したレコードの情報を表示する場合
競合した変更を破棄する場合,レコードセットを現在のデータベースと同期させる [Requery] メソッドを呼び出します。
競合したレコードの情報を表示する場合は,[Requery] を呼び出す前に [adFilterConflictingRecords] によって絞り込まれたレコードセットの情報を取得しておきます。
一般的には,競合した各レコードの ID などをユーザー表示するシナリオが考えられます。
・・・ ※ Catch以降のエラー処理のみ記述しています
Catch:
Select Case Err.Number
Case -2147217864 '←実際には定数にすべきです
rsSQL.Filter = adFilterConflictingRecords ・・・@
rsSQL.Resync ・・・A
・・・(競合したレコードのローカルコピーの値をFields(Index).Valueからデータベース上の現在の値をFields(Index).UnderlyingValueから取得します)
rsSQL.Requery ・・・B
rsSQL.Filter = adFilterNone ・・・C
Case Else
'その他のエラー処理を行います
End Select
Goto Finally
End Sub
- @ レコードセットを競合したレコードに絞り込みます。
- A [UnderlyingValue] に現在のデータベースの値を読み込みます。
- B 確定されていない更新を全て破棄してレコードセットをデータベースに同期させます。
- C フィルターを破棄しておきます。
C 送信した変更全ての情報を表示する場合
競合処理時に一括で上書き,一括で破棄などを行えない場合(競合の処理をユーザーに問い合わせる場合など)は送信された全てのレコード更新についてステータスを評価する必要があります。
[adFilterAffectedRecords] によってバッチ更新されたレコードに絞込み,各レコードのステータス情報を評価して処理を制御します。
各レコードの状態評価にはフラグ化された [Status] プロパティと [RecordStatusEnum] とのビット演算 で行います。
実際に競合を起こしたレコードは [Status] プロパティが [2050] になります([adRecModified] + [adRecConcurrencyViolation]:レコードが変更された+共有的ロック違反のためにレコードが保存されなかった)。
更新を送信する前に,他のユーザーによって更新する値と同じ値に更新された場合(更新時間をずらして2ユーザーで同じ値に更新しようとした場合など)は [Status] は 8 になります([adUnmodified]:レコードは変更されていない)。
この他更新対象レコードが削除された場合など状況に合わせて制御を行う必要があります。
・・・ ※ Catch以降のエラー処理のみ記述しています
Catch:
Select Case Err.Number
Case −2147217864 '←実際には定数にすべきです
rsSQL.Filter =adFilterAffectedRecords ・・・@
Do Until rsSQL.EOF
If (rsSQL.Status And adRecConcurrencyViolation + adRecModified) _
= adRecConcurrencyViolation + adRecModified Then
・・・競合を起こした場合の処理を記述します ・・・A
ElseIf (rsSQL.Status And adUnmodified) Then
・・・同じ値に更新した場合の処理を記述します ・・・B
・・・※ その他必要があるだけ制御を追加します
End If
rsSQL.MoveNext
Loop
rsSQL.Filter = adFilterNone ・・・C
Case Else
'その他のエラー処理を行います
End Select
Goto Finally
End Sub
- @ レコードセットをバッチ更新の影響をうけたレコード全体に絞り込みます。
- A [Status] プロパティと定数 2050 との AND 演算で [adRecModified] フラグと [adRecConcurrencyViolation] フラグが立っているかを評価します。
フラグが立っている場合は競合処理を記述します。
- B [Status] プロパティと定数 8 との AND 演算で [adUnmodified] フラグが立っているかを評価します。
フラグが立っている場合は同値更新処理を記述します。
- C フィルターを破棄しておきます。
(5) 非接続レコードセットの接続情報の検証
非接続レコードセットが保持する接続情報を検証するために以下の実験を行ってみました。
A 実験1
- Srv01 に接続したレコードセットを非接続化する
- 更新を格納しない
- Srv02 に張ったコネクションを渡して再接続する
- [UpdateBatch] を実行する
B 実験2
- Srv01 に接続したレコードセットを非接続化する
- 更新を格納する
- Srv02 に張ったコネクションを渡して再接続する
- [UpdateBatch] を実行する
C 実験3
- 上記の実験を Srv01 から Jet DB Engine に向けて行う
※ 接続したテーブルは Srv01,Srv02 共に同名,同定義のテーブルとする
※ 受け入れ側のテーブルは全て空テーブルとする
※ 想定した機能は Srv01 のテーブルから Srv02 のテーブルにレコードをフルコピーすること
D 結果
実験1,実験2では SQL 内部エラーも VB エラーも起こしません。
実験3でのみ Jet DB Engine がエラー −2147217900 を返します。
実験1では,更新を格納していないため Srv01 も Srv02 も共にテーブルに変化はありません。
実験2では Srv02 へのコネクションを渡し,レコードセットの [ActiveConnection] プロパティが Srv02 側に書き換えられているにも関わらず,全ての更新が元の [Srv01] 側に反映されます。(エラーは起こりません)
実験3では Jet DB Engine がエラー箇所を特定できないエラーを返すため詳細な検証ができていませんが,制御は Jet DB Engine に移っています。
以上のことから,以下の点が推測できます。
- [Recordset] オブジェクトは [Connection] オブジェクトから渡された接続情報を参照だけでなく内部に保持している。
- SQL OLE DB プロバイダの制御下では再接続の際に渡される接続情報は適切にレコードセットに反映されていない。
- JET OLE DB プロバイダの制御下では再接続の際に接続先が切り替わっているが,テーブル定義情報の違いもしくはプロバイダの動作の違いなどの原因によりエラーが発生する。
- レコードセットの非接続化に利用する,[ActiveConnection] の Set Nothing はレコードセット内部に保持されている接続情報を消去していない。
(1) フィールドの値を連続して取得する際の最適化
レコードセット [Fields] コレクションの Index を利用して多数のレコードの値を取得/更新する場合,各レコードごとに [Fields] コレクションの先端 Index 0 から走査が行われるためにオーバーヘッドが生じます。
このオーバーヘッドは,必要となる Index を持つ [Field] アイテムを別の [ADODB.Field] 型変数に事前バインドすることによって解消できます。
Do Until rsSQL.EOF
Debug.Print rsSQL.Fields(0)
Debug.Print rsSQL.Fields(1)
Debug.Print rsSQL.Fields(3)
Debug.Print rsSQL.Fields(5)
rsSQL.MoveNext
Loop
- [Fields] コレクションの Index を利用すると Index を走査するオーバーヘッドが大量レコード走査時に無視できなくなります。
Dim fdNo0 As ADODB.Field
Dim fdNo1 As ADODB.Field
Dim fdNo3 As ADODB.Field
Dim fdNo5 As ADODB.Field ・・・@
・・・・
Set fdNo0 = rsSQL.Fields(0)
Set fdNo1 = rsSQL.Fields(1)
Set fdNo3 = rsSQL.Fields(3)
Set fdNo5 = rsSQL.Fields(5) ・・・A
Do Until rsSQL.EOF
Debug.Print fdNo0
Debug.Print fdNo1
Debug.Print fdNo3
Debug.Print fdNo5 ・・・B
rsSQL.MoveNext
Loop
- @ [Field] オブジェクト変数を用意します。
- A 各 [Field] オブジェクト変数に [Fields] コレクションの要素を結び付けます。
([Field] オブジェクト変数はオブジェクト変数なので,使用し終わったら適切に解放します。)
- B 各 [Field] オブジェクト変数を介してオーバーヘッドのないアクセスが可能です。
実際の変数名にはフィールド名に即したわかりやすい名前を付けます。
(2) [AddNew],[Update] と INSERT 文,UPDATE 文
不要なデータ転送と,不要なログ保存はデータベースリソースを無駄遣いし,パフォーマンスを低下させます。
例えば,レコードの挿入の際に [AddNew] →フィールド編集→ [Update] という流れで行うと,[AddNew] から,1フィールドの編集動作までがひとつひとつ SQL Server に送信されます。
また,レコードセットが作成されていない状況から新たにレコードセットを作成して挿入を行うとレコードセットに含まれる膨大なデータが転送されてリソースの無駄遣いが発生します。
既存レコードの更新についても同様に,1フィールドごとの更新から [Update] を行うと,1つ1つのステップがひとつひとつ送信され,ステップの移動ごとにオーバーヘッドが生じます。
これらレコードの挿入及び更新には,T-SQL の INSERT 文や UPDATE 文を使用します。
T-SQL コマンドを利用すれば,挿入・更新動作を1ステップとして実行するためにリソースを効率的に利用できます。
但し,T-SQL コマンドで実行したデータベースへの変更を,現在作成されているレコードセットに反映するためには(カーソルタイプにも依存しますが)[Recordset] オブジェクトの [Requery] メソッドを実行する必要があります。
既存レコードセットが巨大な場合は,再取得するレコードセットの情報の方が大きい場合があるので注意が必要です。
レコードセットが用意されていない状況では,T-SQL コマンドを利用し,レコードセットが既にある場合は,再取得するレコードセットの大きさを評価して使い分ける必要があります。
(3) DELETE 文と TRUNCATE 文
DELETE 文を WHERE 条件をつけずに実行すると,指定したテーブル内の全てのレコードを削除します。
しかし,DELETE 文は1度に1レコードずつ削除して,削除エントリを1レコードずつトランザクションログに記録します。
一方,TRUNCATE 文はデータを格納する領域を解除することによってテーブル全体のレコードを削除するため,トランザクションログには領域の削除のみが記録されます。
DELETE 文はテーブル内の Identity のカウンタ値を保存するのに対して,TRUNCATE 文はカウンタを初期値にリセットします。
また,TRUNCATE 文はページ全体を削除するためにトリガを発動できず,この他外部キー制約が掛けられているテーブルには使用できません。
TRUNCATE 文はデータ内容を削除するのみなので,テーブル定義,制約,インデックスなどは保存されます。
(4) SELECT * 文を必要最低限に抑える
SELECT * を使用すると,実際には参照も更新もされない無駄な情報がネットワーク経由で送信されることになります。
ローカルに使用されるレコードセットの取得などの場合は必要最低限のフィールドを指定することによって,送信される情報量を抑える必要があります。
レコード数のカウントを取る場合にも,"SELECT COUNT([ID])"のように一意なフィールドを指定するなどして,SQL Server内部の処理も最小化するように努めます。
(SELECT COUNT(*) と SELECT COUNT([ID]) のどちらが早いのかというのはもっと込み入った議論があるようです)
(2) [AddNew],[Update] と INSERT 文,UPDATE 文
(5) TOP ステートメントと [MaxRecords] プロパティ
テーブルの中で上位/下位何レコードかを利用することがわかっている場合は,T-SQL の TOP 文や [Recordset] オブジェクトの [MaxRecords] プロパティを利用します。
SELECT TOP 10 [ID],[Field1],[Field2] FROM [TBL1]
このクエリで先頭 10 レコードを取得します。
ORDER BY 句と組み合わせることによって,昇順 10 レコードや降順 10 レコードを取得することも可能です。
(6) [Recordset] オブジェクトの [ActiveConection]
[Recordset] オブジェクトの [ActiveConnection] プロパティは [Recordset] が閉じている場合は読み書き可能で,開いている場合は読取専用になります。
これの唯一の例外が,非接続レコードセットを作成する場合で,[CursorLocation] プロパティが [UseCliant] を使用している場合のみ上書きが可能になります。
(Nothing以外の値も上書き可能です。)
[Connection] オブジェクトの [Close] メソッドを呼び出すと,[Recordset] は自動的に閉じた状態に変更され,[ActiveConnection] プロパティは Nothing にセットされます。
よって,[Recordset] が閉じられる前に [Connection] の [Close] が行われても比較的オブジェクトの呼び出し構造に不整合は生じません。
しかし,[Connection] の [Close] が行われない状況において,何らかの理由で [Connection] オブジェクトが解放されてしまっても,[Recordset] オブジェクトは影響を受けません。
参照先のコネクションが破棄されているにも関わらず,[ActiveConnection] プロパティは元の接続情報を保持し,条件によっては [Update] メソッドや [UpdateBatch] メソッドが正常に動作する場合があります。
(更新もサーバーに反映されてしまいます)
このことから,[Recordset] は [Connection] オブジェクトから接続情報を完全に参照しているわけではなく,一部を共有しつつ,内部的にも接続情報を保存していると言えます。
[Connection] オブジェクトが破棄されているにもかかわらず,機能の一部が呼び出せるレコードセットができてしまうことから,オブジェクト解放の順序を正確に記述しないと,適切な ADO 運営はできません。
(7) 不正な SQL 文のトラップ
strCMD = "SELECT * FROM [TBL] WHERE [NAME] = '" & _
txtName.Value & "'"
cmdSQL.CommandText = strCMD
cmdSQL.Execute
上記のようなコードには安全上致命的な欠陥が存在します。
テキストボックス txtName に VB では MaxLength プロパティの設定や,VBA ではコードによる入力値のチェックなどが組み込まれていないと,意図しない SQL 文が実行されてしまう可能性があります。
意図しない SQL 文とは,データベースのレコードを改竄・破壊したり,最悪の場合データベースサーバー自体にも被害を及ぼすような SQL 文を言います。
上記のようなコードを実装したアプリケーションで,txtNameに
';DELETE [TBL];
という値を挿入すると,完成する SQL コマンドは
SELECT * FROM [TBL] WHERE [NAME] ='';DELETE [TBL];
となります。
これは1行にいくつかの SQL 文を続けて書く「;」を使用した手法で,
「[NAME]フィールドが空のレコードを選択した後,[TBL]テーブルのレコードを削除しなさい」というコマンドになります。
よって,この入力値がチェックを通ってしまうようなことがあると,データベースが簡単に破壊されてしまいます。
一方,システムストアドプロシージャを利用すると
SELECT * FROM [TBL] WHERE [NAME] ='';xp_cmdshell 'notepad.exe';
というように,SQL Server が稼動しているサーバー上で任意の Shell コマンドを実行することができます。
この Shell コマンドにフォーマットを実行するようなコマンドを投げれば,サーバー自体に致命的な被害が及んでしまいます。
これを防ぐために,
- 入力値のチェックは厳密に何重にも行う
- 特殊文字「'」,「¥」はエスケープするために「''」,「¥¥」に置き換える
- アプリケーションに使用させるデータベースアカウントには必要最低限のデータベースロールしか与えない
などの対策が必要です。
レコードを読み取って更新するだけのアプリケーションである場合,db_Datareader,db_Datawriter の2つの権限があれば充分です。
この場合,ユーザーストアドプロシージャ及びシステムストアドプロシージャの実行権限はなくなります。
全てのデータベースユーザーに管理者権限を与えてしまうのは非常に危険です。
(1) 資源の解放
ADO をはじめ,AactiveX に根ざすオブジェクト変数は全て,使用したら適切に解放しなければなりません。
適切な解放が行われないと,アプリケーションを閉じた後もサーバーへの接続が残ってしまったり,オブジェクトがメモリの一部を占拠したままアクセスできなくなったり,他のアプリケーションに影響を与えるなどの不具合が生じます。
ADO では [Open] メソッドを呼び出して使用するオブジェクトには [Close] を呼び出して,最終的に全てのオブジェクトに対して Set Nothing によるメモリからの削除を行う必要があります。
解放手順とコードについては次々項「共通終了処理」で解説します。
(2) SQL内部エラーの取得
1つの [Connection] に関連のある全ての ADO 操作でのプロバイダが返すエラーは [Connection] の [Errors] コレクションに返されます。
この [Errors] コレクションに格納された個々の [Error] オブジェクトを参照することによって,プロバイダを介して返される SQL の内部エラー情報を取得することができます。
以下のコードでは,各 [Error] オブジェクトの情報をデバッグのためにイミディエイトウィンドウに表示しています。
If Not (cnSQL Is Nothing) Then
For intI = 0 To cnSQL.Errors.Count - 1
Debug.Print "cnSQL Error Index:" & intI
Debug.Print cnSQL.Errors.Item(intI).Number ・・・ VBエラー番号
Debug.Print cnSQL.Errors.Item(intI).Description ・・・ エラー説明
Debug.Print cnSQL.Errors.Item(intI).Source ・・・ エラーを起こした元
Debug.Print cnSQL.Errors.Item(intI).NativeError ・・・ 内部エラー番号
Debug.Print cnSQL_A.Errors.Item(intI).SQLState ・・・ 関連オブジェクトのステータス
Next
End If
(3) 共通終了処理の実装
ADO オブジェクトの解放処理は,エラーが発生して緊急にプロシージャを終了する場合にも,プロシージャの実行が正常に終了した場合にも共通に行う必要があります。
前者の場合は,通常の解放処理を行う前にエラーを適切に処理する必要があるということのみ違いがあります。
よって,ADO にアクセスするプロシージャでは,正常終了の場合には終了処理を行ってプロシージャを出る。
エラーが発生した場合はエラー処理にジャンプした後,残りのプロシージャのステップをとばして,終了処理に再びジャンプする必要があります。
このような処理方法は,C++などのオブジェクト指向言語には一般的な処理で,例外処理という形態で実装されています。
VB.NET についても,Try〜Catch〜Finally という構造で(Try:メイン処理 Catch:エラー処理 Finally:共通終了処理)例外処理が実装されています。
しかし,通常の VB6.0 環境ではこの形態のエラー処理は実装されておらず,擬似的に実現するしかありません。
Goto ステートメントと行ラベルを利用して,以下のように擬似的な共通終了処理を実装します。
Private Sub ADOAccess( )
On Error Goto Catch
・・・メイン処理
Finally:
・・・共通終了処理
Exit Sub
Catch:
・・・エラー処理
Goto Finally
End Sub
この型でコーディングすることによって,共通終了処理が明確化され,より安全な ADO 運用ができるようになります。
Private Sub ADOAccess( )
On Error Goto Catch ・・・@
Dim cnSQL As ADODB.Connection
Dim cmdSQL As ADODB.Command
Dim rsSQL As ADODB.Recordset
・・・メイン処理 ・・・A
Finally: ・・・B
If Not(rsSQL Is Nothing) Then
If rsSQL.State <> adStateClosed Then rsSQL.Close
Set rsSQL = Nothing
End If
If Not(cmdSQL Is Nothing) Then
Set cmdSQL = Nothing
End If
If Not(cnSQL Is Nothing) Then
If cnSQL.State <> adStateClosed Then cnSQL.Close
Set cnSQL = Nothing
End If
Exit Sub
Catch: ・・・C
If Not (cnSQL Is Nothing) Then
For intI = 0 To cnSQL.Errors.Count - 1
Debug.Print "cnSQL Error Index:" & intI
Debug.Print cnSQL.Errors.Item(0).Number
Debug.Print cnSQL.Errors.Item(0).Description
Debug.Print cnSQL.Errors.Item(0).Source
Debug.Print cnSQL.Errors.Item(0).NativeError
Debug.Print cnSQL_A.Errors.Item(0).SQLState
Next
End If
・・・その他のエラー処理
Goto Finally
End Sub
- @ エラー処理の方法を指定します。(Try)
On Error Gotoステートメントによって,エラー処理のラベルにジャンプするように指定します。
例外処理では,Tryステートメントを使用して指定した区域ごとにトラップする例外を分けることができますが,On Error Gotoステートメントによってエラーの種類を制御すると非常に保守性の悪いコードになってしまいます。
よって,エラーの種類による処理の振り分けは,エラー処理ルーチン内でのSelect処理に譲ります。
- A メインのデータアクセスコードを記述します。
オブジェクトの初期化確保から,実際の処理までのデータアクセスコードを記述します。
システムのリソースを早期に解放する必要がある場合などを除いて,オブジェクトの解放などの共通処理はメイン部分には記述しません。
メイン部分の間中,プロシージャが必要とする全てのデータベースオブジェクトを保持することになるので,プロシージャへの機能の切り分け設計が重要になってきます。
- B 共通終了処理を記述します。(Finally)
主としてオブジェクトの解放などの終了処理を行います。
この他,フラグの初期化や関数の場合の戻り値の終了処理など,正常終了時とエラー時に共通で行う処理を全て記述します。
コード例では ADO 資源の解放を行っています。
ADO 資源の解放順は,[Field] オブジェクトなどの末端オブジェクトから解放し,確保したのが古い順に [Recordset] オブジェクトを,最後に各 [Connection] オブジェクトを解放するようにします。
また,[Open] 処理のあるオブジェクトは確実に [Close] 処理を行います。
当該オブジェクトの初期化確保時にエラーが起きて,オブジェクト変数が確保できなかった(Nothing のままの状態)場合に終了処理を行ってしまうと,解放処理の際に多重にエラーが発生してしまいます。
これを防ぐために解放処理を If Not(オブジェクト変数 Is Nothing) Then で囲っています。
(オブジェクト変数が確保できていない場合はそもそも解放の必要がありません)
共通終了処理を完了したら,プロシージャから抜ける準備が整っているので Exit ステートメントによってプロシージャを終了します。
- C エラー処理を記述します。(Catch)
Err.Number によって Select することによって必要なエラー処理を記述します。
コード例では Select は省略してあり,イミディエイトウィンドウに SQL 内部エラーを表示するコードのみ追加してあります。
実際のエラー処理では,ADO エラーに限らず,プロシージャ内で起こりうる全てのエラーに対して,該当するエラー番号を指定して処理を追加していきます。
全てのエラー処理を完了したら,共通終了処理へ移行するので,Goto ステートメントによって Finally ラベルにジャンプします。
(エラー処理の定義によってはResume 0 などを記述する場合もあります。)