#31 Default values for columns are not respected

[Trejkaz]

Something which Access does but Jackcess doesn't: If the schema contains default values for columns, these values are not used when creating new columns. The behaviour for Access appears to be that the default value is inserted in as an actual value when the row is added - I can verify this, because if I make the changes with Access, Jackcess is able to see the default values it inserted.

Example code:

import com.healthmarketscience.jackcess.*;

import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardCopyOption;

public class JackcessTest {
    public static void main(String[] args) throws Exception {
        Path templateFile = Paths.get("/Data/default-values.mdb");
        Path databaseFile = Paths.get("test.mdb");
        Files.copy(templateFile, databaseFile, StandardCopyOption.REPLACE_EXISTING);

        //A way to use Path would be nice...
        try (Database database = DatabaseBuilder.open(databaseFile.toFile())) {
            Table table = database.getTable("Test_Table");

            // Add a row with no values set (except the ID, of course.)
            table.addRow();

            // Now query for that row and see what's in it.
            table.reset();
            Row row = table.getNextRow();
            System.out.println(row.getString("Text_Col"));
            System.out.println(row.getString("Memo_Col"));
            System.out.println(row.getInt("Number_Col"));
            System.out.println(row.getDate("DateTime_Col"));
            System.out.println(row.getBigDecimal("Currency_Col"));
            System.out.println(row.getBoolean("Boolean_Col"));
        } finally {
            Files.delete(databaseFile);
        }
    }
}

What I expected to get was:

Something
Something
42
1/1/2000
42.00
true

What I actually got was:

null
null
null
null
null
false

[James Ahlborn]

Ticket moved from /p/jackcess/bugs/121/

[James Ahlborn]

I moved this from bugs to feature requests because this is an expected situation. jackcess has only relatively recently gained the ability to read the full column properties. as such, jackcess does not currently respect any of the column properties.

[James Ahlborn]

Poked at this a bit more, and there are some additional difficulties that i see.

1. Default values are expressions. Implementing this requires understanding the access expression language. it's possible that some simple values could be supported, but definitely not the full breadth of the expression language.

2. In your example, you essentially passed "null" for the relevant fields. Null is a valid value for a field with a default value. in order to truly handle this, there would need to be a way to distinguish between "insert null" and "insert the default value".

Lastly, unsure if you are aware, but recent versions of jackcess support column validators (see http://jackcess.sourceforge.net/apidocs/com/healthmarketscience/jackcess/util/ColumnValidatorFactory.html and http://jackcess.sourceforge.net/apidocs/com/healthmarketscience/jackcess/util/ColumnValidator.html). This allows you to easily plugin your own custom, per-column logic. Should be fairly straigtforward to generate a ColumnValidatorFactory which checks for a default property on the column and, if found, generates a ColumnValidator which inserts it based on your personal criteria (and using your personal interpretation of the default value expressions).

[James Ahlborn]

As part of the new expression evaluation support, default values will be respected. note that in the initial release, expression evaluation will be disabled by default. Enabling it (using a system property or an explicit call on the Database) will cause default values to be inserted when defined for a field and the input value is null.

Fixed in trunk, will be in the 2.2.0 release.