Value gets changed upon comiting. | CallableStatements
- by Triztian
Hello, I having a weird problem with a DAO class and a StoredProcedure, what is happening is that I use a CallableStatement object which takes 15 IN parameters, the value of the field id_color is retrieved correctly from the HTML forms it even is set up how it should in the CallableStatement setter methods, but the moment it is sent to the database the id_color is overwriten by the value 3 here's the "context":
I have the following class DAO.CoverDAO which handles the CRUD operations of this table
CREATE TABLE `cover_details` (
`refno` int(10) unsigned NOT NULL AUTO_INCREMENT,
`shape` tinyint(3) unsigned NOT NULL ,
`id_color` tinyint(3) unsigned NOT NULL ',
`reversefold` bit(1) NOT NULL DEFAULT b'0' ,
`x` decimal(6,3) unsigned NOT NULL ,
`y` decimal(6,3) unsigned NOT NULL DEFAULT '0.000',
`typecut` varchar(10) NOT NULL,
`cornershape` varchar(20) NOT NULL,
`z` decimal(6,3) unsigned DEFAULT '0.000' ,
`othercornerradius` decimal(6,3) unsigned DEFAULT '0.000'',
`skirt` decimal(5,3) unsigned NOT NULL DEFAULT '7.000',
`foamTaper` varchar(3) NOT NULL,
`foamDensity` decimal(2,1) unsigned NOT NULL ,
`straplocation` char(1) NOT NULL ',
`straplength` decimal(6,3) unsigned NOT NULL,
`strapinset` decimal(6,3) unsigned NOT NULL,
`spayear` varchar(20) DEFAULT 'Not Specified',
`spamake` varchar(20) DEFAULT 'Not Specified',
`spabrand` varchar(20) DEFAULT 'Not Specified',
PRIMARY KEY (`refno`)
) ENGINE=MyISAM AUTO_INCREMENT=143 DEFAULT CHARSET=latin1 $$
The the way covers are being inserted is by a stored procedure, which is the following:
CREATE DEFINER=`root`@`%` PROCEDURE `putCover`(
IN shape TINYINT,
IN color TINYINT(3),
IN reverse_fold BIT,
IN x DECIMAL(6,3),
IN y DECIMAL(6,3),
IN type_cut VARCHAR(10),
IN corner_shape VARCHAR(10),
IN cutsize DECIMAL(6,3),
IN corner_radius DECIMAL(6,3),
IN skirt DECIMAL(5,3),
IN foam_taper VARCHAR(7),
IN foam_density DECIMAL(2,1),
IN strap_location CHAR(1),
IN strap_length DECIMAL(6,3),
IN strap_inset DECIMAL(6,3)
)
BEGIN
INSERT INTO `dbre`.`cover_details`
(`dbre`.`cover_details`.`shape`,
`dbre`.`cover_details`.`id_color`,
`dbre`.`cover_details`.`reversefold`,
`dbre`.`cover_details`.`x`,
`dbre`.`cover_details`.`y`,
`dbre`.`cover_details`.`typecut`,
`dbre`.`cover_details`.`cornershape`,
`dbre`.`cover_details`.`z`,
`dbre`.`cover_details`.`othercornerradius`,
`dbre`.`cover_details`.`skirt`,
`dbre`.`cover_details`.`foamTaper`,
`dbre`.`cover_details`.`foamDensity`,
`dbre`.`cover_details`.`strapLocation`,
`dbre`.`cover_details`.`strapInset`,
`dbre`.`cover_details`.`strapLength`
)
VALUES
(shape,color,reverse_fold,
x,y,type_cut,corner_shape,
cutsize,corner_radius,skirt,foam_taper,foam_density,
strap_location,strap_inset,strap_length);
END
As you can see basically it just fills each field, now, the CoverDAO.create(CoverDTO cover) method which creates the cover is like so:
public void create(CoverDTO cover) throws DAOException {
Connection link = null;
CallableStatement query = null;
try {
link = MySQL.getConnection();
link.setAutoCommit(false);
query = link.prepareCall("{CALL putCover(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
query.setLong(1,cover.getShape());
query.setInt(2,cover.getColor());
query.setBoolean(3, cover.getReverseFold());
query.setBigDecimal(4,cover.getX());
query.setBigDecimal(5,cover.getY());
query.setString(6,cover.getTypeCut());
query.setString(7,cover.getCornerShape());
query.setBigDecimal(8, cover.getZ());
query.setBigDecimal(9, cover.getCornerRadius());
query.setBigDecimal(10, cover.getSkirt());
query.setString(11, cover.getFoamTaper());
query.setBigDecimal(12, cover.getFoamDensity());
query.setString(13, cover.getStrapLocation());
query.setBigDecimal(14, cover.getStrapLength());
query.setBigDecimal(15, cover.getStrapInset());
query.executeUpdate();
link.commit();
} catch (SQLException e) {
throw new DAOException(e);
} finally {
close(link, query);
}
}
The CoverDTO is made of accessesor methods, the MySQL object basically returns the connection from a pool.
Here is the pset Query with dummy but appropriate data:
putCover(1,10,0,80.000,80.000,'F','Cut',0.000,0,15.000,'4x2',1.5,'A',10.000,5.000)
As you can see everything is fine just when I write to the DB instead of 10 in the second parameter a 3 is written. I have done the following:
Traced the id_color value to the create method, still got replaced by a 3
Hardcoded the value in the DAO create method, still got replaced by a 3
Called the procedure from the MySQL Workbench, it worked fined so I assume something is happening in the create method, any help is really appreciated.
