sqlite-jdbc does not comply with JDBC Specification regarding Appendix B.1, B.2, & B.3

[rsmckinney]

TLDR: Column values obtained from getObject should match the types from getColumnType(int), but they often do not.

Referring to JDBC 4.x Specification. (Same mappings exist in JDBC 3 Specification)

According to the spec:

Appendix Table B.3

JDBC Types Mapped to Java Object Types

The getObject methods for both the CallableStatement and ResultSet interfaces use the mapping in TABLE B-3

JDBC Type	Java Object Type
CHAR	String
VARCHAR	String
LONGVARCHAR	String
NUMERIC	java.math.BigDecimal
DECIMAL	java.math.BigDecimal
BIT	Boolean
BOOLEAN	Boolean
TINYINT	Integer
SMALLINT	Integer
INTEGER	Integer
BIGINT	Long
REAL	Float
FLOAT	Double
DOUBLE	Double
BINARY	byte[]
VARBINARY	byte[]
LONGVARBINARY	byte[]
DATE	java.sql.Date
TIME	java.sql.Time
TIMESTAMP	java.sql.Timestamp
DISTINCT	Object*
CLOB	java.sql.Clob
BLOB	java.sql.Blob
ARRAY	java.sql.Array
STRUCT	Struct or SQLData
REF	java.sql.Ref
DATA_LINK	java.net.URL
JAVA_OBJECT	Object*
ROWID	java.sql.RowId
NCHAR	String
NVARCHAR	String
LONGNVARCHAR	String
NCLOB	java.sql.NClob
SQLXML	java.sql.SQLXML

The problem is calls to ResultSetMetadata#getColumnType(int) don't align with calls to ResultSet#getObject(int). Generally, Sqlite's implementation of getObject(int) does not adhere to Table B.3.

For instance, if I call getColumnType(int) and get back Types.DATE, the JDBC specification states that I can expect values of type java.sql.Date for that column. However, if I call getObject(int) for a row including that column, sqlite returns a value of type java.lang.String.

Similarly, calls to getColumn(int, Class<?>) are not covered. If getColumnType(int) returns Types.CLOB and getObject(int, Class<?>) is called with java.sql.Clob for the same column, a SQLFeatureNotSupportedException("not implemented by SQLite JDBC driver") results because getColumn(int, Class<?>) does not handle java.sql.Clob.

This behavior does not appear to comply with the specification (both 3 & 4), but more importantly this behavior complicates writing type-safe code generators that work with sqlite db connections.

[gotson]

For instance, if I call getColumnType(int) and get back Types.DATE, the JDBC specification states that I can expect values of type java.sql.Date for that column. However, if I call getObject(int) for a row including that column, sqlite returns a value of type java.lang.String.

from what i recall when i looked into this, it's because the first one is looking at the table definition, while the second one is looking at the actual value for that row. There is no guarantee that they will match, since SQLite doesn't enforce types.

So you can define a column as 'datetime', and store a float inside.

[rsmckinney]

@gotson That’s a separate issue re no type-safety.

In this case what I am saying is if a column is typed as Types.DATE, getObject(int) should always return a Java.sql.Date regardless of how the value was set. The method should attempt to coerce whatever is stored in that column to a Date. It should throw an exception if that can’t be done. If the user wants the value as some other data type, he can call getInt() etc. for specific coercions. This is how JDBC specifies getObject methods.

[gotson]

@gotson That’s a separate issue re no type-safety.

In this case what I am saying is if a column is typed as Types.DATE, getObject(int) should always return a Java.sql.Date regardless of how the value was set. The method should attempt to coerce whatever is stored in that column to a Date. It should throw an exception if that can’t be done. If the user wants the value as some other data type, he can call getInt() etc. for specific coercions. This is how JDBC specifies getObject methods.

Could you provide a unit test showing the behaviour you think is wrong ?

[rsmckinney]

I don't have time to write unit tests for sqlite. Here again from the OP is a complete description and example:

The problem is calls to ResultSetMetadata#getColumnType(int) don't align with calls to ResultSet#getObject(int). Generally, Sqlite's implementation of getObject(int) does not adhere to Table B.3.

For instance, if I call getColumnType(int) and get back Types.DATE, the JDBC specification states that I can expect values of type java.sql.Date for that column. However, if I call getObject(int) for a row including that column, sqlite returns a value of type java.lang.String.

[gotson]

I don't have time to write unit tests for sqlite

If you don't, why would we? You're just throwing issues without much details or repro, then you are saying you don't have any time to invest in this.

[rsmckinney]

There is plenty enough detail here for a “maintainer” to grok. If you aren’t interested in fixing stuff here, you might consider a different hobby.

[prrvchr]

@gotson That’s a separate issue re no type-safety.

In this case what I am saying is if a column is typed as Types.DATE, getObject(int) should always return a Java.sql.Date regardless of how the value was set. The method should attempt to coerce whatever is stored in that column to a Date. It should throw an exception if that can’t be done. If the user wants the value as some other data type, he can call getInt() etc. for specific coercions. This is how JDBC specifies getObject methods.

It seems to me that @rsmckinney is absolutely right: getObject() should return the Java type corresponding to the SQL type of the Table column, regardless of the format and/or type, enforced or not, used by the underlying database. It is up to the JDBC driver to do this job otherwise what would be the difference between getObject() and getString() with JDBC?

The primary objective when writing a JDBC driver is to keep in mind that JDBC was designed to be database agnostic.

Writing a test case for this seems like a waste of time. I think it would be faster to implement a switch case in the getObject() getter to address what's missing here.

But if I produce a PR that fixes this issue, how can I be sure it will be pushed?