[SOLVED] SQLite won't INSERT?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

[SOLVED] SQLite won't INSERT?

Post 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 :(
Last edited by Chris Corbyn on Sat Aug 04, 2007 9:37 am, edited 2 times in total.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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 :P

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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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 :(
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Aaargghhh, the directory needed permissions setting too. I wonder why. Never mind, it's fixed now :)
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

Man... again? You start a thread with yourself? :lol: :wink:
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Oren wrote:Man... again? You start a thread with yourself? :lol: :wink:
Nobody else talks to me :(
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

At least you always tag those threads with "[SOLVED]" :wink:
sleeplessboy
Forum Newbie
Posts: 1
Joined: Thu Aug 16, 2007 3:25 am

Thanks for the info

Post by sleeplessboy »

I had the same problem, after I set the directory permission, the problem solve.

sleeplessboy
Post Reply