Retrieving a Sql command text for logging purposes (Ado via C++/COM)

Question

Hello,

(PS: I am new to this forum, if this question has been hashed over earlier pls do point me to related thread or so forth)

I am using an ADO command object to interact with a sql backend via a stored proc in native code (c++ to be precise). So I prepare the command object and the required set of parameters and invoke the stored procedure. Also note that the prepared property on this command object is set to false, aka results in not having a compiled version of the Command object before execution.

For e.g. the sql command would look something like this, “ exec [dbo].[<spname>] N’%’ …”

Now the command runs successfully and I am able to retrieve the results properly, but we have a requirement to dump the sql query string that’s been generated from the Ado command object to be dumped onto the log file for debugging purposes. So I did something like this

// code sample to invoke a stored proc using ADO

ccpSPCommand (of CComPtr <ADOCommand> type) ; // the command object already has already been prepared with Parameters so it’s not empty

CComBSTR ccpSqlCommand;

If (ccpSPCommand->get_CommandText(&ccpSPCommand) == S_OK)

{

   CComVariant ccpSPCommandText;

  If (ccpSqlCommand.CopyTo(ccpSPCommandText) == S_OK)

 {

   std::wstring sqlCommandString = ccpSqlCommandText.bstrVal;

   Log(L”Stored proc invoked as ‘%s’.”, sqlCommandString.c_str()); // this prints something like Stored proc invoked as '{ ? = call [dbo].[<StoredProcName>](?, ?, ?, ?, ?, ?, ?, ?,…)’

 }

}

Now my question is that this MSDN link (command text property) states that upon invoking this property I am supposed to get a BSTR which contains the sql command text, but as shown in bold above I get ? in place of parameters, is this expected? Am I supposed to iterate through the list of parameters in the command and replace the “?” with the parameter values  just to get the command string?

Is there an alternate method (a simpler solution J) to get the command string? I unfortunately see in msdn that get_CommandText is the closest property that gets me the command string…

Thanks.,

Raghu (SDE, Windows) Microsoft Corp, Redmond

Answer 1

Hi Raghu,

I think this response answered your question, correct?

"It looks you want that the question marks in the query be substituted with actual parameter values. Actually, that kind of behavior is not expected because substitution should be done on server side to 1) mitigate possible SQL injections and 2) improve performance to avoid recompilation of SQL query. The substitution should not be done on client side."

Thanks,

Cathy Miller

Answer 2

Hello Cathy,

I got the expected response in one of internal dl's. Thanks

---

Raghu (SDE, Windows) Microsoft Corp, Redmond