Using Parameters in queries for MongoDB Connector for iReport?

[cllb21]

Hi, I've been battling with this the last 2 days. I want to use iReport parameters in a query I´m making to the MongoDB database I´m working with.

So, if I make a simple query like this:

{'collectionName':'personas','findQuery':{'_id':{'$oid':'4d9dde28664638480f000000'}}}

It works without a hitch. It reads the fields and I can preview the data normally. But when I want to use an iReport parameter, a string with the ID, so I can provide de ID externally, like this:

{'collectionName':'personas','findQuery':{'_id':{'$oid':'$P{paramName}'}}}

I get this stack trace:

Exception in thread "main" java.lang.IllegalArgumentException: invalid ObjectId [null]
        at org.bson.types.ObjectId.<init>(ObjectId.java:128)
        at org.bson.types.ObjectId.<init>(ObjectId.java:122)
        at com.mongodb.util.JSONCallback.objectDone(JSONCallback.java:38)
        at com.mongodb.util.JSONParser.parseObject(JSON.java:364)
        at com.mongodb.util.JSONParser.parse(JSON.java:316)
        at com.mongodb.util.JSONParser.parseObject(JSON.java:352)
        at com.mongodb.util.JSONParser.parse(JSON.java:316)
        at com.mongodb.util.JSONParser.parseObject(JSON.java:352)
        at com.mongodb.util.JSONParser.parse(JSON.java:316)
        at com.mongodb.util.JSONParser.parse(JSON.java:271)
        at com.mongodb.util.JSON.parse(JSON.java:230)
        at com.mongodb.util.JSON.parse(JSON.java:215)
        at com.jaspersoft.mongodb.MongoDbQueryExecuter.processQuery(MongoDbQueryExecuter.java:79)
        at com.jaspersoft.mongodb.MongoDbQueryExecuter.<init>(MongoDbQueryExecuter.java:53)
        at com.jaspersoft.mongodb.MongoDbQueryExecuterFactory.createQueryExecuter(MongoDbQueryExecuterFactory.java:25)
        at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:683)
        at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:606)
        ....

Now, I have test this in the following forms:

{'collectionName':'personas','findQuery':{'_id':{'$oid':$P{paramName}}}}

"{'collectionName':'personas','findQuery':{'_id':{'$oid':'$P{paramName}'}}}"

"{'collectionName':'personas','findQuery':{'_id':{'$oid':'"+$P{paramName}+"'}}}"

And it´s the same with any of them. I´ve tried making the parameter an Object instead of a string, with the ID as a org.bson.types.ObjectId instance, and using the following query:

{'collectionName':'personas','findQuery':{'_id':$P{clienteID}}}

And I don´t get any exceptions, just an EMPTY report.

I'm using the following code to run my reports. There are several parameters, that I'm commenting/uncommenting for each kind of test.

Also I tried putting only a string parameter as the query, and then passing the query string from the java app to the report, but I get the NullPointerExceptions again:

Exception in thread "main" java.lang.NullPointerException
        at com.jaspersoft.mongodb.MongoDbQueryExecuter.processQuery(MongoDbQueryExecuter.java:81)
        at com.jaspersoft.mongodb.MongoDbQueryExecuter.<init>(MongoDbQueryExecuter.java:53)
        at com.jaspersoft.mongodb.MongoDbQueryExecuterFactory.createQueryExecuter(MongoDbQueryExecuterFactory.java:25)
        at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:683)
        at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:606)
...

Also, it was no use when I tried with a JSON object, using this:

Object polizaJSONQuery = JSON.parse("{'collectionName':'polizas','findQuery':{'_id':{'$oid':'4da623aa6646381c15000003'}}}");

and sending that as the parameter to replace all the query, I get the same exact Exception.

So, how do I made queries with parameters? I just want to give the report a parameter for it to query the MongoDB at runtime. I´m using:

iReport 4.01
MongoDB connector for iReport 0.6
MongoDB driver for Java 2.5
MongoDB 1.6.5

Thanks in advance,

Carlos Luis

Code:

import com.jaspersoft.mongodb.MongoDbConnection;import com.mongodb.util.JSON;import java.io.FileOutputStream;import java.io.IOException;import java.io.PrintStream;import java.util.HashMap;import net.sf.jasperreports.engine.JRException;import net.sf.jasperreports.engine.JasperRunManager;import net.sf.jasperreports.engine.util.JRProperties;import org.bson.types.ObjectId;/** * * @author Carlos Luis */public class Main {    public static void main(String[] args) throws IOException, JRException    {        try{            long start = System.currentTimeMillis();            byte[] tmpReportFile = null;            String jasperName = "formdef_cliente.jasper";            String reportTempFilename = "temp.pdf";            JRProperties.setProperty ("net.sf.jasperreports.query.executer.factory.MongoDbQuery", "com.jaspersoft.mongodb.MongoDbQueryExecuterFactory");                        MongoDbConnection conn = new MongoDbConnection("my URI");            HashMap params = new HashMap();            ObjectId clienteObjID = new ObjectId("4da623aa6646381c15000002");            Object polizaJSONQuery = JSON.parse("{'collectionName':'polizas','findQuery':{'_id':{'$oid':'4da623aa6646381c15000003'}}}");            params.put("clienteID", clienteObjID);            params.put("test", "Hey apple");            params.put("query_poliza", polizaJSONQuery);            tmpReportFile = JasperRunManager.runReportToPdf(jasperName, params, conn);                                   FileOutputStream fout = new FileOutputStream(reportTempFilename);            PrintStream fp = new PrintStream(fout);            fp.write(tmpReportFile,0, tmpReportFile.length);            fp.close();            fout.close();                        System.err.println("Printing time : " + (System.currentTimeMillis() - start));        }        catch(JRException jre){            jre.printStackTrace();        }        catch(IOException ioe){            ioe.printStackTrace();        }    }}

[mdahlman]

Carlos,

You're really close. Just one character! Sorry that this isn't better documented yet. You need the syntax P! (normally read "P bang"). Here's an example of a query that is working well for me.

{
'collectionName':'accounts',
'findFields':{'name':1,'phone_office':1,'billing_address_city':1,'billing_address_street':1,'billing_address_country':1},
'sort':{'billing_address_country':-1,'billing_address_city':1},
'findQueryRegEx':{'name':'/^$P!{NameStartsWith}/'}
}

The Parameter NameStartsWith is a String. I give it a value like "A" or "M" or "M " and I get back the desired results.

In your case you should be fine like this:

{'collectionName':'personas','findQuery':{'_id':{'$oid':'$P!{paramName}'}}}

Please post to let us know if that solves it.

Regards,
Matt

P.S. Carlos emailed me directly using the Jasperforge.org email feature. This is great. Please use this for big data questions. Often I see new posts very quickly... but sometimes I don't. 

[cllb21]

Ohhh thank you, ver much indeed!! Yes, that was it. Just a freaking character. Now all runs smoothly. Finally I can avoid converting PHP arrays to XML files for the report to read the data from. That would have caused me lots of problems when several users decide to run my web app at the same time.

Sorry if I´m a little late in confirming that your answer worked.

 

Cheers,

 

Carlos Luis

[kalatunga]

Now, latest version of MongoDB doesn´t have the type MongoDbConnection. How can i pass this parameter to RunReportToPdf?