It sounds like you loose some of the economy of sqlite by needing a more sophisticated front-end coded up to drive it (for example, to do type checking)...
At any rate, my needs aren't too complicated. I was looking for a portable replacement for MS Access to hold my research results (long time series of measurements) that was easier to script. Sqlite will do, I just need to be careful about gotchas like this. By the way, the affinity modes I was referring to in Section 6 appear to be different than the "normal" mode discussed in Section 2. They are either more restrictive (Strict affinity mode), or less restrictive (No affinity mode). Maybe these have not been implemented yet? Thanks for your help. David On 10/25/05, Dennis Cote <[EMAIL PROTECTED]> wrote: > David Finlayson wrote: > > >Thanks for the link <http://www.sqlite.org/datatype3.html>. In section > >6 of this page there is mention of Affinity Modes, how do you activate > >these? > > > > > See section 2.1 of that page. > > >As it is now you can get completely irrational behavior with > >mathematical operators: > > > >sqlite> create table temp (a INTEGER); > >sqlite> insert into temp values("1"); > >sqlite> insert into temp values("1.9"); > >sqlite> insert into temp values("2hello"); > >sqlite> insert into temp values("hello2"); > >sqlite> select * from temp; > >a > >---------- > >1 > >1.9 > >2hello > >hello2 > > > >sqlite> select sum(a) from temp; > >sum(a) > >---------- > >4.9 > > > >sqlite> select sum(a) from temp where a = "2hello"; > >sum(a) > >---------- > >2 > > > >sqlite> select sum(a) from temp where a = "hello2"; > >sum(a) > >---------- > >0 > > > >That is just irrational. str("2hello") should always return an error > >or null or maybe even 0, but surely not 2. Is this some kind of > >casting behavior inherited from C? > > > > > Sort of. The text values are converted from strings to integers using > atoi() which stops on the first illegal character. > > This usually isn't a problem in practice. Its is also kind of > "irrational" to have both string and numeric data in the same column and > expect to get a valid sum. > > If all the values are actually string representations of actual numeric > data, the conversions will succeed and the sum will be correct. This > behavior is a holdover from earlier versions of sqlite which were > completely typeless (all data was stored as text strings). > > >Also, significant white space between delimiters? ",0.9," is numeric > >", 0.9," is text. That isn't very friendly. > > > > > I agree, but this is only a problem with the .import meta command and > not something inherent in sqlite. You can always write your own program > to read the data the way you want (ignoring whitespace) and call the > correct sqlite API functions to bind that data with the correct type > when it is inserted into sqlite. > > > -- David Finlayson Marine Geology & Geophysics School of Oceanography Box 357940 University of Washington Seattle, WA 98195-7940 USA Office: Marine Sciences Building, Room 112 Phone: (206) 616-9407 Web: http://students.washington.edu/dfinlays
