Excel Add-In for Elasticsearch

Build 24.0.9060

Executing Parameterized Queries

Parameterized queries can be reused and mitigate SQL injection attacks. You can execute SELECT and other SQL data manipulation statements as parameterized queries. Parameterized queries take arrays as input.

Example: Searching Elasticsearch

The following example macro executes a parameterized search: When you run the macro, you are prompted to enter a search value.

Sub DoSelectParams()
  On Error GoTo Error
  pIndustry = InputBox("Industry:", "Get Industry")
  If pIndustry = False Then
    Exit Sub
  End If
  Dim module As New ExcelComModule
  module.SetProviderName ("Elasticsearch")
  Cursor = Application.Cursor
  Application.Cursor = xlWait
  Dim nameArray
  nameArray = Array("IndustryParam")
  Dim valueArray
  valueArray = Array(pIndustry)
  Query = "SELECT Id, Name FROM [CData].[Elasticsearch].Employee WHERE Industry = @IndustryParam"
  module.SetConnectionString ("Server=127.0.0.1;Port=9200;")
  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) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9060