MySQL Bugs: #44985: No parameters from stored procedures when using ADO .NET Data Entities

Bug #44985	No parameters from stored procedures when using ADO .NET Data Entities
Submitted:	20 May 2009 13:43	Modified:	14 Dec 2009 15:52
Reporter:	Morten Andersen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.0.4	OS:	Windows (Vista 32)
Assigned to:	Reggie Burnett	CPU Architecture:	Any
Tags:	ADO, data entities, entities, entity, regression, stored procedures

Description:
When I create function imports in the Model Browser in Visual Studio .NET 2008 the properties is not retrieved. All methods are imported, but none have any input parameters.

How to repeat:
1. Create a stored procedure with input parameters
2. Add your stored procedure to your data entity scheme
3. Create a function import
4. Now, try to use the entity class that is generated. You should see the method there, with the correct return type, but no input parameters

SShot of problem.

Attachment: Bug44958.jpg (image/jpeg, text), 20.76 KiB.

Hi Morten and thanks for your report. Verified as described. Might add my connection string includes "use procedure bodies = true".

I used remote MySQL server 5.1.31 and functioning model with table main (ID Int not null autoincrement primary key, Name varchar(50)) and following SP:
CREATE PROCEDURE `playground`.`fill_main` (IN startval INT)
    BEGIN
       set @x=startval;
       REPEAT
         INSERT INTO main VALUES(NULL, @x);
         SET @x=@x+1;
       UNTIL @x = 100
       END REPEAT;
     END $$

As can be seen in attached screen shot, there are no parameters in this SP discovered.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/81319

747 Reggie Burnett	2009-08-21
      - fixed sql bug in our 6.0 table column ssdl.  I don't see how this would affect 
        stored procedure parameters but bug #44985 is now fixed

fixed in 6.0.5 and 6.1.2+

Not sure how this patch fixes it but with this patch it now works as expected.

An entry was added to the 6.0.5 and 6.1.2 changelogs:

Input parameters were missing from Stored Procedures when using them with ADO.NET Data Entities.

This bug is back in 6.1.3 with VS2008SP1.

Test Case:

mysql> create database Test;
Query OK, 1 row affected (0.06 sec)

mysql> use Test;
Database changed
mysql> create table MyTest (Id int unsigned auto_increment, primary key (Id));
Query OK, 0 rows affected (0.08 sec)

mysql> delimiter //
mysql> create procedure add_MyTest(inout Id int unsigned) begin insert MyTest values (default); select LAST_INSERT_ID() into Id; end //
Query OK, 0 rows affected (0.05 sec)

mysql> delimiter ;
mysql> select * from MyTest;
Empty set (0.00 sec)

mysql> set @Id = 0;    
Query OK, 0 rows affected (0.00 sec)

mysql> call add_MyTest(@Id);
Query OK, 0 rows affected (0.06 sec)

mysql> select @Id;
+------+
| @Id  |
+------+
| 1    | 
+------+
1 row in set (0.00 sec)

mysql> select * from MyTest;
+----+
| Id |
+----+
|  1 | 
+----+
1 row in set (0.00 sec)

mysql> 

Create an "ADO.NET Entity Data Model" of the Test database, checking all Tables / Stored Procedures.
TestModel.edmx will come up after the wizard is finished.
Right-click on MyTest table, and choose Stored Procedure Mapping.
Under Function, click the box titled "<Select Insert Function>", select add_MyTest from the drop-down list.
No parameters are available.

Err, I named my model TestModel.edmx. The default name is Model1.edmx.

This is repeatable using my previous test in VS2008, .NET FW 3.5, c/NET 6.2.1. There are no parameters to be seen.

Same as before

Attachment: bug44985-1.jpg (image/jpeg, text), 23.63 KiB.

Bug#49606 was marked as duplicate of this report.

This is not a bug.  Now let me explain.  :)

The way entity framework works is that things like tables, columns, foreign keys, stored procedures, parameters, etc have to be discoverable via SQL.  MySql version 5.x don't support discovering parameters via sql.  MySql 6.0 included the parameters i_s table so our parameters support is enabled only for 6.0 and higher.  Since 6.0 has been pulled I've been told that those changes will reappear in a 5.5 release.

I have created a worklog to add some stored procedures that will enable features like this on server 5.0, 5.1, and 5.4.  I'm not 100% sure we can do that but we will try.

What I'm gathering is that this is broken for all current release versions of MySQL. Why can't use just have the connector code run SHOW CREATE PROCEDURE and parse it for parameters? This would be an across the board solution, no?

Has a workaround been implemented for this bug? I still see the same behavior on VS2010 and latest .Net Connector.

Is there a solution for this bug yet? I've just downloaded the latest version of the connector and I still have the same problem.

I am using MySQL Connector 6.3.6 and Visual Studio 2010 Professional and I am still seeing the bug as described. Was this bug fixed on this new release? If not, is there a workaround?

After the function is imported on VS2010, I can call the function: context.MyFunction()..... but it doesn't accept any parameter (which in reality my stored procedure accepts an INT input with a string output).

Any advice would be appreciated.

This issue really hampers using MySQL with the ADO.NET EF in real-world scenarios - procedure mapping is (among other things) a critical way out of edge cases where the framework will do something truly horrible from a performance perspective. Are there any workarounds or ways forward identified for this problem?

This problem still exists in the latest versions of VS2010, MySQL Connector/.NET 6.3.6, and MySQL Server 5.5.8.

If Oracle is serious about attempting to sway Microsoft SQL / .NET developers over to MySQL they better get serious about providing proper support for stored procedures in .NET and Entity Framework.  Very disappointed with the weak response from Oracle regarding this "deal breaker" gap in functionality.  My trust for Oracle in a .NET world just went down several notches.

This is still occurring on my setup when adding a stored procedure which doesn't return
anything.

MySql connector 6.4.3
Windows 7 x86
Visual Studio 2010 SP1
Entity Framework 4.1

This is absolutely pathetic, just like the rest of MySQL's support for most stock-standards DBMS functions. And you wonder why most developers LOVE using SQL Server...

Seriously... this is still broken?  Why support MySql at all?