| Subject | Re: [Firebird-Java] jaybird jndi connection problem |
|---|---|
| Author | Rick Fincher |
| Post date | 2003-12-12T21:20:43Z |
Hi Simone,
I'm not quite sure where your problem is but below is the section of the FAQ
regarding Tomcat and JayBird. It has examples of Tomcat Contexts that use a
pooled DataSource with JayBird.
This is relatively new to the FAQ so it may not be in your version.
The example you probably want is the one called "dbTest" that uses DBCP for
the connection pooling. See the Tomcat docs about DBCP.
You need to be sure commons.pool.jar, commons.dbcp.jar,
commons-collection.jar, and commons-logging-api.jar are in
CATALINA_HOME\common\lib. You can get these fro jakarta.apache.org if you
need them.
The examples below have complete demo web apps that create the database and
tables (if necessary) and put stuff in them and search for stuff using
connection pooled JayBird connections to Firebird.
The web-INF/web.xml files and conf\server.xml entries are listed.
Hope this helps!
Rick
8- How do I use JayBird with Tomcat?
CATALINA_HOME is the installation directory for Tomcat 4.x or greater. An
environment variable of that name is set up on the Tomcat server.
TOMCAT_HOME was used in versions before Tomcat 4.
To use JayBird with Tomcat you must put the jar files where your web apps
can access them. Once they are available to your web apps you use JayBird in
your servlets or beans just as you would in standalone programs.
If you have only one webapp using JayBird or you need to keep JayBird
separate from other web apps, put the jar files in the WEB-INF/lib/
subdirectory of your web app.
It is more likely that Firebird will be used by all of your web apps and
Tomcat itself. To have universal access to JayBird, put the jars in
CATALINA_HOME/common/lib/.
A simple example web app that creates a Firebird database and does a few
transactions directly is included below. It is called test.
Below that is the same web app set up to use a DataSource and connection
pooling via DBCP. It is called dbTest.
To use JayBird's internal connection pooling, you can configure an
FBWrapping DataSource for pooling just as you would in a stand-alone
program. If you put a class for doing that in a servlet and start it when
Tomcat is initialized, you can share a pool among web applications. If you
do this, take care to make it thread safe and synchronize access to methods.
Tomcat can also use Firebird for BASIC or FORM based user authentication.
See the Tomcat docs for more details. An example realm for this is listed
below. This goes in the CATALINA_HOME/conf/server.xml file.
<Realm className="org.apache.catalina.realm.JDBCRealm" debug="0"
driverName="org.firebirdsql.jdbc.FBDriver"
userNameCol="USER_NAME"
connectionName="sysdba"
userTable="USERS"
userCredCol="USER_PASS"
validate="true"
connectionURL="jdbc:firebirdsql:localhost/3050:/dir1/subdir/usersdb.gdb"
userRoleTable="USER_ROLES"
roleNameCol="ROLE_NAME"
connectionPassword="masterkey"/>
If your web app is set up to allow Tomcat to authenticate users this tells
Tomcat to use Firebird to look up user names and passwords. It does this by
calling the Firebird driver FBDriver to login to a database named
usersdb.gdb located on localhost in the directory /dir/subdir/, using the
username sysdba and the password masterkey.
Tomcat then takes the username that is typed into the browser by the person
logging into the web app and searches the table named USERS to see if it is
in the field USER_NAME of a record. If it is, it checks to see if the
password typed into the browser is the same as the one in the field
USER_PASS for that record. If it is, the user is allowed to login and Tomcat
opens the table USER_ROLES and searches for all entries with USER_NAME. For
each record it finds, it looks in the ROLE_NAME column and adds that role
name to a list of roles for the user. It then allows access to web apps
based on the roles listed by the database.
You can configure your web apps in WEB-INF/web.xml to only allow users with
certain roles access to the web app. You can even use the role inside your
JSP's to only draw certain parts of an HTML page if a user has the
appropriate role. This allows you to customize each page based on a user's
role.
To use Tomcat's online GUI management web app to control your Tomcat
installation, you must have the role "manager" in the USER_ROLES table under
your name.
See the Tomcat docs for more information.
Sample Web Apps
These examples assume that the username SYSDBA and password masterkey are
valid. These samples create and search for the database in
/databases/test.gdb. To use a different directory change those entries
below. For windows change /databases/test.gdb to c:\\databases\\test.gdb,
for example.
Sample web app test:
To use this sample web app create a folder called "test" in the webapps
directory of your Tomcat installation. Put the HTML file below into a file
called index.htm inside the folder called test. Put the contents of the XML
file following this into test/WEB-INF/web.xml. Finally, put the contents of
the jsp file following that into test/search.jsp. Start Tomcat and run test
in your browser by calling http://localhost/test/index.htm.
Put this in CATALINA_HOME/webapps/test/index.htm:
<html>
< head>
< title>index.htm</title>
< meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
< /head>
<body bgcolor="#CCCCFF" text="#000000">
< /p>
< H1 align="center">Enter Name</H1>
< /H1>
< p>This demo of a simple web app will take the name you type above and
search
a table called CUSTOMERS in a Firebird database named test.gdb using the
JayBird
JDBC
driver.
The table CUSTOMERS has two fields (columns): FIRST_NAME and LAST_NAME.</p>
< p>If the database or tables don't exist they will be created for you. You
will
need to edit the Strings at the top of the file search.jsp to appropriate
ones for your system.</p>
< p>If there are no entries with the same last name that you type in, the
program
will add 3 so you will get some output: "Aaron, Bob, and
Calvin".</p>
< p> </p>
< form name="form1" method="post" action="search.jsp">
< p align="center">
Type a Last Name to search for here:
<input name="name" type="text" id="name">
then click "Submit".</p>
< p align="center"> <input type="submit" name="Submit" value="Submit">
< /p>
< /form>
< p> </p>
< /body>
< /html>
Put this in CATALINA_HOME/webapps/test/WEB-INF/web.xml:
<?xml version="1.0" encoding="UTF-8"?>
< !DOCTYPE web-app PUBLIC '-//Sun Microsystems, Inc.//DTD Web Application
2.2//EN' 'http://java.sun.com/j2ee/dtds/web-app_2.2.dtd'>
<web-app>
<session-config>
<session-timeout>
30
</session-timeout>
</session-config>
<welcome-file-list>
<welcome-file>index.htm</welcome-file>
</welcome-file-list>
< /web-app>
Put this in CATALINA_HOME/webapps/test/search.jsp:
<%@page contentType="text/html; charset=iso-8859-1" language="java"
import="java.sql.*,org.firebirdsql.management.*"%>
<html>
< head>
< title>search.jsp</title>
< meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
< /head>
<body bgcolor="#CCCCFF" text="#000000"><p>
< /p>
< H1 align="center">Test Search</H1>
<%
// Edit the strings below as needed for your system.
String DB_SERVER_URL = "localhost";
int DB_SERVER_PORT = 3050;
// Use forward slashes here, even on Windows systems.
String DB_PATH = "c:/databases";
String DB_NAME = "test.gdb";
String DB_USER = "sysdba";
String DB_PASSWORD = "masterkey";
String DB_CONNECTION_STRING =
"jdbc:firebirdsql:"+DB_SERVER_URL+"/"+DB_SERVER_PORT+":"+DB_PATH+"/"+DB_NAME
;
String lastName = request.getParameter("name");
// To help you debug, you can set a session attribute to values indicating
// the prograss through the web app, then use an HTTP monitor to see how far
// you have progressed.
// This stuff creates the database with the JMX management tools
// if it doesn't already exist.
FBManager fbManager = new FBManager();
fbManager.setServer(DB_SERVER_URL);
fbManager.setPort(DB_SERVER_PORT);
fbManager.start();
fbManager.createDatabase(DB_PATH + "/" + DB_NAME, DB_USER, DB_PASSWORD);
// Load the JayBird driver. This is OK for a test but your real web apps
// should use a dataSource for efficiency and flexibility.
Class.forName("org.firebirdsql.jdbc.FBDriver");
%>
Opening a connection with connection string:
<%=DB_CONNECTION_STRING%><BR><BR>
< %
// Get a connection to the database.
Connection connRSFind = DriverManager.getConnection(DB_CONNECTION_STRING,
DB_USER, DB_PASSWORD);
// Back to HTML, show the user the LAST_NAME typed in.
%>
< p>
The user entered name is: <%=lastName%>
< p>
< %
String sqlString = "SELECT * FROM CUSTOMERS WHERE LAST_NAME = \'" + lastName
+"\'";
// Switch back to html so we can print out the sqlString on the web page.
%>
The SQL String is: <%=sqlString%><p>
<%
PreparedStatement StatementRSFind=null;
ResultSet RSFind=null;
boolean resultException=false;
boolean rsReady = false;
try
{
StatementRSFind = connRSFind.prepareStatement(sqlString);
RSFind = StatementRSFind.executeQuery();
rsReady = RSFind.next();
}
catch(SQLException e1)
{
resultException = true; // Set a flag so that we know there was an error,
not just an empty result set.
%>
Could not find table, I'll try to add it: <%=e1.getMessage()%><BR>
< %
}
if (!rsReady || resultException)
{ // If there are no database entries with this last name, enter a few so
the user has something to look at.
// Or, if there was an error, the table probably doesn't exist yet.
// try to create table CUSTOMERS, in case it doesn't exist yet.
if (resultException)
{// The table CUSTOMERS probably doesn't exist, so we create it here.
Statement statement2=null;
try
{
statement2 = connRSFind.createStatement();
}
catch(SQLException e2)
{
%>
Could not create statement2 with this connection! Check your database
settings in search.jsp: <%=e2.getMessage()%><BR>
< %
}
if (statement2 != null)
{
try
{ // try to crate the table CUSTOMERS.
sqlString = "CREATE TABLE CUSTOMERS(LAST_NAME VARCHAR(50), FIRST_NAME
VARCHAR(50))";
statement2.execute(sqlString);
statement2.close();
}
catch(SQLException e2a)
{
%>
Table CUSTOMERS already exists and we tried to create it again, or it could
not be created: <%=e2a.getMessage()%> <BR>
< %
}
}
} // Table CUSTOZMERS should exist now, if it didn't before or things are
really messed up.
else
{
%>
First attempt at getting a result set produced an empty result set.<BR>
< %
}
// Insert some names into table CUSTOMERS. Either the table is new and
empty, or there were
// no entries with the last name the user gave us, so we'll enter a few, so
the user has
// something to look at.
Statement statement3=null;
try
{
statement3 = connRSFind.createStatement();
}
catch(SQLException e3)
{
%>
Could not create statement3 with this connection! Check your database
settings in search.jsp: <%=e3.getMessage()%><BR>
< %
}
if (statement3 != null)
{
try
{
sqlString = "INSERT INTO CUSTOMERS(LAST_NAME, FIRST_NAME)
VALUES(\'"+lastName+"\', 'Aaron')";
%>
Executing SQL: <%=sqlString%><BR>
< %
statement3.execute(sqlString);
sqlString = "INSERT INTO CUSTOMERS(LAST_NAME, FIRST_NAME)
VALUES(\'"+lastName+"\', 'Bob')";
%>
Executing SQL: <%=sqlString%><BR>
< %
statement3.execute(sqlString);
sqlString = "INSERT INTO CUSTOMERS(LAST_NAME, FIRST_NAME)
VALUES(\'"+lastName+"\', 'Calvin')";
%>
Executing SQL: <%=sqlString%><BR><BR>
< %
statement3.execute(sqlString);
statement3.close();
}
catch(SQLException e3a)
{
%>
We could not enter data in table CUSTOMERS for some reason:
<%=e3a.getMessage()%> <BR>
< %
}
}
// try again to get a result set.
sqlString = "SELECT * FROM CUSTOMERS WHERE LAST_NAME = \'" + lastName +"\'";
StatementRSFind = connRSFind.prepareStatement(sqlString);
RSFind = StatementRSFind.executeQuery();
rsReady = RSFind.next();
}
int i = 0;
if (rsReady)
{
boolean done=false;
while (!done)
{
i++;
String RSFind_Last = (String) RSFind.getObject("LAST_NAME");
String RSFind_First = (String) RSFind.getObject("FIRST_NAME");
// display the names in the browser.
%>
Name <%=i%>: <%=RSFind_First%> <%=RSFind_Last%> <BR>
< %
done = !RSFind.next();
} //End while loop
RSFind.close();
}
else
{
%>
< BR>The result set was empty. Check to be sure database is running and
settings in search.jsp are correct.<BR>
< %
}
if (StatementRSFind != null)
StatementRSFind.close();
if (connRSFind != null)
connRSFind.close();
%>
</body>
< /html>
Sample web app dbTest:
This sample web app shows how to use a Fiorebird DataSource with Tomcat. To
use it, create a folder called "dbTest" in the webapps directory of your
Tomcat installation. Put the HTML file below into a file called index.htm
inside the folder called dbTest. Put the contents of the XML file following
this into dbTtest/WEB-INF/web.xml. Put the contents of the jsp file
following that into dbTest/search.jsp. Put the context XML code after that
into the conf/server.xml file of your Tomcat installation just before the
entry: </Host>. Start Tomcat and run dbTest in your browser by calling
http://localhost/dbTest/index.htm.
Put this in CATALINA_HOME/webapps/dbTest/index.htm:
<html>
< head>
< title>index.htm</title>
< meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
< /head>
<body bgcolor="#CCCCFF" text="#000000">
< /p>
< H1 align="center">Enter Name</H1>
< /H1>
< p>This demo of a simple web app will take the name you type above and
search
a table called CUSTOMERS in a Firebird database named test.gdb using the
JayBird
JDBC
driver.
The table CUSTOMERS has two fields (columns): FIRST_NAME and LAST_NAME.</p>
< p>If the database or tables don't exist they will be created for you. You
will
need to edit the Strings at the top of the file search.jsp to appropriate
ones for your system.</p>
< p>If there are no entries with the same last name that you type in, the
program
will add 3 so you will get some output: "Aaron, Bob, and
Calvin".</p>
< p> </p>
< form name="form1" method="post" action="search.jsp">
< p align="center">
Type a Last Name to search for here:
<input name="name" type="text" id="name">
then click "Submit".</p>
< p align="center"> <input type="submit" name="Submit" value="Submit">
< /p>
< /form>
< p> </p>
< /body>
< /html>
Put this in CATALINA_HOME/webapps/dbTest/WEB-INF/web.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE web-app PUBLIC '-//Sun Microsystems, Inc.//DTD Web Application
2.2//EN' 'http://java.sun.com/j2ee/dtds/web-app_2.2.dtd'>
<web-app>
<session-config>
<session-timeout>
30
</session-timeout>
</session-config>
<welcome-file-list>
<welcome-file>index.htm</welcome-file>
</welcome-file-list>
<resource-ref>
<description>Test SQL DB Connection</description>
<res-ref-name>jdbc/dbTest</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
< /web-app>
Put this in CATALINA_HOME/webapps/dbTest/search.jsp:
<%@page contentType="text/html; charset=iso-8859-1" language="java"
import="java.sql.*,javax.sql.*,javax.servlet.*,
javax.servlet.http.*,javax.naming.*,org.firebirdsql.management.*"%>
<html>
< head>
< title>search.jsp</title>
< meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
< /head>
<body bgcolor="#CCCCFF" text="#000000"><p>
< /p>
< H1 align="center">Test Search</H1>
<%
// Edit the strings below as needed for your system.
String DB_SERVER_URL = "localhost";
int DB_SERVER_PORT = 3050;
// Use forward slashes here, even on Windows systems.
String DB_PATH = "c:/databases";
String DB_NAME = "test.gdb";
String DB_USER = "sysdba";
String DB_PASSWORD = "masterkey";
String DB_CONNECTION_STRING =
"jdbc:firebirdsql:"+DB_SERVER_URL+"/"+DB_SERVER_PORT+":"+DB_PATH+"/"+DB_NAME
;
String lastName = request.getParameter("name");
// To help you debug, you can set a session attribute to values indicating
// the prograss through the web app, then use an HTTP monitor to see how far
// you have progressed.
// This stuff creates the database with the JMX management tools
// if it doesn't already exist.
FBManager fbManager = new FBManager();
fbManager.setServer(DB_SERVER_URL);
fbManager.setPort(DB_SERVER_PORT);
fbManager.start();
fbManager.createDatabase(DB_PATH + "/" + DB_NAME, DB_USER, DB_PASSWORD);
%>
Opening a connection with connection string:
<%=DB_CONNECTION_STRING%><BR><BR>
< %
// Get a connection to the database from the dataSource.
DataSource ds=null;
Connection connRSFind=null;
try
{
Context ctx = new InitialContext();
if(ctx == null )
throw new Exception("Boom - No Context");
ds = (DataSource)ctx.lookup("java:comp/env/jdbc/dbTest");
try
{
connRSFind = ds.getConnection();
}
catch (SQLException e)
{
System.out.println("getting new data source connection failed! Error:
"+e+"\n");
}
}
catch (Exception e)
{
System.out.println("Could Not get data source, error: "+e+"\n");
}
// Back to HTML, show the user the LAST_NAME typed in.
%>
< p>
The user entered name is: <%=lastName%>
< p>
< %
String sqlString = "SELECT * FROM CUSTOMERS WHERE LAST_NAME = \'" + lastName
+"\'";
// Switch back to html so we can print out the sqlString on the web page.
%>
The SQL String is: <%=sqlString%><p>
<%
PreparedStatement StatementRSFind=null;
ResultSet RSFind=null;
boolean resultException=false;
boolean rsReady = false;
try
{
StatementRSFind = connRSFind.prepareStatement(sqlString);
RSFind = StatementRSFind.executeQuery();
rsReady = RSFind.next();
}
catch(SQLException e1)
{
e1.printStackTrace(System.out);
resultException = true; // Set a flag so that we know there was an error,
not just an empty result set.
%>
Could not find table, I'll try to add it: <%=e1.getMessage()%><BR>
< %
}
if (!rsReady || resultException)
{ // If there are no database entries with this last name, enter a few so
the user has something to look at.
// Or, if there was an error, the table probably doesn't exist yet.
// try to create table CUSTOMERS, in case it doesn't exist yet.
if (resultException)
{// The table CUSTOMERS probably doesn't exist, so we create it here.
Statement statement2=null;
try
{
statement2 = connRSFind.createStatement();
}
catch(SQLException e2)
{
%>
Could not create statement2 with this connection! Check your database
settings in search.jsp: <%=e2.getMessage()%><BR>
< %
}
if (statement2 != null)
{
try
{ // try to crate the table CUSTOMERS.
sqlString = "CREATE TABLE CUSTOMERS(LAST_NAME VARCHAR(50), FIRST_NAME
VARCHAR(50))";
statement2.execute(sqlString);
statement2.close();
}
catch(SQLException e2a)
{
%>
Table CUSTOMERS already exists and we tried to create it again, or it could
not be created: <%=e2a.getMessage()%> <BR>
< %
}
}
} // Table CUSTOZMERS should exist now, if it didn't before or things are
really messed up.
else
{
%>
First attempt at getting a result set produced an empty result set.<BR>
< %
}
// Insert some names into table CUSTOMERS. Either the table is new and
empty, or there were
// no entries with the last name the user gave us, so we'll enter a few, so
the user has
// something to look at.
Statement statement3=null;
try
{
statement3 = connRSFind.createStatement();
}
catch(SQLException e3)
{
%>
Could not create statement3 with this connection! Check your database
settings in search.jsp: <%=e3.getMessage()%><BR>
< %
}
if (statement3 != null)
{
try
{
sqlString = "INSERT INTO CUSTOMERS(LAST_NAME, FIRST_NAME)
VALUES(\'"+lastName+"\', 'Aaron')";
%>
Executing SQL: <%=sqlString%><BR>
< %
statement3.execute(sqlString);
sqlString = "INSERT INTO CUSTOMERS(LAST_NAME, FIRST_NAME)
VALUES(\'"+lastName+"\', 'Bob')";
%>
Executing SQL: <%=sqlString%><BR>
< %
statement3.execute(sqlString);
sqlString = "INSERT INTO CUSTOMERS(LAST_NAME, FIRST_NAME)
VALUES(\'"+lastName+"\', 'Calvin')";
%>
Executing SQL: <%=sqlString%><BR><BR>
< %
statement3.execute(sqlString);
statement3.close();
}
catch(SQLException e3a)
{
%>
We could not enter data in table CUSTOMERS for some reason:
<%=e3a.getMessage()%> <BR>
< %
}
}
// try again to get a result set.
sqlString = "SELECT * FROM CUSTOMERS WHERE LAST_NAME = \'" + lastName +"\'";
StatementRSFind = connRSFind.prepareStatement(sqlString);
RSFind = StatementRSFind.executeQuery();
rsReady = RSFind.next();
}
int i = 0;
if (rsReady)
{
boolean done=false;
while (!done)
{
i++;
String RSFind_Last = (String) RSFind.getObject("LAST_NAME");
String RSFind_First = (String) RSFind.getObject("FIRST_NAME");
// display the names in the browser.
%>
Name <%=i%>: <%=RSFind_First%> <%=RSFind_Last%> <BR>
< %
done = !RSFind.next();
} //End while loop
RSFind.close();
}
else
{
%>
< BR>The result set was empty. Check to be sure database is running and
settings in search.jsp are correct.<BR>
< %
}
if (StatementRSFind != null)
StatementRSFind.close();
if (connRSFind != null)
connRSFind.close();
%>
</body>
< /html>
Put this in CATALINA_HOME/conf/server.xml before </Host>:
<Context path="/dbTest" docBase="dbTest"
debug="0" reloadable="true" crossContext="true">
<Logger className="org.apache.catalina.logger.FileLogger"
prefix="localhost_dbTest_log." suffix=".txt"
timestamp="true"/>
<Resource name="jdbc/dbTest"
auth="Container"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/dbTest">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>removeAbandonedTimeout</name>
<value>300</value>
</parameter>
<parameter>
<name>logAbandoned</name>
<value>true</value>
</parameter>
<!-- Maximum number of dB connections in pool. Make sure you
configure your Firebird max_connections large enough to handle
all of your db connections. Set to 0 for no limit.
-->
<parameter>
<name>maxActive</name>
<value>100</value>
</parameter>
<!-- Maximum number of idle dB connections to retain in pool.
Set to 0 for no limit.
-->
<parameter>
<name>maxIdle</name>
<value>30</value>
</parameter>
<!-- Maximum time to wait for a dB connection to become available
in ms, in this example 10 seconds. An Exception is thrown if
this timeout is exceeded. Set to -1 to wait indefinitely.
-->
<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>
<!-- Firebird dB username and password for dB connections -->
<parameter>
<name>username</name>
<value>SYSDBA</value>
</parameter>
<parameter>
<name>password</name>
<value>masterkey</value>
</parameter>
<!-- Class name for JayBird JDBC driver -->
<parameter>
<name>driverClassName</name>
<value>org.firebirdsql.jdbc.FBDriver</value>
</parameter>
<!-- The JDBC connection url for connecting to your Firebird dB.
The autoReconnect=true argument to the url makes sure that the
JayBird JDBC Driver will automatically reconnect if Firebird closed the
connection.
-->
<parameter>
<name>url</name>
<value>jdbc:firebirdsql:localhost/3050:/databases/test.gdb</value>
</parameter>
</ResourceParams>
</Context>
I'm not quite sure where your problem is but below is the section of the FAQ
regarding Tomcat and JayBird. It has examples of Tomcat Contexts that use a
pooled DataSource with JayBird.
This is relatively new to the FAQ so it may not be in your version.
The example you probably want is the one called "dbTest" that uses DBCP for
the connection pooling. See the Tomcat docs about DBCP.
You need to be sure commons.pool.jar, commons.dbcp.jar,
commons-collection.jar, and commons-logging-api.jar are in
CATALINA_HOME\common\lib. You can get these fro jakarta.apache.org if you
need them.
The examples below have complete demo web apps that create the database and
tables (if necessary) and put stuff in them and search for stuff using
connection pooled JayBird connections to Firebird.
The web-INF/web.xml files and conf\server.xml entries are listed.
Hope this helps!
Rick
8- How do I use JayBird with Tomcat?
CATALINA_HOME is the installation directory for Tomcat 4.x or greater. An
environment variable of that name is set up on the Tomcat server.
TOMCAT_HOME was used in versions before Tomcat 4.
To use JayBird with Tomcat you must put the jar files where your web apps
can access them. Once they are available to your web apps you use JayBird in
your servlets or beans just as you would in standalone programs.
If you have only one webapp using JayBird or you need to keep JayBird
separate from other web apps, put the jar files in the WEB-INF/lib/
subdirectory of your web app.
It is more likely that Firebird will be used by all of your web apps and
Tomcat itself. To have universal access to JayBird, put the jars in
CATALINA_HOME/common/lib/.
A simple example web app that creates a Firebird database and does a few
transactions directly is included below. It is called test.
Below that is the same web app set up to use a DataSource and connection
pooling via DBCP. It is called dbTest.
To use JayBird's internal connection pooling, you can configure an
FBWrapping DataSource for pooling just as you would in a stand-alone
program. If you put a class for doing that in a servlet and start it when
Tomcat is initialized, you can share a pool among web applications. If you
do this, take care to make it thread safe and synchronize access to methods.
Tomcat can also use Firebird for BASIC or FORM based user authentication.
See the Tomcat docs for more details. An example realm for this is listed
below. This goes in the CATALINA_HOME/conf/server.xml file.
<Realm className="org.apache.catalina.realm.JDBCRealm" debug="0"
driverName="org.firebirdsql.jdbc.FBDriver"
userNameCol="USER_NAME"
connectionName="sysdba"
userTable="USERS"
userCredCol="USER_PASS"
validate="true"
connectionURL="jdbc:firebirdsql:localhost/3050:/dir1/subdir/usersdb.gdb"
userRoleTable="USER_ROLES"
roleNameCol="ROLE_NAME"
connectionPassword="masterkey"/>
If your web app is set up to allow Tomcat to authenticate users this tells
Tomcat to use Firebird to look up user names and passwords. It does this by
calling the Firebird driver FBDriver to login to a database named
usersdb.gdb located on localhost in the directory /dir/subdir/, using the
username sysdba and the password masterkey.
Tomcat then takes the username that is typed into the browser by the person
logging into the web app and searches the table named USERS to see if it is
in the field USER_NAME of a record. If it is, it checks to see if the
password typed into the browser is the same as the one in the field
USER_PASS for that record. If it is, the user is allowed to login and Tomcat
opens the table USER_ROLES and searches for all entries with USER_NAME. For
each record it finds, it looks in the ROLE_NAME column and adds that role
name to a list of roles for the user. It then allows access to web apps
based on the roles listed by the database.
You can configure your web apps in WEB-INF/web.xml to only allow users with
certain roles access to the web app. You can even use the role inside your
JSP's to only draw certain parts of an HTML page if a user has the
appropriate role. This allows you to customize each page based on a user's
role.
To use Tomcat's online GUI management web app to control your Tomcat
installation, you must have the role "manager" in the USER_ROLES table under
your name.
See the Tomcat docs for more information.
Sample Web Apps
These examples assume that the username SYSDBA and password masterkey are
valid. These samples create and search for the database in
/databases/test.gdb. To use a different directory change those entries
below. For windows change /databases/test.gdb to c:\\databases\\test.gdb,
for example.
Sample web app test:
To use this sample web app create a folder called "test" in the webapps
directory of your Tomcat installation. Put the HTML file below into a file
called index.htm inside the folder called test. Put the contents of the XML
file following this into test/WEB-INF/web.xml. Finally, put the contents of
the jsp file following that into test/search.jsp. Start Tomcat and run test
in your browser by calling http://localhost/test/index.htm.
Put this in CATALINA_HOME/webapps/test/index.htm:
<html>
< head>
< title>index.htm</title>
< meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
< /head>
<body bgcolor="#CCCCFF" text="#000000">
< /p>
< H1 align="center">Enter Name</H1>
< /H1>
< p>This demo of a simple web app will take the name you type above and
search
a table called CUSTOMERS in a Firebird database named test.gdb using the
JayBird
JDBC
driver.
The table CUSTOMERS has two fields (columns): FIRST_NAME and LAST_NAME.</p>
< p>If the database or tables don't exist they will be created for you. You
will
need to edit the Strings at the top of the file search.jsp to appropriate
ones for your system.</p>
< p>If there are no entries with the same last name that you type in, the
program
will add 3 so you will get some output: "Aaron, Bob, and
Calvin".</p>
< p> </p>
< form name="form1" method="post" action="search.jsp">
< p align="center">
Type a Last Name to search for here:
<input name="name" type="text" id="name">
then click "Submit".</p>
< p align="center"> <input type="submit" name="Submit" value="Submit">
< /p>
< /form>
< p> </p>
< /body>
< /html>
Put this in CATALINA_HOME/webapps/test/WEB-INF/web.xml:
<?xml version="1.0" encoding="UTF-8"?>
< !DOCTYPE web-app PUBLIC '-//Sun Microsystems, Inc.//DTD Web Application
2.2//EN' 'http://java.sun.com/j2ee/dtds/web-app_2.2.dtd'>
<web-app>
<session-config>
<session-timeout>
30
</session-timeout>
</session-config>
<welcome-file-list>
<welcome-file>index.htm</welcome-file>
</welcome-file-list>
< /web-app>
Put this in CATALINA_HOME/webapps/test/search.jsp:
<%@page contentType="text/html; charset=iso-8859-1" language="java"
import="java.sql.*,org.firebirdsql.management.*"%>
<html>
< head>
< title>search.jsp</title>
< meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
< /head>
<body bgcolor="#CCCCFF" text="#000000"><p>
< /p>
< H1 align="center">Test Search</H1>
<%
// Edit the strings below as needed for your system.
String DB_SERVER_URL = "localhost";
int DB_SERVER_PORT = 3050;
// Use forward slashes here, even on Windows systems.
String DB_PATH = "c:/databases";
String DB_NAME = "test.gdb";
String DB_USER = "sysdba";
String DB_PASSWORD = "masterkey";
String DB_CONNECTION_STRING =
"jdbc:firebirdsql:"+DB_SERVER_URL+"/"+DB_SERVER_PORT+":"+DB_PATH+"/"+DB_NAME
;
String lastName = request.getParameter("name");
// To help you debug, you can set a session attribute to values indicating
// the prograss through the web app, then use an HTTP monitor to see how far
// you have progressed.
// This stuff creates the database with the JMX management tools
// if it doesn't already exist.
FBManager fbManager = new FBManager();
fbManager.setServer(DB_SERVER_URL);
fbManager.setPort(DB_SERVER_PORT);
fbManager.start();
fbManager.createDatabase(DB_PATH + "/" + DB_NAME, DB_USER, DB_PASSWORD);
// Load the JayBird driver. This is OK for a test but your real web apps
// should use a dataSource for efficiency and flexibility.
Class.forName("org.firebirdsql.jdbc.FBDriver");
%>
Opening a connection with connection string:
<%=DB_CONNECTION_STRING%><BR><BR>
< %
// Get a connection to the database.
Connection connRSFind = DriverManager.getConnection(DB_CONNECTION_STRING,
DB_USER, DB_PASSWORD);
// Back to HTML, show the user the LAST_NAME typed in.
%>
< p>
The user entered name is: <%=lastName%>
< p>
< %
String sqlString = "SELECT * FROM CUSTOMERS WHERE LAST_NAME = \'" + lastName
+"\'";
// Switch back to html so we can print out the sqlString on the web page.
%>
The SQL String is: <%=sqlString%><p>
<%
PreparedStatement StatementRSFind=null;
ResultSet RSFind=null;
boolean resultException=false;
boolean rsReady = false;
try
{
StatementRSFind = connRSFind.prepareStatement(sqlString);
RSFind = StatementRSFind.executeQuery();
rsReady = RSFind.next();
}
catch(SQLException e1)
{
resultException = true; // Set a flag so that we know there was an error,
not just an empty result set.
%>
Could not find table, I'll try to add it: <%=e1.getMessage()%><BR>
< %
}
if (!rsReady || resultException)
{ // If there are no database entries with this last name, enter a few so
the user has something to look at.
// Or, if there was an error, the table probably doesn't exist yet.
// try to create table CUSTOMERS, in case it doesn't exist yet.
if (resultException)
{// The table CUSTOMERS probably doesn't exist, so we create it here.
Statement statement2=null;
try
{
statement2 = connRSFind.createStatement();
}
catch(SQLException e2)
{
%>
Could not create statement2 with this connection! Check your database
settings in search.jsp: <%=e2.getMessage()%><BR>
< %
}
if (statement2 != null)
{
try
{ // try to crate the table CUSTOMERS.
sqlString = "CREATE TABLE CUSTOMERS(LAST_NAME VARCHAR(50), FIRST_NAME
VARCHAR(50))";
statement2.execute(sqlString);
statement2.close();
}
catch(SQLException e2a)
{
%>
Table CUSTOMERS already exists and we tried to create it again, or it could
not be created: <%=e2a.getMessage()%> <BR>
< %
}
}
} // Table CUSTOZMERS should exist now, if it didn't before or things are
really messed up.
else
{
%>
First attempt at getting a result set produced an empty result set.<BR>
< %
}
// Insert some names into table CUSTOMERS. Either the table is new and
empty, or there were
// no entries with the last name the user gave us, so we'll enter a few, so
the user has
// something to look at.
Statement statement3=null;
try
{
statement3 = connRSFind.createStatement();
}
catch(SQLException e3)
{
%>
Could not create statement3 with this connection! Check your database
settings in search.jsp: <%=e3.getMessage()%><BR>
< %
}
if (statement3 != null)
{
try
{
sqlString = "INSERT INTO CUSTOMERS(LAST_NAME, FIRST_NAME)
VALUES(\'"+lastName+"\', 'Aaron')";
%>
Executing SQL: <%=sqlString%><BR>
< %
statement3.execute(sqlString);
sqlString = "INSERT INTO CUSTOMERS(LAST_NAME, FIRST_NAME)
VALUES(\'"+lastName+"\', 'Bob')";
%>
Executing SQL: <%=sqlString%><BR>
< %
statement3.execute(sqlString);
sqlString = "INSERT INTO CUSTOMERS(LAST_NAME, FIRST_NAME)
VALUES(\'"+lastName+"\', 'Calvin')";
%>
Executing SQL: <%=sqlString%><BR><BR>
< %
statement3.execute(sqlString);
statement3.close();
}
catch(SQLException e3a)
{
%>
We could not enter data in table CUSTOMERS for some reason:
<%=e3a.getMessage()%> <BR>
< %
}
}
// try again to get a result set.
sqlString = "SELECT * FROM CUSTOMERS WHERE LAST_NAME = \'" + lastName +"\'";
StatementRSFind = connRSFind.prepareStatement(sqlString);
RSFind = StatementRSFind.executeQuery();
rsReady = RSFind.next();
}
int i = 0;
if (rsReady)
{
boolean done=false;
while (!done)
{
i++;
String RSFind_Last = (String) RSFind.getObject("LAST_NAME");
String RSFind_First = (String) RSFind.getObject("FIRST_NAME");
// display the names in the browser.
%>
Name <%=i%>: <%=RSFind_First%> <%=RSFind_Last%> <BR>
< %
done = !RSFind.next();
} //End while loop
RSFind.close();
}
else
{
%>
< BR>The result set was empty. Check to be sure database is running and
settings in search.jsp are correct.<BR>
< %
}
if (StatementRSFind != null)
StatementRSFind.close();
if (connRSFind != null)
connRSFind.close();
%>
</body>
< /html>
Sample web app dbTest:
This sample web app shows how to use a Fiorebird DataSource with Tomcat. To
use it, create a folder called "dbTest" in the webapps directory of your
Tomcat installation. Put the HTML file below into a file called index.htm
inside the folder called dbTest. Put the contents of the XML file following
this into dbTtest/WEB-INF/web.xml. Put the contents of the jsp file
following that into dbTest/search.jsp. Put the context XML code after that
into the conf/server.xml file of your Tomcat installation just before the
entry: </Host>. Start Tomcat and run dbTest in your browser by calling
http://localhost/dbTest/index.htm.
Put this in CATALINA_HOME/webapps/dbTest/index.htm:
<html>
< head>
< title>index.htm</title>
< meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
< /head>
<body bgcolor="#CCCCFF" text="#000000">
< /p>
< H1 align="center">Enter Name</H1>
< /H1>
< p>This demo of a simple web app will take the name you type above and
search
a table called CUSTOMERS in a Firebird database named test.gdb using the
JayBird
JDBC
driver.
The table CUSTOMERS has two fields (columns): FIRST_NAME and LAST_NAME.</p>
< p>If the database or tables don't exist they will be created for you. You
will
need to edit the Strings at the top of the file search.jsp to appropriate
ones for your system.</p>
< p>If there are no entries with the same last name that you type in, the
program
will add 3 so you will get some output: "Aaron, Bob, and
Calvin".</p>
< p> </p>
< form name="form1" method="post" action="search.jsp">
< p align="center">
Type a Last Name to search for here:
<input name="name" type="text" id="name">
then click "Submit".</p>
< p align="center"> <input type="submit" name="Submit" value="Submit">
< /p>
< /form>
< p> </p>
< /body>
< /html>
Put this in CATALINA_HOME/webapps/dbTest/WEB-INF/web.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE web-app PUBLIC '-//Sun Microsystems, Inc.//DTD Web Application
2.2//EN' 'http://java.sun.com/j2ee/dtds/web-app_2.2.dtd'>
<web-app>
<session-config>
<session-timeout>
30
</session-timeout>
</session-config>
<welcome-file-list>
<welcome-file>index.htm</welcome-file>
</welcome-file-list>
<resource-ref>
<description>Test SQL DB Connection</description>
<res-ref-name>jdbc/dbTest</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
< /web-app>
Put this in CATALINA_HOME/webapps/dbTest/search.jsp:
<%@page contentType="text/html; charset=iso-8859-1" language="java"
import="java.sql.*,javax.sql.*,javax.servlet.*,
javax.servlet.http.*,javax.naming.*,org.firebirdsql.management.*"%>
<html>
< head>
< title>search.jsp</title>
< meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
< /head>
<body bgcolor="#CCCCFF" text="#000000"><p>
< /p>
< H1 align="center">Test Search</H1>
<%
// Edit the strings below as needed for your system.
String DB_SERVER_URL = "localhost";
int DB_SERVER_PORT = 3050;
// Use forward slashes here, even on Windows systems.
String DB_PATH = "c:/databases";
String DB_NAME = "test.gdb";
String DB_USER = "sysdba";
String DB_PASSWORD = "masterkey";
String DB_CONNECTION_STRING =
"jdbc:firebirdsql:"+DB_SERVER_URL+"/"+DB_SERVER_PORT+":"+DB_PATH+"/"+DB_NAME
;
String lastName = request.getParameter("name");
// To help you debug, you can set a session attribute to values indicating
// the prograss through the web app, then use an HTTP monitor to see how far
// you have progressed.
// This stuff creates the database with the JMX management tools
// if it doesn't already exist.
FBManager fbManager = new FBManager();
fbManager.setServer(DB_SERVER_URL);
fbManager.setPort(DB_SERVER_PORT);
fbManager.start();
fbManager.createDatabase(DB_PATH + "/" + DB_NAME, DB_USER, DB_PASSWORD);
%>
Opening a connection with connection string:
<%=DB_CONNECTION_STRING%><BR><BR>
< %
// Get a connection to the database from the dataSource.
DataSource ds=null;
Connection connRSFind=null;
try
{
Context ctx = new InitialContext();
if(ctx == null )
throw new Exception("Boom - No Context");
ds = (DataSource)ctx.lookup("java:comp/env/jdbc/dbTest");
try
{
connRSFind = ds.getConnection();
}
catch (SQLException e)
{
System.out.println("getting new data source connection failed! Error:
"+e+"\n");
}
}
catch (Exception e)
{
System.out.println("Could Not get data source, error: "+e+"\n");
}
// Back to HTML, show the user the LAST_NAME typed in.
%>
< p>
The user entered name is: <%=lastName%>
< p>
< %
String sqlString = "SELECT * FROM CUSTOMERS WHERE LAST_NAME = \'" + lastName
+"\'";
// Switch back to html so we can print out the sqlString on the web page.
%>
The SQL String is: <%=sqlString%><p>
<%
PreparedStatement StatementRSFind=null;
ResultSet RSFind=null;
boolean resultException=false;
boolean rsReady = false;
try
{
StatementRSFind = connRSFind.prepareStatement(sqlString);
RSFind = StatementRSFind.executeQuery();
rsReady = RSFind.next();
}
catch(SQLException e1)
{
e1.printStackTrace(System.out);
resultException = true; // Set a flag so that we know there was an error,
not just an empty result set.
%>
Could not find table, I'll try to add it: <%=e1.getMessage()%><BR>
< %
}
if (!rsReady || resultException)
{ // If there are no database entries with this last name, enter a few so
the user has something to look at.
// Or, if there was an error, the table probably doesn't exist yet.
// try to create table CUSTOMERS, in case it doesn't exist yet.
if (resultException)
{// The table CUSTOMERS probably doesn't exist, so we create it here.
Statement statement2=null;
try
{
statement2 = connRSFind.createStatement();
}
catch(SQLException e2)
{
%>
Could not create statement2 with this connection! Check your database
settings in search.jsp: <%=e2.getMessage()%><BR>
< %
}
if (statement2 != null)
{
try
{ // try to crate the table CUSTOMERS.
sqlString = "CREATE TABLE CUSTOMERS(LAST_NAME VARCHAR(50), FIRST_NAME
VARCHAR(50))";
statement2.execute(sqlString);
statement2.close();
}
catch(SQLException e2a)
{
%>
Table CUSTOMERS already exists and we tried to create it again, or it could
not be created: <%=e2a.getMessage()%> <BR>
< %
}
}
} // Table CUSTOZMERS should exist now, if it didn't before or things are
really messed up.
else
{
%>
First attempt at getting a result set produced an empty result set.<BR>
< %
}
// Insert some names into table CUSTOMERS. Either the table is new and
empty, or there were
// no entries with the last name the user gave us, so we'll enter a few, so
the user has
// something to look at.
Statement statement3=null;
try
{
statement3 = connRSFind.createStatement();
}
catch(SQLException e3)
{
%>
Could not create statement3 with this connection! Check your database
settings in search.jsp: <%=e3.getMessage()%><BR>
< %
}
if (statement3 != null)
{
try
{
sqlString = "INSERT INTO CUSTOMERS(LAST_NAME, FIRST_NAME)
VALUES(\'"+lastName+"\', 'Aaron')";
%>
Executing SQL: <%=sqlString%><BR>
< %
statement3.execute(sqlString);
sqlString = "INSERT INTO CUSTOMERS(LAST_NAME, FIRST_NAME)
VALUES(\'"+lastName+"\', 'Bob')";
%>
Executing SQL: <%=sqlString%><BR>
< %
statement3.execute(sqlString);
sqlString = "INSERT INTO CUSTOMERS(LAST_NAME, FIRST_NAME)
VALUES(\'"+lastName+"\', 'Calvin')";
%>
Executing SQL: <%=sqlString%><BR><BR>
< %
statement3.execute(sqlString);
statement3.close();
}
catch(SQLException e3a)
{
%>
We could not enter data in table CUSTOMERS for some reason:
<%=e3a.getMessage()%> <BR>
< %
}
}
// try again to get a result set.
sqlString = "SELECT * FROM CUSTOMERS WHERE LAST_NAME = \'" + lastName +"\'";
StatementRSFind = connRSFind.prepareStatement(sqlString);
RSFind = StatementRSFind.executeQuery();
rsReady = RSFind.next();
}
int i = 0;
if (rsReady)
{
boolean done=false;
while (!done)
{
i++;
String RSFind_Last = (String) RSFind.getObject("LAST_NAME");
String RSFind_First = (String) RSFind.getObject("FIRST_NAME");
// display the names in the browser.
%>
Name <%=i%>: <%=RSFind_First%> <%=RSFind_Last%> <BR>
< %
done = !RSFind.next();
} //End while loop
RSFind.close();
}
else
{
%>
< BR>The result set was empty. Check to be sure database is running and
settings in search.jsp are correct.<BR>
< %
}
if (StatementRSFind != null)
StatementRSFind.close();
if (connRSFind != null)
connRSFind.close();
%>
</body>
< /html>
Put this in CATALINA_HOME/conf/server.xml before </Host>:
<Context path="/dbTest" docBase="dbTest"
debug="0" reloadable="true" crossContext="true">
<Logger className="org.apache.catalina.logger.FileLogger"
prefix="localhost_dbTest_log." suffix=".txt"
timestamp="true"/>
<Resource name="jdbc/dbTest"
auth="Container"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/dbTest">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>removeAbandonedTimeout</name>
<value>300</value>
</parameter>
<parameter>
<name>logAbandoned</name>
<value>true</value>
</parameter>
<!-- Maximum number of dB connections in pool. Make sure you
configure your Firebird max_connections large enough to handle
all of your db connections. Set to 0 for no limit.
-->
<parameter>
<name>maxActive</name>
<value>100</value>
</parameter>
<!-- Maximum number of idle dB connections to retain in pool.
Set to 0 for no limit.
-->
<parameter>
<name>maxIdle</name>
<value>30</value>
</parameter>
<!-- Maximum time to wait for a dB connection to become available
in ms, in this example 10 seconds. An Exception is thrown if
this timeout is exceeded. Set to -1 to wait indefinitely.
-->
<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>
<!-- Firebird dB username and password for dB connections -->
<parameter>
<name>username</name>
<value>SYSDBA</value>
</parameter>
<parameter>
<name>password</name>
<value>masterkey</value>
</parameter>
<!-- Class name for JayBird JDBC driver -->
<parameter>
<name>driverClassName</name>
<value>org.firebirdsql.jdbc.FBDriver</value>
</parameter>
<!-- The JDBC connection url for connecting to your Firebird dB.
The autoReconnect=true argument to the url makes sure that the
JayBird JDBC Driver will automatically reconnect if Firebird closed the
connection.
-->
<parameter>
<name>url</name>
<value>jdbc:firebirdsql:localhost/3050:/databases/test.gdb</value>
</parameter>
</ResourceParams>
</Context>
----- Original Message -----
>
> Sorry for my english...
> I'm using jndi for connecting to a firebird db (under tomcat):
> 1) defining a application context (with tomcat manager) -->i'm
> thinking server.xml is OK
> 2) firebirdsql driver (last release 1.01) on catalina_home/common/lib
> 3) with "standard" class
> ....
> Context cntx = new InitialContext();
> ...
> DataSource = (DataSource)cntx.lookup
> ("java:comp/env/jdbc/user");
> ....
> Connection = ds.getConnection("sysdba", "masterkey");
> Boom
> Error occurred java.sql.SQLException: Cannot create JDBC driver of
> class 'org.fi
> rebirdsql.jdbc.FBDriver' for connect URL 'C:\progetti\db\USERS.FDB'
>
> 4) with FBWrappingDataSource it's ok.... only if setting db name
> onto code (ds.setDB("path+ db name")
> if i try
> FBWrappingDataSource = (FBWrappingDataSource)cntx.lookup
> ("java:comp/env/jdbc/user");
> Kabooom Class cast exception
>
> How can catch a db name(+ path) from context ?
> Tank's
>