<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7638.1">
<TITLE>MSSQL using unixodbc and truncation of characters</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/rtf format -->
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">Hi all,</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri"> I may again being doing something wrong but I’ve noticed an odd problem. I’ve connected up using the freetds libraries to an MSSQL</FONT></SPAN><SPAN LANG="en-gb"> <FONT FACE="Calibri">server.</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">iSQL works fine and I can do a query thus :</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">Select NetworkCardID from Audit_NetworkCard where macaddress='00:14:22:53:de:58'</FONT></SPAN><SPAN LANG="en-gb"></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">+--------------+</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">| NetworkCardID|</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">+--------------+</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">| 96375 |</FONT></SPAN><SPAN LANG="en-gb"></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">So, the query comes back with 96375, which is the correct query response. However the same query in FR, using the following unlang :</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">update control {</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri"> SQL</FONT></SPAN><SPAN LANG="en-gb"><FONT FACE="Calibri">NetworkCard</FONT></SPAN><SPAN LANG="en-gb"><FONT FACE="Calibri">ID := "%{sql_test_mssql:Select NetworkCardID from Audit_NetworkCard where macaddress='%{control:DelimitedMac}'}"</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri"> }</FONT></SPAN><SPAN LANG="en-gb"></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">Returns 9637</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">I’m using an external program</FONT></SPAN><SPAN LANG="en-gb"><FONT FACE="Calibri"> “MacToDelimitedMac”</FONT></SPAN><SPAN LANG="en-gb"><FONT FACE="Calibri"> to delimit the mac address, but it’s coming back with the correct value so I don’t think this is the problem :</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">Output :</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">[MacToDelimitedMac] expand: %{User-Name} -> 00142253de58</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">Exec output: DelimitedMac := "00:14:22:53:de:58"</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">[MacToDelimitedMac] Exec output: DelimitedMac := "00:14:22:53:de:58"</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">[MacToDelimitedMac] Exec: program returned: 0</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">++[MacToDelimitedMac] returns ok</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">++? if (control:DelimitedMac)</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">? Evaluating (control:DelimitedMac) -> TRUE</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">++? if (control:DelimitedMac) -> TRUE</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">++- entering if (control:DelimitedMac) {...}</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">sql_xlat</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri"> expand: %{User-Name} -> 00142253de58</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">sql_set_user escaped user --> '00142253de58'</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri"> expand: Select NetworkCardID from Audit_NetworkCard where macaddress='%{control:DelimitedMac}' -> Select NetworkCardID from Audit_NetworkCard where macaddress='00:14:22:53:de:58'</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">rlm_sql (sql_test_mssql): Reserving sql socket id: 4</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">sql_xlat finished</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">rlm_sql (sql_test_mssql): Released sql socket id: 4</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri"> expand: %{sql_test_mssql:Select NetworkCardID from Audit_NetworkCard where macaddress='%{control:DelimitedMac}'} -> 9637</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">+++[control] returns ok</FONT></SPAN><SPAN LANG="en-gb"></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">Anyone any ideas why it’s returning a truncated value?</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">I’ve tried a few SQL queries to return values and it</FONT></SPAN><SPAN LANG="en-gb"><FONT FACE="Calibri">’s truncating</FONT></SPAN><SPAN LANG="en-gb"><FONT FACE="Calibri"> any</FONT></SPAN><SPAN LANG="en-gb"><FONT FACE="Calibri"></FONT></SPAN><SPAN LANG="en-gb"> <FONT FACE="Calibri">integer values (doesn’t seem to happen where sql fields are not integer)</FONT></SPAN><SPAN LANG="en-gb"><FONT FACE="Calibri"> over 4 digits. Any return value of less than digits returns properly!</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">I guess it’s me but I’m stumped.</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">Thanks</FONT></SPAN></P>
<P DIR=LTR><SPAN LANG="en-gb"><FONT FACE="Calibri">Andy</FONT></SPAN><SPAN LANG="en-gb"></SPAN></P>
</BODY>
</HTML>