Inconsistent results with ERLANG ODBC

How may we handle FOR JSON PATH queries with ERLANG ODBC?

I have the following simple query in SQL Server:

SELECT 'Bob' [name], 50 [age] FOR JSON PATH

The expected output in SSMS is:

JSON_F52E2B61-18A1-11d1-B105-00805F49916B
-----------------------------------------
[{"name":"Bob","age":50}]

(1 row affected)

Calling this same query with :odbc module in ELIXIR gives different result each time:

{:selected, ['JSON_F52E2B61-18A1-11d1-B105-00805F49916B'],
 [
   [
     <<0, 0, 80, 0, 114, 0, 111, 0, 75, 246, 34, 87, 97, 33, 0, 128, 32, 0, 70, 0, 105, 0, 108, 0, 74, 246, 33, 87, 92, 34, 0, 128, 105, 0, 99, 0, 114, 0, 111, 0, 73, 246, 32, 87, 102, 35, 0, 128, 32,
       0>>
   ]
 ]}
iex(23)>

{:selected, ['JSON_F52E2B61-18A1-11d1-B105-00805F49916B'],
 [[<<0, 0, 0, 0, 0, 0, 0, 0, 207, 63, 70, 190, 0, 5, 0, 142, 0, 0, 0, 0, 0, 0, 0, 0, 204, 63, 69, 190, 253, 6, 0, 138, 7, 0, 0, 0, 67, 0, 0, 0, 205, 63, 68, 190, 0, 7, 0, 137, 112, 97>>]]}
iex(24)>

Is there a config setting required to get the correct results?

I use the following defaults:

  defp _connect(conn_str) do
    :odbc.connect(
      to_charlist("Driver={ODBC Driver 17 for SQL Server};Server=" <> conn_str),
      timeout: 30_000,
      auto_commit: :on,
      binary_strings: :on,
      tuple_row: :off,
      scrollable_cursors: :off,
      trace_driver: :off,
      extended_errors: :on
    )
  end

I would expect the result for invoking the same function multiple times would be the same?

2 Likes

A workaround would be to cast as varchar(8000)

select cast((SELECT 'Bob' [name], 50 [age] FOR JSON PATH) as varchar(8000))

At least this works