How do you put a SQLite index into a variable with Nodejs?

Question

I am using express and nodejs to make a chatroom for a homework problem, and I have the messages saved in a sql .db file. The indices have an integer primary key that is autoincrimented, with 3 more columns of text information: room, nickname, and body. Here's some code where I try and debug:

app.post('/messages', function(request, response){
if(request.body.username == ''){
    var username = "Anonymous";
}

else {
var username = request.body.username;
}
var msg = request.body.msg;
conn.query('INSERT INTO messages(id, room, nickname, body) VALUES ($1, $2, $3, $4);', [n, room1, username, msg]); //room, name, text

var out = conn.query('SELECT body FROM messages WHERE id = 2');
console.log(out);

});

This outputs a bunch of settings for the table

SQLite3Query {
  _readableState:
   ReadableState {
     objectMode: true,
     highWaterMark: 16,
     buffer: [],
     length: 0,
     pipes: null,
     pipesCount: 0,
     flowing: null,
     ended: false,
     endEmitted: false,
     reading: false,
     sync: true,
     needReadable: false,
     emittedReadable: false,
     readableListening: false,
     defaultEncoding: 'utf8',
     ranOut: false,
     awaitDrain: 0,
     readingMore: false,
     decoder: null,
     encoding: null },
  readable: true,
  domain: null,
  _events: {},
  _eventsCount: 0,
  _maxListeners: undefined,
  text: 'SELECT body FROM messages WHERE id = 2',
  _fields: null,
  _result: { rows: [] },
  _errored: false,
  values: [],
  callback: undefined }

The final part "WHERE id = 2" confuses me a bit, because if I put the normal '' around the 2 it breaks.

Using other software, I can see that the database does have information in it. Eventually I would like to have the chatroom print out all the indices from the proper room into HTML, but this hurdle exists.

What do you mean, "if you put the normal " around the 2"? Also, have you read the Node SQLite3 API? You need to use callbacks; the information isn't immediately available. — Colonel Thirty Two
– Colonel Thirty Two, Commented Jan 27, 2016 at 2:05
I googled up on this, but I am still confused by callbacks. Is there a simpler solution? Also, I just wasn't sure whether to say WHERE id = '2' or WHERE id = 2. I assume the second since it's an int. — Scott Williams
– Scott Williams, Commented Jan 27, 2016 at 2:11
Node is all about callbacks. You need to learn how to use them if you plan on using Node. — Colonel Thirty Two
– Colonel Thirty Two, Commented Jan 27, 2016 at 2:12

shotor · Accepted Answer · 2016-01-27 12:28:26Z

The API Colonel_Thirty_Two is referring to: https://github.com/mapbox/node-sqlite3/wiki/API

The section: Database#all(sql, [param, ...], [callback]) is what you are looking for. By looking at the function signature we see that we need to specify a SQL query followed by two optional parameters, one is a callback function.

Further in the description we see that the callback needs to be of the form: function(err, rows). Where err is an object that is null, unless there was en error. And rows is your actual row.

Putting it all together we get:

var query = 'SELECT body FROM messages WHERE id = 2';
conn.all(query, function(err, rows) {
  console.log(rows);
}

Using the param optional parameter we can do:

var query = 'SELECT body FROM messages WHERE id = ?';
conn.all(query, 2, function(err, rows) {
  console.log(rows);
}

WHERE id = 2 should work fine if the key is an integer. Putting "" around 2 will fail because double quotes are not used in SQL. If your primary key was an string you would put single quotes around it.

var query = 'SELECT body FROM messages WHERE id = \'2\'';
conn.all(query, function(err, rows) {
  console.log(rows);
}

Finally heres a nice tutorial: https://codeforgeek.com/2014/07/node-sqlite-tutorial/

Collectives™ on Stack Overflow

How do you put a SQLite index into a variable with Nodejs?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related