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 15:43	Modified:	14 Dec 2009 16:52
Reporter:	Morten Andersen
Status:	Not a Bug
Category:	Connector/Net	Severity:	S2 (Serious)
Version:	6.0.4	OS:	Microsoft Windows (Vista 32)
Assigned to:	Reggie Burnett	Target Version:
Tags:	regression, entities, entity, data entities, stored procedures, ADO
Triage:	D3 (Medium)

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?