Good day - This is the first problem I've encountered with SQLite having used it trouble free for a number of years, so I was surprised when I discovered I can insert data into a table that then cannot be queried :
I have a table: CREATE TABLE hosts ( ip INTEGER NOT NULL , name TEXT NOT NULL ); My application does: BEGIN TRANSACTION; INSERT INTO hosts VALUES ( "a.proper.host.name", 2886748296) ; COMMIT; And then the data cannot be queried with sqlite : $ sqlite3 my.db SQLite version 3.8.8.1 2015-01-20 16:51:25 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> SELECT oid, ip, name FROM hosts ; 1|a.proper.host.name|2886748296 sqlite> SELECT oid, ip, name FROM hosts WHERE ip == 2886748296; sqlite> Note : no result found ! Why ? No good to query by name either : sqlite> SELECT oid, ip, name FROM hosts WHERE name == "a.proper.host.name" ; sqlite> Selecting by oid==1 does work, but the whole point of this table for my application is to map host names and addresses to an unique integer OID which is the key referenced in many other tables: CREATE TABLE ... host INTEGER REFERENCES (hosts.oid) ON DELETE CASCADE , ... Please could anyone suggest why sqlite is failing to select record 1 by the values of any of its fields except oid (ROWID) ? sqlite> EXPLAIN SELECT oid, ip, name FROM hosts WHERE ip == 2886748296; 0|Trace|0|0|0||00| 1|Goto|0|16|0||00| 2|OpenRead|0|11|0|2|00| 3|OpenRead|1|12|0|k(1,B)|00| 4|Int64|0|1|0|2886748296|00| 5|SeekGe|1|13|1|1|00| 6|IdxGE|1|13|1|1|01| 7|IdxRowid|1|2|0||00| 8|Seek|0|2|0||00| 9|IdxRowid|1|3|0||00| 10|Column|1|0|4||00| 11|Column|0|1|5||00| 12|ResultRow|3|3|0||00| 13|Close|0|0|0||00| 14|Close|1|0|0||00| 15|Halt|0|0|0||00| 16|Transaction|0|0|0||00| 17|VerifyCookie|0|25|0||00| 18|TableLock|0|11|0|hosts|00| 19|Goto|0|2|0||00| sqlite> Any ideas anyone ? Any responses gratefully received. I'm running sqlite built from source tarball: sqlite-autoconf-3080801.tar.gz on an Ubuntu 14.04.02 x86_64 8-core Intel i7 (Haswell) system with gcc-4.8.2. I built it from source because the Ubuntu sqlite 3.8.2 does not support the printf() function - but the same problem happens when the 3.8.2 version accesses the same database file - it cannot select any data from the 'hosts' table by non-OID field values. Thanks & Regards, Jason