1

I am trying to add a new record to a table from Derby database. I need to use the record number in one of its fields. E.g. I need to save a path to the image in photo field. And the name of the image must corresponds its id. For example 1.jpg. I was trying this statement (file extention is not used in this example):

PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Users (name, lastname, email, address, password, photo, lastvisit, status) VALUES ('" + name + "','" + lastname + "','" + email + "','" + address + "','" + DigestUtils.toMd5(password) + "', id, '" + now + "','user')");

But I get an error:java.sql.SQLSyntaxErrorException: Column 'ID' is either not in any table in the FROM list... But this column definitely exists. What is the reason of the error?

vitaliy4us
  • 483
  • 5
  • 21
  • 7
    Unrelated, but: please learn to use a `PreparedStatement` **properly**. Do not concatenate values into a SQL string. Use placeholders (`?`) instead. –  Jan 14 '17 at 10:42
  • Do you mean that if the newly created `Users` record gets a user ID of 42, you'd want the value of `photo` to be `'42.jpg'`? If so, then you either need an update statement *after* the insert returns the new ID to you, or you need to do it in a trigger. – Andreas Jan 14 '17 at 11:17
  • See http://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc on how to get a generated id, you then use that value in a subsequent statement. – Mark Rotteveel Jan 14 '17 at 11:20
  • Yes, this is the code I'm using now. Moreover I have to do even more by using this id in the next SQL statement adding the file path to the photo field. But it is looking too much complicated and I hope it should be more convinient decision. – vitaliy4us Jan 14 '17 at 11:34

1 Answers1

1

Well the short answer imho is that you cannot do that in SQL. In your table Users, I guess that your id column is an auto incremented primary key. What you can do is insert your record and then get the last id generated for this insert statement. Then you have to perform an update query to set the value of your photo column.

By the way, definitely learn to use placeholders in prepared statements

I'm not a specialist of Derby but browsing the doc I found how you can get the generated columns. 

pstmt.execute(sql, Statement.RETURN_GENERATED_KEYS);
ResultSet keys = pstmt.getGeneratedKeys();
keys.next();
int id = keys.getInt();
Sébastien Maloron
  • 441
  • 2
  • 6