NOT NULL Constraint

[Wayne Johnson]

I am changing over to Java8 (using Win7) and and have installed UCanAccess to work with Access data bases (.mdb). The existing databases I need to read have a table (that I don't need to read) that has some nulls in a DATETIME column. It may be that the NULLed entries were made before the constraint was added. I can't just change the database because we have hundreds of them from data collection events over the last decade.

With ODBC and Microsoft Access Driver I could connect without complaint, but with Ucanaccess the connection fails with :

SQLException: integrity constraint violation: NOT NULL check constraint; SYS_CT_10302 table: VERSION column: MODIFIED

So it throws the exception and does not connect. Is there a way I can disable or get around the integrity check and complete connection to the database?

[Marco Amadei]

Hi Wayne,
Currently there isn't, because I thought that this behavior added value by forcing the consistency of users database.
Nevertheless this isn't the first time that a situation like that is reported, and I'll deal with it in the next versions. I'm thinking of a solution for it, because I wouldn't like to skip constraints setting by default.
Cheers Marco

[Gord Thompson]

Hi Wayne. If you are not prohibited from tweaking the database files then you could use a workaround like this to run a little VBScript that removes the NOT NULL constraint:

// test data
String dbFileSpec = "C:\\Users\\Public\\ucaTest.mdb";
String tableName = "Version";
String fieldName = "Modified";

String vbsFilePath = System.getenv("TEMP") + "\\DropNotNullConstraint.vbs";
File vbsFile = new File(vbsFilePath);

PrintWriter pw = new PrintWriter(vbsFile);
pw.println("Set dbe = CreateObject(\"DAO.DBEngine.120\")");
pw.println("Set db = dbe.OpenDatabase(\"" + dbFileSpec + "\")");
pw.println("Set fld = db.TableDefs(\"" + tableName + "\").Fields(\"" + fieldName + "\")");
pw.println("fld.Required = False");
pw.println("Set fld = Nothing");
pw.println("Set db = Nothing");
pw.println("Set dbe = Nothing");
pw.close();

Process p = Runtime.getRuntime().exec("cscript /nologo \"" + vbsFilePath + "\"");
p.waitFor();
BufferedReader rdr = 
        new BufferedReader(new InputStreamReader(p.getErrorStream()));
String scriptError = rdr.readLine();

vbsFile.delete();

if (scriptError != null) {
    System.out.println("Error updating database structure:");
    System.out.println(scriptError);
} else {
    try (Connection conn = DriverManager
            .getConnection("jdbc:ucanaccess://" + dbFileSpec)) {
        //
        // the database is now open
        //
    }
}

[Gord Thompson]

Hi Marco.

One possibility might be to have a readonly=true option for the connection URL and then have UCanAccess ignore all such constraints when creating the HSQLDB tables. That could allow people to connect if they just want to read/extract data from the Access file, while users who want to update the file would have to fix their data (in Access) by doing something like

UPDATE [Version] SET [Modified]=#1901-01-01# 
WHERE [Modified] IS NULL

before they can connect via UCanAccess.

[Marco Amadei]

Yes, of course, I will add a readonly option (without constraint settings).
Another option, if the user want to access in write mode instead, is to catch the exception, log a warning and use a check trigger instead of each unsatisfied constraint.
Obviously that should be done for all such constraints (if we want to be flexible as well as MS Access).
Cheers Marco

[Wayne Johnson]

Thank you for your responses.
Gord, I have used your work-around, modified to copy the database and then apply the change to the copy. That does allow me to connect. It's and extra step, but I could build it into my app.
I don't think a readonly=true option would help me. My app needs to make updates to much of the database, only not the table that had the null problem.

[Gord Thompson]

Understood. So for your particular use-case it would be okay if UCanAccess applied the read-only restriction to the table with bad data. That is, instead of requiring that the whole database be read-only, just make the table(s) with constraint violations read-only and have the other tables operate normally.

In my opinion it is a mis-feature of Access to allow adding a NOT NULL constraint to a column with existing NULL values. Either the column should be NULLable, or it should be NOT NULLable and contain no NULL values anywhere. In other words just because Access allows a certain behaviour doesn't necessarily make it "the right thing to do". :)

[Marco Amadei]

Yes, to just make read-only the bad-data-table may be an easy implementation, and without undesired behaviours.
Cheers Marco

[Björn Wilmsmann]

For a current project I'd need such a read-only mode that avoids integrity constraint issues, too. Any idea as to when this will be available? If there's any way I can help please let me know.

[Marco Amadei]

I should be able to release the 2.0.9.4 with this implementation next week.

[yagna sathish]

Hi, i have a different flavour of not null constraint breach at the time of connection

String URL = "jdbc:ucanaccess://" + .... + dbName + ".mdb";
System.out.println("url::>"+URL);
conn = DriverManager.getConnection(URL);

This mdb is not created by ucanaccess driver but by jdbc:odbc:Driver when in Java V1.7. When the get connection for this database executes i have the below warning

*WARNING:Detected Not Null constraint breach, table tab....................making the table WOECUSTORDHEAD readonly

I think this is fix in the above discussion. But I get this for a field that is set as required = no in access.I retried by updating the row with appropriate value as well. Kindly advise.

Thanks you

[Marco Amadei]

It looks like the column is read as required. We would need a test database to reproduce the issue. It shouldn't be the real one, but pay attention it should reproduce an identical issue. May you generate and post it in attachment?

[yagna sathish]

Ok i will post it in 1hr. By the way i dont have this issue in v1.7 with jdbc odbc driver. application runs in both 1.7and1.8. I cant change the mdb file as the mdb is locally stored on user pc. Can be done via pgm but without alter stmnt support yet,is there an alternative approach? I dont get this issue the table is created in v1.8 using ucanaccess driver.

[yagna sathish]

I am attaching the table that is causing the issue. I even tried by manually updated the fields which are blank or null. But have the same "not null breach". Thanks to your fix atleast read is possible but in my case this is the table to Insert, update and delete.

I also noticed that with an other mdb file it worked. So i guess its the data but i was not able to find which data/field is causing the issue. Also the record no. it displayed in the warning if far greater than total records in that table. However the row displayed is present in the table.

Hope there is a quick workaround for this because i have very little time and this is a urgent fix that i have to publish in production at the earliest.

Please let me know if you need any other inforation on the table. I hope the attachment would give you some insight.

[Marco Amadei]

Unfortunately not, I can update the record in the ord table without any issue. I've just tried using the 3.0.3.1. Try yourself with the ucanaccess console. Which ucanaccess version are you using?

[yagna sathish]

I am using 3.0.3.1. Sorry, now the db as 2 tables. Try the ord_issue. When i run test validation rules on the ord_issue tables it voilates requried property at fails at field name -> 4,5

We have such cases usually for MS Access then to maintain integrity of the data, i suppose you would not allow write access?

[Marco Amadei]

The columns "3", "4", "5", "6" are required in access, but have a null value. Inserting not null values in those columns and re-opening the db, the table ord_issue becomes writable. So, where the inconsitency?

[yagna sathish]

Here lies the difficulty. I have lots of data and the mdbs independent for each user. Each can have different set of set of day for each user. I do not face this is not an issue issue however in 1.7 using jdbc odbc for the same table parameters.

So now, a feasible choice is that I create new mdb and run a patch to migrate from old mdb to new. I am thinking of easier and efficient way to distribute this patch, may be via vb script is either than loading via another jar?Any other suggestions? The mdb is required before launching the application. So i will use another jar to create the new mdbs as main.

[Marco Amadei]

Yes, you may want to fix the MDBs using jackcess directly,and at the application startup, embedding the code in the application. You have already what is needed as dependencies. Just 5 to 7 lines of code to add.

[yagna sathish]

Thank you. I was able to change the required field attribute to No as these are not mandatory. However i have noticed another issue. If the table was created using jdbc-odbc and i try to update a field now with ucanaccess in v1.8 then i have an exception that "wrong length for text column, max 6, min 6, got 1.

This field and few others are required field in mdb. During insert or update it is expecting to have minimum length = maximum length which is not mandatory always. It can have from 1 to 6 characters. is there a way to set the minimum length attribute via pgm using ucanaccess or jackcess?

Exception ->

java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.io.IOException: Text is wrong length for TEXT column, max 6, min
6, got 1 (Db=woeOrder.mdb;Table=WOE_CUST_ORD_LINE;Column=ASRT_CODE)
at com.healthmarketscience.jackcess.impl.ColumnImpl.encodeTextValue(Colu
mnImpl.java:1237)
at com.healthmarketscience.jackcess.impl.ColumnImpl.writeFixedLengthFiel
d(ColumnImpl.java:1110)
at com.healthmarketscience.jackcess.impl.ColumnImpl.writeFixedLengthFiel
d(ColumnImpl.java:1067)
at com.healthmarketscience.jackcess.impl.ColumnImpl.writeRealData(Column
Impl.java:1024)
at com.healthmarketscience.jackcess.impl.ColumnImpl.write(ColumnImpl.jav
a:1016)
at com.healthmarketscience.jackcess.impl.ColumnImpl.write(ColumnImpl.jav
a:998)
at com.healthmarketscience.jackcess.impl.TableImpl.createRow(TableImpl.j
ava:2084)
at com.healthmarketscience.jackcess.impl.TableImpl.createRow(TableImpl.j
ava:2036)
at com.healthmarketscience.jackcess.impl.TableImpl.addRows(TableImpl.jav
a:1569)
at com.healthmarketscience.jackcess.impl.TableImpl.addRow(TableImpl.java
:1462)
at net.ucanaccess.converters.UcanaccessTable.addRow(UcanaccessTable.java
:44)
at net.ucanaccess.commands.InsertCommand.insertRow(InsertCommand.java:10
1)
at net.ucanaccess.commands.InsertCommand.persist(InsertCommand.java:148)

    ... 60 more