Solved

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

Posted on 2002-04-27
14
839 Views
Last Modified: 2010-04-04

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
Comment
Question by:skycrusher
  • 5
  • 4
  • 2
  • +2
14 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 6974502
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
 
LVL 44

Expert Comment

by:bruintje
ID: 6974883
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
 
LVL 4

Expert Comment

by:nestorua
ID: 6981799
HI,
Try to explain you problem more exactly: what do you want and what purpose for.
Sincerely,
Nestorua.
0
 

Author Comment

by:skycrusher
ID: 6990302

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
 
LVL 45

Expert Comment

by:aikimark
ID: 6992703
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
 

Author Comment

by:skycrusher
ID: 6993590

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
 
LVL 4

Assisted Solution

by:nestorua
nestorua earned 125 total points
ID: 6993646
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:skycrusher
ID: 7002993

Yes, I want to save it as Binary Field

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

I hope you can help me..

0
 
LVL 45

Expert Comment

by:aikimark
ID: 7003103
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
 

Author Comment

by:skycrusher
ID: 7004245

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
 
LVL 45

Accepted Solution

by:
aikimark earned 125 total points
ID: 7004376
Then define a SQL Server Binary(248) column and use the Convert() function to "pack/unpack" your string.
0
 
LVL 1

Expert Comment

by:pnh73
ID: 9004235
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
 
LVL 45

Expert Comment

by:aikimark
ID: 9005800
Paul,

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

Expert Comment

by:pnh73
ID: 9006163
(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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now