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. 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]<javascript:> > > 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]<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<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.
