I am developing a number of reports for a legacy database. The database stores payroll information. Right now, i am trying to generate a report that links data from an allowances table to an employee table: that is, i am showing records for each allowance via the allowances_hisview ‘table’ (it is actually a database view) and showing the corresponding employee’s name.
The allowances table has an empl_id field, which is what i am using to link to the employee table via the allowances_hisview_to_cemployee relation.
However, when attempting to use this relation to pull related employee data, no such data appears.
Below is the excerpt from the log file. I’m seeing the number format exception, but i’m stumped as to what may be causing it. The relation between the two tables is only on the empl_id field from both tables, and this field is text in both tables.
Is there any way of knowing/figuring out where this NumberFormatException is being generated?
PS. All the data from the allowances_hisview is coming back fine.
java.lang.NumberFormatException: For input string: “allowances_hisview_to_cemployee”
at java.lang.NumberFormatException.forInputString(Unknown Source)
at java.lang.Integer.parseInt(Unknown Source)
at java.lang.Integer.(Unknown Source)
at com.servoy.j2db.dataprocessing.ad$a.getRelatedFoundSet(Unknown Source)
at com.servoy.j2db.dataprocessing.aw.do(Unknown Source)
at com.servoy.j2db.dataprocessing.aw.getValue(Unknown Source)
at com.servoy.j2db.dataprocessing.ad$a.getValue(Unknown Source)
at com.servoy.j2db.dataprocessing.e$a.getStringValue(Unknown Source)
at com.servoy.j2db.util.Text.processTags(Unknown Source)
at com.servoy.j2db.dataui.DataLabel.setValueObject(Unknown Source)
at com.servoy.j2db.dataprocessing.az.a(Unknown Source)
at com.servoy.j2db.dataprocessing.az.setRecord(Unknown Source)
at com.servoy.j2db.dataprocessing.d.a(Unknown Source)
at com.servoy.j2db.b.j$1.run(Unknown Source)
at com.servoy.j2db.b.j.a(Unknown Source)
at com.servoy.j2db.b.f.a(Unknown Source)
at com.servoy.j2db.b.f.a(Unknown Source)
at com.servoy.j2db.b.g.paintComponent(Unknown Source)
at javax.swing.JComponent.paint(Unknown Source)
at javax.swing.JComponent.paintChildren(Unknown Source)
at javax.swing.JComponent.paint(Unknown Source)
at javax.swing.JComponent.paintChildren(Unknown Source)
at javax.swing.JComponent.paint(Unknown Source)
at javax.swing.JViewport.paint(Unknown Source)
at javax.swing.JComponent.paintChildren(Unknown Source)
Well, that was my first thought, but i verified that both the DBMS as well as Servoy recognised both the PK and FK as TEXT (well, varchar2 for the DBMS).
I tried with another report that used a different relation and got something similar (and again, the primary key and foreign key are both text
2007-06-15 13:08:58,578 ERROR [AWT-EventQueue-0] com.servoy.j2db.util.Debug - Throwable
java.lang.NumberFormatException: For input string: “empdeducttrans_to_deducttypes”
at java.lang.NumberFormatException.forInputString(Unknown Source)
at java.lang.Integer.parseInt(Unknown Source)
at java.lang.Integer.(Unknown Source)
at com.servoy.j2db.dataprocessing.ad$a.getRelatedFoundSet(Unknown Source)
at com.servoy.j2db.dataprocessing.aw.do(Unknown Source)
at com.servoy.j2db.dataprocessing.aw.getValue(Unknown Source)
at com.servoy.j2db.dataprocessing.ad$a.getValue(Unknown Source)
at com.servoy.j2db.dataprocessing.e$a.getStringValue(Unknown Source)
at com.servoy.j2db.util.Text.processTags(Unknown Source)
at com.servoy.j2db.dataui.DataLabel.setValueObject(Unknown Source)
at com.servoy.j2db.dataprocessing.az.a(Unknown Source)
at com.servoy.j2db.dataprocessing.az.setRecord(Unknown Source)
at com.servoy.j2db.dataprocessing.d.a(Unknown Source)
at com.servoy.j2db.b.j$1.run(Unknown Source)
While I am fairly new to Servoy, this error is likely due to a data type mismatch.
Check:
Numbers “masquerading” as text (or text masquerading as number) for both PK and FK fields. We have run into this before (Postgres backend)
Maybe try setting up a test expression (via Utils or Marcel’s Toolbox) or test Form to test for string/text vs number/integer and confirm these results against data provider settings and the backend db data type.
In my experience … limited If the PK and FK are properly aligned at the data type level Servoy seems to be rock-solid for joins.
Just from the top of my head: have you checked if servoy has assigned the row_ident property to the right column of your view? Open Dataproviders and double check.
Can you also tell us what version of servoy are you using and what DB?
Each DB has a different way to manage views.
Was there some significant change in how 3.5 handles relations? I’ve now seen this number format exception message in over 5 relations now, which is really strange…
2007-06-18 13:30:02,796 ERROR [AWT-EventQueue-0] com.servoy.j2db.util.Debug - Throwable
java.lang.NumberFormatException: For input string: “emptshts_hisview_to_department”
at java.lang.NumberFormatException.forInputString(Unknown Source)
at java.lang.Integer.parseInt(Unknown Source)
at java.lang.Integer.(Unknown Source)
at com.servoy.j2db.dataprocessing.ad$a.getRelatedFoundSet(Unknown Source)
at com.servoy.j2db.dataprocessing.aw.do(Unknown Source)
at com.servoy.j2db.dataprocessing.aw.getValue(Unknown Source)
at com.servoy.j2db.dataprocessing.ad$a.getValue(Unknown Source)
at com.servoy.j2db.dataprocessing.e$a.getStringValue(Unknown Source)
at com.servoy.j2db.util.Text.processTags(Unknown Source)
at com.servoy.j2db.dataui.DataLabel.setValueObject(Unknown Source)
at com.servoy.j2db.dataprocessing.az.a(Unknown Source)
at com.servoy.j2db.dataprocessing.az.setRecord(Unknown Source)
at com.servoy.j2db.dataprocessing.d.a(Unknown Source)
at com.servoy.j2db.b.j$1.run(Unknown Source)
at com.servoy.j2db.b.j.a(Unknown Source)
at com.servoy.j2db.b.f.a(Unknown Source)
at com.servoy.j2db.b.f.a(Unknown Source)
at com.servoy.j2db.b.g.paintComponent(Unknown Source)
at javax.swing.JComponent.paint(Unknown Source)