Bug #3557 Column default values are not accessible
Submitted: 24 Apr 2004 22:14 Modified: 25 Apr 2004 8:55
Reporter: Teemu Kuulasmaa Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:3.1.1-alpha -> OS:all?
Assigned to: CPU Architecture:Any

[24 Apr 2004 22:14] Teemu Kuulasmaa
Description:
Hi,

I have been developing my java applications by using server 4.0.x and Connector/J 3.0.x. Once I issue resultSet.moveToInserRow() Connector/J fetch column default values and insert row will be filled by these default values. My application is dependent on this Connector/J feature. Unfortunately MySQL server/Connector/J behavior has bee changed! Here is some examples how different server/driver version combinations work!

The combination (4.0.16/3.0.11) works like this (code in "How to repeat" section): 
--------------------------------------------------------------------------
DriverVer: mysql-connector-java-3.0.11-stable ( $Date: 2004/02/04 02:47:36 $, $Revision: 1.27.2.34 $ )
DatabaseVer: 4.0.16-log
Is ResultSet updatable: true
Default value (column 1): XYZ
Default value (column 2): 123
--------------------------------------------------------------------------

The combination (4.1.1a/3.0.11) works differently! Insert row doesn't get filled by column default values:
--------------------------------------------------------------------------
DriverVer: mysql-connector-java-3.0.11-stable ( $Date: 2004/02/04 02:47:36 $, $Revision: 1.27.2.34 $ )
DatabaseVer: 4.1.1a-alpha-max-nt
Is ResultSet updatable: true
Default value (column 1): null
Default value (column 2): null
--------------------------------------------------------------------------

The combination (4.1.1a/3.1-nightly-20040424) throws SQLException:
(if table is empty I get "Exception: Illegal operation on empty result set"!!!)
--------------------------------------------------------------------------
DriverVer: mysql-connector-java-3.1-nightly-20040424 ( $Date: 2004/02/21 17:43:42 $, $Revision: 1.27.4.31 $ )
DatabaseVer: 4.1.1a-alpha-max-nt
Is ResultSet updatable: true
Exception: Illegal operation on empty result set.
java.sql.SQLException: Illegal operation on empty result set.
	at com.mysql.jdbc.ResultSet.checkRowPos(ResultSet.java:4616)
	at com.mysql.jdbc.ResultSet.getObject(ResultSet.java:1393)
	at JDBCTest.<init>(JDBCTest.java:52)
	at JDBCTest.main(JDBCTest.java:64)
--------------------------------------------------------------------------

The combination (4.1.1a/3.1-nightly-20040424) throws SQLException:
(if table is not empty I get "java.sql.SQLException: Before start of result set" !!!)
--------------------------------------------------------------------------
DriverVer: mysql-connector-java-3.1-nightly-20040424 ( $Date: 2004/02/21 17:43:42 $, $Revision: 1.27.4.31 $ )
DatabaseVer: 4.1.1a-alpha-max-nt
Is ResultSet updatable: true
Exception: Before start of result set
java.sql.SQLException: Before start of result set
	at com.mysql.jdbc.ResultSet.checkRowPos(ResultSet.java:4622)
	at com.mysql.jdbc.ResultSet.getObject(ResultSet.java:1393)
	at JDBCTest.<init>(JDBCTest.java:52)
	at JDBCTest.main(JDBCTest.java:64)
--------------------------------------------------------------------------

The combination (4.0.16/3.1-nightly-20040424) throws SQLException:
(if table is empty I get "Exception: Illegal operation on empty result set"!!!)
--------------------------------------------------------------------------
DriverVer: mysql-connector-java-3.1-nightly-20040424 ( $Date: 2004/02/21 17:43:42 $, $Revision: 1.27.4.31 $ )
DatabaseVer: 4.0.16-log
Is ResultSet updatable: true
Exception: Illegal operation on empty result set. 
java.sql.SQLException: Illegal operation on empty result set.
	at com.mysql.jdbc.ResultSet.checkRowPos(ResultSet.java:4616)
	at com.mysql.jdbc.ResultSet.getObject(ResultSet.java:1393)
	at JDBCTest.<init>(JDBCTest.java:52)
	at JDBCTest.main(JDBCTest.java:64)
--------------------------------------------------------------------------

The combination (4.0.16/3.1-nightly-20040424) throws SQLException:
(if table is not empty I get "java.sql.SQLException: Before start of result set"!!!)
--------------------------------------------------------------------------
DriverVer: mysql-connector-java-3.1-nightly-20040424 ( $Date: 2004/02/21 17:43:42 $, $Revision: 1.27.4.31 $ )
DatabaseVer: 4.0.16-log
Is ResultSet updatable: true
Exception: Before start of result set
java.sql.SQLException: Before start of result set
	at com.mysql.jdbc.ResultSet.checkRowPos(ResultSet.java:4622)
	at com.mysql.jdbc.ResultSet.getObject(ResultSet.java:1393)
	at JDBCTest.<init>(JDBCTest.java:52)
	at JDBCTest.main(JDBCTest.java:64)
--------------------------------------------------------------------------

How to repeat:
public class JDBCTest 
{
	public JDBCTest()
	{
		try 
		{
			Driver mysqlDriver = (Driver)Class.forName("com.mysql.jdbc.Driver").newInstance(); 
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:13306/test?user=root&password=root");
			System.out.println("DriverVer: " + conn.getMetaData().getDriverVersion());
			System.out.println("DatabaseVer: " + conn.getMetaData().getDatabaseProductVersion());
			
			Statement stmt1 = conn.createStatement();
			stmt1.executeUpdate("DROP TABLE IF EXISTS `test`.`test1`");
			stmt1.executeUpdate(
				"CREATE TABLE `test`.`test1` ( " +
				"`a` varchar(255) NOT NULL default 'XYZ', " +
				"`b` varchar(255) default '123', " +
				"PRIMARY KEY  (`a`))"
			);
			//stmt1.executeUpdate("INSERT INTO `test`.`test1` VALUES ('x','x')");
			
			Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			ResultSet rs = stmt.executeQuery("SELECT * FROM `test`.`test1`");
			
			System.out.println("Is ResultSet updatable: " + (rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE));
			rs.moveToInsertRow();
			System.out.println("Default value (column 1): " + rs.getObject(1));
		System.out.println("Default value (column 2): " + rs.getObject(2));
		}
		catch (Exception ex) 
		{
			System.out.println("Exception: " + ex.getMessage()); 
			ex.printStackTrace();
		}
	}

	public static void main(String args[])
	{
		new JDBCTest();
	}
}

Suggested fix:
I do not have any but I hope that Mark Matthews has one ;-)
[25 Apr 2004 8:55] 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