David Nelson wrote: > > Getting "logic error or bad data" when using largw data strings: > > > I create the database as follows: > create table Event( Key TEXT[unique], DateTime DATE, Event TEXT, PRIMARY > KEY (Key) ) >
This is incorrect and not doing what you want. The unique constraint must be a separate word, what you have is a "type" called "TEXT[unique]". Furthermore being a primary key implies that Key must be unique so that constraint is redundant. Also, sqlite does not support a DATE type as you have used in your DateTime column. As you have just seen, sqlite allows almost anything to be used as a type name, but only a few special values have real meaning. See http://www.sqlite.org/datatype3.html for the details. The details of your table definition can be see using the table_info() pragma. sqlite> create table Event( Key TEXT[unique], DateTime DATE, Event TEXT, PRIMARY KEY (Key) ); sqlite> pragma table_info(Event); cid name type notnull dflt_value pk ---------- ---------- ------------ ---------- ---------- ---------- 0 Key TEXT[unique] 0 1 1 DateTime DATE 0 0 2 Event TEXT 0 0 The actual type of the data in the DateTime column can be seen using the typeof() function (after you put some data in the table). sqlite> insert or replace into Event values( 'strKey', '07/25/2008 08:00:00','da ta string' ); sqlite> select typeof(DateTime) from Event limit 1; typeof(DateTime) ---------------- text Try this table definition instead: create table Event( Key Text primary key, DateTime Text, Event Text ); > > I add records to the database as follows: > insert or replace into Event values( 'strKey', '07/25/2008 08:00:00', > 'data string' ) > > And I query the database as follows: > SELECT * FROM [Event] WHERE ([Event] LIKE "*foo*" AND [Event] LIKE > "*bar*") AND [Date/Time] BETWEEN DateAdd("n",-5,Now()) AND Now() > > > > The query works fine when my inserted data strings are small, like < 256 > bytes. > This query never worked in sqlite. The column name [Date/Time] is wrong, and the functions Now() and DateAdd() don't exist (unless you have created you own custom functions), and the LIKE operator uses %, not *, for a wildcard character. Also, literal strings should be enclosed in single quotes not double quotes. Try something like this instead: SELECT * FROM Event WHERE Event LIKE '%foo%' AND Event LIKE '%bar%' AND DateTime BETWEEN datetime('now', '-5 minutes') AND datetime('now'); > However, I get 'logic error' on the query if my data strings are largere, > like 2048 bytes. > It works fine for me with 3000 bytes of lorem ipsum text. sqlite> insert or replace into Event values( 'strKey2', '07/25/2008 08:00:01','L orem ipsum dolor sit amet, consectetuer adipiscing elit. Praesent porta tortor a t leo. Vestibulum fringilla tempor nisi. Quisque in tellus. Quisque sit amet pur us. Nulla euismod commodo lacus. Sed ut mi a urna pretium consectetuer. Cras fer mentum dignissim massa. Pellentesque ante. Donec commodo scelerisque tortor. Mor bi nisi lorem, ultrices quis, varius id, accumsan non, nulla. Vivamus mauris neq ue, pellentesque ac, pharetra posuere, accumsan non, est. Nulla eu enim. Integer aliquam libero tempor turpis. Donec ut libero ut pede mattis tristique. Vivamus est. ...> ...> Aenean vitae purus. Aenean et velit. Donec felis nunc, pretium imperdiet , lacinia ac, auctor at, massa. Ut vitae metus. In tempus viverra neque. Etiam f acilisis, pede eu posuere euismod, felis neque tristique metus, a rhoncus est ar cu a tellus. Vestibulum lacinia fringilla ante. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Curabitur pellentesque t ortor a neque. Mauris in quam. ...> ...> Phasellus rutrum. Pellentesque eget neque nec elit faucibus gravida. Aen ean eu augue. Integer interdum consequat arcu. Mauris interdum, nisl eu convalli s pulvinar, mi lectus cursus tellus, quis congue nisi risus eu felis. Morbi adip iscing, est ut adipiscing ultrices, libero orci condimentum nunc, quis luctus ni bh eros nec enim. Cras eros arcu, dignissim eget, ornare vitae, volutpat non, au gue. Pellentesque aliquam fringilla ipsum. Nullam vulputate consectetuer massa. Proin tincidunt pede a ante. Praesent luctus, nunc sed pellentesque suscipit, li bero felis ultrices lectus, et vehicula ante mi quis augue. ...> ...> Nunc sit amet eros ut velit faucibus varius. Nulla facilisi. Cras non pe de sed massa accumsan consequat. Proin in augue. Suspendisse potenti. Aenean tri stique consequat lorem. Donec suscipit mi eu nibh. Duis feugiat tellus. Vivamus tristique. Maecenas nunc lectus, egestas non, tempus eu, accumsan nec, sem. Sed iaculis lacinia nulla. ...> ...> Etiam sem dui, accumsan sagittis, congue ut, mollis venenatis, dolor. Mo rbi tincidunt, dolor hendrerit feugiat pellentesque, nulla justo venenatis quam, eu euismod turpis odio vitae nisl. Pellentesque eleifend, justo eget aliquet co ndimentum, mi velit lacinia turpis, non euismod velit neque vel tortor. Praesent augue quam, consequat vel, posuere eget, tincidunt eget, justo. Vivamus auctor dapibus ante. In lectus tortor, consequat ac, molestie non, pretium a, felis. Al iquam vulputate ante ac lorem. Nullam congue, magna ac congue euismod, nisl just o molestie velit, id egestas neque velit sed erat. Phasellus massa. In hac habit asse platea dictumst. Nunc elit magna, condimentum sit amet, dignissim sit amet, mattis vel, eros. Aliquam risus nulla, pulvinar eget, varius non, luctus sceler isque, ipsum. Morbi eleifend. Sed aliquam dignissim magna. Nulla vitae eros. Ali quam placerat pulvinar odio. In mollis purus eu erat. ...> ...> Aenean tortor purus, lobortis a, eleifend eget, sagittis sed, lacus. Don ec ut neque at odio congue eleifend. In mauris sapien, lobortis metus.' ); sqlite> select length(Event), Event from Event; length(Event) Event ------------- ----------- 11 data string 3010 Lorem ipsum Note, the truncation of the second record above is caused by the .column display mode which sets the length based on the first records data. Changing the display mode back to list and repeating the query returns all the data. sqlite> .mode list sqlite> select length(Event), Event from Event; length(Event)|Event 11|data string 3010|Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Praesent porta to rtor at leo. Vestibulum fringilla tempor nisi. Quisque in tellus. Quisque sit am et purus. Nulla euismod commodo lacus. Sed ut mi a urna pretium consectetuer. Cr as fermentum dignissim massa. Pellentesque ante. Donec commodo scelerisque torto r. Morbi nisi lorem, ultrices quis, varius id, accumsan non, nulla. Vivamus maur is neque, pellentesque ac, pharetra posuere, accumsan non, est. Nulla eu enim. I nteger aliquam libero tempor turpis. Donec ut libero ut pede mattis tristique. V ivamus est. Aenean vitae purus. Aenean et velit. Donec felis nunc, pretium imperdiet, lacini a ac, auctor at, massa. Ut vitae metus. In tempus viverra neque. Etiam facilisis , pede eu posuere euismod, felis neque tristique metus, a rhoncus est arcu a tel lus. Vestibulum lacinia fringilla ante. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Curabitur pellentesque tortor a neque. Mauris in quam. Phasellus rutrum. Pellentesque eget neque nec elit faucibus gravida. Aenean eu a ugue. Integer interdum consequat arcu. Mauris interdum, nisl eu convallis pulvin ar, mi lectus cursus tellus, quis congue nisi risus eu felis. Morbi adipiscing, est ut adipiscing ultrices, libero orci condimentum nunc, quis luctus nibh eros nec enim. Cras eros arcu, dignissim eget, ornare vitae, volutpat non, augue. Pel lentesque aliquam fringilla ipsum. Nullam vulputate consectetuer massa. Proin ti ncidunt pede a ante. Praesent luctus, nunc sed pellentesque suscipit, libero fel is ultrices lectus, et vehicula ante mi quis augue. Nunc sit amet eros ut velit faucibus varius. Nulla facilisi. Cras non pede sed m assa accumsan consequat. Proin in augue. Suspendisse potenti. Aenean tristique c onsequat lorem. Donec suscipit mi eu nibh. Duis feugiat tellus. Vivamus tristiqu e. Maecenas nunc lectus, egestas non, tempus eu, accumsan nec, sem. Sed iaculis lacinia nulla. Etiam sem dui, accumsan sagittis, congue ut, mollis venenatis, dolor. Morbi tinc idunt, dolor hendrerit feugiat pellentesque, nulla justo venenatis quam, eu euis mod turpis odio vitae nisl. Pellentesque eleifend, justo eget aliquet condimentu m, mi velit lacinia turpis, non euismod velit neque vel tortor. Praesent augue q uam, consequat vel, posuere eget, tincidunt eget, justo. Vivamus auctor dapibus ante. In lectus tortor, consequat ac, molestie non, pretium a, felis. Aliquam vu lputate ante ac lorem. Nullam congue, magna ac congue euismod, nisl justo molest ie velit, id egestas neque velit sed erat. Phasellus massa. In hac habitasse pla tea dictumst. Nunc elit magna, condimentum sit amet, dignissim sit amet, mattis vel, eros. Aliquam risus nulla, pulvinar eget, varius non, luctus scelerisque, i psum. Morbi eleifend. Sed aliquam dignissim magna. Nulla vitae eros. Aliquam pla cerat pulvinar odio. In mollis purus eu erat. Aenean tortor purus, lobortis a, eleifend eget, sagittis sed, lacus. Donec ut ne que at odio congue eleifend. In mauris sapien, lobortis metus. sqlite> HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
