tomcat-taglibs-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Keith" <pdra...@pdragon.net>
Subject Re: Trouble with JSP and Oracle sequences
Date Thu, 08 Jul 2004 15:04:05 GMT
Why didn't I think of that? Thank you! :)

Keith

---------- Original Message -----------
From: David Schwartz <ds@arrayone.com>
To: Tag Libraries Users List <taglibs-user@jakarta.apache.org>
Sent: Thu,  8 Jul 2004 10:52:04 -0400
Subject: Re: Trouble with JSP and Oracle sequences

> I would first constrain the sequence value...
> 
> <sql:query var = "mainQuery" >
>     Select main_id.NextVal as currentID From Dual
> </sql:query>
> 
> Then save it into a form var...
> 
> <c:set var = "mainID" scope="request" value = '${mainQuery.rows[0].currentID}'
> />
> 
> Now use this currentID value in your insert queries...
> 
> <sql:update>
>     INSERT INTO main (main_id, project_id, selection )
>     VALUES (
>  	<c:out var = '${mainID}' />,
>  	, ? <sql:param value="${param.project_id}" />
>  	, ? <sql:param value="${param.selection}" />
>      )
> </sql:update>
> 
> Once you have the mainID you can use it throughout the page.
> 
> Quoting Keith <pdragon@pdragon.net>:
> 
> >> First, here's my relevant code:
> >>
> >> <fmt:parseDate value="${param.meeting_date}" var="parsed_meeting_date"
> >> pattern="dd-MMM-yyyy" />
> >>
> >> <sql:transaction>
> >>
> >> <sql:update>
> >> INSERT INTO main (
> >> 	main_id
> >> 	, project_id
> >> 	, selection
> >> )
> >> VALUES (
> >> 	main_id_seq.NEXTVAL
> >> 	, ? <sql:param value="${param.project_id}" />
> >> 	, ? <sql:param value="${param.selection}" />
> >> )
> >> </sql:update>
> >>
> >> <sql:update>
> >> INSERT INTO meeting_dates (
> >>  	main_id
> >> 	, meeting_date
> >> 	, meeting_type_id
> >> )
> >> VALUES
> >> 	main_id_seq.CURRVAL
> >> 	, ? <sql:dateParam value="${parsed_meeting_date}" />
> >> 	, ? <sql:param value="${param.meeting_type_id}" />
> >> )
> >> </sql:update>
> >>
> >> </sql:transaction>
> >>
> >> The user is submitting the data via an HTML form. I need to use the same
> >> sequence number
> >> that was generated by the first <sql:update> as the ID value in the second
> >> <sql:update>.
> >> But, I'm getting the following ORACLE error:
> >>
> >> ORA-00947: not enough values
> >>
> >> Looking through the Oracle documentation, it says the .CURRVAL option for a
> >> sequence is
> >> only available within the same session that the .NEXTVAL option was first
> >> used. The only
> >> thing I can think of that's happening is these two <sql:updates> are not
> >> being done within
> >> the same session, even thought they're in the same <sql:transaction>.
> >> Therefore, the
> >> .CURRVAL of the sequence is no longer available, thus throwing the above
> >> error. Doing
> >> something like this in an SQL*Plus session works perfectly fine and the
> >> documentation
> >> gives examples of multiple inserts being done like this, so I know it can be
> >> done. But, I
> >> don't know enough about what's going on behind the scenes in JSP to be able
> >> to see if
> >> that's what is causing the problem. Anyone able to shed a little light on
> >> what might be
> >> causing this? Thanks!
> >>
> >> Keith
> >>
> >> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
> >> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
> >>
> >>
> 
> David Schwartz
> Array Software Inc.
> http://www.arrayone.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


Mime
View raw message