Confused about Mysql Insert Statement using JDBC

Question

I'm trying to insert a new row into a MYSQL database. The problem is actually how to structure the insert string used to prepare statement. the new row being inserted has new values except for one column that was obtaining its value from another table. I was using H2 for testing and this is how I structured the String.

"INSERT INTO RESERVATION CUSTOMER_CODE,ROOM_NUMBER,RESERVATION_DATE,START_DATE,END_DATE,
  TOTAL_PRICE) values(select CUSTOMER_CODE FROM CUSTOMER where EMAIL=?,?,?,?,?,?)"

I thought that the insert string would be the same for MYSQL, but it's not working. I googled around and found this answer https://stackoverflow.com/a/26080/1788917. So I Changed the String to;

"INSERT INTO RESERVATION  
 CUSTOMER_CODE,ROOM_NUMBER,RESERVATION_DATE,START_DATE,END_DATE,
  TOTAL_PRICE) select CUSTOMER_CODE FROM CUSTOMER where EMAIL=?,?,?,?,?,?"

I think it somehow worked because the error changed. But now the MYSQL driver is telling me that there is still an error. Somehow it's as if the quotation marks are more than required. this is the error

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 ''A13','2013-04-20','2013-04-20','2013-04-24',14000.0' 
   at line ...

meaning it has obtained the first value(CUSTOMER_CODE) properly from the other table but the rest of the values are quoted together which is what is bothering me. How can I solve this...

Where is my mistake?

I decided to write a method that would get me the customer code so that I can the insert statement without any select query. the string now looks like this

"INSERT INTO RESERVATION 
 (CUSTOMER_CODE,ROOM_NUMBER,RESERVATION_DATE,START_DATE,END_DATE,TOTAL_PRICE) 
 values(?, ?, ?, ?, ?, ?)"

But now mysql driver is complaining like this...

java.sql.SQLException: Parameter index out of range (2 > number of parameters, which 
    is 1).  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)

Which is surprising since I have put 6 question marks and not 1. The error comes up at the second line from these lines where I'm setting the values into the the ? placeholders.

preStatement.setLong(1, customer.getCustomerCode());
preStatement.setString(2, reservation.getRoomNumber());
preStatement.setDate(3, reservation.getReservationDate());
preStatement.setDate(4, reservation.getStartDate());
preStatement.setDate(5,reservation.getEndDate());
preStatement.setDouble(6, reservation.getTotalPrice());

So why is it that the MYSQL driver is complaining about the number of parameters when look fine to me. or what am i overlooking?

If only you could read the error message... maybe it explains what the problem is? — JB Nizet, Apr 20 '13 at 12:52
@ JB Nizet, almost at all times the message is helpful, but this time it is speaking in a different language — Obby, Apr 20 '13 at 13:04
Have you tried using your first statement, but with the inner select statement wrapped into parentheses? — JB Nizet, Apr 20 '13 at 13:07

score 0 · Answer 1 · answered Apr 20 '13 at 12:52

You are trying to do something with no sense.

First you declare your intention to insert

CUSTOMER_CODE,ROOM_NUMBER,RESERVATION_DATE,START_DATE,END_DATE, TOTAL_PRICE

But then you only provide 1 value CUSTOMER_CODE

The way to go would be something like

INSERT INTO RESERVATION  
CUSTOMER_CODE,ROOM_NUMBER,RESERVATION_DATE,START_DATE,END_DATE,TOTAL_PRICE) 
select CUSTOMER_CODE,ROOM_NUMBER,RESERVATION_DATE,START_DATE,END_DATE,TOTAL_PRICE where EMAIL=?;

Or, if you know the values

INSERT INTO RESERVATION  
CUSTOMER_CODE,ROOM_NUMBER,RESERVATION_DATE,START_DATE,END_DATE,TOTAL_PRICE) 
VALUES(?, ?, ?, ?, ?, ?);

At the end, the number of elements that you declare to INSERT has to be EQUAL to the VALUES you are inserting, the values can be declared explicitly (with the VALUE keyword) or they may be coming from a SELECT query, in which case you must not use the VALUE keyword

those columns don't come from `CUSTOMER` table, there are only in `RESERVATION` table. It's only the `customer_code` which is retrieved from the `CUSTOMER` table — Obby, Apr 20 '13 at 13:00
Fair enough, but then, either, do not declare these columns to be inserted or declare dummy/empty values for them. — Juan Antonio Gomez Moriano, Apr 20 '13 at 13:06

score 0 · Answer 2 · answered Apr 20 '13 at 13:14

0

Both of your attempts include this:

where EMAIL=?,?,?,?,?,?"

Your email can only equal one value. Maybe you meant this:

where EMAIL in (?,?,?,?,?,?)"

answered Apr 20 '13 at 13:14

Dan Bracuk

20,699
4
26
43

Confused about Mysql Insert Statement using JDBC

2 Answers2