Jaspersoft BI Tools

iReport,Jaspersoft Design Studio,Jaspersoft Server, Schema Workbench,Analysis,Dashboards, Self Service BI, Domains,Topics,Ad-Hoc Reports, Jaspersoft ETL, Embedding BI,HTML-5 charts, Fusion Charts,JFree Charts,Drill down reports , Drill down Dashboards,Visualize.js, Performance Tuning, D3 Charts, Jaspersoft Customization

Wednesday, March 05, 2014

Connecting to Oracle databse from Jasper iReport & Timezone issue

Hi Guys,
While connecting to Oracle database from Jasper iReport you may get time zone error message .. to over come this you need to set the timezone in your iReport.

How to connect to Oracle database from jasper iReport ?
1) First thing you need to know which database version you are using (either oracle 10g/11g/12)
2) You need to download the appropriate jdbc connector from oracle site( you need to have an account for oracle to download)
For example :
To connect 10g database, you need to download classes.12.jar file from below oracle site
http://www.oracle.com/technetwork/database/enterprise-edition/jdbc101040-094982.html

Give a try with ojdbc14.jar file also.

NOTE:
Different versions of Oracle database uses different jar files

3) Placing jar files in iReport
Approach-I
Once the download is completed you need to add that jar file in iReport lib folder which you can find in iReport installation folder.
Location to place the jar file 
C:\Program Files\Jaspersoft\iReport-5.5.0\ireport\libs
Close the iReport if you opened already and Open/start it again(It will load the jar file when you restart the iReport)

Approach-II
Alternatively you can add the jar file in classpath option of Tools
i.e., Go to Tools->Options->Class Path->Add jar file
Once you add the suitable jar file you need to check that jar file.


Approach-III
i) Go to Window ->Click on services(Ctrl+F5)
ii) Expand Database ->You will find Drivers ->Right click on it -> Add jar ->It pop ups a window asking you to locate jar file.. locate the suitable jar file for you Oracle jdbc connection.

Either of the 3 approaches given above must work to add Oracle jdbc driver to iReport.

Now Connecting to Databse
1) Click on Databse icon ->Click New->Click on JDBC Connection  Click on next.
2) JDBC Driver class
Oracle (oracle.jdbc.driver.OracleDriver)
[This should be black in color which means that you have added oracle jdbc connector to iReport using above either 3 approaches]
3) URL
Syntax : jdbc:oracle:thin:@localhost:1521:DatabaseName 
Example1 : jdbc:oracle:thin:@localhost:1521:orcl (where orcl is SID of your Oracle)
Example 2: jdbc:oracle:thin:@192.168.2.9:1521:orcl (Remote database connection using IP)
4) Username & Password
For example : sadakar/sadakar
5) Click on Test Connection .. If the Connection is successful click on OK button.



Where you need to set the timezone in Jasper iReport ?

1) Go to Tools->Options
2) Click on Compilation and Execution
3) Select Report Timezone from the list of drop down
   for example :
   GMT0 (GMT+00:00)
 
Hope this information might be useful for newbies :D