Error executing SQL statement

[marcosep25]

  Hello,

I have a report in iReport 4.5.0 in which I include more tables in a PostgreSQL database without parameters. When I run it on iReport works fine, but when run from eclipse get an error. Try to solve it in postgres create a view that includes all the tables I need, and create a report calling iReport that view. He follows me out the same error in eclipse, but in iReport comes out perfectly. Below I put the error, the consultation report and class in eclipse called the report:
Error: net.sf.jasperreports.engine.jrexception: Error executing SQL statement for: movimiento

Query: SELECT
     imovimiento. "movemppaic" AS imovimiento_movemppaic,
     imovimiento. "movempcod" AS imovimiento_movempcod,
     imovimiento. "movdoccod" AS imovimiento_movdoccod,
     imovimiento. "movdocnro" AS imovimiento_movdocnro,
     imovimiento. "movlin" AS imovimiento_movlin,
     imovimiento. "movvalcre" AS imovimiento_movvalcre,
     imovimiento. "movmdebas" AS imovimiento_movmdebas,
     imovimiento. "usunom" AS imovimiento_usunom
FROM
     imovimiento

Class in eclipse:

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.HashMap;

import java.util.Map;

import javax.swing.*;

import java.io.File;

import java.io.OutputStream;

import java.io.FileOutputStream;

 

import net.sf.jasperreports.engine.JRException;

import net.sf.jasperreports.engine.JasperCompileManager;

import net.sf.jasperreports.engine.JasperFillManager;

import net.sf.jasperreports.engine.JasperPrint;

import net.sf.jasperreports.engine.JasperReport;

import net.sf.jasperreports.view.JasperViewer;

import net.sf.jasperreports.engine.JasperExportManager;

import net.sf.jasperreports.engine.export.JRXlsExporter;  

import net.sf.jasperreports.engine.export.JRXlsExporterParameter;

import net.sf.jasperreports.engine.JasperPrintManager;

 

public class ireport {

Connection conn = null;

 

public static void main(String[] args) {

int ocurrencias = 0;

String cadena = "", tipo = "", par_report = "";

String bd = "", login = "", password = "", url = "", driver = "", ruta = "", destino = "";

bd = "metrocali";

login = "postgres";

password = "postgres";

url = "jdbc:postgresql://localhost:5432/metrocali";

driver = "org.postgresql.Driver";

ruta = "D:\\iReport\\prueba1\\prueba2\\prueba3\\prueba4\\prueba5\\prueba6\\prueba7\\movimiento.jrxml";

tipo = "V";

destino = "D:\\iReport\\prueba1\\prueba2\\prueba3\\prueba4\\prueba5\\prueba6\\prueba7\\movimiento.xls";

par_report = "DOCUMENTOS¬CE¬DI¬";

new ireport(bd, login, password, url, driver, ruta, tipo, destino, par_report);

}

 

public ireport(String bd, String login, String password, String url, String driver, String ruta, String tipo, String destino, String par_report)

{

try{

//Obtenemos el driver

Class.forName(driver);

 

//Obtenemos la conexión

conn = DriverManager.getConnection(url,login,password);

if (conn!=null){

//JOptionPane.showMessageDialog(null, "Conexión a base de datos "+bd+". listo", "Mensaje iReport", JOptionPane.INFORMATION_MESSAGE);

}

}catch(SQLException e){

JOptionPane.showMessageDialog(null, e, "Mensaje iReport", JOptionPane.INFORMATION_MESSAGE);

}catch(ClassNotFoundException e){

JOptionPane.showMessageDialog(null, e, "Mensaje iReport", JOptionPane.INFORMATION_MESSAGE);

}

     

try{

//direccion del archivo JASPER

JasperReport reporte = JasperCompileManager.compileReport(ruta);

 

//Se crea un objeto HashMap

Map <String,Object> parametros = new HashMap<String,Object>();             

parametros.clear();

//Se pasan los parámetros al reporte

//En el reporte se deben crear los parametros con el prefijo par y seguido

//de un consecutivo. Ej: par1, par2, par3. Se le envian los parametros en el mismo orden

String cad_report = "", par = "";

int ocurrencias = 0;

char[] caracteres = new char[par_report.length()];

for (int i = 0; i < par_report.length(); i++) {

caracteres = (char)par_report.charAt(i);

if (caracteres=='¬')

{

ocurrencias = ocurrencias + 1;

par = "par" + ocurrencias; 

parametros.put(par,cad_report);

cad_report = "";

}

else

{

cad_report = cad_report + caracteres;

} //Fin if (caracteres=='¬')

} //Fin for (int i = 0; i < parametro_que_llega.length(); i++) {

 

JasperPrint jprint = JasperFillManager.fillReport(reporte, parametros, conn);

//Se convierte la variable tipo_arch en Character para poder manejar el switch,

//porque este no funciona con string

Character tipo_arch = null;

for (int i = 0; i < tipo.length(); i++) {

tipo_arch = (char)tipo.charAt(i);

}

switch(tipo_arch)

{

case 'V': //Vista previa

JasperViewer.viewReport(jprint, false);

break;

case 'P': //PDF

JasperExportManager.exportReportToPdfFile(jprint, destino);

break;

case 'H': //HTML

JasperExportManager.exportReportToHtmlFile(jprint, destino);

break;

case 'I': //Impresora

JasperPrintManager.printReport(jprint,true);

break;

case 'X': //Excel

       OutputStream outputfile= new FileOutputStream(new File(destino));

 

JRXlsExporter exporterXLS = new JRXlsExporter(); 

exporterXLS.setParameter(JRXlsExporterParameter.JASPER_PRINT, jprint); 

exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, outputfile); 

exporterXLS.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.FALSE); 

exporterXLS.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE); 

exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE); 

exporterXLS.exportReport();

break;

} //Fin de switch(tipo_arch)

//Terminamos la conexion a la base de datos

conn = null;

 

}

catch (JRException E){

JOptionPane.showMessageDialog(null, E, "Mensaje iReport", JOptionPane.INFORMATION_MESSAGE);

}

catch (Exception E){

JOptionPane.showMessageDialog(null, E, "Mensaje iReport", JOptionPane.INFORMATION_MESSAGE);

}

}

}

 

Thank you for your valuable help, for me is very important.

[augarte]

Hi,

I really don't know what could be happening there, but what is strange for me is that the names of your database fields are between double quotes and it seems there is a space betwen the table name and the fields. Anyway, the problem could be that you need to scape double quotes. I know it works from iReport and your query is defined there, but basically the jrxml file is an xml so maybe you need to do something like this:

imovimientos. "movemppaic" AS imovimiento_movemppacic,...

Check this out: http://www.devx.com/tips/Tip/14068

Hope this helps.

Regards,

Aitor

[marcosep25]

Thanks, I'll try your suggestion

[edramu31]

Hello marcosep

 

I hava a similar issue, have you fixed it?

 

Greetings!!

 

net.sf.jasperreports.engine.JRException: Error executing SQL statement for: null.

at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:344)

at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1129)

at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:696)

at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:437)

at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:548)

at net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:396)

at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:90)

at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:456)

at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:863)

at Reporteador_AG4X4.Aplicacion_Reporteo.btnGenerarReporteAprietesInternoActionPerformed(Aplicacion_Reporteo.java:2302)