On 2015/12/02 6:34 PM, Erwin Kalvelagen wrote: > Good morning. > > I wrote a little tool to dump certain data sets into a SQLite database. A > user suggested that I should not use type TEXT but rather type VARCHAR for > character columns, due to some issue with Excel/VBA. See the comments in: > http://yetanothermathprogrammingconsultant.blogspot.com/2013/10/gdx2sqlite.html > > > I could not find a reference to this using Google. So my question is: Is > this a known problem? I would like to understand a little bit better what > this is about.
The reason you are not finding a reference, is because it isn't true. SQLite (as others have mentioned) is ambivalent to those types - either will do. Personally I use VARCHAR(Len) in table column definitions - simply because my schema is then directly interchangeable with MySQL/PostGres and the SQLite query planner sometimes notes that length when considering data shape - but for data purposes, SQLite doesn't care and neither do any wrappers I know of. Also, I can't imagine Excel would have an issue, whether referencing a data object via VB Script or OLE DB table import etc, I have never seen it matter in Excels 2003 through 2013. My guess is your friend probably heard of some peculiarity and then possibly misheard or misinterpreted it to be related to Varchar vs. Text. Note that in MySQL/PostGres/MSSQL/Oracle there is a very big difference. Text (and its sub-types such as mediumtext, longtext etc.) usually is stored as a kind of character blob while Varchar(n) is more like a string with a length constraint. It's perhaps also prudent to note that in SQLite, Varchar(10) is just a Text type, and won't actually limit data entries into that field to only 10 characters. Good luck! Ryan