Exception in thread "main" java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]

Question

while trying to insert genename in ms access table through java code I am getting the error as

Exception in thread "main" java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Saccharomyces cerevisiae (strain ATCC 204508 / S288c)'. at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6964) at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7121) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3117) at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:337) at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(JdbcOdbcStatement.java:287) at sequence.SequenceAnalyser.(SequenceAnalyser.java:50)

my code is

try{
    executeUpdate = odbc.state.executeUpdate("INSERT INTO genename (GENE )VALUES ("+result+")");
    odbc.state.close();
}

what should I do?

Put the results of your query into a local variable and set a breakpoint there to see whether you're getting valid SQL. But in any case, you should be doing parameterized queries. — Paul Sasik, Apr 28 '15 at 21:35

score 0 · Answer 1 · answered Apr 29 '15 at 09:29

It looks like your result string contains

Saccharomyces cerevisiae (strain ATCC 204508 / S288c)

so when you inject that raw string into your SQL statement you end up with invalid syntax.

Instead, you should be using a parameterized query like this

String sql = "INSERT INTO genename (GENE) VALUES (?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, result);
ps.executeUpdate();

Exception in thread "main" java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]

1 Answers1