Bug #64942 | DATETIME and TIMESTAMP values returned as NULL with type VARCHAR | ||
---|---|---|---|
Submitted: | 11 Apr 2012 11:57 | Modified: | 11 Apr 2012 13:01 |
Reporter: | Damien Allison | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | Connector / J | Severity: | S4 (Feature request) |
Version: | 5.1.19 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[11 Apr 2012 11:57]
Damien Allison
[11 Apr 2012 12:11]
Tonci Grgin
Damien, please check DatabaseMetaData.java in test suite, around line 2381 (testABunchOfReturnTypes) for proper usage. If the problem persists, notify us again.
[11 Apr 2012 12:24]
Damien Allison
I will look into that. What I am confused about is that if I use getString (based on the fact that meta data is saying the column is varchar) it returns NULL for a NOT NULL column. the java.sql.ResultSet API spec says it returns null in case of null value but doesn't make provision for unknown type mapping.
[11 Apr 2012 12:38]
Damien Allison
Could you please share a link to the code? I am happy to review. If this behaviour is intentional then I am happy to degrade this bug to a feature request. Could it should be handled as an exception (unsupported type conversion?) rather than 'failing' in a way that is not in keeping with the sql API?
[11 Apr 2012 12:51]
Tonci Grgin
public void testBug64942() throws Exception { System.out.println("java.vm.version : " + System.getProperty("java.vm.version")); System.out.println("java.vm.vendor : " + System.getProperty("java.vm.vendor")); System.out.println("java.runtime.version : " + System.getProperty("java.runtime.version")); System.out.println("os.name : " + System.getProperty("os.name")); System.out.println("os.version : " + System.getProperty("os.version ")); System.out.println("sun.management.compiler : " + System.getProperty("sun.management.compiler")); try { createTable("testbug64942", "(id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"+ "uri VARCHAR(2048) NOT NULL,"+ "fetchedDate TIMESTAMP NOT NULL,"+ "data VARCHAR(255) NOT NULL"+ ") CHARACTER SET utf8;"); this.stmt.executeUpdate("INSERT INTO testbug64942 VALUES (NULL, 'Test', NOW(),'Longer test')"); this.rs = this.stmt.executeQuery("SELECT * FROM testbug64942"); ResultSetMetaData rsMetaData = this.rs.getMetaData(); int numberOfColumns = rsMetaData.getColumnCount(); System.out.println("Dumping column MetaData: "); System.out.println("=========="); for (int i = 1; i <= numberOfColumns; i++) { System.out.println("Column number: " + i); System.out.println("ColumnDisplaySize:" + rsMetaData.getColumnDisplaySize(i)); System.out.println("ColumnLabel: " + rsMetaData.getColumnLabel(i)); System.out.println("ColumnName: " + rsMetaData.getColumnName(i)); System.out.println("ColumnType: " + rsMetaData.getColumnType(i)); System.out.println("ColumnTypeName: " + rsMetaData.getColumnTypeName(i)); System.out.println("ColumnClassName: " + rsMetaData.getColumnClassName(i)); System.out.println("=========="); }; this.rs.close(); this.stmt.close(); } finally { } } Produces: Connected to 5.5.14-log java.vm.version : 1.5.0_17-b04 java.vm.vendor : Sun Microsystems Inc. java.runtime.version : 1.5.0_17-b04 os.name : Windows Server 2008 os.version : null sun.management.compiler : HotSpot Client Compiler Dumping column MetaData: ========== Column number: 1 ColumnDisplaySize:20 ColumnLabel: id ColumnName: id ColumnType: -5 ColumnTypeName: BIGINT UNSIGNED ColumnClassName: java.math.BigInteger ========== Column number: 2 ColumnDisplaySize:2048 ColumnLabel: uri ColumnName: uri ColumnType: 12 ColumnTypeName: VARCHAR ColumnClassName: java.lang.String ========== Column number: 3 ColumnDisplaySize:19 ColumnLabel: fetchedDate ColumnName: fetchedDate ColumnType: 93 ColumnTypeName: TIMESTAMP ColumnClassName: java.sql.Timestamp ========== Column number: 4 ColumnDisplaySize:255 ColumnLabel: data ColumnName: data ColumnType: 12 ColumnTypeName: VARCHAR ColumnClassName: java.lang.String ========== So I see no bug here.
[11 Apr 2012 13:01]
Damien Allison
One thing seems to be that I was pulling the table metadata when requesting the statement metadata (resulting in varchar). I need to revert the changes I have made to the table to find out why I was getting NULL (my issue). I have not changed my query just altered the underlying table and using getLong rather than getDate. I am going to close this for now until I have time to reproduce a longer example and investigate. (there is no - pending status).
[11 Apr 2012 13:01]
Damien Allison
One thing seems to be that I was pulling the table metadata when requesting the statement metadata (resulting in varchar). I need to revert the changes I have made to the table to find out why I was getting NULL (my issue). I have not changed my query just altered the underlying table and using getLong rather than getDate. I am going to close this for now until I have time to reproduce a longer example and investigate. (there is no - pending status).
[11 Apr 2012 13:08]
Tonci Grgin
Damien, this is fine by us. We are always looking over community bugs so your new post will be noted. Also, I see you are using way too big VARCHAR for Java specs possibly causing an error in code. Also, I do not see the INSERT command in your sample. So, next time, you should provide us with all the info you have (ie. MySQL server version is missing, JDK...) and a complete test case including connection string (mine was empty, thus I didn't mentioned it).