Difference in integer length in code and database
Posted: Tue Aug 21, 2007 11:17 am
Hi,
Quite often, I notice that there is a difference between the data type being used in the mysql database and the program code. For example, most of the time, the C# code itself will declare most integers just as int -- being 32 bit (4 byte) signed integers [1, 3, 4].
However, on the database side, some fields are declared as, for example, smallint, which is a 2-byte signed integer [1, 2]. An example of such a field is Mount.DocCategory.
Why is this a problem? Because when you assign a big value to an int in the program code, it may get truncated when you store it to the database. As an example, storing int.MaxValue to a field of type smallint will return short.MaxValue when you load it from the database again. Quite obviously, this isn't good!
It happens that the Unit tests I'm writing detect this. There are a couple of possible solutions:
1. Just ignore the problem.
2. Adjust the types used in the program to those in the database (introducing long, short, whatever)
3. Adjust the types used in the database to those in the program
4. Standarizing on int
Any comments? If there is a consensus that some action should be taken, I volunteer to gradually do so -- starting with the imaging modules (that should be covered by the unit tests).
Frederik.
[1] http://en.wikipedia.org/wiki/Integer_%2 ... science%29
[2] http://dev.mysql.com/doc/refman/4.1/en/ ... types.html
[3] http://msdn2.microsoft.com/en-us/librar ... S.71).aspx
[4] http://msdn2.microsoft.com/en-us/librar ... int32.aspx
Quite often, I notice that there is a difference between the data type being used in the mysql database and the program code. For example, most of the time, the C# code itself will declare most integers just as int -- being 32 bit (4 byte) signed integers [1, 3, 4].
However, on the database side, some fields are declared as, for example, smallint, which is a 2-byte signed integer [1, 2]. An example of such a field is Mount.DocCategory.
Why is this a problem? Because when you assign a big value to an int in the program code, it may get truncated when you store it to the database. As an example, storing int.MaxValue to a field of type smallint will return short.MaxValue when you load it from the database again. Quite obviously, this isn't good!
It happens that the Unit tests I'm writing detect this. There are a couple of possible solutions:
1. Just ignore the problem.
2. Adjust the types used in the program to those in the database (introducing long, short, whatever)
3. Adjust the types used in the database to those in the program
4. Standarizing on int
Any comments? If there is a consensus that some action should be taken, I volunteer to gradually do so -- starting with the imaging modules (that should be covered by the unit tests).
Frederik.
[1] http://en.wikipedia.org/wiki/Integer_%2 ... science%29
[2] http://dev.mysql.com/doc/refman/4.1/en/ ... types.html
[3] http://msdn2.microsoft.com/en-us/librar ... S.71).aspx
[4] http://msdn2.microsoft.com/en-us/librar ... int32.aspx