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 ;-)
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 ;-)