Unfortunately, a need has arisen to change the data type of columns in existing tables in some quite large databases. This is due to the fact that Excel does not take kindly to data received from the SQLite ODBC driver unless they are of a certain data type (the declared name), i.e. the data are not perceived as the right type and so e.g. numerical summary functions will not work.
The issue is covered by this thread: http://www.mail-archive.com/sqlite-users@sqlite.org/msg64714.html This is the reason why I would want to change the data type of existing columns. Otherwise, I guess it would not matter that much as the types of SQLite are bound to the actual data values and not the columns in the schema which only have a data type affinity. Instead of changing the data type I could of course just enclose all columns in CAST statements when selecting data to Excel. However, I prefer to only have to do that when including expressions. Also, I have updated the frame work that I use to handle SQLite databases so that any future tables will only declare data types that will work correctly towards Excel. Thus I now wish to make sure that all existing databases conform with these data types. I was just about to write some code to browse through a data base, find all tables with columns declared as a certain data type to be changed and then move the data over to a new identical table (but defined with the new data type for the relevant columns). However, before going through this exercise I thought I would just ask if any one knew of a better way to do this, as there are several quite large databases to be so handled. When I first learned about SQLite I never like the relaxed handling of data where column data types from the schema are not enforced strictly on the actual data in the tables. This felt instinctively wrong as it is very contrary to other databases and many programming languages that I have worked with. However, I have come to appreciate SQLite as a powerful data container that you can use to handle data in various ways according to need. With e.g. check restraints one can enforce data types on columns at a basic level. On the other hand, it is sometimes more useful to just let data enter the database and then later on use data validation to the extent that it is needed. With this open philosophy of the SQLite database I thought that perhaps there really isn't any compelling reason to disallow changing the declared data type of an existing column since the actual stored data of a column might be of any type - again, according to the basic philosophy of SQLite. Thus changing a declared data type of an existing column should not break anything between the schema and the stored data. But I am not sure if it is even possible. If not, I will move my many Gigabytes of data around, but I thought, it would be worth just checking first. /Frank Missel _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
