Deploying JasperINtelligence with PostgreSQL

Hi: Can Jasper Intelligence be deploy with PostgreSQL instead of MySQL? Changing the configuration file META-INF/context.xml is sufficient? Thank you!.
gustavotanoni's picture
Joined: Aug 22 2006 - 12:35am
Last seen: 15 years 9 months ago

3 Answers:

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:

<test.hibernate.cfg>C:/Docume~1/swood/postgres.hibernate.cfg.xml</test.hibernate.cfg><br />
       	 <>C:/Docume~1/swood/</><br />
       	 <!-- When you run the metadata repository DDL generation, do you want the<br />
       	      database to be dropped and recreated?					--><br />
       	 <metadata.database.generate>true</metadata.database.generate><br />
	 <repository.database.driver.groupId>postgresql</repository.database.driver.groupId><br />
	 <repository.database.driver.artifactId>postgresql</repository.database.driver.artifactId><br />
	 <repository.database.driver.version>8.1-404.jdbc3</repository.database.driver.version><br />
</td></tr></tbody></table><br />
<br />
<br />
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.<br />
<br />
<br />
If you have any problems, let us know and we will help.<br />
<br />
<br />
Sherman<br />
swood's picture
Joined: Jun 21 2006 - 12:48pm
Last seen: 9 years 7 months ago
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.
gustavotanoni's picture
Joined: Aug 22 2006 - 12:35am
Last seen: 15 years 9 months ago

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 as id0_1_, this_.version as version0_1_, 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_, as data2_1_, this_2_.file_type as file3_2_1_, 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 is not null then 10 when is not null then 11 when is not null then 14 when is not null then 15 when is not null then 16 when is not null then 1 when is not null then 2 when is not null then 3 when is not null then 4 when is not null then 5 when is not null then 6 when is not null then 7 when is not null then 8 when is not null then 9 when is not null then 12 when is not null then 13 when is not null then 0 end as clazz_1_, as id21_0_, parent1_.version as version21_0_, parent1_.uri as uri21_0_, parent1_.hidden as hidden21_0_, 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 left outer join JIContentResource this_2_ on left outer join JIFileResource this_3_ on left outer join JIInputControl this_4_ on left outer join JIListOfValues this_5_ on left outer join JIQuery this_6_ on left outer join JIReportUnit this_7_ on left outer join JIOlapUnit this_8_ on left outer join JIOlapClientConnection this_9_ on left outer join JIMondrianConnection this_10_ on left outer join JIXMLAConnection this_11_ on left outer join JIMondrianXMLADefinition this_12_ on left outer join JIReportDataSource this_13_ on left outer join JIJdbcDatasource this_14_ on left outer join JIJNDIJdbcDatasource this_15_ on left outer join JIBeanDatasource this_16_ on inner join JIResourceFolder parent1_ on where parent1_.hidden=? and (parent1_.uri=?) order by parent1_.uri asc, 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 !
mederic.ribreux's picture
Joined: Jul 30 2006 - 6:41pm
Last seen: 15 years 10 months ago