Parameters in query string

By: Dan Pierce - danrpierce
Parameters in query string
2002-11-19 13:59
Hello,

I'm trying to use parameters in my query string and it is not converting it to the value I place in the hashmap.

<parameter name="empid" class="java.lang.String"/>
<queryString><![CDATA[SELECT * FROM employee where empid = '$P{empid}']]></queryString>

File reportFile = new File(application.getRealPath("/reports/fcpmReport.jasper"));
Map parameters = new HashMap();
parameters.put( "BaseDir", reportFile.getParentFile() );
parameters.put( "empid", "110773" );
JasperReport jasperReport = (JasperReport)JRLoader.loadObject( reportFile.getPath() );
ResultSet rs = DatabaseUtils.executeSelect( jasperReport.getQuery().getText() );
ReportDatasource datasource = new ReportDatasource( rs );
byte[] bytes = JasperRunManager.runReportToPdf( reportFile.getPath(), parameters, datasource );

What am I doing wrong?

I'd appreciate a direct response at: stumped@danpierce.net

Dan





By: Teodor Danciu - teodord
RE: Parameters in query string
2002-11-20 13:05

Hi,

Do not quote the parameter value:

<queryString><![CDATA[SELECT * FROM employee where empid = $P{empid}]]></queryString>

I hope this helps.
Teodor





By: Dan Pierce - danrpierce
RE: Parameters in query string
2002-11-20 13:11

But what if the empid parameter I'm passing is a string?





By: Dan Pierce - danrpierce
RE: Parameters in query string
2002-11-20 13:15
I get this error without single quotes:

javax.servlet.ServletException: Line 1: Incorrect syntax near 'P'.





By: Teodor Danciu - teodord
RE: Parameters in query string
2002-11-20 13:41

Are you sure the $P{} syntax is intact?
Maybe you have removed the $ character also.

Teodor





By: Dan Pierce - danrpierce
RE: Parameters in query string
2002-11-20 13:59

I've tried it with and without single quotes. I've tried different select statements and stored procedures. Nothing works. It will not resolve the $P{parameter} no matter what I do. It accepts other parameters like basedir and title but nothing works in the querystring. Am I calling the right method to get the sql string?

JasperReport jasperReport = (JasperReport)JRLoader.loadObject( ... );
String sql = jasperReport.getQuery().getText();

I'm using version 0.4.3. Help. :)

Dan





By: Dan Pierce - danrpierce
RE: Parameters in query string
2002-11-20 15:11

Ok, I was calling the wrong method to get the sql string.

I was using:

ResultSet rs = DatabaseUtils.executeSelect( jasperReport.getQuery().getText() );

And should have been using:

ResultSet rs = JRQueryExecuter.executeQuery( jasperReport.getQuery(), parameters, null, DatabaseUtils.getConn() );

Except now I'm getting a ClassCastException. :)

What are the two maps passed to executeQuery?

java.util.Map parameters, java.util.Map values

I pass in my parameters map but what is the values map? Is it required?

Dan





By: Teodor Danciu - teodord
RE: Parameters in query string
2002-12-07 02:55

Hi,

Why is it that you need the SQL query string
extracted from the report design?

It seems that you have encoutered various
problems, but I'm not sure what exactly are you
trying to do.

Thank you,
Teodor





By: Chuck Deal - cdeal
RE: Parameters in query string
2002-11-21 05:19
The first map, parameters, is used to hold Parameter OBJECTS. The second map is used to hold parameter values. Both maps use the parameter name as the key.

From the code that you posted in the first message, it looks like the map that YOU call parameters is actually the values map.

There is a method called getParametersMap() on the JasperDesign object. This should be able to return the parameters map for you.





By: Dan Pierce - danrpierce
RE: Parameters in query string
2002-11-21 08:10

Ok, here my code. What would be causing me to get a ClassCastException? Everything I'm dealing with is a String.

ServletContext context = this.getServletConfig().getServletContext();
File reportFile = new File( context.getRealPath( "/reports/fcpmReport.jasper" ) );
Map parameters = new HashMap();
parameters.put( "BaseDir", reportFile.getParentFile() );
parameters.put( "ReportTitle", "FCPM Employee Report" );
parameters.put( "empid", "110773" );
byte[] bytes = null;
try {
try {
JasperReport jasperReport = (JasperReport)JRLoader.loadObject( reportFile.getPath() );
ResultSet rs = JRQueryExecuter.executeQuery( jasperReport.getQuery(), parameters, parameters, DatabaseUtils.getConn() );
System.out.println( "SQL: " + jasperReport.getQuery().getText() );
ReportDatasource datasource = new ReportDatasource( rs );
bytes = JasperRunManager.runReportToPdf( reportFile.getPath(), parameters, datasource );
}
catch( SQLException e ) {
System.out.println( "Exception: " + e );
throw new JRException( e.getMessage() );
}
}
catch( JRException e ) {
// display error
return;
}

... write pdf to ServletOutputStream ...

Here's a section of my XML report file:

<parameter name="empid" class="java.lang.String"/>
<queryString><![CDATA[select * from employee where empid = '$P{empid}']]></queryString>
<field name="empguid" class="java.lang.String"/>
<field name="firstname" class="java.lang.String"/>
<field name="lastname" class="java.lang.String"/>





By: Chuck Deal - cdeal
RE: Parameters in query string
2002-11-21 08:40
This line is where the Exception is occuring in your code.

ResultSet rs = JRQueryExecuter.executeQuery( jasperReport.getQuery(), parameters, parameters, DatabaseUtils.getConn() );

Mentally it might help if you rename YOUR parameters ap object to parameterValues. The problem is that the second argument to this method is supposed to be a Map of JRParameter objects. You are sending a Map of Strings.

Based on your code, it looks like you may be able to do something like this:

JasperReport jasperReport = (JasperReport)JRLoader.loadObject( reportFile.getPath() );
Map parameters = new HashMap();
JRParameter[] parms = jasperReport.getParameters();
for (int i = 0; i < parms.length; i++) {
parameters.put(parms.getName(), parms);
}

ResultSet rs = JRQueryExecuter.executeQuery( jasperReport.getQuery(), parameters, parameterValues, DatabaseUtils.getConn() );

I don't know if that will compile, but that is the general idea.