Using scriptlet method in query returns SQL error

[maggix]

Hello, first of all I'd like to excuse myself if this topic has already been discussed, however I could not find any helpful information in this forum. For my other problems, I used the search function or refered to other tutorials found googling, that's why this is my first topic.

 

I am currently working with iReport 3.6.0 from the day of release, and I'm stuck with a problem that involves using a custom scriptlet which works on a parameter in a query.The report I am working on is to be deployed on a JasperServer setup, which I currently use on a local server for testing. The iReport and JasperServer setups are OK (Libraries, JDBC, and so on).

The scriptlet setup is OK, I created a Java class which extends the JRDefaultScriptlet class. I tested it on some text fields, and the result of the expression is correct. Also, there are no errors or warnings on the scriptlet itself.

In the SQL SELECT query (which, by the way, is executed on a Oracle 10g database connected with JDBC) I have this code: 

 

AND ROUND(JULIAN_DATE) Between $P!{DateConverter_SCRIPTLET}.dateToJde($P{fromDateForm})
And  $P!{DateConverter_SCRIPTLET}.dateToJde($P{toDateForm})

 

where DateConverter_SCRIPTLET is the name of my custom scriptlet (which calls the DateUtilitiesPackage.DateUtilitiesScriptlet class I included as a JAR file) and the names fromDateForm and toDateForm are 2 parameters which are marked as "PROMPT" in iReport, and have a corresponding Input Field in JasperServer.  The query is a SELECT query on a database which uses Julian date (CYYDDD) instead of the usual DATE field , and the prompt field is shown to the user as a normal DATE INPUT (dd/MM/yyyy) which is to be converted in the other format. This script is provided by my Java custom scriptlet, which takes a Java.Util.Date value as input and returns a Integer value, which is supposed to be substituted in the above query.

Please note that I already tried using the "!" in $P{fromDateForm} ($P!{fromDateForm}) but nothing changed.

 

 What I want to obtain is that the value I fill in the prompt fields (the Input Fields as called in JasperServer) is to be processed when the query is executed, but I get an error java.SQL.SQLException; the complete resulting error is attached as CODE in this topic.

I don't know if my script does actually perform what is intended, but I dont know if it's possible in iReport to show the executed query somewhere (I didn't find anything about that...) so I can't check what's actually executed. I just need a way to get that value processed somewhere between the insertion of the value in the field by the user action and the query execution.

 

I also attach as "Code" to this topic the method dateToJde which converts the Date format to an Integer corresponding to the date used in JDEdwards, so that you can check for any mistake.

 

I hope that my topic explains correctly my problem, please feel free to ask for more details if needed.

 

Thank you in advance for you attention

Giovanni

Code:

Post Edited by maggix at 10/05/2009 12:56

Post Edited by maggix at 10/05/2009 12:59

[maggix]

Hello, I solved the problem without the help from anyone in this forum and I am posting my solution hoping that would be helpful to someone.

 

Using parameters in queries is OK as long no calculations are to be applied to such parameters. For example, a "static" parameter works but not a calculated one.

Instead of parameter in query, I used a Variable. I defined a Variable without any kind of calculation, and wrote the Expression as follows:

 

$P{MYSCRIPTLET_SCRIPTLET}.methodToCall{ $P{ input_control_parameter }  }

 

By doing this I got the variable evaluated inside the query, by writing

 

SELECT ..... FROM .... WHERE .... field = $V{ myVariable }

(without the "!" )

 

Best regards

Giovanni

[mdahlman]

Giovanni,

 

Well done! I think the important part was moving the reference to the scriptlet outside of the SQL. Rather than using a variable, you could just use a second parameter that does not have an input control. The second param would take the default value of $P{MYSCRIPTLET_SCRIPTLET}.methodToCall{ $P{ input_control_parameter }  }

 

Then the SQL would include SELECT ... WHERE ... SomeColumn = $P{SecondParameter}

 

For a case like this the $P syntax is good. But $P! is equally valid.

 

Regards,
Matt

[maggix]

Hello Matt, thank you for your reply.

I actually tried using a second parameter with the default expression containing the scriplet I wanted to call to execute my calculation, however the "parameter inside parameter" way didn't work for me.

Using a Variable instead of a Parameter was my solution, that's why I felt I needed to share such information, because it took me a while to figure out and I thought someone could need it.

 

Regards

Giovanni

[virenptl40]

Can you tell me the steps how i can add that variable in jasper server.??

because by $P{MYSCRIPTLET_SCRIPTLET}.methodToCall{ $P{ input_control_parameter } }

it is giving me error as MYSCRIPTLET_SCRIPTLET parameter not found. please help.

[honeybakliwal]

 

maggix it returns null value

[honeybakliwal]

$V{Variable_1} it says an undefined symbol in my query