How to configure jasper server with oracle 11g

[bhuvneshwarsharma.bsr]

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

[mgeise]

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.

[bhuvneshwarsharma.bsr]

" 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 ?

[mgeise]

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.

[bhuvneshwarsharma.bsr]

Thanks Mgeise ,

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

 

[ext.joseph.gette]

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

[joshua.pinto]

Did you figure it out?

[ext.joseph.gette]

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-tomcatwebappsjasperserver

7- download jdbc driver for oracle

7-put the ojdbc14.jar in apache-tomcatcommonlib

8- modify apache-tomcatwebappsjasperserverWEB-INFhibernate.properties

modify the line starts with metadata.hibernate.dialect=

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

9-  modify apache-tomcatwebappsjasperserverMETA-INFjasperserverDS-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-tomcatwebappsjasperserverMETA-INFcontext.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

[bbaley]

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 ?

 

[AndreC]

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-postgres-mysql" 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]

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

 

 

description="run ddl for jasperserver pro db">

 

description="run ddl for jasperserver db">

 

description="run ddl for js db">

 

 

[shantiprada]

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..

 

 

[omarntfs]

I have the same problem. any help will be appreciated

thanks in advance

[omarntfs]

When I'm runing js-ant import-minimal-ce I have the following error:

buildomaticconf_sourceieCeapplicationContext-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

softjasperrrce dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with neports-server-cp-5.5

.0buildomaticconf_sourceieCeapplicationContext.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

oftjasperreports-server-cp-5.5.0buildomaticconf_sourceieCeapplicationContext.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:Jaspersoftjasperractory' defined in file [C:Jaspersoftjasperreports-server-cp-5.5.0buildomaticconf_sourceieCeapplicationContext.xml]:

Invoceports-server-cp-5.5.0buildomaticconf_sourceieCeapplicationContext.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:Jaspersoftjasperreport

s-server-cp-5.5.0buildomatiation of init method failed; nested exception is org.hibernate.HibernateException: Dialect class not found: com.jaspersoft.ji.hic

conf_sourceieCeapplicationContext.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:Jaspersoftjasperreports-server-cp-5.5.0

buildomaticconf_sourceieCeapplicationContext.xml]: Invocbernate.dialect.OracleJICustomDialect

[java]     at org.springframework.context.annotation.CommonAnnotationBeanPo

stProcessor.postProcessPropertyValues(CommonAnnotationBeanPostProcessor.java:306

)

 

 

any help please

thanks

[omarntfs]

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.

[jargalejandro]

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

[jargalejandro]

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

[jargalejandro]

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

[Krzysztof.Ir]

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 :/