You are viewing a plain text version of this content. The canonical link for it is here.
Posted to taglibs-user@tomcat.apache.org by Keith <pd...@pdragon.net> on 2004/03/31 04:03:21 UTC
More SQL Date problems
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-yyyy" />
<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!
Keith
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
remove my mail from your list
Posted by "Francis P. Chauvel H." <fc...@terra.com.pe>.
-----Mensaje original-----
De: Jonathan Bruce [mailto:Jonathan.Bruce@Sun.COM]
Enviado el: Viernes, 16 de Abril de 2004 01:52 p.m.
Para: Tag Libraries Users List
CC: Pierre Delisle; Lance Andersen
Asunto: 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 <Ju...@Sun.COM>
>> To: Tag Libraries Users List <ta...@jakarta.apache.org>
>> 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-yyyy" />
>>>>
>>>> <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: taglibs-user-unsubscribe@jakarta.apache.org
>>> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
>>>
>>
>> ------- End of Original Message -------
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
>> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
>>
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
Re: More SQL Date problems
Posted by "Lance J. Andersen" <La...@Sun.COM>.
-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 <Ju...@Sun.COM>
>>> To: Tag Libraries Users List <ta...@jakarta.apache.org>
>>> 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-yyyy" />
>>>>>
>>>>> <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: taglibs-user-unsubscribe@jakarta.apache.org
>>>> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
>>>>
>>>
>>>
>>> ------- End of Original Message -------
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
>>> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
>>>
>>>
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
>> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
Re: More SQL Date problems
Posted by Jonathan Bruce <Jo...@Sun.COM>.
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 <Ju...@Sun.COM>
>> To: Tag Libraries Users List <ta...@jakarta.apache.org>
>> 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-yyyy" />
>>>>
>>>> <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: taglibs-user-unsubscribe@jakarta.apache.org
>>> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
>>>
>>
>> ------- End of Original Message -------
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
>> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
>>
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
please remove my mail from your list fchauvel@terra.com.pe
Posted by "Francis P. Chauvel H." <fc...@terra.com.pe>.
-----Mensaje original-----
De: Keith [mailto:pdragon@pdragon.net]
Enviado el: Viernes, 16 de Abril de 2004 11:52 a.m.
Para: Tag Libraries Users List
Asunto: 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 <Ju...@Sun.COM>
To: Tag Libraries Users List <ta...@jakarta.apache.org>
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 <Ju...@Sun.COM>
> >To: Tag Libraries Users List <ta...@jakarta.apache.org>
> >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-yyyy" />
> >>>
> >>> <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: taglibs-user-unsubscribe@jakarta.apache.org
> >>For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
> >>
> >>
> >------- End of Original Message -------
> >
> >
> >---------------------------------------------------------------------
> >To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
> >For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
> >
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
------- End of Original Message -------
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
Re: More SQL Date problems
Posted by Keith <pd...@pdragon.net>.
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 <Ju...@Sun.COM>
To: Tag Libraries Users List <ta...@jakarta.apache.org>
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 <Ju...@Sun.COM>
> >To: Tag Libraries Users List <ta...@jakarta.apache.org>
> >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-yyyy" />
> >>>
> >>> <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: taglibs-user-unsubscribe@jakarta.apache.org
> >>For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
> >>
> >>
> >------- End of Original Message -------
> >
> >
> >---------------------------------------------------------------------
> >To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
> >For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
> >
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
------- End of Original Message -------
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
Re: More SQL Date problems
Posted by Justyna Horwat <Ju...@Sun.COM>.
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 <Ju...@Sun.COM>
>To: Tag Libraries Users List <ta...@jakarta.apache.org>
>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-yyyy" />
>>>
>>> <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: taglibs-user-unsubscribe@jakarta.apache.org
>>For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
>>
>>
>------- End of Original Message -------
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
>For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
remove from your directory please fchauvel@terra.com.pe
Posted by "Francis P. Chauvel H." <fc...@terra.com.pe>.
-----Mensaje original-----
De: Keith [mailto:pdragon@pdragon.net]
Enviado el: Viernes, 16 de Abril de 2004 09:25 a.m.
Para: Tag Libraries Users List
Asunto: 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 <Ju...@Sun.COM>
To: Tag Libraries Users List <ta...@jakarta.apache.org>
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-yyyy" />
> >
> > <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: taglibs-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
------- End of Original Message -------
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
Re: More SQL Date problems
Posted by Keith <pd...@pdragon.net>.
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 <Ju...@Sun.COM>
To: Tag Libraries Users List <ta...@jakarta.apache.org>
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-yyyy" />
> >
> > <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: taglibs-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
------- End of Original Message -------
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
please remove from your directory fchauvel@terra.com.pe
Posted by "Francis P. Chauvel H." <fc...@terra.com.pe>.
-----Mensaje original-----
De: Hans Bergsten [mailto:hans@gefionsoftware.com]
Enviado el: Viernes, 16 de Abril de 2004 11:07 a.m.
Para: Tag Libraries Users List
Asunto: 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-yyyy" />
>>
>> <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 <ha...@gefionsoftware.com>
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 at <http://TheJSPBook.com/>
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
Re: More SQL Date problems
Posted by Hans Bergsten <ha...@gefionsoftware.com>.
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-yyyy" />
>>
>> <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 <ha...@gefionsoftware.com>
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 at <http://TheJSPBook.com/>
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
Re: More SQL Date problems
Posted by Justyna Horwat <Ju...@Sun.COM>.
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-yyyy" />
>
> <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: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
Re: More SQL Date problems
Posted by Keith <pd...@pdragon.net>.
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: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
Re: More SQL Date problems
Posted by Keith <pd...@pdragon.net>.
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 <ha...@gefionsoftware.com>
To: Tag Libraries Users List <ta...@jakarta.apache.org>
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-yyyy" />
>
> <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 <ha...@gefionsoftware.com>
> Gefion Software <http://www.gefionsoftware.com/>
> Author of O'Reilly's "JavaServer Pages", covering JSP 2.0 and JSTL 1.1
> Details at <http://TheJSPBook.com/>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
------- End of Original Message -------
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
Re: More SQL Date problems
Posted by Hans Bergsten <ha...@gefionsoftware.com>.
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-yyyy" />
<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 <ha...@gefionsoftware.com>
Gefion Software <http://www.gefionsoftware.com/>
Author of O'Reilly's "JavaServer Pages", covering JSP 2.0 and JSTL 1.1
Details at <http://TheJSPBook.com/>
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
Re: More SQL Date problems
Posted by Wolfgang Röckelein <wo...@wiwi.uni-regensburg.de>.
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.
Regards,
Wolfgang
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
RE: More SQL Date problems
Posted by Keith <pd...@pdragon.net>.
This is what I have in the web.xml file:
<context-param>
<param-name>
javax.servlet.jsp.jstl.sql.dataSource
</param-name>
<param-value>
jdbc:oracle:thin:@hostname:1521:sid,oracle.jdbc.driver.OracleDriver,username,passw
ord
</param-value>
So, yes, I am using the Thin client.
The full stack trace is below. The resource_id is they primary key, so I have to insert
that at the least. It's a sequence number within Oracle. That part does work fine if I
fill in the date field.
------
type Exception report
message
description The server encountered an internal error () that prevented it from fulfilling
this request.
exception
javax.servlet.ServletException:
INSERT INTO ccevs_resource_registry (
resource_id,
dob
)
VALUES (res_id_seq.NEXTVAL
, ?
)
: Invalid column type
org.apache.jasper.runtime.PageContextImpl.doHandlePageException
(PageContextImpl.java:864)
org.apache.jasper.runtime.PageContextImpl.handlePageException
(PageContextImpl.java:800)
org.apache.jsp.test_jsp._jspService(test_jsp.java:89)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:133)
javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:311)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:301)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:248)
javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
root cause
java.sql.SQLException: Invalid column type
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.driver.OracleStatement.get_internal_type(OracleStatement.java:6414)
at oracle.jdbc.driver.OraclePreparedStatement.setNull
(OraclePreparedStatement.java:1354)
at oracle.jdbc.driver.OraclePreparedStatement.setObject
(OraclePreparedStatement.java:2791)
at oracle.jdbc.driver.OraclePreparedStatement.setObject
(OraclePreparedStatement.java:3217)
at org.apache.taglibs.standard.tag.common.sql.UpdateTagSupport.setParameters
(Unknown Source)
at org.apache.taglibs.standard.tag.common.sql.UpdateTagSupport.doEndTag(Unknown
Source)
at org.apache.jsp.test_jsp._jspx_meth_sql_update_0(test_jsp.java:186)
at org.apache.jsp.test_jsp._jspx_meth_sql_transaction_0(test_jsp.java:127)
at org.apache.jsp.test_jsp._jspService(test_jsp.java:76)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:133)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:311)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:301)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:248)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter
(ApplicationFilterChain.java:284)
at org.apache.catalina.core.ApplicationFilterChain.doFilter
(ApplicationFilterChain.java:204)
at org.apache.catalina.core.StandardWrapperValve.invoke
(StandardWrapperValve.java:257)
at org.apache.catalina.core.StandardValveContext.invokeNext
(StandardValveContext.java:151)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:567)
at org.apache.catalina.core.StandardContextValve.invokeInternal
(StandardContextValve.java:245)
at org.apache.catalina.core.StandardContextValve.invoke
(StandardContextValve.java:199)
at org.apache.catalina.core.StandardValveContext.invokeNext
(StandardValveContext.java:151)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:567)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:184)
at org.apache.catalina.core.StandardValveContext.invokeNext
(StandardValveContext.java:151)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:164)
at org.apache.catalina.core.StandardValveContext.invokeNext
(StandardValveContext.java:149)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:567)
at org.apache.catalina.core.StandardEngineValve.invoke
(StandardEngineValve.java:156)
at org.apache.catalina.core.StandardValveContext.invokeNext
(StandardValveContext.java:151)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:567)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:972)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(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" <su...@windgazer.nl>
To: "Tag Libraries Users List" <ta...@jakarta.apache.org>
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:pdragon@pdragon.net]
> 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 <ha...@gefionsoftware.com>
> To: Tag Libraries Users List <ta...@jakarta.apache.org>
> 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 <ha...@gefionsoftware.com>
> > > To: Tag Libraries Users List <ta...@jakarta.apache.org>
> > > 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-yyyy" />
> > >>>
> > >>><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 <ha...@gefionsoftware.com>
> > Gefion Software <http://www.gefionsoftware.com/>
> > Author of O'Reilly's "JavaServer Pages", covering JSP 2.0 and JSTL 1.1
> > Details at <http://TheJSPBook.com/>
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
> ------- End of Original Message -------
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
------- End of Original Message -------
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
RE: More SQL Date problems
Posted by Martin van Dijken <su...@windgazer.nl>.
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:pdragon@pdragon.net]
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 <ha...@gefionsoftware.com>
To: Tag Libraries Users List <ta...@jakarta.apache.org>
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 <ha...@gefionsoftware.com>
> > To: Tag Libraries Users List <ta...@jakarta.apache.org>
> > 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-yyyy" />
> >>>
> >>><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 <ha...@gefionsoftware.com>
> Gefion Software <http://www.gefionsoftware.com/>
> Author of O'Reilly's "JavaServer Pages", covering JSP 2.0 and JSTL 1.1
> Details at <http://TheJSPBook.com/>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
------- End of Original Message -------
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
Re: More SQL Date problems
Posted by Keith <pd...@pdragon.net>.
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 <ha...@gefionsoftware.com>
To: Tag Libraries Users List <ta...@jakarta.apache.org>
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 <ha...@gefionsoftware.com>
> > To: Tag Libraries Users List <ta...@jakarta.apache.org>
> > 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-yyyy" />
> >>>
> >>><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 <ha...@gefionsoftware.com>
> Gefion Software <http://www.gefionsoftware.com/>
> Author of O'Reilly's "JavaServer Pages", covering JSP 2.0 and JSTL 1.1
> Details at <http://TheJSPBook.com/>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
------- End of Original Message -------
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
Re: More SQL Date problems
Posted by Hans Bergsten <ha...@gefionsoftware.com>.
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 <ha...@gefionsoftware.com>
> To: Tag Libraries Users List <ta...@jakarta.apache.org>
> 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-yyyy" />
>>>
>>><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 <ha...@gefionsoftware.com>
Gefion Software <http://www.gefionsoftware.com/>
Author of O'Reilly's "JavaServer Pages", covering JSP 2.0 and JSTL 1.1
Details at <http://TheJSPBook.com/>
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
Re: More SQL Date problems
Posted by Keith <pd...@pdragon.net>.
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.
Keith
---------- Original Message -----------
From: Hans Bergsten <ha...@gefionsoftware.com>
To: Tag Libraries Users List <ta...@jakarta.apache.org>
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-yyyy" />
> >
> > <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 <ha...@gefionsoftware.com>
> Gefion Software <http://www.gefionsoftware.com/>
> Author of O'Reilly's "JavaServer Pages", covering JSP 2.0 and JSTL 1.1
> Details at <http://TheJSPBook.com/>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
------- End of Original Message -------
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
Re: More SQL Date problems
Posted by Hans Bergsten <ha...@gefionsoftware.com>.
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-yyyy" />
>
> <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 <ha...@gefionsoftware.com>
Gefion Software <http://www.gefionsoftware.com/>
Author of O'Reilly's "JavaServer Pages", covering JSP 2.0 and JSTL 1.1
Details at <http://TheJSPBook.com/>
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org