Aargh! Thank You! Someone else's eyes do help sometimes...
On 11/03/2015, Hick Gunter <hick at scigames.at> wrote: > You have swapped data and field names in the insert. > > -----Urspr?ngliche Nachricht----- > Von: Jason Vas Dias [mailto:jason.vas.dias at gmail.com] > Gesendet: Mittwoch, 11. M?rz 2015 13:08 > An: sqlite-users at mailinglists.sqlite.org > Betreff: [sqlite] data which when inserted into a table cannot be queried - > a bug ? > > 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 == > sqlite> "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 > > > ___________________________________________ > Gunter Hick > Software Engineer > Scientific Games International GmbH > FN 157284 a, HG Wien > Klitschgasse 2-4, A-1130 Vienna, Austria > Tel: +43 1 80100 0 > E-Mail: hick at scigames.at > > This communication (including any attachments) is intended for the use of > the intended recipient(s) only and may contain information that is > confidential, privileged or legally protected. Any unauthorized use or > dissemination of this communication is strictly prohibited. If you have > received this communication in error, please immediately notify the sender > by return e-mail message and delete all copies of the original > communication. Thank you for your cooperation. > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
