› › ›

Passing parameters to a query

Using values in a query condition, for example, or in a HAVING statement can be done "inline", by integrating the value in the query string itself:

resp = client.sql.query(
    format="txt",
    query="SELECT YEAR(release_date) AS year FROM library WHERE page_count > 300 AND author = 'Frank Herbert' GROUP BY year HAVING COUNT(*) > 0",
)
print(resp)

response = client.sql.query(
  format: 'txt',
  body: {
    query: "SELECT YEAR(release_date) AS year FROM library WHERE page_count > 300 AND author = 'Frank Herbert' GROUP BY year HAVING COUNT(*) > 0"
  }
)
puts response

const response = await client.sql.query({
  format: "txt",
  query:
    "SELECT YEAR(release_date) AS year FROM library WHERE page_count > 300 AND author = 'Frank Herbert' GROUP BY year HAVING COUNT(*) > 0",
});
console.log(response);

POST /_sql?format=txt
{
	"query": "SELECT YEAR(release_date) AS year FROM library WHERE page_count > 300 AND author = 'Frank Herbert' GROUP BY year HAVING COUNT(*) > 0"
}

or it can be done by extracting the values in a separate list of parameters and using question mark placeholders (?) in the query string:

resp = client.sql.query(
    format="txt",
    query="SELECT YEAR(release_date) AS year FROM library WHERE page_count > ? AND author = ? GROUP BY year HAVING COUNT(*) > ?",
    params=[
        300,
        "Frank Herbert",
        0
    ],
)
print(resp)

response = client.sql.query(
  format: 'txt',
  body: {
    query: 'SELECT YEAR(release_date) AS year FROM library WHERE page_count > ? AND author = ? GROUP BY year HAVING COUNT(*) > ?',
    params: [
      300,
      'Frank Herbert',
      0
    ]
  }
)
puts response

const response = await client.sql.query({
  format: "txt",
  query:
    "SELECT YEAR(release_date) AS year FROM library WHERE page_count > ? AND author = ? GROUP BY year HAVING COUNT(*) > ?",
  params: [300, "Frank Herbert", 0],
});
console.log(response);

POST /_sql?format=txt
{
	"query": "SELECT YEAR(release_date) AS year FROM library WHERE page_count > ? AND author = ? GROUP BY year HAVING COUNT(*) > ?",
	"params": [300, "Frank Herbert", 0]
}

The recommended way of passing values to a query is with question mark placeholders, to avoid any attempts of hacking or SQL injection.

« Columnar results Use runtime fields »