Jason O. Jason O. - 3 years ago 246
Javascript Question

Inserting timestamp in sqlite in Javascript

I want Sqlite to autopopulate timestamp, but Javascript doesn't allow me to pass only 2 values (like

VALUES (?, ?)
) and let Sqlite handle the 3rd value for the timestamp. I know I can provide the 3rd value with
new Date()
but this is not the preferred way.

If I run the below code as is, the timestamp column is all null.

What is the right way to do this?

db.serialize(function () {
db.run("CREATE TABLE if not exists songs (title TEXT, lyric TEXT, timestamp DATATIME DEFAULT CURRENT_TIMESTAMP)");

var stmt = db.prepare("INSERT INTO songs VALUES (?, ?, ?)");
for (var i = 0; i < 10; i++) {
stmt.run("Title" + i, "Lyric" + i);
}
stmt.finalize();

});

Answer Source

If you want to use the default value for a column then just leave it out of the INSERT:

var stmt = db.prepare("INSERT INTO songs (title, lyric) VALUES (?, ?)");

Two things to note here:

  1. Specifying the column names in an INSERT statement is always a good idea, hence the (title, lyric) addition. This way there is no ambiguity as to what the placeholders are referring to.
  2. You're only specifying two columns (and letting the third use its default value) so there are only two placeholders.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download