[DUG] MSSQL ID field binary(8)

[David Brennan]

Hi John,

 

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).

 

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.

 

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.

 

Cheers,

David.

 

 

From: delphi-bounces at delphi.org.nz [mailto:delphi-bounces at delphi.org.nz] On
Behalf Of John Bird
Sent: Friday, 6 August 2010 1:11 p.m.
To: delphi at delphi.org.nz
Subject: Re: [DUG] MSSQL ID field binary(8)

 

Dealing with index/ID field that is set as binary - here is the solution
that worked in case any one else comes across same....

 

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....

 

example - using a Company_ID from Comp table to open Contacts that belong to
that Company...

 

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?

 

Step 1:

 

  ADOQueryComp.SQL.Text:='select *, Cast(Company_ID as VARCHAR(10)) as
ACompID, CAST(Company_ID as INT) as DCOMPID from Company '+
    'where Company_Name like '+likestr;

Note - Casting binary to string/Varchar does not work, but to integer does.
Go figure!

 

 

Step 2:

 

  iCompanyID:=cdsComp.FieldbyName('DCompID').asinteger;

  ADOQueryContact.SQL.Text:='select * from Contact '+
    'where Company_ID = CAST('+inttostr(iCompanyID)+' AS BINARY(8))';

John

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserver.123.net.nz/pipermail/delphi/attachments/20100806/2c812e39/attachment-0001.html