On Tue, Oct 23, 2012 at 1:26 AM, <[email protected]> wrote: > The string case is only one instance of this conditional insert pattern. > Some other cases have more complex unique pairs of keys, where a generated > ID is used to identify the row. I have not been able to get the merge to > return anything, but rather throw exceptions with my test cases thus far. I > use the JDBC get generated ID apis in other parts of the application, but > it doesn't apply for the merge command as I get an exception instead of a > result set. Maybe there is something that I'm missing, but I haven't yet > been able to get that to work. > > > Unless there's a bug in H2, the exception is an indication that the merge command is trying to modify more than one row.
Anyway, I'm afraid I cannot help you with further advice. > On , "[email protected]" <[email protected]> wrote: > > 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 > > > > 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 > > > > > > > > > > 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 > [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 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. > > > > > > > > > > -- > 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. > -- 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.
