ADODB.Commandのパラメータ埋め込みクエリーを出力

ADODB.CommandのCommandTextではクエリーを確認できますが、parameterのところが"?"のまま、直接DBにクエリー投げたいので、いちいち値をセットするのはめんどくさいので、メソッドを作ってみました。

' adArray
' adBigInt
' adBinary
' adBoolean
' adBSTR
' adChapter
' adChar
' adCurrency
' adDate
' adDBDate
' adDBTime
' adDBTimeStamp
' adDecimal
' adDouble
' adEmpty
' adError
' adFileTime
' adGUID
' adIDispatch
' adInteger
' adIUnknown
' adLongVarBinary
' adLongVarChar
' adLongVarWChar
' adNumeric
' adPropVariant
' adSingle
' adSmallInt
' adTinyInt
' adUnsignedBigInt
' adUnsignedInt
' adUnsignedSmallInt
' adUnsignedTinyInt
' adUserDefined
' adVarBinary
' adVarChar
' adVariant
' adVarNumeric
' adVarWChar
' adWChar

Public Function printSql(ByRef cmd As ADODB.Command) As String
Dim p As ADODB.Parameter
Dim vntP As Variant
Dim inti As Integer
Dim strR As String
inti = 1
ReDim vntP(cmd.Parameters.Count)
For Each p In cmd.Parameters
    Select Case p.Type
        'ここはニーズに応じてタイプを追加してください
        Case adInteger
            vntP(inti) = p.Value
        Case Else
            vntP(inti) = "'" & p.Value & "'"
    End Select
    inti = inti + 1
Next
strR = cmd.CommandText
For inti = 1 To UBound(vntP)
    strR = Replace(strR, "?", vntP(inti), 1, 1)
Next
printSql = strR
End Function

使い方

Global cnn As New ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset
    Dim param As ADODB.Parameter
    Set cmd = New ADODB.Command

   ' DB接続
    cnn.Open "Provider=SQLOLEDB;" & _
             "Data Source=" & srvName & ";" & _
             "Initial Catalog=" & databaseName & ";", user, pass
    cmd.ActiveConnection = cnn
    cmd.CommandTimeout = 0
    cmd.CommandType = adCmdText
    cmd.CommandText = "SELECT * FROM MyProj WHERE NAME = ? AND Create_at = ? AND hour = ?"

    ' パラメータを作成
    Set param = cmd.CreateParameter("PJNAME", adVarChar, adParamInput, 20)
    cmd.Parameters.Append param
    Set param = cmd.CreateParameter("CREATED", adDBTimeStamp, adParamInput)
    cmd.Parameters.Append param
    Set param = cmd.CreateParameter("HOUR", adInteger, adParamInput)
    cmd.Parameters.Append param

    ' パラメータに値をセット
    cmd.Parameters("PJNAME").Value = "計画A"
    cmd.Parameters("CREATED").Value = "2012-12-01"
    cmd.Parameters("HOUR").Value = 10

    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenStatic
    Debug.Print(printSql(cmd)) '⇒ココ
    rs.Open cmd

結果

SELECT * FROM MyProj WHERE NAME = '計画A' AND Create_at = '2012/12/01' AND hour = 10