VBからOracle 11gに接続 何度も似た様な質問になりますが、依然としてVBからOracle 11gに接続できません。 現在のエラーメッセージは 追加情報:'Oracle.DataAccess.Client.OracleConnection' のタイプ初期化子が例外をスローしました。 で、発生しているコードは Dim cnn As New OracleConnection です。 プロジェクトのプロパティから Oracle.DataAccess.Client Oracle.DataAccess.Types をインポート設定しているので、コードにImports宣言はありません。
・ODBCについては、システムDSNにドライバ(Oracle in OraDb11g_Home1)を登録し、 接続テストもOKです。 ※ただ、接続時のユーザID/パスワードがOracleをインストールした時の値じゃなく、 Widowsログオン時の「それ」になっているのが気にはなりますが。 ・11gでマネージドドライバが使えない事は、過去の質問で理解しております。 ・OADCをインストールする際は64bit用のClient版(XCOPYじゃない方)を選択しました。 ・プロジェクトの[参照]でOracle.DataAccessを追加しています。 相変わらず何が何だかさっぱり分かりません。 SQL*Plusからは、user/pass一体型で"@サービス名"を指定しないと接続できず、 user/pass分離型(二段階入力)では 「ORA-12560: TNS: プロトコル・アダプタ・エラーが発生しました。」 というエラーになり接続できません。 補足 追記ですが、Oracle
11g本体はアンインストーラで削除し、再インストールしました。VBからOracle 11gに接続 何度も似た様な質問になりますが、依然としてVBからOracle 11gに接続できません。
ベストアンサー
こんにちは。 そのパソコンのファイル全部の検索で、transnames.oraのファイルが「ひとつだけ」なのか、確認してください。 オラクルドライバーがインストールされているフォルダでない場所のoraファイルは、バックアップのつもりでも、「存在」してはいけません。 odbcでの接続では、ms提供のドライバーで、すんなり接続できる場合もあるので、 odbcシステム設定で、確認してみてください。 あと、oracle提供のドライバーの宣言などは、サイトのサンプルコードなども参考になるので、チェックしてみてください
(oracleは、VBSのようなスクリプト環境でも使えるので、VBのnew oracleなどの接続で出来ていないのは、インストール環境の確認ぐらいになります。 動的接続で使えるものが、「静的接続」で出来ない、ということなんですから。
確認したら、マイドキュメントにコピーされていたので削除しましたが、結果は変わりませんでした。ただ、もう一人の方の回答に沿ってODACをインストールしたので D:\app\<user>\product\11.2.0\dbhome_1\NETWORK\ADMIN D:\app\<user>\roduct\11.2.0\client_1\Network\Admin\Sample の2か所に存在しています。ODBCは使っていないのですが、それでも複数存在してはいけないのでしょうか。
その他の回答(2件)
・user/pass一体型で"@サービス名"を指定しないと接続できず ・ORA-12560: TNS: プロトコル・アダプタ・エラーが発生しました。 ということは、環境変数ORACLE_HOME、ORACLE_SIDが設定されていないか、正しくないのでは?
前回の質問のやりとりを見てると、 >追加情報:'Oracle.DataAccess.Client.OracleConnection' のタイプ初期化子が例外をスローしました。 これは、 (A) VBのプロジェクトが参照している Oracle.DataAccess.dll のバージョン (B) (A) が参照している OraOps11w.dll のバージョン が不一致のために起きているような気が・・・。 つまり、Oracle Client が複数インストールされているとか、インストール/アンインストールを繰り返したために古い情報がレジストリに残っているために、 Oracle.DataAccess.dll → OraOps11w.dll の参照がおかしくなっているんじゃないかと。 レジストリの内容を確認してみて、同じようなキーが複数存在しているようなら、 (1) Oracle Client をアンインストール (2) レジストリから、該当する Oracle 関連のキーを全て削除 (3) Oracle Client をインストール としてみるといいかも。 ちなみに、何度も言うけど、Oracle.DataAccess を使おうとしているのなら、ODBC は関係ないよ。(↓参照) //www.atmarkit.co.jp/ait/articles/0305/16/news003.html
Oracleに関係しそうなファイルとレジストリキーを全て削除して、Oracle 11g本体とODAC 64Bit Clientをインストールしました。 質問のdllのバージョンは Oracle.DataAccess.dll : 2.112.3.0 OraOps11w.dll : 2.112.3.0 と同じです。ただOraOps11w.dllの方が製品バージョンに"ODAC RELEASE5"と表示(ファイルバージョンの表示は同じ)されています。D:appの下にはユーザフォルダが1つ(Oracle
Home)しかないので、違うファイルを誤って参照しているケースも考えられません。(レジストリもOracle本体とODACのキーがそれぞれが1つしか無いのを確認) これでも追加情報:'Oracle.DataAccess.Client.OracleConnection' のタイプ初期化子が例外をスローしました。 が出るのは、他に何が原因として考えられるでしょうか。 CPU(32/64bit)の選択ミスは、何度も確認したので無いと思います。
Visual Basic(VB6.0A)からOracleへアクセスする方法として oo4o を使った内容を以前から載せてはいましたが、 時代はVB.NETでの開発となり、ADO.NET の方式が主流となってきました。 ADO.NET とは、SQL Server や XML などのデータソースや、OLE DBやODBC経由でデータ ソースに対する一貫性を持ったアクセスができる仕組みです。
オラクルも「.NET」に対応するために、ネイティブにデータベースにアクセスできる「Oracle Data Provider for .NET」(ODP.NETと略します)を提供しています。 基本的な関数などはADO.NETと似た部分が多いですが、オラクル独自の機能が使用可能です。
- ODP.NET使用の準備
- Oracleへの接続と切断
- SQLの実行その1:簡単なSELECT文の実行
- SQLの実行その2:複数行を返すSELECT文の実行
- SQLの実行その3:INSERT,UPDATE,DELETE文の実行
- SQLの実行その4:トランザクションを加味したINSERT,UPDATE,DELETE文の実行
- BLOB型の利用方法:画像データをテーブルに設定
ODP.NET使用の準備
ODP.NETを使用するためには、Oracle Clientをインストールするのがいいのですが、 .NETからデータベースへのアクセスのみを行いたい場合はOracle Data Access Components のインストールで十分です。
オラクルの 「Oracle Data Access Components (ODAC) for Windows ダウンロード」 ページから該当するダウンロードを選びます。
私は32ビット用の 「32-bit ODAC with ODT ダウンロード」 を選びました。 このページを見ますと多くのバージョンがダウンロードできますが、私はオラクルサーバのバージョンに合わせて ODAC 11.2 Release 5 and Oracle Developer Tools for Visual Studio (11.2.0.3.20)をダウンロードしました。
■「ODTwithODAC1120320_32bit.zip」をダウンロードし、デスクトップZIPファイルを解凍します。
■「setup.exe」を実行し、順次画面に従い必要な項目を入力しながら進めます。
ほぼ以下の画像の設定の様に進めればインストールが終了します。(画面は左から右へ、上から下に進みます)
■インストール後「C:\oracle」フォルダに登録されていることを確認します。
オラクルへのアクセスに必要なものは「C:\oracle\odp.net\bin\2.x\Oracle.DataAccess.dll」です。 (.NET4の場合には「C:\oracle\odp.net\bin\4\Oracle.DataAccess.dll」です。)
■VisualStudioでの参照設定
ソリューションエクスプローラの中の参照設定で、「参照の追加」を行います。
参照の追加のダイアログで、「参照」タブの中から、「C:\oracle\odp.net\bin\2.x\Oracle.DataAccess.dll」を設定します。
これで、VB.NET から
ODP.NET を利用する準備ができました。
Oracleへの接続と切断
Orcaleへの接続と切断のみの処理を、フォームに貼り付けたコマンドボタンのクリックイベントでテストします。 ODP.NETを使うためには「Oracle.DataAccess.Client」をソースの先頭で宣言したほうが良いでしょう。
接続のために、Orcaleの接続用オブジェクトの OracleConnection を使用します。 OracleConnection に接続文字列を渡して、オブジェクトの生成を行っています。
■簡易接続ネーミング・メソッドを使用した接続文字列について
//<ホスト名>[:<ポート番号>][/<データベース名(SID)>] (ポート番号省略時は”:1521”が指定されたものとする)Instant Clientのtnsnames設定(tnsnames.oraファイル)を構成することなく、アプリケーションから直接データベースに対する接続文字列を指定できます。 ソース内でコメントの接続文字列は、プログラムをサーバ上で動作させる場合で、localhost の設定でOKです。
'ソースの先頭で以下のImportを宣言 Imports Oracle.DataAccess.Client '----- 'オラクル接続のオープン・クローズのテスト Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click '接続文字列 Dim strConnect As String = _ "Data Source=//192.168.1.207/test; User ID=test;Password=test;" 'Dim strConnect As String = _ ' "Data Source=//localhost/test; User ID=test;Password=test;" 'オラクル接続オブジェクト Dim Conn As New OracleConnection(strConnect) 'オラクル接続オープン Conn.Open() 'オラクル接続クローズ Conn.Close() Conn.Dispose() End Subオラクル接続オブジェクト OracleConnection のメソッド Open・Close を使用して接続のオープン・クローズを行います。
簡単な接続はこれでいいのですが、実際にはエラー処理が必要になります。
VB.NET ではエラー処理に Try...Catch...End Try ブロック構造を使用します。
ODP.NETにはエラーが発生したときに Catch でスローされる OracleExceptionクラスがありますので、これを使った例を以下に示します。
上記の例では、エラーを起こすためにパスワードを間違ったものにしています。 このプログラムを実行すると、以下のメッセージが表示されます。
SQLの実行その1:簡単なSELECT文の実行
データベース内のデータを検索するSQL文としてのSELECT文の実行を行います。
ここでは簡単なSELECT文として、DUAL擬似表からシステム日付を取得する処理を示します。
ODP.NET では以下のオブジェクトを使用しテーブルからのデータを取得します。
OracleConnection | データベースへの接続オブジェクト | データベース接続文字列を渡して接続処理を行う |
OracleCommand | データベースに対して実行する SQL ステートメントまたは ストアド プロシージャ用オブジェクト | SQL文と接続オブジェクト渡して SQL文(ストアド・プロシージャまたはテーブル名)と接続オブジェクト渡して、OracleCommandオブジェクトにリクエストをカプセル化します。OracleCommandオブジェクトは、リクエストを作成してデータベースに送信し、結果を戻します。 |
OracleDataReader | データ行の前方向ストリームを読み取るオブジェクト | OracleCommandオブジェクトのExecuteReader()メソッドによりOracleDataReaderオブジェクトが返されます。 |
■処理手順
・接続文字列の生成
・オラクル接続オブジェクトの生成
・オラクル接続オープン
・コマンドオブジェクトの生成
・コマンドオブジェクトの実行で読込オブジェクト取得
・データ行の読込処理
・読込が正常の場合、各データの取得
・読込オブジェクトのクローズ
・オラクル接続クローズ
データ行の読込が正常でのデータの取得の所で、4種類の方法を示しています。
取得①
OracleDataReaderオブジェクトのプロパティ Item にカラム名文字列を与えて、カラムの値を取得しています。
取得②
OracleDataReaderオブジェクトのプロパティ Item にカラムIndexを与えて、カラムの値を取得しています。
この場合は、Indexとしては 0 , 1 しか設定できません。
取得③
OracleDataReaderオブジェクトのプロパティ Item はデフォルト・プロパティなので、(C#ではインデクサというらしい) OracleDataReaderに直接カラム名文字列を与えることで、カラムの値を取得できます。
取得④
OracleDataReader型指定アクセッサを使用してデータ取得を行います。
型指定アクセッサは、Oracleネイティブ・データ型を.NETタイプに変換するメソッドです。
厳密にデータ変換を行いたいのであれば、この方法がベストだと思います。
■代表的な型指定アクセッサについて
BLOB | System.Byte() | GetBytes |
CHAR | System.String System.Char() | GetString GetChars |
DATE | System.DateTime | GetDateTime |
NUMBER | System.Decimal System.Byte System.Int16 System.Int32 System.Int64 System.Single System.Double | GetDecimal GetByte GetInt16 GetInt32 GetInt64 GetFloat GetDouble |
NCHAR | System.String System.Char() | GetString GetChars |
VARCHAR2 | System.String System.Char() | GetString GetChars |
NVARCHAR2 | System.String System.Char() | GetString GetChars |
■関連記事
⇒Oracle SQL SELECT1 : SELECT文の基礎
SQLの実行その2:複数行を返すSELECT文の実行
SQLの実行その1のSELECT文では1行しか結果を返さないことがはっきりしていましたが、 複数行を返すSELECT文を実行することのほうが実際には多いものです。
以下のプログラムでは、TM_商品の商品コードと商品名の一覧を表示する処理を行っています。
「SQLの実行その1」との違いは、読込処理で OracleDataReader.read() の結果が True ではなくなるまで Whileループ で処理をしているところです。 OracleDataReader.read()
は読込む行が無くなると False を返します。
SQLの実行その3:INSERT,UPDATE,DELETE文の実行
SELECT文以外のDML(データ操作言語)であるINSERT,UPDATE,DELETE文の実行を OracleCommandオブジェクトのメソッドである ExecuteNonQuery を用いて行います。 ExecuteNonQuery の戻り値は処理された行数が返されます。
■1個のINSERT文の実行'1個のINSERT文の実行 Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click '接続文字列 Dim strConnect As String = _ "Data Source=//192.168.1.207/test; User ID=test;Password=test;" 'オラクル接続オブジェクト Dim Conn As New OracleConnection(strConnect) Try 'オラクル接続オープン Conn.Open() 'SQL文 Dim SQL As String = _ "INSERT INTO TM_商品(商品コード, 商品名, 商品区分, 仕入単価, 売上単価)" & _ " VALUES(1001,'商品1001', '区分1001', 10000, 15000)" 'コマンドオブジェクト(SQL文と接続オブジェクト) Dim Cmd As New OracleCommand(SQL, Conn) 'INSERT文の実行 Dim nCnt As Integer = Cmd.ExecuteNonQuery() 'オラクル接続クローズ Conn.Close() Catch exora As OracleException 'オラクルエラー MsgBox(exora.Number & ":" & exora.Message) Catch ex As Exception 'PGエラー MsgBox(ex.Message) Finally Conn.Dispose() End Try End Sub 以下は連続で、INSERT、UPDATE、DELETE文の実行を行います。
上の「1個のINSERT文の実行」でも、以下の例でも、DML文の実行は成功した場合にその都度コミットされています。
⇒Oracle SQL・データの追加(INSERT)
⇒Oracle SQL・データの更新(UPDATE)
⇒Oracle SQL・データの削除(DELETE)
SQLの実行その4:トランザクションを加味したINSERT,UPDATE,DELETE文の実行
「SQLの実行その3:INSERT,UPDATE,DELETE文の実行」ではDML文の実行の都度コミットがされてしまうので、
途中でエラーが在った場合はそこまでの結果がデータに残ってしまいます。
そこで、トランザクション処理をDML文の実行の前後で掛けてやれば、エラーが在った場合には更新処理が反映されません。
以下の例では、OracleConnectionオブジェクトのBeginTransactionメソッドを用いて、 トランザクション開始しOracleTransactionオブジェクトを取得します。
OracleCommandオブジェクトにOracleTransactionオブジェクトを関連付けた後で、DML文の実行を行います。
各DML文の実行が正常に終わった後に、OracleTransactionオブジェクトのCommitを行って、全てのDML文の確定を行います。 尚、オラクルエラーやプログラムエラーが在った場合には、OracleTransactionオブジェクトのRollbackを行って、 全てのDML文の結果の反映を廃棄を行います。
⇒トランザクション・ロック
BLOB型の利用方法:画像データをテーブルに設定
■テーブルにBLOB型を設定しその中に画像データファイルを格納する方法を以下に示します。
テストを行うためのBLOBフィールドを持つテーブルを用意します。(以下のCREATE文でテーブルを作成)
CREATE TABLE TT_BLOB( ID NUMBER(5,0) primary key, BLOB blob ); 画像ファイルはテストのため、「test.png」というファイル名とします。(画像の内容は「Oracleへの接続と切断」でメッセージとして表示しているものです)
このファイルを全てバイト配列に読込み、その値を全てBLOBフィールドに登録します。
INSERT文の定義の文字列の中で「:blob」がありますが、コマンドオブジェクトを生成した後でこの引数名で値を設定できます。 というよりもBLOB型のフィールドにはSQL文で直接記述できないためこの方法を用います。
■BLOBデータを取得し表示
上で登録されたBLOBデータを読込んで、フォーム上のPictureBoxに設定し表示します。
SELECT文でBLOBデータのカラムを指定し、コマンドオブジェクトを生成後、OracleDataReaderで読み込み処理を行います。 OracleDataReader の GetOracleBlob メソッドでBLOBデータの取得を行い、MemoryStreamオブジェクトに変換後、 PictureBoxに画像設定を行います。
'BLOBデータの表示 Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click '接続文字列 Dim strConnect As String = _ "Data Source=//192.168.1.207/test; User ID=test;Password=test;" 'オラクル接続オブジェクト Dim Conn As New OracleConnection(strConnect) Try 'オラクル接続オープン Conn.Open() 'SELECT-SQL文 Dim SQL As String = "SELECT BLOB FROM TT_BLOB WHERE ID = 1" 'コマンドオブジェクト(SQL文と接続オブジェクト) Dim Cmd As New OracleCommand(SQL, Conn) Cmd.CommandType = CommandType.Text '読込オブジェクトに接続 Dim dr As OracleDataReader = Cmd.ExecuteReader() '読込処理 If dr.Read Then 'BLOBデータの取得 Dim blob As Oracle.DataAccess.Types.OracleBlob = dr.GetOracleBlob(0) 'BLOBデータをMemoryStreamオブジェクトに変換 Dim mstr As New System.IO.MemoryStream(blob.Value) '画像設定 PictureBox1.Image = New Bitmap(mstr) End If '読込オブジェクトのクローズ dr.Close() dr = Nothing 'オラクル接続クローズ Conn.Close() Catch exora As OracleException 'オラクルエラー MsgBox(exora.Number & ":" & exora.Message) Catch ex As Exception 'PGエラー MsgBox(ex.Message) Finally Conn.Dispose() End Try End Sub■BLOBデータ更新1:OracleCommandおよびOracleParameterを使用したLOBの更新
既に存在するBLOBデータを更新する方法は、新規追加の場合と同じになります。違いはSQL文がUPDATE文である点のみです。
'BLOBデータの更新の実行1 Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click '接続文字列 Dim strConnect As String = _ "Data Source=//192.168.1.207/test; User ID=test;Password=test;" 'テスト画像データをバイト配列に読込 Dim fs As New System.IO.FileStream("test2.png", IO.FileMode.Open, IO.FileAccess.Read) Dim blob(fs.Length) As Byte fs.Read(blob, 0, fs.Length) 'オラクル接続オブジェクト Dim Conn As New OracleConnection(strConnect) Try 'オラクル接続オープン Conn.Open() 'UPDATE-SQL文 Dim SQL As String = "UPDATE TT_BLOB SET BLOB = :blob WHERE ID = 1" 'コマンドオブジェクト(SQL文と接続オブジェクト) Dim Cmd As New OracleCommand(SQL, Conn) '引数の定義 Dim prm As OracleParameter = Cmd.Parameters.Add("blob", OracleDbType.Blob) prm.Value = blob 'UPDATE文の実行 Dim nCnt As Integer = Cmd.ExecuteNonQuery() 'オラクル接続クローズ Conn.Close() Catch exora As OracleException 'オラクルエラー MsgBox(exora.Number & ":" & exora.Message) Catch ex As Exception 'PGエラー MsgBox(ex.Message) Finally Conn.Dispose() End Try End Sub■BLOBデータ更新2:ODP.NET LOBオブジェクトを使用したLOBの更新
BLOBデータを更新方法のもう一つの方法は、トランザクションを開始し SELECT FOR UPDATE でBLOBデータの行を取得します。 その後、OracleDataReader の GetOracleBlobForUpdate メソッドでBLOBデータ(OracleBlob)そのものを取得します。
OracleBlob の書き込みメソッドを使ってバイト配列を設定してやります。
この処理で一つ注意点があります。SELECT文の中で取得するカラムに PRIMARY KEY を指定しないと、以下のエラーが発生します。 (以下の例では SELECT BLOB, ID FROM
... の中の ID です)
「この操作の選択リストには、PRIMARY KEY、ROWIDまたはUNIQUE NOT NULL列が必要です」
■VB.NET関連記事
⇒「VB.NET-TIPS」ページ