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.
