CODEWITHSUNDEEP
javasql
Ste Prescott
Ste Prescott

Reputation: 1817

Java SQL Syntax error in INSERT INTO statement

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

Upvotes: 1

Views: 6777

Answers (3)

Ste Prescott
Ste Prescott

Reputation: 1817

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

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 50970

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).

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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)

Upvotes: 0

Related Questions