Roger Binns <rog...@...> writes: > > > MikeW wrote: > > I note that in the codebase, sqlite3IsNumber() will return False if an > > otherwise numeric value has leading and/or trailing whitespace, > > *overriding the table schema*. > > Such is dynamic typing ! > > Your dynamic typing statement is wrong. SQLite is dynamically typed in > that it allows the same column in different rows to have a different > data type. For example in one row the column 'id' could have an integer > stored and in the next row it could be a blob. Other SQL databases > force you to have the same type for all rows.
I am aware of that ... > > sqlite3_column_type reports the type you put in the column in the first > place. With some exceptions(*) what you put in is what you get out. > However you can ask for the value to be converted for you. For example > if you call sqlite3_result_text then SQLite will attempt to convert the > value to text for you. Similarly sqlite3_result_int will attempt to > convert it to integer. > I am aware of that ... > It does not attempt to look at your strings to see if they could pass as > integers and report them as that. Trying to do so would be harmful as > for example text phone numbers would be mangled to integers. I am aware of that ... > > In your case you put in text values with leading/trailing whitespace. > So SQLite recorded them as type text. If you want them treated as > integers then insert them as integers and don't include whitespace! Unfortunately the values come via a third party and may contain leading/trailing whitespace in the DB since they may have been manually inserted, so short of sanitising the DB at startup every time it is unavoidable. I guess one approach would be to compile with SQLITE_ENABLE_COLUMN_METADATA and then to read the column type from there rather than from sqlite3_column_type(). That would then indicate reading with sqlite3_column_int() and that ignores leading and trailing whitespace. > > (*) http://www.sqlite.org/datatype3.html > > Roger > Regard, MikeW _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
