DOBON.NETプログラミング道掲示板

[ 最新記事及び返信フォームをトピックトップへ ]

■34042 / inTopicNo.1)  oo4oのAddTableをADOに移行したい
  
□投稿者/ Yoshi 一般人(1回)-(2018/11/26(Mon) 16:40:20)
  • アイコン環境/言語:[VBA] 
    分類:[ASP.NET] 


    業務で使用しているExcelでOfficeの64bitに対応するため、oo4oからADOへの変更作業を行っています。
    Excelを利用しておりVBAで書かれています。

    Oracleのバージョンは11gです。

    AddTableを使いストアドプロシージャで配列として受け渡しをしているようなのですが
    ADOで渡すための方法がわからず、ご教示いただきたいと思っております。

    以下にoo4oで書かれたロジックを記載いたします。
    【VBA(oo4o)】
    OraDB.Parameters.AddTable "inAnimal", ORAPARM_INPUT, ORATYPE_VARCHAR2, Arraysize, 255
    Set inAnimal = OraDB.Parameters("inAnimal")

    'カウンタループ 'l'はカウンタ
    inAnimal(l) = AnimalSet(l)
    'Loop
    ExecuteSQL ("BEGIN PROCEDURE(:inAnimal)End;)

    【PROCEDURE】
    TYPE Array IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
    PROCEDURE (inAnimal IN Array)
    IS...

    少し考えて作ったADOのソースは以下になります。(うまく配列が渡せないため落ちます。)

    Set param = cmd.CreateParameter("inAnimal", adVarChar Or adArray, adParamInput, 255, AnimalSet)

    cmd.Parameters.Append param
    cmd.CommandText = "BEGIN PROCEDURE(:inAnimal); END;"
    cmd.Execute 


    間違っている箇所や方法をご存知の方いらっしゃいましたらよろしくお願いいたします。

マルチポストを報告
違反を報告
引用返信 削除キー/
■34043 / inTopicNo.2)  Re[1]: oo4oのAddTableをADOに移行したい
□投稿者/ 魔界の仮面弁士 大御所(1170回)-(2018/11/26(Mon) 19:13:41)
  • アイコン2018/11/27(Tue) 11:15:33 編集(投稿者)

    No34042に返信(Yoshiさんの記事)
    > AddTableを使いストアドプロシージャで配列として受け渡しをしているようなのですが
    > ADOで渡すための方法がわからず、ご教示いただきたいと思っております。

    ADODB では対応していないと思います。

    ストアドを変更できるなら、配列パラメータ(Associative Array)を用いず、
    Temporary Table 経由で受け渡す作りに書き換えてみてはいかがでしょう。

    ストアドを変更できない場合は、配列渡しができるよう、
    32bit 版プロセスな VBScript 経由で OO4O を呼び出すか、
    もしくは、ODP.NET 経由で呼び出すようなラッパーライブラリを
    作ることを検討してみるとか。


    > Set param = cmd.CreateParameter("inAnimal", adVarChar Or adArray, adParamInput, 255, AnimalSet)

    Set p = cmd.CreateParameter("inAnimal", 型, adParamInput)
    cmd.Parameters.Append p
    Let p.Value = 値
    cmd.Execute

    の構文で実験してみました。


    (1) 「adVarChar Or adVarArray」「adBSTR Or adArray」
    → cmd.Parameters.Append の時点で ADO エラー: adErrInvalidParamInfo
     
    (2) 「adVariant Or adVarArray」「adVariant」
    → Variant()、Variant を受け付けたが、Execute 時に実行時例外(E_FAIL)を起こす。Err.Description は毎回変化するゴミ文字列なので、ポインタが破壊されている可能性あり。
     
    (3) adIUnknown
    → 渡すべきインターフェイスがわからないのですが、
     少なくとも下記のコードでは、内部エラーでクラッシュしました。
     
    'Dim rec As ADODB.Recordset
    'Set rec = New ADODB.Recordset
    'rec.Fields.Append "F1", adVarChar, 2000, adFldIsNullable
    'rec.Open
    'rec.AddNew Array("F1"), Array("いぬ")
    'rec.AddNew Array("F1"), Array("ねこ")
    'rec.AddNew Array("F1"), Array("かめ")
    'rec.UpdateBatch

    'Dim rec As Scripting.Dictionary
    'rec dic = New Scripting.Dictionary
    'rec(12) = "いち"
    'rec(34) = "に"
    'rec(56) = "さん"

    'Dim rec As VBA.Collection
    'Set rec = New VBA.Collection
    'rec.Add "いち", "12"
    'rec.Add "に", "34"
    'rec.Add "さん", "56"

    'Set p.Value = rec 'VBA エラー: 424 オブジェクトが必要です
    'Let p.Value = rec 'ADO エラー: adErrInvalidArgument
    Dim uk As stdole.IUnknown
    Set uk = rec
    'Set p.Value = uk 'VBA エラー: 424 オブジェクトが必要です
    Let p.Value = uk 'ここまでは実行できた
    cmd.Execute 'ここでクラッシュ
違反を報告
引用返信 削除キー/
■34045 / inTopicNo.3)  Re[2]: oo4oのAddTableをADOに移行したい
□投稿者/ 魔界の仮面弁士 大御所(1172回)-(2018/11/27(Tue) 11:19:43)
  • アイコン
    No34043に追記(魔界の仮面弁士の記事)
    > 実験してみました。 
    
    参考までに、当方が実験で用いたパッケージを示しておきます。
    
    --CREATE TABLE TEST_TABLE (COL1 VARCHAR2(2000), COL2 NUMBER );
    --/
    CREATE OR REPLACE PACKAGE PKG_SAMPLE
    AS
        TYPE TestArray IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
        PROCEDURE EXAMPLE(inAnimal IN TestArray);
    END PKG_SAMPLE;
    /
    CREATE OR REPLACE PACKAGE BODY PKG_SAMPLE AS
        PROCEDURE EXAMPLE(inAnimal IN TestArray) IS
           IDX BINARY_INTEGER;
           VAL VARCHAR2(2000);
        BEGIN
           IDX := inAnimal.FIRST;
           FOR N IN 1..inAnimal.COUNT LOOP
              VAL := inAnimal(IDX);
              INSERT INTO TEST_TABLE (COL1, COL2) VALUES ( VAL, IDX );
              IDX := inAnimal.NEXT(IDX);
           END LOOP;
        END EXAMPLE;
    END PKG_SAMPLE;
    /
    /* ADO 側
      Dim cmd As ADODB.Command
      Set cmd = New ADODB.Command
      Set cmd.ActiveConnection = cn
      cmd.CommandText = "PKG_SAMPLE.EXAMPLE"
      cmd.CommandType = adCmdStoredProc
    */
    
    
    > Set p = cmd.CreateParameter("inAnimal", 型, adParamInput)
    
    ここの『型』を調べるために、OLE DB でスキーマを取得してみました。
    
    'https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms713623(v%3dvs.85)
    Dim rs1 As ADODB.Recordset
    // 下記は "SELECT * FROM USER_ARGUMENTS" に相当
    Set rs1 = cn.OpenSchema(adSchemaProcedureParameters)
    
    
    OraOLEDB.Oracle の場合は 1 件のみ返却され、
    MSDAORA で取得した場合は 2 件となっていましたが、
    いずれも、、パラメータの型として adIUnknown が得られましたので、
    値として要求されているのは、いわゆる配列(SAFEARRAY) ではなく、
    何らかの配列オブジェクト(COM インターフェイス)が要求されるようです。
    
    以下に結果を記します。
    (Recordset の列数が多いので、行列変換して縦に並べています)
    
    ==========================================================================
    【OraOLEDB.Oracle の場合】… 1 件が該当
    (1/1) --------------------------------------------------------------------
           Procedure_Catalog: (null)
            Procedure_Schema: スキーマ名
              Procedure_Name: "PKG_SAMPLE.EXAMPLE"
              Parameter_Name: "INANIMAL"
            Ordinal_Position: 1
              Parameter_Type: 1 (adParamInput)
        Parameter_HasDefault: (null)
           Parameter_Default: (null)
                 Is_Nullable: -1 (VARIANT_TRUE)
                    DataType: 13 (adIUnknown)
    Character_Maximum_Length: (null)
      Character_Octet_Length: (null)
           Numeric_Precision: (null)
               Numeric_Scale: (null)
                 Description: (null)
                   Type_Name: "PL/SQL TABLE"
             Local_Type_Name: "PL/SQL TABLE"
                    Overload: (null)
    ==========================================================================
    【MSDAORA の場合】… 何故か 2 件が該当
    (1/2) --------------------------------------------------------------------
           Procedure_Catalog: (null)
            Procedure_Schema: スキーマ名
              Procedure_Name: "PKG_SAMPLE.EXAMPLE"
              Parameter_Name: "INANIMAL"
            Ordinal_Position: 1
              Parameter_Type: 1 (adParamInput)
        Parameter_HasDefault: 0 (VARIANT_FALSE)
           Parameter_Default: (null)
                 Is_Nullable: -1 (VARIANT_TRUE)
                    DataType: 13 (adIUnknown)
    Character_Maximum_Length: (null)
      Character_Octet_Length: (null)
           Numeric_Precision: (null)
               Numeric_Scale: (null)
                 Description: (null)
                   Type_Name: "PL/SQL TABLE"
             Local_Type_Name: (null)
                    Overload: (null)
    (2/2) --------------------------------------------------------------------
           Procedure_Catalog: (null)
            Procedure_Schema: スキーマ名
              Procedure_Name: "PKG_SAMPLE.EXAMPLE"
              Parameter_Name: (null)
            Ordinal_Position: 1
              Parameter_Type: 1 (adParamInput)
        Parameter_HasDefault: 0 (VARIANT_FALSE)
           Parameter_Default: (null)
                 Is_Nullable: -1 (VARIANT_TRUE)
                    DataType: 129 (adChar)
    Character_Maximum_Length: 2000
      Character_Octet_Length: 2000
           Numeric_Precision: (null)
               Numeric_Scale: (null)
                 Description: (null)
                   Type_Name: "VARCHAR2"
             Local_Type_Name: (null)
                    Overload: (null)
    ==========================================================================

違反を報告
引用返信 削除キー/
■34046 / inTopicNo.4)  Re[2]: oo4oのAddTableをADOに移行したい
□投稿者/ Yoshi 一般人(2回)-(2018/11/27(Tue) 16:17:25)
  • アイコン魔界の仮面弁士様
    いろいろとお調べいただきありがとうございます。
    配列で渡すのはできなさそうなんですね。。。

    > ストアドを変更できない場合は、配列渡しができるよう、
    > 32bit 版プロセスな VBScript 経由で OO4O を呼び出すか、
    > もしくは、ODP.NET 経由で呼び出すようなラッパーライブラリを
    > 作ることを検討してみるとか。

    oo4oの配列渡しを使っているプロシージャが5本あり、処理も複雑であり
    ストアドを変更する(変数受け取りなどに変える)と、ほぼ新規に5本
    立ち上げる必要があるためなるべく変更せずに現行処理を使いたいと考えています。

    そうした状況の場合、
    Windows10 64bit
    Excel 64bit で考えると
    どの手法が現実的でしょうか。
    (どういった改修が工数が小さいでしょうか)

    エンジニアとして知識が浅く、稚拙な文章になってしまい申し訳ありません。
    主観で結構ですのでご教示お願いできませんでしょうか。




違反を報告
引用返信 削除キー/
■34047 / inTopicNo.5)  Re[3]: oo4oのAddTableをADOに移行したい
□投稿者/ 魔界の仮面弁士 大御所(1173回)-(2018/11/27(Tue) 16:52:54)
  • アイコン2018/11/27(Tue) 16:58:09 編集(投稿者)

    No34046に返信(Yoshiさんの記事)
    > ストアドを変更する(変数受け取りなどに変える)と、ほぼ新規に5本

    元のストアドは、必ずしも変更しなくて良いのではないでしょうか。


    VBA 側は、配列オブジェクトとして渡していた部分を、
    作業用テーブルへの AddNew (INSERT 処理)に変更します。

    Oracle 側には、その作業テーブルから、データを配列にして
    元のストアドを呼び出すようなストアドを追加する、という流れ。


    この場合、中継用のストアドが増えるだけで、
    元のストアドはそのまま生かせるんじゃないかと…。


    > Windows10 64bit
    > Excel 64bit で考えると
    > どの手法が現実的でしょうか。

    64bit 版 VBA だと、COM(ActiveX) コンポーネントも 64bit 版が要求されますが、
    ・ADODB + OLEDB は配列オブジェクトを渡せない
    ・ODBC でも渡せない
    ・OO4O は 32bit 版しかない
    と言うことで、64bit 版 VBA だと、正直どうしようも無いと思います。
    VBA ではなく、.NET (あるいは Java とか?)なら対処できそうですが。


    > (どういった改修が工数が小さいでしょうか)

    既定では、64bit OS 上であっても、32bit 版の Office が導入されますよね。
    https://support.office.com/ja-jp/article/2dee7807-8f95-4d0c-b5fe-6c6f49b8d261

    64bit 版で無ければならない強い理由付けが無い限りは、
    Office 64bit をアンインストールして、
    32bit 版 Office と 32bit 版 Oracle Client を入れなおすのも選択肢の一つです。
    そうすれば oo4o を引き続き利用できるので、追加の開発工数が不要になります。

    ただ、64bit 版前提の VBA コードを既に組んでしまっているとなると、
    後掛り作業になってしまうでしょう。


    もし、Office は 64bit のままにしておきたいのであれば、
    配列オブジェクトを渡すために、
     ・「32bit VBScript + OO4O」を Excel VBA から Shell 関数等で呼び出す
     ・「.NET Framework + ODP.NET」を Excel VBA から Shell 関数等で呼び出す
    などの手直しが必要になるかと思います。


    ところで、今回の場合、INDEX BY BINARY_INTEGER が付与されているので、
    結合配列(索引付き表)を使っているのですよね。だとしたら、
    > 'カウンタループ 'l'はカウンタ
    > inAnimal(l) = AnimalSet(l)
    > 'Loop
    というのは、少々不自然な印象を受けました。

    索引付きの場合は連番ではなく、いわゆる Key Value Pair な
    連想配列になるはずで、そうすると VBA 側としては、
    Dictionary などでコレクション管理されているはずでは…?


    なお、先の No34045 のパッケージであれば、

    DECLARE
    inanimal スキーマ名.PKG_SAMPLE.TestArray;
    BEGIN
    inanimal(12) := 'いち';
    inanimal(34) := 'に';
    inanimal(56) := 'さん';
    PKG_SAMPLE.EXAMPLE( inanimal );
    inanimal.DELETE;
    END;

    という動的 SQL 文を作って「cn.Execute SQL」することはできます。

    インジェクション対策や SQL コマンドの長さ制限等を考えると、
    あまり積極的にお奨めできる方法では無いですが…。
違反を報告
引用返信 削除キー/
■34055 / inTopicNo.6)  Re[4]: oo4oのAddTableをADOに移行したい
□投稿者/ Yoshi 一般人(4回)-(2018/11/30(Fri) 17:12:14)
  • アイコン返信が遅くなり申し訳ありません。
    下記2点のことから方針が決まりました。

    > 64bit 版 VBA だと、COM(ActiveX) コンポーネントも 64bit 版が要求されますが、
    > ・ADODB + OLEDB は配列オブジェクトを渡せない
    > ・ODBC でも渡せない
    > ・OO4O は 32bit 版しかない
    > と言うことで、64bit 版 VBA だと、正直どうしようも無いと思います。

    > 32bit 版 Office と 32bit 版 Oracle Client を入れなおすのも選択肢の一つです。
    > そうすれば oo4o を引き続き利用できるので、追加の開発工数が不要になります。

    32bit 版 Office と 32bit 版 Oracle Client を使うことで回避できるのであれば
    おおきな工数をかけてまでする作業ではないとのことです。
    このことから本件については解決になりました。

    このたびはお世話になりました。
    大変助かりました。ありがとうございます。


解決み!
違反を報告
引用返信 削除キー/



トピック内ページ移動 / << 0 >>

このトピックに書きこむ

Mode/  Pass/


- Child Tree -