How to configure JDNI in Japser Reporting Server on Mac!!!

Recently when I try to generate a report from Jasper server, I got the below error and it took me lot of time to solve. So posting here to help others.

org.postgresql.util.PSQLException: ERROR: transaction is read-only Detail

One of the reason for getting this error is setting wrong Data source. Initially I set the data source as JDBC Driver. After doing all the possibilities , tried to change the data source from JDBC to JDNI, and it was worked successfully. Below are the steps to set JDNI drive on Jasper reporting server.

1. Open context.xml file (path is given below in Mac OS X)
2. Copy and paste existing resource and edit (given below how to do this) 
3. Login to the server
4. Goto view->repository->public->data source
5. Right click Data Sources -> add resource -> Data Source
6. Check Type as JNDI data source
7. Give appropriate Name, ID and description 
8. Give service name which specified in context.xml file
9. Save the settings by specifying particular location for later use
10. Test Connection to check success/failure

in Mac path for context.xml:
/Applications/jasperreports-server-5.5/apache-tomcat/webapps/jasperserver-pro/META-INF/context.xml

Changing resource in context.xml file:

    <Resource name="jdbc/jasperserver" auth="Container" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000"
        username="jasperdb" password="password" 
        driverClassName="org.postgresql.Driver"
        validationQuery="SELECT 1"
        testOnBorrow="true"
        url="jdbc:postgresql://127.0.0.1:5432/jasperserver?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&autoReconnectForPools=true"
        factory="com.jaspersoft.jasperserver.tomcat.jndi.JSBasicDataSourceFactory"/>

we need to modify name, username, password and url fields in the resource.
name: This will be used while configuring Dat Source
username: user name of your data base
password: Password of your data base
url: url of your data base.

Happy Reading!!!

function date_trunc("unknown", "unknown") is not unique - SQL

Recently I started working on Japser Studio professional for my new project Cloud to generate the reports. I was very new to all cloud technologies including PostgreSql. I faced lot of problems during this time. below is one of such problem, which kill lot of my time. Here is problem and its solution.

Problem statement: Need to generate usage data reports.

To generate usage data reports, I need to compare the existing timestamp from the table with user given data. For comparing data I used date_trunc()  to get year/month/day etc. Here I was getting above error. Below is the query for that where ts is from test_reports table.

select * from test_reports where (date_trunc('day',ts)


ERROR: function date_trunc("unknown", "unknown") is not unique
  Hint: Could not choose a best candidate function. You may need to add explicit type casts. [SQL State=42725]

This error is due to data type(timestamp) mismatching. By seeing/analysing the error, I found its due to mismatching the time. But no idea how to solve it and googled for it like hell. Luckily our TL has suggested the solution that prepending timestamp to the user input variable.

select * from test_reports where (date_trunc('day',ts)timestamp

 '2014-03-21 14:07:00'))
Jasper studio professional:

Need to create the input parameter and pass that parameter as an argument to the sql query. Here are the steps to create the parameter.

1. Go to outline window in the Jasper studio
2. Right click on the Parameters tab and click on "Create Parameter"
3. Then click on the created parameter and go to the properties window
4. Change the name to appropriate one
5. Change the class name to java.sql.timestamp
6. Change the default expression to new Date() as it will give the current date as default value
7. Make the isPrompt check box enable for getting the data from user

Now we can able to read the date value from the user into this newly created parameter.

Usage of the parameter in generating reports:

Need to follow below syntax for  using parameter in the reports generation.

$P!{parameter_name}

select * from test_reports where ts < '$P!{user_date}'

If we use same parameter in date_trunc() function, we will face type mismatch error.

select * from test_report where date_trunc('day',update_ts)

To solve that error, you can prepend 'timestamp' which will convert to required type.

select * from test_report where date_trunc('day',update_ts)timestamp
'$P!{user_date}')

Thanks for reading !!!!