Jonas Bezzubovas Jonas Bezzubovas - 26 days ago 7
MySQL Question

What is wrong with my CREATE TABLE MySQL syntax?

I am having some trouble with the CREATE IF NOT EXISTS clause.

I am using a C# application to create a MySQL table, the connection to DB has been established so it's not a problem.

The error I am getting is an exception when I try to execute the query, I get the message:


MySql.Data.MySqlClient.MySqlException (0x80004005): 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 'IF NOT EXISTS(price
VARCHAR, time VARCHAR)' at line 1


In debug mode, the immediate window shows my command string as:


CREATE TABLE ticks_14_11_2016 IF NOT EXISTS(price VARCHAR, time
VARCHAR)


From the examples I have seen, this should be the proper syntax. I am not worried about constraints and keys for the time being, I just need the query to execute...

Also, here is the C# code which I use to build the string and execute query:

string tableName = "ticks_" + getTodayString();
if (databaseClient.IsConnect()) {
string tableString = "CREATE TABLE " + tableName +
" IF NOT EXISTS" +
"(price VARCHAR, " +
"time VARCHAR)";

try
{
var command = databaseClient.Connection.CreateCommand();
command.CommandText = tableString;
command.ExecuteNonQuery();
} catch (Exception e)
{
Console.WriteLine(e);
}
}


The variable databaseClient has a member that is the MySQLConnection object

Also, my server version is: 5.6.28-76.1

jpw jpw
Answer

You have the if not exists in the wrong place, and also, the varchar type needs a mandatory length argument.

A corrected version should be:

CREATE TABLE IF NOT EXISTS ticks_XXXXX (price VARCHAR(10), time VARCHAR(10));

Change the length to whatever is appropriate for you.

For more information see the reference manual.