CharlesO

CharlesO

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?

First Post!

CharlesO

CharlesO

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

Where Next?

Popular in Questions Top

vonH
In asking this question I am more interested about the expressiveness of the language itself and less concerned about the availability of...
New
Emily
I have VueJS GUIs with the project generated using Webpack. I have Elixir modules that will need to be used by the VueJS GUIs. I forese...
New
lanycrost
Hi everyone! I need implement if…else if…else condition from my elixir code, and anymore of this control flow structures not work proper...
New
ashish173
I am using Ecto timestamps with postgres, I can see the timestamps() use the :naive_dateime but for my use case I wanted to store the ti...
New
nsuchy
Hi. I’ve noticed that Windows Powershell has it’s own IEX command and you cannot access Elixir’s IEX due to the conflict. This isn’t a cr...
New
belgoros
I’m not a pro in using Regex and can’t figure out why the following behaviour happens, especially if we take into account the difference ...
New
svb
Hi! Currently I want to submit a form by pressing the Enter key. However, since my input field is of type “textarea” this is just adds a...
New

Other popular topics Top

nobody
Hi! In PHP: $_SERVER[‘SERVER_ADDR’] - in Elixir? Searched the docs for ip address and the web, no good results. Thanks!
New
New
Darmani72
If I have a post route which an argument: post /my_post_route/:my_param1, MyController.my_post_handler How would get the post params ...
New
dogweather
I wrote this comment on r/haskell, and it’s not popular there. :wink: But I think I’m on to something… Haskell reminds me of Java, and e...
New
dblack
I’ve got an issue with an app and I’ve no idea of how to troubleshoot it. I’m hoping someone here might have seen something similar. I p...
New
romenigld
I am trying to run a deploy with docker and I successfully runned with this command: docker build -t romenigld/blog-prod . but when I t...
New

We're in Beta

About us Mission Statement