I'm also curious about this: How can we insert a new row unless one already exists, and in either case return the primary key of the inserted/matched row?
Thanks, Gili On Thursday, October 25, 2012 4:31:19 PM UTC-4, Brent Ellwein wrote: > > OK, so how would the return case look? I assume that you mean I would > set-up a pre-insert trigger which would do *something* before the insert > actually happens and detect that the insert isn't necessary and then return > a resulting value. Is that essentially what you are suggesting? > > I guess that I don't see how I'm going to get the ID back to the client > using this mechanism. Can you provide an example? > > Thanks! > > > On Thu, Oct 25, 2012 at 11:16 AM, Thomas Mueller > <[email protected]<javascript:> > > wrote: > >> Hi, >> >> > BUT what I would like to do is write a stored procedure which does it >> all at once and then returns the id value quickly >> >> Yes, you could use a user defined Java function >> >> > However, I cannot seem to cache the prepared statement in my function >> call >> >> Is it *really* a big problem in this case? If yes, a workaround would be >> to use a trigger, as within a trigger you can cache prepared statements >> (see the class FullText for details how this can be done). In your case, it >> would be a 'instead of insert' trigger I believe. Maybe even easier might >> be a trigger on update (you wouldn't need to use the JDBC API in this case >> I believe). >> >> Regards, >> Thomas >> >> >> >> On Tue, Oct 23, 2012 at 7:04 AM, [email protected] <javascript:> < >> [email protected] <javascript:>> wrote: >> >>> >>> On Tue, Oct 23, 2012 at 3:10 AM, <[email protected] <javascript:>>wrote: >>> >>>> merge into File (filepath, filesize, datemodified) key (filepath) >>>> values (?,?,?) >>> >>> >>> I've just created a table and tried this from the H2 console >>> >>> merge into File (filepath, filesize, datemodified) key (filepath) values >>> ('foo',9999,889) >>> >>> No exception. And I run it with more than once. So, I have no idea why >>> you are getting one. >>> >>> >>> -- >>> Vasile Rotaru >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "H2 Database" group. >>> To post to this group, send email to [email protected]<javascript:> >>> . >>> To unsubscribe from this group, send email to >>> [email protected] <javascript:>. >>> For more options, visit this group at >>> http://groups.google.com/group/h2-database?hl=en. >>> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To post to this group, send email to [email protected]<javascript:> >> . >> To unsubscribe from this group, send email to >> [email protected] <javascript:>. >> For more options, visit this group at >> http://groups.google.com/group/h2-database?hl=en. >> > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
