C#, MySQL, ADO.NET, delimiter causing syntax error

ado.netcdatabase-restoreMySQLsql-scripts

I have C# code that cycles through .sql files and executes what's inside them to set up a database.

One .sql file is basically as follows:

DROP PROCEDURE IF EXISTS myProc;

DELIMITER $$

CREATE PROCEDURE myProc()
BEGIN
-- procedure stuff goes here
END $$

DELIMITER ;

CALL myProc();

When I input this into the MySQL Query Browser's script window, it runs perfectly… over and over again, just as one would want it to.

However, if I put the string into my IDbCommand and execute it…

connection.Open(); // An IDbConnection
IDbTransaction transaction = connection.BeginTransaction();
using (IDbCommand cmd = connection.CreateCommand())
{
    cmd.Connection = connection;
    cmd.Transaction = transaction;
    cmd.CommandText = line;
    cmd.CommandType = CommandType.Text;

    try
    {
        cmd.ExecuteNonQuery();
    }
    catch (SqlException ex)
    {
        transaction.Rollback();
        return false;
    }
}

transaction.Commit();
connection.Close();

… I get the dreaded exception 1064…

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 $$ CREATE PROCEDURE myProc() BEGIN…

So, the question is… why does MySQL let me do this with no problems, but when I try to run it from C#, it fails? And of course the second question is how I'm supposed to fix it.

Best Answer

I think what you are looking for is this: "Bug #46429: use DELIMITER command in MySql.Data.MySqlClient.MySqlScript"

Related Topic