SQL - placeholders

Hello, my question comes from https://www.codecademy.com/paths/web-development/tracks/building-a-persistent-api/modules/persistent-api-cumulative-projects/projects/codestrips-prj and is related to placeholders.

My question is about step 17, where we know the structure of TABLE Strip and have to INSERT INTO Strip. When do we use a query such as

INSERT INTO Strip (head = $head) VALUES ($head: head)

As opposed to

INSERT INTO Strip (head) VALUES ({$head: variable})

what is used and why?
Is it that in one we are taking javascript variables and interpolating them and in the latter its a value that was passed a a parameter through the js func?

Hello, neither one of those is 100% correct so it’s difficult to answer.

It looks like you’re on the original Web Development career path based on the link you provided, so this is a link you should review: Learn Node-SQLite - Using db.run() exercise. The code you wrote to pass this particular exercise had to use placeholders to create a working SQL INSERT. You could use that as a reference to replicate what you’re trying to do in this project.

That’s important because the placeholders are in the SQL statement, but then the values for the placeholders are provided in the object that is sent with the SQL.

Here’s an example of a SQL INSERT statement that has the placeholders, and the object with the values for the placeholders is supplied as the next argument to db.run()

let nameVariable = 'A Name Here';
let yearVariable = 2020;

db.run(
  'INSERT INTO TheTableName (name, year) VALUES ($name, $year)',
  { $name: nameVariable, $year: yearVariable },
  function (error) {
    // Note: we didn't use an arrow function here so we can access
    // the new row id with this.lastID
    console.log(`The newly inserted row id: ${this.lastID}`);
  }
);

Here is the same, but storing the values for the placeholders in the object before passing it to db.run()

const dataObjectExample = {
  $name: 'A Name Here',
  $year: 2020
};

db.run(
  'INSERT INTO TheTableName (name, year) VALUES ($name, $year)',
  dataObjectExample,
  function (error) {
    console.log(`The newly inserted row id: ${this.lastID}`);
  }
);
2 Likes

Thanks :slight_smile: I appreciate your clear responses!