SQL error with JDBC, parameter out of range

Question

I had a problem with a PreparedStatement insert.

I have this exception:

java.sql.SQLSyntaxErrorException: 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 '?, ?, ?, ?, ?, ?)' at line 1

I think the problem is with the SQL date but I'm not sure. I have an Event Id, but it is an auto_increment attribute, so I think I don't need to place it in the query.

connect();

try {
    String sql = "INSERT INTO agenda(nombre_evento, fecha_evento, hora_inicio, hora_final, lugar, nota)VALUES(?, ?, ?, ?, ?, ?)";
    
    PreparedStatement preparedStatement = con.prepareStatement(sql);
    DateFormat parser = new SimpleDateFormat("yyyy-MM-dd");
    Date fechaInicio = parser.parse(txtFecha.getText().substring(0, 10));
    Date horaInicio = parser.parse(txtHoraInicio.getText().substring(0, 10));
    Date horaFinal = parser.parse(txtHoraFinal.getText().substring(0, 10));
    
    java.sql.Date fechaToSql = new java.sql.Date(fechaInicio.getTime());
    java.sql.Date horaInicioToSql = new java.sql.Date(horaInicio.getTime());
    java.sql.Date horaFinalToSql = new java.sql.Date(horaFinal.getTime());
    
    preparedStatement.setString(1, txtNombre.getText());
    preparedStatement.setDate(2, fechaToSql);
    preparedStatement.setDate(3, horaInicioToSql);
    preparedStatement.setDate(4, horaFinalToSql);
    preparedStatement.setString(5, txtLugar.getText());
    preparedStatement.setString(6, txtNotas.getText());
    
    preparedStatement.execute(sql);
} catch (SQLException ex) {
    System.out.print("NO");
    System.out.print(ex);
}
disconnect();

You say you've changed from '?' to ?, but the code in your question still has '?'. You need to post your actual code. — tgdavies, Aug 21 '21 at 01:50
You're calling `Statement.execute(String)` instead of `PreparedStatement.executeUpdate()` — tgdavies, Aug 21 '21 at 01:56
Again, update your question with the code you are running and the error. Please include the whole stack trace — tgdavies, Aug 21 '21 at 02:14
Not answering your question, I recommend you don’t use `DateFormat`, `SimpleDateFormat` and the two `Date` classes. Those classes are poorly designed and long outdated, the first two in particular notoriously troublesome. Instead use `LocalDate` from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). See [Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2](https://stackoverflow.com/questions/43039614/insert-fetch-java-time-localdate-objects-to-from-an-sql-database-such-as-h2). — Ole V.V., Aug 21 '21 at 05:18
You may want to check [similar question on `setDate` in `PreparedStatement`](https://stackoverflow.com/questions/18614836/using-setdate-in-preparedstatement) — Nowhere Man, Aug 21 '21 at 07:15
Under [the question that @AlexRudenko links to](https://stackoverflow.com/questions/18614836/using-setdate-in-preparedstatement) I recommend [the answer by Basil Bourque](https://stackoverflow.com/a/38809770/5772882) using `LocalDate` and `PreparedStatement.setObject()`. — Ole V.V., Aug 21 '21 at 14:58

SQL error with JDBC, parameter out of range

0 Answers0