Excel Add-In for Salesforce Marketing Cloud

Build 20.0.7695

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 Salesforce Marketing Cloud

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
  pEmailAddress = InputBox("EmailAddress:", "Get EmailAddress")
  If pEmailAddress = False Then
    Exit Sub
  End If
  Dim module As New ExcelComModule
  module.SetProviderName ("SFMarketingCloud")
  Cursor = Application.Cursor
  Application.Cursor = xlWait
  Dim nameArray
  nameArray = Array("EmailAddressParam")
  Dim valueArray
  valueArray = Array(pEmailAddress)
  Query = "SELECT Id, Status FROM Subscriber WHERE EmailAddress = @EmailAddressParam"
  module.SetConnectionString ("User=myUser;Password=myPassword;")
  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)

    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))
          Application.ActiveSheet.Cells(RowIndex, columnIndex + 1).Value = module.GetValue(columnIndex)
        End If
      RowIndex = RowIndex + 1
    MsgBox "The SELECT query was successful."
    MsgBox "The SELECT query failed."
  End If
  Application.Cursor = Cursor
  Exit Sub
    MsgBox "ERROR: " & Err.Description
    Application.Cursor = Cursor
  End Sub

Copyright (c) 2021 CData Software, Inc. - All rights reserved.
Build 20.0.7695