ceid-vg ceid-vg - 3 months ago 13
Java Question

JDBC Insert with Prepared Statement says I have SQL Syntax Error

I am trying a simple insert into my table but my program says I have a SQL Syntax error. Any ideas?


SQL CODE


CREATE TABLE ticket (
start_time DATETIME NOT NULL,
end_time DATETIME NOT NULL,
seat VARCHAR(50) NOT NULL,
sep VARCHAR(50) NOT NULL,
price INT(50) NOT NULL,
foroom VARCHAR(50) NOT NULL,
printer INT(15) NOT NULL,
PRIMARY KEY (seat)
);



JAVA CODE


try
{
System.out.println("Attempting to connect...\n");
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/baseis?autoReconnect=true&useSSL=false","root","");
System.out.println("Connection Succesful!!!");
String sql = "INSERT INTO ticket (start_time)" + " VALUES (?)";
PreparedStatement prepared = connection.prepareStatement(sql);
prepared.setString(1,"2016-07-17 19:00:00");
prepared.executeUpdate(sql);
}
catch(SQLException error)
{
System.out.println("Error: " + error.getMessage());
}



ERROR


Error: 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

Answer

You need to provide values for all NOT NULL columns:

// set some values for the record
String seat = "";
String sep = "";
int price = 0;
String foroom = "";
int printer = 0;

String sql = "INSERT INTO ticket (start_time, end_time, seat, sep, price, foroom, printer) VALUES (?, ?, ?, ?, ?, ?, ?)";
PreparedStatement prepared = connection.prepareStatement(sql);
prepared.setString(1, "2016-07-17 19:00:00");
prepared.setString(2, "2016-07-17 20:00:00");
prepared.setString(3, seat);
prepared.setString(4, sep);
prepared.setInt(5, price);
prepared.setString(6, foroom);
prepared.setInt(7, printer);
prepared.executeUpdate();