WSAD: Creating a "View" in Websphere and DB2
So there I was trying to emulate the functionality of what was a view in the previous Notes version of my application. All the view had to do was list out the eReports from the system by date. Easy:
1. Create a mapping the the STRUTS configuration file for eReportsByDate.
2. Add a "Forward" to this mapping to the view display JSP called /eReports/eReportsByDate.jsp.
3. Create an action command object class to process this mapping called com.spuggy.sampler.eReports.action.viewBydateAction
4. Add this action to the STRUTS configuration.
5. The Action will create a Business Facade Object called eReportManager and call its menthod findAll().
6. The findAll method will create a Data Access Object FindAllEreportsDAO and call the execute function.
7. The execute function will then create a DBBeans object and execute a select statement.
8. The eReportManager will then loop through the result set using another Data Access Object called FindAllEreportsDAORow.
9. When looping around the result set the eReportManager will populate a vector of Data Transfer Objects called EreportsViewDTO.
10. If everything is rosey then eReportManager.findAll() will return the vector of EreportsViewDTOs back to the Action (see 5).
11. The Action will then shove the EreportsViewDTO back into the request object like so - request.setAttribute("eReportVectorDTO", eReportsVector).
12. Then the action object will use the forward mapping to send the user onto the correct JSP page to display the vector of the results.
13. In the JSP Page the STRUTS logic tag library will be used to iterate over the vector of DTO objects: <logic:iterate id="eReport" name="eReportVectorDTO">.
14 Finally open the URL eReportsByDate.do (all *.do URLs are mapped to the main ActionServelt of STRUTS) and voila the table of eReports appears.
Oh
Then you go and refresh the page and the second viewing won't work giving a 500 error. Looking at the server test environment console you see:
[IBM][CLI Driver][DB2/NT] SQL0405N The numeric literal "0" is not valid
because its value is out of range. SQLSTATE=42820
Then you go and search Google's Websphere Newsgroups for that string and after a bit of clicking around find:
To answer my own question, I have since found out that the cache is
broken in certain scenarios and that IBM are aware of it.
If your SQL contains the following...
FETCH FIRST n ROWS ONLY
where n = the number of rows you want returned, the cache attempts to
replace n with a ? parameter.
When the statement is executed a second time, it expects to be passed
a number as you would do with any other ? substitution. It doesn't get
one so substitutes it with 0, which then causes the statement to fail.
Good eh!!
So then you back into WSAD and alter the Prepared Statement Cache size to 0 on the Datasource
configuration in the test server configuration. Then it finally works.
After that you think, now I need to do the forward and back links and you realise that DB2 does not support MYSQL's Limit function, so you have to do some jiggery-pokery with a second sorted column and passing url strings.
Finally after powering down for the day you have a little think and wonder: "How did all this catch on?"
