From d1dcbd5dfbc607ac4e86ffb09dfa7df05ecee47d Mon Sep 17 00:00:00 2001 From: James Ahlborn Date: Wed, 17 Jan 2018 01:26:57 +0000 Subject: [PATCH] implement sql type to DataType conversions specific to access 2016 format git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/trunk@1132 f203690c-595d-4dc9-a70b-905162fa7fd2 --- .../jackcess/ColumnBuilder.java | 15 ++++- .../jackcess/DataType.java | 63 +++++++++++++------ .../jackcess/impl/JetFormatTest.java | 26 ++++++++ 3 files changed, 82 insertions(+), 22 deletions(-) diff --git a/src/main/java/com/healthmarketscience/jackcess/ColumnBuilder.java b/src/main/java/com/healthmarketscience/jackcess/ColumnBuilder.java index 76a1783..35aeca3 100644 --- a/src/main/java/com/healthmarketscience/jackcess/ColumnBuilder.java +++ b/src/main/java/com/healthmarketscience/jackcess/ColumnBuilder.java @@ -94,7 +94,7 @@ public class ColumnBuilder { * Sets the type for the new column based on the given SQL type. */ public ColumnBuilder setSQLType(int type) throws SQLException { - return setSQLType(type, 0); + return setSQLType(type, 0, null); } /** @@ -104,7 +104,18 @@ public class ColumnBuilder { public ColumnBuilder setSQLType(int type, int lengthInUnits) throws SQLException { - return setType(DataType.fromSQLType(type, lengthInUnits)); + return setSQLType(type, lengthInUnits, null); + } + + /** + * Sets the type for the new column based on the given SQL type, target + * data length (in type specific units), and target FileFormat. + */ + public ColumnBuilder setSQLType(int type, int lengthInUnits, + Database.FileFormat fileFormat) + throws SQLException + { + return setType(DataType.fromSQLType(type, lengthInUnits, fileFormat)); } /** diff --git a/src/main/java/com/healthmarketscience/jackcess/DataType.java b/src/main/java/com/healthmarketscience/jackcess/DataType.java index 9baaba6..6850ab6 100644 --- a/src/main/java/com/healthmarketscience/jackcess/DataType.java +++ b/src/main/java/com/healthmarketscience/jackcess/DataType.java @@ -17,14 +17,15 @@ limitations under the License. package com.healthmarketscience.jackcess; import java.io.IOException; +import java.math.BigDecimal; +import java.math.BigInteger; import java.sql.SQLException; import java.sql.Types; +import java.util.Date; import java.util.HashMap; import java.util.Map; -import java.util.Date; -import java.math.BigDecimal; -import java.math.BigInteger; +import com.healthmarketscience.jackcess.impl.DatabaseImpl; import com.healthmarketscience.jackcess.impl.JetFormat; /** @@ -157,7 +158,7 @@ public enum DataType { * String and parsed as Double, or {@code null}. Equivalent to SQL * {@link Types#BIGINT}. */ - BIG_INT((byte) 0x13, null, 8), + BIG_INT((byte) 0x13, Types.BIGINT, 8), /** * Dummy type for a fixed length type which is not currently supported. * Handled like a fixed length {@link #BINARY}. @@ -171,26 +172,26 @@ public enum DataType { 1); /** Map of SQL types to Access data types */ - private static final Map SQL_TYPES = - new HashMap(); + private static final Map SQL_TYPES = + new HashMap(); /** Alternate map of SQL types to Access data types */ private static final Map ALT_SQL_TYPES = new HashMap(); static { for (DataType type : DataType.values()) { if (type._sqlType != null) { - SQL_TYPES.put(type._sqlType, type); + SQL_TYPES.put(type._sqlType, new DataType[]{type}); } } - SQL_TYPES.put(Types.BIT, BYTE); - SQL_TYPES.put(Types.BLOB, OLE); - SQL_TYPES.put(Types.CLOB, MEMO); - SQL_TYPES.put(Types.BIGINT, LONG); - SQL_TYPES.put(Types.CHAR, TEXT); - SQL_TYPES.put(Types.DATE, SHORT_DATE_TIME); - SQL_TYPES.put(Types.REAL, DOUBLE); - SQL_TYPES.put(Types.TIME, SHORT_DATE_TIME); - SQL_TYPES.put(Types.VARBINARY, BINARY); + SQL_TYPES.put(Types.BIT, new DataType[]{BYTE}); + SQL_TYPES.put(Types.BLOB, new DataType[]{OLE}); + SQL_TYPES.put(Types.CLOB, new DataType[]{MEMO}); + SQL_TYPES.put(Types.BIGINT, new DataType[]{LONG, BIG_INT}); + SQL_TYPES.put(Types.CHAR, new DataType[]{TEXT}); + SQL_TYPES.put(Types.DATE, new DataType[]{SHORT_DATE_TIME}); + SQL_TYPES.put(Types.REAL, new DataType[]{DOUBLE}); + SQL_TYPES.put(Types.TIME, new DataType[]{SHORT_DATE_TIME}); + SQL_TYPES.put(Types.VARBINARY, new DataType[]{BINARY}); // the "alternate" types allow for larger values ALT_SQL_TYPES.put(Types.VARCHAR, MEMO); @@ -458,16 +459,38 @@ public enum DataType { public static DataType fromSQLType(int sqlType) throws SQLException { - return fromSQLType(sqlType, 0); + return fromSQLType(sqlType, 0, null); } public static DataType fromSQLType(int sqlType, int lengthInUnits) throws SQLException { - DataType rtn = SQL_TYPES.get(sqlType); - if(rtn == null) { + return fromSQLType(sqlType, lengthInUnits, null); + } + + public static DataType fromSQLType(int sqlType, int lengthInUnits, + Database.FileFormat fileFormat) + throws SQLException + { + DataType[] rtnArr = SQL_TYPES.get(sqlType); + if(rtnArr == null) { throw new SQLException("Unsupported SQL type: " + sqlType); } + DataType rtn = rtnArr[0]; + if((rtnArr.length > 1) && (fileFormat != null)) { + // there are multiple possibilities, ordered from lowest version to + // highest version supported. go in opposite order to find the best + // type for this format + JetFormat format = DatabaseImpl.getFileFormatDetails(fileFormat) + .getFormat(); + for(int i = rtnArr.length - 1; i >= 0; --i) { + DataType tmp = rtnArr[i]; + if(format.isSupportedDataType(tmp)) { + rtn = tmp; + break; + } + } + } // make sure size is reasonable int size = lengthInUnits * rtn.getUnitSize(); @@ -495,7 +518,7 @@ public enum DataType { try { java.lang.reflect.Field sqlTypeField = Types.class.getField(typeName); Integer value = (Integer)sqlTypeField.get(null); - SQL_TYPES.put(value, type); + SQL_TYPES.put(value, new DataType[]{type}); if(altType != null) { ALT_SQL_TYPES.put(value, altType); } diff --git a/src/test/java/com/healthmarketscience/jackcess/impl/JetFormatTest.java b/src/test/java/com/healthmarketscience/jackcess/impl/JetFormatTest.java index b302985..1ed17b7 100644 --- a/src/test/java/com/healthmarketscience/jackcess/impl/JetFormatTest.java +++ b/src/test/java/com/healthmarketscience/jackcess/impl/JetFormatTest.java @@ -5,11 +5,13 @@ import java.io.IOException; import java.io.InputStream; import java.nio.channels.FileChannel; import java.nio.channels.NonWritableChannelException; +import java.sql.SQLException; import java.util.ArrayList; import java.util.EnumSet; import java.util.List; import java.util.Set; +import com.healthmarketscience.jackcess.DataType; import com.healthmarketscience.jackcess.Database; import static com.healthmarketscience.jackcess.Database.*; import com.healthmarketscience.jackcess.DatabaseBuilder; @@ -265,6 +267,30 @@ public class JetFormatTest extends TestCase { } } + public void testSqlTypes() throws Exception { + + JetFormat v2000 = JetFormat.VERSION_4; + for(DataType dt : DataType.values()) { + if(v2000.isSupportedDataType(dt)) { + Integer sqlType = null; + try { + sqlType = dt.getSQLType(); + } catch(SQLException ignored) {} + + if(sqlType != null) { + assertEquals(dt, DataType.fromSQLType(sqlType)); + } + } + } + + assertEquals(DataType.LONG, DataType.fromSQLType(java.sql.Types.BIGINT)); + assertEquals(DataType.BIG_INT, DataType.fromSQLType( + java.sql.Types.BIGINT, 0, Database.FileFormat.V2016)); + assertEquals(java.sql.Types.BIGINT, DataType.BIG_INT.getSQLType()); + assertEquals(DataType.MEMO, DataType.fromSQLType( + java.sql.Types.VARCHAR, 1000)); + } + public static void transferDbFrom(FileChannel channel, InputStream in) throws IOException { -- 2.39.5