Bug #8868 Formatted columns returning strange column type

• Submitted: 1 Mar 2005 15:52
• Modified: 17 May 2005 16:41
• Reporter: Gary McMeekin
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 3.1.7
• OS: Linux (Linux FC3)
• Assigned to: Sergei Golubchik
• CPU Architecture: Any

[1 Mar 2005 15:52] Gary McMeekin

Description:
I have a SQL which formats a timestamp as show below:

SELECT DATE_FORMAT(received_datetime,'%b-%e %l:%i%p') as fmtddate <snip>

I was getting a type conversion error in BeanUtils ResultSetDynaClass and upon digging deeper found that the colum is being assigned the type '[B'. Here is the diagnostic code:

ResultSet rs = pstmt.executeQuery();
rs.next();
Object o = rs.getObject("fmtddate");
logger.debug(o.getClass().getName());

Output:

2005-03-01 10:32:17,715 PoolThread-1 MessageLog  - [B
2005-03-01 10:32:17,763 PoolThread-1 MessageLog  - org.apache.commons.beanutils.ConversionException: Cannot assign value of type '[B' to property 'fmtddate' of type 'java.lang.String'

Output when reverted back to 3.0.16-ga:

2005-03-01 10:41:03,880 PoolThread-1 MessageLog  - java.lang.String
2005-03-01 10:41:03,939 PoolThread-1 MessageLog  - Completed list of messages

How to repeat:
1. Create a ResultSet over a table with the SQL in the description over a Timestamp column.

2. Examine the type of the column returned

[16 Mar 2005 7:27] R Fields

We are also having this exact problem.  We just recently upgraded to the 3.1 series of Connector/J, and all of our database queries that used DATE_FORMAT are now broken.

[16 Mar 2005 14:31] Mark Matthews

This is fixed for 3.1.8, available now in nightly snapshots from http://downloads.mysql.com/snapshots.php

[16 Mar 2005 22:14] R Fields

Hello,

We are getting mixed results for this bug fix (we are using mysql-connector-java-3.1-nightly-20050316-bin.jar).

In some snippets of code, it now appears to be working, and we are getting a String back.  In others, we continue to get the blob.

The following snippet of code (that we use to map the results into a Vector of Hashtables) continues to have the problem:

    public static Vector createResultsVector(ResultSet rs) throws DBException {
        try {
            Vector resultVector = new Vector();
            ResultSetMetaData meta = rs.getMetaData();
            while (rs.next()) {
                Hashtable currentRow = new Hashtable();
                for (int i = 1; i <= meta.getColumnCount(); i++) {
                    Object data = rs.getObject(i);
                    // let's print this out to see what we're getting here
                    System.out.println(meta.getColumnName(i) + "-" + data.getClass().getName());
                    if (data != null) {
                        currentRow.put(meta.getColumnName(i), data);
                    }
                }
                resultVector.addElement(currentRow);
            }
            return resultVector;                                                                                          
        }
        catch (SQLException e) {
            cat.error("DB.createResultsVector failed: " + e.getMessage());
        }
    }

Thank you,
-Richard

[16 Mar 2005 22:21] Mark Matthews

Richard, without seeing the query and the DDL of the table(s) involved, this code snippet isn't much help for resolving the issue.

This is really unfortunately a server bug that the driver has to work-around, so not all cases are currently caught.

-Mark

[16 Mar 2005 23:42] R Fields

Created bug 9236 so that I could upload the table dump that demonstrates the bug.

[17 May 2005 9:29] Sergei Golubchik

no bug in the server here, DATE_FORMAT in 4.1 returns a binary string.

[5 Jan 2006 7:15] tom quas

I'm running into the exact same misbehavior with mysql-connector-java-3.1.12 on

MySQL server version: 4.1.7-standard-log on SUSE 9.1
JDK 1.4.2_09
commons-beanutils 1.6

My query:

select username, MAX(last_startphp) as portal, MAX(last_deep) as deep, MAX(last_cam) as cam
from user_logins
group by username
having username = ?

on a table with the following structure:

CREATE TABLE `user_logins` (
  `id` bigint(20) NOT NULL auto_increment,
  `username` varchar(50) character set latin1 collate latin1_general_ci NOT NULL default '',
  `last_startphp` datetime default NULL,
  `last_deep` datetime default NULL,
  `last_cam` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `username_idx` (`username`),
  KEY `last_startphp_idx` (`last_startphp`),
  KEY `last_deep_idx` (`last_deep`),
  KEY `last_cam_idx` (`last_cam`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci

The Java code:

    protected static DynaBean[] createResult( ResultSet rs ) throws Exception {
        List results = new ArrayList();
        ResultSetDynaClass rsdc = new ResultSetDynaClass( rs );
        BasicDynaClass bdc = new BasicDynaClass( "result", BasicDynaBean.class, rsdc.getDynaProperties() );
        Iterator rows = rsdc.iterator();
        while ( rows.hasNext() ) {
            DynaBean oldRow = (DynaBean)rows.next();
            DynaBean newRow = bdc.newInstance();
            PropertyUtils.copyProperties( newRow, oldRow );
            results.add( newRow );
        }
        return (DynaBean[])convertToArray( results, DynaBean.class );

	static Object[] convertToArray(Collection coll, Class compontentType) {
		Object[] array = (Object[])Array.newInstance(compontentType, coll .size());
		return coll.toArray(array);
	}

Is there a quick way to get around this problem?