Hi All,
I'm seeing some strange behavior with sqlite 3.0.8.
When this script is sent through sqlite3:
create table generic ( user_id int not null, namespace varchar(255) not null, value text not null );
create unique index generic_pk on generic(user_id, namespace);
insert into generic values(1, 'iiiiiii', 'iiiiiii');
select * from generic;
select * from generic where namespace = 'iiiiiii';
select * from generic where namespace like 'iiiiiii';
the output is:
1|iiiiiii|iiiiiii 1|iiiiiii|iiiiiii 1|iiiiiii|iiiiiii
However if I:
1) create the table and index in a script
2) run my program that performs the insert, which gives only good error codes
3) run the select statements, the 1st and 3rd return the row
yet the 2nd returns no results
The strangness with the selects happens in sqlite3 and in my code. In my code I see:
cmd = "select <each column> from generic where user_id=1 and namespace='iiiiiii'
sqlite3_prepare(db, cmd, strlen(cmd), &stmt, &useless) ---> SQLITE_OK
sqlite3_step(stmt) ---> SQLITE_DONE when it relly should return SQLITE_ROW
I've seen this on Red Hat AS3 and Solaris.
SQLite is compiled thread-safe but the stangeness occurs with a single thread running.
It occurs with tables with an unique "primary key" index which is varchar.
Table with int primary keys work as desired.
Has anyone else seen this sort of thing? Any suggestions?
Much Thanks, Bob Gilson