Invalid Column Index Error on report

0

Hey, I have a sql statement that runs fine in a DB and doesn't show any errors in the Dataset and Query Dialog but when I hit the "Read Fields" button it throws the error 
"Invalid Column Index"

Any ideas?

Thanks.

sithen.jb's picture
Joined: Oct 5 2015 - 9:14am
Last seen: 3 years 11 months ago

6 Answers:

0

Seems to be a bug with iReport. Unfortunately, iReport development has already stopped. Recommend you switch over to Jaspersoft Studio.

hozawa's picture
52312
Joined: Apr 24 2010 - 4:31pm
Last seen: 5 months 5 days ago
0

My mistake, I guess I tagged this wrong. I am currently using JasperSoft Studio Verison 6.0.1

 

When I view the error details I get this message

at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:372)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:196)
at com.jaspersoft.studio.data.jdbc.JDBCFieldsProvider.getFields(JDBCFieldsProvider.java:67)
at com.jaspersoft.studio.data.jdbc.JDBCDataAdapterDescriptor.getFields(JDBCDataAdapterDescriptor.java:75)
at com.jaspersoft.studio.property.dataset.dialog.DataQueryAdapters.doGetFields(DataQueryAdapters.java:359)
at com.jaspersoft.studio.data.designer.AQueryDesignerContainer$1.run(AQueryDesignerContainer.java:50)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)
Caused by: java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5386)
at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:5374)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:282)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementParameter(JRJdbcQueryExecuter.java:564)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementParameter(JRJdbcQueryExecuter.java:399)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter$1.visit(JRJdbcQueryExecuter.java:332)
at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter$QueryParameter.accept(JRAbstractQueryExecuter.java:157)
at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter.visitQueryParameters(JRAbstractQueryExecuter.java:646)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:317)
sithen.jb's picture
Joined: Oct 5 2015 - 9:14am
Last seen: 3 years 11 months ago
0

This is usually caused by an error in your sql statement.

hozawa's picture
52312
Joined: Apr 24 2010 - 4:31pm
Last seen: 5 months 5 days ago
0

Yeah, but the problem is my SQL statement is fine. When I run it directly in the DataBase it grabs exactly want. There aren't any errors being show in the Dataset Query Dialog either. So, my SQL works in the DB but not in JasperSoft.

sithen.jb's picture
Joined: Oct 5 2015 - 9:14am
Last seen: 3 years 11 months ago
0

it can be caused by oracle jdbc driver. Select the "tibcosoftware.jdbc.oracle.OracleDriver" for data adapter and test it. If it doesn't work, I agree with hozawa. Problem is in your query or parameters that in use on dataset

zh3ntil's picture
531
Joined: Nov 19 2014 - 11:02pm
Last seen: 3 years 4 months ago
0

I am curently using the tibcosoft.jdbc.OrcaleDriver, I don't believe my parameters are incorrect, but I could be wrong. Heres my sql code

SELECT department.department_name, utl_user.last_name||', ' LASTNAME, utl_user.first_name FIRSTNAME, authorization.labour_skill_cd, authorization.skill_sdesc, license.LIC_CODE, license.LIC_SDESK, utl_role.role_cd, utl_role.role_nameFROM utl_role, utl_user_role, utl_user,   (SELECT org_hr.user_id, org_work_dept.desc_sdesc department_name    FROM org_hr, org_dept_hr, org_work_dept    WHERE org_hr.hr_db_id = org_dept_hr.hr_db_id AND          org_hr.hr_id = org_dept_hr.hr_id AND          org_dept_hr.dept_db_id = org_work_dept.dept_db_id AND          org_dept_hr.dept_id = org_work_dept.dept_id) department,      (SELECT org_hr.user_id, ref_labour_skill.labour_skill_cd, ref_labour_skill.desc_sdesc skill_sdesc       FROM org_hr, org_hr_qual, ref_labour_skill       WHERE org_hr.hr_db_id = org_hr_qual.hr_db_id AND             org_hr.hr_id = org_hr_qual.hr_id AND             org_hr_qual.labour_skill_cd = ref_labour_skill.labour_skill_cd AND             org_hr_qual.labour_skill_db_id = ref_labour_skill.labour_skill_db_id) authorization,   (SELECT DISTINCT org_hr.user_id, lic_defn.lic_cd LIC_CODE, lic_defn.lic_sdesc LIC_SDESK    FROM org_hr, org_hr_lic, lic_defn    WHERE         org_hr.hr_db_id = org_hr_lic.hr_db_id AND        org_hr.hr_id = org_hr_lic.hr_id AND        org_hr_lic.lic_db_id = lic_defn.lic_db_id AND        org_hr_lic.lic_id = lic_defn.lic_id) license             WHERE utl_role.role_id = utl_user_role.role_id ANDutl_user_role.user_id = utl_user.user_id ANDutl_user.user_id = department.user_id ANDutl_user.user_id = authorization.user_id (+) ANDutl_user.user_id = license.user_id (+) AND utl_role.role_cd IN (SELECT role_cd FROM utl_role WHERE role_name LIKE NVL($P{ROLE}, '%'))AND authorization.labour_skill_cd IN (SELECT labour_skill_cd FROM ref_labour_skill WHERE labour_skill_cd LIKE NVL($P{Skill}, '%'))ORDER BY department.department_name, LASTNAME, FIRSTNAME, authorization.labour_skill_cd, license.LIC_CODE, utl_role.role_cd

The section with the parameters is near the end,

AND utl_role.role_cd IN (SELECT role_cd FROM utl_role WHERE role_name LIKE NVL($P{Role}, '%'))

AND authorization.labour_skill_cd IN (SELECT labour_skill_cd FROM ref_labour_skill WHERE labour_skill_cd LIKE NVL($P{Skill}, '%'))

ORDER BY department.department_name, LASTNAME, FIRSTNAME, authorization.labour_skill_cd, license.LIC_CODE, utl_role.role_cd

sithen.jb's picture
Joined: Oct 5 2015 - 9:14am
Last seen: 3 years 11 months ago
Feedback