Google Cloud Spanner Node库不接受参数化SQL。

huangapple
go评论75阅读模式
英文:

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'
    }
};

请参阅 https://github.com/googleapis/nodejs-spanner/blob/02c6e599a7b744a5c610c8d801229c7299abb668/samples/pg-query-parameter.js#L51 获取完整示例。

英文:

> 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.

huangapple
  • 本文由 发表于 2023年1月5日 12:41:32
  • 转载请务必保留本文链接：https://go.coder-hub.com/75013907.html
  • google-cloud-spanner
  • node.js
  • postgresql
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定