We had quite a bit of trouble getting dbforms to work with Microsoft SQL Server. Attached is a readme-type document that describes how we got it to work; we hope other users will find it useful. Please don't hesitate to email feedback so as to improve it.
This doc is just for tables with no foreign keys; we are working on another doc on how we got those working too.
Best,
Richard Bondi
Matthew Stein
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Jump-start Instructions for installing dbforms on Windows 2000 with a MS SQL Server Database
Authors: Richard Bondi ([EMAIL PROTECTED])
& Matthew Stein ([EMAIL PROTECTED])
Created: 27 JAN 2003
Version: beta 1
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Introduction
============
This document was written to help you get dbforms up and running, no
more; hence the "jump-start" in the title.
Dbforms can be thought of *essentially* as an open source add-on for
the Tomcat servlet server that allows you to update, delete, insert,
search, and browse database tables via jsp pages, without having to
write these jsp pages. It is available at
http://jdbforms.sourceforge.net.
This file contains instructions for creating a dbforms installation for
MS SQL Server, for a single table that has no foreign keys, and an
identity column. We wrote it because the existing instructions are
spread out among different files, are missing for at least one important
step, and do not cover problems specific to Microsoft SQL Server.
We use a pre-release version of dbforms because in separate document, we
will describe how to use tables with foreign keys, and our solution only
works with the pre-release version.
In a number of places in this document, we confess our ignorance with
the disclaimer "but it works for us." We would be glad to be enlightened
by future readers, and to improve this document accordingly.
Please email any comments or enhancements to Richard Bondi
([EMAIL PROTECTED]).
For best display, use tabs of two spaces when viewing this document.
Richard Bondi
Matthew Stein
Requirements:
=============
Download all of these files (many of them in zips) to a temporary
location; you will be moving them to permanent locations as part of the
dbforms installation.
- Tomcat 4.0.6
- downloadable from http://jakarta.apache.org, Tomcat project
- use LE version, because you will already have j2sdk 2.0 v.1.4
installed.
- j2sdk 2.0 v.1.4
- http://java.sun.com
- dbforms 1.1.3pr1
- downloadable from http://jdbforms.sourceforge.net
- Microsoft JDBC driver for Microsoft SQL Server.
- downloadable from http://www.microsoft.com/sql/downloads
- Struts v. 1.0.2
- downloadable from http://jakarta.apache.org, Struts project
Currently, click the "Resources" link under "Download" part of the
Commons project at jakarta.apache.org.)(*i*)
- commons-beanutils.jar
- ver. 1.0
- downloadable from http://jdbforms.sourceforge.net, click "Resources"
under "Download"
- downloadable from http://jakarta.apache.org, Commons project
- commons-collections.jar
- ver. 1.0
- downloadable from http://jdbforms.sourceforge.net, click "Resources"
under "Download"
- downloadable from http://jakarta.apache.org, Commons project
- commons-digester.jar
- ver. 1.0
- downloadable from http://jdbforms.sourceforge.net, click "Resources"
under "Download"
- downloadable from http://jakarta.apache.org, Commons project
- commons-logging.jar
- ver. 1.0
- downloadable from http://jdbforms.sourceforge.net, click "Resources"
under "Download"
- downloadable from http://jakarta.apache.org, Commons project
- commons-validator.jar
- ver. ? (manifest file doesn't list version.)
- downloadable from http://jdbforms.sourceforge.net, click "Resources"
under "Download"
- downloadable from http://jakarta.apache.org, Commons project
- jakarta-oro-2.0.6.jar
- downloadable from http://jdbforms.sourceforge.net, click "Resources"
under "Download"
- downloadable from http://jakarta.apache.org, ORO project
- log4j.jar
- Implementation-Version: 1.1b2
- downloadable from http://jdbforms.sourceforge.net, click "Resources"
under "Download"
- downloadable from http://jakarta.apache.org, Log4j project
- cos.jar
- ver. ? (manifest file doesn't list version.)
- downloadable from http://jdbforms.sourceforge.net, click "Resources"
under "Download"
- downloadable from http://www.oreilly.com
Finally, you should also have enough knowledge of XML not to go "huh?"
when we write about "attributes", "elements" or "tags".
Install j2sdk 2.0 v1.4
======================
You must use this version for these dbforms instructions, because of its
xml/xsl classes.
Install MS JDBC Driver
======================
Install the Microsoft JDBC driver for Microsoft SQL Server:
- run the setup.exe.
- Currently, when you run the install, it identifies itself as a
Service Pack 1. That's ok, it includes the original JDBC driver
too.
Install Tomcat and create "dbf" webapp
======================================
- Just run the friendly install program.
- Note: we couldn't get the NT service option to work: we could never
start the service, so we suggest you skip it.
- Read and follow the RUNNING.txt file, included with the download,
but also availabe at http://jakarta.apache.org under the Tomcat
project (make sure you read the one for version 4.0.6).
- Edit the %CATALINA_HOME%\conf\server.xml file:
- Find the first Context element
- Add the following element:
<Context path="/dbf" docBase="dbf" debug="0"
reloadable="true"/>
- "path" is a relative virtual path on the server (e.g.
localhost/dbf)
- "docBase" is the directory where the files are located; this
is relative
off of %CATALINA_HOME%\webapps, but you can specify full paths
such
as c:\mydir\here.
- For more information, read the Tomcat docs.
- Create the directory specified in the "docBase" above.
- Create a sub-directory, WEB-INF
- Create a sub-directory, WEB-INF\lib
Create dbforms web.xml file
===========================
- Create a file called web.xml with the contents shown below, and put it
in the WEB-INF directory you just created. (*ii*)
!! WARNING !!
(You must manually remove any line breaks that occur in any
of the elements below. Formatting this file made them inevitable.)
:::::::::::::::::::::::::::: snip :::::::::::::::::::::::::::::::::
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application
2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd";>
<web-app>
<!--=========== DbForms Configuration Servlet ===========-->
<servlet>
<servlet-name>org.dbforms.ConfigServlet</servlet-name>
<servlet-class>org.dbforms.ConfigServlet</servlet-class>
<init-param>
<param-name>dbformsConfig</param-name>
<param-value>/WEB-INF/dbforms-config.xml</param-value>
</init-param>
<init-param>
<param-name>log4j.configuration</param-name>
<param-value>/WEB-INF/log4j.properties</param-value>
</init-param>
<init-param>
<param-name>resourceBundle</param-name>
<param-value>ApplicationResources</param-value>
</init-param>
<init-param>
<param-name>validation</param-name>
<param-value>/WEB-INF/validation.xml</param-value>
</init-param>
<init-param>
<param-name>validator-rules</param-name>
<param-value>/WEB-INF/validator-rules.xml</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet>
<servlet-name>control</servlet-name>
<servlet-class>org.dbforms.Controller</servlet-class>
<init-param>
<param-name>maxUploadSize</param-name>
<param-value>80000</param-value>
</init-param>
</servlet>
<!-- optional? Seems to work without this. rb, 1/23/2003.-->
<taglib>
<taglib-uri>/WEB-INF/dbforms.tld</taglib-uri>
<taglib-location>/WEB-INF/dbforms.tld</taglib-location>
</taglib>
</web-app>
:::::::::::::::::::::::::::: snip :::::::::::::::::::::::::::::::::
- Create a file index.html that contains simply "hello world" and put
it in %CATALINA_HOME%\webapps\dbf.
- Start Tomcat (run %CATALINA_HOME%\bin\startup.bat), and attempt to
load this file by going to
http://localhost:8080/dbf/index.html
- If there is a problem, it is a Tomcat problem. You'll have to solve
that on your own using all available Tomcat support resources. (Start
with the file RUNNING.txt.)
Get what web.xml needs
======================
A number of files are referred to in web.xml. We list them here by their
<param-name> names, and explain what to do about them.
* <param-name>dbformsConfig</param-name>
We create this file below, with devgui.
* <param-name>log4j.configuration</param-name>
Create a file called log4j.properties and put it into WEB-INF. It should
have the following contents:
!! WARNING !!
(You must manually remove any line breaks that occur in any
of the # comments below. Formatting this file made them inevitable.)
:::::::::::::::::::::::::::: snip :::::::::::::::::::::::::::::::::
#Log4j Configuation file
# Created by Matthew Stein, 1/14/2003
# Based out of text in
# http://www.javaworld.com/jw-11-2000/jw-1122-log4j-p2.html
# First, set the "rootCategory" so everything gets logged if necessary.
# (For multiple logs beyond those specified here, use , name format.
log4j.rootCategory=ALL, A2, A3
# Define A2
# Define writer
# Define layout
log4j.appender.A2=org.apache.log4j.ConsoleAppender
log4j.appender.A2.Target=System.out
log4j.appender.A2.layout=org.apache.log4j.PatternLayout
log4j.appender.A2.layout.ConversionPattern=%-4r [%t] %-5p %c %x - %m%n
#Define A3 [a/k/a A2 written to a file ]
# Define writer
# Define layout
log4j.appender.A3=org.apache.log4j.RollingFileAppender
log4j.appender.A3.File=/RFA.log log4j.appender.A3.MaxBackupIndex=10
log4j.appender.A3.MaxFileSize=1MB log4j.appender.A3.Append=True
log4j.appender.A3.layout=org.apache.log4j.PatternLayout
log4j.appender.A3.layout.ConversionPattern=%d{DATE} %F %p: %C(%x) - %m%n
:::::::::::::::::::::::::::: snip :::::::::::::::::::::::::::::::::
* <param-name>validation</param-name>,
<param-name>validator-rules</param-name>
We don't know what these are or where to get them,
but our Tomcat installation worked without them. We suspect they are
used for form validation, but we really have no clue.
* <taglib-uri>/WEB-INF/dbforms.tld</taglib-uri>
Put %DBFORMS_HOME%\dist\*.tld into WEB-INF
Copy required jars
===================
- Copy these jars, downloaded previously, into WEB-INF\lib:
- commons-beanutils.jar
- commons-collections.jar
- commons-digester.jar
- commons-logging.jar
- commons-validator.jar
- cos.jar
- jakarta-oro-2.0.6.jar
- log4j.jar
- Find the three jars that come with the MS SQL Server JDBC driver,and
copy them into WEB-INF\lib. Their names are:
- mssqlserver.jar
- msbase.jar
- msutil.jar
- Copy %DBFORMS_HOME%\dist\dbforms_v1_1_3pr1.jar into WEB-INF\lib
- From the struts zip file that you downloaded, extract only the
struts.jar file, and put it into WEB-INF\lib.
Prepare MS SQL Server
=====================
In this document, we will assume the following names for MS SQL Server;
just substitute those of your actual MS SQL Server installation:
server name: birdserver
database name: parrotDb
table name: PLUMAGE_TABLE
The table PLUMAGE_TABLE has the following properties:
- you are willing to modify its rows
- it has an identity column
- it has no foreign keys.
(In a different document, we describe how to use dbforms with tables
that have foreign keys.)
Create MS SQL Server user
=========================
In order for dbforms to point to parrotDb, and not some other default db
like "master", we create a user to whom we assign parrotDb as the
default. (This isn't strictly necessary, see footnote *v*; but we
chose to do it that way.)
We will create:
user on parrotDb: dbfuser
password: dbfpwd
user's default database: parrotDb
- Open Sql Enterprise Manager (*ii*)
- Click on the database parrotDb
- Double-click the Users icon on the right
- Right-click on the right pane and select New database user...
- Name: dbfuser
- SQL Server authentication password: dbfpwd
- Database: parrotDb - database
- Roles:
- public (assigned automatically)
- db_datareader
- db_datawrite
Generate dbforms-config.xml
===========================
- (optional) When you installed the Microsoft JDBC driver above, it
installed 3 jars (in the \lib directory underneath the installation
directory). Copy the three Microsoft JDBC jars to a simple, top level
directory (like c:\java\jars or something) now to avoid a horribly
illegible classpath.
- Put the three MS JDBC jars into your classpath
- Set DBFORMS_HOME to the directory you installed dbforms to, e.g.
c:\java\dbforms or whatever. (*iii*)
(These steps are described more generally in the Readme_DevGui (no
extension) in the %DBFORMS_HOME% directory. I have left out the xml/xsl
jars because j2sdk 2.0 1.4 already has stuff like that.)
- Run %DBFORMS_HOME%\bin\devgui.bat
You will see four tabs. Fill them out as follows: (Note that you can
save all the info we are about to type in into a reloadable file, via
the Project menu.)
- WebApps tab:
- Location of Webapp: If for example %CATALINA_HOME% is
c:\tc401, then this would be c:\tc401\webapps\dbf
- Web-URL of Webapp: for the installation described in this document,
this would be http://127.0.0.1:8080/dbf.
- Database tab:
- JDBC driver class: this what is inside the Java call
class.forName. You can find it inside the MS JDBC documentation. It
is:
com.microsoft.jdbc.sqlserver.SQLServerDriver
- JDBC URL: Again, this is in the help file. It is a parameter of
the Java JDBC DriverManager.getConnection call. It is:
jdbc:microsoft:sqlserver://yourServerName:1433
or in the example we are using:
jdbc:microsoft:sqlserver://birdserver:1433
(If your server is on another machine, you might want to use the full
DNS name, e.g. birdserver.internal.pets4all.com, not just birdserver.)
- Username and password:
In our example, these are "dbfuser" and "dbfpwd".
- Click Test connection.
- If unsuccessful, double-check the above values, and that the
MS JDBC jars are really in your classpath.
- XML Config tab
This tab is for creating the dbforms-config.xml. (*iv*)
- DbForms config file:
Specify the path and file name for the docBase you specified \WEB-
INF\dbforms-config.xml If you don't have one yet (and you shouldn't,
if your following these instructions for the first time)
- click Browse
- drill down to the directory specified in
docBase\WEB-INF
- type in "dbforms-config.xml" in the File Name box
- click Open
- ignore the error message.
Alternatively, you can just type in the exact path and file name
manually.
- Examine:
This is self-explanatory. Here you say whether you want to use
dbforms web pages to update/insert/delete tables, and/or views,
and/or system tables.
- choose Tables.
- Click the LOAD button.
- Catalogs:
In MS SQL Server, these are databases.
- Click "in catalog with name"
- select e.g. PLUMAGE_TABLE
- Schemas:
(Honestly, we don't know what these correspond to in MS SQL Server.
Just do what we did:)
- Choose dbo.
- Check "in schema with name pattern."
- Table names:
(You can use the LIKE wild cards % and _. For this example:)
- type PLUMAGE_TABLE for the table name
- Check "with table name pattern."
- Autocommit
- Check "use autocommit". (We don't know why, but it works.)
- Try to write standard types...
- Leave this unchecked. (We don't know why, but it works.)
- Foreign key detection
- For this example, check "deactivated".
- Include catalog/schema name in table name
- Leave unchecked. (*v*)
- Set Date Format to:
- Umm... We don't know whether this is just for how dates are
displayed, or for something else. We've treated it as a
display-
only choice; so choose your favorite.
- Click Generate Xml.
- Expand the window to see the generated xml.
- Edit the generated xml as follows:
- Find the
fieldType="int identity"
attribute. Change this to:
fieldType="int"
- If the same tag doesn't have an autoInc attribute, add:
autoInc="true"
- Delete any columns you don't want to display/edit via dbforms.
- Click Save.
This saves the xml to the xml into the file you specified
at the top of the dialog, as
the specified docBase\WEB-INF\dbforms-config.xml
- XSL Transformation tab
In devgui, click the fourth tab, XSL Transformation. From this dialog,
DevGui simply transforms the dbforms-config.xml file with each of the
xsl style sheets listed. Each style sheet produces a different jsp or
set of jsps. (*vi*)
That's the beauty of dbforms: if you have 20 tables, you don't have to
lose your mind generating custom jsps for each one; instead, you come
to this dialog and generate them all.
- Stylesheet directory:
This will already be set to where the xsls are in the dbforms
directory.
- Use JavaScript Calendar
May or may not work on your browser. Allows you to select a date
from a pop-up calendar instead of typing in the date. Check if
you're feeling moderately adventurous.
- For each xsl stylesheet listed:
- click on it to highlight it
- click "start transformation!"
- Do *not* edit and/or save the xsl stylesheets displayed at the
bottom of the dialog.
Use it
======
- Stop and start tomcat
- Point your browser to:
http://localhost:8080/dbf/menu.jsp
- If you get a crash, go to the DOS window that opens whenever you
launch Tomcat. Scrutinize all the errors and other logging in it.
Your log4j.properties file is maximizing the logging information.
This is always the place to start when trying to debug dbforms.
- Click View Normal button
Note that the next page will take a long time to load. This will be
true of all first pages while Tomcat compiles them for the first
time.
You should see all rows of the table PLUMAGE_TABLE.
- Click all the buttons on menu.jsp; you should be able to figure out
what they do from there.
Conclusion
==========
This document was written to help you get dbforms up and running, no
more; hence the "jump-start" in the title. For example,if you want to
know how to display more rows at a time in Edit Range, we refer you to
the dbforms documentation. Or to configure log4j to customize logging,
see the log4j documentation.
Once again, please email all suggestions and improvements for this
document to [EMAIL PROTECTED]
Footnotes
=========
(*i*) I don't know the difference between a "version" and an
"implementation version", as in the case of log4j.jar. Anyone who knows,
please let me know.
(*ii*) Please note that you will not find this file's full contents in
the documentation. Different parts of the dbforms documentation contain
parts of, but not all of it; an essential part is missing
altogether and only in the mailing list archive.
(*iii*) Shortcut: Instead of right-clicking on My Computer and selecting
Properties, hold down the Windows key and press the Break key. Don't
forget that after making changes to environment variables only affect
new "cmd" dos windows, not any that are already open.
(*iv*) You can actually call it whatever you want, since you specify the
file's name in your web.xml file, in the <param-name>
dbformsConfig</param-name>.
(*v*) There are two reasons why you might check this. -First, it is
a way to ensure that you are referring to the db you want, in this case
"parrotDb". In this example, we chose to do this by creating a Sql
Server user whose default database we can specify; that way, when Java
connects to the db via JDBC, we guarantee that "parrotDb" will be used.
Instead, you could choose any user (with appropriate permissions), not
specify its default db, and instead explicitly state the database and
user by checking "Include Schema name in table name." Unfortunately, we
are baffled by what a Schema name is -- the list of them in devgui
doesn't make sense to us. But that's our problem, not devgui's or Sql
Server's. Second, if you generate dbforms pages for more than one
"catalog" (a MS SQL Server database), then of course you have to specify
the catalog of each table. Otherwise neither JDBC nor SQL Server will
know which db to look for the table in. We haven't tried this, and we
hope never to have to!
(*vi*) If you know xsl, feel free to edit them to your heart's delight,
and store your edited ones in a different directory: just select that
directory here before you start transforming.
end of document
<<< text/plain; charset="us-ascii"; format=flowed: Unrecognized >>>
