Output parameters fail on prepare
Posted: 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:
Here is the proc I'm trying to call.
'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
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$$