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
