Invalid value "0" for parameter "parameterIndex" [90008-193]

[Carsten Langsdorf]

Hi everyone,

I'm getting this strange error on attempting to insert a row. I already found and read through several similar occurrences here in the forum, and although these gave me some sort of idea about the issue, I still can't see how to work around this.

Okay, here's a more detailed sitrep:

I am working on a JavaFX program that uses H2 in embedded mode to store data in a local database (for now). I'm not using JDBC directly, but running through JPA with DataNucleus as persistence provider.

The product versions are:

• jdk1.8.0_121
  
• h2-1.4.193
  
• DataNucleus 5.0.7

In one window I deal with 2 tables (seperately), basically doing CRUD operations. Among other things there is an 'Add' button for each table - which, of course, implements INSERTing a new record via JPA persist.

One of the 2 buttons works like a charm. Using the other one, however, raises an exception:

Caused by: javax.persistence.PersistenceException: Cannot set Object parameter: value = 0 for column "ODDLOOT.BASE.ACCOUNT.ACCOUNTTYPEID" : Unerlaubter Wert "0" für Parameter "parameterIndex"
Invalid value "0" for parameter "parameterIndex" [90008-193]

At first, this misled me into thinking there is a problem with the default value for column ODDLOOT.BASE.ACCOUNT.ACCOUNTTYPEID, as it references another table and 0 would not be a valid value. I had not been worrying about the default values generated by DataNucleus, because the database itself holds defaults values for these columns, and it works smoothly on the other table that is handled by this window. But to be sure, I used a DataNucleus extension (@PrePersist) to even set the value originally set by DataNucleus to 1 - which would be valid. Here's the result:

Caused by: javax.persistence.PersistenceException: Cannot set Object parameter: value = 1 for column "ODDLOOT.BASE.ACCOUNT.ACCOUNTTYPEID" : Unerlaubter Wert "0" für Parameter "parameterIndex"
Invalid value "0" for parameter "parameterIndex" [90008-193]

Thus, this is not the origin of the problem. Searching for similar incidents told me that it is related to the numbering of parameters in prepared statements. Obviously enough, I still have 2 issues here:

1. DataNucleus creates the prepared statements, i got no control here
2. The same thing works with the other table

I already checked the code several times and most thoroughly compared the 2 persistance classes about a dozen times - to no avail. 

Any ideas/suggestions, anyone?

Any help greatly appreciated.

Cheers,

Carsten

[Noel Grandin]

Posting the whole stack trace is normally a good start

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Carsten Langsdorf]

Of course. Here it is, and thanks for having a look:

Exception in thread "JavaFX Application Thread" java.lang.RuntimeException: java.lang.reflect.InvocationTargetException

at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1774)

at javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(FXMLLoader.java:1657)

at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)

at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)

at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)

at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)

at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)

at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)

at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)

at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)

at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)

at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)

at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)

at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)

at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)

at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)

at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)

at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)

at javafx.event.Event.fireEvent(Event.java:198)

at javafx.scene.Node.fireEvent(Node.java:8413)

at javafx.scene.control.Button.fire(Button.java:185)

at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:182)

at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:96)

at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:89)

at com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)

at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)

at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)

at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)

at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)

at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)

at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)

at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)

at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)

at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)

at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)

at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)

at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)

at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)

at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)

at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)

at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)

at javafx.event.Event.fireEvent(Event.java:198)

at javafx.scene.Scene$MouseHandler.process(Scene.java:3757)

at javafx.scene.Scene$MouseHandler.access$1500(Scene.java:3485)

at javafx.scene.Scene.impl_processMouseEvent(Scene.java:1762)

at javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2494)

at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:381)

at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:295)

at java.security.AccessController.doPrivileged(Native Method)

at com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$354(GlassViewEventHandler.java:417)

at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:389)

at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:416)

at com.sun.glass.ui.View.handleMouseEvent(View.java:555)

at com.sun.glass.ui.View.notifyMouse(View.java:937)

at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)

at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.java:191)

at java.lang.Thread.run(Thread.java:745)

Caused by: java.lang.reflect.InvocationTargetException

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at sun.reflect.misc.Trampoline.invoke(MethodUtil.java:71)

at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at sun.reflect.misc.MethodUtil.invoke(MethodUtil.java:275)

at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1769)

... 56 more

Caused by: javax.persistence.PersistenceException: Cannot set Object parameter: value = 0 for column "ODDLOOT.BASE.ACCOUNT.ACCOUNTTYPEID" : Unerlaubter Wert "0" für Parameter "parameterIndex"

Invalid value "0" for parameter "parameterIndex" [90008-193]

at org.datanucleus.api.jpa.NucleusJPAHelper.getJPAExceptionForNucleusException(NucleusJPAHelper.java:317)

at org.datanucleus.api.jpa.JPAEntityManager.flush(JPAEntityManager.java:940)

at org.fractalnerdsongs.oddloot.db.FullAccessData.add(FullAccessData.java:21)

at org.fractalnerdsongs.oddloot.ui.ServersAndAccountsController.onAccountAdd(ServersAndAccountsController.java:208)

... 66 more

Caused by: org.h2.jdbc.JdbcSQLException: Unerlaubter Wert "0" für Parameter "parameterIndex"

Invalid value "0" for parameter "parameterIndex" [90008-193]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)

at org.h2.message.DbException.get(DbException.java:179)

at org.h2.message.DbException.getInvalidValueException(DbException.java:228)

at org.h2.jdbc.JdbcPreparedStatement.setParameter(JdbcPreparedStatement.java:1418)

at org.h2.jdbc.JdbcPreparedStatement.setLong(JdbcPreparedStatement.java:594)

at org.datanucleus.store.rdbms.datasource.dbcp2.DelegatingPreparedStatement.setLong(DelegatingPreparedStatement.java:127)

at org.datanucleus.store.rdbms.datasource.dbcp2.DelegatingPreparedStatement.setLong(DelegatingPreparedStatement.java:127)

at org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.setLong(ParamLoggingPreparedStatement.java:777)

at org.datanucleus.store.rdbms.mapping.datastore.IntegerRDBMSMapping.setObject(IntegerRDBMSMapping.java:232)

at org.datanucleus.store.rdbms.mapping.java.SingleFieldMapping.setObject(SingleFieldMapping.java:190)

at org.datanucleus.store.rdbms.fieldmanager.ParameterSetter.storeObjectField(ParameterSetter.java:195)

at org.datanucleus.state.AbstractStateManager.providedObjectField(AbstractStateManager.java:1460)

at org.datanucleus.state.StateManagerImpl.providedObjectField(StateManagerImpl.java:122)

at org.fractalnerdsongs.oddloot.db.AccountBean.dnProvideField(AccountBean.java)

at org.fractalnerdsongs.oddloot.db.AccountBean.dnProvideFields(AccountBean.java)

at org.datanucleus.state.StateManagerImpl.provideFields(StateManagerImpl.java:1199)

at org.datanucleus.store.rdbms.request.InsertRequest.execute(InsertRequest.java:294)

at org.datanucleus.store.rdbms.RDBMSPersistenceHandler.insertObjectInTable(RDBMSPersistenceHandler.java:162)

at org.datanucleus.store.rdbms.RDBMSPersistenceHandler.insertObject(RDBMSPersistenceHandler.java:138)

at org.datanucleus.state.StateManagerImpl.internalMakePersistent(StateManagerImpl.java:3369)

at org.datanucleus.state.StateManagerImpl.flush(StateManagerImpl.java:4522)

at org.datanucleus.flush.FlushOrdered.execute(FlushOrdered.java:106)

at org.datanucleus.ExecutionContextImpl.flushInternal(ExecutionContextImpl.java:3872)

at org.datanucleus.ExecutionContextImpl.flush(ExecutionContextImpl.java:3818)

at org.datanucleus.api.jpa.JPAEntityManager.flush(JPAEntityManager.java:936)

... 68 more

[Noel Grandin]

parameter indices for PreparedStatement#setLong start at 1, not 0. 

This looks like a bug in your ORM​

[Carsten Langsdorf]

Thanks, Noel.

Just to make sure I get this right, because I'm still learning about all this:

You think it's a bug in DataNucleus, right?

In which case I'll ask the DataNucleus people, obviously.

Thanks again,

Carsten

[Noel Grandin]

Either in DataNucleus, or possibly in your code, if you are using some extension or bypass API possibly.

There is also an outside chance that H2 is doing something weird like returning dodgy metadata that is confusing DataNucleus.

I'm guessing that they are going to ask for a standalone test-case.

You could try testing with Derby, just to be sure.

​

[Carsten Langsdorf]

Just for info, I managed to identify the source of that issue. It is indeed a bug in DataNucleus' SQL generation, and while tracking this one, I even found another one in the same department, even more critical - basically a showstopper.

Thus, I decided to swap out DataNucleus for EclipseLink. After a bit of reworking the code to adapt to the new JPA provider's behaviour, everything works like a charm now.

Thanks again for your help, Noel.

Cheers,

Carsten