DataType.fromSQLType for BIGINT with V2016

[Gord Thompson]

While experimenting with V2016 support, I noticed that this code ...

File dbFile = File.createTempFile("Jackcess", ".accdb");
dbFile.deleteOnExit();
for (FileFormat fmt : new FileFormat[] { FileFormat.V2010, FileFormat.V2016 }) {
    dbFile.delete();
    DatabaseBuilder dbb = new DatabaseBuilder(dbFile);
    dbb.setFileFormat(fmt);
    try (Database db = dbb.create()) {
        DataType dt = DataType.fromSQLType(java.sql.Types.BIGINT);
        System.out.printf("For a \"%s\" file, java.sql.Types.BIGINT maps to %s%n", 
                fmt.toString(), dt.toString());
    }
}

 
... produces this output:

For a "V2010 [VERSION_14]" file, java.sql.Types.BIGINT maps to LONG
For a "V2016 [VERSION_16]" file, java.sql.Types.BIGINT maps to LONG

 
For a V2016 file, shouldn't java.sql.Types.BIGINT map to BIG_INT?

[James Ahlborn]

seriously?! i looked at that code and thought "i'll worry about that when someone complains". i can't believe it came up before i even released 2016 support! ;) the problem is that you need the db format in order to make the right decision, which would require a new version of that method with the appropriate overload (you'll notice that in your test code, you don't actually use the db). i wasn't convinced of the utility of all of that.

[Gord Thompson]

i can't believe it came up before i even released 2016 support! ;)

 
I was looking at it with an eye toward adding V2016 support to UCanAccess.

(you'll notice that in your test code, you don't actually use the db)

 
Quite true, but that's the way that UCanAccess currently determines what java.sql.Types.BIGINT "means", so that's what I used for my example. It's never been an issue before (except for possible data overflow if somebody tries to stuff a BIGINT greater than Integer.MAX_VALUE or less than Integer.MIN_VALUE into an Access LONG), but I guess now it's "a thing".

[Gord Thompson]

Might this suffice?

diff --git a/src/main/java/com/healthmarketscience/jackcess/DataType.java b/src/main/java/com/healthmarketscience/jackcess/DataType.java
index 9baaba6..7649cfe 100644
--- a/src/main/java/com/healthmarketscience/jackcess/DataType.java
+++ b/src/main/java/com/healthmarketscience/jackcess/DataType.java
@@ -25,6 +25,7 @@
 import java.math.BigDecimal;
 import java.math.BigInteger;

+import com.healthmarketscience.jackcess.Database.FileFormat;
 import com.healthmarketscience.jackcess.impl.JetFormat;

 /**
@@ -455,6 +456,16 @@
     throw new IOException("Unrecognized data type: " + b);
   }

+  public static DataType fromSQLType(int sqlType, FileFormat fileFormat)
+    throws SQLException
+  {
+    if ((sqlType == Types.BIGINT) && (fileFormat.equals(FileFormat.V2016))) {
+      return BIG_INT;
+    } else {
+      return fromSQLType(sqlType, 0);
+    }
+  }
+  
   public static DataType fromSQLType(int sqlType)
     throws SQLException
   {

 
for which I could change the call in my test code to

DataType dt = DataType.fromSQLType(java.sql.Types.BIGINT, db.getFileFormat());

 
producing

For a "V2010 [VERSION_14]" file, java.sql.Types.BIGINT maps to LONG
For a "V2016 [VERSION_16]" file, java.sql.Types.BIGINT maps to BIG_INT

[James Ahlborn]

I checked in some changes to trunk to add an optional FileFormat param to fromSQLType and to make the correct decision based on that info. let me know what you think.

[Gord Thompson]

Looks good. Certainly more elegant than my rather simplistic suggestion.

Perhaps we could add one more fromSQLType signature so callers can specify FileFormat without having to plug in a length value for fixed-length fields?

diff --git a/src/main/java/com/healthmarketscience/jackcess/DataType.java b/src/main/java/com/healthmarketscience/jackcess/DataType.java
index 6850ab6..2dd4f7c 100644
--- a/src/main/java/com/healthmarketscience/jackcess/DataType.java
+++ b/src/main/java/com/healthmarketscience/jackcess/DataType.java
@@ -468,6 +468,12 @@
     return fromSQLType(sqlType, lengthInUnits, null);
   }

+  public static DataType fromSQLType(int sqlType, Database.FileFormat fileFormat)
+      throws SQLException
+    {
+      return fromSQLType(sqlType, 0, fileFormat);
+    }
+
   public static DataType fromSQLType(int sqlType, int lengthInUnits,
                                      Database.FileFormat fileFormat)
     throws SQLException

[James Ahlborn]

man tough crowd! i thought about that. didn't want to bloat the code. especially since those methods are mirrored in ColumnBuilder.

[Gord Thompson]

Not a deal-breaker, just looking to avoid a conversation like this (if Jackcess could talk):

User: I want to know what java.sql.Types.BIGINT means for a particular FileFormat.

Jackcess: Okay, but you need to provide a lengthInUnits.

User: Which length? The length of a java.sql.Types.BIGINT or the length of what it maps to?

Jackcess: It doesn't matter because it will be ignored anyway.

[James Ahlborn]

I can see that as an exploratory coding exercise, but i would imagine that most real code will be looping through some collection of arbitrary types and converting them. those types will probably include length information as well, right?

[Gord Thompson]

Point taken.