Obtaining SQL insert auto generated key within JSP

I am doing an <sql:insert> from within a .jsp file. Unfortunately at this point doing the insert from within a bean is not possible. Unfortunate, but that's the way it is. Anyway, I need to be able to determine the auto generated primary index field for the record that I am inserting for further processing beyond this point. For the life of me I have not been able to find out how to do this. I've tried googling, researching on sun.com, looking in the ORA book on JSP, and the book Beginning JSP 2: From Novice to Professional. I have yet to turn up anything that tells me how to retrieve this key.

Can anybody give me a pointer on how to do this from within JSP? I have a deadline to meet here and this small issue is really cutting it close for me.

Thanks in advance.

Damon Getsman
-=-=-
Programmer/Systems Administrator
ITRx - http://www.itrx-nd.com/
-=-=-

Hi Damon

are you inserting into Oracle Database or SQL server?
are you using the auto increment of the SQL server?
in this case you will need to query for the max value of the id column
can u please show me the insert code you use ?

in case you are using oracle sequences just query for the sequence next value and use this value to insert into the database

I was using MySQL as the back end. I ended up finding out that allegedly from within the JSP tags (as opposed to from within a bean, where this code should have been) there is no way to directly request this value. I ended up having to do a quick hack to query with the little identifying information that I had, with a descending sort ordered by the index column returning only 1 result.

It's a shame that I have such a quick deadline that I can't take the time to learn enough raw java to code this project properly; instead I'm stuck putting all of the logic and database access into the .jsp files the same as the previous coder because I've learned everything that I know about this from looking at his code examples.

Thanks anyway.

Damon Getsman
-=-=-=-
ITRx http://www.itrx-nd.com
Programmer/Systems Administrator
-=-=-=-

Hi Damon,
Sorry for my late reply
as you just said it is a shame to have the database queries included into the jsp pages which shows that the previous developer was aware of the basic rules of designing J2EE.

Anyway it looks that the table in this topic is set to auto increment the primary key and you need to know what was the pk inserted for you record

my advice is to create a new <jsp:sql quering for the last values before the insertion and then adding the incremental number defined in the table definition

and for that you will need to see the table definition on the database.

if this does not help fixing the problem please tell me and I'll be glad to help