Zibetti Paolo <[EMAIL PROTECTED]> wrote: > I read in the "changes" page of the SQLite site that version 3.3.x of SQLite > features "Separate INTEGER and REAL affinity". > > What does this exactly mean ? > How is SQLite 3.3.x different from 2.8.x with respect to column affinity ? >
Version 2 stores all data as strings. If you insert an integer into a column, it converts the integer into a string before inserting it. Version 3 knows about other data types. If a column is declared VARCHAR, it will automatically convert any numbers you insert into strings. If a column is declared INTEGER, it will attempt to convert strings you insert into integers. If it cannot convert the string into an integer, it goes ahead and inserts it as a string. Likewise, REAL columns try to convert the values you insert into floating-point numbers, if it can do so without loss of information. Other RDBMSes do similar automatic conversions. If you try to insert a string into an integer column of other database and the string looks like an integer, the database will do the conversion for you. If the string does *not* look like an integer, however, most other database engines will throw an error. SQLite does not do this. It goes ahead and stores the string. The unusual behavior of SQLite is considered a feature, not a bug. Beginning with version 3.3.0, SQLite is a little more fussy about its type conversions. Formerly, if a column was of type REAL, it would be happy to accept an integer. But now, it converts the integer into a floating-point value. -- D. Richard Hipp <[EMAIL PROTECTED]>
