search

bluesoft-rnd / aperte-reports

Automatically exported from code.google.com/p/aperte-reports
0 stars 4 forks source link

AR run on HSQL but can not run on MySQL #38

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
What steps will reproduce the problem?
1. I downloaded bundle tomcat with AR (6.0) and run test ok with default 
context.xml file

2. I want to use MySQL for my liferay, so I modified the following:
- create portal-ext.properties with content:

jdbc.default.jndi.name=jdbc/liferay

- modify context.xml file with content:

     <!-- datasource for sample reports  -->
    <Resource name="jdbc/liferay"
        auth="Container" 
        driverClassName="com.mysql.jdbc.Driver" 
        maxActive="8" 
        maxIdle="4"  
        password="lportal_cims" 
        type="javax.sql.DataSource" 
        url="jdbc:mysql://localhost/lportal_cims" 
        username="lportal_cims"
        />

    <!--
    <Resource name="jdbc/liferay"
        auth="Container" 
        driverClassName="org.hsqldb.jdbcDriver" 
        maxActive="8" 
        maxIdle="4"  
        password="" 
        type="javax.sql.DataSource" 
        url="jdbc:hsqldb:${liferay.home}/data/hsql/lportal" 
        username="sa"
        />
    -->

3. When start liferay, liferay db generated on MySQL db.

What is the expected output? What do you see instead?

I tried invoke report file liferay-users.jrxml but it show error:

Caused by: net.sf.jasperreports.engine.JRException: Unknown column name : 
FIRSTNAME
    at net.sf.jasperreports.engine.JRResultSetDataSource.getColumnIndex(JRResultSetDataSource.java:390)
    at net.sf.jasperreports.engine.JRResultSetDataSource.getFieldValue(JRResultSetDataSource.java:120)
    at net.sf.jasperreports.engine.fill.JRFillDataset.setOldValues(JRFillDataset.java:922)
    at net.sf.jasperreports.engine.fill.JRFillDataset.next(JRFillDataset.java:887)
    at net.sf.jasperreports.engine.fill.JRFillDataset.next(JRFillDataset.java:864)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.next(JRBaseFiller.java:1435)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:126)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:836)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:746)
    at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:58)
    at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:417)
    at org.apertereports.engine.ReportMaster.buildJasperPrint(ReportMaster.java:528)
    at org.apertereports.engine.ReportMaster.generateReport(ReportMaster.java:412)

I dont known what wrong in my configuration, MySQL db generated but why AR 
unknown column ...?

Thanks
ThaiSon

Original issue reported on code.google.com by sonmapqn...@gmail.com on 27 Nov 2012 at 9:08

Attachments:

GoogleCodeExporter commented 9 years ago 
Please check your context.xml file once again. Attached configuration contains 
two resources with the same name:

    Resource name="jdbc/liferay"

The first resource shoud have the name as follows:

    Resource name="jdbc/aperte-reports"

Regards,
Tomek

Original comment by tsera...@bluesoft.net.pl on 27 Nov 2012 at 11:18

GoogleCodeExporter commented 9 years ago 
hi Tomek

in context.xml file which i wrote, i want to:

enable db MySQL:

    <Resource name="jdbc/liferay"
        auth="Container" 
        driverClassName="com.mysql.jdbc.Driver" 
        maxActive="8" 
        maxIdle="4"  
        password="lportal_cims" 
        type="javax.sql.DataSource" 
        url="jdbc:mysql://localhost/lportal_cims" 
        username="lportal_cims"
        />

disable HSQL:

    <!--
    <Resource name="jdbc/liferay"
        auth="Container" 
        driverClassName="org.hsqldb.jdbcDriver" 
        maxActive="8" 
        maxIdle="4"  
        password="" 
        type="javax.sql.DataSource" 
        url="jdbc:hsqldb:${liferay.home}/data/hsql/lportal" 
        username="sa"
        />
    -->

So, no the same Resource name

Thanks
ThaiSon

Original comment by sonmapqn...@gmail.com on 27 Nov 2012 at 11:55

GoogleCodeExporter commented 9 years ago 
Oh, I'm sorry... I haven't noticed that the first statement was a comment. I 
will try to check it soon.

Tomek

Original comment by tsera...@bluesoft.net.pl on 27 Nov 2012 at 12:00

GoogleCodeExporter commented 9 years ago

Original comment by tsera...@bluesoft.net.pl on 27 Nov 2012 at 2:46

GoogleCodeExporter commented 9 years ago

Original comment by tsera...@bluesoft.net.pl on 27 Nov 2012 at 2:46

GoogleCodeExporter commented 9 years ago 
hi Tomek

Thanks for your support. I find out the bug. The bug is located in query of 
liferay-users.jrxml.

        <queryString>
            <![CDATA[select screenname, firstname fn, lastname ln
from USER_ order by lastname]]>
        </queryString>

It run on HSQL but not run on MySQL. When we change type of db, db syntax 
sometimes must be changed. However, I run liferay-users.jrxml on IReport 4.8 
that datasource connect directly to MySQL, it run (?)

By the way, thanks a lot
ThaiSon.

Original comment by sonmapqn...@gmail.com on 27 Nov 2012 at 2:50

GoogleCodeExporter commented 9 years ago 
Hi ThaiSon,

You're welcome.

I don't know if you know the solution to this problem... I found out that there 
are problems using aliases in sql query with some jdbc drivers 
(http://community.jaspersoft.com/questions/513341/unknown-column-name). You can 
just remove aliases from query:

    <queryString>
        <![CDATA[select screenname, firstname, lastname
from USER_ order by lastname]]>
    </queryString>

and then it works properly.

Regards,
Tomek

Original comment by tsera...@bluesoft.net.pl on 27 Nov 2012 at 3:13

GoogleCodeExporter commented 9 years ago

Original comment by tsera...@bluesoft.net.pl on 27 Nov 2012 at 3:22