tomcat-taglibs-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Don Albertson <dalbert...@pennswoods.net>
Subject Re: Trouble with JSP and Oracle sequences
Date Thu, 08 Jul 2004 14:36:55 GMT
Keith wrote:

> 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
> 
> 

I can't help you to use the sql taglib but I can tell you how I deal with 
that situation using servlet technology.
The SQL table is represented by a class which has read and write methods 
and maintains a state variable so it can decide whether to Insert, 
Update, or Delete when you use the write method.  On Insert it retrieves 
the sequence.nextval for the sequence field and holds on to it for the 
life of the object.  The object meets the minimal requirements for a jsp 
bean so it can be set and got from the jsp based on what the browser 
provides on POST from the form.

---------------------------------------------------------------------
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