No, sorry, jackcess does not do anything for calculated fields. My guess is that the expression is saved as a column property. You can grab the expression from there, interpret the expression and set the appropriate calculated value yourself.
As of the 2.0.4 release, there is a new ColumnValidator interface which could probably make this easier to implement. you could set a calculated field ColumnValidator instance on the appropriate columns and that would "automatically" insert the values for you whenever you add a row to the table.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi James,
Yes, I can read the expression as column property, but I'm facing with a validation issue which happens in a specific case.
If the Result Type is Currency but it differs from the type of some element inside the expression (e.g. Expression = [currency field name]*4) ,
it's impossible to read table rows because of a com.healthmarketscience.jackcess.RuntimeIOException: Invalid money value.
You can reproduce this specific issue with a simple loop:
for(Row row : table_with_calculated_field) {
System.out.println(row);
}
You'll get:
java.io.IOException: Invalid money value.
at com.healthmarketscience.jackcess.impl.ColumnImpl.readCurrencyValue(ColumnImpl.java:814)
at com.healthmarketscience.jackcess.impl.ColumnImpl.read(ColumnImpl.java:658)
at com.healthmarketscience.jackcess.impl.ColumnImpl.read(ColumnImpl.java:626)
at com.healthmarketscience.jackcess.impl.TableImpl.getRowColumn(TableImpl.java:767)
at com.healthmarketscience.jackcess.impl.TableImpl.getRow(TableImpl.java:673)
at com.healthmarketscience.jackcess.impl.TableImpl.getRow(TableImpl.java:652)
at com.healthmarketscience.jackcess.impl.CursorImpl.getCurrentRow(CursorImpl.java:640)
at com.healthmarketscience.jackcess.impl.CursorImpl$BaseIterator.next(CursorImpl.java:756)
Thanks in advance!!!
Last edit: Marco Amadei 2014-06-23
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Yes. Just a follow up on this bug. As far as I can tell, the problem is just with currencies. Any other combination that does NOT include currencies does not seem to throw this error. As such, there must be some problem with the currency handling code.
To be more precise in reproducing the bug: Create a calculated field in a ms access db that returns a number value. Then put the expression [currency_value]*4, where currency_value is a column of type currency. This use-case will certainly reproduce the bug.
Last edit: Robert Robertino 2014-06-24
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Access does save the value of a calculated field in each row of the table. For example, with the table
where [LastFirst] is the calculated field
[LastName]&", "&[FirstName]
the record is saved in the .accdb file as
(Use "View Image" in your browser to see the whole picture.)
re: Jackcess writing calculated fields
Even if Jackcess did not automatically calculate the value of the calculated field it would be nice if it could accept the value for a calculated field and store that value. As it stands now if we calculate the value ourselves and try to insert it into a Jackcess row
Compact and Repair does not fix it, but it can be "repaired" by doing
UPDATE Table1 SET FirstName=FirstName WHERE ID=2
If Jackcess could be tweaked to reliably store the (pre-calculated) value in the record then if UCanAccess is able to do the calculation, Jackcess would be able to save it.
Last edit: Gord Thompson 2014-06-24
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
so, seeing these two examples, my guess is that the field value must have "additional" information encoded in it. that would explain why reading certain types is causing errors and why writing the "simple" value does not show up correctly in access. please file a bug and i'll look into it. (it would be great if you could attach an example db to the bug as well).
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I'm actually using UcanAccess which uses this library, however the question remains the same. Does Jackcess support calculated fields?
EDIT: A tutorial on calculated fields. http://office.microsoft.com/en-001/access-help/add-a-calculated-field-to-a-table-HA101820564.aspx
Last edit: Robert Robertino 2014-06-23
No, sorry, jackcess does not do anything for calculated fields. My guess is that the expression is saved as a column property. You can grab the expression from there, interpret the expression and set the appropriate calculated value yourself.
As of the 2.0.4 release, there is a new ColumnValidator interface which could probably make this easier to implement. you could set a calculated field ColumnValidator instance on the appropriate columns and that would "automatically" insert the values for you whenever you add a row to the table.
Hi James,
Yes, I can read the expression as column property, but I'm facing with a validation issue which happens in a specific case.
If the Result Type is Currency but it differs from the type of some element inside the expression (e.g. Expression = [currency field name]*4) ,
it's impossible to read table rows because of a com.healthmarketscience.jackcess.RuntimeIOException: Invalid money value.
You can reproduce this specific issue with a simple loop:
for(Row row : table_with_calculated_field) {
System.out.println(row);
}
You'll get:
java.io.IOException: Invalid money value.
at com.healthmarketscience.jackcess.impl.ColumnImpl.readCurrencyValue(ColumnImpl.java:814)
at com.healthmarketscience.jackcess.impl.ColumnImpl.read(ColumnImpl.java:658)
at com.healthmarketscience.jackcess.impl.ColumnImpl.read(ColumnImpl.java:626)
at com.healthmarketscience.jackcess.impl.TableImpl.getRowColumn(TableImpl.java:767)
at com.healthmarketscience.jackcess.impl.TableImpl.getRow(TableImpl.java:673)
at com.healthmarketscience.jackcess.impl.TableImpl.getRow(TableImpl.java:652)
at com.healthmarketscience.jackcess.impl.CursorImpl.getCurrentRow(CursorImpl.java:640)
at com.healthmarketscience.jackcess.impl.CursorImpl$BaseIterator.next(CursorImpl.java:756)
Thanks in advance!!!
Last edit: Marco Amadei 2014-06-23
Yes. Just a follow up on this bug. As far as I can tell, the problem is just with currencies. Any other combination that does NOT include currencies does not seem to throw this error. As such, there must be some problem with the currency handling code.
To be more precise in reproducing the bug: Create a calculated field in a ms access db that returns a number value. Then put the expression [currency_value]*4, where currency_value is a column of type currency. This use-case will certainly reproduce the bug.
Last edit: Robert Robertino 2014-06-24
re: Jackcess reading calculated fields
Access does save the value of a calculated field in each row of the table. For example, with the table
where [LastFirst] is the calculated field
the record is saved in the .accdb file as
(Use "View Image" in your browser to see the whole picture.)
re: Jackcess writing calculated fields
Even if Jackcess did not automatically calculate the value of the calculated field it would be nice if it could accept the value for a calculated field and store that value. As it stands now if we calculate the value ourselves and try to insert it into a Jackcess row
the result is an #Error
Compact and Repair does not fix it, but it can be "repaired" by doing
If Jackcess could be tweaked to reliably store the (pre-calculated) value in the record then if UCanAccess is able to do the calculation, Jackcess would be able to save it.
Last edit: Gord Thompson 2014-06-24
so, seeing these two examples, my guess is that the field value must have "additional" information encoded in it. that would explain why reading certain types is causing errors and why writing the "simple" value does not show up correctly in access. please file a bug and i'll look into it. (it would be great if you could attach an example db to the bug as well).
New issues:
https://sourceforge.net/p/jackcess/bugs/105/
https://sourceforge.net/p/jackcess/bugs/106/