Re: More SQL Date problems
Hans,
I looked into the SQL problem and consulted with the JDBC specification
lead, Jonathan Bruce. Jonathan said that what JSTL is doing is correct:
when setObject(index, null) is passed in a null value this should be
converted by the driver to an SQL null.
This behavior is in fact enforced as part of the J2EE compatibility in
the CTS. The JDBC Driver Test Suite is publicly accessible and can be
used to weed out the JDBC drivers that are not compatible.
Thanks,
Justyna
Hans Bergsten wrote:
Wolfgang Röckelein wrote:
Hi,
at JDBC level there are two different possibilities to set a
parameter value to null: with setNull and setting to null. Depending
on the driver sometimes only on of these methods work, and when it
does not work, you see the java.sql.SQLException: Invalid column
type error you see.
I think this was already changed or discussed sometime during the
standard taglib development.
Right. I was looking at the code for JSTL in the CVS archive, and it
calls setObject(index, null) when passed a null value, and there's a
comment that this should be converted by the driver to an SQL null.
Browsing through the JDBC JavaDocs and the JDBC spec, there seems to
be some support for this claim, but it's not 100% clear. It's possible
that the driver Keith is using doesn't handle it, and maybe it would
be better if JSTL used setNull(). The reason it doesn't is that it
would required additional type info for the sql:param case.
Pierre, this may be something to look at again for JSTL.next.
Keith, a work-around for this would be:
fmt:parseDate value=${param.dob} var=parsed_dob
pattern=dd-MM- /
c:choose
c:when test=${!empty parsed_dob}
sql:update
INSERT INTO resource_registry (resource_id, dob)
VALUES (res_id_seq.NEXTVAL, ? )
sql:dateParam value=${parsed_dob} type=date/
/sql:update
/c:when
c:otherwise
INSERT INTO resource_registry (resource_id)
VALUES (res_id_seq.NEXTVAL)
/sql:update
/c:otherwise
/c:choose
If the real case involves many parameters that may be null, this
gets ugly, but if it's just this one, it may be okay.
Hans
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: More SQL Date problems
So this is something in the Oracle JDBC driver that needs to be fixed? Just wondering
whether I have to watch for a new JDBC driver or the next JSTL update to solve the
problem.
Right now I'm using a workaround similar to what Hans showed me and things are working
fine. Just may be confusing for whoever may take over for me in the future if they
don't
know about the problem.
Thanks!
Keith
-- Original Message ---
From: Justyna Horwat [EMAIL PROTECTED]
To: Tag Libraries Users List [EMAIL PROTECTED]
Sent: Fri, 16 Apr 2004 09:54:13 -0700
Subject: Re: More SQL Date problems
Hans,
I looked into the SQL problem and consulted with the JDBC specification
lead, Jonathan Bruce. Jonathan said that what JSTL is doing is correct:
when setObject(index, null) is passed in a null value this should be
converted by the driver to an SQL null.
This behavior is in fact enforced as part of the J2EE compatibility in
the CTS. The JDBC Driver Test Suite is publicly accessible and can be
used to weed out the JDBC drivers that are not compatible.
Thanks,
Justyna
Hans Bergsten wrote:
Wolfgang Röckelein wrote:
Hi,
at JDBC level there are two different possibilities to set a
parameter value to null: with setNull and setting to null. Depending
on the driver sometimes only on of these methods work, and when it
does not work, you see the java.sql.SQLException: Invalid column
type error you see.
I think this was already changed or discussed sometime during the
standard taglib development.
Right. I was looking at the code for JSTL in the CVS archive, and it
calls setObject(index, null) when passed a null value, and there's a
comment that this should be converted by the driver to an SQL null.
Browsing through the JDBC JavaDocs and the JDBC spec, there seems to
be some support for this claim, but it's not 100% clear. It's possible
that the driver Keith is using doesn't handle it, and maybe it would
be better if JSTL used setNull(). The reason it doesn't is that it
would required additional type info for the sql:param case.
Pierre, this may be something to look at again for JSTL.next.
Keith, a work-around for this would be:
fmt:parseDate value=${param.dob} var=parsed_dob
pattern=dd-MM- /
c:choose
c:when test=${!empty parsed_dob}
sql:update
INSERT INTO resource_registry (resource_id, dob)
VALUES (res_id_seq.NEXTVAL, ? )
sql:dateParam value=${parsed_dob} type=date/
/sql:update
/c:when
c:otherwise
INSERT INTO resource_registry (resource_id)
VALUES (res_id_seq.NEXTVAL)
/sql:update
/c:otherwise
/c:choose
If the real case involves many parameters that may be null, this
gets ugly, but if it's just this one, it may be okay.
Hans
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--- End of Original Message ---
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: More SQL Date problems
Justyna Horwat wrote:
Hans,
I looked into the SQL problem and consulted with the JDBC specification
lead, Jonathan Bruce. Jonathan said that what JSTL is doing is correct:
when setObject(index, null) is passed in a null value this should be
converted by the driver to an SQL null.
This behavior is in fact enforced as part of the J2EE compatibility in
the CTS. The JDBC Driver Test Suite is publicly accessible and can be
used to weed out the JDBC drivers that are not compatible.
Thanks Justyna! So, it sounds like the case is closed. A JDBC driver
that doesn't handle setObject(index, null) the way JSTL expects isn't
spec compliant.
Hans
Hans Bergsten wrote:
Wolfgang Röckelein wrote:
Hi,
at JDBC level there are two different possibilities to set a
parameter value to null: with setNull and setting to null. Depending
on the driver sometimes only on of these methods work, and when it
does not work, you see the java.sql.SQLException: Invalid column
type error you see.
I think this was already changed or discussed sometime during the
standard taglib development.
Right. I was looking at the code for JSTL in the CVS archive, and it
calls setObject(index, null) when passed a null value, and there's a
comment that this should be converted by the driver to an SQL null.
Browsing through the JDBC JavaDocs and the JDBC spec, there seems to
be some support for this claim, but it's not 100% clear. It's possible
that the driver Keith is using doesn't handle it, and maybe it would
be better if JSTL used setNull(). The reason it doesn't is that it
would required additional type info for the sql:param case.
Pierre, this may be something to look at again for JSTL.next.
Keith, a work-around for this would be:
fmt:parseDate value=${param.dob} var=parsed_dob
pattern=dd-MM- /
c:choose
c:when test=${!empty parsed_dob}
sql:update
INSERT INTO resource_registry (resource_id, dob)
VALUES (res_id_seq.NEXTVAL, ? )
sql:dateParam value=${parsed_dob} type=date/
/sql:update
/c:when
c:otherwise
INSERT INTO resource_registry (resource_id)
VALUES (res_id_seq.NEXTVAL)
/sql:update
/c:otherwise
/c:choose
If the real case involves many parameters that may be null, this
gets ugly, but if it's just this one, it may be okay.
Hans
--
Hans Bergsten[EMAIL PROTECTED]
Gefion Software http://www.gefionsoftware.com/
Author of O'Reilly's JavaServer Pages, covering JSP 2.0 and JSTL 1.1
Author of O'Reilly's JavaServer Faces, covering JSF 1.0
Details athttp://TheJSPBook.com/
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: More SQL Date problems
Keith,
That's why they have the compatibility tests to avoid the very problem
that you are seeing with the Oracle driver. This is definitely a bug in
Oracle's driver implementation that needs to be fixed.
Justyna
Keith wrote:
So this is something in the Oracle JDBC driver that needs to be fixed? Just wondering
whether I have to watch for a new JDBC driver or the next JSTL update to solve the
problem.
Right now I'm using a workaround similar to what Hans showed me and things are working
fine. Just may be confusing for whoever may take over for me in the future if they don't
know about the problem.
Thanks!
Keith
-- Original Message ---
From: Justyna Horwat [EMAIL PROTECTED]
To: Tag Libraries Users List [EMAIL PROTECTED]
Sent: Fri, 16 Apr 2004 09:54:13 -0700
Subject: Re: More SQL Date problems
Hans,
I looked into the SQL problem and consulted with the JDBC specification
lead, Jonathan Bruce. Jonathan said that what JSTL is doing is correct:
when setObject(index, null) is passed in a null value this should be
converted by the driver to an SQL null.
This behavior is in fact enforced as part of the J2EE compatibility in
the CTS. The JDBC Driver Test Suite is publicly accessible and can be
used to weed out the JDBC drivers that are not compatible.
Thanks,
Justyna
Hans Bergsten wrote:
Wolfgang Röckelein wrote:
Hi,
at JDBC level there are two different possibilities to set a
parameter value to null: with setNull and setting to null. Depending
on the driver sometimes only on of these methods work, and when it
does not work, you see the java.sql.SQLException: Invalid column
type error you see.
I think this was already changed or discussed sometime during the
standard taglib development.
Right. I was looking at the code for JSTL in the CVS archive, and it
calls setObject(index, null) when passed a null value, and there's a
comment that this should be converted by the driver to an SQL null.
Browsing through the JDBC JavaDocs and the JDBC spec, there seems to
be some support for this claim, but it's not 100% clear. It's possible
that the driver Keith is using doesn't handle it, and maybe it would
be better if JSTL used setNull(). The reason it doesn't is that it
would required additional type info for the sql:param case.
Pierre, this may be something to look at again for JSTL.next.
Keith, a work-around for this would be:
fmt:parseDate value=${param.dob} var=parsed_dob
pattern=dd-MM- /
c:choose
c:when test=${!empty parsed_dob}
sql:update
INSERT INTO resource_registry (resource_id, dob)
VALUES (res_id_seq.NEXTVAL, ? )
sql:dateParam value=${parsed_dob} type=date/
/sql:update
/c:when
c:otherwise
INSERT INTO resource_registry (resource_id)
VALUES (res_id_seq.NEXTVAL)
/sql:update
/c:otherwise
/c:choose
If the real case involves many parameters that may be null, this
gets ugly, but if it's just this one, it may be okay.
Hans
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--- End of Original Message ---
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: More SQL Date problems
Alright. Thanks a lot, everyone! I'll be contacting Oracle Support next week to see
about
getting the issue resolved. :)
Keith
-- Original Message ---
From: Justyna Horwat [EMAIL PROTECTED]
To: Tag Libraries Users List [EMAIL PROTECTED]
Sent: Fri, 16 Apr 2004 11:24:32 -0700
Subject: Re: More SQL Date problems
Keith,
That's why they have the compatibility tests to avoid the very problem
that you are seeing with the Oracle driver. This is definitely a bug in
Oracle's driver implementation that needs to be fixed.
Justyna
Keith wrote:
So this is something in the Oracle JDBC driver that needs to be fixed? Just
wondering
whether I have to watch for a new JDBC driver or the next JSTL update to solve the
problem.
Right now I'm using a workaround similar to what Hans showed me and things are
working
fine. Just may be confusing for whoever may take over for me in the future if they
don't
know about the problem.
Thanks!
Keith
-- Original Message ---
From: Justyna Horwat [EMAIL PROTECTED]
To: Tag Libraries Users List [EMAIL PROTECTED]
Sent: Fri, 16 Apr 2004 09:54:13 -0700
Subject: Re: More SQL Date problems
Hans,
I looked into the SQL problem and consulted with the JDBC specification
lead, Jonathan Bruce. Jonathan said that what JSTL is doing is correct:
when setObject(index, null) is passed in a null value this should be
converted by the driver to an SQL null.
This behavior is in fact enforced as part of the J2EE compatibility in
the CTS. The JDBC Driver Test Suite is publicly accessible and can be
used to weed out the JDBC drivers that are not compatible.
Thanks,
Justyna
Hans Bergsten wrote:
Wolfgang Röckelein wrote:
Hi,
at JDBC level there are two different possibilities to set a
parameter value to null: with setNull and setting to null. Depending
on the driver sometimes only on of these methods work, and when it
does not work, you see the java.sql.SQLException: Invalid column
type error you see.
I think this was already changed or discussed sometime during the
standard taglib development.
Right. I was looking at the code for JSTL in the CVS archive, and it
calls setObject(index, null) when passed a null value, and there's a
comment that this should be converted by the driver to an SQL null.
Browsing through the JDBC JavaDocs and the JDBC spec, there seems to
be some support for this claim, but it's not 100% clear. It's possible
that the driver Keith is using doesn't handle it, and maybe it would
be better if JSTL used setNull(). The reason it doesn't is that it
would required additional type info for the sql:param case.
Pierre, this may be something to look at again for JSTL.next.
Keith, a work-around for this would be:
fmt:parseDate value=${param.dob} var=parsed_dob
pattern=dd-MM- /
c:choose
c:when test=${!empty parsed_dob}
sql:update
INSERT INTO resource_registry (resource_id, dob)
VALUES (res_id_seq.NEXTVAL, ? )
sql:dateParam value=${parsed_dob} type=date/
/sql:update
/c:when
c:otherwise
INSERT INTO resource_registry (resource_id)
VALUES (res_id_seq.NEXTVAL)
/sql:update
/c:otherwise
/c:choose
If the real case involves many parameters that may be null, this
gets ugly, but if it's just this one, it may be okay.
Hans
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--- End of Original Message ---
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--- End of Original Message ---
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: More SQL Date problems
Justyna,
This is also a secret/poorly documented property in the Oracle-thin
driver that you can set to force the driver to operate in a CTS
compliant mode. Lance - can you remember the exact property ?
-Jonathan
Justyna Horwat wrote:
Keith,
That's why they have the compatibility tests to avoid the very problem
that you are seeing with the Oracle driver. This is definitely a bug in
Oracle's driver implementation that needs to be fixed.
Justyna
Keith wrote:
So this is something in the Oracle JDBC driver that needs to be fixed?
Just wondering whether I have to watch for a new JDBC driver or the
next JSTL update to solve the problem.
Right now I'm using a workaround similar to what Hans showed me and
things are working fine. Just may be confusing for whoever may take
over for me in the future if they don't know about the problem.
Thanks!
Keith
-- Original Message ---
From: Justyna Horwat [EMAIL PROTECTED]
To: Tag Libraries Users List [EMAIL PROTECTED]
Sent: Fri, 16 Apr 2004 09:54:13 -0700
Subject: Re: More SQL Date problems
Hans,
I looked into the SQL problem and consulted with the JDBC
specification lead, Jonathan Bruce. Jonathan said that what JSTL is
doing is correct: when setObject(index, null) is passed in a null
value this should be converted by the driver to an SQL null.
This behavior is in fact enforced as part of the J2EE compatibility
in the CTS. The JDBC Driver Test Suite is publicly accessible and can
be used to weed out the JDBC drivers that are not compatible.
Thanks,
Justyna
Hans Bergsten wrote:
Wolfgang Röckelein wrote:
Hi,
at JDBC level there are two different possibilities to set a
parameter value to null: with setNull and setting to null.
Depending on the driver sometimes only on of these methods work,
and when it does not work, you see the java.sql.SQLException:
Invalid column type error you see.
I think this was already changed or discussed sometime during the
standard taglib development.
Right. I was looking at the code for JSTL in the CVS archive, and it
calls setObject(index, null) when passed a null value, and there's a
comment that this should be converted by the driver to an SQL null.
Browsing through the JDBC JavaDocs and the JDBC spec, there seems to
be some support for this claim, but it's not 100% clear. It's possible
that the driver Keith is using doesn't handle it, and maybe it would
be better if JSTL used setNull(). The reason it doesn't is that it
would required additional type info for the sql:param case.
Pierre, this may be something to look at again for JSTL.next.
Keith, a work-around for this would be:
fmt:parseDate value=${param.dob} var=parsed_dob
pattern=dd-MM- /
c:choose
c:when test=${!empty parsed_dob}
sql:update
INSERT INTO resource_registry (resource_id, dob)
VALUES (res_id_seq.NEXTVAL, ? )
sql:dateParam value=${parsed_dob} type=date/
/sql:update
/c:when
c:otherwise
INSERT INTO resource_registry (resource_id)
VALUES (res_id_seq.NEXTVAL)
/sql:update
/c:otherwise
/c:choose
If the real case involves many parameters that may be null, this
gets ugly, but if it's just this one, it may be okay.
Hans
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--- End of Original Message ---
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: More SQL Date problems
-Doracle.jdbc.J2EE13Compliant=true is the property that must be set.
You need to use Oracle 9.2.0.3 or greater btw for the JDBC driver version.
You also need to modify your init.ora to include compatible=9.0.0.0.0
This works great with the Oracle ojdbc.jar
or the classes12dms.jar and dms2Server.jar (part of o4j)
regards
lance
Jonathan Bruce wrote:
Justyna,
This is also a secret/poorly documented property in the Oracle-thin
driver that you can set to force the driver to operate in a CTS
compliant mode. Lance - can you remember the exact property ?
-Jonathan
Justyna Horwat wrote:
Keith,
That's why they have the compatibility tests to avoid the very
problem that you are seeing with the Oracle driver. This is
definitely a bug in Oracle's driver implementation that needs to be
fixed.
Justyna
Keith wrote:
So this is something in the Oracle JDBC driver that needs to be
fixed? Just wondering whether I have to watch for a new JDBC driver
or the next JSTL update to solve the problem.
Right now I'm using a workaround similar to what Hans showed me and
things are working fine. Just may be confusing for whoever may take
over for me in the future if they don't know about the problem.
Thanks!
Keith
-- Original Message ---
From: Justyna Horwat [EMAIL PROTECTED]
To: Tag Libraries Users List [EMAIL PROTECTED]
Sent: Fri, 16 Apr 2004 09:54:13 -0700
Subject: Re: More SQL Date problems
Hans,
I looked into the SQL problem and consulted with the JDBC
specification lead, Jonathan Bruce. Jonathan said that what JSTL is
doing is correct: when setObject(index, null) is passed in a null
value this should be converted by the driver to an SQL null.
This behavior is in fact enforced as part of the J2EE compatibility
in the CTS. The JDBC Driver Test Suite is publicly accessible and
can be used to weed out the JDBC drivers that are not compatible.
Thanks,
Justyna
Hans Bergsten wrote:
Wolfgang Röckelein wrote:
Hi,
at JDBC level there are two different possibilities to set a
parameter value to null: with setNull and setting to null.
Depending on the driver sometimes only on of these methods work,
and when it does not work, you see the java.sql.SQLException:
Invalid column type error you see.
I think this was already changed or discussed sometime during the
standard taglib development.
Right. I was looking at the code for JSTL in the CVS archive, and it
calls setObject(index, null) when passed a null value, and there's a
comment that this should be converted by the driver to an SQL null.
Browsing through the JDBC JavaDocs and the JDBC spec, there seems to
be some support for this claim, but it's not 100% clear. It's
possible
that the driver Keith is using doesn't handle it, and maybe it would
be better if JSTL used setNull(). The reason it doesn't is that it
would required additional type info for the sql:param case.
Pierre, this may be something to look at again for JSTL.next.
Keith, a work-around for this would be:
fmt:parseDate value=${param.dob} var=parsed_dob
pattern=dd-MM- /
c:choose
c:when test=${!empty parsed_dob}
sql:update
INSERT INTO resource_registry (resource_id, dob)
VALUES (res_id_seq.NEXTVAL, ? )
sql:dateParam value=${parsed_dob} type=date/
/sql:update
/c:when
c:otherwise
INSERT INTO resource_registry (resource_id)
VALUES (res_id_seq.NEXTVAL)
/sql:update
/c:otherwise
/c:choose
If the real case involves many parameters that may be null, this
gets ugly, but if it's just this one, it may be okay.
Hans
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--- End of Original Message ---
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
RE: More SQL Date problems
(CoyoteAdapter.java:206)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:833)
at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection
(Http11Protocol.java:732)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:619)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run
(ThreadPool.java:688)
at java.lang.Thread.run(Thread.java:534)
-- Original Message ---
From: Martin van Dijken [EMAIL PROTECTED]
To: Tag Libraries Users List [EMAIL PROTECTED]
Sent: Wed, 31 Mar 2004 08:58:24 +0200
Subject: RE: More SQL Date problems
Hmm Keith,
Can you specify the url you're using to connect to the database? * out the
uid and psw of course... Also can you post a stack trace? I did a few
Googles on your exception and heard some people shouting the oracle thin
driver should work best. It's what I have used in the past and I've not had
any headaches with it.
Martin
-Oorspronkelijk bericht-
Van: Keith [mailto:[EMAIL PROTECTED]
Verzonden: woensdag 31 maart 2004 5:55
Aan: Tag Libraries Users List
Onderwerp: Re: More SQL Date problems
Yeah, I'd read about not using that bridge driver. I'm using Oracle 9i
Release 2, J2SE
v1.4.2, and the JDBC Oracle Driver for JDK 1.4.
Keith
-- Original Message ---
From: Hans Bergsten [EMAIL PROTECTED]
To: Tag Libraries Users List [EMAIL PROTECTED]
Sent: Tue, 30 Mar 2004 20:40:41 -0800
Subject: Re: More SQL Date problems
Keith wrote:
Ahhh, ok. I assumed it was the JSTL stuff throwing the error. I've not
done much
programming in actual Java, which is why I'm using the JSTL library.
The field is set to accept a NULL value in the database. There's already
data in the
database, and there's many DOB's missing for some people. I can do
inserts to the
database from SQL*Plus fine and leave the date fields blank. It's just
when I'm
trying
to do it via JSP. I'm trying to design an new interface to the database
and this has
been my roadblock for the week.
Okay. Which JDBC driver are you using? If it's the JDBC-ODBC bridge, I
suggest you try with a real Oracle JDBC type 3 or 4 driver instead. The
bridge driver is buggy and not intended for production use.
Hans
-- Original Message ---
From: Hans Bergsten [EMAIL PROTECTED]
To: Tag Libraries Users List [EMAIL PROTECTED]
Sent: Tue, 30 Mar 2004 19:30:44 -0800
Subject: Re: More SQL Date problems
Keith wrote:
Now I'm thorougly confused. I could've sworn this was working before
(as I claimed
in my
first email to the group.
fmt:parseDate value=${param.dob} var=parsed_dob
pattern=dd-MM- /
sql:transaction
sql:update
INSERT INTO resource_registry ( dob )
VALUES (? sql:dateParam value=${parsed_dob} type=date/ )
/sql:update
/sql:transaction
This works perfectly fine when I put a date in the format specified in
the
parseDate
action. The JSP book I got (O'Reilly 3rd Ed) says the sql:dateParam
action is
supposed
to set the value to an SQL NULL when a null value is provided to it. I
keep getting
an
Invalid Column Type SQL exception (not an Oracle error) back whenever I
leave the
date
field blank. Anyone know what's wrong? Thanks!
It looks to me as if the Invalid Column Type SQL exception indeed
comes from the database (or the JDBC driver), because nothing in JSTL
can issue such an error message (JSTL doesn't have enough info; it
just relays the error issued by the JDBC driver).
JSTL sets the parameter in the SQL statement to SQL NULL if the
sql:dateParam value is null (according to the spec; bugs in an
implementation is a different story). One possible reason for the
error you get is that the column isn't declared to accept a NULL
value. Check the database table constraints.
Hans
--
Hans Bergsten[EMAIL PROTECTED]
Gefion Software http://www.gefionsoftware.com/
Author of O'Reilly's JavaServer Pages, covering JSP 2.0 and JSTL 1.1
Details athttp://TheJSPBook.com/
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--- End of Original Message ---
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--- End of Original Message ---
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e
Re: More SQL Date problems
Wolfgang Röckelein wrote:
Hi,
at JDBC level there are two different possibilities to set a parameter
value to null: with setNull and setting to null. Depending on the driver
sometimes only on of these methods work, and when it does not work, you
see the java.sql.SQLException: Invalid column type error you see.
I think this was already changed or discussed sometime during the
standard taglib development.
Right. I was looking at the code for JSTL in the CVS archive, and it
calls setObject(index, null) when passed a null value, and there's a
comment that this should be converted by the driver to an SQL null.
Browsing through the JDBC JavaDocs and the JDBC spec, there seems to
be some support for this claim, but it's not 100% clear. It's possible
that the driver Keith is using doesn't handle it, and maybe it would
be better if JSTL used setNull(). The reason it doesn't is that it
would required additional type info for the sql:param case.
Pierre, this may be something to look at again for JSTL.next.
Keith, a work-around for this would be:
fmt:parseDate value=${param.dob} var=parsed_dob
pattern=dd-MM- /
c:choose
c:when test=${!empty parsed_dob}
sql:update
INSERT INTO resource_registry (resource_id, dob)
VALUES (res_id_seq.NEXTVAL, ? )
sql:dateParam value=${parsed_dob} type=date/
/sql:update
/c:when
c:otherwise
INSERT INTO resource_registry (resource_id)
VALUES (res_id_seq.NEXTVAL)
/sql:update
/c:otherwise
/c:choose
If the real case involves many parameters that may be null, this
gets ugly, but if it's just this one, it may be okay.
Hans
--
Hans Bergsten[EMAIL PROTECTED]
Gefion Software http://www.gefionsoftware.com/
Author of O'Reilly's JavaServer Pages, covering JSP 2.0 and JSTL 1.1
Details athttp://TheJSPBook.com/
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: More SQL Date problems
Thanks a lot, Hans! Yeah, I had seen a workaround like that would work. I groaned when
I
realized that, though. Some other forms I'm working on have a lot more date fields
(two
have 15 each), so it would get pretty ugly.
The other problem comes in when/if the date column data needs to be deleted. I have
another UPDATE form that pulls the existing data from the database and fills in the
form. People can then edit that entry. If the sql:dateParam tag worked like it's
supposed
to, they could just erase the date in the desired form field, and the JSTL tag would
set
it to NULL, effectively deleting it. The workaround wouldn't work in this case,
because
the field is not originally empty. Fortunately, in the case of my application, dates
never should be deleted once entered. Mine is a unique case, though, and is
effectively
using what seems to be a bug to keep the date in the database. :p
Is there any chance of this problem being fixed in the case of the Oracle JDBC driver?
Keith
-- Original Message ---
From: Hans Bergsten [EMAIL PROTECTED]
To: Tag Libraries Users List [EMAIL PROTECTED]
Sent: Wed, 31 Mar 2004 12:18:19 -0800
Subject: Re: More SQL Date problems
Wolfgang Röckelein wrote:
Hi,
at JDBC level there are two different possibilities to set a parameter
value to null: with setNull and setting to null. Depending on the driver
sometimes only on of these methods work, and when it does not work, you
see the java.sql.SQLException: Invalid column type error you see.
I think this was already changed or discussed sometime during the
standard taglib development.
Right. I was looking at the code for JSTL in the CVS archive, and it
calls setObject(index, null) when passed a null value, and there's a
comment that this should be converted by the driver to an SQL null.
Browsing through the JDBC JavaDocs and the JDBC spec, there seems to
be some support for this claim, but it's not 100% clear. It's possible
that the driver Keith is using doesn't handle it, and maybe it would
be better if JSTL used setNull(). The reason it doesn't is that it
would required additional type info for the sql:param case.
Pierre, this may be something to look at again for JSTL.next.
Keith, a work-around for this would be:
fmt:parseDate value=${param.dob} var=parsed_dob
pattern=dd-MM- /
c:choose
c:when test=${!empty parsed_dob}
sql:update
INSERT INTO resource_registry (resource_id, dob)
VALUES (res_id_seq.NEXTVAL, ? )
sql:dateParam value=${parsed_dob} type=date/
/sql:update
/c:when
c:otherwise
INSERT INTO resource_registry (resource_id)
VALUES (res_id_seq.NEXTVAL)
/sql:update
/c:otherwise
/c:choose
If the real case involves many parameters that may be null, this
gets ugly, but if it's just this one, it may be okay.
Hans
--
Hans Bergsten[EMAIL PROTECTED]
Gefion Software http://www.gefionsoftware.com/
Author of O'Reilly's JavaServer Pages, covering JSP 2.0 and JSTL 1.1
Details athttp://TheJSPBook.com/
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--- End of Original Message ---
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: More SQL Date problems
I just got that prohibited content email myself. All I'm sending is plaintext email. Is that something from the mailing list doing it? Snipped Hans' original message out and am resending... Thanks a lot, Hans! Yeah, I had seen a workaround like that would work. I groaned when I realized that, though. Some other forms I'm working on have a lot more date fields (two have 15 each), so it would get pretty ugly. The other problem comes in when/if the date column data needs to be deleted. I have another UPDATE form that pulls the existing data from the database and fills in the form. People can then edit that entry. If the sql:dateParam tag worked like it's supposed to, they could just erase the date in the desired form field, and the JSTL tag would set it to NULL, effectively deleting it. The workaround wouldn't work in this case, because the field is not originally empty. Fortunately, in the case of my application, dates never should be deleted once entered. Mine is a unique case, though, and is effectively using what seems to be a bug to keep the date in the database. :p Is there any chance of this problem being fixed in the case of the Oracle JDBC driver? Keith - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: More SQL Date problems
Keith wrote:
Now I'm thorougly confused. I could've sworn this was working before (as I claimed in my
first email to the group.
fmt:parseDate value=${param.dob} var=parsed_dob pattern=dd-MM- /
sql:transaction
sql:update
INSERT INTO resource_registry ( dob )
VALUES (? sql:dateParam value=${parsed_dob} type=date/ )
/sql:update
/sql:transaction
This works perfectly fine when I put a date in the format specified in the parseDate
action. The JSP book I got (O'Reilly 3rd Ed) says the sql:dateParam action is supposed
to set the value to an SQL NULL when a null value is provided to it. I keep getting an
Invalid Column Type SQL exception (not an Oracle error) back whenever I leave the date
field blank. Anyone know what's wrong? Thanks!
It looks to me as if the Invalid Column Type SQL exception indeed
comes from the database (or the JDBC driver), because nothing in JSTL
can issue such an error message (JSTL doesn't have enough info; it
just relays the error issued by the JDBC driver).
JSTL sets the parameter in the SQL statement to SQL NULL if the
sql:dateParam value is null (according to the spec; bugs in an
implementation is a different story). One possible reason for the
error you get is that the column isn't declared to accept a NULL
value. Check the database table constraints.
Hans
--
Hans Bergsten[EMAIL PROTECTED]
Gefion Software http://www.gefionsoftware.com/
Author of O'Reilly's JavaServer Pages, covering JSP 2.0 and JSTL 1.1
Details athttp://TheJSPBook.com/
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: More SQL Date problems
Keith wrote:
Ahhh, ok. I assumed it was the JSTL stuff throwing the error. I've not done much
programming in actual Java, which is why I'm using the JSTL library.
The field is set to accept a NULL value in the database. There's already data in the
database, and there's many DOB's missing for some people. I can do inserts to the
database from SQL*Plus fine and leave the date fields blank. It's just when I'm trying
to do it via JSP. I'm trying to design an new interface to the database and this has
been my roadblock for the week.
Okay. Which JDBC driver are you using? If it's the JDBC-ODBC bridge, I
suggest you try with a real Oracle JDBC type 3 or 4 driver instead. The
bridge driver is buggy and not intended for production use.
Hans
-- Original Message ---
From: Hans Bergsten [EMAIL PROTECTED]
To: Tag Libraries Users List [EMAIL PROTECTED]
Sent: Tue, 30 Mar 2004 19:30:44 -0800
Subject: Re: More SQL Date problems
Keith wrote:
Now I'm thorougly confused. I could've sworn this was working before (as I claimed
in my
first email to the group.
fmt:parseDate value=${param.dob} var=parsed_dob pattern=dd-MM- /
sql:transaction
sql:update
INSERT INTO resource_registry ( dob )
VALUES (? sql:dateParam value=${parsed_dob} type=date/ )
/sql:update
/sql:transaction
This works perfectly fine when I put a date in the format specified in the parseDate
action. The JSP book I got (O'Reilly 3rd Ed) says the sql:dateParam action is
supposed
to set the value to an SQL NULL when a null value is provided to it. I keep getting
an
Invalid Column Type SQL exception (not an Oracle error) back whenever I leave the
date
field blank. Anyone know what's wrong? Thanks!
It looks to me as if the Invalid Column Type SQL exception indeed
comes from the database (or the JDBC driver), because nothing in JSTL
can issue such an error message (JSTL doesn't have enough info; it
just relays the error issued by the JDBC driver).
JSTL sets the parameter in the SQL statement to SQL NULL if the
sql:dateParam value is null (according to the spec; bugs in an
implementation is a different story). One possible reason for the
error you get is that the column isn't declared to accept a NULL
value. Check the database table constraints.
Hans
--
Hans Bergsten[EMAIL PROTECTED]
Gefion Software http://www.gefionsoftware.com/
Author of O'Reilly's JavaServer Pages, covering JSP 2.0 and JSTL 1.1
Details athttp://TheJSPBook.com/
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: More SQL Date problems
Yeah, I'd read about not using that bridge driver. I'm using Oracle 9i Release 2, J2SE
v1.4.2, and the JDBC Oracle Driver for JDK 1.4.
Keith
-- Original Message ---
From: Hans Bergsten [EMAIL PROTECTED]
To: Tag Libraries Users List [EMAIL PROTECTED]
Sent: Tue, 30 Mar 2004 20:40:41 -0800
Subject: Re: More SQL Date problems
Keith wrote:
Ahhh, ok. I assumed it was the JSTL stuff throwing the error. I've not done much
programming in actual Java, which is why I'm using the JSTL library.
The field is set to accept a NULL value in the database. There's already data in
the
database, and there's many DOB's missing for some people. I can do inserts to the
database from SQL*Plus fine and leave the date fields blank. It's just when I'm
trying
to do it via JSP. I'm trying to design an new interface to the database and this
has
been my roadblock for the week.
Okay. Which JDBC driver are you using? If it's the JDBC-ODBC bridge, I
suggest you try with a real Oracle JDBC type 3 or 4 driver instead. The
bridge driver is buggy and not intended for production use.
Hans
-- Original Message ---
From: Hans Bergsten [EMAIL PROTECTED]
To: Tag Libraries Users List [EMAIL PROTECTED]
Sent: Tue, 30 Mar 2004 19:30:44 -0800
Subject: Re: More SQL Date problems
Keith wrote:
Now I'm thorougly confused. I could've sworn this was working before (as I
claimed
in my
first email to the group.
fmt:parseDate value=${param.dob} var=parsed_dob pattern=dd-MM- /
sql:transaction
sql:update
INSERT INTO resource_registry ( dob )
VALUES (? sql:dateParam value=${parsed_dob} type=date/ )
/sql:update
/sql:transaction
This works perfectly fine when I put a date in the format specified in the
parseDate
action. The JSP book I got (O'Reilly 3rd Ed) says the sql:dateParam action is
supposed
to set the value to an SQL NULL when a null value is provided to it. I keep
getting
an
Invalid Column Type SQL exception (not an Oracle error) back whenever I leave the
date
field blank. Anyone know what's wrong? Thanks!
It looks to me as if the Invalid Column Type SQL exception indeed
comes from the database (or the JDBC driver), because nothing in JSTL
can issue such an error message (JSTL doesn't have enough info; it
just relays the error issued by the JDBC driver).
JSTL sets the parameter in the SQL statement to SQL NULL if the
sql:dateParam value is null (according to the spec; bugs in an
implementation is a different story). One possible reason for the
error you get is that the column isn't declared to accept a NULL
value. Check the database table constraints.
Hans
--
Hans Bergsten[EMAIL PROTECTED]
Gefion Software http://www.gefionsoftware.com/
Author of O'Reilly's JavaServer Pages, covering JSP 2.0 and JSTL 1.1
Details athttp://TheJSPBook.com/
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--- End of Original Message ---
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
RE: More SQL Date problems
Hmm Keith,
Can you specify the url you're using to connect to the database? * out the
uid and psw of course... Also can you post a stack trace? I did a few
Googles on your exception and heard some people shouting the oracle thin
driver should work best. It's what I have used in the past and I've not had
any headaches with it.
Martin
-Oorspronkelijk bericht-
Van: Keith [mailto:[EMAIL PROTECTED]
Verzonden: woensdag 31 maart 2004 5:55
Aan: Tag Libraries Users List
Onderwerp: Re: More SQL Date problems
Yeah, I'd read about not using that bridge driver. I'm using Oracle 9i
Release 2, J2SE
v1.4.2, and the JDBC Oracle Driver for JDK 1.4.
Keith
-- Original Message ---
From: Hans Bergsten [EMAIL PROTECTED]
To: Tag Libraries Users List [EMAIL PROTECTED]
Sent: Tue, 30 Mar 2004 20:40:41 -0800
Subject: Re: More SQL Date problems
Keith wrote:
Ahhh, ok. I assumed it was the JSTL stuff throwing the error. I've not
done much
programming in actual Java, which is why I'm using the JSTL library.
The field is set to accept a NULL value in the database. There's already
data in the
database, and there's many DOB's missing for some people. I can do
inserts to the
database from SQL*Plus fine and leave the date fields blank. It's just
when I'm
trying
to do it via JSP. I'm trying to design an new interface to the database
and this has
been my roadblock for the week.
Okay. Which JDBC driver are you using? If it's the JDBC-ODBC bridge, I
suggest you try with a real Oracle JDBC type 3 or 4 driver instead. The
bridge driver is buggy and not intended for production use.
Hans
-- Original Message ---
From: Hans Bergsten [EMAIL PROTECTED]
To: Tag Libraries Users List [EMAIL PROTECTED]
Sent: Tue, 30 Mar 2004 19:30:44 -0800
Subject: Re: More SQL Date problems
Keith wrote:
Now I'm thorougly confused. I could've sworn this was working before
(as I claimed
in my
first email to the group.
fmt:parseDate value=${param.dob} var=parsed_dob
pattern=dd-MM- /
sql:transaction
sql:update
INSERT INTO resource_registry ( dob )
VALUES (? sql:dateParam value=${parsed_dob} type=date/ )
/sql:update
/sql:transaction
This works perfectly fine when I put a date in the format specified in
the
parseDate
action. The JSP book I got (O'Reilly 3rd Ed) says the sql:dateParam
action is
supposed
to set the value to an SQL NULL when a null value is provided to it. I
keep getting
an
Invalid Column Type SQL exception (not an Oracle error) back whenever I
leave the
date
field blank. Anyone know what's wrong? Thanks!
It looks to me as if the Invalid Column Type SQL exception indeed
comes from the database (or the JDBC driver), because nothing in JSTL
can issue such an error message (JSTL doesn't have enough info; it
just relays the error issued by the JDBC driver).
JSTL sets the parameter in the SQL statement to SQL NULL if the
sql:dateParam value is null (according to the spec; bugs in an
implementation is a different story). One possible reason for the
error you get is that the column isn't declared to accept a NULL
value. Check the database table constraints.
Hans
--
Hans Bergsten[EMAIL PROTECTED]
Gefion Software http://www.gefionsoftware.com/
Author of O'Reilly's JavaServer Pages, covering JSP 2.0 and JSTL 1.1
Details athttp://TheJSPBook.com/
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--- End of Original Message ---
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
