Dennis Cote wrote:
The second issue is demonstrated by the last last three statements.
CREATE TABLE t (a REAL, b REAL);
INSERT INTO t VALUES (5, 2);
SELECT a / b FROM t;
Here he has explicitly declared the columns a and b to be of type
real. He then stores integer literal values into those columns. This
is where the problem occurs, not during the division in the select
statement.
SQL is a typed language. SQLite was originally an untyped
implementation of SQL. In version 3 SQLite was changed to introduce
stronger data typing, while still trying to maintain compatibility
with its previous untyped versions. It does this very well in most
cases. SQLite uses manifest typing, where each data value has its own
associated data type whereas the standard assumes each column has a
data type. In a few cases SQLite bumps into areas where this
implementation produces non-standard behavior. This is one of them.
To produce standard behavior, integer values stored into columns of
type real should be converted to real values.
This is actualy the problem we have,
even we take care of inserting /or importing data doing the right
bindings (to fellow the example above)
and it's diffult to care of everything ..
rc = sqlite3_prepare(db, "insert into t values (?,?);", -1, &stmt, 0);
if (rc == SQLITE_OK && stmt != NULL ) {
sqlite3_bind_double(stmt, 1, 5);
sqlite3_bind_double(stmt, 2, 2);
sqlite3_step(stmt);
rc = sqlite3_reset(stmt);
}
rc = sqlite3_finalize(stmt);
sqlite3> select a,b,(a/b) from t
5.0|2.0|2.5 Ok.
when a user issue an update command (i have no control on this command)
on the two columns with
sqlite3> update set a= 5, b =2;
sqliote3> select a,b,(a/b) from t;
5|2|2 which is not correct.
But i don't see a problem when explictly doing 5/2 gives 2. AS 5 and 2
are integers.
regards
hamid
