Re: Insert if row does not already exist, cache PreparedStatement in a function

[vrota...@gmail.com]

So what's wroing with:

      create table unique_names (id identity, name varchar not null unique)

You can even make the a column of type varchar the primary key. I did.

On Mon, Oct 22, 2012 at 7:18 PM, Brent Ellwein <brent....@gmail.com> wrote:

Hello,

I am trying to have a unique String column for my H2 database implementation.  As this is not natively supported, I'm walking around in the weeds a little bit, and store the hash of the string in an indexed column in the table.  For insert, I have a two-part check.

1)  see if a row exists with the String value using an SQL select statement, if so return the generated ID for the already existing row.
2)  insert the new row and then return the resulting generated ID.

This seems to work fine with a combination of a select statement and a followup insert statement.  BUT what I would like to do is write a stored procedure which does it all at once and then returns the id value quickly.  I was able to write this function.  However, I cannot seem to cache the prepared statement in my function call, and thus generate the statement for each call.  The end result is that the stored procedure executes more slowly than running several calls through a client with cached stored procedures.

Thus my question is, is there a way to cache a prepared statement from within a stored procedure?  Is there a better approach to the problem which I have not yet thought of?

Thanks you for your input.
--Brent

--
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/-/P4Pa9RQ1EgMJ.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

--
   Vasile Rotaru

[Brent Ellwein]

What is the difference between varchar and text.  H2 specifically rejects using unique or primarykey for text fields.

Also, this does not solve the problem of caching the preparedStatement in the function.  Any suggestions there?

[Rami Ojares]

> 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

[vrota...@gmail.com]

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 h2-da...@googlegroups.com.

To unsubscribe from this group, send email to h2-database+unsubscribe@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

--
   Vasile Rotaru

[Brent Ellwein]

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 <rami....@gmail.com> 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 h2-da...@googlegroups.com.

To unsubscribe from this group, send email to h2-database...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

--
   Vasile Rotaru

[vrota...@gmail.com]

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 <brent....@gmail.com> 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 <rami....@gmail.com> 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 h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.

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 h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

--
   Vasile Rotaru

[brent....@gmail.com]

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.

[vrota...@gmail.com]

On Tue, Oct 23, 2012 at 1:26 AM, <brent....@gmail.com> 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.

 

--
   Vasile Rotaru

[brent....@gmail.com]

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

[vrota...@gmail.com]

On Tue, Oct 23, 2012 at 3:10 AM, <brent....@gmail.com> wrote:

merge into File (filepath, filesize, datemodified) key (filepath) values (?,?,?)

I've just created a table and tried this from the H2 console

merge into File (filepath, filesize, datemodified) key (filepath) values ('foo',9999,889)

No exception. And I run it with more than once. So, I have no idea why you are getting one.

--
   Vasile Rotaru

[Thomas Mueller]

Hi,

> BUT what I would like to do is write a stored procedure which does it all at once and then returns the id value quickly

Yes, you could use a user defined Java function

>  However, I cannot seem to cache the prepared statement in my function call

Is it *really* a big problem in this case? If yes, a workaround would be to use a trigger, as within a trigger you can cache prepared statements (see the class FullText for details how this can be done). In your case, it would be a 'instead of insert' trigger I believe. Maybe even easier might be a trigger on update (you wouldn't need to use the JDBC API in this case I believe).

Regards,

Thomas

--
   Vasile Rotaru

--

[Brent Ellwein]

OK, so how would the return case look?  I assume that you mean I would set-up a pre-insert trigger which would do *something* before the insert actually happens and detect that the insert isn't necessary and then return a resulting value.  Is that essentially what you are suggesting?

I guess that I don't see how I'm going to get the ID back to the client using this mechanism.  Can you provide an example?

Thanks!

[Gili]

I'm also curious about this:

How can we insert a new row unless one already exists, and in either case return the primary key of the inserted/matched row?

Thanks,

Gili

[Thomas Mueller]

Hi,

> How can we insert a new row unless one already exists, and in either case return the primary key of the inserted/matched row?

Well, how could you insert the row if you don't know the primary key already when inserting?

Regards,

Thomas

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

[Gili]

Hi Thomas,

Like this:

create table employee (id identity not null, email varchar(100) not null);

insert into employee (email) values ('f...@bar.com');

If the row does not exist, I'd retrieve the inserted primary key. If it does exist, I'd want to (but don't know how) retrieve the existing primary key. I ended up implementing this using two separate statements (insert followed by select) but I was wondering if there was a better way.

Thanks,

Gili

[Thomas Mueller]

Hi,

You would need a unique index on the column "email". For H2, you could use a combination of:

insert into employee(email) select ? from dual where not exists(select * from employee where email=?);
select id from employee where email=?

This could be written as a user defined function. This should work for other databases as well. For H2, you could use "merge into employee(email) key(email) values(?)" as well.

Regards,
Thomas

[cowwoc]

    Looks good.

Thank you,
Gili

You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/ejdytiTCvbA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.