CachedRowSet and double column types  
Index ‹ java-programmer
Author Message
donlelel
PostPosted: 2006-12-24 23:01:00 Top
java-programmer, CachedRowSet and double column types Hi, there,

I have a CachedRowSet obtained from Sun's CachedRowSetImpl and I'm
trying to use it with a MySql database. I have a table test that has
the basic column types. If I use a plain double column type and I have
to update a row with the double field having the fraction part
something like 1/3 (i.e. 0.3333....), then the acceptChanges fails with
the error
javax.sql.rowset.spi.SyncProviderException: 1 conflicts while
synchronizing
If I have a double(12,2) column type or the like (i.e. I limit the
precision) or if I have a fixed fraction part (something like 0.5,
let's say), then acceptChanges works fine.
I havent logged the sqls generated by the acceptChanges of the
CachedRowSetImpl, but I remember encountering something similar on some
different software components and what was happening in that case (and
I suppose it's happen in this case too) is that the sqls generated were
trying to find the original row using all the fields in the row and not
only the primary key, as it should be the case, i.e. there was
something like :
update test set f1=x1, f2=x2, f3=x3 where f1=y1 and f2=y2 and f3=y3
Now, since there's that "unlimited" number of digits after the decimal
point, it wouldnt have found the original record (maybe because there
was a different number of digits used to represent the same number on
the MySql server and on the MySql client).

My question - is there a way to tell CachedRowSet how the row
identification should be done (to say what goes into the "where"
clause)? Eventually is there a smarter, free implementation of
CachedRowSet ?

Thanks, Don

PS. See used code and table data bellow.

---------------------------------

package dbtools;

import com.sun.rowset.CachedRowSetImpl;
import javax.sql.rowset.CachedRowSet;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import java.util.*;
import java.sql.*;

public class CacheRecSet {

Connection connection = null;
Random rand = new Random();
private Log log = LogFactory.getLog(this.getClass());

/** Creates a new instance of CacheRecSet */
public CacheRecSet() {
}

protected void setUp() {
if (connection != null) {
return;
}

/*
// MySql through OBDC
String uri =
"jdbc:odbc:mysqlodbcdbstorage;UID=tintin;PWD=tintin";

log.info("Initializing connection from '" + uri + "'");

try {
// Load the JDBC-ODBC bridge driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch (ClassNotFoundException e) {
log.error("Opening connection",e);
return;
}

try {
connection = DriverManager.getConnection(uri);
log.info(" Loaded connection from '" + uri + "'");
} catch (Exception e) {
log.error("ERROR Opening connection");
return;
}
*/


// MySql direct
String uri =
"jdbc:mysql://localhost:3306/dbstorage?user=tintin&password=tintin";

log.info("Initializing connection from '" + uri + "'");

try {
// Load the driver
Class.forName("org.gjt.mm.mysql.Driver");
} catch (ClassNotFoundException e) {
log.error("Opening connection", e);
}
// add support for connection pooling
// Class.forName("org.apache.commons.dbcp.PoolingDriver");

try {
connection = DriverManager.getConnection(uri);
log.info(" Loaded connection from '" + uri + "'");
} catch (Exception e) {
log.error("Opening connection",e);
}

/*
// PostgreSql direct
String uri =
"jdbc:postgresql://localhost:5432/dbstorage?user=tintin&password=tintin";

log.info("Initializing connection from '" + uri + "'");

try {
// Load the driver
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e) {
log.error("Opening connection", e);
}

try {
connection = DriverManager.getConnection(uri);
log.info(" Loaded connection from '" + uri + "'");
} catch (Exception e) {
log.error("Opening connection", e);
}
*/
}

protected void tearDown() {
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
log.error("Closing connection");
}
}

connection = null;

}

public void testMulti() {
if (connection == null) {
log.error("testMulti - null connection");
}

log.info("testMulti");

CachedRowSet crset;

try {
java.sql.Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM test where
RecId = 1"); // for postgresql it doesnt work like this
//ResultSet rs = stmt.executeQuery("SELECT * FROM test
where "RecId" = 1"); // for postgresql it works like this

crset = new CachedRowSetImpl();
crset.populate(rs);

log.info("SIZE = "+crset.size());
if (crset.size() > 0) {
crset.first();

double fract = (double)1 / 3;
//fract = fract/3;
double dbl = (rand.nextInt(50)*1000) + fract;
java.util.Date dt = new java.util.Date();
java.sql.Date dt2 = new java.sql.Date(dt.getTime());

crset.updateInt("Married", 0); // int field (wanted as
bool...)
crset.updateString("Name", "coco"); // varchar field
crset.updateDouble("Salary", dbl); // double fld
crset.updateDate("DtCreate", dt2); // date field
String comments = "a test "+dt.toString() + " ===> " +
rand.nextInt(50);
crset.updateString("Comments", comments); // blob field
crset.updateInt("Age", rand.nextInt(50)); // int field

//int [] keys = {1};
//crset.setKeyColumns(keys);

//crset.setTableName("test"); // when dealing
PostGreSql, needed to be added as it seems...

crset.updateRow();
crset.acceptChanges(connection);
}

rs.close();

} catch (Exception e) {
log.error("testMulti",e);
return;
}

log.info("...OK");
}

public static void main(String args[]) {
CacheRecSet instance = new CacheRecSet();

instance.setUp();
instance.testMulti();
instance.tearDown();
}

}

-----------------------------------


CREATE DATABASE IF NOT EXISTS dbstorage;
USE dbstorage;

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`RecID` int(10) unsigned NOT NULL auto_increment,
`Married` int(11) default NULL,
`Name` varchar(200) default NULL,
`Salary` double(10,2) default NULL,
`DtCreate` date default NULL,
`Comments` text,
`Age` int(10) unsigned default NULL,
PRIMARY KEY (`RecID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*!40000 ALTER TABLE `test` DISABLE KEYS */;
INSERT INTO `test`
(`RecID`,`Married`,`Name`,`Salary`,`DtCreate`,`Comments`,`Age`) VALUES
(1,0,'coco',15000.33,'2006-12-24','a test Sun Dec 24 00:20:09 EST 2006
===> 25',22),
(2,1,'0',NULL,NULL,NULL,NULL),
(3,NULL,'silviu',NULL,NULL,NULL,12);
/*!40000 ALTER TABLE `test` ENABLE KEYS */;

 
 
Index ‹ java-programmer