Newbie struggling with date parameter in ireport

0

Hi there, I have literally been using Jasper for a few days now and so far I like a lot.

I am testing out some of the features, but am struggling to get date parameters to work with an sql stored procedure.

The report query is Execute sp_MyProcedure $P{@In_Start}$P!{@In_End}$P{@In_Skill}

I have set up teh parameters and they are set to timestamp for @In_start & @In_End and string for @In_Skill

The fields cannot be found by the query, i get the following error - Error executing SQL Statement for null check username and password

If I modify the stored procedure to remove the start and end date parameters and adjust the jasper parameters then the query works, but I need it to include date parameters.

 

Does anyone have any suggestion what I might be doing wrong?

grant.king's picture
Joined: Jan 24 2013 - 8:56am
Last seen: 6 years 3 months ago
Can any one help with this question? I can get the query to work if I use a view rather than a stored procedure. I guess that means I have the parameters set up correctly and there must be an issue with my syntac in the query when using a stored procedure, but I cant work it out
grant.king - 6 years 3 months ago
So I got the query to work now, but the report does not run correctly. I get an error stating the the scalar variable must be declared. The parameter is set as sql.timestamp in jasper, is this correct?
grant.king - 6 years 3 months ago

1 Answer:

0

Hi grant.king,

See the problem which I see here is that the parameters you gave in the stored procedures, are not initialized thats why they tend to give NULL POINTER EXCEPTION so for that you could give the default value of the parameters as -1 for the numeric parameters or " " for the text parameter .

 

Next problem which I see could be problematic is the sequence of the parameters in the parameter list . suppose there are two parameters P1 and P2 they are defined in the sequence P1 and then P2 but in the stored procedure the parameter P2 is used before P1... then that will give an error in SQL.

So please check for above things and let me know if it helps

Thanks and Regards,

Ankur 

Ankur Gupta's picture
Joined: Jan 21 2013 - 10:36pm
Last seen: 1 year 1 month ago

Thanks Ankur

I have set the default values of the parameters to be -1 and "" as suggested and also checked that the parameters run in the same order as they do in the stored procedure.

I had already got the report to run in ireport even before your suggestions, however when I try to run the report through the repository navigator it does not return any results regardless of what dates I enter.

When I try to run the report on jasper Server, the calendar selector does not allow me to select dates and when I try to enter them manually, it says I am not entering a valid data type

http://community.jaspersoft.com/questions/808677/jasper-server-date-para...

grant.king - 6 years 3 months ago

Hi,

Have you checked the datatype for the parameter you have used, try with the :
java.sql.TimeStamp or java.util.Date

and then as you have parameters of date type give the default values as the current date : new java.util.Date()
so that the default value is not null, and in the Jasper Server while setting the input controls have you checked the option for Always show popup if not then please check it , then you will be able to set the date.

Hope that it works.

Regards,
Ankur

Ankur Gupta - 6 years 3 months ago
Feedback
randomness