MySQL Bugs: #31522: Cannot use stored procedures with parameters in strongly typed datasets

Bug #31522	Cannot use stored procedures with parameters in strongly typed datasets
Submitted:	11 Oct 2007 7:26	Modified:	1 Mar 2008 9:04
Reporter:	Sarah Green	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.1.2	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Parameter, stored procedure, strongly typed dataset

Description:
Cannot use stored procedures with parameters in strongly typed datasets.

Visual Studio expects parameters to be prefixed with "?" and this doesn't appear to be overridable (even tho' there is an option to prefix parameters in the Tools->Options->Query and View Designers section)

MySQL will not accept "?" as a valid character for prefixing parameters.

How to repeat:
Create a stored procedure with a valid parameter.

Either:
In Visual Studio, add a table adapter specifying "Use existing stored procedures". Select the relevant stored procedure. The table adapter creation wizard terminates without an error.

Or:
In Visual Studio, create a new table adapter using SQL statements rather than stored procedures then view the Properties window for one of the existing queries (e.g. Fill Query). Change the "Command Type" from "Text" to "Stored Procedure". Select the relevant stored procedure from the drop down list which is now available under "Command Text". A pop-up error message is displayed:

"Failed to update CommandText:
The schema of data returned by your new command could not be retrieved. Schema of table "<tablename>" was not changed and might not reflect data retrieved by your command. The database returned the following error:
Parameter '?<parameter name>' not found in the collection."

In both cases the query is not created.

Hi Sarah and thanks for your report. Verified just as described, no matter which options one uses the wizard just disappears...

 - MySQL server 5.0.50pb on WinXP Pro SP2 localhost
 - Latest c/NET 5.1 snapshot installed on clean machine

Does anyone have a workaround for this? Or any idea when it will be fixed?

I can't verify this.  Tonci, can you give me steps to reproduce?

VS working

Attachment: Bug31522.JPG (image/jpeg, text), 53.97 KiB.

Use old syntax=True (use @ instead of ?)

Attachment: Bug31522-1.JPG (image/jpeg, text), 31.93 KiB.

Seems this is present in 5.1.4, see attached images. I would change synopsis to "Table adapter wizard connection string differs from one defined for connection in use" but it's too long.

mysql> create database bug31522;
Query OK, 1 row affected (0.00 sec)

mysql> use bug31522
Database changed
mysql> create table test(Id INT UNSIGNED NOT NULL PRIMARY KEY, SomeTxt VARCHAR(5
0))ENGINE=MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO test VALUES (1, "Some text 1"), (2, "Some text 2"), (3, "Some
 text 3");
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE PROCEDURE b31522 (IN inId INT)
    -> BEGIN
    -> SELECT * FROM test WHERE test.Id >= inId;
    -> END //
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER ;
mysql> CALL b31522(2);
+----+-------------+
| Id | SomeTxt     |
+----+-------------+
|  2 | Some text 2 |
|  3 | Some text 3 |
+----+-------------+
2 rows in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> CALL b31522(1);
+----+-------------+
| Id | SomeTxt     |
+----+-------------+
|  1 | Some text 1 |
|  2 | Some text 2 |
|  3 | Some text 3 |
+----+-------------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Picture 1 connection string properties:
 Ignore prepare=False
 Use Procedure Bodies=True (my account has that privilege
 Use old syntax=False (use ? instead of @)

Add Table Adapter via wizard and do as Sarah described.

For picture two, I changed "Use old syntax" to TRUE but in both cases parameter is described as "?inId", so when running with Preview data one gets error:
testDataSet.b315221.Fill.GetData(@inId) could not be previewed. Parameter ?inID not found in collection.

I think the problem is in that Table adapter configuration wizard, Connection string differs from connection string associated with used connection in exactly ";use old syntax=True":

 - Table adapter configuration wizard, Connection string (expanded):
server=localhost;user id=root;Password=tonchika;persist security info=True;database=bug31522;allow zero datetime=True;character set=latin1;convert zero datetime=True;ignore prepare=False;use procedure bodies=True;logging=True

 - Server explorer, connection, properties, Connect string:
server=localhost;user id=root;persist security info=True;database=bug31522;allow zero datetime=True;character set=latin1;convert zero datetime=True;ignore prepare=False;use procedure bodies=True;logging=True;use old syntax=True

I don't believe this is a bug.  What you are seeing is that the connection string used by table adapter wizard comes from the web.config which is set the first time you use the wizard.  Just because you modify the connection in server explorer (to add or remove use old syntax) doesn't automatically update the web.config.  

My feeling is that the user is getting these connection strings out of sync.  One is using old syntax and one is not.  This of course will cause problems.

I've just tried repeating the problem several times on a later version of my program using 5.1.3 and all is fine. I'm still somewhat mystified as to exactly how I managed to get the connection strings "out of sync" but it seems to have cured itself without my doing anything (or at least without my doing anything intentionally!) Unfortunately I can't remember if I've upgraded since the version I originally reported on so it may well have been cured by the 5.1.3 upgrade. In either case it seems to be fixed in 5.1.3 so suggest this is now closed down.

Seems to have appeared again on 5.2.5,

since 
CREATE PROCEDURE `FOO`(
  IN _MAXCOUNT int
)
BEGIN
    (....code omitted...)
    SET @COUNTER := 0;

    SELECT H_DATE, @COUNTER := @COUNTER +1 AS CNT
    FROM 
    (... code omitted...)

Fails with MySql.Data.MySqlClient.MySqlException: Parameter '@COUNTER' must be defined. Or there is something I am doing wrong ?