How can I view the SQL code of my query after it has been parameterized by Parameters.Append
Add a comment
|
2 Answers
You can't.
Parametrized Queries are not built like a string that you could output when ready.
It's always a two-step process:
- Prepare a query in the from of "SELECT foo FROM foo_table WHERE id = ?", send it to the server (and get an identifier back).
- Send all the parameters to fill the question marks to the server, along with the identifier of the prepared statement.
At no point in time the two (query and parameters) get in touch outside of the database server. (This is the reason why parametrized queries are much more secure than hand-made SQL strings).
2 Comments
Tomalak
It has nothing to do with ASP. This is the general way things go with parametrized queries.
You can write some simple code to parse the results back out after a fashion by looping through the parameters. Here is a VBScript / Classic ASP example:
queryPlain = command.CommandText
For Each p In command.Parameters
If (p.type = adChar) or (p.type = adBSTR) or (p.type = adDBDate) Then
queryPlain = Replace(queryPlain , "?", "'" & p.value & "'",1,1)
Else
queryPlain = Replace(queryPlain , "?", p.value,1,1)
End If
Next
Here is something similar for VB.net
queryPlain = command.CommandText
For Each p As SqlParameter In command.Parameters
queryPlain = queryPlain.Replace(p.ParameterName, p.Value.ToString())
Next
Now queryPlain contains the SQL including the parameters. I find this useful for debugging purposes.
1 Comment
dakab
Neat idea, works straightforward. Could further be tweaked by checking for NULL, prepending p.name and adding other string types. Otherwise, a useful means for inspecting command parameters.
