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-boun...@delphi.org.nz [mailto:delphi-boun...@delphi.org.nz] On Behalf Of John Bird Sent: Friday, 6 August 2010 1:11 p.m. To: delphi@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
_______________________________________________ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject: unsubscribe
