• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

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....
0
robinM
Asked:
robinM
  • 3
  • 2
1 Solution
 
kylarCommented:
If you're using primitives, I don't think the container will allow you to put a null into it, despite the databases willingness to accept it. Can you define a number (like -1) as a "unused" or null value, and check for that instead?

Kylar
0
 
robinMAuthor Commented:
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......
0
 
kylarCommented:
Well, you could hack around it by not using CMP and when the bean gets passed a '-1' then it records a null value in the table. Something like that?

Kylar
0
 
robinMAuthor Commented:
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
0
 
robinMAuthor Commented:
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("insert 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
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now