Placed At : BLR
Gitanjali Singh@SATYAM
12/20/2000 03:28 PM
hi all,
We all know that by default, the CMP EJB connects to the Hypersonic SQL
database, which is the default database provided with Orion. Here are some
examples that I tried to connect to MS Access, Oracle and Cloudscape
(file-based database provided with Weblogic). Any suggestion or feedback is
welcome.
Connecting to Ms-Access:
Following are the steps to connect to Ms-Access:
1. Create a DSN, which is used in the url field of data source.
2. The Orion EJB container connects to the database through JDBC driver. The
properties for connecting to the database are supplied to the Orion server
through the [ORION-HOME]/config/data-sources.xml file.Therefore, to connect to
MsAccess create a data-source like given below and add it in data-sources.xml
file :
<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="MsAccess"
schema="database-schemas/ms-access.xml"
location="jdbc/MsAccessCoreDS"
xa-location="jdbc/xa/MsAccessXADS"
ejb-location="jdbc/MsAccessDS"
connection-driver="sun.jdbc.odbc.JdbcOdbcDriver"
username=""
password=""
url="jdbc:odbc:TestDSN"
inactivity-timeout="30"
/>
Important note: The syntax of the CREATE TABLE in Ms-Access and that given by
the Orion server do not match. Suppose an EJB field userID is a primary key. So
when Orion starts, it tries to create a table using the following syntax:
CREATE TABLE Sample (userID TEXT(255) Not NULL PRIMARY KEY, name TEXT(255) NULL,
address TEXT(255) NULL, age INTEGER)
But the syntax required by Ms-Access is like this:
CREATE TABLE Sample (userID TEXT(255) Not NULL CONSTRAINT myConstraint PRIMARY
KEY, name TEXT(255) NULL, address TEXT(255) NULL, age INTEGER)
Therefore, a change should be made in the [ORION-HOME]/config/database-schemas/
ms-access.xml file which is the database-schema for Ms-Access. Change the value
of primary-key attribute of the "database-schema" tag as:
<database-schema name="Microsoft Access" not-null="not null" null=""
primary-key="constraint myConstraint primary key">
By default, a data source does not use any schema. To use the schema for Access,
its name is included in the "schema" attribute of the "data-source" tag in the
data-sources.xml file.
3. To connect this data-source to the EJB, the data-source name has to be
specified in the Orion-specific ejb-jar file i.e. orion-ejb-jar.xml. If
container doesn't find this file in the .ear file, then is generated by the
container at deployment. In the <entity-deployment> tag of this file, change the
value of "data-source" attribute to MsAccessDS, which is the JNDI name of the
data source. For example:
<entity-deployment name="TestCmp.ejb.Sample" location="TestCmp.ejb.Sample"
wrapper="SampleHome_EntityHomeWrapper2"table="Sample"datasource="jdbc/MsAccessDS">
4. After making changes to these files, we need to restart the server. When
server starts, it
will auto-deploy the EJB and if the specified table does not exist in the
database, it will auto-create the table.
Connecting to Oracle:
We can connect to Oracle either by creating a DSN or using a thin driver. Here
is an example of connecting the EJB through the thin driver. The steps are as
follows:
1. Copy the Oracle driver classes in the lib directory of Orion and add them
in the classpath of server.
2. Add a data source for connection in the data-sources.xml file as:
<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="oracle"
location="jdbc/oracleDS"
xa-location="jdbc/xa/oracleXADS"
ejb-location="jdbc/oracleDS"
connection-driver="oracle.jdbc.driver.OracleDriver"
username="gita"
password="Gita"
url="jdbc:oracle:thin:@192.168.23.192:1526:TESTVEN"
inactivity-timeout="30"
/>
The format of url field is: "driver@hostname:port:database".
3. To attach this data source with EJB, we just change the value of
"data-source"
attribute to oracleDS in the orion-ejb-jar.xml file of the application.
Connecting to Cloudscape:
Cloudscape is a pure Java SQL DBMS, which comes integrated with the Weblogic
server. We can connect EJB to the Cloudscape database also. The steps are as
follows:
1. Include the Cloudscape driver cloudscape.jar in the lib directory of Orion.
2. Include the cloudscape's demo database in the Orion directory.
3. Create a data-source for connection and append in data-sources.xml file as:
<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="Cloudscape"
location="jdbc/CloudscapeCoreDS"
xa-location="jdbc/xa/CloudscapeXADS"
ejb-location="jdbc/CloudscapeDS"
connection-driver="COM.cloudscape.core.JDBCDriver"
username=""
password=""
url="jdbc:cloudscape:demo"
inactivity-timeout="30"
/>
4. To use this data-source, change the value of "data-source" attribute to
CloudscapeDS in the orion-ejb-jar.xml file of the application.
References:
1. http://www.orionsupport.com/articles/jdbc.html
2. http://www.orionsupport.com/articles/multipleapps.html