There are currently no frequently asked questions associated with this exercise – that’s where you come in! You can contribute to this section by offering your own questions, answers, or clarifications on this exercise. Ask or answer a question by clicking reply () below.
If you’ve had an “aha” moment about the concepts, formatting, syntax, or anything else with this exercise, consider sharing those insights! Teaching others and answering their questions is one of the best ways to learn and stay sharp.
Join the Discussion. Help a fellow learner on their journey.
Ask or answer a question about this exercise by clicking reply () below!
Agree with a comment or answer? Like () to up-vote the contribution!
I was wondering the exact same thing, and seeing that you asked this question three months ago and nobody has replied, I will leave it as a mystery for now (I could not find the answer myself anywhere).
if you re-run the exercise the results appear in different orders, so there is no logic behind the order which leaves me to conclude the following. Using db.all or db.get methods within a loop, makes asynchronous calls to the database we are just seeing printed whatever results are back first. The reason for a changing order of results coming back from an unchanged database are mysterious, but ill put it down to a secret of the interweb
The only reason I have found to be placeholders to be of any use is that placeholders can refer to undefined values without causing errors (unless you have NOT NULL constraints ofc). EG:
Sqlite3 will treat the undefined description value as NULL when using placeholders. However, if you were to do the same thing with template literals where description is an undefined value, you get an error as sqlite does not know how to interpret the value:
db.run(INSERT INTO table (name, age, description) VALUES ('${name}', ${age}, ${description})), // Will cause an error!
…
If you wrap ${description} in quotes sqlite3 will assume the value is the text string ‘undefined’, which does not work either.
If anybody has anything to add or correct in any way please do so, these are just observations my experiments!
Works fine with both placeholders and template literals, sqlite understands that null means null.
If you wrap the template literal in quotes however sqlite will interpret the value as the string ‘null’, and not a null value.
I find the written tasks not very clear, which is frustrating. I spent too much time figuring out why my results were coming out as undefined and later realised my code in the sql query command part, written as 'db.get(“SELECT * FROM TemperatureData WHERE id_no = $idNo”, was because id_no was incorrect (i made this up for my own understanding). After looking at the solution, it was ‘id’ instead of ‘id_no’. When i made the change, it then worked. They should have mentioned to use id as one of the endpoints!..rant over…
In short, placeholder assigns a single value to a parameter. Template literals can be used to maliciously amend the entire query and potentially allow access to all records.
Full explanation by factoradic can be found in this thread:
That sure is odd, isn’t it?
I tried ordering the results using ORDER BY id, here’s my code:
ids.forEach(function (id) {
console.log(id)
db.get(
'SELECT * FROM TemperatureData WHERE id = $id ORDER BY id',
{
$id: id
},
(error, rows) => {
printQueryResults(rows);
}
);
});
Still, different results everytime… anybody knows how to fix this into a nice, structured result?
I think it’s because db.get() gets only one row per request to the db and this operations are asynchronous.
ORDER BY id worked, but there was only one row in the result))
For the particular order we have to use WHERE … IN … construction and change .get() to .all()
const { printQueryResults } = require('./utils');
const sqlite = require('sqlite3');
const db = new sqlite.Database('./db.sqlite');
const ids = [1, 25, 45, 100, 360, 382];
// your code below:
db.all(`SELECT * FROM TemperatureData WHERE id IN (${ids.join(",")})`,
(error, row) => {printQueryResults(row)});
) below.
) to up-vote the contribution!
