Issue using variable length fields with Erlang ODBC
Rob A'Court <[email protected]> Thu, 21 Apr 2016 10:45:55 +0000
| Newsgroups | gmane.comp.lang.erlang.bugs |
|---|---|
| Message-ID | <DB5PR04MB16083F1DE82CA9EF46B1E0B1DF6E0@DB5PR04MB1608.eurprd04.prod.outlook.com> |
--===============6976633336224346138==
Content-Language: en-US
Content-Type: multipart/alternative;
boundary="_000_DB5PR04MB16083F1DE82CA9EF46B1E0B1DF6E0DB5PR04MB1608eurp_"
--_000_DB5PR04MB16083F1DE82CA9EF46B1E0B1DF6E0DB5PR04MB1608eurp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
When querying variable length fields using ODBC in Erlang, the response ret=
urned seems to be gibberish. I've posted this on the questions board and it=
has been suggested that this is a real bug.
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 ret=
urned 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 S=
erver which comes back as a NVarCharMax and there is no way of converting i=
t 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} =3D :odbc.connect('Driver=3D{ODBC Driver 11 for SQL Serve=
r}; Server=3DTheServer;Uid=3Dsa;Pwd=3Dpassword;Database=3DTheDatabase',[])
IO.inspect :odbc.sql_query(connection, 'set showplan_xml on')
IO.inspect :odbc.sql_query(connection, 'Select * from customers'), limit: 9=
000
Here is the equivalent in python that works fine:
#!/usr/bin/env python
import pyodbc
conn =3D pyodbc.connect('Driver=3D{ODBC Driver 11 for SQL Server}; Server=
=3DTheServer;Uid=3Dsa;Pwd=3Dpassword;Database=3DTheDatabase')
cur =3D conn.cursor()
cur.execute('set showplan_xml on')
cur.execute('Select * from customers')
for row in cur :
print row
Many thanks!
Rob
--_000_DB5PR04MB16083F1DE82CA9EF46B1E0B1DF6E0DB5PR04MB1608eurp_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"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;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@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=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-GB" link=3D"#0563C1" vlink=3D"#954F72">
<div class=3D"WordSection1">
<p class=3D"MsoNormal">When querying variable length fields using ODBC in E=
rlang, the response returned seems to be gibberish. I’ve posted this =
on the questions board and it has been suggested that this is a real bug.<o=
:p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">We can query tables in a MS SQL Server database but =
if the table contains a VarCharMax or NVarCharMax field (both variable leng=
th) then the result returned is not what we expect. For NVarCharMax a binar=
y 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=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">In our particular scenario we are trying to get a Sh=
owPlanXML 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 is=
sue.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">The issue does not seem to be with the ODBC driver a=
s trying the same thing in python works fine.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">Here is what we are trying to do in Elixir:<o:p></o:=
p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">:odbc.start<o:p></o:p></p>
<p class=3D"MsoNormal">{:ok, connection} =3D :odbc.connect('Driver=3D{ODBC =
Driver 11 for SQL Server}; Server=3DTheServer;Uid=3Dsa;Pwd=3Dpassword;Datab=
ase=3DTheDatabase',[])<o:p></o:p></p>
<p class=3D"MsoNormal">IO.inspect :odbc.sql_query(connection, 'set showplan=
_xml on')<o:p></o:p></p>
<p class=3D"MsoNormal">IO.inspect :odbc.sql_query(connection, 'Select * fro=
m customers'), limit: 9000<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">Here is the equivalent in python that works fine:<o:=
p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">#!/usr/bin/env python<o:p></o:p></p>
<p class=3D"MsoNormal">import pyodbc<o:p></o:p></p>
<p class=3D"MsoNormal">conn =3D pyodbc.connect('Driver=3D{ODBC Driver 11 fo=
r SQL Server}; Server=3DTheServer;Uid=3Dsa;Pwd=3Dpassword;Database=3DTheDat=
abase')<o:p></o:p></p>
<p class=3D"MsoNormal">cur =3D conn.cursor()<o:p></o:p></p>
<p class=3D"MsoNormal">cur.execute('set showplan_xml on')<o:p></o:p></p>
<p class=3D"MsoNormal">cur.execute('Select * from customers')<o:p></o:p></p=
>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">for row in cur :<o:p></o:p></p>
<p class=3D"MsoNormal"> print row <o:p><=
/o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">Many thanks!<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">Rob<o:p></o:p></p>
</div>
</body>
</html>
--_000_DB5PR04MB16083F1DE82CA9EF46B1E0B1DF6E0DB5PR04MB1608eurp_--
--===============6976633336224346138==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
_______________________________________________
erlang-bugs mailing list
[email protected]
http://erlang.org/mailman/listinfo/erlang-bugs
--===============6976633336224346138==--