Binary to int conversion 1

[cyberbiker]

I really suspect this is not the best forum for this question, but I really do not know where to post this question.

I need to covert some binary values to a 32 bit integer by any means possible.

These values, once converted to a 32 bit integer will be a constant

this is an example of the binary:

10000010000000001000011000000000

when I use Windows calculator I convert to 2181072384 which is over the range for int data type.



Terry (cyberbiker)

 

[SQLSister]

have you tried bigint?

Questions about posting. See faq183-874

 

[cyberbiker]

BigInt is 64 bit is it not?

I am also thinking of the sql_variant datatype, but BOL says that sql_variant is "Not fully supported" with ODBCwhich may be a problem

I was thinking that I have not converted the value properly. It has been some time since I have done any binary to integer conversions and the values just "Don't look right" to my eyes.

I need to confirm or deny the accuracy of my conversions before proceeding



Terry (cyberbiker)

 

[ESquared]

Are your binary 'values' actually string data?

If you create a table with the values 0 to 62 in it you can do a set-based binary to integer conversion in SQL server (as opposed to loop-based).

SET NOCOUNT ON
CREATE TABLE #nums (pos bigint)
DECLARE @cntr int
SET @cntr = 0
WHILE @cntr < 63 BEGIN
   INSERT INTO #nums VALUES (@cntr)
   SET @cntr = @cntr + 1
END

DECLARE @binstring varchar(63)
SET @binstring = '10000010000000001000011000000000'

SELECT
   IntegerVal = 
      sum(power(convert(bigint,2),pos) 
      * substring(reverse(@binstring),pos+1,1)) -- yeah, implicit conversion
   FROM #nums

DROP TABLE #nums


-- Result Set:
--------------
-- IntegerVal
--------------
-- 2181072384

I only did 63 bits (0 to 62) instead of 64 (0 to 63) because bigint is signed and it would be a pain to handle the exception.

Alternately, you could convert each pair of 4 bits into a hexidecimal string character. Then you can either convert the string to int or leave it as is. Hex lets you manipulate substrings of bits more conveniently.

 

[cyberbiker]

E-Squared

Your suggestion did show my calculations were correct and thank you for pointing out the fact that bigint is signed

integer is also signed and that is my problem I think.

This project is very difficult to explain, but simply put, Each bit stored in a 32-bit field has a particular meaning.
I cannot use a 64 bit field

I will try to explain, but my terminology may not be up to the task at this point. I need to work on this a bit more now that I know where I am starting from

Several years ago I worked on a similar project except that there I used a binary file.

I need to reevaluate what I am trying to do and see if I can find some way to get the old code off a 3.5 floppy.
I also need to speak with the engineers involved to clear up some questions which will take some time.

Let me know if you would like a full description of what I do. It does relate to SQL Server programming so I guess it will be on topic. It will probably be next week at the earliest



Terry (cyberbiker)

 

[ESquared]

You can handle the unsigned bit with exception logic and CASE statements.

I'm curious why you need exactly 32 bits... in fact, I'm curious why you're using bitmasks at all. As much as I love them because they let me do fun bit manipulation & arithmetic, they're not the best choice for every conceivable use.

As for difficult to explain, it's probably not difficult at all. Bitmasking is an old old programming method of storing information. You can even use groups of bits to represent values, not just individual bits. It's a nice way to compress information but not the most efficient way of manipulating information...

I hope you're good with ORs and ANDs and XORs

...speaking of which, what's the binary operator for XOR and NOT in SQL server?

AND = &, OR = |

 

[cyberbiker]

Basically, a PLC control will be using this value.
It will be looking for (and I quote) "A 32-bit integer"

I need to find some old code I worked with and refresh my memory as to exactly what will be being done with the value stored in the database field.

My confusion here is that I have never seen the software that is being used to create the ladder logic for this project and need to speak with the engineer to clear up a couple of points (I know just enough ladder logic to be dangerous)

It has been so long since I worked with bits that I just needed to be certain that my conversion was correct before going "off the deep end". Remember you can build an 100hp engine but if you put on the wrong chain, you do not get far.


Terry (cyberbiker)

 

[ESquared]

So modify your code to give a 32-bit integer, and add special case handling that ORs the result with 0x80000000 (-2147483648) to turn on the highest bit on.

FYI you can turn the highest bit off by ANDing with 0x7fffffff (2147483647).

 

[cyberbiker]

from BOL

int

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.

My conversion gives: 2181072384 which cannot be stored as a 4 byte field in SQL Server


Additionally, my largest value(at this time) will be:
00101000000000000010100000000000:
which converts to:

671098880

Assumption is that my conversions of:

10000010000000001000011000000000 and
00101000000000000010100000000000
to Dword Decimal using windows calculator are correct.

I am reasonably certain that the PLC control is actually looking for a DWord (Unsigned 32 bit integer) I finally found my notes from a few years ago and although this is a different make of control I think it uses the same datatypes. Then I think I just wrote a long data type to a binary file usinb VB, but I cannot be certain

The previous app used position 7-16 and 24-32 as "The most significant bits" while the requirements for this app specifies using 1-6 and 17-23 instead.

I am also thinking (Based on something I was told 4 or 5 years ago in ref to these controls) that the current requirements could be in error

Your last post has given me some ideas and I will experiment a bit with the code you posted this weekend to see if I can work out what I need.

I appreciate the help




Terry (cyberbiker)

 

[ESquared]

I don't understand why you say you have two seemingly conflicting requirements. It MUST be a 32-bit integer, yet you MUST handle more than 32 bits of data... what gives?

 

[cyberbiker]

Maybe I need to go back to square one.

A single bit can be on or off, correct? Thus the value is 1(on) or 0 (off)

This should be a binary representation of a 32 bit piece of data (4 Bytes each byte consiting of 8 bits)

00101000000000000010100000000000:

Which I convert to a DWord which I believe is defined as a 32 bit unsigned integer giving a value of:
671098880

Assuming that windows calculator and the help menu associated with that application as well as my old notes are accurate and that I understand what I am reading correctly this should be the value that I need and is a DWord value.

But I cannot save this value as a 32 bit integer since it is far outside the permisable range of values.

I am not 100% certain that I am converting the value properly since the resulting value is so large, but everything I am finding so far seems to indicate that I am.

I have done this before as I mentioned and the values were within the permitted value range of the VB long data type
(equivilant to SQL Server integer) but that was using the second and fourth bytes as significant instead of the first and third. It has also been some time ago and I did not document the process of converting binary to a Long (Integer). I guess I felt it was so easy that I would not forget.

If I am correctly converting the binary then I need to speak with the engineers about this, but I cannot reach the person I need until Tuesday.

If I am not converting the binary properly then I need to know that before speaking with the engineers.

This value will act as a "roadmap". Think of this like driving a car with your wife beside you. if you reach a spot in the road where the the sign post reads 0, she ignores the turn and you drive straight. If the signpost is 1, she tells you to turn.

I need to store only a small number of these values which will then be constant.






Terry (cyberbiker)

 

[ESquared]

32 bit unsigned integer: 0 to 4,294,967,295

32 bit signed integer: -2,147,483,648 to 2,147,483,647

671,098,880 can be stored in 32 bit integer, even a signed one, no problem. 671 million is smaller than 2 billion or 4 billion.

 

[cyberbiker]

Darn it. I never inserted the commas, just looked at it and thought I was looking at 6 billion, not 671 million.
My close eyesight is not so good. I know that, but cannot seem to adjust.

However

2,181,072,384 is too large
(as well as 3 additional values out of 15 or so that I converted.)

My actual largest value is 2,483,064,832 (Again too large)

This now makes a bit of sense to me and seems to indicate that my conversions are correct.

Terry (cyberbiker)

 

[ESquared]

2,181,072,384 is not too large. it will fit if you code it as

33588736 OR -2147483648

which is the normal value of the low 31 bits + the high bit in signed notation

 

[cyberbiker]

Thank you very much for your help.

Your stored procedure code has been a big help.
I have experimented with it and have shown that my origional conversions seem to have been correct, which was my big question.

Based on what I have been reading storing the value as 33588736 should give what is required.

My understanding is that the high bit is 0 for a positive number.

Using your code to convert the additional values to 32 bit looks like the solution.





Terry (cyberbiker)

 

[ESquared]

yes, the high bit is 0 for a positive number and 1 for a negative number.

If you are doing any of it in SQL, why not use bigint, then convert to binary, then to int? (Or find some other conversion process that does the trick.) This way you can do no "special handling" for what end up being negative numbers as ints.

 

[cyberbiker]

First:
these will be a limited number of values that will remain "constant".

second:
Time is critical since I am delaying the engineer writing the ladder logic. (Of course, they are out relaxing and I am working over a holiday weekend)

I took my motorcycle out for a 300 mile trip yesterday which has helped clear my mind and I have been thinking on some ideas for future development if I can get the time authorized. The code you supplied me would be a great starting point if I have your permission to use it other than as an educational aid.

In this case, there will be no possibility of negative numbers occuring. The "Road Map" is just that. The binary value is designed almost as a sketch. Perhaps, you might look at this process as if drawing a flow chart using 1's and 0's instead of symbols. Branch at 1's only. The last bit could be considered the end of the flow chart. Since there is no possiblity of a branch at the very end,it must always be 0.

My plan for the future would be to create a VB app using check boxes for each bit. The user would check the proper boxes then pass that value to your stored procedure, convert and store in the db.

I could do this as a utility app or OCX control depending on several (non technical) factors. Pretty simple app actually.

My biggest problem has actually been not knowing if I was in fact converting binary to decimal properly.

Your code proved to me that I was and I truly thank you for that.

Your comment: "it will fit if you code it as

33588736 OR -2147483648"

combined with your stored procedure cleared up everything for me.








Terry (cyberbiker)

 

[ESquared]

I should give it in SQL syntax as well as VB:

33588736 | -2147483648

 

[cyberbiker]

I might perhaps do it using SQL but I think the best for this situation might be a small stand alone utility that would pass the binary to an SQL stored procedure as an input argument where I then convert and store the values to be used when needed

Upon success, I would display a "You did it!" type of message and let it go at that.

I think this might be a case of the less information the end user receives the better

The binary values will be a constant for each machine system
Changes will only be made if major changes to the system are made which would be

Since these value will almost never change anyway. I think doing the conversion once and storing that result would be more efficient than storing the binary string and converting each time

Regardless, SQL would perform the conversion. The front end will be VB though.

Terry (cyberbiker)