Menu
I have a WPF application where I access a SQLite database via ADO.NET (http://adodotnetsqlite.sourceforge.net/). So far everything works fine, but when I try to execute the following SQL:
sqlite_cmd.CommandText = "CREATE TABLE IF NOT EXISTS notes (id integer primary key, text varchar(100));";
sqlite_cmd.ExecuteNonQuery();
I get the following exception:
An exception of type 'Finisar.SQLite.SQLiteException' occurred in SQLite.NET.dll but was not handled in user code.
Additional information: near "NOT": syntax error
When I remove the IF NOT EXISTS part it works fine, but I want to create the table only if it is not already there. Is there anything I'm doing wrong?
648
Every CREATE DATABASE IF NOT EXISTS statement is replicated, whether or not the database already exists on the source. Similarly, every CREATE TABLE IF NOT EXISTS statement without a SELECT is replicated, whether or not the table already exists on the source.
Which command creates a database only if it does not already exist? The IF NOT EXISTS clause prevents you from an error of creating a new database that already exists in the database server. You cannot have 2 databases with the same name in a MySQL database server.
In standard SQL there is no way to use a table without creating it. There is a shortcut to create table based on a select statement.
This question has some answers which may be helpful. From that question, however, this answer suggests that SQLite 3.3 and above support IF NOT EXISTS.
Based on that question's answers, you could try selecting the COUNT of tables named 'notes' using this (slightly modified) query:
SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='notes';
You can then test the result of that query. If there were 0 results, create the table. Otherwise, don't create the table.
50
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
