0

I have a query like this

SCHEMA

|Firm_Id|Name |global_firm_id|
|  14   | Test| 9821         |

firmId is PK
global_firm_id is unique and not null


UPDATE FIRMS SET name = (:firmName) WHERE  global_firm_id = (:globalFirmId)";

in Spring boot I execute this

 var affectedRows = 0;
 KeyHolder holder = new GeneratedKeyHolder();
 SqlParameterSource parameters = new MapSqlParameterSource()
                .addValue("globalFirmId", globalFirmId)
                .addValue("firmName", firmName);
 affectedRows = namedParameterJdbcTemplate.update(UPDATE_FIRM_INFO, parameters, holder,new String[]{"FIRM_ID"});

I want the PK of the row updated in this case.

At runtime I get error

java.sql.SQLException: operation not allowed

I dont get what I am doing wrong. Can I not retrieve primary key in case I do an update?

EDIT: The absolute kicker is this logic worked in tests with H2 database but not when using oracle.

CONCLUSION: This is not doable even though h2 says otherwise. Personally H2 should mimic real DB as close as possible.

Praveen
  • 557
  • 1
  • 5
  • 20
  • You indeed cannot with an update. The class is named `GeneratedKeyHolder` for a a reason, this will only work for a INSERT statement that generates a primary key. – M. Deinum Jul 28 '22 at 14:05
  • @M.Deinum Is there any way to retrieve PK in same execution or do I need to create a separate query – Praveen Jul 28 '22 at 14:08
  • There is nothing in standard SQL that allows this, some database have special support for this (SQL Server for instance) but most solution do require and additional select. A quick google search shows that you can do this in Oracle but you would need to write a stored procedure/function for this. – M. Deinum Jul 28 '22 at 14:12

0 Answers0