CODEWITHSUNDEEP
c#mysqlspecial-characters
oshirwani
oshirwani

Reputation: 13

Column name having @ symbol

I am executing a dynamically generated MySQL query using C# code. An exception gets thrown:

CREATE TABLE dump ("@employee_OID" VARCHAR(50));

"{"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '\"@employee_OID\" VARCHAR(50))' at line 1"}"

I cannot skip the '@' symbol from the Column Name.

Manually, using MySQL Workbench, I can get it executed after executing the following query.

SET sql_mode='ANSI_QUOTES';

Following is the code I am able to produce:

MySqlConnection conn = null;
MySqlCommand cmd = null;
string mySQLConnectionString = ConfigurationManager.AppSettings["MySQLAutomationServerConnectionString"];
//Dynamically getting generated using some other code
string sqlQueryString = "CREATE TABLE dump ("@employee_OID" VARCHAR(50));";

try
{
    conn = new MySqlConnection(mySQLConnectionString);
    conn.Open();
    cmd = new MySqlCommand(sqlQueryString, conn);
    executionResultStatus = cmd.ExecuteNonQuery();

}
catch (MySqlException ex)
{
    Console.WriteLine("MySQL Exception: " + ex.Message.ToString());
}

I have tried to execute the "SET sql_mode='ANSI_QUOTES';" query before this gets executed using the code right before executing this code, its not working. What shall I do?

Upvotes: 0

Views: 67

Answers (1)

Steve
Steve

Reputation: 216323

Change your command to 

string sqlQueryString = "CREATE TABLE dump (`@employee_OID` VARCHAR(50));";

Notice how there are two backticks before and after the problematic name (ALT+096)
This will allow your engine to ignore the character @ used to identify parameters

Upvotes: 1

Related Questions