今回はVBAからADO経由でデータベースサーバーのストアドプロシージャを実行する方法を紹介します。
尚、以前にVBAのADOを使用したデータベースの接続関連のクラスを紹介させていただきましたが、今回はそのクラスをベースに、ストアド実行用のメソッドを追加する方法で実装しています。
よって、以下の記事も併せてご参照ください。
【Excel・Access VBA】データベース接続用クラスで時短プログラミング
ExcelやAccessでデータベースに接続してデータ取得やデータ更新処理を実行する場合に、ADOを利用して実装すること...
www.depthbomb.net
2019.11.27
サンプルプログラム
以下の項で、以前に紹介したデータベース接続用クラスに追加するメソッドのプログラムと、そのメソッドを呼び出すサンプルプログラムを記載します。
データベース接続クラスに追加するプログラム
当処理は、以前に紹介したデータベース接続用クラス(クラス名：DataBaseAccess)をそのまま使用していただいている場合は、以下のプログラムをそのクラスの処理の最後尾にでもペタッと貼り付けてもらうだけで良いです。
※Connectionは上記リンクで掲載しているクラスから取得してきていることが前提の処理で作られているので、もしそのクラスを使わずに当メソッドを使用する場合は、データベースとのConnectionを別途生成してください。
'引数のストアド名とパラメーターを使用して、ストアドを実行します。
'引数1：ストアド名
'引数2：パラメーター※2次元配列(パラメータ名,データ型,値) パラメーターは5つまで。
'戻り値：ストアドの戻り値をそのままVariantにセット
Public Function execute_usp(SpName As String, Param() As String) As Variant
Dim cmd As New ADODB.Command
Dim Param1 As New ADODB.Parameter
Dim Param2 As New ADODB.Parameter
Dim Param3 As New ADODB.Parameter
Dim Param4 As New ADODB.Parameter
Dim Param5 As New ADODB.Parameter
Dim i As Integer
Set cmd.ActiveConnection = mCon
'コマンドタイプにストアドを指定します。
cmd.CommandType = adCmdStoredProc
'ストアド名をセットします。
cmd.CommandText = SpName
'戻り値のパラメーターを指定します。
'※なぜか引数より先に戻り値の定義をしないとエラーになるので注意。
cmd.Parameters.Append cmd.CreateParameter("Return", adInteger, adParamReturnValue)
'引数のパラメーター配列を要素数分ループします。
For i = LBound(Param, 1) To UBound(Param, 1)
'パラメータが5以上(カウンタが4以上)の場合はループから抜けます。
If i > 4 Then
Exit For
End If
'パラメーターをセットします。
Select Case i
Case 0
Set Param1 = cmd.CreateParameter(Param(0, 0), Param(0, 1), adParamInput)
Param1.Value = Param(0, 2)
cmd.Parameters.Append Param1
Case 1
Set Param2 = cmd.CreateParameter(Param(1, 0), Param(1, 1), adParamInput)
Param2.Value = Param(1, 2)
cmd.Parameters.Append Param2
Case 2
Set Param3 = cmd.CreateParameter(Param(2, 0), Param(2, 1), adParamInput)
Param3.Value = Param(2, 2)
cmd.Parameters.Append Param3
Case 3
Set Param4 = cmd.CreateParameter(Param(3, 0), Param(3, 1), adParamInput)
Param4.Value = Param(3, 2)
cmd.Parameters.Append Param4
Case 4
Set Param5 = cmd.CreateParameter(Param(4, 0), Param(4, 1), adParamInput)
Param5.Value = Param(4, 2)
cmd.Parameters.Append Param5
End Select
Next i
cmd.execute
'ストアドの戻り値を当処理の戻り値にセットします。
execute_usp = cmd.Parameters("Return")
Set Param5 = Nothing
Set Param4 = Nothing
Set Param3 = Nothing
Set Param2 = Nothing
Set Param1 = Nothing
Set cmd = Nothing
End Functionメソッドの仕様概要
- メソッドの引数は「実行するストアド名」「ストアドに渡すパラメータ」。
- パラメータは五つまで対応しそれ以上渡されても無視します。
- パラメータは二次元配列とし、「パラメータ名」「データ型」「値」を格納します。
- メソッドの戻り値にはストアドの戻り値をそのままセットします。
メソッド呼び出しサンプルプログラム
当項では呼び出し側のサンプルプログラムを記載します。
'当関数は正常終了時には空文字、異常終了時にはエラーメッセージを戻り値に格納します。
Public Function SampleFunc(Target1CD As Long, Target2CD As Long) As Variant
Dim db As test_project.DataBaseAccess
Dim strSpName As String
Dim strPara(1, 2) As String
Dim varReturn As Variant
On Error GoTo ErrorProcess
'■処理1 データベースの接続処理
'----------------------------------------------------------------------------------------------
Set db = test_project.DatabaseFactory.creater
'サーバデータベースと接続します。
db.connect
'■処理2 サーバ側のストアドを実行します。
'----------------------------------------------------------------------------------------------
'ストアド名を指定します。
strSpName = "dbo.sample_stored_procedure"
'パラメーター1
strPara(0, 0) = "Target1CD" 'パラメーター名
strPara(0, 1) = adBigInt 'データ型
strPara(0, 2) = Target1CD '値
'パラメーター2
strPara(1, 0) = "Target2CD" 'パラメーター名
strPara(1, 1) = adBigInt 'データ型
strPara(1, 2) = Target2CD '値
'※上記パラメータのデータ型をダブルクォーテーションで囲むと
' CreateParameterメソッドの定数として扱われなくなるので注意。
'ストアドを実行します。
varReturn = db.execute_usp(strSpName, strPara)
If varReturn = 1 Then
SampleFunc = "ストアド実行処理でエラーが発生しました。処理を中止します。"
Set db = Nothing
Exit Function
End If
'■処理3 後処理
'----------------------------------------------------------------------------------------------
'データベースと切断します。
db.disconnect
'各オブジェクト変数を解放します。
Set db = Nothing
'正常終了の場合は空文字を返します。
SampleFunc = ""
Exit Function
ErrorProcess:
SampleFunc = "ERROR: " & Err.Description
On Error Resume Next
db.disconnect
Set db = Nothing
End Function上記のサンプルプログラムでは、実行パラメータを二つ持っている「dbo.sample_stored_procedure」という名称のストアドを実行しています。
また、ストアドの戻り値が1の場合は異常とみなしています。
ストアドのパラメーター作る処理で指定する「データ型」は、ADOにおけるデータ型の定数を指定します。
データ型と定数の一覧は以下のリンク先で確認してください。
DataTypeEnum -Microsoft ADO プログラマリファレンス
データ型と定数の一覧は以下のリンク先で確認してください。
DataTypeEnum -Microsoft ADO プログラマリファレンス
最後に
今回作成したストアド実行用のメソッドは、どんなストアドでも呼べるように汎用性を持たせた実装をしたつもりですが、実行環境に合わせて作り替えてご使用ください。
それでは今回も読んでいただきありがとうございました。
2021年9月追記
最近当ブログに問い合わせをいただき、ストアド側のパラメーターのデータ型が日付の場合に、ADO側で指定するパラメーターのTypeをadDateにしたらエラーが出るため、対応方法を教えて欲しいとご連絡をいただきました。
ストアド側はSQL Serverとのことです。
この場合、SQL Serverで一般的に日付型として使われる「datetime」をストアド側のパラメーターとして使用しているのであれば、ADO側で作成するパラメーターのTypeは「adDBTimeStamp」を指定してください。
ご参考まで。
最近当ブログに問い合わせをいただき、ストアド側のパラメーターのデータ型が日付の場合に、ADO側で指定するパラメーターのTypeをadDateにしたらエラーが出るため、対応方法を教えて欲しいとご連絡をいただきました。
ストアド側はSQL Serverとのことです。
この場合、SQL Serverで一般的に日付型として使われる「datetime」をストアド側のパラメーターとして使用しているのであれば、ADO側で作成するパラメーターのTypeは「adDBTimeStamp」を指定してください。
ご参考まで。
