Description:
I'm using this method
public void java.sql.CallableStatement.setNull(int parameterIndex, int sqlType)
in an attempt to set the value in a column to NULL. The columns in question allow NULL.
The problem is...
VARCHAR EXAMPLE: If the type of the column is a VARCHAR, it doesn't report an error but instead puts the String "null" in there instead of the NULL value.
INTEGER EXAMPLE: If the type of the column is an INT, it reports the following error...
java.sql.SQLException: Incorrect integer value: 'null' for column 'technologyIdIN' at row 1
(Where 'technologyIdIN' is the IN parameter I'm trying to set NULL).
I found another person in the Forum who ran into what seems to be the same issue...
"CallableStatement.setNull(String, java.sql.Types) doesn't work"
http://forums.mysql.com/read.php?39,63506,63506#msg-63506
That person suggested that these might be a work-around...
setObject(String, null)
&
setObject(String, null, java.sql.Types.CHAR)
But when I tried these, I end up with a different problem which I'm not sure how to solve (yet)....
java.sql.SQLException: No access to parameters by name when connection has been configured not to access procedure bodies
How to repeat:
Windows XP.
mysql Ver 14.12 Distrib 5.0.37, for Win32 (ia32)
mysql-connector-java-5.0.5-bin.jar
java version "1.4.2_12"
--------------------------------------------------------
This is the table I'm using....
--------------------------------------------------------
CREATE TABLE Device_Type
(
Device_Type_Id INT NOT NULL,
Technology_Type_Id INT,
Image_URL VARCHAR(100),
PRIMARY KEY(Device_Type_Id)
) ENGINE=InnoDB;
--------------------------------------------------------
--------------------------------------------------------
This is the PROC I'm using for the VARCHAR EXAMPLE...
--------------------------------------------------------
DROP PROCEDURE IF EXISTS set_url_in_device_type;
DELIMITER $$
CREATE PROCEDURE set_url_in_device_type (
IN deviceTypeIdIN INT,
IN imageUrlIN VARCHAR(100)
)
BEGIN
update device_type
set image_url = imageUrlIN
where device_type_id = deviceTypeIdIN;
END;
$$
DELIMITER ;
--------------------------------------------------------
--------------------------------------------------------
This is the PROC I'm using for the INTEGER EXAMPLE...
--------------------------------------------------------
DROP PROCEDURE IF EXISTS set_tech_in_device_type;
DELIMITER $$
CREATE PROCEDURE set_tech_in_device_type (
IN deviceTypeIdIN INT,
IN technologyIdIN INT
)
BEGIN
update device_type
set Technology_Type_Id = technologyIdIN
where device_type_id = deviceTypeIdIN;
END;
$$
DELIMITER ;
--------------------------------------------------------
I begin by inserting a single record in the table...
INSERT INTO Device_Type (Device_Type_Id,Technology_Type_Id,Image_URL)
VALUES(100,1,"initial insert");
And this is the java I'm using....
-------------------
VARCHAR EXAMPLE
-------------------
//get a connection to your database...
//Connection con = ...
CallableStatement cs = con.prepareCall("{call set_url_in_device_type(?,?)}");
cs.setInt(1,100);
cs.setNull(2,Types.VARCHAR);
cs.executeUpdate();
PreparedStatement stmt1 = con.prepareStatement(select Device_Type_Id, Image_URL, Technology_Type_Id from Device_Type);
ResultSet rs1 = stmt1.executeQuery();
while(rs1.next()) {
int tid = rs1.getInt(1);
String url = rs1.getString(2);
url = (url==null)?"[NULL]":url;
if(rs1.wasNull()) {
url+="-WASNULL";
}
int ttid = rs1.getInt(3);
System.out.println(
tid + ", " +
url + ", " + // the string null gets printed
ttid + ", " +
descName);
}
-------------------
UNEXPECTED RESULT: for url, the string 'null' gets printed while I'm expecting '[NULL]-WASNULL'
-------------------
INTEGER EXAMPLE
-------------------
//get a connection to your database...
//Connection con = ...
CallableStatement cs = con.prepareCall("{call set_tech_in_device_type(?,?)}");
cs.setInt(1,100);
cs.setNull(2,Types.INTEGER);
cs.executeUpdate();
...
this results in
java.sql.SQLException: Incorrect integer value: 'null' for column 'technologyIdIN' at row 1
-------------------
Description: I'm using this method public void java.sql.CallableStatement.setNull(int parameterIndex, int sqlType) in an attempt to set the value in a column to NULL. The columns in question allow NULL. The problem is... VARCHAR EXAMPLE: If the type of the column is a VARCHAR, it doesn't report an error but instead puts the String "null" in there instead of the NULL value. INTEGER EXAMPLE: If the type of the column is an INT, it reports the following error... java.sql.SQLException: Incorrect integer value: 'null' for column 'technologyIdIN' at row 1 (Where 'technologyIdIN' is the IN parameter I'm trying to set NULL). I found another person in the Forum who ran into what seems to be the same issue... "CallableStatement.setNull(String, java.sql.Types) doesn't work" http://forums.mysql.com/read.php?39,63506,63506#msg-63506 That person suggested that these might be a work-around... setObject(String, null) & setObject(String, null, java.sql.Types.CHAR) But when I tried these, I end up with a different problem which I'm not sure how to solve (yet).... java.sql.SQLException: No access to parameters by name when connection has been configured not to access procedure bodies How to repeat: Windows XP. mysql Ver 14.12 Distrib 5.0.37, for Win32 (ia32) mysql-connector-java-5.0.5-bin.jar java version "1.4.2_12" -------------------------------------------------------- This is the table I'm using.... -------------------------------------------------------- CREATE TABLE Device_Type ( Device_Type_Id INT NOT NULL, Technology_Type_Id INT, Image_URL VARCHAR(100), PRIMARY KEY(Device_Type_Id) ) ENGINE=InnoDB; -------------------------------------------------------- -------------------------------------------------------- This is the PROC I'm using for the VARCHAR EXAMPLE... -------------------------------------------------------- DROP PROCEDURE IF EXISTS set_url_in_device_type; DELIMITER $$ CREATE PROCEDURE set_url_in_device_type ( IN deviceTypeIdIN INT, IN imageUrlIN VARCHAR(100) ) BEGIN update device_type set image_url = imageUrlIN where device_type_id = deviceTypeIdIN; END; $$ DELIMITER ; -------------------------------------------------------- -------------------------------------------------------- This is the PROC I'm using for the INTEGER EXAMPLE... -------------------------------------------------------- DROP PROCEDURE IF EXISTS set_tech_in_device_type; DELIMITER $$ CREATE PROCEDURE set_tech_in_device_type ( IN deviceTypeIdIN INT, IN technologyIdIN INT ) BEGIN update device_type set Technology_Type_Id = technologyIdIN where device_type_id = deviceTypeIdIN; END; $$ DELIMITER ; -------------------------------------------------------- I begin by inserting a single record in the table... INSERT INTO Device_Type (Device_Type_Id,Technology_Type_Id,Image_URL) VALUES(100,1,"initial insert"); And this is the java I'm using.... ------------------- VARCHAR EXAMPLE ------------------- //get a connection to your database... //Connection con = ... CallableStatement cs = con.prepareCall("{call set_url_in_device_type(?,?)}"); cs.setInt(1,100); cs.setNull(2,Types.VARCHAR); cs.executeUpdate(); PreparedStatement stmt1 = con.prepareStatement(select Device_Type_Id, Image_URL, Technology_Type_Id from Device_Type); ResultSet rs1 = stmt1.executeQuery(); while(rs1.next()) { int tid = rs1.getInt(1); String url = rs1.getString(2); url = (url==null)?"[NULL]":url; if(rs1.wasNull()) { url+="-WASNULL"; } int ttid = rs1.getInt(3); System.out.println( tid + ", " + url + ", " + // the string null gets printed ttid + ", " + descName); } ------------------- UNEXPECTED RESULT: for url, the string 'null' gets printed while I'm expecting '[NULL]-WASNULL' ------------------- INTEGER EXAMPLE ------------------- //get a connection to your database... //Connection con = ... CallableStatement cs = con.prepareCall("{call set_tech_in_device_type(?,?)}"); cs.setInt(1,100); cs.setNull(2,Types.INTEGER); cs.executeUpdate(); ... this results in java.sql.SQLException: Incorrect integer value: 'null' for column 'technologyIdIN' at row 1 -------------------