Ucanaccess Jackcess Calculated Fields

[Robert Robertino]

Is it possible through either of the two libraries? Or am I doomed to getting 0'es on all calculated fields?

EDIT: Also, try creating a ms access database with a numeric calculated field which multiplies by 2 a currency field and get some pop corn as everything falls apart and even the connection fails.

[Marco Amadei]

Firstly, what does 0'es mean?
Can you post a code example about your issue?
It isn't so understandable...

[Robert Robertino]

It means that you get 0 for any sort of calculated field in a MS Access file. There is no need to post an example. Perform a simple select * from any table that has a calculated field. It will not work. Furthermore, should the calculated field type differ from the type of the elements inside the formula (number vs currency), the whole driver will fail on getting a connection to the database.

EDIT: In case you don't know what a calculated field is: http://office.microsoft.com/en-001/access-help/add-a-calculated-field-to-a-table-HA101820564.aspx

[Marco Amadei]

Okey currently, UCanAccess doesn't support this. Nevertheless I'm going to verify if it's possible to add this feature in the 2.0.7.

[Robert Robertino]

Yes, it depends on what you can talk with the jackcess developers. However, you should also try and reproduce the second problem, the calculated field issue. The error string is "invalid money value" for these two specific types. I suggest you skip these columns entirely or simply initialize them with their natural values (null, 0, "") until support is provided. Failure to connect to a whole database because of a single (msaccess valid!) field is a big issue.

[Marco Amadei]

You're right, but unfortunatly I get an unexpected "Invalid money value" from jackcess while reading the calculated field (and simply invoking table.getNextRow()), and this causes the connection crash, mmmmm.... Just a moment Robertigno, I need to do a little analysis, before thinking of some workaround.
I'll reply asap.

[Marco Amadei]

Hi,
It seems to be vice-versa...

I have every needed information to implement calculated fields
BUT
we firstly have to involve the jackcess team for the specific case you mentioned (calculated field type differs from the type of the elements inside the formula ).
Try this:


public static void main(String[] s) throws IOException{
Table table = DatabaseBuilder.open(new File("your db path")).getTable("your table with calculated field");
for(Row row : table) {
System.out.println(row);
}
}

and you'll get a com.healthmarketscience.jackcess.RuntimeIOException: Invalid money value. I can't read the table values so I can only skip the table creation to avoid the connection crash.
Yes, it's a major issue.

[Robert Robertino]

Hello. Sorry for not replying sooner. I'm gmt+2 and it was getting late. Yes, I've read James' post and it seems it is quite possible to compute calculated fields.

There is a very good thing here, and it is that a calculated field only depends on elements within the same row. This makes everything much easier! This, along with a limited number of built-in functions and constants and only base math support should make this task quite a bit more simple. Good thing Microsoft was lazy about implementing more complex mathematical functions or cross-row functions.

As far as the bug is concerned, from trying that snippet of code I can see that we cannot use the data inside the table. However, the getTable function works! This leads to the possibility of hacks. Ugly, terrible hacks on metadata.
table.getColumn("Field1").getProperties() -> _props -> "EXPRESSION"
So we can identify which columns are calculated fields and simply remove them from our data in the intermediary step. This as a temporary hack I may implement until the problem is properly addressed.

Have you spoken with James about the bug? Any chance of it getting solved in an "official release"?

[Marco Amadei]

Hi,
I've already implemented the new "calculated field feature" and I'm going to commit it on svn today, or tommorrow at later.
It already works fine in my development environment.
About the currency problem, I don't think it's solvable without a jackcess team involvment:
you can get the Table object, but you cannot move the cursor because jackcess populates a map with all column/row value entries, moving the cursor.
So when you iterate between rows, jackcess invokes
defaultCursor.getNextRow(); and this call gets the map to be completly populated with its values. That unfortunatly causes a validation exception on the currency values and you cannot read none of the other column values (in this specific case).
James hasn't still replied to me, hope to get good news from him.
Cheers Marco

[Robert Robertino]

Ok, I understand. Hopefully the bug will be solved.
And I'm impressed that you solved the calculated field feature on such short notice. Did you provide support for Operators, Constants, and built-in functions too? Out of curiosity, which library did you use to parse the equation in Java? (by any chance the SF project JEP?)
Robert

[Marco Amadei]

Question 1: yes, of course, see answer 2.
Question 2: that I'm doing is very close to the implementation (already done)
of the column default values because in both cases I've to interpreter a "formula"(e.g. date() or 'pippo'&date()) which you can see as a sql fragment. Because UCanAccess converts access sql in the sql supported by hsqldb, I use hsqldb as formula interpreter.
In order to implements calculated field I've only to add a trigger which shouldn't have effect on the accdb file but only on the hsqldb mirror tables.
For instance, if the formula is IIF(ID IS NULL,'CCC','HH')&'DD', I have to generate and create a hsqldb trigger like the following(in pseudo-code):

CREATE TRIGGER expr0 BEFORE INSERT (AND UPDATE) ON
Table1 REFERENCING NEW as newrow FOR EACH ROW
BEGIN ATOMIC declare ID INTEGER;
SET ID=newrow.ID;
SET newrow."FIELD 2" =IIF(ID IS NULL,'CCC','HH')||'DD';
END

where IIF(ID IS NULL,'CCC','HH')||'DD'; is the conversion of s IIF(ID IS NULL,'CCC','HH')&'ID' (as result of my internal sql converter). That works because I've implemented in hsqldb and, therefore, in java, the access functions like IIF.
Now I'm still fighting with hsqldb triggers generation (there's a naming problem I'm solving). But I'm very close to the solution.
Cheers Marco

[Marco Amadei]

I would have finished my part, but I must wait for the jackcess implementation, because there is a further issue inserting or updating rows in a table with a calculated field.
It seems to be related to the currency problem mentioned above (see https://sourceforge.net/p/jackcess/bugs/105/).

[Robert Robertino]

Ah, I understand. It is good to see that there already exists an implementation in hsqldb on calculated fields and ms access functions and you needed only to trigger the computation.
Too bad there are two bugs waiting to be solved now in jackcess in order to provide this feature. Hopefully in at most one month we can see these issues solved. Thanks for your work, rated your lib.

[Marco Amadei]

Okey, thank you! Should the Jackcess team not complete the solution in the next few days, I will release the 2.0.7 with limited features about calculated field (tables with a calculated field marked as read-only).
Then, at the next jackcess release, I'll post a new UCanAccess realese with the full feature.