CData Excel Add-In for Google BigQuery 2019 - Online Help
Questions / Feedback?

Executing Parameterized Queries

CData Excel Add-In for Google BigQuery 2019 - Build 19.0.7354

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 Google BigQuery

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
  prepository.name = InputBox("repository.name:", "Get repository.name")
  If prepository.name = False Then
    Exit Sub
  End If
  Dim module As New ExcelComModule
  module.SetProviderName ("GoogleBigQuery")
  Cursor = Application.Cursor
  Application.Cursor = xlWait
  Dim nameArray
  nameArray = Array("repository.nameParam")
  Dim valueArray
  valueArray = Array(prepository.name)
  Query = "SELECT actor.attributes.email, repository.name FROM publicdata.samples.github_nested WHERE repository.name = @repository.nameParam"
  module.SetConnectionString ("ProjectId=NameOfProject;DatasetId=NameOfDataset;")
  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