2001 JI Open Discussion Posted August 17, 2006 Share Posted August 17, 2006 By: Roberto Rios - bobrivers SQL query problem 2002-12-06 13:57Hi, 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:58Hi, 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:18Hi, 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:27Hi, 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:39just 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:41Hi, 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:46Thanks 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:55Thanks 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:20is 'name' a reserved keyword? Bernd By: Roberto Rios - bobrivers RE: SQL query problem 2002-12-10 02:17Good 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:40Hi, 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now