Visual Basic Tips(oo4o)
Visual Basic(以下VBと略す)からOracleへアクセスする方法はODBC経由の方式と、oo4o経由の方法があります。oo4oのTipsと銘打っておきながら最初にODBCの話が出てくるのもおかしいのですが、なぜoo4oなのかを述べる上で敢えて説明します。
以前はVBよりもAccessでの業務用ソフトの開発が多かった為にODBC経由でOracleへの接続が主でした。この時のAccessのバージョンはAccess95及びAccess97でした。Accessはうまく開発を行えばVBよりも開発工数を少なくでき、また内部的にレポートの機能を持っている為、VBの様に別にレポート用のミドルウエア(Crystal
Report等)を導入する必要もありませんでした。
しかし、ODBCとOracleとの相性の問題等により種々の問題がありました。また、データ入力方法、データの見せ方などでAccessでは客先の要望に答えられないようになりだしAccessの代わりとしてVBを使用することになってきました。
- (1)oo4o使用の準備
- (2)Oracleへの接続と切断
- (3)実際のプログラミングでのOracleへの接続と切断
- (4)SQLの実行その1:簡単なSELECT文の実行
- (5)SQLの実行その2:INSERT,UPDATE,DELETE文の実行
- (6)SQLの実行その3:複数行を返すSELECT文の実行
- (7)BLOB型の利用方法:画像データをテーブルに設定
(1)oo4o使用の準備
oo4oからOracleへ接続する為には、接続用のサービス名をOracle用の名前解決
ファイル(TNSNAME.ORA)に設定しておく必要があります。通常はNet8 Configuration-
Assistantにより設定します。このとき確実にOracleに接続できることを確認しておきます。
[tnsping.exeを用いての確認は以下を参照]
D:\Oracle\Ora81\bin>tnsping orcl
TNS Ping Utility for 32-bit Windows: Version 8.1.6.0.0 - Production on 20-SEP-20
02 16:01:28
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=rnk2000)(PORT=1521))
OK (70ミリ秒)
oo4oがインストールされているディレクトリ下にVB用のOracle定義ファイル[ORACONST.TXT]
がありますので、このファイルを[ORACONST.BAS]等のファイル名に変更しVBのプログラム開発の
ディレクトリにコピーして参照することを勧めます。
(私のマシンのディレクトリ[D:\Oracle\Ora81\oo4o\ORACONST.TXT])
oo4oで使用するパラメータ等の数値を意味のあるシンボルでVBのソース上に記述できます。
[ORACONST.TXT]
'''''''''''''''''''''''''''' ' Oracle Objects for OLE global constant file. ' This file can be loaded into a code module. '''''''''''''''''''''''''''' 'Editmode property values ' These are intended to match similar constants in the ' Visual Basic file CONSTANT.TXT Global Const ORADATA_EDITNONE = 0 Global Const ORADATA_EDITMODE = 1 Global Const ORADATA_EDITADD = 2 ' Field Data Types ' These are intended to match similar constants in the ' Visual Basic file DATACONS.TXT Global Const ORADB_BOOLEAN = 1 Global Const ORADB_BYTE = 2 Global Const ORADB_INTEGER = 3 ..... ..... .....
(2)Oracleへの接続と切断
OrcaleをPL/SQL等で利用したことがある方であれば、最初にOracleにログオン しなければOracleへのアクセスが何もできないことはご存知のことと思いますが、 oo4oからのアクセスでも最初にログオン処理を行います。
'Oracleハンドリングオブジェクトの宣言 Public OraSess As Object ' Oracleセッションオブジェクト Public OraDB As Object ' Oracleデータベースオブジェクト 'データベースへの接続/解放のテストサブルーチン Public Sub DbOpenCloseTest() On Error Goto DbOpenCloseTest_Err Dim pstrErr As String 'セッションオブジェクトの生成 Set OraSess = CreateObject("OracleInProcServer.XOraSess") 'データベースのオープン処理(ログイン処理) Set OraDB = OraSess.OpenDatabase("ORCL", "SCOTT/TIGER", ORADB_DEFAULT) '-------------------------------------------------------------- ' 実際のデータベースに対する処理..... '-------------------------------------------------------------- 'データベースのクローズ処理(DAOとの互換性の為のCloseメソッドで実際にはコールの必要無し) OraDB.Close 'データベースオブジェクトの廃棄(必ずオブジェクトの生成の逆順で廃棄処理を行う) Set OraDB = Nothing 'セッションオブジェクトの廃棄 Set OraSess = Nothing DbOpenCloseTest_Exit: On Error Resume Next Exit Sub DbOpenCloseTest_Err: 'エラー処理 If OraSess.LastServerErr = 0 Then If OraDB.LastServerErr = 0 Then 'VBエラー pstrErr = Error(Err.Number) Else 'データベース処理系エラー pstrErr = OraDB.LastServerErrText OraDB.LastServerErrReset EndIf Else 'セッション系エラー pstrErr = OraSess.LastServerErrText OraSess.LastServerErrReset EndIf 'エラー表示 MsgBox pstrErr, vbOKOnly, "DbOpenCloseTest" Resume DbOpenCloseTest_Exit End Sub
注: Oracleハンドリングオブジェクトの宣言でデータ型をObject型に宣言していますが、 VBの参照設定で[Oracle InProc Server 2.3 Type Library]をチェックしておけばデータ型を それぞれ以下の様に宣言できます。
Public OraSess As OraSession ' Oracleセッションオブジェクト Public OraDB As OraDatabase ' Oracleデータベースオブジェクト
この様に宣言することは、事前バインディングと呼ばれコンパイルの時に型チェックが行われ、
As Objectと宣言するよりは実行速度が少なからず向上する様です。As Objectと宣言することを
実行時バインディングと呼ばれます。
但し、oo4oの小さなバージョンアップの場合でも事前バインディングではプログラムの再構築が
必要になります。
実行時バインディングではこの必要は無く、そのままプログラムが実行可能です。
oo4oのマイナーバージョンアップ(バグFIX)毎にプログラムの再構築を行うことは煩雑になるので、
私は実行時バインディング(As Object)でプログラミングしています。
(3)実際のプログラミングでのOracleへの接続と切断
前回の説明では、Oracleへの接続と解放を1個の関数の中で行っていますが、実際はその様な プログラミングはされず、接続と切断を別々の関数で組んでおきプログラムの開始時点でOracle への接続を行い、プログラムの終了時点でOracleの切断を行う様に私は組んでいます。 その例としての関数と使用例を以下に示します。
<Oracleハンドリング関数用Moduleファイル>Option Explicit 'Oracleハンドリングオブジェクトの宣言 Public OraSess As Object ' Oracleセッションオブジェクト Public OraDB As Object ' Oracleデータベースオブジェクト Private fOraSess As Boolean 'セッション済フラグ Private fOraDB As Boolean 'データベースオープン済フラグ '------------------------------------------------------------------- ' 名称 : Ora_Open ' 機能 : データベースのオープン ' 引数 : 無し ' 戻値 : True(正常終了) , False(エラー発生) '------------------------------------------------------------------- Public Function Ora_Open() As Boolean On Error GoTo Ora_Open_Err Dim pstrErr As String Ora_Open = False Screen.MousePointer = vbHourglass If fOraSess = False Then 'セッションオブジェクトの生成 Set OraSess = CreateObject("OracleInProcServer.XOraSession") fOraSess = True 'データベースのオープン処理(ログイン処理) Set OraDB = OraSess.OpenDatabase("ORCL", "SCOTT/TIGER", ORADB_DEFAULT) fOraDB = True End If Ora_Open_Exit: Ora_Open = True Screen.MousePointer = vbDefault Exit Function Ora_Open_Err: Screen.MousePointer = vbDefault 'エラー処理 If OraSess.LastServerErr = 0 Then If OraDB.LastServerErr = 0 Then 'VBエラー pstrErr = Error(Err.Number) Else 'データベース処理系エラー pstrErr = OraDB.LastServerErrText OraDB.LastServerErrReset End If Else 'セッション系エラー pstrErr = OraSess.LastServerErrText OraSess.LastServerErrReset End If 'エラー表示 MsgBox pstrErr, vbOKOnly, "Ora_Open" On Error Resume Next End Function '------------------------------------------------------------------- ' 名称 : Ora_Close ' 機能 : データベースのクローズ ' 引数 : 無し ' 戻値 : True(正常終了) , False(エラー発生) '------------------------------------------------------------------- Public Function Ora_Close() As Boolean On Error GoTo Ora_Close_Err Dim pstrErr As String Ora_Close = False If fOraDB = True Then 'データベースのクローズ処理 OraDB.Close 'データベースオブジェクトの廃棄 Set OraDB = Nothing End If If fOraSess = True Then 'セッションオブジェクトの廃棄 Set OraSess = Nothing End If ExitHandler: Ora_Close = True Exit Function Ora_Close_Err: 'エラー処理 If OraSess.LastServerErr = 0 Then If OraDB.LastServerErr = 0 Then 'VBエラー pstrErr = Error(Err.Number) Else 'データベース処理系エラー pstrErr = OraDB.LastServerErrText OraDB.LastServerErrReset End If Else 'セッション系エラー pstrErr = OraSess.LastServerErrText OraSess.LastServerErrReset End If 'エラー表示 MsgBox pstrErr, vbOKOnly, "Ora_Close" On Error Resume Next End Function
<TEST用Formファイル>
Private Sub Form_Load() '"接続中..."メッセージを表示するウインドウの表示 Load frmLogin frmLogin.Show DoEvents 'オラクル接続の関数コール If Ora_Open() = False Then MsgBox "オラクルとの接続に失敗しました。" End If '"接続中..."メッセージを表示するウインドウを廃棄 Unload frmLogin End Sub Private Sub Form_Unload(Cancel As Integer) 'オラクル解放の関数コール If Ora_Close() = False Then MsgBox "オラクルの接続解除に失敗しました。" End If End Sub
※実際のプログラムに組み込む場合には、Ora_Openは引数としてOracleのSID(接続文字列)、
ユーザ名、パスワードを渡した方がより実用的だと思います。
(4)SQLの実行その1:簡単なSELECT文の実行
データベース内のデータを検索するSQL文としてのSELECT文の実行を行います。
ここでは簡単なSELECT文として、DUAL擬似表からシステム日付を取得する関数を示します。
クエリー(SELECT文)の結果として返されるデータの集まりを結果セットといい、この結果セットを
制御するものがカーソルとよばれるものです。カーソル処理を行う為にOraDynasetオブジェクトを
利用しその中のメソッド、プロパティにアクセスすることでデータを取り扱うことができます。
OraDynasetオブジェクトのメソッド、プロパティの詳細は別のところで説明したいと思います。
実際のプログラムは以下の通り長くないので、一読すれば内容はすぐに理解できると思います。
このSQL文は結果が1行のみ必ず返されるとわかっていますので、データを取得後はダイナセットを
明示的に廃棄処理しています。ダイナセットもCloseメソッドは無く、ダイナセットオブジェクトを
Nothingに設定することで廃棄します。
<Oracleハンドリング関数用Moduleファイル>
'------------------------------------------------------------------- ' 名称 : Ora_GetDate ' 機能 : データベースの日付取得 ' 引数 : 無し ' 戻値 : 日付文字列 '------------------------------------------------------------------- Public Function Ora_GetDate() As String On Error GoTo Ora_GetDate_Err Dim pstrErr As String Dim pstrSQL As String Dim pobjDyn As Object 'ダイナセットオブジェクト Ora_GetDate = "" pstrSQL = "SELECT SYSDATE FROM DUAL" 'ダイナセットオープン Set pobjDyn = OraDB.CreateDynaset(pstrSQL, ORADYN_READONLY) '検索結果の格納 Ora_GetDate = pobjDyn.Fields("SYSDATE").Value ' Ora_GetDate = pobjDyn.Fields(0).Value 'ダイナセットをクローズ Set pobjDyn = Nothing ExitHandler: Exit Function Ora_GetDate_Err: 'エラー処理 If OraSess.LastServerErr = 0 Then If OraDB.LastServerErr = 0 Then 'VBエラー pstrErr = Error(Err.Number) Else 'データベース処理系エラー pstrErr = OraDB.LastServerErrText OraDB.LastServerErrReset End If Else 'セッション系エラー pstrErr = OraSess.LastServerErrText OraSess.LastServerErrReset End If 'エラー表示 MsgBox pstrErr, vbOKOnly, "Ora_GetDate" On Error Resume Next End Function
<TEST用Formファイル>
Private Sub Command1_Click() Dim pstr As String pstr = Ora_GetDate() '上記関数のコール MsgBox "Oracle Date = " & pstr '結果の表示 End Sub
■関連記事
⇒Oracle SQL SELECT1 : SELECT文の基礎(5)SQLの実行その2:INSERT,UPDATE,DELETE文の実行
SELECT文以外のDML(データ操作言語)であるINSERT,UPDATE,DELETE文の実行を
OraDatabaseオブジェクトのメソッドであるExecuteSQLを用いて行います。
ExecuteSQLの戻り値は処理された行数が返されます。
今回の例はOracleをインストールした時にデフォルトとして作成されるEMP表を
利用しています。
Private Sub Command1_Click() On Error Goto Err Dim pstrSQL As String Dim pintCnt As Integer 'EMPNOが6000の給料を10%増しにするUPDATE文 pstrSQL = "UPDATE EMP SET SAL = SAL * 1.1 WHERE EMPNO = 6000" pintCnt = OraDB.Execiute(pstrSQL) MsgBox pintCnt & "件の処理を行いました", vbOKOnly , "TEST" 'EMPNOが8888の従業員のデータ行を作成するINSERT文 pstrSQL = "INSERT INTO EMP (EMPNO) VALUES(8888)" pintCnt = OraDB.Execiute(pstrSQL) MsgBox pintCnt & "件の処理を行いました", vbOKOnly , "TEST" 'EMPNOが8888の従業員のデータを削除するDELETE文 pstrSQL = "DELETE FROM EMP WHERE EMPNO = 8888" pintCnt = OraDB.Execiute(pstrSQL) MsgBox pintCnt & "件の処理を行いました", vbOKOnly , "TEST" Exit Sub Err: 'エラー処理 If OraDB.LastServerErr = 0 Then 'VBエラー pstrErr = Error(Err.Number) Else 'データベース処理系エラー pstrErr = OraDB.LastServerErrText OraDB.LastServerErrReset End If 'エラー表示 MsgBox pstrErr, vbOKOnly, "Command1_Click" On Error Resume Next End Sub
■関連記事
⇒Oracle SQL・データの追加(INSERT)⇒Oracle SQL・データの更新(UPDATE)
⇒Oracle SQL・データの削除(DELETE)
(6)SQLの実行その3:複数行を返すSELECT文の実行
SQLの実行その1のSELECT文では1行しか結果を返さないことがはっきりしていましたが、
複数行を返すSELECT文を実行することのほうが実際には多いものです。
以下のプログラムでは
EMP表の中のEMPNOを文字列の配列に返す処理を行っています。
ダイナセットのFieldオブジェクトの値がNULLの可能性がある場合は、Valueの値がNULLかどうかを
IsNull関数で判定しNULLの場合には適切な処理が必要です。
<Oracleハンドリング関数用Moduleファイル>
'------------------------------------------------------------------- ' 名称 : Ora_SelectData1 ' 機能 : EMP表の全てのEMPNOを取得する(結果を文字列配列に返す) ' 引数 : astrNoData() (データ格納用文字列配列) ' 戻値 : True(正常終了) , False(エラー発生) '------------------------------------------------------------------- Public Function Ora_SelectData1(ByRef astrNoData() As String) As String On Error GoTo Ora_SelectData1_Err Dim pstrSQL As String Dim pstrErr As String Dim pobjDyn As Object 'ダイナセットオブジェクト Dim pstr As String Dim pint As Integer Ora_SelectData1 = False 'ダイナセットオープン pstrSQL = "SELECT EMPNO FROM EMP" Set pobjDyn = OraDB.CreateDynaset(pstrSQL, ORADYN_READONLY) 'EMPNOのデータを格納する配列の初期化 ReDim astrNoData(0) '検索結果の格納 If (pobjDyn.EOF = False) And (pobjDyn.BOF = False) Then pint = 0 'レコードの終わりまでを処理する Do While (pobjDyn.EOF = False) 'EMPNOのデータを格納する配列の領域拡張 pint = pint + 1 ReDim Preserve astrNoData(pint) 'Filedオブジェクトの保持している値がNULLかのチェック If IsNull(pobjDyn.Fields("EMPNO").Value) Then pstr = "" Else pstr = CStr(pobjDyn.Fields("EMPNO").Value) End If astrNoData(pint) = pstr 'カーソルを次のレコードに移動する pobjDyn.MoveNext Loop End If 'ダイナセットをクローズ Set pobjDyn = Nothing Ora_SelectData1 = True ExitHandler: Exit Function Ora_GetDate_Err: 'エラー処理 If OraSess.LastServerErr = 0 Then If OraDB.LastServerErr = 0 Then 'VBエラー pstrErr = Error(Err.Number) Else 'データベース処理系エラー pstrErr = OraDB.LastServerErrText OraDB.LastServerErrReset End If Else 'セッション系エラー pstrErr = OraSess.LastServerErrText OraSess.LastServerErrReset End If 'エラー表示 MsgBox pstrErr, vbOKOnly, "Ora_SelectData1" On Error Resume Next End Function
<TEST用Formファイル>
Private Sub Command1_Click() Dim pstr As String Dim pstrNo() As String If Ora_SelectData1(pstrNo) = True Then '上記関数のコール '結果の表示 pstr = "" For i = 1 To UBound(pstrNo) pstr = pstr & pstrNo(i) & vbCr Next i MsgBox pstr End If End Sub
上記のIsNullの判定している部分を以下の様に関数化してコールしてやればより使いやすいと思います。
Variant型を文字列に変換する関数を示していますが、実際にはInteger型やLong型等に変換する関数がを作って使用しています。
.... Do While (pobjDyn.EOF = False) 'EMPNOのデータを格納する配列の領域拡張 ReDim Preserve astrNoData(pint) 'Filedオブジェクトの保持している値がNULLかのチェック astrNoData(pint) = VntToStr(pobjDyn.Fields("EMPNO").Value) 'カーソルを次のレコードに移動する pobjDyn.MoveNext pint = pint + 1 Loop .... '------------------------------------------------------------------- ' 名称 : VntToStr ' 機能 : 与えられたVariant型データを文字列に変換 ' 引数 : astrData (Variant型データ) ' 戻値 : 文字列データ '------------------------------------------------------------------- Function VntToStr(ByVal avntData As Variant) As String On Error GoTo VntToStr_Err '戻り値を初期化する dfVntToStr = "" '有効な文字列に変換する If Not IsNull(avntData) Then dfVntToStr = CStr(avntData) End If ExitHandler: Exit Function VntToStr_Err: On Error Goto 0 End Function
(7)BLOB型の利用方法:画像データをテーブルに設定
テーブルにBLOB型を設定しその中に画像データファイルを格納する方法を以下に示します。
OracleSQL入門の中で使用している商品マスタに画像データを設定することにします。
以下のSQL文でTM_商品のテーブルにカラムを追加します。
>ALTER TABLE TM_商品 ADD 商品画像 BLOB;BLOBをoo4oで扱うにはOraBLOBオブジェクトを利用します。BLOB列を含むダイナセットを作成してやり BLOB列のValue値をOraBLOBオブジェクトに設定してやります。OraBLOBオブジェクトのメソッドには ディスク上のファイルへの読み書きを簡単に行える、CopyFromFile,CopyFileが用意されています。
CopyFromFileはファイルからBLOBに内容をコピーし、CopyFileは逆にBLOBからファイルへのコピーを行います。
以下に、商品画像データ設定関数及び、商品画像データ取得関数を示します。
<Oracleハンドリング関数用Moduleファイル>
'------------------------------------------------------------------- ' 名称 : SetSYOHIN_Image ' 機能 : 画像データ設定 ' 引数 : strCode (商品コード) ' : strFilePath (画像データを格納するファイル名) ' 戻値 : True(正常終了) , False(エラー発生) '------------------------------------------------------------------- Public Function SetSYOHIN_Image(ByVal strCode As String, _ ByVal strFilePath As String) As Boolean On Error GoTo ErrorHandler Dim strSQL As String Dim objOraDyn As OraDynaset Dim objOraBlb As OraBlob Dim pstrErr As String '戻り値初期化 SetSYOHIN_Image = False 'BLOB列を含むSELECT文 strSQL = "SELECT 商品画像 FROM TM_商品 WHERE 商品コード = " & strCode 'ダイナセットの設定 Set objOraDyn = OraDB.CreateDynaset(strSQL, ORADYN_DEFAULT) With objOraDyn .MoveFirst .Edit 'LOB列をEmptyキーワードで初期化 .Fields(0).Value = Empty .Update 'BLOB列の値をOraBLOBオブジェクトに設定 Set objOraBlb = objOraDyn.Fields(0).Value .MoveFirst '更新モード .Edit 'CopyFromFileメソッドで指定ファイルから読み込みBLOBに設定 objOraBlb.CopyFromFile strFilePath .Update Set objOraBlb = Nothing End With 'ダイナセットの解放 Set objOraDyn = Nothing '正常戻り SetSYOHIN_Image = True Exit Function ErrorHandler: 'エラー処理 Set objOraBlb = Nothing Set objOraDyn = Nothing If OraDB.LastServerErr = 0 Then 'VBエラー pstrErr = Error(Err.Number) Else 'データベース処理系エラー pstrErr = OraDB.LastServerErrText OraDB.LastServerErrReset End If 'エラー表示 MsgBox pstrErr, vbOKOnly, "SetSYOHIN_Image" On Error Resume Next SetSYOHIN_Image = False End Function '------------------------------------------------------------------- ' 名称 : GetSYOHIN_Image ' 機能 : 画像データ取得 ' 引数 : strCode (商品コード) ' : strFilePath (画像データを格納するファイル名) ' 戻値 : True(正常終了) , False(エラー発生) '------------------------------------------------------------------- Public Function GetSYOHIN_Image(ByVal strCode As String, _ ByVal strFilePath As String) As Boolean On Error GoTo ErrorHandler Dim strSQL As String Dim objOraDyn As Object Dim objOraBlb As Object Dim pstrErr As String '戻り値初期化 GetSYOHIN_Image = False 'BLOB列を含むSELECT文 strSQL = "SELECT 商品画像 FROM TM_商品 WHERE 商品コード = " & strCode 'ダイナセットの設定 Set objOraDyn = OraDB.CreateDynaset(strSQL, ORADYN_DEFAULT) 'BLOB型にデータがあり?? If IsNull(objOraDyn.Fields(0).Value) = True Then 'データが無い!! GetSYOHIN_Image = False Else 'BLOB型のオブジェクトを設定 Set objOraBlb = objOraDyn.Fields(0).Value 'ファイルへの書込み objOraBlb.CopyToFile strFilePath 'BLOB型のオブジェクトを解放 Set objOraBlb = Nothing 'データが在り GetSYOHIN_Image = True End If 'ダイナセットの解放 Set objOraDyn = Nothing '正常戻り GetSYOHIN_Image = True Exit Function ErrorHandler: 'エラー処理 Set objOraBlb = Nothing Set objOraDyn = Nothing If OraDB.LastServerErr = 0 Then 'VBエラー pstrErr = Error(Err.Number) Else 'データベース処理系エラー pstrErr = OraDB.LastServerErrText OraDB.LastServerErrReset End If 'エラー表示 MsgBox pstrErr, vbOKOnly, "GetSYOHIN_Image" On Error Resume Next GetSYOHIN_Image = False End Function
<TEST用Formファイル>
Private Sub Command1_Click() If SetSYOHIN_Image("1", "TEST1.BMP") = False Then '上記関数のコール MsgBox "商品マスタの画像設定でエラーが発生しました。", vbOKOnly End If If GetSYOHIN_Image("1", "TEST1_COPY.BMP") = False Then MsgBox "商品マスタの画像取得でエラーが発生しました。", vbOKOnly End If End Sub