EXAMPLE JDBC RESOURCE CONFIGURATION FOR INGRES IN GLASSFISH APPLICATION SERVER

makeshiftklipInternet και Εφαρμογές Web

31 Οκτ 2013 (πριν από 3 χρόνια και 1 μήνα)

408 εμφανίσεις

EXAMPLE
JDBC RESOURCE
CONFIGURATION FOR
INGRES IN
GLASSFISH APPLICATION SERVER



I. INTRODUCTION


This article assumes that the reader is familiar with installing/us
ing GLASSFISH, an
open source Java EE Application Server from Sun (a
.
k
.
a Sun
Java System Ap
plication
Server)

in his/her

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

in Glassfish
,
us
e

the JDBC resource name

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

a
ccess 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, mi
xed mode)

B. Ingres JDBC driver
: Ingres

Corporation
-

JDBC Driver [3.2]

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

D.
Glassfish

version: Glassfish V2 UR1


Jar archive downloaded: glassfish
-
installer
-
v2ur1
-
b09d
-
windows.jar


III. GLASSFISH ADMIN SERVE
R STARTUP/SHUTDOWN COMMANDS


The ‘asadmin’ utility is used to perform several administrative tasks on the Application
Server. The server life cycle commands such as ‘start
-
domain’ is used to start the domain
named domain
-
1 and ‘stop
-
domain’ is used to stop

the Administration Server of the
domain
-
1. Please refer to the Glassfish documentation at the URL
(
http://docs.sun.com/app/docs/doc/819
-
3671
) to learn more about this utility.


C:
\
glassfish
\
domains
\
domain1
\
logs>
asadmin start
-
domain domain1

Starting Doma
in domain1, please wait.

Log redirected to C:
\
glassfish
\
domains
\
domain1
\
logs
\
server.log.

Redirecting output to C:
\
glassfish
\
domains
\
domain1
\
logs
\
server.log

Domain domain1 started.

Domain [domain1] is running [Sun Java System Application Server 9.1_01 (buil
d b09d
-
fcs)] with its configuration and logs at: [C:
\
glassfish
\
domains].

Admin Console is available at [http://localhost:4848].

Use the same port [4848] for "asadmin" commands.

User web applications are available at these URLs:

[http://localhost:8080 https
://localhost:8181 ].

Following web
-
contexts are available:

[/web1 /__wstx
-
services jpa
-
example ].

Standard JMX Clients (like JConsole) can connect to JMXServiceURL:

[service:jmx:rmi:///jndi/rmi://rajus01
-
755:8686/jmxrmi] for domain management
purposes.

Do
main listens on at least following ports for connections:

[8080 8181 4848 3700 3820 3920 8686 ].

Domain supports application server clusters and other standalone instances.


C:
\
glassfish
\
domains
\
domain1
\
logs>
asadmin stop
-
domain domain1

Domain domain1 stopp
ed.


IV. CONFIGURATION STEPS



1. Login to the application server
(default

username:admin, password:adminadmin )



Fig 1
:
Glassfish

Admin Console Login Screen


2. Specify the location of
the
Ingres JDBC driver (iijdbc.jar) as

shown below by selecting
the (C
onfiguration
s
-
>server
-
config
-
>JVM S
ettings) on the left pane of the
Admin
console window. Typically in an Ingres installati
on
,

this jar file is located in
%
II_SYSTEM
%
\
ingres
\
lib director
y. 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. This may
r
equire restarting the AppServer.




Fig 2
: JVM classpath settings to include Ingres JDBC driver




3. Define Connection pool/ JDBC resources
for the

In
gres JDBC driver. The
figures
(Fig
5

through Fig 15
) that are
shown

in
the
appendix section at the end o
f the article

display
the steps to co
nfigure Connection pool/JDBC resources for Ingres
in Glassfish
.

Fig
ures

5
and 6

are the views of Resources
-
>JDBC, Resources
-
>JDBC
-
>Connection Pools
respectively

while navigating the tree on the left pane of Admin conso
le
. As noticed in
these
two
figures
,

there are n
o JDBC resources for Ingres

currently exist
ing

in
Glassfish
.
Fig 7

displays the

connection pool configurati
on for Ingres.

This example configuration
uses
the
XADatasource class

for the Resource Type
. This can

potentially be a Datasource
class
or
a CPDataSource class

d
epending on the JDBC resource

needs o
f
the
appl
ication
.

L
eave
‘Database Vendor’ field

blank
.

Fig 8

shows the default values for other
conn
ection pool configuration parameters in Glassfish
. These
values can be changed
based

on the application needs. Fig 9

is
the
continuation of Fig 8

for connection pool
configuration which displays the settings for connection validati
on and
t
ransaction
configuration
parameters for

connections in the connection pool
. The table name is
chosen to be “iitables” for the query to validate the connections in the pool before
providing
one

to the application. Fig 10 is the continuation of Fig 8 and Fig 9
,

displaying
the required
Ingres specific
datasource properties such as
serverName, userName,
password, portName, and d
atabaseName.





serverName


the name of the host on which Ingres is running (the Ingres iigcd /
Data Access Server (DAS) specifically...often with DBMS)



portName


this is the port on which Ingres listens for
JDBC connections. This

may be a mnemonic (like “II7”
).
It is R27 in this example.

Alternatively, the
Ingres data source property

‘portNumber’
can be used to specify a specific port.



databaseName


Ingres database name. If the Ingres DAS and DBMS are
inst
alled together (which is common), this is all that is needed. If installed on
different machines, then an Ingres vnode (virtual node) definition is also required
and must be specified using the format
vnode::dbname.

A vnode is generally
preconfigured in I
ngres using the Ingres Network Utility, in which case the name
of the vnode is used.



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 v
alidated 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
frequently 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 not need to be specified with the current r
elease, 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.


Fig

11

displays the completed connection pool configurat
ion named “Ingres2006R2XA”
on the tree v
iew of the Admin console. T
he edit view of the conne
ction pool
configuration

is shown in Fig 12. As shown in Fig 13
successful testing
(Ping button)
of
connection pool Ingres2006R2XA that was just created in the abo
ve steps

is completed
.


A JNDI name must be set up
now
for the application to access the JDBC Resource just
set up
.
Fig
14

displays the JDBC Resource

definition for Ingres which uses

the
connection pool name Ingres2006R2XA. The JNDI name is defined as
jdb
c_
_
/ingres
200r2

and this resource is
deployed on the default application server
running on domain1.
Finally,
the successful creation of
the

JDBC Resource

(jdbc__/ingres200r2)

for Ingres

is shown in the Fig

15
.


4. Test the Ingres JDBC resource name by usin
g 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.*" import="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( init Ct x != null )


{


ds = (javax.sql.DataSource) initCtx.lookup("jdbc/__ingres200r2");


}


}


cat ch(Exception e)


{


Syst em.out.println("DataSource Context not found");


Syst em.out.println(e.t
oSt ring());


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


}




t ry


{


if( ds != null )


{


conn = ds.get Connection();




Syst em.out.println("Got t he connect
ion successfully
\
n");



if( conn != null )


{


st mt = conn.createSt atement();


rs = st mt.executeQuery("SELECT t able_name from iitables");


}


}


}


cat ch(SQLException e
)


{


Syst em.out.println("An error occurs.");


Syst em.out.println(e.toSt ring());


t hrow new UnavailableException(this, "Cannot connect wit h t he specified dat abase.");


}



%>


<p>Return result:</p>



<table border=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>

<% }


stmt.close();


rs.close();


conn.close();


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

%>



</table>

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



</body>

</html>



The above application (jndi.jsp) can be deployed in Glassfish by simply copying it in
to

the folder C:
\
glassfish
\
domains
\
domain1
\
docroot
\
jsp given that Glassfish is installed in
C:
\
glassfish. The page can be accessed using the web browser as shown below

at the
URL shown in
the
browser
.

Glassfish is listening for HTTP requests at port 8080.


The
following is sample output from the applicatio
n.





Fig 3
: The JSP application output

The same Ingres JDBC resource

nam
e can potentially be used in other

EJB applications
needing access to
the
DBMS server.



V. JDBC DRIVER TRACING


If any problems occur while trying to access data in an Ingres data
base, 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 clas
spath for loading by the server class
loader.


ingres.jdbc.trace.log=c:
\
\
glassfish
\
\
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 comme
nted out
to limit the tracing
.
In this case,
the iijdbc.properties file
was placed
in
to

c:
\
2006r2jdbc as per the

JVM
settings shown
in Fig 4
below.



Fig 4: The iijdbc.properties file location for tracing Ingres JDBC driver


VI. CONCLUSION


This article
is
intended

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

by providing a clear example on

how to configure Ingres in
Glassfish Open Source Application Server.

In case of any issues or problems using the
Ingres JDBC dr
iver with Glassfish, 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

suppor
t issue.



VII. REFERENCES


1.

http://wiki.glassfish.java.net/Wiki.jsp?page=GlassFishWiki





VIII.
APPENDIX: CONFIGU
R
ATION SCREENS

(Fig

5

through Fig
15
)






Fig 5
: AppServer Resour
ces





Fig 6
: AppServer JDBC Connection Pools Resource




Fig 7
: Define JDBC connection pool for Ingres




Fig 8
: Define JDBC connection pool for Ingres






Fig 9
: Define JDBC connection pool for Ingres







Fig 10
: JDBC connection pool for Ingres



Fig 11
: JDBC connection pool for Ingres completed



Fig 12
: Edit JDBC connection pool for Ingres (if required )





F
ig 13
: Test JDBC connection pool for Ingres




Fig 14
: Create JDBC Resource for Ingres using the Connection pool name above



Fig 15
: JDBC resourc
e for

Ingres is successfully created.