unknown column name

[manshack_one]

I created a very simple report in iReport.  Here's the query:

SELECT
     call_detail.`Unit` AS call_detail_Unit,
     call_detail.`DialedNumber` AS call_detail_DialedNumber,
     call_detail.`Rate` AS call_detail_Rate,
     call_detail.`Minutes` AS call_detail_Minutes,
     call_detail.`Last` AS call_detail_Last,
     call_detail.`First` AS call_detail_First,
     call_detail.`Revenue` AS call_detail_Revenue,
     call_detail.`Commission` AS call_detail_Commission
FROM
     `call_detail` call_detail
WHERE
     call_detail.`call2` LIKE "Complete"

It compiles and runs fine in iReport.  However, when I publish it to jasperserver and try to run it I get this error:

com.jaspersoft.jasperserver.api.JSExceptionWrapper: net.sf.jasperreports.engine.JRException: Unknown column name : call_detail_Unit

Both iReport and jasperserver are pointing to the same datasource.  I have a feeling it's something to do with the alias it's using.  However I've tried changing the query to leave out the alias's and the dot notation on the columns and get the same error.  I've also tried creating the report and instead of pushing it to the server, going into jasperserver and pulling it in using the new report wizard.  Same result either way.  How do I get them to talk to the database the same way?

[manshack_one]

Any ideas?

[lucianc]

Are you using the same JDBC driver in iReport and JasperServer?

Regards,

Lucian

[manshack_one]

As far as I know, yes. I'll double check it but I'm pretty sure it's the same mysql jdbc on the server and the pc.

 

Be Right Back with the answer.

 

[manshack_one]

Both using

com.mysql.jdbc.Driver

[lucianc]

manshack_one
Wrote:

Both using com.mysql.jdbc.Driver

Yes, but is it the same version of the driver jar? iReport could use the 5.1 com.mysql.jdbc.Driver class, and JS could use the 3.1 version of the same class, and these different classes might work differently.

Also, what iReport and JasperServer versions do you use?

Regards,

Lucian

[manshack_one]

iReport version 3.5.2

JasperServer 3.5.0

 

In iReport I set the compatibility to 3.5.0 to force a downgrade on the report.

I guess I don't see the relevance of the driver version though. If I'm using JDBC
in Ubuntu to get to a mysql database and you're in windows using ODBC we're
still seeing the same database. We just have different doors to go through. That
being said I setup a data source in jasperserver that points to the database and in
iReport during publishing of the report I chose to use that datasource from the repository.
I've tried it the other way also and using the datasource from iReport. Either way it
gets the same error.

How do I find out which jdbc driver version I have? iReport is on windows, jasperserver is on linux.

[lucianc]

manshack_one
Wrote:

How do I find out which jdbc driver version I have?  iReport is on windows, jasperserver is on linux.

Use $P{REPORT_CONNECTION}.getMetaData().getDriverVersion() as expression for a report text field, and run the report in iReport and JasperServer.

And while you're at it, add another text field that displays net.sf.jasperreports.engine.JasperReport.class.getPackage().getImplementationVersion()

Regards,

Lucian

Post Edited by lucianc at 09/10/2009 14:02

[manshack_one]

on Jasperserver:

mysql-connector-java-5.1.5
3.5.0

in iReport:

null
3.5.2

 

[lucianc]

manshack_one
Wrote:

in iReport: null

Use the MySQL data source to run the report.

[manshack_one]

mysql-connector-java-5.0.7

 

sorry bout that. thought i'd told it to use the datasource.

AFAIK both programs are using the bundled jdbc drivers.

[manshack_one]

mysql-connector-java-5.0.7

sorry bout that. AFAIK both programs are using the bundled drivers.

[lucianc]

I see, I wasn't aware that iR and JS ship with different MySQL drivers.

For testing purposes, could you copy the iReport mysql-connector-java jar (located under ide8/modules/ext) to JasperServer (under $TOMCAT/common/lib, and delete the existing mysql jar there)? Then run again the report that shows the MySQL driver version to confirm that JS is using 5.0.7 as well, and run the report that fails in JS to see whether the driver makes any difference.

[manshack_one]

I renamed the one in tomcat to .bak and copied the 5.0.7 out there. Restarted jasperserver and it still says 5.1.5 when I run that report. where else would it be looking for the jar?

[lucianc]

I don't know, but you can find out where the class is loaded from by using the following expression in a text field (set isStretchWithOverflow="true" so that it prints the entire text).

Regards,

Lucian

Code:

getClass().getResource("/com/mysql/jdbc/Driver.class").toString()

[manshack_one]

jar:file:/opt/jasperserver-3.5.0/apache-tomcat/webapps/jasperserver/WEB-INF/lib/mysql-connector-java-5.1.5.jar!/com/mysql/jdbc/Driver.class

So it was hiding in the web-inf/lib directory.
Verified it was using the 5.0.7 jar file but the report still gets the same error.  I think it's something to do with the query structure and how it aliases everything.  I think I'll copy that report and rewrite the query without the aliases.  I'll let  you know what I find out.

[manshack_one]

Finally got it working!

I ended up downloading 3.6.0 iReport to fix a bug where having compatibility mode on would delete my group footer band everytime you closed the report.  I re-ran the report and verified everything is still on 5.0.7 jdbc driver.  Then I replaced the query with this:

SELECT *
FROM
      call_detail
WHERE
 CallDate = $P{ReportDate}
 and UPPER(call2) LIKE "COMPLETE"
 and UPPER(call1) NOT LIKE "THIRD PARTY IVR"
 and UPPER(Last) NOT LIKE "SECURUS"
 and UPPER(First) NOT LIKE "SECURUS"
 and DialedNumber NOT LIKE "8162437111"

Basically it replaced all the alias parts of the query.  Then I made sure the report name was identical between iReport and jasperserver's resource and label names (not sure if that made a difference or not).  Then I published the report with the new query to a new folder on jasperserver and set up the input control again.  Now it runs.  I really think it had something to do with that query.  It would seem that others would have similar issues though if they're using the query designer in iReport.  Doesn't matter to me right now.  It works.  :)  Thanks for all your time.

[mdahlman]

manshack_one,

 

It sounds like you encountered exactly the same issue that I ran into last week (though in my case it was with JasperServer Data Domains). For anyone else that runs into this issue, here are a few details. It depends closely on the version of the MySQL JDBC driver that you use.

 

Query:
select account_id as a1 from account

5.0.5 behavior:
rsmd.getColumnName() = a1
rsmd.getColumnLabel() = a1

 

5.1.5 behavior (we ship with JS Pro 3.5):
rsmd.getColumnName() = account_id
rsmd.getColumnLabel() = a1

 

5.1.6 behavior:
rsmd.getColumnName() = a1
rsmd.getColumnLabel() = a1

 

5.1.8 behavior (current release as Sept 2009):
rsmd.getColumnName() = account_id
rsmd.getColumnLabel() = a1

 

5.1.8 behavior with jdbc:mysql://localhost:3306/foodmart?useOldAliasMetadataBehavior=true
rsmd.getColumnName() = a1
rsmd.getColumnLabel() = a1
 
Yikes!! (I'm not making this up. I tested them all.) These bugs explain some of what's going on: http://bugs.mysql.com/35150, http://bugs.mysql.com/21379.

 

I suspect that iReport and/or JR use getColumnName when perhaps they could be using getColumnLabel.

 

In my tests with DB2, Oracle, MS SQL Server, PostgreSQL, and MySQL (I forget if I tried others) I never found a case of getColumnName and getColumnLabel returning different information... with the glaring exception of the MySQL fun listed above.

 

That's more information that most folks will want... but maybe it will help someone with an issue like yours.

 

-Matt

[manshack_one]

So which JDBC version do you recommend using?  I downloaded iReport 3.6.0 and have it running with the 5.0.7 driver as well as JasperServer 3.5.0 with the 5.0.7 driver.  Should I stick with what I have or would it be less buggy to use a higher version JDBC driver on both sides?

[elvermg]

 Same problem here, please help!!!