creation of access table without NOT NULL constraint

[aditya]

Hi,

I have a problem in creating a access table column RQST_DEL_DATE of DATETIME type without NOT NULL for a column which resulting creating a NOT NULL field in the access table with column property as required : yes .
Below is the query for creating my table :

Statement sqlOut = connOut.createStatement ();
String sqlOutTxt =
"create table WOE_CUST_ORD_LINE (" +
" MMM_ORG_CODE CHAR (3) not null," +
" CO_ID CHAR (2) not null," +
" SLS_DEPT_ID CHAR(3) not null," +
" SLS_TERR_CODE CHAR(6) not null," +
" CUST_ORD_ANNO CHAR(4) not null," +
" PREFISSO CHAR(1) not null," +
" SLMN_REF_NBR INTEGER not null," +
" LINE_NBR INTEGER not null," +
" MMM_ID CHAR(11) not null," +
" MMM_ID_DESC VARCHAR(100) not null," +
" ORD_QTY DECIMAL(13,2) not null," +
" SELL_UNIT CHAR(4) not null," +
" CUST_UNIT_DESC CHAR(12) not null," +
" RQST_DEL_DATE DATETIME," +
" PRCE DECIMAL(13,4) not null," +
" PRCE_CODE CHAR(1) not null," +
" DSCT_1 DECIMAL(5,3) not null," +
" DSCT_2 DECIMAL(5,3) not null," +
" DSCT_3 DECIMAL(5,3) not null," +
" DSCT_4 DECIMAL(5,3) not null," +
" DSCT_5 DECIMAL(5,3) not null," +
" CARTONE_VAL DECIMAL(13,4) not null," +
" PRCE_FRMT_CODE CHAR (1) NOT NULL,"+
" CONSTRAINT [PK_WOE_CUST_ORD_LINE] PRIMARY KEY" +
" (" +
" [MMM_ORG_CODE] ," +
" [CO_ID] ," +
" [SLS_DEPT_ID] ," +
" [SLS_TERR_CODE] ," +
" [CUST_ORD_ANNO] ," +
" [PREFISSO] ," +
" [SLMN_REF_NBR] ," +
" [LINE_NBR]" +
")" +
")";

When i try to insert a null value into the RQST_DEL_DATE column it throws an error as below:

integrity constraint violation: NOT NULL check constraint; SYS_CT_10325 table: WOE_CUST_ORD_LINE column: RQST_DEL_DATE

And when i manually change the property of the RQST_DEL_DATE column from required : YES to NO i am able to insert the data and working fine.

Please help me in making the property of column as NO for without NOT NULL column

Note: i am using java 8 with Ucanaccess latest version driver for connecting to access db

[Marco Amadei]

You're right, it's partially due to the use of the DECIMAL keyword instead of the access specific NUMERIC. This would solve partially the issue, because even using NUMERIC, the numeric column will be created as nullable, so it's a general issue in parsing with numerics. It will be fixed ASAP in the 3.0.2.

[aditya]

Thanks for the responce, unfortunately we have used the decimal datatype is used in many places in my project and it will be a huge workaround to change it everywhere, we would like to know when the version 3.0.2 will be released and also we have issue with UCanAccess driver for Alter table command which is not supported yet by ucanaccess when can we expect the fix :) waiting for the release of 3.0.2. Cheers

[Marco Amadei]

I have already developed the patch, it was a trivial bug. The official release won't take more than one week(it will likely be this saturday), but in this very moment I've just this fix and I want to do it because it's a major bug.
Jackcess doesn't support alter table, and this is the main reason why it isn't supported yet.
Nevertheless I'll seek to emulate it (that is something very complicated) in the 3.0.3.

[aditya]

Hi Marco, Could you please post an update when can be 3.0.2 could be released with the fix. We are waiting to get the latest release, Thank you.

[Marco Amadei]

You're totally right, I'd been waiting for a requirement definition. However I'll release the the 3.0.2 that will contain this fix only, ASAP. I mean, let me try during this week.

[Marco Amadei]

Some unexpected effort at work, I'm going to release tomorrow. Cheers Marco