CAP Java Query Builder API - How to excute HANA functions/procedures?

[christoffer_fuss]

Hi,

We are using CAP Java and HANA Cloud. We can succesfully execute simple SQL statements by using the Query Builder API but we have some problems with more complex queries, for example:

1. How can we execute a query with "count distinct". I can add a distinct() function but this is not working in the count function:
   

   Select.from(Apicalls_.class)
         .distinct()
         .columns(c -> func("TO_DATE", c.Date()).as("date"), 
                  c -> func("COUNT", c.UserId()).as("count"))
         .groupBy(g -> func("TO_DATE", g.Date()))
         .orderBy(o -> func("TO_DATE", o.Date()).asc())

2. How can we execute the HANA Fuzzy search methods?
3. How can we execute HANA functions or procedures?

I find many examples for Node.js but the examples for CAP Java are always very simple SQL statements....
Can anybody help with some examples?

Best Regards,

Chris

[former_member41752]

Hi all,

I have the following problem. My SQL like this:

SELECT TO_DECIMAL(SCORE(),3,2) score, T0.title as "title"<br>FROM MY_BOOKSHOP_BOOKS T0 WHERE CONTAINS(T0.title, 'olbia city', FUZZY(0.7,'similarCalculationMode=substringsearch'))<br>order by SCORE() DESC;

How can I build it with CQL statements in Java? I don't know how to execute a query with "order by SCORE() DESC".

Select.from(Books_.class) .where(e -> booleanFunc("CONTAINS", Arrays.asList(CQL.get(Books.TITLE).asRef(), val("olbia city"), plain("FUZZY(0.7, 'similarCalculationMode=substringsearch')"))))

Best regards,

Lihua

[former_member41752]

I have solved it with subquery.

[adrian_goerler]

Hi,

> there is an SQL exception

I have to confirm that the generated SQL is wrong. Thanks for bringing this to our attention. We will provide a fix with the upcoming release 1.11.0.

Best regards,

Adrian

[adrian_goerler]

Hi,

there was a mistake in the answer I provided: you need to use a Value as the argument for inner function. You can use CQL.literal for the conversion:

Select.from(AUTHOR).where(a -> a.name().func("contains", "search term", CQL.func("fuzzy", CQL.literal(0.7))));

[former_member708468]

Hello,

I have tried the above as well, still without success. The function accepts the arguements (String, Value<?>....), and since the second agruement provided in the example is a String, it does not compile. I have tried substituting it for CQL.literal and CQL.plain, but even after doing so, the code compiles, but there is an SQL exception.

1) Using CQL.literal:
Code: "500"
"SQLException executing >>SELECT T0.\"TEXT\", T0.\"ID\" FROM \"DOCUMENTS\" T0 WHERE contains(T0.\"TEXT\", ?, fuzzy(?)) with parameters('LOCALE' = 'de')<<"

2) Using CQL.plain:
Code: "500"
SQLException executing >>SELECT T0.\"TEXT\", T0.\"ID\" FROM \"DOCUMENTS\" T0 WHERE contains(T0.\"TEXT\", Search Term, fuzzy(0.7)) with parameters('LOCALE' = 'de')<<"

A simple select with a where condition like below

.where(a -> a.Text().contains("Search term")));

works just fine.

Do you have any suggestions how to fix this?

[adrian_goerler]

Hi,

1. count distinct

a) count distinct is avaliable via OData using the standard OData $apply aggregation method countdistinct.

b) with release 1.11 we will add a countDistinct method to the Value and CQL apis.

c) As long as 1.11 is not released you can use the following (ugly) workaround:

Select.from(AUTHOR).columns(a -> func("count", CQL.plain("distinct " + columnName)).as("cnt"))

2.) To use fuzzy search on a single column you can nest function calls, e.g.:

Select.from(AUTHOR).where(a -> a.name().func("contains", "search term", CQL.func("fuzzy", 0.7)));

for a multi column fuzzy search you'd have to resort to executing plain SQL, at the time being, I am afraid.

3.) We do not have a high-level API to call functions or procedures on HANA. Instead, provided you a are running on Spring, we recommend to use plain SQL for such purposes. Please consider to use Spring's JdbcTemplate, which you may use to execute a SimpleJdbcCall.

Best regards,

Adrian

[former_member708468]

Hello Adrian,

Thank you for your answer. I tried the solution you provided for the Fuzzy search in 2.), but the CQL.func does not accept a double as an argument and demands a CqnValue.

I am also not sure, that nesting funciton calls is possible, because when I provide an example of "CqnValue b = null" to the CQL.func method, it says that provided arguements are not applicable as well.

an you please assist me on this issue?

Best regards,
Vsevolod

[christoffer_fuss]

Hi Adrian,

Thanks 🙂 The count distinct is working with the workaround 🙂
I have the same problem with the fuzzy search than mentioned in the comment above.
Is it planed to offer a high-level API to call functions or procedures on HANA in future releases?

Best Regards,

Chris