Output parameters fail on prepare

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
DayOldMeat
Posts: 1
Joined: Tue 15 Jun 2010 22:28

Output parameters fail on prepare

Post by DayOldMeat » Tue 15 Jun 2010 22:58

My code (VB in ASPx application) below fails on the .Prepare. The error message is:

'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; select @OUT_Message' at line 1'

I'm going against MySQL version 5.0. This same code worked in the 4.x version of corelab driver. I'm trying to use the latest version to date. (5.0.93) I found if I predefine the parameters without preparing it works fine, but this driver becomes a lot less useful if I have to change this everywhere. - Help!

VB Code:

Code: Select all

  
            Dim mySQLConn As New MySqlConnection
            Dim mySQLCommand As New MySqlCommand

            Try
                mySQLConn.ConnectionString = WebConfigurationManager.ConnectionStrings("PSOConnectionString").ToString
                mySQLConn.Open()

                mySQLCommand.Connection = mySQLConn
                mySQLCommand.CommandType = System.Data.CommandType.StoredProcedure
                mySQLCommand.ParameterCheck = True
                               mySQLCommand.CommandText = "getTESTOUTPUT"
                mySQLCommand.Prepare()  [b]<---ERROR IS HERE[/b]

                         mySQLCommand.Parameters("IN_MYID").Value = CopyFromProjectUID

                
                Dim parOutUID As New MySqlParameter

                parOutUID.Direction = System.Data.ParameterDirection.Output
                parOutUID.ParameterName = "OUT_Message"
                parOutUID.MySqlType = MySqlType.VarChar
                parOutUID.Size = 200
                mySQLCommand.Parameters.Add(parOutUID)

                parOutUID = Nothing

                mySQLCommand.ExecuteNonQuery()

                Return mySQLCommand.Parameters.Item("OUT_Message").Value

            Catch ex As Exception
                Throw ex

            Finally
                mySQLCommand.Dispose()
                mySQLCommand = Nothing

                mySQLConn.Close()
                mySQLConn.Dispose()
                mySQLConn = Nothing
            End Try
Here is the proc I'm trying to call.

Code: Select all

CREATE DEFINER=`root`@`%` PROCEDURE `getTESTOUTPUT`(IN IN_MyID INT, OUT OUT_Message VARCHAR(200))
BEGIN
     
   
          
		SET OUT_Message = 'Here is a message.';
        
    
END$$

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Wed 16 Jun 2010 16:41

We've reproduced the problem, we will investigate it and inform you about the results.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 09 Jul 2010 17:13

We've fixed the problem with preparation of MySqlCommand executing stored procedures. The fix is available in the latest 5.80.146 build of dotConnect for MySQL. This build can be downloaded from
http://www.devart.com/dotconnect/mysql/download.html
(the trial version) or from Registered Users' Area:
http://secure.devart.com/

For more information on improvements and fixes available in dotConnect for MySQL 5.80.146, please refer to
http://www.devart.com/forums/viewtopic.php?t=18425

Post Reply