com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax;

[tangara goh]

Hi,

Can someone tell me what's wrong with the below sql please cos I thought it is the same format at my the other sql statement....

project being the database name follows by the table name (teaches_subject) and then the column name...

Basically, I am using this in a GeneratedKeys() code;

the tutor_id will be the value of the generated keys.

String sql3 = "INSERT INTO project.teaches_subject(tutor_id) VALUES (?)";

Eclipse said that my sql has error... so I am showing my sql here...

Hope someone can tell me the wrong error...

tks.

[Ron McLeod]

tangara goh wrote:Eclipse said that my sql has error...

What exactly was the message?

[Brian Tkatch]

project being the database name follows by the table name (teaches_subject) and then the column name...

Table name comes after the schema, not the database name. In SQL Server, you can use database..table, if you want to specify the database name but not the schema name.

[tangara goh]

Ron McLeod wrote:

tangara goh wrote:Eclipse said that my sql has error...

What exactly was the message?

Hi Ron,

Here's the error message

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near '?)' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
	at com.mysql.jdbc.Util.getInstance(Util.java:387)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2546)

[Jesper de Jong]

Aha. Can you show how you are using this SQL statement exactly? The error is most likely in that piece of code.

You should use this SQL statement with class PreparedStatement, and set the appropriate parameter on the PreparedStatement object.

[tangara goh]

I managed to find the error now :

"INSERT INTO table name(tutor_id)"
+ "VALUES ('" +
+ tutor_Id
+ "' )";

[Knute Snortum]

I'm not sure that's a good solution.  It leaves you vulnerable to SQL injection attacks.  I'd follow Jesper's suggestion about using a PreparedStatement.