I have a previously created table (created in Access, not using UCanAccess DDL) with a long field (long integer) declared. When, in Java, I attempt something like this...
"insert into atable (thelongfield) values(" + System.currentTimeMillis() + ")"
...I get the following stack trace (am using all default settings to get the connection, table is defined with autonumber primary key - this works when using integer sized numbers)...
Caused by: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::5.0.1 data exception: numeric value out of range
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:230)
at com.webfuture.database.util.StatementUtil.executeUpdate(StatementUtil.java:33)
... 7 more
Caused by: java.sql.SQLDataException: data exception: numeric value out of range
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source)
at net.ucanaccess.jdbc.ExecuteUpdate.executeWrapped(ExecuteUpdate.java:65)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:264)
at net.ucanaccess.jdbc.ExecuteUpdate.execute(ExecuteUpdate.java:48)
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:228)
... 8 more
Caused by: org.hsqldb.HsqlException: data exception: numeric value out of range
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.types.NumberType.convertToInt(Unknown Source)
at org.hsqldb.types.NumberType.convertToType(Unknown Source)
at org.hsqldb.StatementDML.getInsertData(Unknown Source)
at org.hsqldb.StatementInsert.getResult(Unknown Source)
at org.hsqldb.StatementDMQL.execute(Unknown Source)
at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
at org.hsqldb.Session.executeDirectStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 14 more
Is this a known issue?
Thanks,
Matthew
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
No dear, access(jackcess) integer correspond to java long, access(jackess) int correspond to java short.
It's misleading I know, but it's hsqldb (that throws the exception) over constrained not access.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Am making some headway having updated the attached. However, somewhere an int is getting into the mix as when storing Integer.MAX_VALUE + 1, am getting a negative value in the Access field (2's compliment or something).
Hopefully the following will be of use for future visitors, it turns out I was using the Number data type, sub-specifying the 'Long Integer' form for the field size (when defining a field in MS Access). Turns out the correct data type to use is 'Large Number' instead of Number.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hopefully the following will be of use for future visitors, it turns out I
was using the Number data type, sub-specifying the 'Long Integer' form for
the field size (when defining a field in MS Access). Turns out the correct
data type to use is 'Large Number' instead of Number.
So far it does, but I've not submitted it to regular unit testing (edge cases, etc.). I only need it to support an existing workflow with limited functional use (basically I use Access as a UI to make manual DML changes to other underlying databases, the structures of which are not too complicated).
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hello,
I have a previously created table (created in Access, not using UCanAccess DDL) with a long field (long integer) declared. When, in Java, I attempt something like this...
"insert into atable (thelongfield) values(" + System.currentTimeMillis() + ")"
(also get the same problem when I try this...
"insert into atable (thelongfield) values(" + Long.valueOf(Integer.MAX_VALUE + 1L).longValue() + ")"
...)
...I get the following stack trace (am using all default settings to get the connection, table is defined with autonumber primary key - this works when using integer sized numbers)...
Caused by: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::5.0.1 data exception: numeric value out of range
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:230)
at com.webfuture.database.util.StatementUtil.executeUpdate(StatementUtil.java:33)
... 7 more
Caused by: java.sql.SQLDataException: data exception: numeric value out of range
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source)
at net.ucanaccess.jdbc.ExecuteUpdate.executeWrapped(ExecuteUpdate.java:65)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:264)
at net.ucanaccess.jdbc.ExecuteUpdate.execute(ExecuteUpdate.java:48)
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:228)
... 8 more
Caused by: org.hsqldb.HsqlException: data exception: numeric value out of range
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.types.NumberType.convertToInt(Unknown Source)
at org.hsqldb.types.NumberType.convertToType(Unknown Source)
at org.hsqldb.StatementDML.getInsertData(Unknown Source)
at org.hsqldb.StatementInsert.getResult(Unknown Source)
at org.hsqldb.StatementDMQL.execute(Unknown Source)
at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
at org.hsqldb.Session.executeDirectStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 14 more
Is this a known issue?
Thanks,
Matthew
P.S. Access version is 2016
UCanAccess version is 5.0.1
The issue appears to be here; net.ucanaccess.converters.TypesMap, specifically this...
ACCESS_TO_HSQL_TYPES_MAP.put(AccessType.LONG.name(), "INTEGER");
...on line 54 (version 5.0.1). Why is a long being mapped to an integer?
Line 90 may be the issue (in addition to or instead of the above)...
JACKCESS_TO_HSQLDB_TYPES_MAP.put(DataType.LONG, "INTEGER");
No dear, access(jackcess) integer correspond to java long, access(jackess) int correspond to java short.
It's misleading I know, but it's hsqldb (that throws the exception) over constrained not access.
Am making some headway having updated the attached. However, somewhere an int is getting into the mix as when storing Integer.MAX_VALUE + 1, am getting a negative value in the Access field (2's compliment or something).
Hopefully the following will be of use for future visitors, it turns out I was using the Number data type, sub-specifying the 'Long Integer' form for the field size (when defining a field in MS Access). Turns out the correct data type to use is 'Large Number' instead of Number.
so, do you confirm? Does it work this way?
Il Mar 23 Mar 2021, 22:33 Matthew lastfreedom@users.sourceforge.net ha
scritto:
So far it does, but I've not submitted it to regular unit testing (edge cases, etc.). I only need it to support an existing workflow with limited functional use (basically I use Access as a UI to make manual DML changes to other underlying databases, the structures of which are not too complicated).