net.ucanaccess.jdbc.UcanaccessSQLException: data exception: string data, right truncation

[David Martin]

I am converting an existing application from JDBC-ODBC Bridge to UcanAccess. I am working my way through the errors -- most of which make sense. Apparently UcanAccess is much more finicky that the original ODBC. This one however, does not make sense. The program is copying Oracle data to MS Access for use in Ad Hoc reporting. The error implies a size difference between the source table column and destination table column. None of the columns are different sized. The column that I think is the most likely culprit is 255 characters long. In the case of the failed insert, the Oracle column text uses all 255 characters. As I said, the Access column is the same size, so I don't see why there is a problem. Do you have any advice? I'd hate to shave characters off the data to grind it to fit when it shouldn't be necessary.

Thank you for your help.

Dave Martin

Error:
net.ucanaccess.jdbc.UcanaccessSQLException: data exception: string data, right truncation
INFO | jvm 1 | 2015/03/04 13:36:05 | at net.ucanaccess.jdbc.UcanaccessPreparedStatement.executeUpdate(UcanaccessPreparedStatement.java:261)

Here is the insert:

insert into ECNTable
(productID,
productType,
firstHull,
parentProductNumber,
parentProductRev,
ECNNo,dscrSummary,
ECNIncorp,
ECNReasonCode,
ECNIncDate,
ECNProd,
workAccompBy,
productCategory,
toBeIncOnRev,
soc,
dateChanged,
[printSize ])
values(
14974,
12,
'471',
'346-0013',
'A',
'E-004',
'1. DELETED DISCONNECT SWITCH (P/N 67-5038, INDEX NUMBER 2009954) FOR WELDER IN ENGINEERS WORKSHOP (4-69). ADDED RECEPTACLE WITH SWITCH INTER-LOCKING (P/N 69-3442, INDEX NUMBER 22919) AND PLUG (P/N 69-3051, INDEX NUMBER 222920). CHANGE CABLE TYPE FOR CIRC',
'I',
null,
#2005-04-15#,
null,
null,
null,
'B',
null,
#2005-04-15#,
'A')

[David Martin]

Sorry for not originally posting the error and code.

[Marco Amadei]

Something really odd, because UcanAccess create the column with the right size.
In fact if you use the following string of exactly 255 characters, the insert will work(you can try):

insert into ECNTABLE (ECNINCORP) values('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');

Also your string seems to have 254 characters only. A bit of debug is needed.

[Marco Amadei]

Found, it's a conversion bug to be fixed in the 2.0.9.4.
In the meantime, the way I suggest is to properly use the PreparedStatement. Your code will improve:

ps =conn.prepareStatement("INSERT INTO ECNTABLE (productID,ECNINCORP) VALUES( ?,?)");
ps.setInt(1, id);
ps.setString(2, "1. DELETED DISCONNECT SWITCH (P/N 67-5038, INDEX NUMBER 2009954) FOR WELDER IN ENGINEERS WORKSHOP (4-69). ADDED RECEPTACLE WITH SWITCH INTER-LOCKING (P/N 69-3442, INDEX NUMBER 22919) AND PLUG (P/N 69-3051, INDEX NUMBER 222920). CHANGE CABLE TYPE FOR CIRC");
ps.execute();

This will work.
Thank you for reporting.

[David Martin]

Thank you very much. I'll give this a shot.