Create a MySql Function from .NET

Kai

Hi,

Im having a problem trying to create a mysql function using vb.net

the command im trying to execute is :

DELIMITER $$
                DROP FUNCTION IF EXISTS `GetEmployeeOverTimeRate`$$
                CREATE FUNCTION  `GetEmployeeOverTimeRate`(employeeID INTEGER) RETURNS decimal(6,2)
                BEGIN
                DECLARE currentRate decimal(6,2);
                SET currentRate = null;

                SELECT COALESCE(HourlyRate2,0.00) into currentRate
                FROM pais_employeesrates p
                WHERE Employee_ID = employeeID
                ORDER BY RateStartDate DESC
                Limit 1;

                return COALESCE(currentRate,0.00);
                END;
                 $$
                DELIMITER ;

using the following vb.net

        mCommandObject = CreateCommand(sql)
        mCommandObject.CommandType = CommandType.Text
        Dim ret As Integer
        ret = Me.mCommandObject.ExecuteNonQuery()

The CreateCommand sets the connection settings and the sql to execute.

The error i get back 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 'DELIMITER $$
DROP FUNCTION IF EXISTS `GetEmployeeOverTimeRate`$' at line 1"


Now im guessing its the DELIMETER statement it is having issues with but i havent been able to find a work around and there is very little on the net about how to do this. Now your probably saying why doesnt he just use mysql command line and script it rather than running it through .net but this is on a distributed system. I cant run the script on all machines as i dont have access to them. I need to do it over .net :mad:

Any ideas?

Kai

Tis fine i got it, spent 2 hours trying different things, Posted and then got the solution 5 minutes later.
Anyway it works fine if you drop the DELIMETER stuff completely

Mr. Flibble

I find that if you put a break point just after you set the sql string, and then view and copy out the string contents and run it directly in MySQL it will show up where the problem lies quickly.

Edit: Err, just copped that you can't run it directly so the above point probably isn't too helpful.