Bug #21770 MetaData getColumnClassName() Returning Strange Result & Some Text as Blob

• Submitted: 22 Aug 2006 7:44
• Modified: 18 Sep 2006 8:39
• Reporter: dana
• Status: Not a Bug
• Category: Connector/J
• Severity: S2 (Serious)
• Version: mysql-connector-java-3.1.13
• OS: Linux (linux and win)
• Assigned to: Tonci Grgin

[22 Aug 2006 7:44] dana

Description:
===================================================================================
Resulting Data output from two connectorj versions. Notice blob columnclass
===================================================================================

mysql-connector-java-3.1.13-bin.jar  

1 data_type_id Data Type Id java.lang.Long INTEGER UNSIGNED 10
2 char_type Char Type java.lang.String VARCHAR 30
3 varchar_type Varchar Type java.lang.String VARCHAR 30

4 tinyBlob_type TinyBlob Type [B TINYBLOB 255
5 blob_type Blob Type [B BLOB 65535
6 mediumBlob_type MediumBlob Type [B MEDIUMBLOB 16777215
7 longBlob_type LongBlob Type [B MEDIUMBLOB 16777215

8 tinyText_type TinyText Type java.lang.String TINYBLOB 255
9 text_type Text Type java.lang.String TEXT 65535
10 mediumText_type MediumText Type java.lang.String MEDIUMBLOB 16777215
11 longText_type LongText Type java.lang.String MEDIUMBLOB 16777215
12 enum_type Enum Type java.lang.String CHAR 4
13 set_type Set Type java.lang.String CHAR 5
14 tinyInt_type TinyInt Type java.lang.Integer TINYINT 4
15 smallInt_type SmallInt Type java.lang.Integer SMALLINT UNSIGNED 5
16 mediumInt_type MediumInt Type java.lang.Integer MEDIUMINT 9
17 int_type Int Type java.lang.Integer INTEGER 11
18 bigInt_type BigInt Type java.lang.Long BIGINT 20
19 float_type Float Type java.lang.Float FLOAT 12
20 double_type Double Type java.lang.Double DOUBLE 22
21 decimal_type Decimal Type java.math.BigDecimal DECIMAL 18
22 date_type Date Type java.sql.Date DATE 10
23 time_type Time Type java.sql.Time TIME 8
24 dateTime_type DateTime Type java.sql.Timestamp DATETIME 19
25 timeStamp_type TimeStamp Type java.sql.Timestamp TIMESTAMP 14
26 year_type Year Type java.sql.Date YEAR 4

mysql-connector-java-3.0.10-stable-bin.jar

1 data_type_id Data Type Id java.lang.Long LONG 10
2 char_type Char Type java.lang.String VARCHAR 30
3 varchar_type Varchar Type java.lang.String VARCHAR 30

4 tinyBlob_type TinyBlob Type java.lang.Object BLOB 255
5 blob_type Blob Type java.lang.Object BLOB 65535
6 mediumBlob_type MediumBlob Type java.lang.Object BLOB 16777215
7 longBlob_type LongBlob Type java.lang.Object BLOB 16777215

8 tinyText_type TinyText Type java.lang.String TEXT 255
9 text_type Text Type java.lang.String TEXT 65535
10 mediumText_type MediumText Type java.lang.String TEXT 16777215
11 longText_type LongText Type java.lang.String TEXT 16777215
12 enum_type Enum Type java.lang.String CHAR 4
13 set_type Set Type java.lang.String CHAR 5
14 tinyInt_type TinyInt Type java.lang.Byte TINY 4
15 smallInt_type SmallInt Type java.lang.Integer SHORT 5
16 mediumInt_type MediumInt Type java.lang.Integer INT 9
17 int_type Int Type java.lang.Integer LONG 11
18 bigInt_type BigInt Type java.lang.Long LONGLONG 20
19 float_type Float Type java.lang.Float FLOAT 12
20 double_type Double Type java.lang.Double DOUBLE 22
21 decimal_type Decimal Type java.math.BigDecimal DECIMAL 18
22 date_type Date Type java.sql.Date DATE 10
23 time_type Time Type java.sql.Time TIME 8
24 dateTime_type DateTime Type java.sql.Timestamp DATETIME 19
25 timeStamp_type TimeStamp Type java.sql.Timestamp TIMESTA

How to repeat:
==============================================================================
Code for obtaining column Name, class, type, and size.
==============================================================================
			

	dbMetaData = dbConnection.getMetaData();

	sqlStatementString = "SELECT * FROM " + sqlTable + " LIMIT 1";

	db_resultSet = mysqlStatement.executeQuery(sqlStatementString);

	tableMetaData = db_resultSet.getMetaData();

					

	//=======================================================	
	// Column Names, Form Fields, ComboBox Text and HashMaps

	for (int i=1; i<tableMetaData.getColumnCount()+1; i++)

	{	

		colNameString = tableMetaData.getColumnName(i);

		comboBoxNameString = parseColumnNameField(colNameString);

		fields.addElement(comboBoxNameString);

		columnNamesHashMap.put(comboBoxNameString, colNameString);

				

		// Additional Information about each column.

		columnClass = tableMetaData.getColumnClassName(i);

		columnClassHashMap.put(comboBoxNameString, columnClass);

				

		columnType = tableMetaData.getColumnTypeName(i);

		columnTypeHashMap.put(comboBoxNameString, columnType);

				

		columnSize = new Integer(tableMetaData.getColumnDisplaySize(i));

		columnSizeHashMap.put(comboBoxNameString, columnSize);

				

		System.out.println(i + " " + colNameString + " " +

				   comboBoxNameString + " " + 

				   columnClass + " " + columnType + " " +

				   columnSize);

	}

===========================================================================
SQL Table description.
===========================================================================

DROP TABLE IF EXISTS data_types;
CREATE TABLE data_types (

    # Table id and creation data entries.

    data_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

    # Character, text, and blob type fields.

    char_type CHAR(30) DEFAULT NULL,
    varchar_type VARCHAR(30) DEFAULT NULL,
    tinyBlob_type TINYBLOB DEFAULT NULL,
    blob_type BLOB DEFAULT NULL,
    mediumBlob_type MEDIUMBLOB DEFAULT NULL,
    longBlob_type LONGBLOB DEFAULT NULL,
    tinyText_type TINYTEXT DEFAULT NULL,
    text_type TEXT DEFAULT NULL,
    mediumText_type MEDIUMTEXT DEFAULT NULL,
    longText_type LONGTEXT DEFAULT NULL,
    enum_type ENUM("New", "Used") NOT NULL DEFAULT 'New',
    set_type SET("a", "b", "c") DEFAULT NULL,

    # Numeric fields.

    tinyInt_type TINYINT DEFAULT NULL,
    smallInt_type SMALLINT UNSIGNED DEFAULT NULL,
    mediumInt_type MEDIUMINT DEFAULT NULL,
    int_type INT DEFAULT NULL,
    bigInt_type BIGINT DEFAULT NULL,
    float_type FLOAT DEFAULT NULL,
    double_type DOUBLE DEFAULT NULL,
    decimal_type DECIMAL(16,2) DEFAULT NULL,
    
    # Date and time fields.
   
    date_type DATE  DEFAULT NULL,
    time_type TIME DEFAULT NULL,
    dateTime_type DATETIME DEFAULT NULL,
    timeStamp_type TIMESTAMP(14) DEFAULT NULL, 
    year_type YEAR DEFAULT NULL
)
TYPE = InnoDB;

[31 Aug 2006 12:32] Tonci Grgin

Test case

Attachment: TestBug21770.java (text/x-java), 4.16 KiB.

[31 Aug 2006 12:41] Tonci Grgin

Hi dana and thanks for your problem report.
Test case result:
Connected to 5.0.23-log BK, Win XP
1 data_type_id java.lang.Long INTEGER UNSIGNED 10
2 char_type java.lang.String CHAR 30
3 varchar_type java.lang.String VARCHAR 30
4 tinyBlob_type [B TINYBLOB 255
5 blob_type [B BLOB 65535
6 mediumBlob_type [B MEDIUMBLOB 16777215
7 longBlob_type [B LONGBLOB 2147483647
8 tinyText_type java.lang.String VARCHAR 255
9 text_type java.lang.String VARCHAR 65535
10 mediumText_type java.lang.String VARCHAR 16777215
11 longText_type java.lang.String VARCHAR 2147483647
12 enum_type java.lang.String CHAR 4
13 set_type java.lang.String CHAR 5
14 tinyInt_type java.lang.Integer TINYINT 4
15 smallInt_type java.lang.Integer SMALLINT UNSIGNED 5
16 mediumInt_type java.lang.Integer MEDIUMINT 9
17 int_type java.lang.Integer INTEGER 11
18 bigInt_type java.lang.Long BIGINT 20
19 float_type java.lang.Float FLOAT 12
20 double_type java.lang.Double DOUBLE 22
21 decimal_type java.math.BigDecimal DECIMAL 18
22 date_type java.sql.Date DATE 10
23 time_type java.sql.Time TIME 8
24 dateTime_type java.sql.Timestamp DATETIME 19
25 timeStamp_type java.sql.Timestamp TIMESTAMP 19
26 year_type java.sql.Date YEAR 4

Test info:
 - Test was run from svn repo against latest 3.1 branch
 - MySQL server used is 5.0.23-log BK on WinXP Pro SP2 localhost
 - jre1.5.0_07
No assertation failed. VARCHAR types longer than 65535 bytes would have been silently
converted to corresponding TEXT types by server.

If you have new info to provide, please post it and reopen report.

[15 Sep 2006 20:46] dana

Tonci,
Notice your test output for blob type, the returned Meta
getColumnClassName get "[B" instead of the java.lang.Object.

4 tinyBlob_type [B TINYBLOB 255
5 blob_type [B BLOB 65535
6 mediumBlob_type [B MEDIUMBLOB 16777215
7 longBlob_type [B LONGBLOB 2147483647

3.0.10-stable Output:

4 tinyBlob_type TinyBlob Type java.lang.Object BLOB 255
5 blob_type Blob Type java.lang.Object BLOB 65535
6 mediumBlob_type MediumBlob Type java.lang.Object BLOB 16777215
7 longBlob_type LongBlob Type java.lang.Object BLOB 16777215

[18 Sep 2006 8:39] Tonci Grgin

Hi Dana. This change is noted in the manual:
Changes in MySQL Connector/J 3.0.11-stable (19 February 2004)
• Return [B instead of java.lang.Object for BINARY, VARBINARY and LONGVARBINARY types
from ResultSetMetaData.getColumnClassName() (JDBC compliance).