Inserting into tables connected by a many-to-many relationship

[Olimpiu Pop]

Hello,

I'm working on a DB with the following structure:

[Events]
eventid:int - auto generated, primary Key
name:varchar(200)
type:int

[Problems]
problemid:int - auto generated, primary key
name:varchar(200)
type:int

[EventsProblems]
linkId:int - auto generated, primary key
eventId: int - FK referencing events table
problemId: int - FK referencing problems table

So I need to add a problem to an event, this implies to insert in the link table the id's
of the other two tables. Can you recommand me a decent way to do this?

ZT

[Bauke Scholtz]

Just insert the desired ID values? Sorry, I don't see a problem here.

[Olimpiu Pop]

Maybe I just can't see the wood because of the trees..but:

- I insert a problem, then I have to insert in the link table, in order to do that
I need both the ids(eventid,problemid). My problem is how to obtain the problemid.
Can you recommend a way to obtain this id(problemid) besides the one where I
select this ID from the problems table by extracting the last inserted row.

[Bauke Scholtz]

Oh, now I see, your actual problem is that you don't know how to obtain the generated key (the insert ID) from the statement?

If so, just use Statement#getGeneratedKeys() on the same statement directly after the executeUpdate(). It returns a ResultSet with the generated key in the 1st column of the 1st row.

[Olimpiu Pop]

that's it . Sorry for the confusion. Do you think that this can be done with a stored procedure?

Tz

[Campbell Ritchie]

"zup troll" please read the important administrative private message I am about to send you.

CR