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 > <thomas.to...@gmail.com<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, vrota...@gmail.com <javascript:> < >> vrota...@gmail.com <javascript:>> wrote: >> >>> >>> On Tue, Oct 23, 2012 at 3:10 AM, <brent....@gmail.com <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 h2-da...@googlegroups.com<javascript:> >>> . >>> To unsubscribe from this group, send email to >>> h2-database...@googlegroups.com <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 h2-da...@googlegroups.com<javascript:> >> . >> To unsubscribe from this group, send email to >> h2-database...@googlegroups.com <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 h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.