System.Data.SQLite

Please, I need some help with rewriting a sql syntax to accomplish an operation similar to 'insert if not exists'.

To insert a row in a table created using 'create table timeline (ts datetime, name text, val real)' , I execute...

sql  = "select * from timeline ";
sql += "where ts = '" + bdi.Timestamp.ToString("yyyy-MM-dd HH:mm:ss.fff") + "'";
sql += " and name = '" + bdi.TagName + "'";
sdr = mem.ExecuteQuery(sql);
bool exists = sdr.HasRows;
mem.CloseQuery(sdr);
if (!exists) {
  sql = "insert into timeline values (";
  sql += "'" + bdi.Timestamp.ToString("yyyy-MM-dd HH:mm:ss.fff") + "','" + bdi.TagName + "',";
  sql += bdi.Value.ToString().Replace(',', '.') + ")";
  mem.ExecuteNonQuery(sql);
}

Is it possible to rewrite this to execute as only one command? The solutions found by googling I can't get to execute using SQLite. 

TIA, Jacob

 You can run a statement like this:

INSERT INTO Timeline (name, ts)

SELECT @name, @ts

WHERE NOT EXISTS (SELECT 1 FROM Timeline WHERE name=@name AND ts = @ts);

Notice there is no FROM clause, just SELECT.. WHERE.

Also note that you should not use "+=" on strings.  Use StringBuilder or use @"" to create string blocks and substitute stuff with String.Format(), and if the substitutions are values and not dynamic SQL, then use parameterized SQL as in my example.

HTH,

Sam

 Jacob

Security and stability first, you should be using Paramaterized Queries. Robert has an excellent write-up here http://sqlite.phxsoftware.com/forums/t/83.aspx.

If you want unique values in a table, use unique indexes. And to avoid an sql error when attempting to insert, use "insert or ignore".

Notice below I create a table with 3 cols, 2 of which together are a unique index. I use insert or ignore and I do not get an error when I attempt to insert a duplicate. It silently fails. If you remove "or ignore" you will get an sqliteexception.

C:\sqlite>sqlite3.exe testdb.db
SQLite version 3.5.1
Enter ".help" for instructions
sqlite> create table people (lastname text, firstname text, salary double);
sqlite> create unique index people_unique_1 on people (lastname, firstname);
sqlite> insert or ignore into people values ('smith', 'john', 44548.33);
sqlite> select * from people;
smith|john|44548.33
sqlite> insert or ignore into people values ('smith', 'john', 44548.33);
sqlite> select * from people;
smith|john|44548.33
sqlite> insert or ignore into people values ('smith', 'linda', 44548.33);
sqlite> select * from people;
smith|john|44548.33
smith|linda|44548.33
sqlite>

Hi Sam,

Thank you for your reply. Before posting, I tried a similar approach as suggested, but I failed getting it to parse. I can now see the error I did.

I'm having a closer look at the parameterized SQL features - it's a new world to me.  

Thanks again,

Jacob

Hello Nate,

Thank you for the link - I'll definitely use that approach where ever possible.

And the unique index feature is great. I'll possibly omit 'or ignore' because I could utilize the 'not inserted' exception for statistics purposes. It makes it even better than my thoughts on the 'not exists' approach.

Regards,

Jacob