Executing Parameterized Queries
Parameterized queries can be reused and mitigate SQL injection attacks. You can execute SELECT Parameterized queries take arrays as input.
Example: Searching Google Search
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
pSearchTerms = InputBox("SearchTerms:", "Get SearchTerms")
If pSearchTerms = False Then
Exit Sub
End If
Dim module As New ExcelComModule
module.SetProviderName ("GoogleSearch")
Cursor = Application.Cursor
Application.Cursor = xlWait
Dim nameArray
nameArray = Array("SearchTermsParam")
Dim valueArray
valueArray = Array(pSearchTerms)
Query = "SELECT DisplayLink, Title FROM WebSearch WHERE SearchTerms = @SearchTermsParam"
module.SetConnectionString ("ApiKey=abc123; CustomSearchID=def456;")
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