grant.king Posted March 26, 2013 Share Posted March 26, 2013 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_SkillThe fields cannot be found by the query, i get the following error - Error executing SQL Statement for null check username and passwordIf 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? Link to comment Share on other sites More sharing options...
grant.king Posted April 4, 2013 Author Share Posted April 4, 2013 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 Link to comment Share on other sites More sharing options...
grant.king Posted April 4, 2013 Author Share Posted April 4, 2013 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? Link to comment Share on other sites More sharing options...
Ankur Gupta Posted April 17, 2013 Share Posted April 17, 2013 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 helpsThanks and Regards,Ankur Link to comment Share on other sites More sharing options...
grant.king Posted April 19, 2013 Author Share Posted April 19, 2013 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-parameter Link to comment Share on other sites More sharing options...
Ankur Gupta Posted April 19, 2013 Share Posted April 19, 2013 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now