Solved

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

Posted on 2002-04-27
14
850 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 

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
 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Delphi selector screen 2 84
Reconfigure Delphi Install? 2 60
PHP preg_replace code convert to Delphi 14 66
How to Get Images From Server to Client using App Tethering 1 26
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

856 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