KB401674 - [XL]XLODBCアドイン関数(255バイト以上のSQL文を実行する方法)

詳細

Excel 5.0 で扱える文字列の制限は、255 バイト（半角 255 文字、全角
127 文字) です。クエリーの内容がこの文字制限を越える時は、クエリーを
垂直方向の配列として指定して下さい。配列として指定した文字列は、最終的に
連結されて、完全な SQL クエリーとして再構成されます。配列を利用するとき、
配列の 1 要素は 127 バイト以下にすることが重要です。

サンプルプログラム

サンプルプログラムは「 SQL Server 」と「 ORACLE 」に対するものを用意しま
した。それぞれのプログラム実行環境は次の通りです。

  SQL Server
    プログラム実行環境
       サーバー       SQLServer
       ODBC ドライバ  SQLServerODBC ドライバ (Excel5.0 添付)
       データソース名 TstSQL
    TstSQL の内容
       サーバー名     serversql1
       ユーザー ID    sa
       データベース   pubs
       パスワード     Excel
  ORACLE
    プログラム実行環境
       サーバー       ORACLE
       ODBC ドライバ  ORACLE Ver. 6 用 ODBC ドライバ (Access2.0 添付)
       データソース名 TstORA
    TstORA の内容
       サーバー名     serverora1
       ユーザー ID    SCOTT
       パスワード     Tiger
 XLODBC アドイン関数を使用したサンプル プログラムは次の通りです。

注意

以下のサンプルプログラムはプログラミング言語の使用方法の一例として紹介
するものであり、内容およびその運用結果に関しては一切の責任を負いません。
また、お客様固有の目的に合わせたプログラムの内容変更は行っておりません
ので、あらかじめご了承ください。

1. 255 バイトを越える SQL 文を配列として送る

次のサンプルプログラムでは、255 バイトを越える SQL 文を配列として
指定し、クエリーを実行しています。配列の 1 要素は 127 バイト以下にすることが重要です。

サンプル 1-1 (SQLServer)

  Sub GetdataSQL255()
  Dim sql(7) As Variant
  Dim con As Variant
  'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
  con = SQLOpen("DSN=TstSQL;UID=sa;DATABASE=pubs;PWD=Excel", , 2)
      '255 バイト以上の SQL 文を 1 要素 127 バイト以下の配列として定義
      sql(1) = "SELECT 売上.注文番号, 売上.書籍番号, 書名.書名, " _
          & "書店.書店番号, 書店.書店名, 売上.数量 FROM pubs.dbo."
      sql(2) = "書店 書店, pubs.dbo.書名 書名, pubs.dbo.売上 売上 " _
          & "WHERE 書名.書籍番号 = 売上.書籍番号 AND 売"
      sql(3) = "上.書店番号 = 書店.書店番号 AND ((売上.注文番号 In " _
          & "('P3087a','P2121','423LL922','423LL930')) AND (売"
      sql(4) = "上.数量>=10) AND (書名.書籍番号='MC3021') OR (書名." _
          & "書籍番号='BU1032') OR (書名.書籍番号='BU1111') OR"
      sql(5) = " (書名.書籍番号='BU2075') OR (書名.書籍番号='BU7832')" _
          & " OR (書名.書籍番号='MC2222') OR (書名.書籍番号="
      sql(6) = "'MC3021') OR (書名.書籍番号='MC3026'))"
  'クエリーの実行
  SQLExecQuery con, sql
  '以前に実行されたクエリーの結果を取得し Sheet1 のセル A1 に貼り付け
  SQLRetrieve con, Range("Sheet1!A1"), , , True
  'データソースとの接続を切断
  SQLClose con
  End Sub

サンプル 1-2 (ORACLE)

  Sub GetdataORA255()
  Dim sql(6) As Variant
  Dim con As Variant
  'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
  con = SQLOpen("DSN=Tstora;UID=SCOTT;PWD=Tiger", , 2)
      '255 バイト以上の SQL 文を 1 要素 127 バイト以下の配列として定義
      sql(1) = "SELECT EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.MGR,"
      sql(2) = "EMP.HIREDATE, EMP.SAL, EMP.COMM, EMP.DEPTNO FROM " _
          & "SCOTT.EMP EMP WHERE (EMP.EMPNO=7369) "
      sql(3) = "AND (EMP.ENAME Like '%S%') OR (EMP.EMPNO=7499) AND " _
          & "(EMP.ENAME Like '%J%') OR"
      sql(4) = "(EMP.EMPNO=7521) AND (EMP.ENAME Like '%K%') OR " _
          & "(EMP.EMPNO=7566) AND (EMP.ENAME Like '%A%') OR"
      sql(5) = "(EMP.EMPNO=7654) AND (EMP.ENAME Like '%D%') OR " _
          & "(EMP.EMPNO=7698) AND (EMP.ENAME Like '%J%') OR"
      sql(6) = "(EMP.EMPNO=7782) AND (EMP.ENAME Like '%M%') OR " _
          & "(EMP.EMPNO=7788) AND (EMP.ENAME Like '%T%')"
  'クエリーの実行
  SQLExecQuery con, sql
  '以前に実行されたクエリーの結果を取得し Sheet1 のセル A1 に貼り付け
  SQLRetrieve con, Range("Sheet1!A1"), , , True
  'データソースとの接続を切断
  SQLClose con
  End Sub

2. 255 バイト以上の SQL 文を 1 要素 127 バイト以下の配列にする関数を作成

次のサンプルプログラムでは、「 255 バイトを越える SQL 文を 1 つの要素に
ついて 127 バイト以下の配列に変換」する関数 QueryToArray を使って
クエリーを実行しています。なお、「サンプル 2-1 」では、SQL 文に全角文字を
使用しているため 1 つの要素を 63 文字に、「サンプル 2-2 」では、SQL 文に
半角文字のみを使用しているため 1 つの要素を 127 文字という設定にして
あります。指定する文字数は必要に応じて変更してください。

サンプル 2-1 (SQLServer)

  Sub GetdataSQL255a()
  Dim con As Variant
  Dim sql As String
  'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
  con = SQLOpen("DSN=TstSQL;UID=sa;DATABASE=pubs;PWD=Excel", , 2)
      '255 バイト以上の SQL 文の定義
      sql = "SELECT 売上.注文番号, 売上.書籍番号, 書名.書名, 書店." _
          & "書店番号, 書店.書店名, 売上.数量 FROM pubs.dbo."
      sql = sql + "書店 書店, pubs.dbo.書名 書名, pubs.dbo.売上 売上 " _
          & "WHERE 書名.書籍番号 = 売上.書籍番号 AND 売"
      sql = sql + "上.書店番号 = 書店.書店番号 AND ((売上.注文番号 In " _
          & "('P3087a','P2121','423LL922','423LL930')) AND (売"
      sql = sql + "上.数量>=10) AND (書名.書籍番号='MC3021') OR (書名." _
          & "書籍番号='BU1032') OR (書名.書籍番号='BU1111') OR"
      sql = sql + " (書名.書籍番号='BU2075') OR (書名.書籍番号=" _
          & "'BU7832') OR (書名.書籍番号='MC2222') OR (書名.書籍番号="
      sql = sql + "'MC3021') OR (書名.書籍番号='MC3026'))"
  '255 バイト以上の SQL 文を自動的に一つの要素 63 文字以下の配列に変換する
  '関数 QueryToArray を実行し リターン値の配列でクエリー実行
  SQLExecQuery con, QueryToArray(sql, 63)
  '以前に実行されたクエリーの結果を取得し Sheet1 の A1 のセルに貼り付け
  SQLRetrieve con, Range("Sheet1!A1"), , , True
  'データソースとの接続を切断
  SQLClose con
  End Sub
  '255 バイトを越える SQL 文を 1 要素 63 文字以下の配列に変換する関数
  Function QueryToArray(Q As String, Optional MaxLength As Variant) As _
      Variant
  Dim Shift, Size, I As Integer
  Dim TmpArr() As String
      '配列の要素の文字列の最大文字数 (MaxLength) が指定されていなかったら
      '最大文字数を 63 characters にセットします
      If IsMissing(MaxLength) Then MaxLength = 63
      '配列のインデックス番号の最小値の取得
      Shift = LBound(Array(1)) - 1
      Size = (Len(Q) + MaxLength) \ MaxLength
      ReDim TmpArr(Size + Shift, 1 + Shift) As String
      For I = 1 To Size
    TmpArr(I + Shift, 1 + Shift) = _
    Mid$(Q, (I - 1) * MaxLength + 1, MaxLength)
      Next I%
      QueryToArray = TmpArr
  End Function

サンプル 2-2 (ORACLE)

  Sub GetdataORA255a()
  Dim con As Variant
  Dim sql As String
  'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
  con = SQLOpen("DSN=Tstora;UID=SCOTT;PWD=Tiger", , 2)
      '255 バイト以上の SQL 文の定義
      sql = "SELECT CUSTOMER.CUSTOMER_ID, CUSTOMER.NAME, CUSTOMER." _
          & "ADDRESS, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIP_CODE"
      sql = sql + ", CUSTOMER.AREA_CODE , CUSTOMER.PHONE_NUMBER, " _
          & "CUSTOMER.SALESPERSON_ID, CUSTOMER.CREDIT_LIMIT, "
      sql = sql + "CUSTOMER.Comments FROM BROWSER.CUSTOMER CUSTOMER " _
          & "WHERE (CUSTOMER.CUSTOMER_ID=100) AND (CUSTOMER.NAME='J%') "
      sql = sql + "OR (CUSTOMER.CUSTOMER_ID=101) AND (CUSTOMER.NAME=" _
          & "'T%') OR (CUSTOMER.CUSTOMER_ID=102) AND (CUSTOMER.NAME="
      sql = sql + "'V%') OR (CUSTOMER.CUSTOMER_ID=103) AND (CUSTOMER." _
          & "NAME='J%') OR (CUSTOMER.CUSTOMER_ID=104) OR (CUSTOMER."
      sql = sql + "CUSTOMER_ID=105) OR (CUSTOMER.CUSTOMER_ID=106) OR " _
          & "(CUSTOMER.CUSTOMER_ID=107) OR (CUSTOMER.CUSTOMER_ID=108)"
  '255 バイト以上の SQL 文を自動的に一つの要素 127 文字以下の配列に変換する
  '関数 QueryToArray を実行し リターン値の配列でクエリー実行
  SQLExecQuery con, sqlQueryToArray(sql, 127)
  '以前に実行されたクエリーの結果を取得し Sheet1 の A1 のセルに貼り付け
  SQLRetrieve con, Range("Sheet1!A1"), , , True
  'データソースとの接続を切断
  SQLClose con
  End Sub
  '255 バイトを越える SQL 文を 1 要素 127 文字以下の配列に変換する関数
  Function sqlQueryToArray(Q As String, Optional MaxLength As Variant) _
      As Variant
  Dim Shift, Size, I As Integer
  Dim TmpArr() As String
      '配列の要素の文字列の最大文字数 (MaxLength) が指定されていなかったら
      '最大文字数を 127 characters にセットします
      If IsMissing(MaxLength) Then MaxLength = 127
      '配列のインデックス番号の最小値の取得
      Shift = LBound(Array(1)) - 1
      Size = (Len(Q) + MaxLength) \ MaxLength
      ReDim TmpArr(Size + Shift, 1 + Shift) As String
      For I = 1 To Size
    TmpArr(I + Shift, 1 + Shift) = _
    Mid$(Q, (I - 1) * MaxLength + 1, MaxLength)
    Sheets("sheet2").Range("a1").Offset(I, 0).Formula = TmpArr(I + _
                     Shift, 1 + Shift)
      Next I%
      sqlQueryToArray = TmpArr
  End Function