How to pass datetime parameter to postgresql sql?

Question

I am using Jaspersoft studio to create a report. The data adapter is a postgresql query (to a postgres server). The query:

select * from dbo.FACTSALES where FactSales.saledatekey BETWEEN to_char($P!{StartDate}, 'YYYYMMDD')::INTEGER  AND to_char($P!{EndDate}, 'YYYYMMDD')::INTEGER

FactSales.saledatekey is Integer.

My plan is to put two data time parameters (StartDate, EndDate) in the report so I can choose start and end dates to run the report. So the query is as above and I also created two parameters in Outline with the same names and of type java.sql.Timestamp (no default expression). But When i run the report I get Error executing SQL Statement.

EDIT

so I changed $P! to $P

select * from dbo.FACTSALES where FactSales.saledatekey BETWEEN to_char($P{StartDate}, 'YYYYMMDD')::INTEGER  AND to_char($P{EndDate}, 'YYYYMMDD')::INTEGER

But I still get the same error.

jrxml (I changed from select * to just two columns):

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="test4" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="fd68b751-49c2-4153-b2a8-48a95af021c9">
        <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
        <property name="com.jaspersoft.studio.data.defaultdataadapter" value="datamart"/>
        <property name="ireport.jasperserver.url" value="http://10.20.169.43:8080/jasperserver/"/>
        <property name="ireport.jasperserver.user" value="Nian"/>
        <property name="ireport.jasperserver.report.resource" value="/reports/NianTest/test4_files/main_jrxml"/>
        <property name="ireport.jasperserver.reportUnit" value="/reports/NianTest/test4"/>
        <parameter name="StartDate" class="java.sql.Timestamp">
            <parameterDescription><![CDATA[]]></parameterDescription>
        </parameter>
        <parameter name="EndDate" class="java.sql.Timestamp"/>
        <queryString>
            <![CDATA[select  customerkey, productkey from dbo.FACTSALES where $X{[BETWEEN], FactSales.saledatekey , to_char($P{StartDate} , 'YYYYMMDD')::INTEGER, to_char($P{EndDate}, 'YYYYMMDD')::INTEGER}]]>
        </queryString>
        <field name="customerkey" class="java.lang.Integer"/>
        <field name="productkey" class="java.lang.Integer"/>
        <background>
            <band splitType="Stretch"/>
        </background>
        <title>
            <band height="79" splitType="Stretch"/>
        </title>
        <pageHeader>
            <band height="35" splitType="Stretch"/>
        </pageHeader>
        <columnHeader>
            <band height="61" splitType="Stretch">
                <staticText>
                    <reportElement x="50" y="0" width="100" height="30" uuid="1471918d-b246-4aea-ba5c-d65a7fa48284"/>
                    <text><![CDATA[customerkey]]></text>
                </staticText>
                <staticText>
                    <reportElement x="268" y="0" width="100" height="30" uuid="361e8408-7859-483f-8143-5834fff3594d"/>
                    <text><![CDATA[productkey]]></text>
                </staticText>
            </band>
        </columnHeader>
        <detail>
            <band height="125" splitType="Stretch">
                <textField>
                    <reportElement x="50" y="49" width="100" height="30" uuid="3423ea0c-aa51-4537-ab28-29ae255c313c"/>
                    <textFieldExpression><![CDATA[$F{customerkey}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="200" y="40" width="100" height="30" uuid="d8381278-1fc1-4f9f-bd85-e65a85b77630"/>
                    <textFieldExpression><![CDATA[$F{productkey}]]></textFieldExpression>
                </textField>
            </band>
        </detail>
        <columnFooter>
            <band height="45" splitType="Stretch"/>
        </columnFooter>
        <pageFooter>
            <band height="54" splitType="Stretch"/>
        </pageFooter>
        <summary>
            <band height="42" splitType="Stretch"/>
        </summary>
</jasperReport>

and two parameters:

StartDate class: java.sql.Timestamp
EndDate class: java.sql.Timestamp

The error:

ERROR: function to_char(unknown, unknown) is not unique Hint: Could not choose a best candidate function. You might need to add explicit type casts

Your $P!{StartDate} is java.sql.Timestamp?, you know that when you use the ! you are doing string substitution?, futhermore there is a specially command for between query [using-parameters-queries](http://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v60/using-parameters-queries) — Petter Friberg, Apr 12 '16 at 11:29
I will suggest this as duplicate http://stackoverflow.com/questions/11871042/jasperreports-passing-parameters-to-query, Alex answer show the difference from $P{ and $P!{, however probably in your case I would have used $X{IS_BETWEEN, column, startParam, endParam} — Petter Friberg, Apr 12 '16 at 11:31
can you give a little bit of more detail? I am really new to this. — thotwielder, Apr 12 '16 at 11:46
if you use $P!{} it is pure string sostituzione (parameter should be string or int, not a class) and if you use $P{} that is better (no sql injection code) it uses prepared statement (? and does for example ps.setTimestamp($P{yourParam}) — Petter Friberg, Apr 12 '16 at 13:56
When you test you need to set default expression otherwise it is null, if you want an answer you need to show complete jrxml definition of parameters and table definition (column type for columns included) — Petter Friberg, Apr 12 '16 at 13:58

Petter Friberg · Accepted Answer · 2016-04-13T15:59:30.427

1

The query using prepared statement will be:

select customerkey, productkey from dbo.FACTSALES where FactSales.saledatekey BETWEEN to_char($P{StartDate}::timestamp, 'YYYYMMDD')::int  AND to_char($P{EndDate}::timestamp, 'YYYYMMDD')::int

or change you parameter to Integer and use the build-in between function

select customerkey, productkey from dbo.FACTSALES where $X{[BETWEEN], FactSales.saledatekey ,StartDate, EndDate}

If you like to test in IDE always consider to set default values to parameters.

<parameter name="StartDate" class="java.sql.Timestamp" isForPrompting="false">
    <defaultValueExpression><![CDATA[new java.sql.Timestamp(1l)]]></defaultValueExpression>
</parameter>
<parameter name="EndDate" class="java.sql.Timestamp" isForPrompting="false">
    <defaultValueExpression><![CDATA[new java.sql.Timestamp(new java.util.Date().getTime())]]></defaultValueExpression>
</parameter>

edited Apr 13 '16 at 15:59

answered Apr 12 '16 at 19:39

Petter Friberg

21,252
9
60
109

FactSales.saledatekey is integer (i.e 20160413), so yours won't work. That's why in my original query i converted the start/end dates to integer for the comparison. I have tried to converted salesdatekey to datetime, something like this dbo.FN_convertIntToDate(saledatekey) >= $P{StartDate} and dbo.FN_convertIntToDate(saledatekey) <= $P{EndDate}, where FN_convertIntToDate is a function, and it works, but just would like to know how to make the original work? – thotwielder Apr 13 '16 at 08:25
Aaah its an integer in that date format, I believe in timestamp format (ms), Use parameter that are Integer? – Petter Friberg Apr 13 '16 at 08:32
No, it doesn't work. I have actually tired this in my second version. I got this error: ERROR: function to_char(unknown, unknown) is not unique Hint: Could not choose a best candidate function. You might need to add explicit type casts. Position: 87 in Dataset and Query Dialog window. – thotwielder Apr 13 '16 at 11:06
Ok, and this? to_char($P{StartDate}::timestamp, 'YYYYMMDD')::int, but I would have passed int to report : ), to avoid all this casting... – Petter Friberg Apr 13 '16 at 11:13
Yeah, that works! Thanks. You can modify your answer with this piece of code. – thotwielder Apr 13 '16 at 13:52

How to pass datetime parameter to postgresql sql?

1 Answers1