Obtaining SQL insert auto generated key from query within JSP

[Damon Getsman]

Okay, this posting is about JSP, not pure Java, but I couldn't find a
more proper newsgroup(s) for it.

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

 

[Damon Getsman]

Whoops; the subject should say 'from INSERT within JSP', not 'from
query within JSP'.

 

[Mark Space]

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

I'm guessing here but probably something like:

<sql:query>
select LAST_INSERT_ID();
</sql:query>

Do a search on that sql function, see what comes up. It's connection
oriented so I'm hoping the JSP will maintain an open connection for ya.

 

[RedGrittyBrick]

I'm guessing here but probably something like:

<sql:query>
select LAST_INSERT_ID();
</sql:query>

Do a search on that sql function, see what comes up. It's connection
oriented so I'm hoping the JSP will maintain an open connection for ya.

The SQL for retrieving a serial number assigned by the DBMS is very
specific to the DBMS. There will be a different function for each of
SQL-Server, Oracle, PostGres, MySQL, Derby and others.

I've not used JSP so if JSP magically unifies this, that would be
surprising but welcome!

 

[Mark Space]

The SQL for retrieving a serial number assigned by the DBMS is very
specific to the DBMS. There will be a different function for each of
SQL-Server, Oracle, PostGres, MySQL, Derby and others.

I've not used JSP so if JSP magically unifies this, that would be
surprising but welcome!

Yup, I haven't used SQL since the 1980's. I thought last_insert_id()
was ANSI, but it's not. To the OP: Result: screwxxored by
non-interoperable vendors again. Ah well.

 

[Damon Getsman]

Well, I guess I should specify that I'm using MySQL as the back end.

I managed to get JSP to keep the session open by wrapping the
<sql:insert> that I was talking about along with the <sql:query> in a
<sql:transaction> set (I think). What I've got right now is basically

<sql:transaction dataSource="${whatever}">
<sql:insert>
INSERT INTO blah
(one, two, three)
VALUES (?, ?, ?)
...the corresponding <sqlaram ...> statements...
</sql:insert>

<sql:query var="pId">
SELECT LAST_INSERT_ID() FROM blah
</sql:query>
</sql:transaction>

That particular jsp fragment crashed completely on me when I had the
semicolon after LAST_INSERT_ID(), so I removed it.

Now I'm getting back as a value for ${pId}:
org.apache.taglibs.standard.tag.common.sql.ResultImpl@randomhex

Not sure where to go from here, but I think you guys have helped me
get a little further... Any other ideas? (And it's all much
appreciated)

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

 

[Chris Riesbeck]

The SQL for retrieving a serial number assigned by the DBMS is very
specific to the DBMS. There will be a different function for each of
SQL-Server, Oracle, PostGres, MySQL, Derby and others.

I've not used JSP so if JSP magically unifies this, that would be
surprising but welcome!

I couldn't find anything in the JSTL SQL section to allow access to the
statement, which is what Derby uses to get generated keys.