oo4oからADOへの変換 (2) パラメータとストアド・プロシージャ

前回に引続き、oo4oからADOにコードを変換してみます。今回はパラメータを使ってストアド・プロシージャを呼び出します。

サンプルコードはOracle® Objects for OLE開発者ガイドのOraParametersコレクション Addメソッド の例を使用しています。

ADO

Sub Form_Load()
 
  'Declare variables
  'Dim OraSession As OraSession
  'Dim OraDatabase As OraDatabase
  Dim cnn As ADODB.Connection
 
  'Create the OraSession Object.
  'Set OraSession = CreateObject("OracleInProcServer.XOraSession")
  Set cnn = CreateObject("ADODB.Connection")
 
  'Create the OraDatabase Object.
  'Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
  cnn.Open "Provider=OraOLEDB.Oracle;Data Source=ExampleDb;User ID=scott;Password=tiger"
  'cnn.Open "Provider=MSDAORA;Data Source=ExampleDb;User ID=scott;Password=tiger"
 
  Dim cmd As ADODB.Command
  Set cmd = CreateObject("ADODB.Command")
  Set cmd.ActiveConnection = cnn
  cmd.CommandType = adCmdText
 
  Dim param As ADODB.Parameter  
  'Add EMPNO as an Input/Output parameter and set its initial value.
  'OraDatabase.Parameters.Add "EMPNO", 7369, ORAPARM_INPUT
  'OraDatabase.Parameters("EMPNO").serverType = ORATYPE_NUMBER
  Set param = cmd.CreateParameter("EMPNO", , adParamInput, , 7369)
  param.Type = adNumeric
  cmd.Parameters.Append param
 
  'Add ENAME as an Output parameter and set its initial value.
  'OraDatabase.Parameters.Add "ENAME", 0, ORAPARM_OUTPUT
  'OraDatabase.Parameters("ENAME").serverType = ORATYPE_VARCHAR2
  Set param = cmd.CreateParameter("ENAME", , adParamOutput, , "")
  param.Type = adVarChar
  param.Size = 255
  cmd.Parameters.Append param
 
  'Execute the Stored Procedure Employee.GetEmpName to retrieve ENAME.
  ' This Stored Procedure can be found in the file ORAEXAMP.SQL.
  'OraDatabase.ExecuteSQL ("Begin Employee.GetEmpName (:EMPNO, :ENAME); end;")
  cmd.CommandText = "Begin Employee.GetEmpName (?, ?); end;"
  cmd.Execute
  'Display the employee number and name.
 
  'パラメータをクリアするため一旦変数に格納する。
  Dim ename As String
  ename = cmd.Parameters("ENAME")
 
  'パラメータをクリアする。
  Dim i As Integer
  For i = cmd.Parameters.Count - 1 To 0 Step -1
      cmd.Parameters.Delete i
  Next
 
  'パラメータの再設定
  'Add SAL as an Output parameter and set its initial value. 
'  OraDatabase.Parameters.Add "SAL", 0, ORAPARM_OUTPUT 
'  OraDatabase.Parameters("SAL").ServerType = ORATYPE_NUMBER 
  Set param = cmd.CreateParameter("SAL", , adParamOutput, , 0)
  param.Type = adNumeric
  cmd.Parameters.Append param
 
  Set param = cmd.CreateParameter("EMPNO", , adParamInput, , 7369)
  param.Type = adNumeric
  cmd.Parameters.Append param
 
  'Execute the Stored Function Employee.GetSal to retrieve SAL.
  ' This Stored Function can be found in the file ORAEXAMP.SQL.
  'OraDatabase.ExecuteSQL ("declare SAL number(7,2); Begin :SAL:=Employee.GetEmpSal (:EMPNO); end;")
  cmd.CommandText = "Begin ? := Employee.GetEmpSal(?); end;"
  cmd.Execute
 
  'Display the employee name, number and salary.
  'MsgBox "Employee " & OraDatabase.Parameters("ENAME").Value & ", #" & _
            OraDatabase.Parameters("EMPNO").Value & ",Salary=" & _
            OraDatabase.Parameters("SAL").Value
  MsgBox "Employee " & ename & ", #" & _
            cmd.Parameters("EMPNO").Value & ",Salary=" & _
            cmd.Parameters("SAL").Value
 
  'Remove the Parameters.
  'OraDatabase.Parameters.Remove "EMPNO"
  'OraDatabase.Parameters.Remove "ENAME"
  'OraDatabase.Parameters.Remove "SAL"
  For i = cmd.Parameters.Count - 1 To 0 Step -1
      cmd.Parameters.Delete i
  Next
 
End Sub

• 一応、OraOLEDB でなく MSDAORA でも動きます。
• ADO は位置によるバインドしか使用できないため、この例のようにバインド変数を使いまわして使用順を変えている場合、パラメータを一旦クリアして再設定する必要があります。
• oo4o では、ストアド・プロシージャ名を必ず BEGIN END ブロックで囲みますので、ADOの CommandType は adCommandStoredProcedure ではなくadCommandText を指定します。（指定しなければ adCommandUnknown となりパフォーマンスが低下することがあります。）

OraOLEDBとMSDAORAの違い

• パラメータのプレースフォルダは、OraOLEDB の場合は、oo4o のコロンを付けたパラメータ(:EMPNO )がそのまま使えますが、MSDAORA では ? に置き換える必要があります。
• VARCHAR2 の OUT パラメータは、MSDAORAでは Type adBSTR で Size指定不要にできますが、OraOLEDBでは Sizeを指定する必要があります。（ここでは、よく使われる Type adVarchar Size 255 にしています。）
• ストアド・ファンクションの呼び出し "declare SAL number(7,2); Begin :SAL:=Employee.GetEmpSal (:EMPNO); end;" は OraOLEDB では実行できますが、MSDAORAでは "Begin ? := Employee.GetEmpSal(?); end;" にする必要があります。