Bug #3570 inconsistent reporting of column type
Submitted: 26 Apr 2004 16:04 Modified: 27 Apr 2004 10:21
Reporter: Karl Niemeier Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:mysql-connector-java-3.1-nightly-2004042 OS:Microsoft Windows (Windows XP Pro 2002)
Assigned to: Mark Matthews CPU Architecture:Any

[26 Apr 2004 16:04] Karl Niemeier
Description:
MySQL Version: 5.0.0-alpha-nt.
Driver: mysql-connector-java-3.1-nightly-20040426 ( $Date: 2004/02/21 17:43:42 $, $Revision: 1.27.4.31 $ )

The column types obtained from a ResultSetMetaData that you can get by doing a SELECT don't always agree with the column type obtained from a ResultSetMetaData that you get from DatabaseMetaData.getColumns. For example, for a float column, one returns java.sql.Types.REAL and the other returns java.sql.Types.FLOAT. For a blob, one returns Types.LONGVARBINARY and the other returns a Types.VARBINARY.

How to repeat:
Use the following DDL to create the test table:

drop table if exists test1;
create table test1 (x float, y blob);
insert into test1 (x) values(1);
insert into test1 (x) values(2);

Run the following Java code:

      Class.forName("com.mysql.jdbc.Driver");
      Connection conn = DriverManager.getConnection("your connection info");
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("select * from test1 where 1 = 0;");
      ResultSetMetaData rsmd = rs.getMetaData();
      for(int i=1; i<=rsmd.getColumnCount(); i++){
        System.out.println("ResultSetMetaData getColumnName: " + rsmd.getColumnName(i));
        int type = rsmd.getColumnType(i);
        System.out.println("ResultSetMetaData getColumnType: " + type);
      }

      stmt.close();
      DatabaseMetaData dbmd = conn.getMetaData();
      rs = dbmd.getColumns(null, null, "test1", "%");
      while (rs.next()) {
        String colName = rs.getString(4);
        System.out.println("getColumns: column name: " + colName);
        int type = rs.getInt(5);
        System.out.println("getColumns: column type: " + type);
      }

Observed Result:
ResultSetMetaData getColumnName: x
ResultSetMetaData getColumnType: 7
ResultSetMetaData getColumnName: y
ResultSetMetaData getColumnType: -4
getColumns: column name: x
getColumns: column type: 6
getColumns: column name: y
getColumns: column type: -3
[27 Apr 2004 10:21] Mark Matthews
This is now fixed in the source repository, and will be included with Connector/J 3.1.2 and 3.0.12...See http://downloads.mysql.com/snapshots.php to test the nightly snapshot with these fixes applied after 00:00 GMT today.