>From the merge documention "MERGE INTO [ ..] Updates existing rows, and insert rows that don't exist. If no key column is specified, the primary key columns are used to find the row. If more than one row per new row is affected, an exception is thrown. If the table contains an auto-incremented key or identity column, and the row was updated, the generated key is set to 0; otherwise it is set to the new key."
Unfortunately the query MERGE INTO [..] RETURNING ID is not supported (the returning bit) but as I understand you can get the generated id (specified above) using the JBDC api On Tue, Oct 23, 2012 at 1:11 AM, Brent Ellwein <[email protected]>wrote: > varchar is a nicer solution than text, thank you for that suggestion, > however merge doesn't really work for my application. If the inset fails, > then the merge will throw an exception rather than return the generated row > ID. > > What I need is to get the ID for the row back if it already exists. I > think that what I need is a conditional insert which will always return the > row ID. From what I have been able to read so far this is not a supported > feature of H2 which lead me down the stored procedure road. But to make > the procedure faster it would help to cache the prepared statement, rather > than recompile them on each call. > If you chose to have the unique string value as the primary key you already have it. Otherwise see above. > > The string example is just one case of a more complicated system of > conditional insertion steps. Any other suggestions? > > On Monday, October 22, 2012 3:40:24 PM UTC-6, Vasile Rotaru wrote: >> >> >> >> On Tue, Oct 23, 2012 at 12:15 AM, Rami Ojares <[email protected]> wrote: >> >>> What is the difference between varchar and text. H2 specifically >>>> rejects using unique or primarykey for text fields. >>>> >>> >>> I am looking at the list of types supported by h2 >>> http://www.h2database.com/**html**/datatypes.html<http://www.h2database.com/html/datatypes.html> >>> I don't see type text mentioned. >>> I remember that type from mysql though. >>> >>> Anyway, if you don't know what type text is why do you want to use it? >>> Just use varchar. >>> >>> >>> > Also, this does not solve the problem of caching the preparedStatement >>> in the function. >>> >>> Consider using merge statement >>> http://www.h2database.com/**html**/grammar.html#merge<http://www.h2database.com/html/grammar.html#merge> >>> >>> >>> >> I'd say yes, to both suggestions. varchar is a standard SQL type suppored >> by H2, and while H2 may support that type in some of its compatibility >> modes, it also may not >> >> Just make you string colump a primary key, and use MERGE INTO ... >> >> >>> -- >>> 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]. >>> To unsubscribe from this group, send email to h2-database...@**google** >>> groups.com. >>> >>> For more options, visit this group at http://groups.google.com/**group** >>> /h2-database?hl=en <http://groups.google.com/group/h2-database?hl=en>. >>> >>> >> >> >> -- >> Vasile Rotaru >> > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/h2-database/-/cCEgFiqzwKoJ. > > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > -- 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]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
