How to read/write binary field in MS SQL with Delphi

Although this data is binary (0 and 1 values), it seems to be represented as a string.  Therefore, I'd recommend storing it in a char or varchar field.

Hi skycrusher,

i could be missing something, what are you trying to do?
 
-save that string into the database
-or a big field as binary into SQL Server
-or a binary field with undefined length into a field in SQL Server

:O)Bruintje

HI,
Try to explain you problem more exactly: what do you want and what purpose for.
Sincerely,
Nestorua.

Actually, I want to save a string ( 248 char in length). The string is a pattern of 1 and 0. Of course I can save
this sequence of characters as "String"

AdoTable1.Fieldbyname('FCode').AsString:='0011010101....';

but if I save it as string, I will need 248 byte in the table ( CMIIW ).

If I can save it as Binary field, I thought I just need a few bytes.

FYI, the table should hold aproximately 60,000 records. So if I have to save it as "char" or "varchar", I think it will consume a lot of disk space :((

Any sugestions will be OK with me... ;))

TIA.
 
Regards,
skycrusher

skycrusher,

If you're primarily interested in saving space, you can fairly easily compresss string data and place it into a blob field.  You can even place the data into a character field.

component to do the compresssion:
http://www.delphi32.com/vcl/1375

===========================================
roll-your-own compression will result in an 8:1 reduction of the data.
loop through the characters, eight characters at-a-time
  loop through the current eight characters, converting to a byte
  add the byte to a variable or stream

~~~~~~~~~~
function BinStringToByte(BinString : string) : byte;
var
  i : integer;
  Num : byte;
begin
  Num := 0;
  for i := 1 to length(BinString) do
    if BinString[i] = '1' then
      Num := (Num shl 1) + 1 else
      Num := (Num shl 1);
  Result := Num;
end;

=========================================
of course, you will need a different RYO algorithm if the string lengths differ.

If possible, I don't want to use a component.

I have the Transact SQL book, and it said that the binary field is the most suitable field for saving a pattern of "1" and "0".

But unfortunately, that book did not give me enough information about how to fill this kind of field :((

Yes, I want to save it as Binary Field

ex:  ADOTable1.FieldByName('FCode').AsBinary:= ......

I hope you can help me..

If you create a bunch of columns of datatype "bit", then the database will automatically compress this data by storing in shared byte units.  You will be able to individually address the different bits quite easily this way (if you want to).

=============================================
There are two SQL Server functions (CAST & CONVERT) that might be useful in converting your string to a binary data type (and back into string when you retrieve it).

Yes, I know that SQL Server will automatically pack the "bit" type if there are some columns who have this kind of field.

Unfortunately, in my case.. what I want to save is a column :((

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

PAQ/No Refund

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
Paul (pnh73)
EE Cleanup Volunteer

Paul,

I think we've done an adequate job of answering this question.

(sorry about that! must have been a lapse in concentration!!)

I will leave a recommendation in the Cleanup topic area that this question is:

Split between aikimark and nestorua

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Paul (pnh73)
EE Cleanup Volunteer