Dennis Cote wrote:
Floppe wrote:
Will Leshner wrote:
On 4/5/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
Hello,
When using sqlite-3.3.4 with windows I get the following strange
behaviour.
create table Muppet (Kermit float);
insert into Muppet values (100);
select * from Muppet;
100.0
That's not strange. Kermit is defined to be a FLOAT, so it gets
returned as a FLOAT. Perhaps you wanted it to be returned as an
INTEGER? You could try something like this:
SELECT cast(kermit as INTEGER) FROM Muppet;
I wanted it to return the same format as SQLite does in Windows CE or
any other SQL engine does. Consistency? If someone enters 100 then it
could be a rounded value but if someone enters 100.0 (or 100.1) then it
usually isn't rounded. So now I need two fields, one with the value and
one that says how many decimals the user entered and then format the
output? Or make every field as varchars? Seems a little stupid to me.
If this is a part of the roadmap and not a bug then the next question
would be, is version 3.2.8 stable as that was the last version that
worked as I wanted in winxp?
This behavior was changed as a result of this bug report
http://www.sqlite.org/cvstrac/tktview?tn=1362
I think the current behavior is correct.
If you have a column where you want to store exactly the data the user
entered, you should use a text column. You can convert that text to
numeric data by whatever means you deem appropriate whenever you want
to. You should only use a floating point column if you want to store the
numeric value that the user entered regardless of how he entered it. The
database should not make a distinction between 1.0 and 1.000 for a
floating point value, but these are not the same thing when you
interpret the extra decimal places as increased accuracy.
This is very similar to the use of leading zeros on integer numbers. As
integers, 001 and 1 are the same thing. If you need to distinguish
between the two, you should be using a text column.
Dennis Cote
Dennis' advice is well founded. We use TEXT fields for decimal numbers,
and give them a declared type of DECIMAL(P,S) where P is the precision
and S the scale so that they are SQL compliant. Sqlite permits this.
These display format decimal numbers interface to a display format
arithmetic package which links into the application and a few custom
Sqlite functions so that arithmetic can be performed without going into
floating point and risking the consequent loss of accuracy.
Unfortunately you cannot use arithmetic within Sqlite nor the arithmetic
aggregate functions.
One day I shall make a couple of patches into Sqlite so that the SUM and
TOTAL aggregate functions and the +,-,* and / use the accurate decimal
numbers. A quick browse through the tightly written and well documented
Sqlite suggests that the changes would be quite limited and use the
existing data structures unchanged. The lack of an accurate number
system is a shortcoming with Sqlite. A possible remedy would be to
implement the decimal standard proposed by IBM recently.
http://www2.hursley.ibm.com/decimal/decarith.pdf
We use a set of display format fixed point arbitrary precision and scale
arithmetic functions salvaged from one of our legacy language
compiler/support packages and which was the reason users wanted the old
language to live on - accurate, easy to use numeric handling. The debit
and credit columns always matched.
Despite the nuisance of having to fiddle with numbers we believe that
the elegance in design and simplicity in use of Sqlite makes the effort
to adapt it to our needs well worthwhile. The application server we
have constructed using it fits in as an Apache module, is small and fast
and makes for easy to maintain, easy to install systems in what would
otherwise be "computer hostile" environments.
