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 <[email protected]
<javascript:_e(%7B%7D,'cvml','[email protected]');>> 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 <[email protected]
> <javascript:_e(%7B%7D,'cvml','[email protected]');>>:
>
>> 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 <[email protected]>
>> 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 <[email protected]>
>>> :
>>>
>>>> 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 <[email protected]>:
>>>>
>>>>> 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.
>>>>>
>>>>> --
>>>>> 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 [email protected].
>>>>> To post to this group, send email to [email protected].
>>>>> Visit this group at http://groups.google.com/group/h2-database.
>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>
>>>>
>>>> --
>>>> 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
>>>> [email protected].
>>>> To post to this group, send email to [email protected].
>>>> Visit this group at http://groups.google.com/group/h2-database.
>>>> For more options, visit https://groups.google.com/d/optout.
>>>>
>>>
>>> --
>>> 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 [email protected].
>>> To post to this group, send email to [email protected].
>>> Visit this group at http://groups.google.com/group/h2-database.
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>> --
>> 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
>> [email protected]
>> <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');>
>> .
>> To post to this group, send email to [email protected]
>> <javascript:_e(%7B%7D,'cvml','[email protected]');>.
>> Visit this group at http://groups.google.com/group/h2-database.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> 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 [email protected]
> <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');>
> .
> To post to this group, send email to [email protected]
> <javascript:_e(%7B%7D,'cvml','[email protected]');>.
> Visit this group at http://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
