• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Just insert the desired ID values? Sorry, I don't see a problem here.
 
Olimpiu Pop
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
that's it . Sorry for the confusion. Do you think that this can be done with a stored procedure?

Tz
 
Marshal
Posts: 79177
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"zup troll" please read the important administrative private message I am about to send you.

CR
 
reply
    Bookmark Topic Watch Topic
  • New Topic