ResultSetMetaData does not return the SQL Type if there are no results from the query

[sproket]

Hi all,

If I run the following in my Unit test I see all the SQL types
returned as 0

rs = st.executeQuery("SELECT * FROM Orders WHERE 1=0");

ResultSetMetaData rsMetaData = rs.getMetaData();
for (int i = 1; i <= rsMetaData.getColumnCount(); i++) {
log.info(rsMetaData.getColumnName(i) + " " +
rsMetaData.isAutoIncrement(i) + " " + rsMetaData.getColumnType(i));
}

Result:
INFO May 15 07:09:10 net.sf.pinf.TestSQLite - ID true 0
INFO May 15 07:09:10 net.sf.pinf.TestSQLite - NAME false 0
INFO May 15 07:09:10 net.sf.pinf.TestSQLite - Customer_ID false 0
INFO May 15 07:09:10 net.sf.pinf.TestSQLite - CREATED false 0

If I try this with other jdbc drivers on different databases I do get
the types back.

[Joel Carranza]

You will want to consult the SQLite documentation on types: http://www.sqlite.org/datatype3.html

"Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored.

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container."

This is unfortunately at odds with JDBC. The ResultSetMetdata returned by Xerial returns the value type within the context of the current result set row.

So you must call rs.next() before you get anything useful out of  ResultSetMetaData.getColumnType().  Alternatively you may want to use the JDBC function  DatabaseMetaData.getColumns() which returns a type based on the column "storage class"

--joel

[Grace B]

Fixed the issue https://bitbucket.org/xerial/sqlite-jdbc/issue/43