On Mon, Apr 26, 2010 at 7:59 PM, Black, Michael (IS)
<michael.bla...@ngc.com> wrote:
>
> First off confirm it's not a bug with sqlite2:
>
Michael , thank you for this checklist.
Here is what I have.
>
> sqlite> create table Groups (name varchar(10));
> sqlite> insert into Groups values('bob');
> sqlite> insert into Groups values('jean-baptiste');
> sqlite> select * from Groups where name='jean-baptiste';
> jean-baptiste
>
> If you don't get a results this way tje sqlite2 is the problem (which I
> doubt).
I get the result perfectly. So this is not sqlite2 issue. I doubt it
could be sqlite issue so I never looked at it this way. But worth a
shot.
>
> Then do an sql .dump of your table.
> sqlite> .dump Groups
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE Groups (name varchar(10));
> INSERT INTO "Groups" VALUES('bob');
> INSERT INTO "Groups" VALUES('jean-baptiste');
> COMMIT;
>
> Then you should be able to see the SQL representation of the string and
> perhaps see what your problem is.
Ok, i tried this suggestion on both the test table we made above and
my own current DB. I see the dashes.
>
> I don't know if sqlite2 has the .mode command, but if it does it's simpler
> yet.
>
> sqlite> .mode insert
> sqlite> select * from Groups where name like('%jean%');
> INSERT INTO table VALUES('jean-baptiste');
>
I did not go this far. Weirdly enough all your testing showed me the
real cause. The query egenrated by PHP uses quotes around column names
for select statements. so if I use
sqlite> Select * from Groups where 'name' = 'Jean-baptiste';
// will not work
sqlite> Select * from Groups where name = 'Jean-baptiste';
// works
sqlite> Select * from "Groups" where "name" = 'jean-baptiste';
//works for double quotes around tabel and column names.
This is weird, just the day I posted all the problems I used double
quotes. I even tried with various combos. The PHP framework I have
been using always generates the last combination of quotes so it
should have worked. I don't know the reason for this happening or
something has changed since then that I cannot find. I am going to run
the the queries again to see if I get results in PHP, if not this is
not an sqlite problem anymore, mor elikely a driver or framework
issue.
Either that or I am incredibly stupid for having wasted everyone's
time. Apologies if that is the case.
jason.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
