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.
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.
