[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 942
  • Last Modified:

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


Hi,...

I am using Delphi 6 and MS SQL Server 7.0.

I have a pattern of (for example: '10001010111010111011') and I want to save this pattern into database.
Someone told me that the best choice is using a binary field in SQL Server, but I could not figure it out how to do this.

Could anyone give me an example on how to do this ??

Thanks...


0
skycrusher
Asked:
skycrusher
  • 5
  • 4
  • 2
  • +2
2 Solutions
 
aikimarkCommented:
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.
0
 
bruintjeCommented:
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
0
 
nestoruaCommented:
HI,
Try to explain you problem more exactly: what do you want and what purpose for.
Sincerely,
Nestorua.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
skycrusherAuthor Commented:

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
0
 
aikimarkCommented:
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.
0
 
skycrusherAuthor Commented:

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 :((


0
 
nestoruaCommented:
HI, skycrusher,
If you mean 248 bytes and every byte is "0" or "1", then you
can use 8 fields of type INTEGER to store your information
(and you know how to do this).
As for using BINARY or VARBINARY type I try now to read from the Help how to use it.
Sincerely,
Nestorua.
0
 
skycrusherAuthor Commented:

Yes, I want to save it as Binary Field

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

I hope you can help me..

0
 
aikimarkCommented:
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).
0
 
skycrusherAuthor Commented:

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 :((


0
 
aikimarkCommented:
Then define a SQL Server Binary(248) column and use the Convert() function to "pack/unpack" your string.
0
 
pnh73Commented:
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
0
 
aikimarkCommented:
Paul,

I think we've done an adequate job of answering this question.
0
 
pnh73Commented:
(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
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now