[erlang-questions] Issue using variable length fields with Erlang ODBC

[Rob A'Court]

When querying variable length fields using ODBC in Erlang, the response returned seems to be gibberish.

We can query tables in a MS SQL Server database but if the table contains a VarCharMax or NVarCharMax field (both variable length) then the result returned is not what we expect. For NVarCharMax a binary is returned which has part of the original query and other seemingly random data as if it’s the wrong area of memory. For VarCharMax an empty list is always returned.

In our particular scenario we are trying to get a ShowPlanXML from MS SQL Server which comes back as a NVarCharMax and there is no way of converting it to a fixed length field type to work around the issue.

The issue does not seem to be with the ODBC driver as trying the same thing in python works fine.

Here is what we are trying to do in Elixir:

:odbc.start
{:ok, connection} = :odbc.connect('Driver={ODBC Driver 11 for SQL Server}; Server=TheServer;Uid=sa;Pwd=password;Database=TheDatabase',[])
IO.inspect :odbc.sql_query(connection, 'set showplan_xml on')
IO.inspect :odbc.sql_query(connection, 'Select * from customers'), limit: 9000


Here is the equivalent in python that works fine:

#!/usr/bin/env python
import pyodbc
conn = pyodbc.connect('Driver={ODBC Driver 11 for SQL Server}; Server=TheServer;Uid=sa;Pwd=password;Database=TheDatabase')
cur = conn.cursor()
cur.execute('set showplan_xml on')
cur.execute('Select * from customers')

for row in cur :
  print row


Many thanks!

Rob

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://erlang.org/pipermail/erlang-questions/attachments/20160411/71e7e22f/attachment.htm>