| Bug #20084 | SQL works in Query-browser; fails in Connector/NET | ||
|---|---|---|---|
| Submitted: | 26 May 2006 6:50 | Modified: | 2 Jun 2006 9:21 |
| Reporter: | Mark Johnson (Basic Quality Contributor) | ||
| Status: | Not a Bug | ||
| Category: | Connector/Net | Severity: | S3 (Non-critical) |
| Version: | 1.0.7 | OS: | Microsoft Windows (w2k/linux) |
| Assigned to: | Target Version: | ||
[26 May 2006 7:08]
Mark Johnson
The following error was logged: Message: #42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mj_spListRatingsOfRecipe' at line 1 Stack Trace: at MySql.Data.MySqlClient.PacketReader.CheckForError() in d:\Program Files\MySQL\MySQL Connector Net 1.0.7\mysqlclient.MRJ\PacketReader.cs:line 149 at MySql.Data.MySqlClient.PacketReader.ReadHeader() in d:\Program Files\MySQL\MySQL Connector Net 1.0.7\mysqlclient.MRJ\PacketReader.cs:line 117 at MySql.Data.MySqlClient.PacketReader.OpenPacket() in d:\Program Files\MySQL\MySQL Connector Net 1.0.7\mysqlclient.MRJ\PacketReader.cs:line 131 at MySql.Data.MySqlClient.NativeDriver.ReadResult(Int64& affectedRows, Int64& lastInsertId) in d:\Program Files\MySQL\MySQL Connector Net 1.0.7\mysqlclient.MRJ\nativedriver.cs:line 369 at MySql.Data.MySqlClient.CommandResult.ReadNextResult(Boolean isFirst) in d:\Program Files\MySQL\MySQL Connector Net 1.0.7\mysqlclient.MRJ\CommandResult.cs:line 125 at MySql.Data.MySqlClient.CommandResult..ctor(Driver d, Boolean isBinary) in d:\Program Files\MySQL\MySQL Connector Net 1.0.7\mysqlclient.MRJ\CommandResult.cs:line 52 at MySql.Data.MySqlClient.NativeDriver.SendQuery(Byte[] bytes, Int32 length, Boolean consume) in d:\Program Files\MySQL\MySQL Connector Net 1.0.7\mysqlclient.MRJ\nativedriver.cs:line 330 at MySql.Data.MySqlClient.MySqlCommand.GetNextResultSet(MySqlDataReader reader) in d:\Program Files\MySQL\MySQL Connector Net 1.0.7\mysqlclient.MRJ\command.cs:line 295 at MySql.Data.MySqlClient.MySqlDataReader.NextResult() in d:\Program Files\MySQL\MySQL Connector Net 1.0.7\mysqlclient.MRJ\datareader.cs:line 683 at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) in d:\Program Files\MySQL\MySQL Connector Net 1.0.7\mysqlclient.MRJ\command.cs:line 406 at MySql.Data.MySqlClient.MySqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) in d:\Program Files\MySQL\MySQL Connector Net 1.0.7\mysqlclient.MRJ\command.cs:line 370 at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at MRJ.RecipeDB.RecipeRead.MRJ.RecipeDB.IRecipeRead.ListRatingsOfRecipe(Int32 RecipeID, DataSet& list) in g:\develop\recipe\dataTier\RecipeDB\RecipeRead.cs:line 328 However, I can, as shown, call the stored procedure from the mysql command line utility.
[26 May 2006 13:45]
Tonci Grgin
Hi Mark. Thanks for your problem report. Can you please provide me with DDL script and dump containing portion of your data?
[28 May 2006 21:47]
Tonci Grgin
Hi Mark. I would very much like you to post the entire project instead of snippet. I'm especially interested in you connect string! So, please post small but complete C# project showing your problem on data you provided.
[28 May 2006 21:53]
Tonci Grgin
Sorry, this should have been added with the rest of the text... Mark, please dump data in form sutable for import, it will shorten my test time. Changing actual numbers to 'x' is ok as long as you can produce the error on that subset of data with your test case. Example: INSERT INTO Person VALUES (1,'Diana' ,'Johnson' ,'(902) xxx-xxxx','xxx@xxx.xxx'), (2,'Mark' ,'Johnson' ,'(403) xxx-xxxx','mrj001@shaw.ca'), (3,'Stephanie','Johnson' ,'(403) xxx-xxxx','xxx@xxxxl.xxx' ), (4,'Ken' ,'DeleteMe','555-1212' ,'nobody@nowhere.com'), (5,'Jane' ,'Doe' ,NULL ,NULL), (6,'Susan' ,'Muise' ,NULL ,NULL), (7,'Barbie' ,'Doll' ,NULL ,NULL), (8,'Melodee' ,'Richards',NULL ,NULL), (9,'Shauna' ,'Welch' ,NULL ,NULL), (10,'Violi' ,'J' ,NULL ,NULL), (11,'n' ,'n' ,NULL ,NULL); INSERT INTO Ratings VALUES (8,2,5,"0000-00-00 00:00:00"), (7,2,3,"2002-04-28 00:00:00"), (8,4,3,"0000-00-00 00:00:00");
[1 Jun 2006 12:28]
Mark Johnson
bzip2 encoded tar file of project illustrating problem.
Attachment: 20084.tar.bz2 (application/x-tbz, text), 180.12 KiB.
[1 Jun 2006 12:33]
Mark Johnson
I uploaded a file demonstrating the problem. It is a bzip2 compressed tar file. Please see the README file in it. Note that the segment of code used to call this stored procedure is almost the same as that used to call other stored procedures (except for the name of the stored procedure and parameters). Other calls work, while this one fails. I suspected something peculiar to this procedure and tried a few variations on the select statement in it. You will see these in the submitted code (file RecipeRead.cs).
[1 Jun 2006 22:36]
Tonci Grgin
Hi Mark. I found what I was looking for in your connection string. Try this one: "SERVER=munja;DATABASE=Bug20084;PORT=3307;userid=root;password=; pooling=false; Allow Zero Datetime=True" << here Reference in manual: http://dev.mysql.com/doc/refman/5.0/en/connector-net-using-datetime.html Everything works just fine.
[2 Jun 2006 8:03]
Mark Johnson
Thanks. I had missed that. It works now. Please update this issue to "not a bug". (I only have Open and Closed available for status.)
[2 Jun 2006 9:21]
Tonci Grgin
Mark, glad it works!
Description: I have: mysql-standard-5.0.21-linux-i686-glibc23 on Slackware linux 10.2 with kernel 2.6.12 Connector/NET version 1.0.7 on W2k Pro. Mysql-query-browser version 1.1.20 on W2k. The following SQL command works in Query Browser: SELECT r.Rating, r.`Date`, p.FirstName, p.LastName FROM Ratings r, Person p WHERE r.RecipeID = 8 AND r.PersonID = p.id ORDER BY r.Rating DESC, p.LastName, p.FirstName; However, when I call it through Connector/NET, no data table is returned. Here is an example mysql session calling a stored procedure containing the select statement: mj@enterprise:~/develop/recipe/database/mysql$ /usr/local/mysql/bin/mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 42 to server version: 5.0.21-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use Recipe; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> call mj_spListRatingsOfRecipe(8); +--------+---------------------+-----------+----------+ | Rating | Date | FirstName | LastName | +--------+---------------------+-----------+----------+ | 5 | 0000-00-00 00:00:00 | Mark | Johnson | | 3 | 0000-00-00 00:00:00 | Ken | DeleteMe | +--------+---------------------+-----------+----------+ 2 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> quit Bye Here is the code for the stored procedure (from show create procedure): CREATE DEFINER=`root`@`localhost` PROCEDURE `mj_spListRatingsOfRecipe`( RecipeID INT ) BEGIN SELECT r.Rating, r.`Date`, p.FirstName, p.LastName FROM Ratings r, Person p WHERE r.RecipeID = RecipeID AND r.PersonID = p.id ORDER BY r.Rating DESC, p.LastName, p.FirstName; END (Note: I tried changing the name of the parameter to nRecipeID, but this did not work better. (I changed it in both the stored procedure and the C# code too)) Here is some C# code I used with Connector/NET (essentially duplicates a function which works perfectly with another stored procedure using a simple SQL select): void IRecipeRead.ListRatingsOfRecipe(int RecipeID, out DataSet list) { MySqlCommand cmd; MySqlDataAdapter ad; MySqlConnection con; MySqlParameter prm; DataSet ds = null; try { ad = new MySqlDataAdapter(); con = new MySqlConnection(Util.GetConnectionString()); cmd = new MySqlCommand(); cmd.Connection = con; // Tried this first: // cmd.CommandType = CommandType.StoredProcedure; // cmd.CommandText = "mj_spListRatingsOfRecipe"; // prm = cmd.Parameters.Add("RecipeID", MySqlDbType.Int32); // prm.Value = RecipeID; // Tried this second: cmd.CommandType = CommandType.Text; // cmd.CommandText = "SELECT r.Rating, r.Date, p.FirstName, p.LastName FROM Ratings r, Person p WHERE r.RecipeID = 8 AND r.PersonID = p.id ORDER BY r.Rating DESC, p.LastName, p.FirstName;"; // // // Tried this third: cmd.CommandText = "SELECT r.Rating, p.FirstName, p.LastName FROM Ratings r, Person p WHERE r.RecipeID = 8 AND r.PersonID = p.id ORDER BY r.Rating DESC, p.LastName, p.FirstName;"; // prm = cmd.Parameters.Add("RecipeID", MySqlDbType.Int32); // prm.Value = RecipeID; ad.SelectCommand = cmd; ds = new DataSet(); ad.Fill(ds); } catch(Exception exc) { Util.LogError(exc); } list = ds; } How to repeat: see description.