exclamation mark saved as point

[T F]

Hello,

I'm new here, because I have detected a strange behaviour when saving text via Java and UCanAccess to an access mdb, when the text contains an exclamation mark.

I think this is a bug in UCanAccess. I found it in Version 3.0.1. After downloading and testing actual Version 3.0.3 I found out that this version still contains the bug. A colleague, who ist still working with version 2.0.4.1, reported me, that the bug doesn't exist in that version.

I have a test.mdb that contains one simple table "Test" with one field "Test". The field is declared as TEXT datatype, but I also tested with LONG TEXT datatype. Whatever, result is the same.
I inserted this via a little Java-Application and UCanAccess:

insert into Test (Test) values ('Test!')

That was saved in the mdb:
Test.
The exclamation mark was replaced by a point.

Next I tried this:

insert into Test (Test) values ('Test!!')

The result was:
Test.!
The first exclamation mark was replaced by a point, the second one was not.

I even tried to insert this:

insert into Test (Test) values ('Test\u0021')

The result was the same as with the test above:
Test.

I don't know, if it is a problem with UCanAccess or with Jackcess. I only use UcanAccess and haven't tested the behaviour with pure Jackcess.

Please analyse the behaviour and if it's a bug than please fix it, if you can. If it is not a bug, please tell me, how I can solve the problem with inserting an exclamation mark into an access textfield. I can't use an older version of UCanAccess like my collegue, because I need some features that are only available in newer versions of UCanAccess.

Thank you for your help!

Kind regards

[Gord Thompson]

I have been able to recreate your results with UCanAccess 3.0.3.

query                                            value inserted
-----------------------------------------------  --------------
INSERT INTO [Test] ([Test]) VALUES ('Test!')     Test.
INSERT INTO [Test] ([Test]) VALUES ('Test!!')    Test.!
INSERT INTO [Test] ([Test]) VALUES ('Test!!!')   Test.!.
INSERT INTO [Test] ([Test]) VALUES ('Test!!!!')  Test.!.!

[Gord Thompson]

I expect that this issue will be fixed in a future release of UCanAccess. In the meantime you can use one of the following workarounds:

String sql = "INSERT INTO [Test] ([Test]) VALUES ('Test' & Chr(33))";
try (Statement st = conn.createStatement()) {
    st.executeUpdate(sql);
}

 
or

String sql = "INSERT INTO [Test] ([Test]) VALUES (?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, "Test!");
    ps.executeUpdate();
}

[T F]

Thank you for your fast reply and the workaround!

[Marco Amadei]

It's a critical bug, will be fixed ASAP(probably tomorrow with a dedicated release).
Thank you for reporting.

[Marco Amadei]

Fixed in the 3.0.3.1.