RegularTable ; how to disable statement / resultset caching?

[Atul]

Hi -

I'm extending RegularTable to surface some complex object graphs in a
query-friendly JDBC-compliant form (H2).

Once the select statement (query) is issued against H2, a "before
select trigger" populates the table for what's being requested, sends
it to the user and then truncates the table. So, my datasets are
short-lived, or just for the duration of the resultset being sent.


My question: I want the same query to always do a NEW prepare and NOT
utilize any type of caching. I noticed that on a fresh start of H2,
new queries are received at Select.prepare(). Subsequent queries do
NOT make it to this method, as I assume they're cached.

How do I disable this type of caching or have my query interpreted in
Select.prepare() every time?

Thanks in advance -

AC

[Atul]

After some analysis it seems that:

Query.query(int limit,ResultTarget resultTarget) allows me to disable
reusal of results via:
session.getDatabase().setOptimizeReuseResults(false);

This is helpful for my use case. My next problem however is finding a
good way to truncate my table(s) once the query lifecycle is over.

I tried following Sergi's advice of removing results in Table.unlock()
but then the data's lost before being sent back to the caller.

I will continue the discussion at that original thread:
https://groups.google.com/group/h2-database/browse_thread/thread/10f11d8fb986bea6

AC

[Thomas Mueller]

Hi,

Caching or results can be disabled by appending ;QUERY_CACHE_SIZE=0 to the database URL.

There is a flag: Parser.recompileAlways. If set, statements are always re-compiled. The flag is set for example if the query contains "like" conditions or non-deterministic method calls (see the source code). I guess you could extend that, so that if a table of a certain kind is used within the query, the flag is set in any case. I guess the database will still cache the result, you would need to change that part as well.

To truncate tables after running a statement, there is the (currently undocumented) syntax "create table test(id int) on commit delete rows" / "on commit drop". Maybe this is useful, even thought it is not much tested so far.

Regards,

Thomas

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

[Atul]

Hi Thomas -

The Parser flag is very helpful. Thank you.

Re: "on commit delete rows"

It seems the syntax is illegal when combined with ENGINE directive:

stat.execute("CREATE TABLE t2(id int, object_id int, name varchar)
ENGINE \"" + UserTableEngine.class.getName() + "\" on commit delete
rows ");

CREATE TABLE T1(ID INT, NAME VARCHAR) ENGINE
"com.corp.mode.tables.ObjectTableEngine" ON COMMIT DELETE ROWS

>> org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement



On Jul 17, 12:26 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> >http://groups.google.com/group/h2-database?hl=en.- Hide quoted text -
>
> - Show quoted text -

[Thomas Mueller]

Hi,

Re: "on commit delete rows"

Currently this only works for temporary tables.

Regards,

Thomas