0

The DB2-express is throwing an error that seems impossible, when JPA tries to persist an entity:

ReportingSQLException: "ITEMID" is not valid in the context where it is used..

INSERT INTO NULLID."DynamicDatabaseTable" ("colname", "rownumber", "value")
VALUES (?, ?, ?) [params=?, ?, ?]

Since ITEMID is not used in the query above, how can this error be? The ITEMID is an auto-identity-generated column.

I have tried executing manually the query and it works OK, so anybody know what's all about? Even after lot of googling, I'm totally clueless here.

The database table in DB2 is fine, I have triple check:

"itemid" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1...), 
"rownumber" BIGINT NOT NULL, 
"colname" CHAR(30 OCTETS) NOT NULL, 
"value" VARCHAR(254 OCTETS)

I had created a simple java "JPA Entity from a table", by using the Eclipse wizard, and I've triple check the class is fine.

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name="\"itemid\"", unique=true, nullable=false)
private long itemid;

@Column(name="\"rownumber\"", nullable=false)
private long rownumber;

@Column(name="\"colname\"", nullable=false, length=30)
private String colname;

@Column(name="\"value\"", length=254)
private String value;

I have also restarted the whole computer just in case, but nothing... And I'm using most recent versions, clean installed couple of weeks ago.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
JCM
  • 548
  • 6
  • 15

1 Answers1

2

"itemid" is not equal itemid -- SQL identifiers are by default converted to upper case if not enclosed in double quotation marks. According to your DDL you created the table with lowercase column names. Apparently, JPA does not handle that properly and somewhere in the generated code uses unquoted itemid, which gets converted to upper case and, not surprisingly, the column cannot be found in the table.

There is no reason to use case sensitive identifiers in SQL, so I suggest you don't do that to avoid these kinds of problems.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
  • Re-created the table with uppercases _ITEMID_ and also re-created the entity with _insertable=false_ and it worked fine! It seems the ID field is somehow rescued from the DB to fill-in the java field. – JCM Mar 21 '15 at 07:38