How to extract the where clause from a Select object?

[Pablo Beltran]

Hi,

Given a SQL quer... how to get the where clause? 

...

String sql = "select * from T where col1=value1 and col2=value2 or col3=value3";

Parser parser = new Parser(session);

Select select = (Select) parser.prepare(sql);

String where = ...

//How to get the where clause? "col1=value1 and col2=value2 or col3=value3"

Thanks!

Pablo.

[Fred&Dani&Pandora&Aquiles]

Hi,

You can find it in the condition attribute of the Select class.

Regards,

Fred

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
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/d/optout.

[Pablo Beltran]

Hi Fred,

I've searched for the condition attribute in the org.h2.command.dml.Select class source code and it's declared as private:

private Expression condition;

and I was unable to find any public (nor other) method to read it.

How do you access to the condition attribute?

Thanks!

Pablo.

--
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/5XkMAGROV8Q/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

[Thomas Mueller]

Hi,

> how to get the where clause? 

Well, why do you need it exactly?

Regards,

Thomas

[Pablo Beltran]

Hi Thomas,

I want so support SQL for a commercial product which has it own custom query syntax.

At the earliest try, I used H2 function tables which worked perfectly:

select * from H2_FUNCTION_TABLE(custom_syntax)

then I captured the custom_syntax parameter and wrapped the result data into a SimpleResulset, but.. it did not work well with JasperReports as it does not understand the H2 syntax for function tables.

Then, the second try was create a pluggable table:

select * from H2_PLUGGABLE_TABLE where custom_syntax

but it did not work because the H2 parser tries to parse the custom_syntax. So accessing to the WHERE clause is not already necessary.

Finally, I supported the 3rd party queries as a parameter of a pluggable table:

select * from H2_PLUGGABLE_TABLE where special_param='custom_syntax'

as the special_param is defined as VARCHAR, no problem with the H2 parser. So really the problem was moved from requiring to access to the where clause to access to the query conditions which is the same kind of problem as neither the where clause nor the query conditions are accessible via API. 

I resolved it in the simplest way by modifying the sources and adding a new public method to make the private conditions attribute accessible. In this way when a query is run against the pluggable table, I capture it and look for the special-param in the query conditions, run it against the 3rd party system and wrap the results in a SimpleResultset.

It works nicely!! and therefore end users will be able to run custom_syntax queries like SQL queries from any standard industry reporting tool like Birt, jasperReports, etc.

I've provided a bit large answer because I think this is an interesting business case for H2 as it can be used to wrap any custom language (even Lucene queries, for instance) into standard SQL queries.

Thanks!

Pablo. 

 

[Pablo Beltran]

Hi Thomas,

This brings a further question:

It looks like H2 runs twice the same query when it includes an OR in the where clause. For example:

select address from PERSONS where name='John' OR name ='Peter'

they work well separately:

=========================

select address from PERSONS where name='John';

address1

=========================

select address from PERSONS where name='Peter';

address2 

=========================

however combining the above queries with an OR  produces duplicate (and unexpected results):

=========================

select address from PERSONS where name='John' OR name ='Peter' 

address1

address2

address1

address2

=========================

This is surely because a my misunderstanding about H2 internals.

In my pluggable table I read the condition (name='John' OR name'Peter') and collect all of them, so I know I've to add results for John and Peter only, then  I add address1 and address2 into the SimpleResulset.

However, the same code is called again, so it adds the duplicate results:

So I guess the pluggable table is invoked by H2  twice, once for John and once for Peter. A sort of UNION:

select address from PERSONS where name='John' OR name ='Peter'

seems to be equivalent to:

select address from PERSONS where name='John' 

UNION

select address from PERSONS where name ='Peter'

How could I identify the conditions applied in each stage:

First, name='John' only and later the same for the next condition name='Peter'.

Thanks,

Pablo.

[Pablo Beltran]

Hi Thomas again.

please do not waste your time on this. I've seen that H2 ask for the data rightly, It asks for John and later for Peter data. The problem was that my plugged table is a wrapper of an underlying FunctionAlias and I took a the code code of few classes for that  and therefore I inherited some undesired behaviors from the org.h2.index.FunctionIndex among other classes which I modified:

 public Cursor find(Session session, SearchRow first, SearchRow last) {

        if (functionTable.isBufferResultSetToLocalTemp()) {

            return new FunctionCursor(functionTable.getResult(session));

        }

        return new FunctionCursorResultSet(session,

                functionTable.getResultSet(session));

    }

The method above does not pass forward the SearchRow parameters to the functionTable (only the session is forwarded), so such information was lost from the functionTable (which I modified too). This is the reason because I needed to access to the select object conditions. I realized now that it was totally unnecessary furthermore stupid because parsing a query from inside a parsed query had not too much sense and it also was pretty difficult to make it work rightly (it was like replacing and existing wheel by a square).

Thanks,

Pablo.

 

On Saturday, April 11, 2015 at 5:08:19 PM UTC+2, Pablo Beltran wrote:

[Thomas Mueller]

Hi,

> I want so support SQL for a commercial product which has it own custom query syntax.

OK. Please be aware that there is a potential license problem, unless one of the following applies:

- the commercial product is open source, or

- integrate H2 in such a way that you don't modify the source code of H2

 If you do modify the source code of H2, you would need to publish those changes.

> it did not work well with JasperReports as it does not understand the H2 syntax for function tables.

Does JasperReports really need to understand the syntax? I think JasperReports should have an option to not parse the SQL statement.

> It looks like H2 runs twice the same query when it includes an OR in the where clause

For the case "name = 'x' or name = 'y'", H2 would convert that to "name in ('x', 'y'). It would use an index on "name" (the same index twice). "Union" is not needed in this case.

For the case "name = 'y' or firstName = 'x'", H2 would not use an index, which is unfortunate. It should convert it to "union".

Regards,

Thomas

On Tue, Apr 14, 2015 at 8:02 PM, Pablo Beltran <pbel...@gmail.com> wrote:

Hi Thomas,

I want so support SQL for a commercial product which has it own custom query syntax.

At the earliest try, I used H2 function tables which worked perfectly:

select * from H2_FUNCTION_TABLE(custom_syntax)

then I captured the custom_syntax parameter and wrapped the result data into a SimpleResulset, but.. it did not work well with JasperReports as it does not understand the H2 syntax for function tables.

Then, the second try was create a pluggable table:

select * from H2_PLUGGABLE_TABLE where custom_syntax

but it did not work because the H2 parser tries to parse the custom_syntax. So accessing to the WHERE clause is not already necessary.

Finally, I supported the 3rd party queries as a parameter of a pluggable table:

select * from H2_PLUGGABLE_TABLE where special_param='custom_syntax'

as the special_param is defined as VARCHAR, no problem with the H2 parser. So really the problem was moved from requiring to access to the where clause to access to the query conditions which is the same kind of problem as neither the where clause nor the query conditions are accessible via API. 

I resolved it in the simplest way by modifying the sources and adding a new public method to make the private conditions attribute accessible. In this way when a query is run against the pluggable table, I capture it and look for the special-param in the query conditions, run it against the 3rd party system and wrap the results in a SimpleResultset.

It works nicely!! and therefore end users will be able to run custom_syntax queries like SQL queries from any standard industry reporting tool like Birt, jasperReports, etc.

I've provided a bit large answer because I think this is an interesting business case for H2 as it can be used to wrap any custom language (even Lucene queries, for instance) into standard SQL queries.

Thanks!

Pablo. 

 

2015-04-14 19:28 GMT+02:00 Thomas Mueller <thomas.to...@gmail.com>:

Hi,

> how to get the where clause? 

Well, why do you need it exactly?

Regards,

Thomas

On Sun, Apr 12, 2015 at 6:39 PM, Pablo Beltran <pbel...@gmail.com> wrote:

Hi Fred,

I've searched for the condition attribute in the org.h2.command.dml.Select class source code and it's declared as private:

private Expression condition;

and I was unable to find any public (nor other) method to read it.

How do you access to the condition attribute?

Thanks!

Pablo.

2015-04-12 12:16 GMT+02:00 Fred&Dani&Pandora&Aquiles <zep...@gmail.com>:

Hi,

You can find it in the condition attribute of the Select class.

Regards,

Fred

2015-04-11 12:08 GMT-03:00 Pablo Beltran <pbel...@gmail.com>:

Hi,

Given a SQL quer... how to get the where clause? 

...

String sql = "select * from T where col1=value1 and col2=value2 or col3=value3";

Parser parser = new Parser(session);

Select select = (Select) parser.prepare(sql);

String where = ...

//How to get the where clause? "col1=value1 and col2=value2 or col3=value3"

Thanks!

Pablo.

--