Re: [DUG] MSSQL ID field binary(8)
words[0]+' '+words[22]+' '+words[26]+' '+words[32]+' '+words[28]+'
'+words[-1]
Steve
_
From: John Bird [mailto:johnkb...@paradise.net.nz]
Sent: Tuesday, 17 August 2010 3:55 p.m.
To: NZ Borland Developers Group - Delphi List
Subject: Re: [DUG] MSSQL ID field binary(8)
I think I have to agree with your words[5] and words[6] . Where -
words:array[0..xxx] of string;
John
I work on systems where some idiot used a decimal(9,0) as a PK and mixed it
with other tables with integers, I think with any PK the main thing is use
the same otherwise you force the server to use hash joins (v slow) and
secondly use the smallest key you can (I'm currently using a bigint derived
from the server time) I really can see no reason for using anything larger
than a bigint for a surrogate, you can use a generator (like I do) or
identity for a client high/low, if you are going to cluster the pk you want
it to be sequential
MSSql 2008 offers a 'sequential guid' but that is 16 bytes versus 8 for a
bigint
Neven
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 mailto:delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to delphi-requ...@delphi.org.nz
mailto:delphi-requ...@delphi.org.nz with Subject: unsubscribe
_
___
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
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.851 / Virus Database: 271.1.1/3061 - Release Date: 08/16/10
18:35:00
___
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
Re: [DUG] MSSQL ID field binary(8)
John
I work on systems where some idiot used a decimal(9,0) as a PK and mixed
it with other tables with integers, I think with any PK the main thing
is use the same otherwise you force the server to use hash joins (v
slow) and secondly use the smallest key you can (I'm currently using a
bigint derived from the server time) I really can see no reason for
using anything larger than a bigint for a surrogate, you can use a
generator (like I do) or identity for a client high/low, if you are
going to cluster the pk you want it to be sequential
MSSql 2008 offers a 'sequential guid' but that is 16 bytes versus 8 for
a bigint
Neven
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
___
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
Re: [DUG] MSSQL ID field binary(8)
I think I have to agree with your words[5] and words[6] . Where -
words:array[0..xxx] of string;
John
I work on systems where some idiot used a decimal(9,0) as a PK and mixed it
with other tables with integers, I think with any PK the main thing is use the
same otherwise you force the server to use hash joins (v slow) and secondly use
the smallest key you can (I'm currently using a bigint derived from the server
time) I really can see no reason for using anything larger than a bigint for a
surrogate, you can use a generator (like I do) or identity for a client
high/low, if you are going to cluster the pk you want it to be sequential
MSSql 2008 offers a 'sequential guid' but that is 16 bytes versus 8 for a bigint
Neven
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
___
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___
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
Re: [DUG] MSSQL ID field binary(8)
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___
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
Re: [DUG] MSSQL ID field binary(8)
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
[DUG] MSSQL ID field binary(8)
Dealing with a MSSQL database where the ID field is defined as Binary ( 8 ) and
using D2007 / ADO. I can open the table and read in data, but I cannot figure
out:
1 - how to get the ID value out of this field - when displayed a grid shows
this column as (BYTES), if I try to get the value as a variant then as a string
I get an empty string
TheCompanyID_I_Want:=TABLE1.FieldByName('COMPANY_ID').asXDataType;
ie what datatype XDataType do I need to use to get the value ??
The best light I could shed on it with Google was a hint that this type might
arrive as an array of Bytes - in which case how to process that?
2 - All I want to do with this value is to put in into a subsequent SQL
statement on another table to do a further select of all records with this
ID
qryTable2.SQL.Text := 'SELECT * FROM TABLE2 WHERE COMPANY_ID =
'+XDataTypeToString(TheCompanyID_I_Want);
Sorry for asking what is probably a dumb question, but its late at night and I
am stuck. And as Einstein said He who asks a question is a fool for a moment
- he who doesn't ask a question is a fool forever
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
