Bug #1206 ResultSetMetaData.getColumnType(int) returns VARCHAR (12) for DATETIME columns
Submitted: 5 Sep 2003 14:54 Modified: 5 Sep 2003 15:50
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:4.1.0-alpha OS:Microsoft Windows (Windows 2000 Pro V 5.0 sp2)
Assigned to: CPU Architecture:Any

[5 Sep 2003 14:54] [ name withheld ]
Description:
When using the MySQLConnector/J to access a MyISAM table, the ResultSetMetaData.getColumnType(int) method returns VARCHAR (12) instead of DATETIME (93).  This was repeated with versions 3.0.6, 3.0.8 and 3.1 alpha of the MySQLConnector/J driver.  See the code in the 'How to repeat' section to recreate the problem.  I am not sure whether this problem is related to the MySQL Connector/J driver or the 4.1.0-alpha database server.
     This could be very problematic since this method is used in the com.workingdogs.village api which is used by the Torque Jakarta Project.  All database retrievals in Torque rely on this functionality which causes DATETIME fields always to be returned as null.

How to repeat:
-- Here is the SQL to create the test db
drop database if exists test;
create database test;

grant all privileges on test.* to testuser identified by 'testuser';

use test;

drop table if exists TestTable;

CREATE TABLE TestTable
(
                    TestTableId BIGINT NOT NULL,
                    TestDate DATETIME NULL,
    PRIMARY KEY(TestTableId)
);

insert into TestTable (TestTableId, TestDate) values (1,'2003-09-04');
----------------------------------------------------------------------
// Here is the sample JDBC code to show the error

import java.sql.*;

public class JDBCDateTest {

  public static void main(String[] args) throws Exception{

	// Load the driver
	Class.forName("com.mysql.jdbc.Driver");

	// Establish a connection
	String url = "jdbc:mysql://localhost:3306/test";
	Connection conn = DriverManager.getConnection(url,"testuser", "testuser");

	// Create and execute a statment
	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery("SELECT * From TestTable");
	String columnName = "TestDate";

	// Display the results
	ResultSetMetaData meta = rs.getMetaData();
	int columnNumber = rs.findColumn(columnName);

	while(rs.next()) {
		System.out.println("Column Value: " + rs.getDate(columnName));
		System.out.println("Column Type Name: " + meta.getColumnTypeName (columnNumber));
		System.out.println("Column Type: " + meta.getColumnType (columnNumber));
	}
  }
}
// This produced the following results for me:
// Column Value: 2003-09-04
// Column Type Name: UNKNOWN
// Column Type: 12
[5 Sep 2003 15:50] Mark Matthews
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

This has been fixed for quite some time in the nightly builds of 3.0.x and 3.1.x. It was related to protocol-level changes in MySQL-4.1.0. I've tested both with your code, and get the correct type, 'DATETIME'.

Check out http://mmmysql.sourceforge.net/snapshots/ for nightly builds of Connector/J that you can test this with yourself.
[8 Sep 2003 12:29] [ name withheld ]
This seems to be an issue only with the 1.3.1_04 jdk.  I upgraded to 1.4.2 and the problem was corrected.