After Connecting from VBA, call the Select method to execute SQL SELECT statements.

public bool Select(string queryString, object paramNames, object paramValues)

The Select method returns a boolean indicating the query's success or failure and accepts the following parameters:

queryString	The query to execute; for example SELECT Id, Name FROM [CData].[Elasticsearch].Employee WHERE Industry = 'Floppy Disks'
paramNames	An array with the named parameters used in the query. Required if using a parameterized query.
paramValues	An array with the values that correspond to the parameter names. Required if using a parameterized query.

Accessing the Results

To return a field from the results, call the module's GetColumnName and GetValue methods. Call MoveNext to iterate over the results. The following example also contains some additional logic to write rows to the sheet.

• List the columns in the first row of the spreadsheet:
  

  Dim ColumnCount As Integer
  ColumnCount = module.GetColumnCount
  For Count = 0 To ColumnCount - 1
    Application.ActiveSheet.Cells(1, Count + 1).Value = module.GetColumnName(Count)
  Next 

• Start outputting each row at row 2 of the spreadsheet. Additional logic checks for null values and date types.
  

  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

Example

The following is a simple SELECT example. See Executing Parameterized Queries for an example macro that searches Elasticsearch.

Sub DoSelect()
  On Error GoTo Error
  Dim module As New ExcelComModule
  module.SetProviderName ("Elasticsearch")
  Cursor = Application.Cursor
  Application.Cursor = xlWait
  Dim nameArray,valueArray

  Query = "SELECT Id, Name FROM [CData].[Elasticsearch].Employee LIMIT 10"
  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 = 10
    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
  Application.Cursor = Cursor
  module.Close
  Exit Sub
Error:
  MsgBox "ERROR: " & Err.Description
  Application.Cursor = Cursor
  module.Close
End Sub