sqlserver interpolate binary value

[artemklevtsov]

Hi,

I try to fetch data from the SQL Server and faced with some issues.

Desired config:

# docker run -d --rm --name mssql -p 1433:1433 -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Password*" mcr.microsoft.com/mssql/server:2022-latest

input:
  sql_raw:
    driver: mssql
    dsn: sqlserver://sa:Password*@127.0.0.1:1433
    init_statement: |
      CREATE TABLE test (id binary(16), code nvarchar(9));
      INSERT INTO test VALUES (0x80B8FD42E350B98111E63F87B9BF576C, 'Code123');
    query: |
      SELECT * FROM test WHERE id = ?
    args_mapping: |
      root = [ 0x80B8FD42E350B98111E63F87B9BF576C ]

output:
  stdout: {}

Output:

❯ GODEBUG="x509negativeserial=1" rpk connect run tmp/insert.yaml
INFO Running main config from specified file       @service=redpanda-connect benthos_version=4.47.1 path=tmp/insert.yaml
ERRO Config lint error                             @service=redpanda-connect lint="tmp/insert.yaml(13,30) expected query, got: x80B8"
ERRO shutting down due to linter errors, to prevent shutdown run Redpanda Connect with --chilled  @service=redpanda-connect

Trying:

# docker run -d --rm --name mssql -p 1433:1433 -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Password*" mcr.microsoft.com/mssql/server:2022-latest

input:
  sql_raw:
    driver: mssql
    dsn: sqlserver://sa:Password*@127.0.0.1:1433
    init_statement: |
      CREATE TABLE test (id binary(16), code nvarchar(9));
      INSERT INTO test VALUES (0x80B8FD42E350B98111E63F87B9BF576C, 'Code123');
    query: |
      SELECT * FROM test WHERE id = ?
    args_mapping: |
      root = [ "0x80B8FD42E350B98111E63F87B9BF576C".bytes() ]

output:
  stdout: {}

Return nothing.

Works with unsafe_dynamic_query:

# docker run -d --rm --name mssql -p 1433:1433 -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Password*" mcr.microsoft.com/mssql/server:2022-latest

input:
  generate:
    count: 1
    mapping: |
      root.id = "0x80B8FD42E350B98111E63F87B9BF576C"
  processors:
    - sql_raw:
        driver: mssql
        dsn: sqlserver://sa:Password*@127.0.0.1:1433
        unsafe_dynamic_query: true
        init_statement: |
          CREATE TABLE test (id binary(16), code nvarchar(9));
          INSERT INTO test VALUES (0x80B8FD42E350B98111E63F87B9BF576C, 'Code123');
        query: |
          SELECT * FROM test WHERE id = ${! json("id") }

output:
  stdout: {}

But it is not good variant.

What other options are there to do this correctly?

[mihaitodor]

Hey @artemklevtsov 👋 Please try using root = [ "80B8FD42E350B98111E63F87B9BF576C".decode("hex") ] in the args_mapping. This config works for me:

input:
  sql_raw:
    driver: mssql
    dsn: sqlserver://sa:Password*@localhost:1433
    init_statement: |
      CREATE TABLE test (id binary(16), code nvarchar(9));
      INSERT INTO test VALUES (0x80B8FD42E350B98111E63F87B9BF576C, 'Code123');
    query: |
      SELECT * FROM test WHERE id = ?
    args_mapping: |
      root = [ "80B8FD42E350B98111E63F87B9BF576C".decode("hex") ]

output:
  stdout: {}

PS: Moving to a discussion as per #2026.

[artemklevtsov]

Thanks. Really works.