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;" にする必要があります。

oo4oからADOへの変換 (1)

1. 基礎編
2. パラメータとストアド・プロシージャ
3. Edit メソッドとトランザクション
4. AddNew メソッドとデフォルト列値
5. Find 系メソッド
6. カーソル変数（REF CURSOR）
7. Adapterクラスの作成（ADOでパラメータの名前によるバインドを可能にする）
   

基本的に oo4o は ODP.NET へ移行することになりますが、VBA の場合は ADO に移行することになるかと思います。
oo4o をどこまで機械的に ADO に変換できるか確認するため、とりあえず ORACLE_BASE\ORACLE_HOME\oo4o\excel\samples のサンプルを ADO に書き換えてみます。

ADO

Sub Get_Data()
  ' Create and initialize the necessary objects
'  Dim OraSession As Object
'  Dim OraDatabase As Object
  Dim cnn As Object
 
'  Dim EmpDynaset As Object
  Dim rst As Object
 
  Dim ColNames As Object
 
'  Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'  Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
  Set cnn = CreateObject("ADODB.Connection")
  cnn.Open "Provider=OraOLEDB.Oracle;Data Source=ExampleDb;User ID=scott;Password=tiger"
 
'  Set EmpDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)
  Set rst = CreateObject("ADODB.Recordset")
  Const adUseClient = 3
  rst.CursorLocation = adUseClient
  Const adOpenStatic= 3
  Const adLockOptimistic = 2
  Const adCommandText = 1
  rst.Open "select * from emp", cnn, adOpenStatic, adLockOptimistic, adCommandText
 
  ' Using field array, ie. ColNames("ename").value, is significantly faster than using
  '   field lookup, ie. EmpDynaset.fields("ename").value
'  Set ColNames = EmpDynaset.Fields
  Set ColNames = rst.Fields
 
  ' Place column headings on sheet
  For icols = 1 To ColNames.Count
    Worksheets("DataSheet").Cells(1, icols).Value = ColNames(icols - 1).Name
  Next
 
  ' Place data on sheet using CopyToClipboard
'  EmpDynaset.CopyToClipboard -1
  Sheets("DataSheet").Select
  Range("A2").Select
'  ActiveSheet.Paste
  ActiveCell.CopyFromRecordset rst
End Sub

• oo4oのサンプルに合わせて、遅延バインディングにしています。

CreateDynaset のオプション

上の例では、 oo4o の CreateDynaset のオプション 0& (ORADYN_DEFAULT) に合わせて、ADO では、Recordset の CursorLocation を adUseClient にし、OpenメソッドでCursorType:=adOpenStatic, LockType:=adLockOptimistic を使うようにしています。

この設定だと ADO の Recordset は厳密にはORADYN_DEFAULT ではなく、ORADYN_NO_BLANKSTRIP + ORADYN_ORAMODE + ORADYN_NO_REFETCHと同じ振る舞いをします。
（参考: AddNew メソッドとデフォルト列値）

今回の例では読取り専用で充分ですので、oo4o の CreateDynaset は、

oo4o

  Const ORADYN_READONLY = &H4
  Const ORADYN_NOCACHE = &H8
  Set EmpDynaset = OraDatabase.CreateDynaset("select * from emp", ORADYN_READNONLY + ORADYN_NOCACHE)

ADO の Recordset.Open は、

ADO

  Const adOpenForwardOnly= 0
  Const adLockReadOnly = 1
  rst.Open "select * from emp", cnn, adOpenForwardOnly, adLockReadOnly
  '規定値なので、rst.Open "select * from emp", cnn で可

または、

  ' Executeメソッドは読み取り専用で、前方スクロールタイプのカーソルを返す。
  Set rst= cnn.Execute("select * from emp")

で良いと思います。

参考

OraOLEDBの機能

docs.oracle.com

とくに ADOプログラマのためのヒント は必見。

DAO から ADO への移植 1

msdn.microsoft.com

oo4oはDAOに近いモデルのため参考になります。

エラー処理

エラー処理を追加してみます。
サンプルは Oracle® Objects for OLE開発者ガイド の LastServerErr プロパティの例を ADO に書き換えたものです。SQL文にfrom 句がないことに注意。

ADO

Sub Form_Load()
 
 'Declare variables as OLE Objects.
' Dim OraSession As OraSession
  Dim cnn As ADODB.Connection
 
' Dim OraDatabase As OraDatabase
' Dim OraDynaset As OraDynaset
  Dim rst As ADODB.Recordset
 
 'Create the OraSession Object.
' Set OraSession = CreateObject("OracleInProcServer.XOraSession")
  Set cnn = CreateObject("ADODB.Connection")
 
 'Set up an error handler.
 On Error GoTo errhandler
 
 'Create the OraDatabase Object by opening a connection to Oracle.
' Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
  cnn.Open "Provider=OraOLEDB.Oracle;Data Source=ExampleDb;User ID=scott;Password=tiger"
 
 'Attempt to Create the OraDynaset Object.
 'Notice that the FROM keyword is missing from the SQL statement.
' Set OraDynaset = OraDatabase.CreateDynaset("select * emp", 0&)
  Set rst = CreateObject("ADODB.Recordset")
  Const adUseClient = 3
  rst.CursorLocation = adUseClient
  Const adOpenStatic = 3
  Const adLockOptimistic = 2
  Const adCommandText = 1
  rst.Open "select * emp", cnn, adOpenStatic, adLockOptimistic, adCommandText
 
Exit Sub
 
errhandler:
 
 'Check to see if an Oracle error has occurred.
' If OraDatabase.LastServerErr <> 0 Then
 If cnn.Errors.Count > 0 Then
  ' 厳密には
  'If cnn.Errors(cn.Errors.Count - 1).NativeError <> 0 Then
 
'  MsgBox OraDatabase.LastServerErrText
  MsgBox cnn.Errors(cnn.Errors.Count - 1).Description
 
 Else 'Must be some non-Oracle error
  MsgBox "VB:" & Err & " " & Error(Err)
 End If
 
 Exit Sub
 
End Sub

oo4o、ADO いずれも以下のメッセージボックスが表示されます。

ADO は、cnn.Errors.Count > 0 でOraSession.LastServerErr <> 0も捕まえられます。

続く

oo4oはいつまで使えるのか

Oracle Client の oo4o は 11g (11.2) の 32bit 版が最終。

Oracle® Objects for OLE開発者ガイド 11gリリース2 (11.2) for Microsoft Windows - Oracle Objects for OLEの紹介 に

Oracle Database 11gリリース2(11.2)は、Oracle Objects for OLEをサポートする最後のデータベース・バージョンです。

との記載がある。

ただし、上記開発者ガイドのシステム要件には、

32-bit: Windows 7(Professional、EnterpriseおよびUltimate Edition)

までしか記載がなく、Windows 8 以降は記載されていない。

Oracle® Database Clientインストレーション・ガイド 11gリリース2 (11.2) for Microsoft Windows - Oracle Database Clientの全般的なソフトウェア要件(Windows 32-bit)には、Windows 8、Windows 8.1 の 記載があるにもかかわらず。

なお、Oracle Client 11.2 から Server 12.2 12.1 への接続は基本的にサポートされている。

Oracle Client / Server Interoperability Support | Oracle 日本

www.oracle.com

Oracle® Database Readme 12cリリース2 (12.2) - Oracle Database 12cリリース2 (12.2)のこのリリースで使用できない機能または制限されている機能 には、

32ビット上のOracle Database Clientはサポートされません。

との記載がある。

まとめ

oo4oは Windows 7（32bit構成）かつ Oracle Server 12.1 が最終。
Windows 7 の延長サポート終了は、2020年1月14日。

---

注: 最新・正確な情報はご自身でご確認ください。

Oracleのドキュメント参照

「SQL*Plusユーザーズ・ガイドおよびリファレンス」に辿り着くのに手間取ったのでメモ。
OTNのドキュメント・インデックスにアクセス。
データベースの使用しているOracleバージョンを選択。
Oracle Database ドキュメントのLinkのView Libraryをクリック。
画面左メニュー下部のすべてのドキュメントやブック（バージョンにより名称は異なる）をクリック。