Run MySQL DDL command using C# Application

Question

I am using MySQL database with C# to develop an application.Using MySQL Server 5.0 and odbc connector. In some cases I am required to execute ddl commands such as ALTER TABLE or CREATE TABLE to manipulate the database. In these cases I need to use the IF EXISTS command to check the database before I execute commands. I write below commands that execute without any problem in Navicat or Workbench, but do not work when send this commands with application by ExecuteNoneQury methods.

what is wrong?

use db;
drop procedure if exists  sp_update ;
delimiter //

create procedure sp_update()
begin

     if not exists( SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tab' AND COLUMN_NAME = 'col' and table_schema = 'db') then
       ALTER TABLE `tab` ADD COLUMN `col`  int(11) NULL DEFAULT NULL ;
    end if;

end//

delimiter ;
call sp_update();
drop procedure if exists  sp_update ;

C# Command :

public override int ExecuteNoneQuery(string commandText)
    {
        int obTemp = 0;
        Conn = new MySqlConnection(Connection.ConnectionString);
        try
        {
            MySqlCommand MySqlCommand = new MySqlCommand(commandText, Conn);
            if (Conn.State == ConnectionState.Closed)
            {
                Conn.Open();
            }
            obTemp = MySqlCommand.ExecuteNonQuery();
        }
        finally
        {
            if (Conn.State == ConnectionState.Open)
            {
                Conn.Close();
            }
        }
        return obTemp;
    }

Error 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 // create procedure sp_update() begin if not exists( SELECT' at line 1 — mo.meisam
– mo.meisam, Commented Nov 21, 2011 at 16:20

Vladislav Vaintroub · Accepted Answer · 2011-11-25 20:54:28Z

3

"delimiter" is not MySQL syntax.. It is a convinience function for the mysql command line client and is only understood by it (well, some GUI clients mimic the behavior too, to be able to run scripts that are originally thought for command line client).

But, you do not need "delimiter" in any code executed by connectors. Using it will result in syntax error like the one you got.

answered Nov 25, 2011 at 20:54

Vladislav Vaintroub

5,7581 gold badge27 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marian Zagoruiko Over a year ago

Thank you very much. Saved me a lot of time and nerves.

ahsteele · Accepted Answer · 2011-11-25 20:55:50Z

1

I solved my own problem. I needed to split up my sql command into two parts.

Part 1 create procedure:

drop procedure if exists  sp_update ;
create procedure sp_update()
begin

     if not exists( SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tab' AND COLUMN_NAME = 'col' and table_schema = 'db') then
       ALTER TABLE `tab` ADD COLUMN `col`  int(11) NULL DEFAULT NULL ;
    end if;
end

Part 2:

call sp_update();
drop procedure if exists  sp_update ;

Send each command to MySQL separately.

edited Nov 25, 2011 at 20:55

ahsteele

26.6k28 gold badges139 silver badges256 bronze badges

answered Nov 25, 2011 at 20:46

mo.meisam

431 silver badge7 bronze badges

Collectives™ on Stack Overflow

Run MySQL DDL command using C# Application

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related