[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

詳細

• データソースに接続する前に SQLOpen の第一引数 DSN= で使用するデータソースを定義しておく必要があります。
• サンプルプログラムで使用する SQLOpen、SQLExecQuery、SQLRetrieve、SQLRetrieveToFile、SQLClose は XLODBC アドインに含まれます。この関数を使用可能にするには、[ツール] - [参照設定] コマンドで XLODBC.XLA の参照を設定しておく必要があります。
• XLODBC アドイン関数についての詳細は、[?] - [キーワードで検索]コマンド - [ODBC 関数アドイン] を参照して下さい。
• SQLOpen の第一引数で指定する接続文字列 ("DSN=・・・・・PWD=・・・") は、ご使用になっている ODBC ドライバにより異なります。
• 接続文字列についての詳細は、ご使用になっている ODBC ドライバのオンライン ヘルプを参照して参照していていただくか、ODBC ドライバメーカーにお問い合わせ下さい。

APPNOTE NO: 401674 Additional reference words: macro Addin character