Java SQL Syntax error in INSERT INTO statement

Question

I am trying to insert the data below into a table that has the columns ID[autoNumber], type[text], to[text], from[text], message[text] and attachmentLoc[text]

So the autonumber is generated I insert them supplying the column names. When it runs the console prints out:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3149)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:216)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(JdbcOdbcPreparedStatement.java:138)
at MultiEchoServer.saveMessage(MultiEchoServer.java:147)
at ClientHandler.run(MultiEchoServer.java:409)

The code that is bringing the error is :

try
    {
        String sql = "INSERT INTO Messages (type, to, from, message, attachmentLoc) VALUES(?,?,?,?,?)";     
        PreparedStatement stmt = database.prepareStatement(sql);
        stmt.setString(1, message.getType());
        stmt.setString(2, message.getTo());
        stmt.setString(3, message.getFrom());
        stmt.setString(4, message.getMessage());
        stmt.setString(5, attachmentLoc);
        stmt.executeUpdate();
    }
    catch (SQLException e)
    {
        System.out.println("Could not perform statement");
        e.printStackTrace();
    }

Thanks for your help

Could it be because I am using the word from in my database? — Ste Prescott
– Ste Prescott, Commented Apr 18, 2012 at 17:27

Larry Lustig · Accepted Answer · 2012-04-18 18:36:53Z

1

You don't state what database engine you're using, but a number of the column names (as well as the table name) are likely candidates for reserved words. The very most obvious is from.

You will need to write those identifiers in identifier quotes which are generally either the backtick character (`) or the characters [ and ] (depending on engine):

INSERT INTO `Messages` (`type`, `to`, `from`, `message`, `attachmentLoc`)
  VALUES (?,?,?,?,?);

or

INSERT INTO [Messages] ([type], [to], [from], [message], [attachmentLoc]) 
   VALUES (?,?,?,?,?);

The quoting is only required around the identifiers that are reserved words but using it around all identifiers will not hurt. Also, no reason not to include the space after VALUES and the semi-colon at the end of the statement (although most engines don't need either).

answered Apr 18, 2012 at 18:36

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ste Prescott Over a year ago

I answered my own question, thanks anyway. It was because from was a reserved word. I change the column name to messageFrom

Larry Lustig Over a year ago

Different engines establish different lists of reserved words. I'd watch out for type, to, and message as well. Obviously they're not reserved in whichever SQL product you're using now but if you switch out the engine later it's something to be aware of.

Larry Lustig Over a year ago

PS: When you answer your own question, you should post it as an answer, not a comment, then (after a brief delay) you can mark your own answer correct. You don't score points for accepting your own answer, but it removes the question from the "still open" list and helps people who come along in the future find the correct answer.

Gordon Linoff · Accepted Answer · 2012-04-18 18:10:54Z

0

You need valid syntax for the INSERT statement. SQL does not understand the question mark.

Perhaps, you want one of the following:

'?'
NULL
''

Or some other value.

So, assuming the columns accept NULL values, you could use: VALUES(NULL, NULL, NULL, NULL, NULL)

answered Apr 18, 2012 at 18:10

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Larry Lustig Over a year ago

Those are parameters placeholders.

Ste Prescott · Accepted Answer · 2013-03-02 00:50:42Z

0

From is a reserved word, after this was changed to messageFrom it allowed the insertion to the database.

answered Mar 2, 2013 at 0:50

Ste Prescott

1,8172 gold badges23 silver badges43 bronze badges

Collectives™ on Stack Overflow

Java SQL Syntax error in INSERT INTO statement

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related