acessing mysql user defined function

[oratile]

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!

 

[Dunfiddlin]

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

 

[oratile]

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.

 

[Dunfiddlin]

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

 

[oratile]

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

 

[Dunfiddlin]

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?

 

[oratile]

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:

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?

 

[Dunfiddlin]

MessageBox.Show(myCommand.ExecuteNonQuery())

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

 

[oratile]

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

 

[Dunfiddlin]

The Ins and Outs of Stored Procedures in VB.NET

 

[oratile]

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