Hi All--
I'm having problems with what I thought was going to be a
very simple project and, after trying my best, find I must
now swallow my pride and *plead* for help.
The "simple" project: Updating a single SQL table using an
online form with fields that correspond exactly to those of
the table (i.e. no business logic). The same form serves
for both input and display of the data. There are about 150
fields in the form and table.
The problem: Depending on which approach I use, I get bad
data in my database, errors doing an update, extremely slow
execution of an update, and/or a complete blow-up of Java
and Tomcat.
My configuration:
RedHat 7.2
J2SDK1.4.0
Jakarta-Tomcat-4.0.3
JSTL 1.0 Release Candiate 1
PostgreSQL-7.1.3
The approaches I've tried and the problems they've given me:
1. The KISS approach
I first tried a simple:
UPDATE programs
SET org = '<c:out value="${param.org}" default="" />',
name = '<c:out value="${param.name}" default="" />',
. . . .
WHERE progKey = '<c:out value="${param.progCode}" />'
Using the <c:out> tag has the beauty of allowing me to set
default values for any undefined fields. The tag also
defaults to escapeXml="true" which escapes any single
quotes in the data which would otherwise prematurely end
the quoted string.
This approach works great and is superfast -- virtually
instantaneous (and this for an update of all 150 fields in
the record!).
The problem is that the escapeXml mechanism is really
designed for data flowing in the other direction (from a
database to a web page). The single quote and other
characters (double-quotes, angle brackets, and ampersand)
are encoded into ampersand-escaped "character-entity" codes
designed for HTML output. This poses a slight problem when
I output the data into an HTML form text field, where the
escape coding is displayed literally. It poses a greater
problem when that same data is stored a second time and all
the ampersands in the previous escapeXml encoding are
escaped again (eventually resulting in &amp;amp;...).
Conclusion: We need an "escapeSql" attribute for the c:out
tag. This would double any single quotes in the data.
2. The "right" way.
After consulting the archives for this group, I realized
that I should be using the <param> tag with my updates to
handle the embedded single quotes in my data.
UPDATE programs
SET org = ?,
name = ?,
. . . .
WHERE progKey = ?
<sql:param value="${param.org}" />
<sql:param value="${param.name}" />
. . .
<sql:param value="${param.progCode}" />
Rather than fix the problem, this created
NullPointerExceptions when I performed an update:
: Failed to store object - Exception:
java.lang.NullPointerException
Stack Trace:
java.lang.NullPointerException at
org.postgresql.Connection.putObject(Connection.java:790)
at
org.postgresql.jdbc2.PreparedStatement.setObject(PreparedStatement.java:596)
at
org.apache.taglibs.standard.tag.common.sql.UpdateTagSupport.setParameters(UpdateTagSupport.java:285)
at
org.apache.taglibs.standard.tag.common.sql.UpdateTagSupport.doEndTag(UpdateTagSupport.java:192)
at
I finally traced this problem to undefined request
parameter fields, specifically radio buttons and check
boxes (i.e. whenever a check box was left unchecked or when
the user didn't select any of a set of radio buttons).
Observation: The error message is pretty cryptic. Any way
the name of the undefined parameter in question could be
displayed?
Conclusion: The param tag should have a default attribute
similar to the c:out tag.
2a. Updating in smaller bites.
Somewhere in debugging the above, I started blowing up the
JVM with each update:
# HotSpot Virtual Machine Error, Internal Error
# Please report this error at
# http://java.sun.com/cgi-bin/bugreport.cgi
#
# Java VM: Java HotSpot(TM) Client VM (1.4.0-b92 mixed mode)
#
# Error ID: 47454E45524154452F4F502D41500E4350500848
#
# Problematic Thread: prio=1 tid=0x0x89461f8 nid=0xd2b
runnable
I solved this by breaking my update into two statements.
(I'm updating about 150 fields in the table.) I'm thinking
the problem may be due to my memory (256M) or perhaps a
limitation in JSTL (maybe 128 fields?). I had no problem in
updating all 150 fields using the KISS method (No. 1 above).
At any rate, I can live with this workaround.
3. Form validation via JSP
Clearly I had to validate the checkbox and radio button
fields to ensure they were defined before proceeding with
my update. So I added statements like the following (which
is validation for a checkbox named "gym"):
<c:set var="gym" value="${param.gym}" />
<c:if test="${empty gym}" >
<c:set var="gym" value="No" />
</c:if>
(And I changed the corresponding param statement to refer
to "gym" instead of "param.gym")
I repeated this 16 times , once for each checkbox and set
of radio buttons in my form.
This works.
The only problem is that an update that previously took
less than one second now takes 55 seconds! Not a good thing.
I hope I am doing something terribly wrong with my
validation. Someone please tell me that JSP tags are not
inherently this slow.
Summarizing:
Conclusion: We need an "escapeSql" attribute for the c:out
tag. This would double any single quotes in the data.
Observation: The error message for a NullPointerException
is pretty cryptic. Any way the name of the undefined
parameter in question could be displayed?
Conclusion: The param tag should have a default attribute
similar to the c:out tag.
Big problem: An update that took one second has gone to 55
seconds by adding param tags and validation of 16 fields.
Am I doing something terribly wrong with my validation?
My configuration:
RedHat 7.2
J2SDK1.4.0
Jakarta-Tomcat-4.0.3
JSTL 1.0 Release Candiate 1
PostgreSQL-7.1.3
Any help, advice, observations, etc. would be greatly
appreciated. TIA.
--John Hicks
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
