Thread: acessing mysql user defined function

I am struggling with something that i thought is simple and straight and forward. I have mysql database and a user defined function in it. I have tested the function in command prompt and works fine but when accessing the function using vb.net application there is a syntax error:;

Imports System.Configuration 

Imports System.Data.DataSet 

Imports MySql.Data.MySqlClient 

Imports MySql.Data.MySqlClient.MySqlConnection 

Imports MySql.Data.MySqlClient.MySqlException

 Imports MySql.Data.MySqlClient.MySqlCommand

 Imports System 

Imports System.Collections.Generic 

Imports System.Text 

Imports System.Data 

Imports MySql.Data 

 Public Class Data  

Dim con = "server= localhost;" _ 

    & "uid=root;" _              

   & "database=doset_Management_System;"     

Dim cn As New MySqlConnection(con) 

Public Function insertlesson(ByVal faciName As String, ByVal faciSname As String, ByVal Cluster As String, ByVal loca As String, ByVal group As String, ByVal start As String, ByVal endtime As String, ByVal weekDay As String, ByVal year As String) As String         



Dim msg As String         

  cn.Open() 

        Try 

            Dim status As String = "world"                        

Dim myCommand As New MySqlCommand("hello('" & status & "')", cn) 

            myCommand.CommandType = CommandType.StoredProcedure 'There is an error here. commandType underlined

             Dim rv As MySqlParameter = myCommand.Parameters.Add("rv", MySqlDbType.String) 

            rv.Direction = ParameterDirection.ReturnValue             

msg = rv.Value             

myCommand.ExecuteNonQuery()         

Catch ex As MySqlException 

            cn.Close() 

            MessageBox.Show("Error number '" & ex.Number & "' has occured. Here is the error: '" & ex.Message & "' ", "Error:")             

Return False         

End Try         

cn.Close()

         Return msg      

End Function

  End Class

first i have this syntax error: 'CommandType' is ambiguous, imported from the namespaces or types 'System.Data, MySql.Data.MySqlClient.MySqlCommand'. I tried to solve the problem by using syntax below:

myCommand.CommandType = System.Data.CommandType.StoredProcedure

That acturaly remove the syntax problem but still i get the error message: Error number 1064 has occured. Here is the error: You have an error in your sql syntax; check your manual that correspond to you sql sever version for the right syntax to use near 'world')" at line 1 Please help!

Have you tried stepping through lines 23-28 to see what myCommand actually contains?

Yes i have tried stepping through stated lines and myCommand is as below:

myCommand {MySql.Data.MySqlClient.MySqlCommand} MySql.Data.MySqlClient.MySqlCommand
CommandText "hello('world')" String
CommandType Text {1} System.Data.CommandType
rv Nothing MySql.Data.MySqlClient.MySqlParameter

This is not helping me much.

Really? What did you type in to the command line? I very much doubt that it looked anything like this!

oh, sorry, i step through run the code and step through using f11. How do i step through the lines using command line? I am running my program in windows environment

Okay. Here's how your enquiry reads to us ...

I have a user defined function in an sql database .... see if you can guess what it does and how it's called.
I have tested it in command prompt ... see if you can guess what I typed.
Here's some code ... feel free to trudge through it and see what the result is.
Now tell me if that result matches the guess you made about what it should like in steps 1 & 2.

We mindreaders like Yoda aren't.

So, I now know what your code produces for myCommand. But what am I supposed to be comparing it to?

Ok, i get what you mean...Thanx for response, i managed to get it working. I have another problem though. Mysql function i created returns a string value. The function is as below:

PHP Code:



CREATE DEFINER=`root`@`localhost` FUNCTION `saveLesson`(`dow` VARCHAR(20), `thiscluster` VARCHAR(20), `location` VARCHAR(20), `stT` TIME, `stP` TIME, `faciName` VARCHAR(200), `faciSname` VARCHAR(200), `group1` VARCHAR(10)) RETURNS varchar(200) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT 'validate and save lesson' BEGIN DECLARE starttime time; 
DECLARE stoptime time;
 DECLARE wday varchar(20); 
DECLARE cluster varchar(20); 
DECLARE loc varchar(20);
 DECLARE faci varchar(20); 
declare num INT; 
declare returnValue varchar(200);
 select ID into faci from facilitator where Firstname = faciName and Surname = faciSname; SELECT count(*) into num from lesson where faciID = faci and Week_Day = dow and (stT BETWEEN start_time AND end_time OR stP BETWEEN start_time AND end_time) AND Year = YEAR(CURDATE());
 if num > 0 then 
set returnValue = "There is a lesson at that time";
 return returnValue;
 end if; 
INSERT INTO lesson (FaciID, Cluster, location, Group1, start_time, end_time, Week_Day, Year) VALUES (faci, thiscluster, location, group1, stT, stP, dow, year(CURDATE())); set returnValue = "lesson saved"; return returnValue; 
end 


Now, in the application side i want to get the message returned by the function. I used the code below:

Dim msg As String

        'Dim dt1 As New DataTable("Student_Registration")

        'Dim faciID As String = findFaciID(Faci)

        cn.Open()

        Try

            Dim status As String = "world"

            'Dim adapter As New MySqlDataAdapter("INSERT INTO lesson(FaciID, Cluster, location, Group1, start_time, end_time, Week_Day, Year) VALUES('" & faciID & "', '" & Cluster & "', '" & loca & "', '" & group & "', '" & start & "', '" & endtime & "','" & weekDay & "','" & year & "')", cn)

            'adapter.Fill(dt)

            'cmd.Connection = cn

            'Dim myCommand As MySqlCommand = New MySqlCommand("saveLesson(tuesday, sefhophe, sefhophe primary school, 08:50:00,09:00:00,dineo, kgabaga, A)", cn)



            Dim myCommand As MySqlCommand = New MySqlCommand()

            myCommand.CommandText = "saveLesson"

            myCommand.CommandType = System.Data.CommandType.StoredProcedure

            myCommand.Connection = cn

            Dim dow As MySqlParameter = myCommand.Parameters.Add("dow", MySqlDbType.VarChar, 20)

            'rv.Direction = ParameterDirection.ReturnValue

            'myCommand.Parameters("s").Direction = ParameterDirection.ReturnValue

            dow.Value = "tuesday"



            Dim thiscluster As MySqlParameter = myCommand.Parameters.Add("thiscluster", MySqlDbType.VarChar, 20)

            thiscluster.Value = "sefhophe"



            Dim location As MySqlParameter = myCommand.Parameters.Add("location", MySqlDbType.VarChar, 20)

            location.Value = "sefhophe primary school"





            Dim stT As MySqlParameter = myCommand.Parameters.Add("stT", MySqlDbType.String)

            stT.Value = "16:00:00"



            Dim stP As MySqlParameter = myCommand.Parameters.Add("stP", MySqlDbType.String)

            stP.Value = "18:00:00"





            Dim faciNam As MySqlParameter = myCommand.Parameters.Add("faciName", MySqlDbType.VarChar, 200)

            faciNam.Value = "dineo"



            Dim faciSnam As MySqlParameter = myCommand.Parameters.Add("faciSname", MySqlDbType.VarChar, 200)

            faciSnam.Value = "kgabaga"



            Dim group1 As MySqlParameter = myCommand.Parameters.Add("group1", MySqlDbType.VarChar, 10)

            group1.Value = "A"





            Dim myRetParam As MySqlParameter = New MySqlParameter()

            myRetParam.Direction = System.Data.ParameterDirection.ReturnValue



            myRetParam.ParameterName = "returnValue"

            myCommand.Parameters.Add(myRetParam)





            MessageBox.Show(myCommand.ExecuteNonQuery())





        Catch ex As MySqlException

            cn.Close()

            MessageBox.Show("Error number '" & ex.Number & "' has occured. Here is the error: '" & ex.Message & "' ", "Error:")

            Return False

        End Try

        cn.Close()

        Return ""

I always receive 0. What might be the problem?

MessageBox.Show(myCommand.ExecuteNonQuery())

This returns the number of rows affected by the command, not the return value from the function.

ok. But how do i return value from the stored function?

The Ins and Outs of Stored Procedures in VB.NET

ok. But how do i return value from the stored function?