0

I am struggling with proper parameter passing to a MySQL query. In MySQL workbench, my query works fine, but not in the C# code. I assume it is due to wrong parameter passing.

That's why I'd like to see what precisely do I pass to the cmd.ExecuteScalar() method. But I can't figure out how to determine the cmd string.

In debugger I only get query with formal parameters, not passed ones. And even by using cmd.ToString() I get this nonsense:

MySql.Data.MySqlClient.MySqlCommand.

Here is my code:

string timeStampStr = timeStamp.ToString("yyyy-MM-dd hh:mm:ss");
...
MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM plc WHERE plc.last_communication < @timeThreshold AND plc.id = @plcId", _conn);

cmd.Parameters.AddWithValue("@timeThreshold", timeStampStr); // Is this correct ? timeStampStr is a string
cmd.Parameters.AddWithValue("@plcId", plcId);
object result = cmd.ExecuteScalar();

Thank you !

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sold Out
  • 1,321
  • 14
  • 34

1 Answers1

3

Your best bet is probably to enable the query log on MySQL and use that to profile what was sent to the database engine.

This is because the application code doesn't actually replace the placeholders with the parameter values, the database engine does. The application code invokes the parameterized query and supplies the parameters simultaneously. (As a bit of a side-effect, this allows database engines to cache execution plans for parameterized queries much more effectively, since the query itself doesn't change. This provides a slight performance improvement when using parameterized queries over concatenated values.)

And even by using cmd.ToString() I get this nonsence: MySql.Data.MySqlClient.MySqlCommand.

That's not nonsense, that's the name of the class on which you're calling .ToString(). The default behavior of .ToString() for reference types is to return the name of the type, unless you override it.

David
  • 208,112
  • 36
  • 198
  • 279
  • In the end I found the string parameter value in clients debuger. It looks to be correct, but as stated above, I don't know what exactly happens on the server's DB engine. This article seems to be closely related: http://stackoverflow.com/questions/7772815/a-way-to-see-query-after-parameters-are-applied The answer by Christopher Thomas Nico suggests client debugging as follows: string tmp = command.CommandText.ToString(); foreach (SqlParameter p in cmd.Parameters) { tmp = tmp.Replace('@' + p.ParameterName.ToString(),"'" + p.Value.ToString() + "'"); } – Sold Out Sep 16 '14 at 13:02
  • @Peter: You found the string parameter, sure. That's in the `timeStampStr` variable. But what I'm suggesting is that you profile the database engine itself to see exactly how it's receiving the query. You can then replicate that query as-is directly against the database and entirely eliminate the application code as the variable, which would help you in debugging. – David Sep 16 '14 at 13:04
  • @Peter: That method of debugging will show you something *similar* to what the database engine receives, but it won't be exact. Debugging is a whole lot easier when you examine the *actual* values instead of different values that you assume are "close enough." – David Sep 16 '14 at 13:06
  • @David Yes I got that suggestion with DB engine logging @ first time, thank you for your time :) I am quite unpleasantly surprised with .NET and MySQL cooperation, as I used C++ / SQLite before and there was no doubt if server receives precisely what I sent it.. :-) I dont see, why should there by any ... – Sold Out Sep 16 '14 at 13:16
  • 1
    @Peter: I think you're misunderstanding what's happening here. The server *is* receiving precisely what you send it, there is no doubt regarding that. You're sending it the *exact string* of that parameterized query, and you're sending it those *exact* parameters. The code isn't trying to trick you, it's doing what you tell it to do. Regarding your debugging effort, best of luck with that. But regarding the question, your best bet is to examine the query from the perspective of the database rather than code. (The same is true of SQL Server, using the Profiler tool.) – David Sep 16 '14 at 13:22
  • @David Yap, I see your point. And I acknowledge :) I wonder whether the problem could be with incompliant server OS version. I did not choose it, but it is XP 64bit... There are reported problems with MySQL WorkBench running on XP 64 bit. See here: http://bugs.mysql.com/bug.php?id=71090 3 of us were not able to start debug logs, due to known bugs/OS inconsistences. We'll try same thing on different OS. And I wonder, if same client code will run smoothly on that one. Many thanks to all of you for time and support ! – Sold Out Sep 19 '14 at 10:51