Install Jasperserver comunity edition on oracle

[mgolisch]

Hi,

We would like to install jasperserver and have it use oracle instead of the default mysql for its metadata database.

Is there some documentation on howto do that?

Or maybe there even is ready to use sqlstatements and instructions on howto tweak the config files for it to work out.

Like it seems it just uses the database connection defined in tomcats config file, so it shouldnt be more than creating the needed database tables in oracle ,change the tomcat config to use a datasource that connects to the oracle db and tweak the hibernate settings to use oracle.

Will it be that easy or am i overseeing something?

Would be great if someone could give me some pointers.

 

thx in advance

Michael

[ibrahimhamza]

First if you can buy Pro edition it is recommonded to have support to your self and to project too

second if you can't

1- download the latest bin file 

from http://sourceforge.net/project/showfiles.php?group_id=162962&package_id=195243

current is jasperserver-3.1-bin.zip  

2- download Tomcat 5.5

http://tomcat.apache.org/download-55.cgi

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

[ibrahimhamza]

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

 

[ibrahimhamza]

11- start tomcat

12- login in the url http://localhost:8080/jasperserver/

username  jasperadmin and password   password

 

 

 

[mgolisch]

Code:

Thx

[baggypants]

This is great. I've bookmarked this thread.

I've also jiggled about with the schema sql script for 3.5 It seems to work fine but no warrenty or recompense etc... I've split it into two scritps. One creates the tables and the other adds the default security settings. I did this because I have a situation where the tables create fine, but if I run the security script and then try to run a js-import it seems to crash on the user import with the code below.

Just creating the tables and running js-import withouth creating the default security works fine though.

 

 

Code:

User anonymousUser already exists, skipping.15:17:20,992 ERROR Cipherer:146 - java.lang.NumberFormatException: For input string: "pa"java.lang.NumberFormatException: For input string: "pa"

[yeti96]

great note! i did spent hours of searching for an installation description.

after 10 min.i got jasperserver up und running, exept the ability to log on.

whenever i try to logon i get an error message "invalid credentials supplied"

mhhmmm... and how to run "js-import"?

jasperserver.log:

 

 

Code:

Post Edited by yeti96 at 07/20/2009 15:31

 

I solved the problem:

When using the sql-statements to setup defaul security change the password settings for the created users

instead of 

INSERT INTO JIUser (id,username,tenantId,fullname,emailAddress,password,externallyDefined,enabled) VALUES

 (hibernate_sequence.nextval, 'jasperadmin',1,'Jasper Administrator',NULL,'password',0,1);

use

 

INSERT INTO JIUser (id,username,tenantId,fullname,emailAddress,password,externallyDefined,enabled) VALUES

 (hibernate_sequence.nextval, 'jasperadmin',1,'Jasper Administrator',NULL,'349AFAADD5C5A2BD477309618DCD58B9',0,1);

 

 

Password will be set to "jasperadmin"

 

Post Edited by yeti96 at 07/22/2009 06:55

[lorenzo.renzi]

 

I have install jasperserver 3.5 on mysql and work fine.

I modify this installation to work on ORACLE.

I modify this file:

1. create user jasper/jasper

2. installa the script  on user jasper

3. modify this file:

D:\Programmi\Apache Software Foundation\Tomcat 6.0\webapps\jasperserver\WEB-INF\hibernate.properties

# Properties file with JDBC-related settings.
# Applied by PropertyPlaceholderConfigurer from "applicationContext-*.xml".
# Targeted at system administrators, to avoid touching the context XML files.

# Property that determines the Hibernate dialect
metadata.hibernate.dialect=org.hibernate.dialect.OracleDialect
metadata.hibernate.dataSource.jndiName=jdbc/jasperserver
#
# default_schema setting will be uncommented if your database requires
# a schema to be specified (such as for DB2)
#
#metadata.hibernate.default_schema=${metadata.hibernate.default_schema}

D:\Programmi\Apache Software Foundation\Tomcat 6.0\webapps\jasperserver\META-INF\context.xml

<Context path="/jasperserver"
            debug="5" reloadable="true" crossContext="true">

        <!-- maxActive: Maximum number of dB connections in pool. Make sure you
             configure your mysqld max_connections large enough to handle
             all of your db connections. Set to 0 for no limit.
        -->
        <!-- maxIdle: Maximum number of idle dB connections to retain in pool.
             Set to -1 for no limit.  See also the DBCP documentation on this
             and the minEvictableIdleTimeMillis configuration parameter.
         -->
        <!-- maxWait: Maximum time to wait for a dB connection to become available
             in ms, in this example 10 seconds. An Exception is thrown if
             this timeout is exceeded.  Set to -1 to wait indefinitely.
        -->
    <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:@192.168.1.101:1521:orcl"/>
</Context>

 

D:\Programmi\Apache Software Foundation\Tomcat 6.0\webapps\jasperserver\META-INF\jasperserverDS-jdbc.xml

<?xml version='1.0' encoding='UTF-8'?>
<jdbc-data-source xmlns="http://www.bea.com/ns/weblogic/90" xmlns:sec="http://www.bea.com/ns/weblogic/90/security" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:wls="http://www.bea.com/ns/weblogic/90/security/wls" xsi:schemaLocation="http://www.bea.com/ns/weblogic/920 http://www.bea.com/ns/weblogic/920.xsd">
  <name>JasperServerDataBase</name>
  <jdbc-driver-params>
    <url>jdbc:oracle:thin:@192.168.1.101:1521:orcl</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>
  <jdbc-connection-pool-params>
    <test-table-name>SQL SELECT 1</test-table-name>
  </jdbc-connection-pool-params>
  <jdbc-data-source-params>
    <jndi-name>JasperServerDataBase</jndi-name>
    <global-transactions-protocol>OnePhaseCommit</global-transactions-protocol>
  </jdbc-data-source-params>
</jdbc-data-source>
 

 

When i try to connect i have this log:

 

12:30:49,524  WARN JDBCExceptionReporter,http-3434-1:77 - SQL Error: 1064, SQLState: 42000
12:30:49,524 ERROR JDBCExceptionReporter,http-3434-1:78 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') and roles0_.userId=1' at line 1
12:30:49,540  WARN LoggerListener,http-3434-1:55 - Authentication event AuthenticationFailureServiceExceptionEvent: jasperadmin; details: org.acegisecurity.ui.WebAuthenticationDetails@0: RemoteIpAddress: 127.0.0.1; SessionId: E747FF27AC24C5B5E6272286813E82D3; exception: JDBC exception on Hibernate data access; nested exception is org.hibernate.exception.SQLGrammarException: could not initialize a collection: [com.jaspersoft.jasperserver.api.metadata.user.domain.impl.hibernate.RepoUser.roles#1]; nested exception is org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access; nested exception is org.hibernate.exception.SQLGrammarException: could not initialize a collection: [com.jaspersoft.jasperserver.api.metadata.user.domain.impl.hibernate.RepoUser.roles#1]

 

 

Why?!

 

Thank's

Lorenzo Renzi

 

iTHAN srl

 

 

 

 

 

 

[lorenzo.renzi]

I follow this step for install Jasper on oracle.

 

I login without problem

 

I have a problem after login, with a IE error.

I attach the problem.

 

Any Idea?!

 

Lorenzo

[yeti96]

nope,

maybe u look for messages in the tomcat or the jasesperserver logs.

 

[lorenzo.renzi]

No tomcat error Log.

No log in jasperserver/log

 

Lorenzo

 

[baggypants]

Here are the updated scripts with the admin fix from yeti96

 

[thelarch]

The above steps were very helpful to me.   Also I found that I had to do these things:

1)  Jasper 3.5 came with a lot of junk in the context.xml and DS files regarding sample projects "foodmart" and "sugarcrm".   These had to be removed from WEB-INF and META-INF

2)  Also there was more JDBC connection string information in $CATALINA_HOME/conf/Catalina/localhost/jasperserver.xml

Until I did these things, I got lots of errors about missing mysql drivers, which I don't have and don't use.   After making these changes I got up and running just fine.

[yz37811]

I just tried to install jasper 3.1 with oracle, following the steps posted here. Things went fine until I created a simple report using database data. After that, the server throws an exception when it's restarted:

 

[JPivot] 07 Nov 2009 20:30:44,968 ERROR [session ] org.apache.catalina.session.S

tandardManager#doLoad: IOException while loading persisted sessions: java.io.Wri

teAbortedException: writing aborted; java.io.NotSerializableException: com.jaspe

rsoft.jasperserver.api.engine.jasperreports.domain.impl.ReportUnitResult

java.io.WriteAbortedException: writing aborted; java.io.NotSerializableException

: com.jaspersoft.jasperserver.api.engine.jasperreports.domain.impl.ReportUnitRes

ult

 

Did anybody ever see this or could anyone provide any hits on what could be wrong?

 

[yz37811]

Actually, I noticed that the above exception does not affect the report or anything else. I checked the ReportUnitResult class definition, it is not implementing Serializable. I don't know why the code tries to read/write the object. I also tried different cases and found that it does not happen if I don't have a session open when I shutdown the server. This also happens with server 3.5.1 with mysql database. So, it's not oracle related. I guess.

[hussainian]

I've followed the instructions for my JasperServer 3.5.

I'm getting the exception BAD CREDENTIALS

"11:56:02,944  WARN LoggerListener,http-9080-8:55 - Authentication event AuthenticationFailureBadCredentialsEvent: jasperadmin; details: org.acegisecurity.ui.WebAuthenticationDetails@0: RemoteIpAddress: 127.0.0.1; SessionId: B93C4C6B80CCDB8EE3FFE704B934ACBA; exception: Bad credentials"

I've attached all the changed files with this post.

I'm using Oracle 10g, Tomcat 6.0, JasperServer 3.5, Firefox on Windows XP.

The name of host is "proxy" and the service is "proxy".

The username and password for Oracle is "jasperreport".

I've checked the JIUSER table from Oracle, it has the the user "jasperadmin" (the schema was created with the later Oracle-3.5.zip with encrypted passwrod).

Anyother information required, i'll provide.

Regards.

AsadULLAH.

 

Post Edited by hussainian at 16/01/2010 12:38 PM

[tkavanagh]

I haven't read the whole thread carefully ... but you seem to be connecting to MySQL instead of Oracle. There is a Tomcat configuration issue that is quite tricky, that might be good to take a look at.

 

Normally, you edit the context.xml here: <tomcat>/webapps/jasperserver/META-INF/context.xml

 

However, tomcat has an operation where it takes this file and copies it to the config dir (in the case of a deploy of an archived war file ie jasperserve.war - as opposed to a non-archived directory jasperserver).

 

Anyway, check that there is not a jasperserver.xml in the following location: <tomcat>/conf/Catalina (I think that is the path).

 

You can make changes to the one in META-INF but they won't get used if the file in conf is present. Delete the file in conf.

 

If you are using Oracle, you need to be connecting to Oracle.

[emmerson]

this is my grain of rice for JasperServer 3.7.0 and Oracle10g, works fine, into rar you can see all files needed and scripts for database.

Post Edited by emmerson at 01/28/2010 17:19

[hussainian]

Thanks All!
JasperServer 3.7 with Oracle 10g is up.

[mail2vsdvg1]

Hi emmerson,

I'm Vijay, as for instruction you are provided in  attached document

i have replace files under tomcatInstalationDir, but when i executed (open file: File->Open)

scripts under Oracle10g DDL Scripts in Oracle 10g SQL Plus as SYSTEM

user.

I got error message: SP2-0423: Illegal GET command

And i have attache screen shot

 

[dimuthu89]

Hi,

 

I have ERP called Adempiere and need to deploy jasper reports through Jasperserver.My database is Oracle and so i need to configure jasperserver datasource to Oracle.So please help me step by step to do this.

[dimuthu89]

Hi,

 

I have ERP called Adempiere and need to deploy jasper reports through Jasperserver.My database is Oracle and so i need to configure jasperserver datasource to Oracle.So please help me step by step how to do this.

 

Thank You