1

Whenever my primaryGrpId comes as null the following native query is failing with SQLGrammerException, because it is expecting NUMBER. Andy idea how to set NULL if parameter value is null with native query in JPA. The database is ORACLE.

@Modifying
@Query(nativeQuery = true, value = "update EVENT set PRIMARY_CATEGORY_ID=?2, PRIMARY_GROUPING_ID=?3 where PRIMARY_PERFORMER_ID=?1")
  void updatePrimaryCatGrpOfEntities(Long primaryPerfId, Long primaryCatId, Long primaryGrpId);

Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

All my DAO methods are just declared, no implementation is provided to write something like "setParameter"....etc.

enter image description here

Neil Stockton
  • 11,383
  • 3
  • 34
  • 29
user1614862
  • 3,701
  • 7
  • 29
  • 46
  • suppose this [post](https://stackoverflow.com/questions/30219486/spring-data-jpa-and-parameters-that-can-be-null) address this issue you are facing. – Rajith Pemabandu Aug 16 '17 at 00:21
  • possible duplicate https://stackoverflow.com/questions/21731343/jpa-native-query-set-null-parameter – Scary Wombat Aug 16 '17 at 00:26
  • see the answers in this [question](https://stackoverflow.com/questions/8211195/postgresql-jdbc-null-string-taken-as-a-bytea) – Scary Wombat Aug 16 '17 at 01:07
  • If [this near-duplicate question](https://stackoverflow.com/questions/8211195/postgresql-jdbc-null-string-taken-as-a-bytea) used Oracle as the underlying database instead of PostgreSQL I'd close this one as a duplicate. However, the different database makes it sufficiently different IMO to leave it open. Try the solutions given in the near-duplicate and hopefully one or more of them will help you resolve your issue. Best of luck. – Bob Jarvis - Слава Україні Aug 16 '17 at 01:19
  • 1
    I would not like to write any implementation for DAO method updatePrimaryCatGrpOfEntities. I don't see any of these answers are providing a solid solution especially for update queries. – user1614862 Aug 16 '17 at 01:26
  • 1
    It seems to be not possible to deal with NULL values with native queries in JPA – user1614862 Aug 16 '17 at 19:22
  • FWIW I'm also encountering this issue, and it looks unrelated to the posts other comments link to. – Andbdrew Mar 23 '18 at 17:30
  • In my case, luckily the nullable problem fields were all grouped together logically in a way that allowed me to just write 2 update queries (one with the non-null fields and one that omits them). It's a pure coincidence that the application logic allows for this non-null latch behavior, so you're probably not so lucky. I recommend switching to http://jdbi.org/ – Andbdrew Mar 26 '18 at 19:53
  • interestingly, it seems like you can set `String` columns to `null` using native queries like this, but I can't seem to get it to work with any other column type – Andbdrew Jun 04 '18 at 18:24
  • @user1614862 what's your oracle driver version? – Andbdrew Jun 28 '18 at 16:47

1 Answers1

-2

Check the column definition allows null.

NimChimpsky
  • 46,453
  • 60
  • 198
  • 311