MySQLSyntaxErrorException inserting into database

Question

I have seen lot of answers over here regarding my question, but can not find solution for it. I am reading a excel file and storing in mysql database. This is my code

PreparedStatement sql_statement = (PreparedStatement) con
                .prepareStatement("insert into medtest(FMCODE,FLAG,MCODE,EMPNO,NAME,ADDRESS1,ADDRESS2,ADDRESS3,BALANCE,HOSPITAL_O,HOSPITAL_I,NURSING,GENERAL,PRIVATE,SPLCODE,BKCD,ACCOUNT_NO) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        wb = WorkbookFactory.create(new File(filePath));
        // System.out.println(wb.getSheetName());
        Sheet mySheet = wb.getSheetAt(0);

        for (Row row : mySheet) {
            String fmcode = row.getCell(0).getStringCellValue();
            String flag = row.getCell(1).getStringCellValue();
            int mcode = (int) row.getCell(2).getNumericCellValue();
            String empno = row.getCell(3).getStringCellValue();
            String name = row.getCell(4).getStringCellValue();
            String address1 = row.getCell(5).getStringCellValue();
            String address2 = row.getCell(6).getStringCellValue();
            String address3 = row.getCell(7).getStringCellValue();
            double balance = (double) row.getCell(8).getNumericCellValue();
            double hospital_o = (double) row.getCell(9)
                    .getNumericCellValue();
            double hospital_i = (double) row.getCell(10)
                    .getNumericCellValue();
            double nursing = (double) row.getCell(11).getNumericCellValue();
            double general = (double) row.getCell(12).getNumericCellValue();
            double prvte = (double) row.getCell(13).getNumericCellValue();
            String splcode = row.getCell(14).getStringCellValue();
            String bkcd = row.getCell(15).getStringCellValue();
            String account_no = row.getCell(16).getStringCellValue();
            String sql = "insert into medtest values('" + fmcode + "','"
                    + flag + "','" + mcode + "','" + empno + "','" + name
                    + "','" + address1 + "','" + address2 + "','"
                    + address3 + "','" + balance + "','" + hospital_o
                    + "','" + hospital_i + "','" + nursing + "','"
                    + general + "','" + prvte + "','" + splcode + "','"
                    + bkcd + "','" + account_no + "')";
            PreparedStatement ps = (PreparedStatement) con
                    .prepareStatement(sql);
            ps.executeUpdate();

When i am inserting my excel file into database getting the below exception, how to solve it.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
        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
        'S DENTAL CLINIC','KINGSWAY CAMP, DELHI.','0.0','0.0','0.0','0.0','0.0','0.0','nu'
        at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    ...

The error is pointing to the ps.executeUpdate(); line.

You are executing sql_statement but all the thing you get from your excel file are put into a query (BTW this is very wrong) and build into another prepared statement (ps) which is never executed. — Nitek, May 21 '15 at 05:15
I don't think building your own SQL-String is a good idea. Have look at this example (http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html) on how to hand over your parameters into the prepared statement, make use of your "sql_statement" and throw away the "ps" part — Nitek, May 21 '15 at 05:30
"I am getting an error" is meaningless. Please include the *exact* error(s) you're seeing in your question, otherwise it's very difficult for people to help you. — dimo414, May 21 '15 at 05:32
@dimo414 I have updated my question, once can you see into it — spt, May 21 '15 at 06:40
@spt your "update" is a completely new question. If you have a new question, feel free to post one, but please don't hijack an existing question. — dimo414, May 21 '15 at 06:58

score 0 · Answer 1 · answered May 21 '15 at 05:27

Replace

 sql_statement.executeUpdate() with ps.executeUpdate().

You are trying to execute preparedstatement sql_statement without setting the parameters. Either you can set the parameters and invoke sql_statement.executeUpdate() or simply replace sql_statement.executeUpdate with ps.executeUpdate().

score 0 · Answer 2 · edited May 23 '17 at 12:14

The error message tells you exactly what's wrong - your query is broken around 'S DENTAL CLINIC'. My guess is this is part of a larger string, like BOB'S DENTAL CLINIC - do you see the problem now? The string you're trying to insert into the query contains a ', breaking the rest of the query.

The right thing to do here, as already suggested, is to use Prepared Statements. You should never construct dynamic SQL statements via string concatenation; not only does it risk these sort of syntax errors, but it's also the cause of SQL injection attacks.

Some related questions:

MySQLSyntaxErrorException inserting into database

2 Answers2