auto increment primary key val into XML attribute

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

alexs - 25 Aug 2006 12:34 GMT

Chaps,

I'm starting to play with db2 V9.1 and am writing a stored procedure
to manage accounting records from oiur RADIUS server.

I've got an XML aware table with an auto increment primary key, a few
sql columns and an XML column. For every XML object I'm inserting, i'd
lke to have an ID attribute that has the same value as the SQL primary
key that'll be created upon executing the insert.

Is there any way in my Java stored procedure I can get hold of what the
"next" primary key will be so that I can do the above.

Of course I could cheat and insert the sql primary key into the
attribute when i retrieve the data, but that would stop me doing other
things

TIA

alex

Reply to this Message

Artur - 26 Aug 2006 10:30 GMT

alexs,

if I undestand it correctly, you would like to have the ID before you
insert the record (in order to compose XML attribute).

Use sequence, and in your stored procedure just generate the next value
before you insert the record:

CREATE SEQUENCE seq1 AS BIGINT
VALUES NEXTVAL INTO id FOR seq1

-- Artur Wronski

Reply to this Message

alexs - 31 Aug 2006 15:27 GMT

hi,
o.k. think I'm missing something here.

1).
If I've defined the primary key for a table to be auto increment then
surely you can't insert a value into it that's generated by the stored
procedure.

2). If the primary key isn't auto increment, and i generate the key
value from within a stored procedure using a create sequence statement,
what happens if I update the sp, or the instrance is restarted, If i've
got pirmary keys in the range 1 - 99 and i stop and start the database,
how does the create sequence statement know to start from 100 and not
1 ?

It sort of assumes that at some point when the sp is being initialized
you do some magic to find out what the next primary key value is and
use that to init your sequence

> alexs,
>
[quoted text clipped - 8 lines]
>
> -- Artur Wronski

Reply to this Message