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 HDFS
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
pFileId = InputBox("FileId:", "Get FileId")
If pFileId = False Then
Exit Sub
End If
Dim module As New ExcelComModule
module.SetProviderName ("HDFS")
Cursor = Application.Cursor
Application.Cursor = xlWait
Dim nameArray
nameArray = Array("FileIdParam")
Dim valueArray
valueArray = Array(pFileId)
Query = "SELECT FileId, ChildrenNum FROM Files WHERE FileId = @FileIdParam"
module.SetConnectionString ("Host=sandbox-hdp.hortonworks.com;Port=50070;Path=/user/root;")
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