PreparedStatement Insert in Java

Question

i have written a code, where if a button is pressed, values from textfields shall be taken to create a record in a database. the code compiles but when i run it, i get this error message:

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 'From, To, TotalDays, VacationType, Notes, Signature, Date) VALUES('','','','',''' at line 1

Any suggestions?

    final JButton btnSubmit = new JButton("Submit");
    btnSubmit.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent arg0) {                 
        try {

            String vacationid = text_vacationID.getText();
            String staffid = text_staffID.getText();
            String from = text_from.getText();
            String to = text_to.getText();
            String totaldays = text_totalDays.getText();
            String vacationtype = text_vacationType.getText();
            String notes = textArea.getText();
            String signature = text_signature.getText();
            String date = text_date.getText();


            String sql = "INSERT into vacation (VacationID, StaffID, From, To, TotalDays, VacationType, Notes, Signature, Date) VALUES" + "(?,?,?,?,?,?,?,?,?)";    

            PreparedStatement prest = con.prepareStatement(sql);
            prest.setString(1, vacationid);
            prest.setString(2, staffid);
            prest.setString(3, from);
            prest.setString(4, to);
            prest.setString(5, totaldays);
            prest.setString(6, vacationtype);
            prest.setString(7, notes);
            prest.setString(8, signature);
            prest.setString(9, date);

            prest.executeUpdate();
            JOptionPane.showMessageDialog(frmBookVacation, "Vacation has been booked for Employee with ID: " + vacationid);


        } 

        catch (SQLException e) {
        //System.out.println("Record couldn't be added!");
        e.printStackTrace();
        JOptionPane.showMessageDialog(frmBookVacation, "Vacation couldn't be booked. Please try again.");
        }
        }

        });
    btnSubmit.setBounds(201, 350, 89, 23);
    panel_1.add(btnSubmit);

From is a reserved keyword, To and Date probably too (check the manual). — Eduardo, Mar 08 '12 at 22:03

T.J. Crowder · Answer 1 · 2014-05-04T18:56:54.710

5

from is a reserved word in SQL, you need to escape it with backticks (or quotes if you have ANSI mode enabled).

String sql = "INSERT into vacation (`VacationID`, `StaffID`, `From`, `To`, `TotalDays`, `VacationType`, `Notes`, `Signature`, `Date`) VALUES" + "(?,?,?,?,?,?,?,?,?)";

I've done all of the column names there, for consistency and because a couple of the others might be reserved by some engines (or not).

edited May 04 '14 at 18:56

answered Mar 08 '12 at 22:02

T.J. Crowder

1,031,962
187
1,923
1,875

`DATE` is not a reserved keyword (at least, not anymore). – Amal Murali May 04 '14 at 17:41
@AmalMurali: Thanks, I've waffled that sentence now. :-) – T.J. Crowder May 04 '14 at 18:57

score 1 · Answer 2 · answered Mar 08 '12 at 22:03

1

"From" is a keyword. Remember, keywords in SQL are case-insenstive. Surround it in back quotes

`From`

answered Mar 08 '12 at 22:03

Mike Samuel

118,113
30
216
245

score 1 · Answer 3 · answered Mar 08 '12 at 22:05

1

Some columns names match SQL keywords: http://www.sql.org/sql-database/postgresql/manual/sql-keywords-appendix.html

answered Mar 08 '12 at 22:05

Puce

37,247
13
80
152

score 0 · Accepted Answer · edited May 04 '14 at 18:34

0

From is a reserved word in MySQL. Also you can get rid of all those fields from your table in the code as the following:

String sql = "INSERT into vacation VALUES" + "(?,?,?,?,?,?,?,?,?)";

That should work.

edited May 04 '14 at 18:34

hichris123

10,145
15
56
70

answered Mar 08 '12 at 22:06

8

*"Also you can get rid of all those fields from your table in the code as the following"* Only if you're specifying all of the fields, in the order in which the table has them defined, and no one ever edits the table definition. Very, very bad idea to rely on that. Explicit column names are used for a reason. – T.J. Crowder Mar 08 '12 at 22:09

PreparedStatement Insert in Java

4 Answers4