Hi, SELECT oid, ip, name FROM hosts ; 1|a.proper.host.name|2886748296
shows that ip = 'a.proper.host.name ' and name = 2886748296, so of course your queries give no data. The problem is that your insert statement does not name the columns, which is never a good idea. Martin Am 11.03.2015 um 13:08 schrieb Jason Vas Dias: > 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 > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- *Codeswift GmbH * Kr?utlerweg 20a A-5020 Salzburg Tel: +49 (0) 8662 / 494330 Mob: +49 (0) 171 / 4487687 Fax: +49 (0) 3212 / 1001404 engelschalk at codeswift.com www.codeswift.com / www.swiftcash.at Codeswift Professional IT Services GmbH Firmenbuch-Nr. FN 202820s UID-Nr. ATU 50576309
