|
Mr. Peter Norton : Speaks about Configuring Oracle
|
If u wish to use Oracle the following things are must and should.
Step 1. classes12.zip which suits your version of database must be placed in some folder
say for example D:\oracle\ora81\jdbc\lib\classes12.zip , then the classpath in your env variables
but be something like
Variable Name : CLASSPATH ::
value : C:\j2sdkee1.3.1\lib\ejb10deployment.jar;C:\j2sdkee1.3.1\lib\j2ee.jar;
C:\j2sdkee1.3.1\lib\j2ee-ri-svc.jar;C:\j2sdkee1.3.1\lib\j2eetools.jar;
C:\j2sdkee1.3.1\lib\jhall.jar;D:\oracle\ora81\jdbc\lib\classes12.zip
Step 2. Edit userconfig.bat in C:\j2sdkee1.3.1\bin Directory and add this
line at Bottom
SET J2EE_CLASSPATH=D:\oracle\ora81\jdbc\lib\classes12.zip;
Step 3. Setting up TNSNAMES
Re-Start Your System after u make change
|
|
TNSNAMES [To be used Only if u r using Oracle DB] : D:\oracle\ora81\network\ADMIN\tnsnames.ora
|
This is one of the most important files your Entry must look something like this
But PLEASE CONSULT YOUR ORACLE DBA while making changes in this file
EntryName =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle_database_ipaddress)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = service_name)
)
)
In the Deploytool Click on Tools -> Server Configuration -> Standard Data Sources Menu Item
and setup our Oracle Driver Class and JNDI Name and JDBC URL
Click on Add Button Next to JDBC Drivers type oracle.jdbc.driver.OracleDriver
Similarly click add button next to DataSource and Add JNDI name as
jdbc/Data and the corresponding JDBC URL as jdbc:oracle:thin:@dbserver_ipaddress:1521:sid
Observe the lines highligted and Talk to Your DBA about the JDBC URL part
Highlight the WAR File on left pane (ignore the current reading on left pane right now),
click on the Resource Refs Tab on right pane , give the
JNDI Name , Authentication Container , make it Sharable, DB username and Password.
|
|
Start Cloudscape Inbuilt with J2EE 1.3.1 database
|
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\james>cd\
C:\>cloudscape -start
Sun Jan 16 10:17:28 2005: [RmiJdbc] Starting Cloudscape RmiJdbc Server Versi
on 1.7.2 ...
Sun Jan 16 10:17:32 2005: [RmiJdbc] COM.cloudscape.core.JDBCDriver registere
d in DriverManager
Sun Jan 16 10:17:32 2005: [RmiJdbc] Binding RmiJdbcServer...
Sun Jan 16 10:17:32 2005: [RmiJdbc] No installation of RMI Security Manager.
..
Sun Jan 16 10:17:32 2005: [RmiJdbc] RmiJdbcServer bound in rmi registry
|
|
Open isql prompt where we can create Our tables & do SQL work , similar to SQL*Plus of Oracle
|
We will create a table named custdata under the default DB provided by cloudscape
custdata will have customer id, customer name, customer phone number , customer plan
by now u must have understood this is got something to do with calculation of our
telephone bills. telephone service provider will provide many services or plans
to its subscribes each having its own business logic and tariffs , which we can deal in depth
later, customer plan is related to the id of the plan the customer has subscribed , we assume that
1 customer can have just 1 plan .
There will be one more table named plans which will store planid and the corresponding
name for that plan. Our Telecom company offers 4 plans. each have their own value , which will
be discussed later.
friends lemme tell u this db is not as good as MySQL , cause i cant see show tables; show databases;
working , dont know whats the syntax in this cloudscape . anyway let us work with this
cause i cant offord to run oracle and j2ee on same computer.
my system might die due to load 
|
C:\>cloudscape -isql
ij version 4.0 (c) 1997-2001 Informix Software, Inc.
WARNING 01J01: Database 'CloudscapeDB' not created, connection made to existing
database instead.
CONNECTION0* - jdbc:cloudscape:CloudscapeDB;create=true
* = current connection
ij> create table custdata ( custid int, custname varchar (100), custph varchar (20), custplan int);
ij> insert into custdata values (1, 'James Smith', '123456', 1);
ij> insert into custdata values (2, 'Peter', '123455', 2);
ij> insert into custdata values (3, 'Mike', '123454', 4);
ij> insert into custdata values (4, 'Johan', '123434', 3);
ij> insert into custdata values (5, 'Andrew', '3345545', 1);
ij> select * from custdata;
CUSTID |CUSTNAME
|CUSTPH |CUSTPLAN
-------------------------------------------------------------------------------
--------------------------------------------------------------------------
1 |James Smith
|123456 |1
2 |Peter
|123455 |2
3 |Mike
|123454 |4
4 |Johan
|123434 |3
5 |Andrew
|3345545 |1
5 rows selected
ij> create table plans (planid int, plan_details varchar(100));
0 rows inserted/updated/deleted
ij> insert into plans values (1, 'General');
1 row inserted/updated/deleted
ij> insert into plans values (2, 'Economy');
1 row inserted/updated/deleted
ij> insert into plans values (3, 'Special');
1 row inserted/updated/deleted
ij> insert into plans values (4, 'Super');
1 row inserted/updated/deleted
ij> select * from plans;
PLANID |PLAN_DETAILS
----------------------------------------------------------
------------------------------------
1 |General
2 |Economy
3 |Special
4 |Super
4 rows selected
ij>
|
|
I think i will take leave now , over worked today. need to take pics of a celeb who is having
fun in private yatch with her new found frnd 15 yrs younger to her .. these pics can be sold
for more than working life time as DBA  .
Mike pls dont shut down the Cloudscape server
the command is cloudscape -stop . I will let Mr. Hen Fisherman
take over . as he will do the front end to connect to db and select records .. and test db connection
i am off .....
|
|
|
Hi Folks, now let us write a JSP to connect to db and fetch records . let us keep it simple
no beans or ejb a this stage . as we are yet to plan and our EJB guy is still searching his
lost wife.. , err.. also searching other's wifes... incase he can get one
|
|
|
|
Database Accessing Process
|
Write the code as show below and save it in e:\phone\jsp dir as checkdb.jsp
<HTML>
<HEAD>
<TITLE>
James Smith's Java : james_smith73@yahoo.com
</TITLE>
</HEAD>
<body bgcolor="#FFFFFF" text="#800000">
<%@ page import="java.sql.*" %>
<%@ page import="java.lang.*" %>
<%@ page import="java.util.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<%
//Select Query
String query = "select * from custdata order by custid asc";
Statement stmt = null;
Connection mycon = null;
//Connect to DB using JNDI lookup ------------------
try
{
InitialContext ic = new InitialContext();
mycon = (( DataSource )ic.lookup("jdbc/Cloudscape")).getConnection();
}
catch(Exception e )
{
e.printStackTrace();
}
//Get the Resultset ----------------------------------
stmt = mycon.createStatement();
ResultSet rs = stmt.executeQuery(query);
String CustID="";
String CustName="";
String CustPlan="";
String CustPh="";
%>
<table border=1 >
<tr bgcolor="#800000">
<td><font color=white>CustId</font></td>
<td><font color=white>CustName</font></td>
<td><font color=white>CustPlan</font></td>
<td><font color=white>CustPhone</font></td>
</tr>
<%
while(rs.next())
{
CustID = rs.getString(1);
CustName = rs.getString(2);
CustPlan = rs.getString(3);
CustPh = rs.getString(4);
%>
<tr>
<td><%=CustID%></td>
<td><%=CustName%></td>
<td><%=CustPlan%></td>
<td><%=CustPh%></td>
</tr>
<%
}
%> </table><%
// Close everything -------------------------
rs.close();
stmt.close();
mycon.close();
%>
</body>
</HTML>
|
Start ur j2ee server , deploytool and cloudscape db incase its already not started .
Click on PhoneWAR on left pane of deploytool. on the right pane u can see just
1 welcome.jsp . click on the edit button next to it marked in red.
Observe the red marking drag n drop the checkdb.jsp from e:\phone\jsp
into Contents of PhoneWAR click OK button below
Now u can see that PhoneWAR had new jsp added marked in red
click on tools server congiguration menu item . observe that cloudscape driver class is added
and our JNDI name jdbc/Cloudscape is already defined with its own JDBC URL
For Oracle users , refer the figure above .. as how to add driver class and JDBC URL . this varies
from one database vendor to other .. please refer the manuals given by DB Vendors
click on resource refs tab on right pane, here u define the coded name , to put in simple words
the JNDI mapping u will be using for this WAR file. and database username and password. right now
dbusername and password is blank for Cloudscape.. . for Oracle consult ur DBA
Click on Tools => Deploy as u have added a new JSP file . Click OK ,
Done and Over ..........
|
|
Final Output in Browser from Cloudscape Database
|
In the browser type in http://localhost:8000/WelcomeRoot/jsp/checkdb.jsp
|
|
What Next => Available : YES
|
Next Lesson will be given entire team how to Plan a System and Architectural design..
You have to mail me your ur Feedback its very important for me
to write more and keep interest on this chapter at james_smith73@yahoo.com
Join the group to be updated in case u are not the group member.
|
|
Reach me!
|
|
|
Java, J2EE, J2SE and all Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc.
in the United States and other countries. 
|
If you like this article and/or code 