PDA

View Full Version : MySQL Insert Issue (JAVA)



kevinharper
December 8th, 2012, 03:50 AM
I am having issues inserting into my MySQL DB.

I have successfully executed SELECT queries, but keep getting syntax errors. I even got one after inserting a row manually via PHPMyAdmin, copying the query it returned for the insert, and then pasting it into the SQL textarea.

The DB isn't used for anything except this project. But I'm not sure if I should just share the un/pwd on an open forum. :S

Here is the code that query construct that is passed via executeUpdate():

query = "INSERT INTO " + USERS + " ('id','user_name','password','full_name','active')" +
" VALUES(NULL,'" + userName + "','" + password + "','" + fullName + "',1)";

Here's what it looks like when I print it to the console:
INSERT INTO cis279JAVA.users ('id','user_name','password','full_name','active') VALUES(NULL,'testUN','testPWD','testName',1)

I have also tried the above string with single quotes (') around the 1, no difference.

spjackson
December 8th, 2012, 01:11 PM
You cannot use single quotes to quote column names. You have 3 options: don't quote the columns, use the MySQL default of backtick, or use the ANSI standard double quote.

You don't need to quote column names, unless there is something special about them, such as containing a space.


(id,user_name,password,full_name,active)


The MySQL default is to use backticks, but this is not portable to other DBMS.


(`id`,`user_name`,`password`,`full_name`,`active`)


You can use ANSI standard double quote, but you need to explicitly enable it in MySQL.


set session sql_mode = 'ansi';
("id","user_name","password","full_name","active")

kevinharper
December 8th, 2012, 08:01 PM
Worked like a charm! Awesome! Thank you for the reply. I spent hrs looking into this in the past few days. I must have overlooked that minor detail when reading through the dozens of tutorials.