C#, MySQL, ADO.NET, delimiter causing syntax error
ado.net, c#, database-restore, mysql, sql-scripts
Solution
I think what you are looking for is this: "Bug #46429: use DELIMITER command in MySql.Data.MySqlClient.MySqlScript"
Problem
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.