<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-GB" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal">When querying variable length fields using ODBC in Erlang, the response returned seems to be gibberish.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">The issue does not seem to be with the ODBC driver as trying the same thing in python works fine.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Here is what we are trying to do in Elixir:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">:odbc.start<o:p></o:p></p>
<p class="MsoNormal">{:ok, connection} = :odbc.connect('Driver={ODBC Driver 11 for SQL Server}; Server=TheServer;Uid=sa;Pwd=password;Database=TheDatabase',[])<o:p></o:p></p>
<p class="MsoNormal">IO.inspect :odbc.sql_query(connection, 'set showplan_xml on')<o:p></o:p></p>
<p class="MsoNormal">IO.inspect :odbc.sql_query(connection, 'Select * from customers'), limit: 9000<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Here is the equivalent in python that works fine:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">#!/usr/bin/env python<o:p></o:p></p>
<p class="MsoNormal">import pyodbc<o:p></o:p></p>
<p class="MsoNormal">conn = pyodbc.connect('Driver={ODBC Driver 11 for SQL Server}; Server=TheServer;Uid=sa;Pwd=password;Database=TheDatabase')<o:p></o:p></p>
<p class="MsoNormal">cur = conn.cursor()<o:p></o:p></p>
<p class="MsoNormal">cur.execute('set showplan_xml on')<o:p></o:p></p>
<p class="MsoNormal">cur.execute('Select * from customers')<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">for row in cur :<o:p></o:p></p>
<p class="MsoNormal"> print row <o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Many thanks!<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Rob <o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>