SQL exception preparing query with ORMLite

I am using an ORM (ORMlite) and all my calls are going well until I get the following error.

Exception in thread “main” org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement ” SELECT * FROM “”STORIES”” WHERE “”TITLE”” = ‘Deepcut case leads ‘NOT FOLLOWED[*]” “; SQL statement: SELECT * FROM Stories WHERE title = ‘Deepcut case leads ‘not followed” [42000-152] at org.h2.message.DbException.getJdbcSQLException(DbException.java:327) at org.h2.message.DbException.get(DbException.java:167) at org.h2.message.DbException.get(DbException.java:144) at org.h2.message.DbException.getSyntaxError(DbException.java:179) at org.h2.command.Parser.getSyntaxError(Parser.java:480) at org.h2.command.Parser.prepareCommand(Parser.java:229) at org.h2.engine.Session.prepareLocal(Session.java:426) at org.h2.engine.Session.prepareCommand(Session.java:374) at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1093) at org.h2.jdbc.JdbcPreparedStatement.(JdbcPreparedStatement.java:71) at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:601) at com.j256.ormlite.jdbc.JdbcDatabaseConnection.compileStatement(JdbcDatabaseConnection.java:83) at com.j256.ormlite.stmt.mapped.MappedPreparedStmt.compile(MappedPreparedStmt.java:44) at com.j256.ormlite.stmt.StatementExecutor.buildIterator(StatementExecutor.java:169) at com.j256.ormlite.stmt.StatementExecutor.query(StatementExecutor.java:119) at com.j256.ormlite.dao.BaseDaoImpl.query(BaseDaoImpl.java:189)

I’m confused as to whats going wrong. I am calling the search from these lines:

// get our query builder from the DAO
QueryBuilder<Story, Integer> queryBuilder = StoryDao.queryBuilder();
// the 'title' field must be equal to title (a variable)
queryBuilder.where().eq(Story.TITLE_FIELD_NAME, title);
// prepare our sql statement
PreparedQuery<Story> preparedQuery = queryBuilder.prepare();
// query for all stories that have that title
List<Story> accountList = StoryDao.query(preparedQuery);

Answer

Syntax error in SQL statement ” SELECT * FROM “”STORIES”” WHERE “”TITLE””…

@bemace is correct that there seem to be quotes in the title that is screwing up the escaping of strings generated by the query.

In ORMLite, you should use the SelectArg feature which will generate a query with SQL ? arguments and then pass the string to the prepared statement directly.

See the documentation on the SelectArg. With it, you’d do something like:

QueryBuilder<Story, Integer> queryBuilder = StoryDao.queryBuilder();
SelectArg titleArg = new SelectArg();
queryBuilder.where().eq(Story.TITLE_FIELD_NAME, titleArg);
PreparedQuery<Story> preparedQuery = queryBuilder.prepare();
titleArg.setValue(title);
List<Story> accountList = StoryDao.query(preparedQuery);

Advertisement

Answer