<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:x="urn:schemas-microsoft-com:office:excel" xmlns:p="urn:schemas-microsoft-com:office:powerpoint" xmlns:a="urn:schemas-microsoft-com:office:access" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" xmlns:b="urn:schemas-microsoft-com:office:publisher" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:odc="urn:schemas-microsoft-com:office:odc" xmlns:oa="urn:schemas-microsoft-com:office:activation" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:q="http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc="http://microsoft.com/officenet/conferencing" xmlns:D="DAV:" xmlns:Repl="http://schemas.microsoft.com/repl/" xmlns:mt="http://schemas.microsoft.com/sharepoint/soap/meetings/" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:ppda="http://www.passport.com/NameSpace.xsd" xmlns:ois="http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir="http://schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:dsp="http://schemas.microsoft.com/sharepoint/dsp" xmlns:udc="http://schemas.microsoft.com/data/udc" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sub="http://schemas.microsoft.com/sharepoint/soap/2002/1/alerts/" xmlns:ec="http://www.w3.org/2001/04/xmlenc#" xmlns:sp="http://schemas.microsoft.com/sharepoint/" xmlns:sps="http://schemas.microsoft.com/sharepoint/soap/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:udcs="http://schemas.microsoft.com/data/udc/soap" xmlns:udcxf="http://schemas.microsoft.com/data/udc/xmlfile" xmlns:udcp2p="http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf="http://schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss="http://schemas.microsoft.com/office/2006/digsig-setup" xmlns:dssi="http://schemas.microsoft.com/office/2006/digsig" xmlns:mdssi="http://schemas.openxmlformats.org/package/2006/digital-signature" xmlns:mver="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns:mrels="http://schemas.openxmlformats.org/package/2006/relationships" xmlns:spwp="http://microsoft.com/sharepoint/webpartpages" xmlns:ex12t="http://schemas.microsoft.com/exchange/services/2006/types" xmlns:ex12m="http://schemas.microsoft.com/exchange/services/2006/messages" xmlns:pptsl="http://schemas.microsoft.com/sharepoint/soap/SlideLibrary/" xmlns:spsl="http://microsoft.com/webservices/SharePointPortalServer/PublishedLinksService" xmlns:Z="urn:schemas-microsoft-com:" xmlns:st="" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<title>Message</title>
<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;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.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="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-NZ link=blue vlink=purple id=MailContainerBody
name="Compose message area">
<div class=WordSection1>
<p class=MsoNormal><span style='color:#1F497D'>Hi John,<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>Yep I would have thought using a
Binary field as an ID field would be very, very unusual. They are lower class
citizens in terms of what operations work natively on them, including indices
as your friends commented. There are other gotcha’s with how ADO and
Delphi will handle them too (eg Blob fields in ClientDatasets don’t send
their value as part of updates if they haven’t changed, presumably to
avoid sending huge chunks of unchanged data back to the database, but this
would obviously be a big problem if it was acting as a key field).<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>Binary fields are made for
storing large blocks of binary (or any adhoc) data and they do that job well
but I wouldn’t use them for anything else.<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>I can’t imagine many
situations where one of the integer types or a Varchar field wouldn’t be
able to do the job as an ID field.<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>Cheers,<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>David.<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:
"Tahoma","sans-serif"'>From:</span></b><span lang=EN-US style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'> delphi-bounces@delphi.org.nz
[mailto:delphi-bounces@delphi.org.nz] <b>On Behalf Of </b>John Bird<br>
<b>Sent:</b> Friday, 6 August 2010 1:11 p.m.<br>
<b>To:</b> delphi@delphi.org.nz<br>
<b>Subject:</b> Re: [DUG] MSSQL ID field binary(8)<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<div>
<div>
<p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Dealing
with index/ID field that is set as binary - here is the solution that worked in
case any one else comes across same....</span><span lang=EN-US
style='font-size:12.0pt;font-family:"Times New Roman","serif"'><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-US style='font-size:12.0pt;font-family:"Times New Roman","serif"'> <o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Note
for MSSQL using such a field in a JOIN SQL statement would be perfectly fine as
normal, as MSSQL has a native binary type. Delphi ADO however does
not, so this is the hoops I had to jump through when its two different datasets
and I needed to get the value in order to open the other with SQL or as a
parameter....</span><span lang=EN-US style='font-size:12.0pt;font-family:"Times New Roman","serif"'><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-US style='font-size:12.0pt;font-family:"Times New Roman","serif"'> <o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Arial","sans-serif"'>example
- using a Company_ID from Comp table to open Contacts that belong to that
Company...</span><span lang=EN-US style='font-size:12.0pt;font-family:"Times New Roman","serif"'><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-US style='font-size:12.0pt;font-family:"Times New Roman","serif"'> <o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Comments
from friends who know MSSQL - their opinion is that an ID/index field that
is binary is unusual practice - restricts what kind to indexes can be made for
instance. Anyone here have opinion on this? have others come
across ID/Index that is Binary often?</span><span lang=EN-US style='font-size:
12.0pt;font-family:"Times New Roman","serif"'><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-US style='font-size:12.0pt;font-family:"Times New Roman","serif"'> <o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Step
1:</span><span lang=EN-US style='font-size:12.0pt;font-family:"Times New Roman","serif"'><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-US style='font-size:12.0pt;font-family:"Times New Roman","serif"'> <o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Arial","sans-serif"'>
ADOQueryComp.SQL.Text:='select *, Cast(Company_ID as VARCHAR(10)) as ACompID,
CAST(Company_ID as INT) as DCOMPID from Company '+<br>
'where Company_Name like '+likestr;</span><span lang=EN-US
style='font-size:12.0pt;font-family:"Times New Roman","serif"'><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Note
- Casting binary to string/Varchar does not work, but to integer
does. Go figure!<o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-US style='font-size:12.0pt;font-family:"Times New Roman","serif"'> <o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-US style='font-size:12.0pt;font-family:"Times New Roman","serif"'> <o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Step
2:</span><span lang=EN-US style='font-size:12.0pt;font-family:"Times New Roman","serif"'><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-US style='font-size:12.0pt;font-family:"Times New Roman","serif"'> <o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Arial","sans-serif"'>
iCompanyID:=cdsComp.FieldbyName('DCompID').asinteger;</span><span lang=EN-US
style='font-size:12.0pt;font-family:"Times New Roman","serif"'><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Arial","sans-serif"'>
ADOQueryContact.SQL.Text:='select * from Contact '+<br>
'where Company_ID = CAST('+inttostr(iCompanyID)+' AS
BINARY(8))';</span><span lang=EN-US style='font-size:12.0pt;font-family:"Times New Roman","serif"'><o:p></o:p></span></p>
</div>
</div>
<div>
<p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Arial","sans-serif"'>John</span><span
lang=EN-US style='font-size:12.0pt;font-family:"Times New Roman","serif"'><o:p></o:p></span></p>
</div>
</div>
</body>
</html>