Mendix Java ERROR: operator does not exist: bigint = character varying Hint: No operator matches the given name and argument types

Question

After upgrading to v9 in Mendix, one of our Java actions is causing an error. The error can be seen below: ERROR: operator does not exist: bigint = character varying Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 1009

This Java is used to take in files from a list, process them and return a list of calculations

public java.util.List<IMendixObject> executeAction() throws Exception
    {
        this.DailyPricingList = java.util.Optional.ofNullable(this.__DailyPricingList)
            .orElse(java.util.Collections.emptyList())
            .stream()
            .map(__DailyPricingListElement -> XXXX.DailyPricing.initialize(getContext(), __DailyPricingListElement))
            .collect(java.util.stream.Collectors.toList());

        // BEGIN USER CODE

        List<IMendixObject> calculationsList = new ArrayList<IMendixObject>();
        
        IContext context = getContext();
        IDataTable calcs = Core.retrieveOQLDataTable(context, 
                "SELECT XXX,XXX,XX,SUM(XXX),SUM(XXXX),SUM(XXX),SUM(XXX),SUM(XXX),SUM(XXXX),XXX,XXX,XXX "
                + "FROM Administration.Calculations "
                + "WHERE Administration.Calculations_DailyPricing IN ("+(this.DailyPricingList.stream().map((dp) -> Long.toString(dp.getMendixObject().getId().toLong())).collect(Collectors.joining("','", "'", "'")))+") "
                        + "GROUP BY XXX,XXX,XXX,XXX,XXXX,XXX");
        calcs.forEach(row -> CreateObj(row,calculationsList,context));

        return calculationsList;
        
        // END USER CODE

Column names have been replaces with X's for security. I presume it involves the toString within the select statement however my Java is limited and would not know how to fix this.

Any help would be great

Tried removing the toString but seemed to error on the Collectors.joining

Answer 1

Move the creation of that SQL to a variable, and log it. Then grab the generated SQL from the log and try to manually execute it. Does it work? Is it valid SQL?

But by it's name, Administration.Calculations_DailyPricing sounds like it would be a numeric column type, so using the Collections.joining overload of

Collectors.joining("','", "'", "'")

would generate SQL like:

...WHERE Administration.Calculations_DailyPricing IN ('42', '99', '188')

Assuming Administration.Calculations_DailyPricing is numeric, the generated SQL should look like this:

...WHERE Administration.Calculations_DailyPricing IN (42, 99, 188)

so that joining should be simplified to:

Collectors.joining(", ")

so there are no quotes surrounding the numeric values. (The Mendix upgrade is apparently less forgiving attempting to match string values against numeric values.)

side note: if DailyPricingList is empty, then invalid SQL is going to be generated. Hopefully there is a safety check that was just not included in the example code.