robinM
asked on
Entity Beans using CMP and nullability of database table fields.
If I have an entity bean field modelled as an int, how can the the database be passed a null value for this field using CMP.
Eg. If the employeeID field in the database table allows nulls, and I have the employeeID field modelled as an int in my entity bean, how is it possible to assign the table column a null value? Obviously, I cannot assign null to an int in Java.
Any ideas?
We're using allaire Jrun 3.0 if that has any implications....
Eg. If the employeeID field in the database table allows nulls, and I have the employeeID field modelled as an int in my entity bean, how is it possible to assign the table column a null value? Obviously, I cannot assign null to an int in Java.
Any ideas?
We're using allaire Jrun 3.0 if that has any implications....
ASKER
Yeah, I had kind of thought of that already, but referential integrity requires that to be a valid primary key in another table, which would make the data held non-valid.
I was hoping there was some way round it......
I was hoping there was some way round it......
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yeah. But BMP is such a hassle to achieve what should be possible (in my opinion) with CMP. If nobody comes back with an alternative, I'll award your points. Thanks for your help Kylar
ASKER
Thanks for your help. The final solution was two fold. For BMP i used the following piece of code.
con = this.getConnection();
ps = con.prepareStatement("inse rt into MBORMUser (cSession, aSession, userTypeKey, ormAccountKey, userName, password, passwordHint, sessionCreationFirstScreen , validateTimeOut, startDate, statusDate, status) " +
"values (?,?,?,?,?,?,?,?,?,?,?,?)" );
ps.setInt(1, cSession);
ps.setInt(2, aSession);
ps.setInt(3, userTypeKey);
ps.setNull(4, java.sql.Types.INTEGER); // on creation, the ormAccountKey should always be null.
ps.setString(5, userName);
ps.setString(6, password);
ps.setString(7, passwordHint);
ps.setString(8, sessionCreationFirstScreen );
ps.setInt(9, validateTimeOut);
ps.setTimestamp(10, startDate);
ps.setTimestamp(11, statusDate);
ps.setString(12, status);
Note the ps.setNull() method call, and when using CMP I used the method you suggested that called a stored procedure which amends a value less than 1 to a NULL.....
-- Stored Procedure for creating a new session
-- used in EJB method : createSQL
CREATE PROCEDURE spr_ins_session
@MBORMUserKey int,
@termUser int,
@startDate datetime,
@statusDate datetime,
@status varchar(20),
@lastValidate datetime,
@sessionKey int OUTPUT
AS
BEGIN
IF @termUser < 1
SELECT @termUser = NULL
INSERT INTO session(MBORMUserKey, termUser, startDate, statusDate, status, lastValidate)
VALUES (@MBORMUserKey, @termUser, @startDate, @statusDate, @status, @lastValidate)
SELECT @sessionKey = MAX(sessionKey) FROM session
END
GO
Thanks for your help
con = this.getConnection();
ps = con.prepareStatement("inse
"values (?,?,?,?,?,?,?,?,?,?,?,?)"
ps.setInt(1, cSession);
ps.setInt(2, aSession);
ps.setInt(3, userTypeKey);
ps.setNull(4, java.sql.Types.INTEGER); // on creation, the ormAccountKey should always be null.
ps.setString(5, userName);
ps.setString(6, password);
ps.setString(7, passwordHint);
ps.setString(8, sessionCreationFirstScreen
ps.setInt(9, validateTimeOut);
ps.setTimestamp(10, startDate);
ps.setTimestamp(11, statusDate);
ps.setString(12, status);
Note the ps.setNull() method call, and when using CMP I used the method you suggested that called a stored procedure which amends a value less than 1 to a NULL.....
-- Stored Procedure for creating a new session
-- used in EJB method : createSQL
CREATE PROCEDURE spr_ins_session
@MBORMUserKey int,
@termUser int,
@startDate datetime,
@statusDate datetime,
@status varchar(20),
@lastValidate datetime,
@sessionKey int OUTPUT
AS
BEGIN
IF @termUser < 1
SELECT @termUser = NULL
INSERT INTO session(MBORMUserKey, termUser, startDate, statusDate, status, lastValidate)
VALUES (@MBORMUserKey, @termUser, @startDate, @statusDate, @status, @lastValidate)
SELECT @sessionKey = MAX(sessionKey) FROM session
END
GO
Thanks for your help
Kylar