Page 1 of 1
Cant insert
Posted: Fri Jul 27, 2007 6:48 pm
by Dave2000
Hi,
I have done something to my MySQL database

I am no longer able to successfully execute INSERT queries.
I can do other queries fine - updade, select, delete all work fine - just not INSERT
The user has the privilege to do update. "ALL PRIVILEGES" is set..
I am using code that I know is right because I haven't changed it and it worked fine a few days ago. I have tried writing new code specifically to get any sort of update working...
Code: Select all
CREATE TABLE `test` (
`id` INT( 10 ) NOT NULL ,
`name` VARCHAR( 20 ) NOT NULL
)
Code: Select all
INSERT INTO `test` ( `id` , `name` )
VALUES (
'2', 'tt'
);
I also tried in phpmyadmin - nothing
Is there some kind of privilege I may be missing? Well, there must be, but I have no idea what... Help! Thanks in advance!

Posted: Fri Jul 27, 2007 6:56 pm
by timvw
You're abusing the backticks `... Since you don't even have problematic table/column names you could easily do without them... And in SQL (what's so standard if every dbms has it's own dialect anway) string values have to be quoted...
Code: Select all
INSERT INTO test ( id, name ) VALUES ( 1 , 'xxx' );
Posted: Fri Jul 27, 2007 7:16 pm
by superdezign
timvw wrote:You're abusing the backticks `
Abusing the proper syntax...?
An in response to the question, it may be an issue if setting your INT column to the value of a string, but I believe that MySQL automatically converts the value that you give into an integer. I could be mistaken, however.
Posted: Fri Jul 27, 2007 7:56 pm
by Dave2000
Sorry to waste everyone's time. I made a stupid mistake. The SQL statement I showed in my first post did insert the data - it was just that I had got so used to it not inserting that I didn't notice when it did insert.
What I was doing wrong...
My production server is MySQL 4.1.10. Inserting into an auto_incremented field can be done like
Code: Select all
INSERT INTO test (auto_increment_field, aaa) VALUES ('', 'hello');
but on my development server (MySQL 5.0.37) it only works if done like
Code: Select all
INSERT INTO test (auto_increment_field, aaa) VALUES (NULL, 'hello');
I dont know if this is an option that can be set or a change between versions...
Again, sorry to waste everyone's time.
Shears
Posted: Fri Jul 27, 2007 9:13 pm
by superdezign
Shears wrote:My production server is MySQL 4.1.10. Inserting into an auto_incremented field can be done like
Code: Select all
INSERT INTO test (auto_increment_field, aaa) VALUES ('', 'hello');
but on my development server (MySQL 5.0.37) it only works if done like
Code: Select all
INSERT INTO test (auto_increment_field, aaa) VALUES (NULL, 'hello');
The whole point of the auto_increment field is so that you do not have to set it's value. Just remove it from the query and let it increment on it's own.
Posted: Sat Jul 28, 2007 2:41 am
by timvw
superdezign wrote:
Abusing the proper syntax...?
VALUES ( 1, `name` )
string values belong between quotes, not backticks...
Code: Select all
-- these days i prefer to be explicit as possible, but this one would work too...
INSERT INTO test ( aaa ) VALUES ( 'xxx' );
Posted: Sat Jul 28, 2007 6:24 am
by superdezign
timvw wrote:string values belong between quotes, not backticks...
But nowhere in the example are backticks used to surround a string, only the table name and column names...

Posted: Sat Jul 28, 2007 11:57 am
by timvw
superdezign wrote:timvw wrote:string values belong between quotes, not backticks...
But nowhere in the example are backticks used to surround a string, only the table name and column names...

You're right (My silly underconscience tricked me into seeing things that weren't there...)
Posted: Sat Jul 28, 2007 12:22 pm
by superdezign
timvw wrote:(My silly underconscience tricked me into seeing things that weren't there...)
