How to Pass Array of Variable Length Strings to PostgreSQL Stored Function

Issue Description

Customer has a requirement to create a report which calls custom PostgreSQL function with 'character varying[]' parameter type (array of variable string).

The question is how to pass java.util.Collection parameter to the function?

Resolution

Let's assume the custom function DLL looks like

CREATE OR REPLACE FUNCTION p1_matrix_arr_text(agreementtype_ar character varying[])

  RETURNS text AS ....

In JasperReports we usually use java.util.Collection type to work with list of values but java.util.Collection object can not be used directly in query in JasperReport.

Within PostgreSQL the following SQL should be used to call the function:

select p1_matrix_arr_text from p1_matrix_arr_text ('{par_value1,par_value1}')

In order to 'convert' JasperReports' collection into the array of strings, we will create a Scriptlet with following Java code:

public String Collection2String(java.util.Collection myCollection) throws JRScriptletException
    {
        StringBuilder sb = new StringBuilder();
        Integer cnt = new Integer(1);

        sb.append("'{");
        for (Object obj : myCollection) {
            String s = (String) obj;
            if (cnt == 1) {sb.append(s);} else {sb.append(",").append(s);}
            ++cnt;

        }
        sb.append("}'");   
        return sb.toString();
    }

The scriptlet function returns a string like {val1,val2,val3,...,valN} built on inbound java.uitl.Collection parameter.

The next step is to compile the scriptlet, pack it to a JAR file and configure Jaspersoft Studio to use that scriplet.

In report JRXML file, we need to specify 'Scriptlet Class' property to use our custom class.

Due to some limitations, JasperReports Library does not allow using Collection type parameter in query, even if it as a part of expression like

select p1_matrix_arr_text from p1_matrix_arr_text($P{REPORT_SCRIPTLET}.Collection2String($P{p_collection}))

To avoid this limitation, we need to create a new String parameter p_string with expression

$P{REPORT_SCRIPTLET}.Collection2String( $P{p_collection})

So, the report query we use is:

select p1_matrix_arr_text from p1_matrix_arr_text($P!{p_string})

As a result, we can run the report, pass collection to stored function and get the function output.

In order to make the report work in JasperReports Server, the scriptlet JAR file should be placed into WEB-INFlib folder.

Attached are complete scriptlet code and report JRXML file

Ref. Case 01443237

myutils.java

pgsql_function.jrxml