EXAMPLE JDBC RESOURCE CONFIGURATION FOR INGRES IN APACHE TOMCAT APPLICATION SERVER

coldwaterphewΔιακομιστές

17 Νοε 2013 (πριν από 3 χρόνια και 7 μήνες)

112 εμφανίσεις

EXAMPLE
JDBC RESOURCE
CONFIGURATION FOR
INGRES IN
APACHE TOMCAT

APPLICATION SERVER



I. INTRODUCTION


This article assumes that the reader is familiar with installing/us
ing TOMCAT
, an
open
source Jav
a EE Application Server
in

his/her

favorite OS. The prima
ry goal of this
documentation is to demonstr
ate how to configure
an
Ingres JDBC resource

in
TOMCAT
,
us
e

the JDBC resource name

i
n a simple JSP application,
deploy
the JSP
application in TOMCAT
, and

access the JSP

from a web browser
.


II. SOFTWARE USED:


A.

JAVA SDK: java version "1.5.0_14" (It should be JDK 1.5 or higher.)


Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_14
-
b03)


Java HotSpot(TM) Client VM (build 1.5.0_14
-
b03, mixed mode)

B. Ingres JDBC driver
: Ingres

Corporation
-

JDB
C Driver [3.2]

C.
Ingres
DBMS version: II 9.1.1
(int.w32
/00)

D.
TOMCAT

AS

version:
Apache Tomcat Version 6.0.16


Ja
r archive

downloaded:
apache
-
tomcat
-
6.0.16.zip

downloaded from



http://tomcat.apache.org/



III. TOMCAT

SERVER STARTUP/SHUTDOWN COMMANDS


The ‘
startup
.bat

,

shutdown.bat
’ uti
lities located at C:
\
<CATALINA_HOME
>
\
bin can
be
used to start/stop TOMCAT

application server
.


A.
Starting
the

Application Server:



Fig 1: Run ‘startup’ command


Fig 2:

Tomcat console window


B. Stopping
the

Application Server:



Fig 3
: Run ‘
shutdown
’ command



The ‘startup’ console window shown in Fig 2 will disappear.



IV. CONFIGURATION STEPS



Fig

4: Tomcat

installation directory and Ingres JDBC driver location



1
. Copy
the
In
gres JDB
C driver (iijdbc.jar) into
C:
\
apache
-
tomcat
-
6.0.16
\
lib

directory
.
Typically in an Ingres installati
on
,

this jar file is located in
%
II_SYSTEM
%
\
ingres
\
lib
directory. Also, it can be downloaded separately from
www.ingres.com
. This is just one
way of telling the application server’s classloader to load the Ingres JDBC driver. There
may be many other ways of doing this. Thi
s
may require restarting the TOMCAT

s
erver.

In the screen shot above it has been

shown that iijdbc.jar is

placed in the

lib


directory of
the TOMCAT

server
’s
‘default’

configuration
.


2. Change directory to
C:
\
apache
-
tomcat
-
6.0.16
\
conf
. Add the following Ingres JDBC
configuration

( should match the Ingres Test configuration that is
used)

to context.
xml
file.


context.xml:


<?xml version='1.0' encoding='utf
-
8'?>

<!
--


Licensed to the Apache Software Foundation (ASF) under one or more


contributor license agreements. See the NOTICE file distributed with


this work for additional in
formation regarding copyright ownership.


The ASF licenses this file to You under the Apache License, Version 2.0


(the "License"); you may not use this file except in compliance with


the License. You may obtain a copy of the License at



http://
www.apache.org/licenses/LICENSE
-
2.0



Unless required by applicable law or agreed to in writing, software


distributed under the License is distributed on an "AS IS" BASIS,


WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.


See
the License for the specific language governing permissions and


limitations under the License.

--
>

<!
--

The contents of this file will be loaded for each web application
--
>

<Context path="/ingres" docBase="ingres" debug="0"


reloadable="
true" >


<Logger className="org.apache.catalina.logger.FileLogger"


prefix="localhost_edbc_log." suffix=".txt"


timestamp="true"/>


<!
--

Default set of monitored resources
--
>


<WatchedResource>WEB
-
INF/we
b.xml</WatchedResource>




<!
--

Uncomment this to disable session persistence across Tomcat restarts
--
>


<!
--


<Manager pathname="" />


--
>



<!
--

Uncomment this to enable Comet connection tacking (provides events


on session expira
tion as well as webapp lifecycle)
--
>


<!
--


<Valve className="org.apache.catalina.valves.CometConnectionManagerValve" />


--
>


<!
--

Ingres Examples Context
--
>



<Resource name="jdbc/IngresDB"


auth="Container"



type="javax.sql.DataSource"


username="rajus01"


password="pwd"


url="jdbc:ingres://localhost:II7/ormdb;cursor=readonly;auto=multi"


driverClassName="com.ingres.jdbc.IngresDriver"


maxActi
ve="8"


maxIdle="4"


maxWait="60000"


validationQuery="select 1"/>



factory="org.apache.commons.dbcp.BasicDataSourceFactory"


<ResourceLink name="jdbc/IngresDB" global="jdbc/IngresDB" type="javax.sql.DataSourc
e" />


</Context>


The definitions of
Ingres specific
data source

properties such as
use
rName, password
are
provided below
.





userName

/
password



user ID and password. These values are not required if
connecting to Ingres on the same machine; otherwise,

user and password are
required and are validated against the operating system on the remote machine.
For other security options, see the Ingres documentation.


There are many other data source properties
for the Ingres JDBC driver, but two are

used
frequ
ently enou
gh to deserve special mention.


cursorMode
=readonly

This option generally improves response times by utilizing more block fetching of data
and reducing database locking. While this option is now the default for the Ingres 2006
driver and does no
t need to be specified with the current release, this value was not the
default in prior versions, including some initial releases of Ingres 2006. Since this option
is beneficial for new development, it doesn't hurt to always specify the option so there's

no doubt about whether it's on or not.


autocommitMode
=multi

Applications that expect to keep multiple cursors (selects) open when AUTOCOMMIT is
on will encounter errors such as “No MST in progress, trying to open additional cursor”.
If this error occurs
, set this attribute. When autocommit is on, Ingres only allows one
cursor (select) to be open at a time. Since autocommit is the default for JDBC, the
problem can easily occur. Some of the other databases do not have this behavior and
applications may

have been coded with that expectation. Setting this attribute causes the
Ingres JDBC driver to emulate the behavior, and thereby allow multiple cursors to be
open concurrently. Please see the Ingres JDBC driver documentation for details.


3
. Test the Ing
res JDBC resource name by using it in
a simple JSP application
(jndi.jsp)
.


<html>

<head>

<title>Access to Ingres server on host RAJUS01</title>

</head>

<body>

<%@ page language="java" contentType="text/html" import="java.sql.*"
import="javax.naming.*" im
port="javax.sql.*" import="com.ingres.jdbc.*"%>

<h1>Select table_name from iitables </h1>



<%


Connection conn = null;


Statement stmt = null;


ResultSet rs = null;


javax.sql.DataSource ds = null;



try


{


Context initCtx =
new InitialContext();


if( initCtx != null )


{


ds = (javax.sql.DataSource) initCtx.lookup("IngresDS");


}


}


catch(Exception e)


{


System.out.println("DataSource Context not found");


Sy
stem.out.println(e.toString());


throw new UnavailableException(this, "DataSource Context not found");


}




try


{


if( ds != null )


{


conn = ds.getConnection();




System.out.prin
tln("Got the connection successfully
\
n");



if( conn != null )


{


stmt = conn.createStatement();


rs = stmt.executeQuery("SELECT table_name from iitables");


}


}


}


catch(SQLException e)


{


System.out.println("An error occurs.");


System.out.println(e.toString());


throw new UnavailableException(this, "Cannot connect with the specified
database.");


}



%>


<p>Return result:</p>



<table bo
rder=1 cellpadding=2 cellspacing=0 width=500>

<% int countrows = 0; %>

<% while (rs.next()) { %>

<tr height="302">


<td height="16"><%= rs.getString(1) %></td>


<% countrows++; %>

</tr>

<% }


if( rs != null )


rs.close();


if( stmt !=

null )


stmt.close();


if( conn != null )


conn.close();


System.out.println("Successfully closed the Database connection.");

%>



</table>

<p><%=countrows%> row(s) found.</p>



</body>

</html>



For example, t
he above application
(jndi.jsp) can be

deployed in TOMCAT
server

by
doing the following steps.


a.

Create a directory called ‘ingres’ under
C:
\
apache
-
tomcat
-
6.0.16
\
webapps
\
.

b.

Create a directory called WEB
-
INF
, WEB
-
INF
\
lib in


C:
\
apache
-
tomcat
-
6.0.16
\
webapps
\
ingres d
irectory. Place iijdbc.jar in


C:
\
apache
-
tomcat
-
6.0.16
\
webapps
\
WEB
-
INF
\
lib. Place web.xml in


C:
\
apache
-
tomcat
-
6.0.16
\
webapps
\
WEB
-
INF.

c.

Create a directory ‘jsp’ in C
:
\
apache
-
tomcat
-
6.0.16
\
webapps
\
. Place the

above JSP file jndi
.jsp in C
:
\
apache
-
tomcat
-
6.0.16
\
webapps
\
jsp directory.


The directory structure looks like this for example.



Fig 5: Deploy jndi.jsp in Tomcat


Contents of web.xml:


<?xml version="1.0" encoding="ISO
-
8859
-
1"?>

<!
--


Licensed to the Apache Software Founda
tion (ASF) under one or more


contributor license agreements. See the NOTICE file distributed with


this work for additional information regarding copyright ownership.


The ASF licenses this file to You under the Apache License, Version 2.0


(the "Lic
ense"); you may not use this file except in compliance with


the License. You may obtain a copy of the License at



http://www.apache.org/licenses/LICENSE
-
2.0



Unless required by applicable law or agreed to in writing, software


distributed under

the License is distributed on an "AS IS" BASIS,


WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.


See the License for the specific language governing permissions and


limitations under the License.

--
>


<web
-
app xmlns="http://j
ava.sun.com/xml/ns/javaee"


xmlns:xsi="http://www.w3.org/2001/XMLSchema
-
instance"


xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web
-
app_2_5.xsd"


version="2.5">

</web
-
app>


The page can be accessed using th
e web browser as shown below

at the
URL shown in
the
browser
.

TOMCAT
AS

is listening for HTTP requests at port 8080

by default
.


The
following is sample output from the application.




Fig 6
: The JSP application output

The TOMCAT

console window will displ
ay the following lines while accessing the
simple JSP successfully using the Web browser.



Fig 7: Tomcat console o/p while accessing jndi.jsp from web browser


The same Ingres JDBC resource

nam
e can potentially be used in other

EJB
applications
needing a
ccess to
the
Ingres
DBMS server.



V. JDBC DRIVER TRACING


If any problems occur while trying to access data in an Ingres database, the Ingres JDBC
driver trace can be used to track
them
down.
For example, one of the ways to get JDBC
driver tracing is to

put the following entries in the iijdbc.properties file and include the
directory where this file is placed in the server classpath for loading by the server class
loader.


ing
res.jd
bc.trace.log=c:
\
\

apache
-
tomcat
-
6.0.16
\
\
jdbc_driv.log

ingres.jdbc.trace.
drv=5

#ingres.jdbc.trace.ds=<Datasource tracing level (0
-
5)>

ingres.jdbc.trace.msg=3

#ingres.jdbc.trace.msg.tl=<Transport I/O tracing level (0
-
5)>

#ingres.jdbc.trace.msg.nl=<Network I/O tracing level (0
-
5)>

#ingres.jdbc.trace.timestamp=<Include Timstamp in

tracing (true)>

#ingres.jdbc.dbms.trace.log=<Path and file name for DBMS trace>


As shown above
,

s
ome of the trace entries
have been commented out
to limit the tracing
.
In this case,
the iijdbc.properties file
was placed
in
to

C:
\
apache
-
tomcat
-
6.0.16

and
this
directory name was inc
luded in the CLASSPATH that gets set in ‘catalina
.bat’ startup
command.



set CLASSPATH=%CATALINA_HOME%;%CLASSPATH%


VI. CONCLUSION


This article is
intended

to give the Ingres community
a
jumpstart
in
developing middle
ti
er Java applications

by providing a clear example on

how t
o configure si
mple Ingres
data source in TOMCAT

Open Source Application Server.

In case of any issues or
problems using the I
ngres JDBC driver with TOMCAT
, please use the forums at
http://community.ingres.com/forums/index.php?c=3

for help and answers to questions.
In case of a licensed Ingres customer, please
open a

support issue.



VII. REFERENCES


1.
http://tomcat.apache.org/tomcat
-
6.0
-
doc/index.html