Description:
Hi,
We get different return values when using INSERT ON DUPLICATE KEY UPDATE with jdbc connector than using mysql commandline. In commandline we get a value of 2 when the statement INSERT ON DUPLICATE KEY update a row, while we get a value of 3 with the jdbc connector
How to repeat:
mysql> use test
Database changed
mysql> CREATE TABLE `LgstApplication` (
-> `a` varchar(25) NOT NULL COMMENT 'Format is: CountryCode+Number+Kind',
-> `b` varchar(50) DEFAULT NULL COMMENT 'Questel XAP',
-> `c` date DEFAULT NULL COMMENT 'Questel APD\nPRS L014EP\nXLEV L007EP',
-> `d` enum('INVENTION','DESIGN','UTILITY_MODEL','PLANT_PATENT','TRANSLATION') DEFAULT NULL COMMENT '(see Enumd class)',
-> `e` date DEFAULT NULL COMMENT 'First Date of Issued Grant',
-> `f` date DEFAULT NULL COMMENT 'if PAP is present takes PAP\nelse if FD contains the XAP [xxxxCC-xxxxxxx] take the Oldest FD dates \n',
-> `g` varchar(255) DEFAULT NULL COMMENT 'Linked to the f',
-> `h` varchar(50) DEFAULT NULL,
-> `EED` date DEFAULT NULL COMMENT 'Expected Expiration Date',
-> `j` date DEFAULT NULL COMMENT 'If a user override the EED value\n',
-> `k` tinyint(1) DEFAULT NULL COMMENT 'If the OverridenEEDValue is permanent or if the next event on this application will re-compute the EED and the OverridenEEDValue will be reset to null\n',
-> `l` tinyint(1) NOT NULL DEFAULT '1',
-> `m` varchar(25) DEFAULT NULL,
-> `n` varchar(25) DEFAULT NULL,
-> `o` smallint(5) unsigned DEFAULT NULL,
-> `p` tinyint(1) NOT NULL DEFAULT '1',
-> `q` tinyint(1) NOT NULL DEFAULT '0',
-> `r` tinyint(1) DEFAULT NULL COMMENT 'From Questel NO field.\nUS only, if a disclaimer is noted',
-> `s` tinyint(3) unsigned NOT NULL COMMENT '2 for WO, 1 for EP, 0 for others, this column is used only for sort reasons',
-> PRIMARY KEY (`a`),
-> KEY `idx1` (`l`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO LgstApplication (a,b,c,d,e,f,g,h,EED,j,k,l,m,n,o,p,q,r,s) VALUES ('CA458556A','1984CA-0458556','1984-07-10','INVENTION',NULL,NULL,NULL,NULL,NULL,NULL,0,1,NULL,NULL,0,1,0,0,0) ON DUPLICATE KEY UPDATE b = VALUES(b), c = VALUES(c), d = VALUES(d), e = VALUES(e), f = VALUES(f), g = VALUES(g), h = VALUES(h), m = VALUES(m), n = VALUES(n), o = VALUES(o),p = VALUES(p), r = VALUES(r), s = VALUES(s);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO LgstApplication (a,b,c,d,e,f,g,h,EED,j,k,l,m,n,o,p,q,r,s) VALUES ('CA458556A','1984CA-0458556','1984-07-11','INVENTION','1989-03-07',NULL,NULL,NULL,NULL,NULL,0,1,NULL,NULL,0,1,0,0,0) ON DUPLICATE KEY UPDATE b = VALUES(b), c = VALUES(c), d = VALUES(d), e = VALUES(e), f = VALUES(f), g = VALUES(g), h = VALUES(h), m = VALUES(m), n = VALUES(n), o = VALUES(o),p = VALUES(p), r = VALUES(r), s = VALUES(s);
Query OK, 2 rows affected (0.00 sec)
Using jdbc connector 5.1.16 :
/*****sample code*****/
/*** you will need to adapt it with your configuration ***/
public static void main(String arg[]) throws Exception{
System.setProperty("JREP_PROPERTIES_FILE", "file.properties");
Environment.loadProperties();
String driver = System.getProperty(Environment.JREP_DB_DRIVER);
try{
Class.forName(driver);
Connection connexion = DriverManager.getConnection(
System.getProperty(Environment.JREP_DB_URL),
System.getProperty(Environment.JREP_DB_USER),
System.getProperty(Environment.JREP_DB_PWD));
Statement instruction = connexion.createStatement();
int i1 = instruction.executeUpdate("delete from LgstApplication");
System.out.println("i1="+i1);
int i2 = instruction.executeUpdate("INSERT INTO LgstApplication (a,b,c,d,e,f,g,h,EED,j,k,l,m,n,o,p,q,r,s) VALUES ('CA458556A','1984CA-0458556','1984-07-10','INVENTION',NULL,NULL,NULL,NULL,NULL,NULL,0,1,NULL,NULL,0,1,0,0,0) ON DUPLICATE KEY UPDATE b = VALUES(b), c = VALUES(c), d = VALUES(d), e = VALUES(e), f = VALUES(f), g = VALUES(g), h = VALUES(h), m = VALUES(m), n = VALUES(n), o = VALUES(o),p = VALUES(p), r = VALUES(r), s = VALUES(s)");
System.out.println("i2="+i2);
int i3 = instruction.executeUpdate("INSERT INTO LgstApplication (a,b,c,d,e,f,g,h,EED,j,k,l,m,n,o,p,q,r,s) VALUES ('CA458556A','1984CA-0458556','1984-07-11','INVENTION','1989-03-07',NULL,NULL,NULL,NULL,NULL,0,1,NULL,NULL,0,1,0,0,0) ON DUPLICATE KEY UPDATE b = VALUES(b), c = VALUES(c), d = VALUES(d), e = VALUES(e), f = VALUES(f), g = VALUES(g), h = VALUES(h), m = VALUES(m), n = VALUES(n), o = VALUES(o),p = VALUES(p), r = VALUES(r), s = VALUES(s)");
System.out.println("i3="+i3);
}
catch (Exception e){
System.out.println("Driver error : "+e);
}
}
/***end of sample code***/
Output :
i1=1
i2=1
i3=3 <--- you see the differences ?
Description: Hi, We get different return values when using INSERT ON DUPLICATE KEY UPDATE with jdbc connector than using mysql commandline. In commandline we get a value of 2 when the statement INSERT ON DUPLICATE KEY update a row, while we get a value of 3 with the jdbc connector How to repeat: mysql> use test Database changed mysql> CREATE TABLE `LgstApplication` ( -> `a` varchar(25) NOT NULL COMMENT 'Format is: CountryCode+Number+Kind', -> `b` varchar(50) DEFAULT NULL COMMENT 'Questel XAP', -> `c` date DEFAULT NULL COMMENT 'Questel APD\nPRS L014EP\nXLEV L007EP', -> `d` enum('INVENTION','DESIGN','UTILITY_MODEL','PLANT_PATENT','TRANSLATION') DEFAULT NULL COMMENT '(see Enumd class)', -> `e` date DEFAULT NULL COMMENT 'First Date of Issued Grant', -> `f` date DEFAULT NULL COMMENT 'if PAP is present takes PAP\nelse if FD contains the XAP [xxxxCC-xxxxxxx] take the Oldest FD dates \n', -> `g` varchar(255) DEFAULT NULL COMMENT 'Linked to the f', -> `h` varchar(50) DEFAULT NULL, -> `EED` date DEFAULT NULL COMMENT 'Expected Expiration Date', -> `j` date DEFAULT NULL COMMENT 'If a user override the EED value\n', -> `k` tinyint(1) DEFAULT NULL COMMENT 'If the OverridenEEDValue is permanent or if the next event on this application will re-compute the EED and the OverridenEEDValue will be reset to null\n', -> `l` tinyint(1) NOT NULL DEFAULT '1', -> `m` varchar(25) DEFAULT NULL, -> `n` varchar(25) DEFAULT NULL, -> `o` smallint(5) unsigned DEFAULT NULL, -> `p` tinyint(1) NOT NULL DEFAULT '1', -> `q` tinyint(1) NOT NULL DEFAULT '0', -> `r` tinyint(1) DEFAULT NULL COMMENT 'From Questel NO field.\nUS only, if a disclaimer is noted', -> `s` tinyint(3) unsigned NOT NULL COMMENT '2 for WO, 1 for EP, 0 for others, this column is used only for sort reasons', -> PRIMARY KEY (`a`), -> KEY `idx1` (`l`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO LgstApplication (a,b,c,d,e,f,g,h,EED,j,k,l,m,n,o,p,q,r,s) VALUES ('CA458556A','1984CA-0458556','1984-07-10','INVENTION',NULL,NULL,NULL,NULL,NULL,NULL,0,1,NULL,NULL,0,1,0,0,0) ON DUPLICATE KEY UPDATE b = VALUES(b), c = VALUES(c), d = VALUES(d), e = VALUES(e), f = VALUES(f), g = VALUES(g), h = VALUES(h), m = VALUES(m), n = VALUES(n), o = VALUES(o),p = VALUES(p), r = VALUES(r), s = VALUES(s); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO LgstApplication (a,b,c,d,e,f,g,h,EED,j,k,l,m,n,o,p,q,r,s) VALUES ('CA458556A','1984CA-0458556','1984-07-11','INVENTION','1989-03-07',NULL,NULL,NULL,NULL,NULL,0,1,NULL,NULL,0,1,0,0,0) ON DUPLICATE KEY UPDATE b = VALUES(b), c = VALUES(c), d = VALUES(d), e = VALUES(e), f = VALUES(f), g = VALUES(g), h = VALUES(h), m = VALUES(m), n = VALUES(n), o = VALUES(o),p = VALUES(p), r = VALUES(r), s = VALUES(s); Query OK, 2 rows affected (0.00 sec) Using jdbc connector 5.1.16 : /*****sample code*****/ /*** you will need to adapt it with your configuration ***/ public static void main(String arg[]) throws Exception{ System.setProperty("JREP_PROPERTIES_FILE", "file.properties"); Environment.loadProperties(); String driver = System.getProperty(Environment.JREP_DB_DRIVER); try{ Class.forName(driver); Connection connexion = DriverManager.getConnection( System.getProperty(Environment.JREP_DB_URL), System.getProperty(Environment.JREP_DB_USER), System.getProperty(Environment.JREP_DB_PWD)); Statement instruction = connexion.createStatement(); int i1 = instruction.executeUpdate("delete from LgstApplication"); System.out.println("i1="+i1); int i2 = instruction.executeUpdate("INSERT INTO LgstApplication (a,b,c,d,e,f,g,h,EED,j,k,l,m,n,o,p,q,r,s) VALUES ('CA458556A','1984CA-0458556','1984-07-10','INVENTION',NULL,NULL,NULL,NULL,NULL,NULL,0,1,NULL,NULL,0,1,0,0,0) ON DUPLICATE KEY UPDATE b = VALUES(b), c = VALUES(c), d = VALUES(d), e = VALUES(e), f = VALUES(f), g = VALUES(g), h = VALUES(h), m = VALUES(m), n = VALUES(n), o = VALUES(o),p = VALUES(p), r = VALUES(r), s = VALUES(s)"); System.out.println("i2="+i2); int i3 = instruction.executeUpdate("INSERT INTO LgstApplication (a,b,c,d,e,f,g,h,EED,j,k,l,m,n,o,p,q,r,s) VALUES ('CA458556A','1984CA-0458556','1984-07-11','INVENTION','1989-03-07',NULL,NULL,NULL,NULL,NULL,0,1,NULL,NULL,0,1,0,0,0) ON DUPLICATE KEY UPDATE b = VALUES(b), c = VALUES(c), d = VALUES(d), e = VALUES(e), f = VALUES(f), g = VALUES(g), h = VALUES(h), m = VALUES(m), n = VALUES(n), o = VALUES(o),p = VALUES(p), r = VALUES(r), s = VALUES(s)"); System.out.println("i3="+i3); } catch (Exception e){ System.out.println("Driver error : "+e); } } /***end of sample code***/ Output : i1=1 i2=1 i3=3 <--- you see the differences ?