节点PostgresSQL

Question

我试图查询postgres数据库，但我的查询没有返回任何有用的内容。它工作时，我不使用参数子，即const sql = "SELECT acronym, definition FROM terms WHERE acronym='Thing'"，但我不确定这应该如何做。我试过很明显的方法，但没有。我认为这可能是因为postgres“不支持标识参数”--这意味着有人知道为什么我的查询不能工作？

app.get("/terms", async (req, res) => {
  let values = req.query.term;
  const sql = "SELECT acronym,definition FROM terms WHERE acronym($1)";
  
  const terms = await pgClient.query(sql, values);

  res.send(terms);
  pgClient.end();
});

工作时

{data: {…}, status: 200, statusText: 'OK', headers: {…}, config: {…}, …}
config: {transformRequest: {…}, transformResponse: {…}, timeout: 0, xsrfCookieName: 'XSRF-TOKEN', adapter: ƒ, …}
data: {command: 'SELECT', rowCount: 46, oid: null, rows: Array(46), fields: Array(2), …}
headers: {access-control-allow-origin: '*', connection: 'keep-alive', content-length: '10023', content-type: 'application/json; charset=utf-8', date: 'Sat, 15 Jan 2022 10:41:45 GMT', …}
request: XMLHttpRequest {readyState: 4, timeout: 0, withCredentials: false, upload: XMLHttpRequestUpload, onreadystatechange: ƒ, …}
status: 200
statusText: "OK"
[[Prototype]]: Object...

不工作

xhr.js:178 POST http://localhost:8080/api/values 502 (Bad Gateway)
dispatchXhrRequest @ xhr.js:178
xhrAdapter @ xhr.js:12
dispatchRequest @ dispatchRequest.js:59
Promise.then (async)
request @ Axios.js:51
Axios.<computed> @ Axios.js:71
wrap @ bind.js:9
...
xhr.js:178 GET http://localhost:8080/api/terms/all?term=Word 502 (Bad Gateway)

dispatchDiscreteEvent @ react-dom.development.js:4168
createError.js:16 Uncaught (in promise) Error: Request failed with status code 502
    at createError (createError.js:16)
    at settle (settle.js:18)
    at XMLHttpRequest.handleLoad (xhr.js:77)

Answer 1

关于您的代码的两个注释：

• values必须是一个数组，每个参数都有一个条目，参数在

查询中显示为$1, $2, ... (或者，取决于数据库，作为?)，其他语法没有任何变化。不需要再次引用字符串。

let values = [req.query.term]; // an array containing the string
const sql = "SELECT acronym,definition FROM terms WHERE acronym=$1";
const terms = await pgClient.query(sql, values);