Selecting from sequence

[N D Fisher]

Hi,

For some reason when I try selecting from an Oracle sequence using JSP it always selects the numbers as multiples of two, instead of single numbers. E.g if I run the following line from SQLPLus:

select track_num.nextval TRACK_SEQ from dual;

It does exactly as you'd expect and returns 1,2,3,4, etc.


However, when run from a JSP page as follows:

<jbo:ApplicationModule id="AppModuleDataControl2" definition="DataBindings.AppModuleDataControl2" releasemode="Stateful" />

<jbo:CreateViewObject appid="AppModuleDataControl2" name="av1" >
select track_num.nextval TRACK_SEQ from dual
</jbo:CreateViewObject>

<jbo:DataSource id="ds1" appid="AppModuleDataControl2" viewobject="av1" />

<jbo:Row id="row1" datasource="ds1" action="current" ></jbo:Row>

sequence no is:

<jbo:ShowValue datasource="ds1" dataitem="TRACK_SEQ" ></jbo:ShowValue>


It always returns 2,4,6,8,10, etc.

When I return to SQLPLus and run the same line again it returns 11,12,13,14, etc.

Any ideas?!? This is driving me nuts!

Many thanks,

Nick.
[ June 20, 2007: Message edited by: N D Fisher ]

[Mark Spritzler]

Well, I think the JSP is running twice, and that is why you get even numbers. I don't think putting that query into the JSP page is a good idea, as you are now tying the JSP page to a Sequence in Oracle. You could try just using CurVal and adding one to it, or not assigning the PK to the object till after the data gets inserted.

Mark

[N D Fisher]

Hi Mark,

Yes, it does seem to be executing twice, but my question is why! It's very strange. None of my other JSP select statements do that.

So which way do you recommend I actually retrieve the next value from the sequence using JSP then? Curval will probably get the current number OK, but what about getting the next. Is there some standard way of doing this with JSP?

Many thanks for your help,

Nick.
[ June 21, 2007: Message edited by: N D Fisher ]

[N D Fisher]

Just to clarify things further....

I have now tested this code by creating a session variable which gets incremented by 1 directly after the statement that calls 'nextval'. When printed out, this variable only increments by 1 at a time - whereas the sequence I'm getting returned is still incrementing by 2.

So how this nextval line is getting called twice, I have no idea.

Thanks,


Nick

[ June 21, 2007: Message edited by: N D Fisher ]
[ June 21, 2007: Message edited by: N D Fisher ]

[Mark Spritzler]

I think this must be related to the JSP/Servlet lifecycle, which I don't have enough knowledge of to say exactly what that is, but Why not have a backend object to handle the call to the database. .I am saying you are tying the front-end UI to the backend database.

Mark

[N D Fisher]

Thanks Mark,

I'm doing it via a stored PL-SQL procedure now instead, so it's working fine. I just thought there might be a neater way of doing things.

Cheers,


Nick.

[Mark Spritzler]

There is a neater way, don't have the query or stored procedure typed in your JSP page, have a backend component that handles all that stuff for you.

Use a framework like Struts, JSF, JBoss Seam, There are two others that I can't think of the name that are supposed to be really good too.

Mark