MySQL Bugs: #39409: Error creating dataset from stored procedure

Bug #39409	Error creating dataset from stored procedure
Submitted:	11 Sep 2008 19:41	Modified:	26 May 2009 13:04
Reporter:	Mike Hat	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	5.2.5	OS:	Windows (revision 1411)
Assigned to:	Reggie Burnett	CPU Architecture:	Any
Tags:	dataset, Parameters, Server Explorer, stored procedures, table adapter

Description:
When I create a tableadapter on a Dataset I cannot use a stored procedure with variables, i get the following error: "The method or operation is not implemented"

before 1410 I got "Column 'ROUTINE_TYPE' does not belong to table Procedure Parameters".

Seems that Bug #39252 (http://bugs.mysql.com/bug.php?id=39252) is allready documented and its not exactly about this so im opening this new bug report.

This was on the 5.2.3 release and suposely fixed on revision 1410, I still get half of the problems.

I'm building the mysql.data and mysql.visualstudio dlls from revision 1411, creating an installer from the svn tree 5.2 revision 1411.

Im using this simple SP:

PROCEDURE IdentProducer_user (IN _user_id LONG)
BEGIN  
SELECT entidad.razon_social, entidad.password FROM entidad where entidad.entidad_id = _user_id;
END

How to repeat:

* Build revision 1411 from 5.2 branch
* create a dataset
* add a tableadapter
* Use an existing stored procedure

Error window when adding a tableadapter

Attachment: error1.jpg (image/jpeg, text), 94.90 KiB.

Hi Mike and thanks for your report. I still think it's duplicate but will give it a try. Can you please rebuild Rev. 1413 (or up) as afais changes were merged there.
Inform me of result.

I compiled 1413 and 1417 (no much difference there) and now i cannot even start to use de mysql.visualstudio.dll, i get an error from VS2008 when i even go to the server explorer (attached image below)

Error window when i try to open the server explorer

Attachment: error1.jpg (image/jpeg, text), 51.15 KiB.

I'm surprised you were even able to build the DDEX provider.  It references our package load key which is not distributed via scm.  The compile should have failed when it couldn't find it.

I used the key that was distributed with the 5.2.3 source, perhaps it should not be there?

The installer worked fine from the 1410 and 1411 revisions but with the change from globalinstaller to wix registry (1413) i found that, even when i can build the installer, the mysql.visualstudio.dll has some misconfiguration with the VS2008 or something, I get the error message from the image i posted before.

I tried to see if I could find a working couple:

* connector 5.2.3(relase) + Mysql 5.1(rel) -> no good, hangs when choosing a SP with options (creating a tableadapter)

* connector 5.2.3(1411) + Mysql 5.1(rel) -> cant complete the create tableadapter wizard

* connector 5.2.3(1418) + Mysql 5.1(rel) -> no good, cant make it work with visual studio, not even debug it

* connector 5.2.3(release) + Mysql 6.0(Alpha) -> no good, hangs when choosing a SP with options (creating a tableadapter) just like with mysql 5.1

* connector 5.2.3(1411) + Mysql 6.0(rel) -> no good, hangs when choosing a SP with options (creating a tableadapter), or says "Error Message: Value cannot be null. Parameter name: key"

Ohh perhaps you mean the key built with the sn.exe. I built my own and compiled mysql.data.dll and mysql.visualstudio.dll with it.

Compiling right out of svn is not always guaranteed to work (as you are discovering).  Sometimes I fix bugs and then have to go back and clean up things when I release products.  I may have to fix a few small things related to my removal of globalinstaller.

I dont need the latest code, Im not that needy.

Just wondering how to get the SP with variables working, since it would simplify my work a lot.

I tried all posibilities because in the other bug report this was supposed to work  with mysql 6.0 on release version, but didnt, and neither it does with the latest code added to the connector code (not installer) rev [1410] or [1411].

I really dont need the installer that much, I could checkout the old installer from revision 1410 and use it with the new fix yet to come :'(

Will this issue be addressed? I remember putting a bugreport about this 6 months ago :(

It would be great if it would work at least with a single Mysql Server - Mysql connector combo version, that will be used on the develop machine.

I use MySql .Net Connector 5.2.5 on Visual Studio 2008 dataset designer and also experience failure when adding a query. What happens is the dialog is just closed when I select one of the Stored Procedure, but it is not always.

The process will success if the stored procedure is simple i.e. only contains a single select. But if they consist of more complex statements the failure occur.

example of SP that works fine:

CREATE DEFINER=`root`@`localhost` PROCEDURE `Get_IdGenre`(
  pNamaGenre nvarchar(64)
  )
BEGIN

  select IdGenre from Lst_Genre
    where NamaGenre = pNamaGenre;

END

and this is example more complex SP that failures to add:

CREATE DEFINER=`root`@`localhost` PROCEDURE `do_BuatSession`(
  pIdPetugas int,
  pIdPelanggan int,
  pTanggal datetime
  )
body:
BEGIN
  if (select count(*) from T_Session
    where IdPelanggan = pIdPelanggan and StatusSession = 70000) != 0 then
    leave body;
  end if;

  insert into T_Session
      (IdSession, IdPetugas, IdPelanggan, Tanggal, StatusSession)
    values
      (null, pIdPetugas, pIdPelanggan, pTanggal, 70000);

END

I've try using MySql 6.0 and got the same behaviour. I also tried with some SP and always got failure when the SP have more than just select statements.

I post this information because I don't find other reports/comments include this observation.

Thank You

Mike, I believe Reggie is busy on other things now, so no definite answer.
Raymon, test your SP's in mysql cmd line client and see if the second one actually works there.

Tonci,
The SP works fine in MySql command line client.
I also made a small and simple database (backup is attached bellow) that repeats the behaviour.
Thank You

small database that repeats the behaviour

Attachment: testdb.zip (application/x-zip-compressed, text), 1018 bytes.

In one of the previous posts it states that there is a problem with more complex stored procedures.  I found that on a complex stored procedure - specifically one where I'm inserting records and NOT returning anything - that if I put a statement at the end of that stored procedure such as:
"SELECT LAST_INSERT_ID() LastID;" that visual studio gets a little farther with defining a tableAdapter.

In other words, visual studio (or the MySQL.Net connector) behaves better if your stored procedures return a value.

Problem still exists where the "TableAdapter Query Configuration Wizard" returns an error simmilar to this:
   The wizard detected the following problems when configuring TableAdapter query ....
   Details:
      Generated SELECT statement.
      The method or operation is not implemented.

I am using version 5.2.5 while getting the error message stated in the last comment.

Just curious... Is this bug being worked on?  (or will it be in the future?)

Thanks

Sorry to post so much.

Just another observation....

BEFORE Stored Procedure Definition:
----------------------------------------
CREATE PROCEDURE `schema`.`CreateMeeting` (MeetingDate Date, GroupID int,
                             StrictAttendeeList boolean, AddAttendees boolean,
                             MarkAs char(7), Location varchar(80))

AFTER Definition (which works):
-----------------------------------
CREATE PROCEDURE `schema`.`CreateMeeting` (MeetingDate Date, GroupID int,
                             StrictAttendeeList int, AddAttendees int,
                             MarkAs char(7), Location varchar(80))

Visual studio does not seem to like "boolean" parameters.  Changed them to "int" and worked like a charm.  (still received some type of warning message, but it works).

So the work-around for me was:
1. Make sure the Stored Procedure returns a value.  ie. "Select 1 ReturnValue;"
2. Use only certain parameter types.  (sorry haven't tested what these are - other than "boolean" doesn't seem to be supported)
3. Close and then re-start Visual Studio
4. Add your Stored Procedure Query to your .xsd in Visual Studio

Using Raymond's tesdb I am able to repeat the problem with:
  o MySQL server 6.0.10x64 on W2K8x64 localhost
  o VS2005 Pro
  o c/NET 5.2.5GA

Reggie, I have tons of screenshots if needs be.

What seems to be wrong in the formed query against I__S:
2 Query	SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA LIKE 'testdb' AND SPECIFIC_NAME LIKE 'GetNameId' AND ROUTINE_TYPE LIKE 'PROCEDURE'
*works*

2 Query	SELECT * FROM mysql.proc WHERE 1=1 AND db LIKE 'testdb' AND name LIKE 'GetNameId'
*works*

SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA LIKE '`testdb`' AND SPECIFIC_NAME LIKE '`GetNameId`'
this one *fails* due to double quoting ('`Get).

Forgot to say, this is exact sequence of commands issued against server while I was testing.

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/73431

fixed in 5.2.7

Not 100% sure that this fix will address all the issues here but I was able to reproduce an issue with using nvarchar and nchar parameters and addressed that issue.  That fixed my issue with raymond's database.

An entry was added to the 5.2.7 changelog:

When a TableAdapter was created on a DataSet, it was not possible to use a stored procedure with variables. The following error was generated:

The method or operation is not implemented

Please reopen this bug - I have just encountered it on 6.0.4.

Hello everyone, sorry for my poor English.
I have also had problems with the tableadapters to get data from stored procedures, when the stored procedure contains variables or perform some operations. to work, I add the tableadapter even with error, then I add the columns manually and parameters ... then the tableadapter works properly.