Hi there,
SQL-Servers a pain with identities where with Oracle its a lot simpler. For
SQL I dont use them.
I solved this by writing a generic Session bean called IdentityGenerator
with a method nextVal().
I then get the nextID() based on SQLStatement / s in the env tag of the
beans deployment descriptor.
On SQL, I am unfotunately unable to insert into the target SQLTable to get
the @@IDENTITY and reading the last row is too risky, So I use another table
that has a field for entity and lastID.
The CMP bean that needs the nextId() calls this session bean in its
ejbCreate(parms...) method.
The Spec specifies that you should initialize the state of the bean and its
key in the ejbCreate() method
so this is okay.
Here's the code, hope it helps.
=============The remote Interface===============
/**
* Class that provides an Identity Service.
*/
public interface IdentityService extends EJBObject{
/**
* Returns the Next Identity value based on the ref passed in.
*/
Long nextVal(String ref) throws RemoteException;
}
================= The Implementing Session Bean ==============
/**
* Class that Implements the Identity Service Session
* The identity Service is based on environment variables setup
* in the deployment descriptor.
*
* This beans Transaction descriptor should be set to requires_new.
* This allows the underlying Transaction Table's row to be locked for
* a small period of time.
*
* For each Object that uses this service the Follwing entries are
* required in the deployment descriptor.
* ref is the paramater passed to nextVal.
*
* 1.<env-entry>"IDQuery/" + ref</env-entry> should contain the
* SQL Statement used to generate the Id.
*
* 2.<env-entry>"db/" + ref</env-entry> should contain the resource
* ref used to get Hold of the jdbc resource manager.
*
* 3.<resource-ref>the refered to resource in point 2 </resource-ref>
*
* EG Deployment Descriptor
* This deployment descriptor sets up this Identity Service
* for a Customer and an Account Entity Bean.
* The Customer Entity Bean resides in a database that does not
* Support sequences, such as MSSQLServer.
* The Account Entity Bean sits in an Oracle Database.
*
* <env-entry>
* <description>
* The SQL statement to execute that returns the result for the
* Id for Customer
* This example uses a SQL database table called GenIdentities
* that has 2 columns
* an entity column which is the primary key.
* an Id column which contains the last used ID.
* The Database is obtained by using the env entry jdbc/Customer
* </description>
* <env-entry-name>IDQuery/Customer</env-entry-name>
* <env-entry-type>java.lang.String</env-entry-type>
* <env-entry-value>
* Update Sequences set lastId=lastID + 1 where SequenceName =
'Customer'
* Select lastID from Sequences where SequenceName = 'Customer'
* </env-entry-value>
* </env-entry>
* <env-entry>
* <description>
* This entry contains the Jndi name of the jdbc resource manager
* that is used to generate a customer Id.
* </description>
* <env-entry-name>db/Customer</env-entry-name>
* <env-entry-type>java.lang.String</env-entry-type>
* <env-entry-value>jdbc/SalesDB</env-entry-value>
* </env-entry>
*
* <env-entry>
* <description>
* The SQL statement to execute that returns the result for an
* Id for Account, this statement uses an Oracle Sequence.
* The Database is obtained by using the env entry jdbc/Account
* </description>
* <env-entry-name>IDQuery/Account</env-entry-name>
* <env-entry-type>java.lang.String</env-entry-type>
* <env-entry-value>
* Select ACCOUNTSEQ.NEXTVAL from DUAL
* </env-entry-value>
* </env-entry>
* <env-entry>
* <description>
* This entry contains the Jndi name of the jdbc resource manager
* that is used to generate an Account Id.
* </description>
* <env-entry-name>db/Account</env-entry-name>
* <env-entry-type>java.lang.String</env-entry-type>
* <env-entry-value>jdbc/FinanceDB</env-entry-value>
* </env-entry>
*
* <resource-ref>
* <description>
* A data source for the database in which the customer
* enterprise bean's indentity generators table will reside
* </description>
* <res-ref-name>jdbc/SalesDB</res-ref-name>
* <res-type>javax.sql.DataSource</res-type>
* <res-auth>Bean</res-auth>
* </resource-ref>
* <resource-ref>
* <description>
* A data source for the database in which the account
* enterprise bean's indentity sequence will reside
* </description>
* <res-ref-name>jdbc/FinanceDB</res-ref-name>
* <res-type>javax.sql.DataSource</res-type>
* <res-auth>Bean</res-auth>
* </resource-ref>
*
*/
public class IdentityServiceSession extends AbstractSessionBean{
public void ejbCreate() throws CreateException, EJBException,
java.rmi.RemoteException{
}
public Long nextVal(String ref) throws RemoteException{
String query = getRefQuery(ref);
Connection conn = null;
Statement statement = null;
ResultSet rs = null;
String errMsg = "Error generating ID for " + ref;
try{
conn = getConnection(ref);
statement = conn.createStatement();
rs = statement.executeQuery(query);
if ( !rs.next() )
throw new RemoteException(errMsg);
String strKey = rs.getString(1);
try { return new Long( strKey ); }
catch( NumberFormatException e ){
errMsg = "Unable to convert " + strKey + " to a Long";
throw new RemoteException( errMsg ) ;
}
}
catch( SQLException e ) {
throw new RemoteException(errMsg + "\n" + e.getMessage());
}
finally{
if (rs != null) try { rs.close(); } catch (Exception e){}
rs = null;
if (statement != null) try { statement.close(); } catch (Exception
e){}
statement = null;
if (conn != null) try { conn.close(); } catch (Exception e){}
conn = null;
}
}
public String getRefQuery(String ref) throws NoSuchObjectException{
CCHInitialContext context = getInitialContext();
String query = (String) context.lookup(
"java:comp/env/IDQuery/" + ref, String.class );
return query;
}
public Connection getConnection(String ref)
throws NoSuchObjectException, SQLException {
CCHInitialContext context = getInitialContext();
String dbref = (String)context.lookup(
"java:comp/env/db/" + ref, String.class );
DataSource ds = (DataSource)context.lookup(
"java:comp/env/" + dbref, DataSource.class);
Connection conn = ds.getConnection();
return conn;
}
}
=========Class that uses id service================
public class ShareTypeEntity extends AbstractEntityBean{
public Long ID;
public String description;
private transient IdentityServiceHome idServiceHome;
public Long getID() throws RemoteException{
return ID;
}
public void setID(Long newID){
ID = newID;
}
public String getDescription() throws RemoteException{
return description;
}
public void setDescription(String newDesc) throws RemoteException{
description = newDesc;
}
/**
* Dont Allow removes
*/
public void ejbRemove() throws RemoveException, EJBException,
java.rmi.RemoteException{
throw new RemoveException("Remove not supported");
}
public Long ejbCreate(String desc) throws RemoteException,
CreateException{
if ( desc == null ) throw new CreateException("Description Required");
if ( desc.trim().length() == 0 ) throw new CreateException("Description
Required");
System.err.println( "ShareType create called desc=" + desc);
description = desc;
ID = getNextId();
return null;
}
public void ejbPostCreate(String desc) throws RemoteException,
CreateException{
}
private Long getNextId() throws CreateException, EJBException,
RemoteException{
IdentityService idService = getIdentityServiceHome().create();
return idService.nextVal("ShareType");
}
private IdentityServiceHome getIdentityServiceHome() throws
RemoteException{
if ( idServiceHome == null) {
idServiceHome = (IdentityServiceHome)super.getHome(
"java:comp/env/ejb/IdentityService",IdentityServiceHome.class);
}
return idServiceHome;
}
}
Hope this helps, As you can see with the SQL descriptor I pass 2
statements to the database
to get the Updated value. As it is in its own transaction this should be
fine, providing the database can handle 2 statements.
Noel
----- Original Message -----
From: "Mike Cannon-Brookes" <[EMAIL PROTECTED]>
To: "Orion-Interest" <[EMAIL PROTECTED]>
Sent: Saturday, April 08, 2000 7:40 AM
Subject: RE: CMP and Identity columns
> Shayne,
>
> As far as I know you cannot use identity (or autonumber fields) with EJBs.
> You have to create the primary key within Java.
>
> Anyone else?
>
> Mike
>
> > -----Original Message-----
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED]]On Behalf Of Shayne Hughes
> > Sent: Saturday, 8 April 2000 9:38
> > To: Orion-Interest
> > Subject: CMP and Identity columns
> >
> >
> > I have a table on Microsoft SQL Server that has an identity column such
> > as...
> >
> > CREATE TABLE MyTable (
> > id int IDENTITY (1, 1) PRIMARY KEY NOT NULL ,
> > Name varchar (255) NULL
> > )
> >
> > I'd like to create a CMP EJB for this table. Is it possible to instruct
> > Orion to not specify a value for the "id" field when inserting a
> > new row and
> > to execute the proper SQL command to get back the "id" of the new row?
> >
> > My classes only have create methods that specify "Name". Here's
> > the message
> > from the exception that occurs...
> >
> > Error creating EntityBean: [ECOLI]Cannot insert explicit value
> > for identity
> > column in table 'GelGroup' when IDENTITY_INSERT is set to OFF.
> >
> > I hope this is on topic. My reseach points to this being a
> > container config
> > thing.
> >
> > Shayne
> >
> >
> >
> >
>
>
