I am trying to use UCanAccess to query a MS Access .accdb file. Everything works great, except when I query multi-value fields. For example those that have entries in the Row Source of a table field's Lookup tab in design view in MS Access. My code crashes when I try to output the result:
ResultSet rslt = stmt.executeQuery("SELECT [singleValue], [multiValue] FROM [TableName];");
int count = 0;
while (rslt.next())
System.out.println(count++ + "\t" + rslt.getString(1) + "\t" + rslt.getString(2));
The ResultSet is returned fine, and the singleValue prints fine, but the following error is thrown if I try to print the multiValue from the ResultSet:
Exception in thread "main" net.ucanaccess.jdbc.UcanaccessSQLException: incompatible data type in conversion: from SQL type OTHER to java.lang.String, value: instance of org.hsqldb.types.JavaObjectData
I have tried querying a query that is stored in the .accdb, but that does not work, because it just triggers the original query, and returns the same ResultSet.
Am I missing something simple or is this something UCanAccess can not handle?
This is the first question about it I have ever seen. You can see an example of the complex types usage with UCanAccess in the ucanaccess web site, tab "Getting Started" (at the end of the page). Here's a junit test case: https://sourceforge.net/p/ucanaccess/code/HEAD/tree/ucanaccess/trunk/src/test/java/net/ucanaccess/test/ComplexTest.java (see the testComplex method).
In particular you can't call
rslt.getString(2)
but have to userslt.getObject(2)
. You'll get a ucanaccess wrapper of your data. If you wanted to get string that described the data content you can userslt.getObject(2).toString()
. The wrapping classes are:In your example,
rslt.getObject(2)
should return an array ofnet.ucanaccess.complex.SingleValue
. Then you can call the method singleValue.getValue() on each array element to get the wrapped value.