Any workarounds for handling issues with `nvarchar(MAX)` in Erlang ODBC?

Question

There seem to be some issues in Erlang ODBC relating to nvarchar(MAX) and varchar(MAX).

For example running SELECT 'Bob' [name], 50 [age] FOR JSON PATH should give the following:

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

Erlang ODBC produces:

{: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>>
   ]
 ]}

and on another run ...

{: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>>]]}

I'm using the following connection settings:

timeout: 30_000,
auto_commit: :on,
binary_strings: :on,
tuple_row: :off,
scrollable_cursors: :off,
trace_driver: :off,
extended_errors: :on

The output of the FOR JSON clause is of type nvarchar(MAX).

Please are there any workarounds for handling issues with nvarchar(MAX) in Erlang ODBC.

What do you get if you configure `binary_strings` to `:off`? — Steve Vinoski, May 27 '19 at 11:27
I cannot find anything definitive one way or the other but I would be pretty surprised if the ODBC driver supported the "for json" clause. ODBC doesn't really support much beyond ANSI standard SQL--intentionally. — Onorio Catenacci, May 28 '19 at 16:34
If I were you, I'd first be asking if the SQL-Server ODBC driver supports the "FOR JSON" clause at all. You probably want to post a question here: https://dba.stackexchange.com/ because you're more likely to get a knowledgeable answer there. — Onorio Catenacci, May 28 '19 at 16:39
@OnorioCatenacci thanks for this answer, but my test show that the problem is deeper than that. Even `SELECT cast('Hi Joe' as nvarchar(MAX)) [msg]` fails, with `nvarchar(MAX)` or even `varchar(MAX)` This leads me to suspect that `Erlang ODBC` driver is unable to handle `nvarchar(MAX)` and `varchar(MAX)` properly. — Charles Okwuagwu, May 28 '19 at 16:57
This may have some bearing on this issue: https://stackoverflow.com/questions/22517250/how-to-convert-an-elixir-binary-to-a-string You're using NVARCHAR (unicode vs. ascii encoding) and that may be part of the issue you're seeing. — Onorio Catenacci, May 28 '19 at 20:09
the biggest problem i see here is the fact that a simple query like: `SELECT cast('hello' as nvarchar(MAX)) [msg]` gives a different result for each invocation. that seems to point to an issue with ERLANG ODBC handling nvarchar(MAX) — Charles Okwuagwu, May 28 '19 at 20:33
I think you're right but I don't think it's the Erlang ODBC driver. I think it's the ODBC driver in general. Take a look at this: https://social.msdn.microsoft.com/Forums/windows/en-US/b59ae1ba-1add-4a93-845e-e4505603d23f/selecting-varcharmax-or-nvarcharmax-columns-using-quotodbc-driver-11-for-sql-serverquot?forum=sqldataaccess — Onorio Catenacci, May 28 '19 at 21:31
@OnorioCatenacci i had seen those links. The complaint there was for `ODBC Driver 11 for SQL Server` i'm running the latest, `Driver={ODBC Driver 17 for SQL Server}` — Charles Okwuagwu, May 28 '19 at 21:46
FWIW I wouldn't be terribly surprised if the error is still present. One person reported the error on ODBC Driver 13. Someone else reported still seeing the issue on February 28, 2019--so I wouldn't be surprised if the issue is still present. — Onorio Catenacci, May 30 '19 at 20:32
@OnorioCatenacci your intuition was very correct. There is an elegant solution for this, simply use the OLEDB provider via ODBC — Charles Okwuagwu, Sep 29 '20 at 16:43

Charles Okwuagwu · Accepted Answer · 2020-09-29T16:43:15.883

There is solution that works fine on windows: use the OLEDB provider via ODBC like below

 conn = "Provider=MSDASQL;DRIVER={SQL Server};SERVER=#{server};PORT=#{port};DATABASE=#{db};UID=#{uid};PWD=#{pwd};"

    :odbc.connect(
      to_charlist(conn),
      timeout: 30_000,
      # auto_commit: true,
      binary_strings: :on,
      tuple_row: :off,
      # scrollable_cursors: :off,
      # trace_driver: :off,
      extended_errors: :on
    )

Any workarounds for handling issues with `nvarchar(MAX)` in Erlang ODBC?

1 Answers1