ambiguous type reporting from ResultSet.getMetaData()

[RavenAtSafe]

First off, this is a pretty amazing project - thank you for the hard work!

We are observing that the following Access type pairs are reported in a way that makes it impossible to tell them apart:

• 'single' and 'double' have vendorTypeName 'DOUBLE'
• 'counter' (autonumber) and 'long' have vendorTypeName 'INTEGER'
• 'byte' and 'integer' have vendorTypeName 'SMALLINT'

Would it be possible to have these types differentiated either by their JDBC type, or by their vendor type name?

[Marco Amadei]

For the counter type you can use the ResultSetMetadata isAutoIncrement method.
Before answering about the residual cases, do you really need using ResultSetMetadata instead of DatabaseMetadata? Vendor type (i.e., access type ) is reported there, I added a specific column named ORIGINAL_TYPE in the resultset returned by getColumns Method.
I could expose it in ResultSetMetadata too, but I would like to better understand your concern.

[RavenAtSafe]

Hi Marco,

Thanks for the quick response.

My life is complicated by the fact that most of my code needs to be JDBC driver agnostic. Specifically, I need to support many different databases with many different forms of catalog/schema/"search path" support. It is challenging to work with the results from methods like getColumns() because it is hard (perhaps impossible) to work out which results are from the table you actually care about (if you are working with unqualified table names). In contrast, if you inspect the ResultSet metadata after executing "select * from table where 1=0", you can be 100% sure that you are looking at the correct metadata.

Now, it does look like I might be able to pull enough information off the ResultSet metadata (catalog, schema, etc) that I could make a completely unambiguous DatabaseMetadata.getColumns query, but that seems kind of roundabout?

Cheers,
Raven

[Marco Amadei]

The point is, I should create a method out of the interface in ResultSetMetadata(you would need a cast) and so nothing is more driver dependent than this. Of course it's a roundabout and anyway the ORIGINAL_TYPE column is a ucanaccess customisation: ORIGINAL_TYPE isn't obviously a standard column name...

[RavenAtSafe]

I'm not sure I understand your suggestion. I was just hoping that ResulSetMetadata.getColumnTypeName() and .getColumnType() would be more completely implemented.

For example:

Access Type	getColumnType	getColumnTypeName
single	java.sql.Types.FLOAT	SINGLE
counter/autonumber	java.sql.Types.INTEGER	COUNTER
byte	java.sql.Types.SMALLINT or TINYINT	BYTE