英文:
Google Cloud Spanner Node Library won't accept parameterized SQL
问题
我尝试实现Google Spanner库(版本6.6.0)的节点,并在参数化SQL方面遇到了问题。
这是未参数化的示例,当我运行它时,我会得到以下输出:
Results: [{"id":"8694b2b9-add0-4024-bb22-bd711bbda9d4","name":"Name","text":"New Message"}]
Complete
然而,如果我将查询对象更新为使用参数化,如下所示:
const query = {
sql: `SELECT id, name, text
FROM greetings WHERE name = @Name`,
params: {
Name: 'Name'
}
};
我会得到以下错误:
Error: 3 INVALID_ARGUMENT: Invalid parameter name: name. Expected one of 'p1', 'p2', ..., 'p65535'
这似乎奇怪,因为参数名必须命名为p1,p2等,但在Google Cloud Spanner文档中并不是这样,但我还是尝试了一下。
所以,我将查询对象转换为如下所示(根据错误的指示):
const query = {
sql: `SELECT id, name, text
FROM greetings WHERE name = @p1`,
params: {
p1: 'Name'
}
};
现在我会得到以下错误:
Error: 3 INVALID_ARGUMENT: [ERROR] column "p1" does not exist
我似乎无法弄清楚如何在database.run()函数调用上使参数化SQL工作。我是否做错了什么,或者我是否配置数据库不正确?如果我使用的是postgresql方言,是否会有所不同?
希望这可以帮助你解决问题。
英文:
I'm attempting to implement the node Google Spanner library (v6.6.0) and I'm running into an issue with parameterized sql.
const {Spanner} = require('@google-cloud/spanner');
async function doStuff() {
// Creates a client
const spanner = new Spanner({
projectId: "<my_project_id>",
});
// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance("<my_instance>");
const database = instance.database("<my_db>");
const query = {
sql: `SELECT id, name, text
FROM greetings WHERE name = 'Name'`,
};
const [results] = await database.run(query);
console.log("Results: " + JSON.stringify(results));
}
doStuff().then(() => console.log("Complete"));
This is not parameterized, and when I run this I get the following output:
Results: [{"id":"8694b2b9-add0-4024-bb22-bd711bbda9d4","name":"Name","text":"New Message"}]
Complete
as expected.
However, if I update the query object to use parameterization, like so:
const query = {
sql: `SELECT id, name, text
FROM greetings WHERE name = @Name`,
params: {
Name: 'Name'
};
I get the following error:
node:internal/process/promises:288
triggerUncaughtException(err, true /* fromPromise */);
^
Error: 3 INVALID_ARGUMENT: Invalid parameter name: name. Expected one of 'p1', 'p2', ..., 'p65535'
at Object.callErrorFromStatus (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\call.js:31:19)
at Object.onReceiveStatus (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\client.js:360:49)
at <project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\call-stream.js:111:35
at Object.onReceiveStatus (<project_path>\node_modules\grpc-gcp\build\src\index.js:73:29)
at InterceptingListenerImpl.onReceiveStatus (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\call-stream.js:106:23)
at Object.onReceiveStatus (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\client-interceptors.js:328:181)
at <project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\call-stream.js:188:78
at process.processTicksAndRejections (node:internal/process/task_queues:77:11)
for call at
at ServiceClientImpl.makeServerStreamRequest (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\client.js:343:34)
at ServiceClientImpl.<anonymous> (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\make-client.js:105:19)
at <project_path>\node_modules\@google-cloud\spanner\build\src\v1\spanner_client.js:221:29
at <project_path>\node_modules\google-gax\build\src\streamingCalls\streamingApiCaller.js:38:28
at <project_path>\node_modules\google-gax\build\src\normalCalls\timeout.js:44:16
at Object.request (<project_path>\node_modules\google-gax\build\src\streamingCalls\streaming.js:130:40)
at makeRequest (<project_path>\node_modules\retry-request\index.js:141:28)
at retryRequest (<project_path>\node_modules\retry-request\index.js:109:5)
at StreamProxy.setStream (<project_path>\node_modules\google-gax\build\src\streamingCalls\streaming.js:121:37)
at StreamingApiCaller.call (<project_path>\node_modules\google-gax\build\src\streamingCalls\streamingApiCaller.js:54:16) {
code: 3,
details: "Invalid parameter name: name. Expected one of 'p1', 'p2', ..., 'p65535'",
metadata: Metadata {
internalRepr: Map(1) {
'grpc-server-stats-bin' => [
Buffer(10) [Uint8Array] [
0, 0, 236, 206, 58,
2, 0, 0, 0, 0
]
]
},
options: {}
}
}
Node.js v18.12.1
Process finished with exit code 1
That seems odd that the parameter names must be named p1, p2, etc, because it is not the case in the Google Cloud Spanner docs, but I went ahead and tried it anyway.
So, I convert the query object like so (as instructed by the error):
const query = {
sql: `SELECT id, name, text
FROM greetings WHERE name = @p1`,
params: {
p1: "Name"
}
};
Now I get the following error:
node:internal/process/promises:288
triggerUncaughtException(err, true /* fromPromise */);
^
Error: 3 INVALID_ARGUMENT: [ERROR] column "p1" does not exist
at Object.callErrorFromStatus (<project_path>\moleculer-gcp-spanner\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\call.js:31:19)
at Object.onReceiveStatus (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\client.js:360:49)
at <project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\call-stream.js:111:35
at Object.onReceiveStatus (<project_path>\node_modules\grpc-gcp\build\src\index.js:73:29)
at InterceptingListenerImpl.onReceiveStatus (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\call-stream.js:106:23)
at Object.onReceiveStatus (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\client-interceptors.js:328:181)
at <project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\call-stream.js:188:78
at process.processTicksAndRejections (node:internal/process/task_queues:77:11)
for call at
at ServiceClientImpl.makeServerStreamRequest (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\client.js:343:34)
at ServiceClientImpl.<anonymous> (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\make-client.js:105:19)
at <project_path>\node_modules\@google-cloud\spanner\build\src\v1\spanner_client.js:221:29
at <project_path>\node_modules\google-gax\build\src\streamingCalls\streamingApiCaller.js:38:28
at <project_path>\node_modules\google-gax\build\src\normalCalls\timeout.js:44:16
at Object.request (<project_path>\node_modules\google-gax\build\src\streamingCalls\streaming.js:130:40)
at makeRequest (<project_path>\node_modules\retry-request\index.js:141:28)
at retryRequest (<project_path>\node_modules\retry-request\index.js:109:5)
at StreamProxy.setStream (<project_path>\node_modules\google-gax\build\src\streamingCalls\streaming.js:121:37)
at StreamingApiCaller.call (<project_path>\node_modules\google-gax\build\src\streamingCalls\streamingApiCaller.js:54:16) {
code: 3,
details: '[ERROR] column "p1" does not exist',
metadata: Metadata {
internalRepr: Map(1) {
'grpc-server-stats-bin' => [
Buffer(10) [Uint8Array] [
0, 0, 197, 240, 249,
3, 0, 0, 0, 0
]
]
},
options: {}
}
}
Node.js v18.12.1
Process finished with exit code 1
I can't seem to figure out how to get parameterized sql to work on a database.run() function call. Am I doing something wrong or have I set my database up incorrectly? I'm using the postgresql dialect if that makes a difference.
答案1
得分: 1
是的,这确实有所不同。PostgreSQL方言数据库使用PostgreSQL SQL方言。这意味着查询必须使用PostgreSQL样式的查询参数,即$1、$2等
。因此,您的查询应该如下所示:
const query = {
sql: `SELECT id, name, text
FROM greetings WHERE name = $1`,
params: {
p1: 'Name'
}
};
英文:
> I'm using the postgresql dialect if that makes a difference.
Yes, that makes the difference. PostgreSQL-dialect databases uses the PostgreSQL SQL dialect. That means that queries must use PostgreSQL style query parameters, which is $1, $2, ...
. So your query should be:
const query = {
sql: `SELECT id, name, text
FROM greetings WHERE name = $1`,
params: {
p1: 'Name'
};
See https://github.com/googleapis/nodejs-spanner/blob/02c6e599a7b744a5c610c8d801229c7299abb668/samples/pg-query-parameter.js#L51 for a full sample.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论