How to configure jasper server with oracle 11g

Posted on May 14, 2013 at 7:37am
-1

Actually i installed jasper server 5.1 cp but sample file is not there in buildomatic for oracle configuration..

JasperReports® Server
bhuvneshwarsharma.bsr's picture
bhuvneshwarshar...
3
Joined: Jan 9 2013 - 9:20am
Last seen: 6 years 9 months ago

7 Answers:

0
Posted on May 14, 2013 at 10:04pm

Oracle is not supported for the repository DB in the community edition.  You can use it to report off of (as a data source), but the only repository DB's that are supported are PostgreSQL or MySQL.

mgeise's picture
mgeise
22977
Joined: Mar 5 2007 - 6:18am
Last seen: 2 months 1 week ago

" You can use it to report off of " , I am not getting meaning of this , can you please described it and does oracle support comertial version of jasper server ?

bhuvneshwarsharma.bsr - 6 years 9 months ago

There are two types of databases involved with JasperReports server - The database that holds the repository (report definitions, users, etc.) and the data sources that hold the data you are trying to analyze. The community edition does not support Oracle as the repository database. You can, however, connect to an Oracle database to do reporting and analysis if the data you are trying to analyze is in an Oracle database.

The commercial version of the server does support using Oracle as the repository database.

mgeise - 6 years 9 months ago

Thanks Mgeise ,
can we use jasper server for UI purpose means with jQuery Something .. ?

bhuvneshwarsharma.bsr - 6 years 9 months ago
add comment
0
Posted on November 22, 2013 at 12:25am
There's way how to turn off validation for Oracle DB in Buildomatic. I'm trying to use oracle as repository but can't resolve one error. When i'll find solution i can tell you - how to use oracle repository. But be ready to changing scripts and configs inside of buildomatic
ext.joseph.gette's picture
ext.joseph.gette
14
Joined: Nov 18 2013 - 1:53am
Last seen: 6 years 1 month ago

Did you figure it out?

joshua.pinto - 6 years 2 months ago
add comment
1
Posted on December 12, 2013 at 1:57am

1- download the latest bin file 

2- download Tomcat

3- download the jasperreport schema from the attachment

you can transfer postgres or mysql schema to oracle if you understand where is the difference.

4- create a user for example called jasper

and run the attached sql schema inside

5 - install tomcat

6- extract the jasperserver-3.1-bin.zip then extract the jasperserver.war inside the apache-tomcat\webapps\jasperserver\

7- download jdbc driver for oracle

7-put the ojdbc14.jar in apache-tomcat\common\lib

8- modify apache-tomcat\webapps\jasperserver\WEB-INF\hibernate.properties

modify the line starts with metadata.hibernate.dialect=

to be metadata.hibernate.dialect=org.hibernate.dialect.OracleDialect

9-  modify apache-tomcat\webapps\jasperserver\META-INF\jasperserverDS-jdbc.xml

modify the section <jdbc-driver-params> to be

  <jdbc-driver-params>
    <url>jdbc:oracle:thin:@localhost:1521:XE</url>
    <driver-name>oracle.jdbc.driver.OracleDriver</driver-name>
    <properties>
      <property>
        <name>user</name>
        <value>jasper</value>
      </property>
    </properties>
    <password-encrypted>jasper</password-encrypted>
  </jdbc-driver-params>

10 -  modfiy apache-tomcat\webapps\jasperserver\META-INF\context.xml

modify the section starts with  <Resource name="jdbc/jasperserver"  to be

    <Resource name="jdbc/jasperserver" auth="Container" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000"
            username="jasper" password="jasper" driverClassName="oracle.jdbc.driver.OracleDriver"
            url="jdbc:oracle:thin:@localhost:1521:XE"/>

 

This instruction will help you install Oracle repository. But there's another problem connected with memory leak. I didn't resolve it yet

ext.joseph.gette's picture
ext.joseph.gette
14
Joined: Nov 18 2013 - 1:53am
Last seen: 6 years 1 month ago

Hi - when you installed this way, I assume you did not already have an Oracle schema created and that you did not have to run any DDL scripts to create it ?

Did it create the schema/user, tables and objects when it found it did not exist or ?

If not, did you generate a DLL script from an existing postgres instance ?

bbaley - 6 years 2 months ago

Could you please explain me in detail the step 4

4- create a user for example called jasper
and run the attached sql schema inside

I couldn't find the attached sql scheme.
Greatly appreciate your help..

shantiprada - 5 years 10 months ago

thank you, i successfully configured the version jasperreports-server-cp-5.6.0-bin with oracle 11g xe

jargalejandro - 5 years 3 months ago
add comment
0
Posted on February 21, 2014 at 12:16pm
Today I have face the same problem.
 
We are using AWS RDS for our database and already have an Oracle instance for our data.
 
Then in our previous version of jasper (4.0.0) we had setup our Oracle as our datasource for the data and an MySQL for the jasperdb.
 
While this woked good it added lot of cost for us, so I looked at migrating to Oracle for the jasperdb.
 
It took me a while and many tries but then figured out that all it was missing was few configs to create the missing tables.
 
Since we still dont make use of any Pro features, but only an unsupported database, I assume it is ok to share my changes so others can take advantage of Oracle for the CE edition.
 
Again, this will only allow you to use Oracle as the database for the CE and does not add any of the Pro features.
 
The first thing I did is to follow the steps outlined here "http://community.jaspersoft.com/questions/543372/changing-jasper-server-..." but for Oracle instead of MySQL.
 
Here are the basic steps;
 
./js-export.sh --everything --output-zip postgresbackup.zip
 
Now modify the default_master.properties file with the required informations for your oracle instance. You can use the oracle_master.properties as a reference.
 
Then I had to change the hibernate dialect in the build_conf/default/hibernate.cfg.xml . Look for the hibernate line and change it for org.hibernate.dialect.Oracle10gDialect
 
Next, you will need to copy the ojdbc driver to the conf_source/ieCe/lib/
 
 cp conf_source/db/oracle/ojdbc6-11.2.0.2.jar conf_source/ieCe/lib/
 
now install the attached files as follow;
 
db.xml -> conf_source/db/oracle/db.xml
 
js-create.ddl -> install_resources/sql/oracle/js-create.ddl
 
 
 
Now you are ready to run the buildomatic scripts to create the Oracle repository.
 
./js-ant create-js-db
./js-ant create-sugarcrm-db 
./js-ant create-foodmart-db
./js-ant load-sugarcrm-db 
./js-ant load-foodmart-db
./js-ant update-foodmart-db
./js-ant init-js-db-ce
./js-ant import-minimal-ce
./js-ant import-sample-data-ce
 
Only the commands in bold are absolutely necessary; the others load sample databases and reports. It's up to you whether you want them or not.
 
 Next you should be able to run your import to populate your Oracle repository from your backup.
 
./js-import.sh --input-zip postgresbackup.zip
 
Once this is fully loaded you are ready to modify your webapp files to tell Jasper to use the new Oracle repository
 
- Next, modify your context.xml file
 
Your context.xml file, which is under [TOMCAT_HOME]/webapps/jasperserver/META-INF, will contain JNDI entries that point to the old Postgres database. You'll need to modify the entries there with the right JDBC URL's,  and the right username/password.
 
Also, make sure to delete the [TOMCAT_HOME]/conf/Catalina/localhost/jasperserver.xml file (if it's there), because Tomcat will check for the existence of this file before reading in the context.xml file you just modified.
 
- Start up your Tomcat server
 
- Log in (Hopefully this works)
 
 
 
Thanks to gdmoreno to give me the initial steps 
 
AndreC's picture
AndreC
98
Joined: Dec 18 2009 - 12:05pm
Last seen: 5 months 2 weeks ago

Sorry the attachments did not work,
here is a block you can add to your existing db.xml. Just note that this way you will create more tables than required but it seem they don't do any harm.

conf_source/db/oracle/db.xml

<!-- START - new section added by Andre on Feb 21 2014 for the oracle migration -->
<target name="init-js-db-pro"
description="run ddl for jasperserver pro db">
<runSQL jdbcUrl="${js.jdbcUrl}" print="true">
<fileset dir="${currentSqlDir}">
<include name="js-pro-create.ddl"/>
<include name="quartz.ddl"/>
</fileset>
</runSQL>
</target>

<target name="init-js-db-ce"
description="run ddl for jasperserver db">
<runSQL jdbcUrl="${js.jdbcUrl}" print="true">
<fileset dir="${currentSqlDir}">
<include name="js-pro-create.ddl"/>
<include name="quartz.ddl"/>
</fileset>
</runSQL>
</target>

<target name="init-quartz"
description="run ddl for js db">
<runSQL jdbcUrl="${js.jdbcUrl}" print="true">
<fileset dir="${currentSqlDir}">
<include name="quartz.ddl"/>
</fileset>
</runSQL>
</target>
<!-- END -->

AndreC - 5 years 12 months ago

thank you, i successfully configured the version jasperreports-server-cp-5.6.0-bin with oracle 11g xe

jargalejandro - 5 years 3 months ago
add comment
0
Posted on June 9, 2014 at 3:07am
I have the same problem. any help will be appreciated thanks in advance
omarntfs's picture
omarntfs
49
Joined: Jul 11 2012 - 6:25am
Last seen: 2 years 8 months ago
0
Posted on June 13, 2014 at 8:27am
When I'm runing js-ant import-minimal-ce I have the following error:
buildomatic\conf_source\ieCe\applicationContext-cascade.xml
     [java] Started to load resources
     [java] Resource name: applicationContext.xml
     [java] Resource name: applicationContext-cascade.xml
     [java] Resource name: applicationContext-data-snapshots.xml
     [java] Resource name: applicationContext-events-logging.xml
     [java] Resource name: applicationContext-export-config.xml
     [java] Resource name: applicationContext-export-import.xml
     [java] Resource name: applicationContext-logging.xml
     [java] Resource name: applicationContext-olap-connection.xml
     [java] Resource name: applicationContext-report-scheduling.xml
     [java] Resource name: applicationContext-search.xml
     [java] Resource name: applicationContext-security.xml
     [java] Resource name: applicationContext-themes.xml
     [java] Resource name: applicationContext-virtual-data-source.xml
    [java] org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'executionOwnerVoter': Injection of resource dependencies
failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'securityContextProvider': FactoryBean threw
exception on object creation; nested exception is org.springframework.beans.facorg.springframework.beans.factory.BeanCreationException: Error creating bean with
name 'executionOwnerVoter': Injection of resoutory.BeanCreationException: Error creating bean with name 'securityContextProviderTarget' defined in file [C:\Jasper
soft\jasperrrce dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with neports-server-cp-5.5
.0\buildomatic\conf_source\ieCe\applicationContext.xml]: Cannot resolve reference to bean 'userAuthorityService' while setting bean property 'userDetailsService
'; nested exception is org.springframework.beans.factory.BeanCreationException:Error creating bean with name 'userAuthorityService' defined in file [C:\Jaspers
oft\jasperreports-server-cp-5.5.0\buildomatic\conf_source\ieCe\applicationContext.xml]: Cannot resolve reference to bean 'sessionFactory' while setting bean
property 'sessionFactory'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFame 'securityContext
Provider': FactoryBean threw exception on object creation; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with
 name 'securityContextProviderTarget' defined in file [C:\Jaspersoft\jasperractory' defined in file [C:\Jaspersoft\jasperreports-server-cp-5.5.0\buildomatic\conf_source\ieCe\applicationContext.xml]:
Invoceports-server-cp-5.5.0\buildomatic\conf_source\ieCe\applicationContext.xml]: Cannot resolve reference to bean 'userAuthorityService' while setting bean property 'userDetailsService';
nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'userAuthorityService' defined in file [C:\Jaspersoft\jasperreport
s-server-cp-5.5.0\buildomatiation of init method failed; nested exception is org.hibernate.HibernateException: Dialect class not found: com.jaspersoft.ji.hic\
conf_source\ieCe\applicationContext.xml]: Cannot resolve reference to bean 'sessionFactory' while setting bean property 'sessionFactory'; nested exception is org.
springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory' defined in file [C:\Jaspersoft\jasperreports-server-cp-5.5.0
\buildomatic\conf_source\ieCe\applicationContext.xml]: Invocbernate.dialect.OracleJICustomDialect
[java]     at org.springframework.context.annotation.CommonAnnotationBeanPo
stProcessor.postProcessPropertyValues(CommonAnnotationBeanPostProcessor.java:306
)
 
 
any help please
thanks
omarntfs's picture
omarntfs
49
Joined: Jul 11 2012 - 6:25am
Last seen: 2 years 8 months ago
1
Posted on June 19, 2014 at 3:08am

I solved it by changing js.jdbc.properties manually, by modifying these properties:
# Property that determines the Hibernate dialect
metadata.hibernate.dialect=org.hibernate.dialect.OracleDialect

# jasperserver repo db settings
metadata.jdbc.driverClassName=oracle.jdbc.OracleDriver
metadata.jdbc.url=jdbc:oracle:thin:@localhost:1521:xe
metadata.jdbc.username=jasperserver
metadata.jdbc.password=password
metadata.jdbc.password.escapeXML=password

metadata.jndi=jdbc/jasperserver

metadata.database.generate=true

I post the answer in case someone has the same problem.

omarntfs's picture
omarntfs
49
Joined: Jul 11 2012 - 6:25am
Last seen: 2 years 8 months ago

thank you, i successfully configured the version jasperreports-server-cp-5.6.0-bin with oracle 11g xe

jargalejandro - 5 years 3 months ago

Where is that js.jdbc.properties file located? I am trying to configure Jasper Server 6.2 with Oracle 11g XE and cannot find that :/

Krzysztof.Ir - 4 years 1 month ago
add comment
Feedback