I don't know what to think about the merge exception, my unit test has a
database with only a single row in it when the exception is thrown. Maybe I
am not using the merge statement in the correct way? Here is the SQL of my
test case
CREATE TABLE File (
dbid bigint identity PRIMARY KEY NOT NULL,
filepath varchar NOT NULL unique,
filesize bigint NOT NULL,
datemodified bigint NOT NULL
);
merge into File (filepath, filesize, datemodified) key (filepath) values
(?,?,?)
I appreciate your assistance as I do think that my application is better
now than when I started.
However, my primary questions regarding conditional insert or cached
PreparedStatements within a DB function remain unresolved.
--Brent
On , "[email protected]" <[email protected]> wrote:
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.
--
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.
