[DUG] MSSQL ID field binary(8)

[John Bird]

MessageDealing 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/27bbc707/attachment.html