CData Excel Add-In for Amazon Athena 2019J - Online Help

パラメータ化されたクエリの実行

CData Excel Add-In for Amazon Athena 2019J - Build 19.0.7354

パラメータ化されたクエリは、再利用してSQL インジェクション攻撃を軽減できます。SELECT 実行できます。パラメータ化されたクエリは配列を入力として受け取ります。

例:Amazon Athena の検索

次のマクロ例は、パラメータ化された検索を実行します。マクロを実行すると、検索値を入力するよう求められます。

Sub DoSelectParams()
  On Error GoTo Error
  pCustomerId = InputBox("CustomerId:", "Get CustomerId")
  If pCustomerId = False Then
    Exit Sub
  End If
  Dim module As New ExcelComModule
  module.SetProviderName ("AmazonAthena")
  Cursor = Application.Cursor
  Application.Cursor = xlWait
  Dim nameArray
  nameArray = Array("CustomerIdParam")
  Dim valueArray
  valueArray = Array(pCustomerId)
  Query = "SELECT Name, TotalDue FROM Customers WHERE CustomerId = @CustomerIdParam"
  module.SetConnectionString ("AccessKey='a123';SecretKey='s123';Region='IRELAND';Database='sampledb';S3StagingDirectory='s3://bucket/staging/';")
  If module.Select(Query, nameArray, valueArray) Then
    Dim ColumnCount As Integer
    ColumnCount = module.GetColumnCount
    For Count = 0 To ColumnCount - 1
      Application.ActiveSheet.Cells(1, Count + 1).Value = module.GetColumnName(Count)
    Next

    Dim RowIndex As Integer
    RowIndex = 2
    While (Not module.EOF)
      For columnIndex = 0 To ColumnCount - 1
        If Conversion.CInt(module.GetColumnType(columnIndex)) = Conversion.CInt(vbDate) And Not IsNull(module.GetValue(columnIndex)) Then
          Application.ActiveSheet.Cells(RowIndex, columnIndex + 1).Value = Conversion.CDate(module.GetValue(columnIndex))
        Else
          Application.ActiveSheet.Cells(RowIndex, columnIndex + 1).Value = module.GetValue(columnIndex)
        End If
      Next
      module.MoveNext
      RowIndex = RowIndex + 1
    Wend
    MsgBox "The SELECT query was successful."
  Else
    MsgBox "The SELECT query failed."
  End If
  module.Close
  Application.Cursor = Cursor
  Exit Sub
Error:
    MsgBox "ERROR: " & Err.Description
    module.Close
    Application.Cursor = Cursor
  End Sub

 
 
Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 19.0.7354.0