Get sql before execute

Support
#1

Hi,

is there a way to get the created SQL before execution?
I do not mean the JPQL and not the trace info in logfiles.

Would like to get the complete SQL which will be send to the DB just before execution.

Or, if that is not possible, can I get the complete JPQL?
But do not want to write my own decompiler for the parameters/where clauses/constraints…
So, really the complete JPQL just before it gets translated into SQL.

Regards
Roland

#2

Hi Roland,

Currently you can get and modify the final JPQL if you override the com.haulmont.cuba.core.sys.QueryImpl bean and its transformQueryString() method.
If you explain your motivation, we’ll consider introducing a more simple way of doing this.

As for SQL generated from JPQL - you cannot affect it directly, you can only see it in the logs.

Regards,
Konstantin

#3

Hi Konstantin,

thx for your response.

First the funny answer:
Before we came to Cuba, we have been working with Codecharge, and Alexsey asked me to tell him advantages of Codecharge, because he could not imagine, that there would be something Cuba cannot do better.
And here is one, getting the before execute (any many others) in Codecharge was a standard method. :wink:

And here the real one:
In almost any project we get the request to download data into csv files.
But, the amount of data is often up to 1 million rows.
Usually we try to use other existing tools to do that.
But that does not really work with so much rows.
In Cuba we can use the standard tables and even a pivot table or chart, to create/find and handle needed filter settings.
Because with your enhancement you did for saving filter xml not only related to screens, we can provide a very useful app for filtering data.
After getting the needed filter, we would like to use a report where we would like to pass the sql for downloading the data into a csv file.
In theory it works fine,
but analyzing (recompiling) the paramters and also the constraints would be a too complex work, even when there would be a change in the framework like it has been for constraints the last months.

So, just getting the before execute sql and pass it to a report would be great.
In that way, we would directly also have the constraints used, which are not included in reports up to now.
No constraints in reporst makes it almost impossible to handle user group based data security.

Regards
Roland

#4

So you actually need the SQL query that will be executed on the database for a certain filter conditions to send it somewhere and execute by an external tool?

#5

Hi Konstantin,

we want to use the Report AddOn to download big amount of rows as csv.
That report contains only 1 paramter ${SQL}…
We use the standard table and filter to define the needed filtering and would then
run the report, passing the complete SQL (or JPQL) to that parameter
(including all filter settings and the constraints).
Doing this, we could combine the table with the reports and also the missing support of constraints in the Report AddOn would be solved.

And, the cherry on the cream would be, if the information about non_collapsed columns would be reflected in that SQL/JPQL. :slight_smile:

Regards
Roland

#6

You can use a load delegate in your screen to get the resulting JPQL:

@Inject
private DataManager dataManager;

@Inject
private ConditionJpqlGenerator conditionJpqlGenerator;

@Install(to = "ordersDl", target = Target.DATA_LOADER)
private List<Order> ordersDlLoadDelegate(LoadContext<Order> loadContext) {
    String queryString = loadContext.getQuery().getQueryString();
    Condition condition = loadContext.getQuery().getCondition();

    if (condition != null) {
        Set<String> nonNullParamNames = loadContext.getQuery().getParameters().entrySet().stream()
                .filter(e -> e.getValue() != null)
                .map(Map.Entry::getKey)
                .collect(Collectors.toSet());
        Condition actualized = condition.actualize(nonNullParamNames);
        queryString = conditionJpqlGenerator.processQuery(queryString, actualized);
    }

    System.out.println(">>>>>> start JPQL");
    System.out.println(queryString);
    System.out.println(">>>>>> end JPQL");

    return dataManager.loadList(loadContext);
}

Collapsed columns don’t affect the query in any way, but you can get it from the table and use in the report somehow.

#8

Hi Konstantin,

I get your solution running,
but the output is not what I expected.
It shows me the parameternames, but not the parametervalues:
I see this:
select e from rusdwh_CubaSalesPrimarySecondary e where e.brandCode = :filter_brandCode87563
But I would expect this:
select e from rusdwh_CubaSalesPrimarySecondary e where e.brandCode = ‘52’

//brandCode is String format

so I will have a look, how to overwrite the bean you mentioned…

#9

You can get the parameter values from the loadContext.getQuery().getParameters() map.

#10

thx, I got those parameters, but, do you mean, I should then replace them on my own, right?

#11

If you need a single query string - then yes, you have to replace them. It should be easy for simple values like strings or numbers.

#12

Hi, yes, you are right… but, because we have to handle all kind of attribute types and then all types of filter (equal, contains…) it will become too complex … I will see how we can do it with the overwrite of the QueryImpl class

#13

I got the bean overwritten and had a look at transformQueryString() …
In the result I can see the constraints now added to the query.
But, the queryString still contains the parameter names and not the values.

Isn’t there place where I can get the final JPQL/SQL before it is executed?

Regards
Roland

#14

Parameters are never included into the query string, even on the SQL level.
SQL queries are executed using JDBC PreparedStatement, which accepts parameters separately.

Regards,
Konstantin