On Wed, Mar 11, 2015 at 7:08 AM, Jason Vas Dias
<jason.vas.dias at gmail.com> wrote:
> 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;
Looks wrong to me. The first value should be the "ip" and the second
the "name". You have them in the opposite order.
BEGIN TRANSACTION;
INSERT INTO hosts
VALUES ( 2886748296, "a.proper.host.name" )
; COMMIT;
or
BEGIN TRANSACTION;
INSERT INTO hosts(name,ip)
VALUES ( "a.proper.host.name", 2886748296)
; COMMIT;
Despite the fact that "ip" is an INTEGER, SQLite is happy to save a
character string into it. I guess this is part of the "lite".
>
> 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
--
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
