signed vs. unsigned numbers (1 Viewer)

the_net_2.0

Banned
Local time
Today, 02:49
Joined
Sep 6, 2010
Messages
812
all,

I have this situation and I can't figure out if it is actually relevant! I'm getting an overflow error in access on an INTEGER field at 33300, which is obviously over the allowable maximum for access's integer field size.

My question is: What is the difference between a signed an unsigned integer?

I just don't get this. I understand this much:

  • Signed Integers: Can have either a (-) preceeder or a (+) preceeder

but so what?? Obviously a positive integer converts to a different binary number pattern than an unsigned (negative) number does, but again so what??

The issue I'm struggling with is that I have 5 processes that my numbers go through, and by the time it gets to stage 4, it is input into the field as 33300, but the field is (at that time) indicated as an integer, which of course throws the error.

The people we send this data to request that they have an unsigned integer in that field. However, I don't get the connection between their request and the signed. vs. unsigned part.

Apparently what happens in our code when it converts to the output format, is that the output number becomes -32236. I don't know the specifics behind this conversion, but I'm sure I could find out through google. But obviously, if our customer wants an unsigned integer (double field if it has to be 33300 to begin with) as the end result, that negative sign attached to the -32236 number has to be positive, as unsigned integers cannot be less than 0 in decimal format!

I'm absolutely confused by this. Can somebody shed some light for me on how simple this is please? The issues I'm facing are:

  • converting the int field to double, BEFORE the conversion takes place
  • converting the negative output number to a signed integer

obviously I know how to do the first part, but the second is throwing me for a loop. Any help appreciated. I don't think I'll ever understand binary. Thanks guys! :)

Adam
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:49
Joined
Jan 20, 2009
Messages
12,849
In a signed integer one of the bits is used to indicate positive/negative. Consequently the maximum for Integer is 2^15 - 1 = 32,767

You should be using the Long datatype which is a four byte signed integer.
Its maximum is 2^31 -1 = 2,147,483,647
 

the_net_2.0

Banned
Local time
Today, 02:49
Joined
Sep 6, 2010
Messages
812
In a signed integer one of the bits is used to indicate positive/negative.

THAT'S what I want to know! I don't really understand this. Isn't it the left-most bit that indicates this? If so, is the indicator a 1 or a 0??


You should be using the Long datatype which is a four byte signed integer.
Its maximum is 2^31 -1 = 2,147,483,647

Yes, I know this. However, for whatever reason (which I assume is a reasonable one) the end users/customers of this data have specified that they need a 2-byte unsigned integer from me in order for their systems to work. So it begs the question:

* How do I get a 4-byte signed integer (LONG type in Access) back into a 2-byte unsigned integer format?? THAT is what I'm lost on.

Or should I be lost? Heck, I haven't worked with binary "anything" in years! :)
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:49
Joined
Jan 20, 2009
Messages
12,849
Access doesn't have an unsigned integer datatype.

It begs a question about the output format of the data you are asked to provide and how they are importing it.
 

the_net_2.0

Banned
Local time
Today, 02:49
Joined
Sep 6, 2010
Messages
812
Access doesn't have an unsigned integer datatype.

It begs a question about the output format of the data you are asked to provide and how they are importing it.

but that doesn't answer my question, sir.

How about this:

If the customer wanted an unsigned integer, but the supplier gave us data such that the conversion process results in a negative LONG in access, who's fault is that? Probably the supplier, right?
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:49
Joined
Jan 20, 2009
Messages
12,849
The problem is with the customer. They have specified something in meaningless terms because it is without a context. Unfortunately they don't understand the process or are using the wrong data translation interface.

When a number is stored in Access it is a number, a mathematical entity with a well defined meaning, end of story. The datatype only defines how it is encoded for ACCESS and set limits on the possible values.

That value can be communicated to other applications in a number of ways. One is as a character in a text file. That character can be written in many languages but it is still the same mathematical concept. If you want it written in Chinese then that needs to be translated.

Another is as a value via an ODBC connection. If the ODBC translator is doing its job then that value is the same value regardless of the datatype or the system it is displayed in.

However the binary representation in that language is not going to necessarily be the same as the original so binary encoding cannot simply be passed between systems. An unsigned integer datatype in one system does not neceessarly encode the same in another system.

Different operating systems represent values in different binary structures. One of the major differences is between BigEndian and LittleEndian systems. These are very different encoding systems and the data must be translated between them.

Defining a datatype of the output without an encoding context is completely meaningless. Trying to pass a value with only a datatype specified is like using characters that make up a language and assuming they have the same meaning in the other language. Unsurprisingly the value is arriving in the other application having lost its original meaning.
 

the_net_2.0

Banned
Local time
Today, 02:49
Joined
Sep 6, 2010
Messages
812
Galax,

Let me just ask you this:

In Access help, it says that cint() converts an input to a signed integer. It also says that Access does not support the return of unsigned data types of any kind. So the question then is, what's the purpose of cint() other than to round fractional numbers to the nearest integer value? To me, that's all the function is good for. Signed vs. unsigned is not even part of the equation.

Correct?

So we would have to write our own UDF to convert a signed integer with a negative value to it's unsigned integer equivalent with a positive value? That makes sense to me. What do you think, sir?
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:49
Joined
Jan 20, 2009
Messages
12,849
what's the purpose of cint() other than to round fractional numbers to the nearest integer value?

Cint() isn't actually for rounding but converting the datatype to integer. Int() is for returning the integer part of a number and would be applied intrinsically during the datatype conversion.

Cint() is used more than you might realise because it is applied implicity when a string datatype is subjected to an arithmetic operation.

Signed vs. unsigned is not even part of the equation.

Exactly. As I tried to express earlier, the difference between signed and unsigned integer is irrelevant beyond setting the limits of the values the datatype can support. The value of an integer is the same regardless of the datatype being signed or unsigned.

Provided the values are supported by the datatype used in each system it should not matter when data is moved between systems. If the value changes when moved then the problem is with the translator that has been applied not the datatype. If that system is another database then the ODBC connector is faulty, not your data.

So we would have to write our own UDF to convert a signed integer with a negative value to it's unsigned integer equivalent with a positive value?

As far as I can see you have a signed integer with a positive value. That value is being misinterpreted in the destination system as a negative because its representation is being incorrectly interpreted from the encoding system used in Access to the encoding system of the destination system.

A UDF could be written to rescale the value so that it gets the answer they want when it is is misinterpreted by the translator. However that is a workaround and is not really addressing the fundamental translation problem.

The crux of the problem is the operating system and application that is expressing your positive integer as a negative and what is going wrong with the translation.

Perhaps the destination is a MySQL database which does support an Unsigned Integer datatype. If so then then there is something wrong with the ODBC connector being used.

Actually that might be a solution. Give them the data in a MySQL database. Then if it still comes up wrong in their system they might start to consider the problem is in their sytem. I promise you, if your assignment is to provide data in an Access database then they are absolutely barking up the wrong tree in thinking the datatype is the issue.

http://help.scibit.com/mascon/masconMySQL_Field_Types.html
 

the_net_2.0

Banned
Local time
Today, 02:49
Joined
Sep 6, 2010
Messages
812
thanks for the link! See, those conversion ranges from signed to unsigned is what I'm looking at now. for instance, the small integer has different ranges depending on whether it is signed or not.

And as I've said earlier, Access's casting functions do not support unsigned values, so the return of CINT() is always signed. So as an example (using the MYSQL ranges for "very small integer", when I'm working in Access and I'm given a value of -65 in a field, obviously casting that number will return a signed value. But say I use a UDF like the following in Access to get rid of the negative sign in the above value:

Code:
function UnsignIt(data as integer) as integer [COLOR="DarkGreen"]'DATA = -65 here[/COLOR]
   UnsignIt = data + 128 [COLOR="DarkGreen"]'convert to the new range[/COLOR]
end function

When it comes to actually encoding that value, is that the point where "signed vs. unsigned" becomes relevant? I didn't tell you this either ... this data (like the above example) is being streamed into binary computer files by vb6 simply using I/O. The customer/person using the machine that's running on these binary files is claiming that the machine requires this particular data to be an unsigned 2 byte integer (when the machine needs to interpret the bytes). So, if we take the MYSQL ranges defined in your link and apply my little function above, we get this:

Code:
data (signed) = [B]-65[/B]
data (unsigned - after my conversion function) = [B]63[/B]

but then you convert those numbers to binary strings and you get this:

Code:
data (signed) -----> -65 = 1000001 (binary) ([B]3F [/B]in hex)
data (unsigned) ------> 63 = 111111 (binary) ([B]41 [/B]in hex)

so that's a problem, right? Obviously those two binaries are different and a machine is going to read them differently and act two different ways when it reads that byte.

Does that make any sense? So the bottom line I'm looking at is that the two ranges from your MYSQL page eventually tell a machine to do two different things? If I don't make sense though, that's fine. I feel like I'm a dog chasing my tail...:rolleyes:
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:49
Joined
Jan 20, 2009
Messages
12,849
I really don't think you are getting the point I am making at all. The VALUE is the VALUE regardsless of datatype. If the destination system reads it as a different value then it is NOT a problem with the datatype but a problem with the TRANSLATION (or failure to translate) to the other system.

As I keep repeating the datatype itseld is irrelevant. You seem to be persuing the kludge and in doing so are entering uncharted territory.

If you put the data into MySQL then this would be done through the ODBC connection. If you update an unsigned integer field in MySQL from a signed integer field in Access then the VALUE will still be the same.

The real point is, how are these values being transferred to and read the destination system?

Are you actually asked to supply hexadecimal code? If so then you need the full specification of the encoding system not some lame request for an "unsigned integer".
 

the_net_2.0

Banned
Local time
Today, 02:49
Joined
Sep 6, 2010
Messages
812
The real point is, how are these values being transferred to and read the destination system?

Are you actually asked to supply hexadecimal code? If so then you need the full specification of the encoding system not some lame request for an "unsigned integer".

Sorry, Galax. I figured the issue was with the transfer of the data. So here's how it works:

the data we're talking about is in one field in an access table, which usually has about 400,000 records in it. we get the records from a supplier in various formats (.dat, .ascii, etc...). Same data really, just different file ext. So we get it, process it in Access through vb6 code and then stream it out (I/O) with code (again vb6) to multiple files with different extensions. The files with various extensions are created with vb6 because different record types go into each file. By the time we stream the data into these files, all of the fields in all of the records in Access either hold a 1 or a 0. Hence, the files are created in binary.

So we are outputting a database that comprises multiple files. This database is just one component that goes into the full system that a pilot will use to fly a helicopter. So I think the "destination system" that you're thinking of is nothing more than the management system (console) as a whole that a helicopter pilot would see through a number of gauges on his dashboard. This database is basically the flight plan data that he must use in order to fly any given route that is available for him in the area for which he is flying.

Make sense?

So from an encoding perspective, I have no idea how that works. But the transfer of the data from us to them basically takes place through a disc load. So for instance, the binary files are copied to a disc and that disc is place into the management system device (as a whole) that helicopter manufacturers will install inside the dashboard when they make a new helicopter.

This is just more of a reason for my last post. The only numerical value representations that the management system has to work with when the discs are put into it and read, are binary byte patterns from the binary files. Hence, my previous question about whether a signed integer creates a different byte pattern than it's unsigned integer equivalent does. Because if it does, then I have an issue on my hands.

Becoming clearer now? :)

Edit
This page does some good justice explaining how a negative number can be interpreted by reading binary patterns: http://www.math.grin.edu/~rebelsky/Courses/152/97F/Readings/student-binary#neg

But still, gets confusing!
 
Last edited:

Banana

split with a cherry atop.
Local time
Today, 00:49
Joined
Sep 1, 2005
Messages
6,318
You keep talking about reading binary files and writing into other binary files. It seems to me that if you're reading the binary sequence literally (which is usually always the case) and the size of the binary sequence is correct, then it won't actually matter if you're putting it into a field that's a signed integer, a text or even a float. What DOES matter is that you then have to write the binary sequence just as literally.

Rephrasing, a binary sequence is always same irrespective of how it's stored. If it appears to give different values, it's only because we chose to interpret it differently. So, 0xFFFF can either mean -1 if it's said to be a signed integer or 65535 if it's an unsigned integer or a string of two ASCII characters, "" or maybe a string of one Unicode character, "" (don't forget there's more than one kind of Unicode encoding!). But the binary sequence is still same.

The implication is that when you're just reading binary sequence, it doesn't really matter what data type it's defined to be. The data type serves as a means to tell us how we should read this or that binary sequence.

If all you're doing is reading the binary sequence, storing it and writing it out into other files, then it may be that VARBINARY() is actually more apropos data type for your table's fields than any integers. For reasons unknown, this is not available via table designer in Access, but you can do a CREATE TABLE or ALTER TABLE DDL and use that data type.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 07:49
Joined
Sep 12, 2006
Messages
15,614
Here's another thought.way of looking at it.

A 2-byte variable can hold a maximum of 2^16 different values (from a bit pattern of all 0's through to a pattern of all 1's)

These 2 bytes can be treated in whatever way the compiler/programmer requires -
eg 2 different chars (note there is no char datatype in VBA), a 2-char string, an array of 2 bytes, an array of 16 1 bit-flags - etc, etc

Now a VBA integer is designated as a 2 byte number and can therefore take any of 65536 (2^16) values. Because integers are signed, this means a number can actually run from -32768 to +32767.

And it is in this context that cint is useful - to convert 2 bytes of data (say a string) into a variable defined as a numeric type - so it can then be manipulated as a number.


A 4 byte number (a long) goes from approx -2 billion to plus 2 billion.


Now I don't understand the point you are making. Why should an internal data representation within access have any relevance to what a client wants. The only point is that if a database needs a numeric value that won't exceed 32767, then you can use an integer data type. If it may exceed that number, then you need a long. If the client says he needs a 2-byte data type, but the values exceed 32767 then there is a problem.

However you say you are iterating the same set of data multiple times, which is causing the overflow? So maybe it's your program logic that's the issue.

Hope this helps.
 

Users who are viewing this thread

Top Bottom