Java BatchUpdateException in SQL processing

Question

The following code gives a BatchUpdateException and I have no idea why.

public void createContactmoment(Document document) {
    try {
        c = MySqlDAOFactory.getInstance().getConnection();
        //
        c.setAutoCommit(false);
        //
        String sql = "insert into index(iddocument,chapter,chapterdate)"
                + "values (?,?,?)";
        //
        prest = c.prepareStatement(sql);
        //
        int count = 1;
        //Formatter for the time
        DateTimeFormatter format = DateTimeFormat.forPattern("dd-MM-yyyy");
        //
         while (count <= Integer.parseInt(document.getTeachingWeeks())
            || count == document.getAllDates().size()
            || count == document.getContactMomentList().size()) {
            //
            if (MysqlDocumentDAO.getInstance().findByName(
                    document.getName()) == -1)
                return;
            //
            prest.setInt(1,MysqlDocumentDAO.getInstance().findByName(
            document.getName()));
            if (document.getContactMomentList().get(count) != null)
            prest.setString(2,document.getContactMomentList().get(count));
            else
            prest.setString(2, "No contactmoment found, but expected");
            //
            if (document.getAllDates().get(count) != null)
                prest.setString(3,(format.print(document.getAllDates().get(count))));
            else
            prest.setString(3, "Na date found, but expected");
            //
            prest.addBatch();
            //
            count++;
        }
        //
        prest.executeBatch();
        c.commit();
        //
    } catch (SQLException e) {
        JdbcLogging.info("Dit trad op bij het uitvoeren van de batchtaak (contactMoments)"+ " :" + e);
    } finally {
        MySqlConnectionFactory.getInstance().closeStatement(prest);
        MySqlConnectionFactory.getInstance().closeConnection(c);
    }
}

The error I see in my log is :

INFO JdbcLogging:25 - Dit trad op bij het uitvoeren van de batchtaak (contactMoments) :java.sql.BatchUpdateException: 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 'index(iddocument,chapter,chapterdate)values (44,'Examen; * Verdedigen project','' at line 1

So it seems that in the last update I'm missing some data. The data I want to write does excist (I checked with a system printline.

Any suggestions would be more then welcome.

Ravinder Reddy · Accepted Answer · 2012-05-15 12:49:01Z

3

index is a reserved word and should have not used for a table name. In your query put the word `index` in `back quotes` and execute then it should be working.

String sql = "insert into `index`(iddocument,chapter,chapterdate) values (?,?,?)";

answered May 15, 2012 at 12:49

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

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

1 Comment

Darth Blue Ray Over a year ago

I renamed the table end refactored my code and it does the trick!

wattostudios · Accepted Answer · 2012-05-15 13:02:17Z

1

Try adding a space between the 2 SQL lines - the ) and the word values should be separated...

String sql = "insert into index (iddocument,chapter,chapterdate) "
           + "values (?,?,?)";

Similarly, it wouldn't hurt to try adding a space between index and ( - it just depends on how picky the SQL interpreter is.

Also, index is a bad name for a table, as index has special meaning in many databases. I assume that index is the name of your table, and you aren't actually trying to insert data into an index!?

Finally, not sure if you need a ; at the end of the statement? It isn't usually required when building up the code like this, so maybe try without it first, and perhaps add it later if you're still having problems.

edited May 15, 2012 at 13:02

answered May 15, 2012 at 12:49

wattostudios

8,78013 gold badges45 silver badges58 bronze badges

4 Comments

Ravinder Reddy Over a year ago

not having a space after ) and values is not an error. And ; is part of JAVA syntax for end of a statement.

wattostudios Over a year ago

Well, in the exception output it shows chapterdate)values (ie, without a space), so I'm wondering whether the SQL interpreter is actually expecting a space there? Its not technically an error, however it does make it harder for the interpreter to correctly work out what the SQL statement is supposed to be doing.

Ravinder Reddy Over a year ago

exception trace actually starts from index(id... means error is with or before index

wattostudios Over a year ago

On that note, the first full 'word' of the exception trace is index(iddocument,chapter,chapterdate)values. I know that most interpreters should identify the brackets as being a separator, but I would take this as a definite assurance. If its just seeing this as a single word, which in this case would represent a table name, then its going to throw up.

Collectives™ on Stack Overflow

Java BatchUpdateException in SQL processing

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related