Jump to content
Changes to the Jaspersoft community edition download ×

SQL query problem


Recommended Posts

By: Roberto Rios - bobrivers

SQL query problem

2002-12-06 13:57

Hi,

 

I have defined the following SQL query inside a report :

 

SELECT productName, COUNT(productCode) as productTotal, (SELECT COUNT(productCode) FROM v_list) as granTotal FROM v_list GROUP BY productName ORDER BY productTotal DESC

 

Basically this SQL gives me the products names and their respective totals. The other select, just sum all the products and gives me a general total.

 

So, when buildind the XML, I define 3 fields : <field name="productName" class="java.lang.String"/>, <field name="productTotal" class="java.lang.Integer"/> and <field name="granTotal" class="java.lang.Integer"/>

 

After compile it, when I try to view it, I receive the following error :

 

dori.jasper.engine.JRException: Unable to get value for field 'granTotal' of class 'java.lang.Integer'

 

If I remove the field granTotal, it works fine.

 

My SQL is correct, because when I run it directly, it returns me what I want.

 

I am using jsp. The entire tomcat error is :

 

Apache Tomcat/4.0.6 - HTTP Status 500 - Internal Server Error

 

--------------------------------------------------------------------------------

 

type Exception report

 

message Internal Server Error

 

description The server encountered an internal error (Internal Server Error) that prevented it from fulfilling this request.

 

exception

 

javax.servlet.ServletException: Unable to get value for field 'granTotal' of class 'java.lang.Integer'

at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:471)

at org.apache.jsp.rel_0005fjasperreports_0005ffila$jsp._jspService(rel_0005fjasperreports_0005ffila$jsp.java:175)

at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:201)

at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:381)

at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:473)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:190)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)

at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)

at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2347)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)

at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)

at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:468)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)

at org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1027)

at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1125)

at java.lang.Thread.run(Thread.java:536)

 

 

root cause

 

java.sql.SQLException: [Microsoft][sqlServer 2000 Driver for JDBC]Invalid column name: granTotal

at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)

at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)

at com.microsoft.jdbc.base.BaseResultSet.getColumnOrdinal(Unknown Source)

at com.microsoft.jdbc.base.BaseResultSet.getString(Unknown Source)

at dori.jasper.engine.JRResultSetDataSource.getFieldValue(JRResultSetDataSource.java:199)

at dori.jasper.engine.fill.JRBaseFiller.next(JRBaseFiller.java:665)

at dori.jasper.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:158)

at dori.jasper.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:421)

at dori.jasper.engine.fill.JRFiller.fillReport(JRFiller.java:143)

at dori.jasper.engine.JasperFillManager.fillReport(JasperFillManager.java:348)

at dori.jasper.engine.JasperFillManager.fillReport(JasperFillManager.java:288)

at dori.jasper.engine.JasperRunManager.runReportToPdf(JasperRunManager.java:280)

at org.apache.jsp.rel_0005fjasperreports_0005ffila$jsp._jspService(rel_0005fjasperreports_0005ffila$jsp.java:151)

at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:201)

at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:381)

at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:473)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:190)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)

at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)

at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2347)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)

at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)

at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:468)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)

at org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1027)

at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1125)

at java.lang.Thread.run(Thread.java:536)

 

NESTED BY :

dori.jasper.engine.JRException: Unable to get value for field 'granTotal' of class 'java.lang.Integer'

at dori.jasper.engine.JRResultSetDataSource.getFieldValue(JRResultSetDataSource.java:264)

at dori.jasper.engine.fill.JRBaseFiller.next(JRBaseFiller.java:665)

at dori.jasper.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:158)

at dori.jasper.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:421)

at dori.jasper.engine.fill.JRFiller.fillReport(JRFiller.java:143)

at dori.jasper.engine.JasperFillManager.fillReport(JasperFillManager.java:348)

at dori.jasper.engine.JasperFillManager.fillReport(JasperFillManager.java:288)

at dori.jasper.engine.JasperRunManager.runReportToPdf(JasperRunManager.java:280)

at org.apache.jsp.rel_0005fjasperreports_0005ffila$jsp._jspService(rel_0005fjasperreports_0005ffila$jsp.java:151)

at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:201)

at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:381)

at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:473)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:190)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)

at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)

at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2347)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)

at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)

at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:468)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)

at org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1027)

at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1125)

at java.lang.Thread.run(Thread.java:536)

 

 

 

--------------------------------------------------------------------------------

 

 

Any idea ?

 

TIA,

 

Roberto

 

 

 

By: Teodor Danciu - teodord

RE: SQL query problem

2002-12-07 00:42

 

Hi,

 

As you can see from the exception stack trace,

there is a column name mismatch.

Verify that the returned result set really contains

a column named "granTotal".

 

I hope this helps.

Teodor

 

 

 

By: Roberto Rios - bobrivers

RE: SQL query problem

2002-12-09 08:58

Hi,

 

Thanks, but it isn't a column name mismatch.

 

I have build another report, and the problem occured again.

 

But now I have a clue : I am using iReport to build the XML file, than I use JasperReports routines (provided in demo samples : webapp) to compile and generate the PDF file.

 

When I use iReport, everything works fine. I am able to build, compile and generate the PDF file. No error occurs.

 

But, when I try to use the XML file (generated by iReport, the same one that worked with it) with the webapp, I am able to compile it, but not to generate the PDF.

 

When I call the application that reads and fills the .jasper file, the error message that I posted before occurs.

 

So, I am wondering if maybe we could have an incompatibility between JasperReports and iReport...

 

Now I will try to build a XML file starting from scratch... Do you want to see the XML file generated by iReport?

 

Thanks,

 

Roberto

 

 

By: Teodor Danciu - teodord

RE: SQL query problem

2002-12-09 09:53

 

Hi,

 

Are you sure it is not a JDBC driver problem?

I suspect you don't use the same driver in both

scenarios.

 

I hope this helps.

Teodor

 

 

 

By: Roberto Rios - bobrivers

RE: SQL query problem

2002-12-09 10:18

Hi,

 

Thanks for the replys.

 

I did a test, without iReport, and it doesn't work. Take a look in my message posted at 15:27.

 

Even when I build the XML from the scratch, the error occurs...

 

I have built a very small report. When I use column alias, it doesn't work. If I remove the column alias and use the real column name, it works fine.

 

Roberto

 

 

By: Roberto Rios - bobrivers

RE: SQL query problem

2002-12-09 09:27

Hi,

 

As I said, I built another XML this time using only jEdit...

 

And the errors occured again.

 

I think that JasperReports doesn't accept ALIAS... or my config (I am using SQL Server 2000, JDK 1.4.1 and W2K Pro)

 

I have posted the XML that I used to do the tests... It is very minimalistic...

 

Using the code below, generates the error. If I remove the alias clause, and use the real column name, the same XML works fine...

 

<?xml version="1.0"?>

<!DOCTYPE jasperReport PUBLIC "-//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">

 

<jasperReport

name="rel_jasperreports_fila"

columnCount="1"

printOrder="Vertical"

pageWidth="595"

pageHeight="842"

columnWidth="270"

columnSpacing="15"

leftMargin="20"

rightMargin="20"

topMargin="30"

bottomMargin="30"

isTitleNewPage="false"

isSummaryNewPage="false">

<reportFont name="Arial_Normal" isDefault="true" fontName="Arial" size="8" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>

<reportFont name="Arial_Bold" isDefault="false" fontName="Arial" size="8" isBold="true" pdfFontName="Helvetica-Bold" pdfEncoding="Cp1252" isPdfEmbedded="false"/>

<reportFont name="Arial_Italic" isDefault="false" fontName="Arial" size="8" isItalic="true" pdfFontName="Helvetica-Oblique" pdfEncoding="Cp1252" isPdfEmbedded="false"/>

<reportFont name="Comic_Normal" isDefault="false" fontName="Comic Sans MS" pdfFontName="COMIC.TTF" pdfEncoding="Identity-H" isPdfEmbedded="true"/>

<reportFont name="Comic_Bold" isDefault="false" fontName="Comic Sans MS" isBold="true" pdfFontName="COMICBD.TTF" pdfEncoding="Identity-H" isPdfEmbedded="true"/>

<queryString><![CDATA[sELECT nom_fila AS name FROM v_chamado]]></queryString>

<field name="name" class="java.lang.String"/>

<title>

<band height="0">

</band>

</title>

<pageHeader>

<band height="0">

</band>

</pageHeader>

<columnHeader>

<band height="0">

</band>

</columnHeader>

<detail>

<band height="20">

<textField isBlankWhenNull="true">

<reportElement x="155" y="0" width="25" height="11"/>

<textFieldExpression class="java.lang.String">

$F{name}

</textFieldExpression>

</textField>

</band>

</detail>

<columnFooter>

<band height="0">

</band>

</columnFooter>

<pageFooter>

<band height="0">

</band>

</pageFooter>

<summary>

<band height="0">

</band>

</summary>

</jasperReport>

 

 

 

 

By: Bernd Proissl - berndproissl

RE: SQL query problem

2002-12-09 10:39

just a guess: have you tried

SELECT nom_fila name FROM v_chamado

?

 

Bernd

 

 

 

 

By: Roberto Rios - bobrivers

RE: SQL query problem

2002-12-09 10:41

Hi,

 

Thanks for the replys.

 

I did a test, without iReport, and it doesn't work. Take a look in my message posted at 15:27.

 

Even when I build the XML from the scratch, the error occurs...

 

I have built a very small report. When I use column alias, it doesn't work. If I remove the column alias and use the real column name, it works fine.

 

Roberto

 

 

By: Roberto Rios - bobrivers

RE: SQL query problem

2002-12-09 10:46

Thanks Bernd, but yes.

 

I have tryed all the forms (that I know) like :

 

1) SELECT name = nom_fila FROM v_chamado;

2) SELECT nom_fila AS 'name' FROM v_chamado;

3) SELECT nom_fila 'name' FROM' v_chamado;

4) SELECT nom_fila name FROM v_chamado;

 

And I always receive the same error : the column alias is not recognized...

 

Roberto

 

 

By: Roberto Rios - bobrivers

RE: SQL query problem

2002-12-09 10:55

Thanks Bernd, but yes.

 

I have tryed all the forms (that I know) like :

 

1) SELECT name = nom_fila FROM v_chamado;

2) SELECT nom_fila AS 'name' FROM v_chamado;

3) SELECT nom_fila 'name' FROM' v_chamado;

4) SELECT nom_fila name FROM v_chamado;

 

And I always receive the same error : the column alias is not recognized...

 

Roberto

 

 

By: Bernd Proissl - berndproissl

RE: SQL query problem

2002-12-09 12:20

is 'name' a reserved keyword?

 

Bernd

 

 

 

By: Roberto Rios - bobrivers

RE: SQL query problem

2002-12-10 02:17

Good guess, but no.

 

I have tried another word, like 'nombre' and I got the same error.

 

Are you able to use alias ?

 

Roberto

 

 

By: Roberto Rios - bobrivers

RE: SQL query problem

2002-12-10 04:40

Hi,

 

Teodor mentioned that I could have a JDBC problem.

 

So I decided to change, by accessing the database through ODBC (sun.jdbc.odbc.JdbcOdbcDriver).

 

And .... worked. I got no errors.

 

So, I decided to do a new download from microsoft site, and ... worked.

 

I really don't know why, because the driver never gave me headache before....

 

So, thanks. Sorry about.

 

Roberto

 

 

By: Teodor Danciu - teodord

RE: SQL query problem

2002-12-10 04:48

 

Hi,

 

I'm glad you made it.

 

Good luck!

Teodor

Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...