JDBC or DB Exception: No value specified for Parameter

Classic Servoy
1

Hello,

I’m back again on a new thread (original semi-related discussion on “J2DB Exception After Postgres Schema Change”) - but with a new twist to the problems. Appreciate any help from the folks at Servoy or elsewhere on this new one!

Latest rev’s of Postgres, JDBC drivers, and Servoy running on a Linux host with XP Pro (SP2) client. I am getting a Parameter error (see Servoy log extract below). The situation is:

  1. A very simple form (no tab panels or anything - just a few fields)
  2. All not-null-fields in the table are managed by the database itself (it writes the default values)
  3. Navigation etc is fine in this table
  4. I can add and delete in another table
  5. If I try to add a record in this simple form I get a Parameter error (see Parameter 57 in the log below)
  6. If I remove a field from the backend db table, the parameter number decreases; if I add a field to the backend db table schema, the parameter number grows larger. The delta between the number of fields and the Parameter number error is equal to 10.
  7. Servoy repository/data providers are refreshing fine and reading new fields and schema properly.

I am wondering if there is something weird with the drivers ??? Suggestions, tips? I’m starting to get stuck on this one - appreciate any assistance with thanks,

Michael

2007-04-19 15:39:42,171 ERROR [AWT-EventQueue-0] com.servoy.j2db.util.Debug - Throwable
org.postgresql.util.PSQLException: No value specified for parameter 57.
at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:146)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:182)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:305)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.servoy.j2db.persistence.datasource.p.invoke(Unknown Source)
at $Proxy1.executeUpdate(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:207)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:207)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:207)
at com.servoy.j2db.dataprocessing.SQLEngine.performUpdates(Unknown Source)
at com.servoy.j2db.dataprocessing.aa.if(Unknown Source)
at com.servoy.j2db.FormController.selectNextRecord(Unknown Source)
at com.servoy.j2db.cmd.aa.doIt(Unknown Source)
at com.servoy.j2db.cmd.c$2.run(Unknown Source)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
2007-04-19 15:39:42,171 ERROR [AWT-EventQueue-0] com.servoy.j2db.util.Debug - Throwable
com.servoy.j2db.ApplicationException: Cannot save form data
at com.servoy.j2db.dataprocessing.aa.if(Unknown Source)
at com.servoy.j2db.FormController.selectNextRecord(Unknown Source)
at com.servoy.j2db.cmd.aa.doIt(Unknown Source)
at com.servoy.j2db.cmd.c$2.run(Unknown Source)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: com.servoy.j2db.dataprocessing.DataException: No value specified for parameter 57.
at com.servoy.j2db.c.a.a(Unknown Source)
at com.servoy.j2db.persistence.Server.translateSQLException(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.performUpdates(Unknown Source)
… 11 more

2

Hi Michael,

First of are you sure you only have 1 pgsql jdbc driver in the drivers directory. Having more than 1 of the same driver can give unexpected results.
Also you say you use not-null columns that auto-filled by the back-end database. Dit you also tell Servoy that these columns have db managed auto-enter values ? If not then Servoy will pass a NULL to those columns anyway and the default values will not be used.

Hope this helps.

3

Hi Robert,

There are multiple JDBC drivers in the c:\program files\servoy\drivers directory. These drivers are comprised of ONE postgresql driver (the one that shipped with the Servoy Feb 2007 builds - I have experimented with other recent drivers from Postgres) and, as well, other drivers that shipped with the product (such as Oracle, Firebird, etc). Not-null fields are set to be db managed by Servoy (via the DataProviders screens) - I have this particular null management feature working well in other No-null tables/forms against Postgres nulls.

Based on the Servoy.log does it “feel like” a JDBC issue or something else? For example, can one ever get into a situation where a Repository table gets corrupted somehow and so the solution is working fine for most tables but acts up with one table? In other words the JDBC driver is working properly but the java class interaction with the Repository is assuming certain things that are not quite aligned properly in the Repository db?

Thanks, Michael

4

Message from thread author:

This issue is resolved.

The following condition will raise a Data Exception error:

On a Postgres database, boolean fields that are set to NOT NULL = FALSE (ie - NULL’s are ok) with a DEFAULT VALUE set by the database (such as TRUE or FALSE) will fail.

The Servoy repository correctly reads these types of fields as Integer and considers them as a Database Default type (see the Data Providers screen). However, this default Servoy setting will fail and raise DataExceptions in Postgres. If one changes the radio button within the dataproviders to “Value (Database Managed)”, Servoy will work fine. In my opinion, at least with respect to Postgres, the native Servoy repository synchronization class should either:

(1) set the repository to Value (Database Managed) and not set it to Database Default, OR:

(2) the java class passing values to the backend db should do nothing (ie - not pass a parameter) when the backend db is managing the insert for the field.

Michael

5

please make a case for this describing when we shouldnt send something when we do it anyway