Deploying JasperINtelligence with PostgreSQL

[gustavotanoni]

Hi: Can Jasper Intelligence be deploy with PostgreSQL instead of MySQL? Changing the configuration file META-INF/context.xml is sufficient? Thank you!.

[swood]

We have not looked at this in a while and did not complete the task, but here is what you have to do.

 

 

You will need to run a build to get the metadata repository database scripts for Postgres.

 

In your Maven2 settings.xml before you run the build, you will need parameters like:

 

Code:

<test.hibernate.cfg>C:/Docume~1/swood/postgres.hibernate.cfg.xml</test.hibernate.cfg>
       	 <test.hibernate.jdbc.properties>C:/Docume~1/swood/postgres.jdbc.properties</test.hibernate.jdbc.properties>
       	 <!-- When you run the metadata repository DDL generation, do you want the
       	      database to be dropped and recreated?					-->
       	 <metadata.database.generate>true</metadata.database.generate>
	 <repository.database.driver.groupId>postgresql</repository.database.driver.groupId>
	 <repository.database.driver.artifactId>postgresql</repository.database.driver.artifactId>
	 <repository.database.driver.version>8.1-404.jdbc3</repository.database.driver.version>

 

 

Postgres BLOBs need to be read in a transaction, so you will need to try to change the transaction attributes on beans in /WEB-INF/applicationContext.xml to remove readOnly.

 

 

If you have any problems, let us know and we will help.

 

 

Sherman

JasperSoft

[gustavotanoni]

Thank you! I had already started trying to build for a different target DB. My requirements have changed a bit, my target DB now is MS SQL. So far I have followed your suggestions and the only blocking issue that I have is when trying to run the tables_sqlServer.sql script. The driver (I have tried both, sqljdbc.jar and jtds-1.1.jar) complains about the 'GO' statement. With the osql.exe utility the scripts runs ok... but maven has an issue... I will continue to investigate.

[mederic.ribreux]

Hello,

 

I published a patch to get postgreSQL as a repository DB for Jasperserver.

I know that my patch gets some bugs: the database definition is not good: some Large Objects (oids) have to be redefined as bytea.

 

Now I am facing another problem:

After adding a JRXML (subreport) on the server, if I try to get into the folder in which the JRXML has been put, I get the following error message:

Error Message:

org.springframework.webflow.ActionExecutionException: Exception thrown executing [AnnotatedAction@22b7f8 targetAction = com.jaspersoft.jasperserver.war.action.RepoAdminAction@1bbad6f, attributes = map[[empty]]] in state 'initAction' of flow 'repoAdminFlow'; nested exception is org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for SQL [select this_.id as id0_1_, this_.version as version0_1_, this_.name as name0_1_, this_.parent_folder as parent4_0_1_, this_.childrenFolder as children5_0_1_, this_.label as label0_1_, this_.description as descript7_0_1_, this_.creation_date as creation8_0_1_, this_1_.type as type1_1_, this_1_.maxLength as maxLength1_1_, this_1_.decimals as decimals1_1_, this_1_.regularExpr as regularE5_1_1_, this_1_.minValue as minValue1_1_, this_1_.maxValue as maxValue1_1_, this_1_.strictMin as strictMin1_1_, this_1_.strictMax as strictMax1_1_, this_2_.data as data2_1_, this_2_.file_type as file3_2_1_, this_3_.data as data3_1_, this_3_.file_type as file3_3_1_, this_3_.reference as reference3_1_, this_4_.type as type4_1_, this_4_.mandatory as mandatory4_1_, this_4_.readOnly as readOnly4_1_, this_4_.data_type as data5_4_1_, this_4_.list_of_values as list6_4_1_, this_4_.list_query as list7_4_1_, this_4_.query_value_column as query8_4_1_, this_4_.defaultValue as defaultV9_4_1_, this_6_.sql_query as sql2_8_1_, this_7_.reportDataSource as reportDa2_9_1_, this_7_.query as query9_1_, this_7_.mainReport as mainReport9_1_, this_8_.olapClientConnection as olapClie2_12_1_, this_8_.mdx_query as mdx3_12_1_, this_10_.reportDataSource as reportDa2_14_1_, this_10_.mondrianSchema as mondrian3_14_1_, this_11_.catalog as catalog15_1_, this_11_.username as username15_1_, this_11_.password as password15_1_, this_11_.datasource as datasource15_1_, this_11_.uri as uri15_1_, this_12_.catalog as catalog16_1_, this_12_.mondrianConnection as mondrian3_16_1_, this_14_.driver as driver18_1_, this_14_.password as password18_1_, this_14_.connectionUrl as connecti4_18_1_, this_14_.username as username18_1_, this_15_.jndiName as jndiName19_1_, this_16_.beanName as beanName20_1_, this_16_.beanMethod as beanMethod20_1_, case when this_10_.id is not null then 10 when this_11_.id is not null then 11 when this_14_.id is not null then 14 when this_15_.id is not null then 15 when this_16_.id is not null then 16 when this_1_.id is not null then 1 when this_2_.id is not null then 2 when this_3_.id is not null then 3 when this_4_.id is not null then 4 when this_5_.id is not null then 5 when this_6_.id is not null then 6 when this_7_.id is not null then 7 when this_8_.id is not null then 8 when this_9_.id is not null then 9 when this_12_.id is not null then 12 when this_13_.id is not null then 13 when this_.id is not null then 0 end as clazz_1_, parent1_.id as id21_0_, parent1_.version as version21_0_, parent1_.uri as uri21_0_, parent1_.hidden as hidden21_0_, parent1_.name as name21_0_, parent1_.label as label21_0_, parent1_.description as descript7_21_0_, parent1_.parent_folder as parent8_21_0_, parent1_.creation_date as creation9_21_0_ from JIResource this_ left outer join JIDataType this_1_ on this_.id=this_1_.id left outer join JIContentResource this_2_ on this_.id=this_2_.id left outer join JIFileResource this_3_ on this_.id=this_3_.id left outer join JIInputControl this_4_ on this_.id=this_4_.id left outer join JIListOfValues this_5_ on this_.id=this_5_.id left outer join JIQuery this_6_ on this_.id=this_6_.id left outer join JIReportUnit this_7_ on this_.id=this_7_.id left outer join JIOlapUnit this_8_ on this_.id=this_8_.id left outer join JIOlapClientConnection this_9_ on this_.id=this_9_.id left outer join JIMondrianConnection this_10_ on this_.id=this_10_.id left outer join JIXMLAConnection this_11_ on this_.id=this_11_.id left outer join JIMondrianXMLADefinition this_12_ on this_.id=this_12_.id left outer join JIReportDataSource this_13_ on this_.id=this_13_.id left outer join JIJdbcDatasource this_14_ on this_.id=this_14_.id left outer join JIJNDIJdbcDatasource this_15_ on this_.id=this_15_.id left outer join JIBeanDatasource this_16_ on this_.id=this_16_.id inner join JIResourceFolder parent1_ on this_.parent_folder=parent1_.id where parent1_.hidden=? and (parent1_.uri=?) order by parent1_.uri asc, this_.name asc]; SQL state [25P01]; error code [0]; Les Large Objects ne devraient pas être utilisés en mode auto-commit.; nested exception is org.postgresql.util.PSQLException: Les Large Objects ne devraient pas être utilisés en mode auto-commit.

 

I suppose that the problem comes from a definition of my database schema (perhaps another bytea should be used instead of Large Object) or from hibernate configuration (auto-commit deactivated).

 

I tried to put hibernate in a non auto-commit mode (by adding <prop key="hibernate.connection.autocommit">false</prop> in the props in /WEB-INF/applicationContext.xml) with no success at all !