Page 1 of 1
[SOLVED] SQLite won't INSERT?
Posted: Thu Aug 02, 2007 5:41 pm
by Chris Corbyn
EDIT | I've changed the thread title since my problem changed pretty quickly...
Code: Select all
sqlite> INSERT INTO user (user.username) VALUES ('a_person');
SQL error: near ".": syntax error
I thought that was an SQL standard? How can I explictly set the table name in this query -- I know it's abiguous but this is something which is generated from code (an ORM system) and I don't really want to have to go back and rewrite it all

Posted: Thu Aug 02, 2007 6:56 pm
by Chris Corbyn
OK, so having "hacked" my way around this issue until I find a solution (I do some preg_ work in the query before running it -- definitely not a permanent solution) I now am rid of the error. But I've got a new issue now
For those who can't tell, I've never used SQLite before
I've created a database table. I can insert into it from command line and then select the result. However, when inserting into it through PDO I get no errors, but affectedRows() returns 0 and nothing gets inserted. Any clues?
I thought it might be permissions but I've CHMOD the file to 0777 with no joy.
I read around the documentation on how inserts and updates work and it says it need to establish a file lock on the database and that it won't work on NFS mounts... this is not on a NFS mount, it's on a HFS+ file system. I've also tried the same code on a Debian Etch server without luck too
I'm guessing this is something silly I've missed but I'm totally baffled.
Posted: Thu Aug 02, 2007 7:21 pm
by Benjamin
Does it have anything to do with user being a reserved keyword? I don't know if that still holds true in what your using. Never used it.
Posted: Thu Aug 02, 2007 7:41 pm
by Chris Corbyn
astions wrote:Does it have anything to do with user being a reserved keyword? I don't know if that still holds true in what your using. Never used it.
You had me wondering there, but no, creating a new table called "foo" with just an ID and a username column also fails.
Hmm, now I feel a bit stupid because PDO does the same thing with MySQL. It's the first time I've used PDO but it seems to be operating in read-only mode or something.
/goes to consult PDO docs...
EDIT | No, take that back, MySQL does work, I just didn't type the DSN properly. Hmm, I'm off to bed, it's 2am and I'm wrecked

Posted: Sat Aug 04, 2007 9:34 am
by Chris Corbyn
Still not able to INSERT (or UPDATE) when I use PDO with an SQLite database. Anyone got any clues as to what could be the cause?

The DB file is set with 777 permissions and switching to a MySQL database, stil with PDO actually works for INSERT queries. I'm lost and google doesn't really help me here.
I can READ from the database just fine, it's just writing to it that doesn't work
SQLite 3 if it helps.
Posted: Sat Aug 04, 2007 9:37 am
by Chris Corbyn
Aaargghhh, the directory needed permissions setting too. I wonder why. Never mind, it's fixed now

Posted: Sat Aug 04, 2007 2:37 pm
by Oren
Man... again? You start a thread with yourself?

Posted: Sun Aug 05, 2007 7:34 am
by Chris Corbyn
Oren wrote:Man... again? You start a thread with yourself?

Nobody else talks to me

Posted: Sun Aug 05, 2007 10:50 am
by Oren
At least you always tag those threads with "[SOLVED]"

Thanks for the info
Posted: Thu Aug 16, 2007 3:30 am
by sleeplessboy
I had the same problem, after I set the directory permission, the problem solve.
sleeplessboy