Reading .mdb files with UCanAccess returns column

问题:

I'm in the middle from migrating from the JDBC-ODBC bridge driver to the UCanAccess driver.

In doing this, I'm facing the following problem: The UCanAccess driver returns all columnames in UPPERCASE, but I need them to be CamelCase.

Any ideas?

Thx!

回答1:

With UCanAccess version 3.x, released in August 2015, ResultSetMetaData now returns the column names in mixed case if that is how they are defined in the database. (That is, they are no longer forced to UPPERCASE.)

---

(Original Answer)

Since UCanAccess depends on Jackcess, one possible workaround might be to retrieve the column names from Jackcess itself. The following code, using UCanAccess 2.0.4 and Jackcess 2.0.4, ...

package ucanaccesstest;

import java.io.File;
import java.io.IOException;
import java.sql.*;
import com.healthmarketscience.jackcess.*;

public class UCanAccessTestMain {

    public static void main(String[] args) {
        String dbFileSpec = "C:/Users/Public/mdbTest.mdb";
        String tableName = "ucaTest";

        // UCanAccess
        try (Connection conn = DriverManager.getConnection("jdbc:ucanaccess://" + dbFileSpec)) {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM [" + tableName + "] WHERE False");
            ResultSetMetaData rsmd = rs.getMetaData();
            System.out.println("Column names as reported by ResultSetMetaData:");
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                System.out.println(rsmd.getColumnName(i));
            }
            rs.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        System.out.println();

        // Jackcess
        try (Database db = DatabaseBuilder.open(new File(dbFileSpec))) {
            Table tbl = db.getTable(tableName);
            System.out.println("Column names as reported by Jackcess:");
            for (Column col : tbl.getColumns()) {
                System.out.println(col.getName());
            }
        } catch (IOException e) {
            e.printStackTrace(System.out);
        }
    }

}

... produces the following console output:

Column names as reported by ResultSetMetaData:
ID
FIELD1
FIELD2

Column names as reported by Jackcess:
Id
Field1
Field2