I am currently working on a project to create a front-end to a database that is currently being migrated from MS Access to SQL Server. Due to a number of reasons, we are doing a proof of concept with a WYSIWYG editor called Wavemaker. The product works fine and I am impressed with its ease of use but I am running into an issue that I have been trying to resolve for some time. A key piece of the interface that we want to develop involves a crosstab view of data that is executed from a SQL Server stored procedure utilizing a dynamic pivot query. Meaning that the column headings change dynamically with the data over time (Months will be the columns) but the number of columns will remain the same.
Kudos to the original author:
http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx The idea is to use Wavemaker's livegrid widget so that users can click on a cell to obtain referenced information. Meaning that this data will not be edited. The challenge that I am having is that Wavemaker utilzes Hibernate and HQL to connect and query our SQL Server database. My question is how do I use Hibernate to create a
Java Service that calls a SQL Server stored procedure? No parameters are being passed. In a SQL query statement, all I have to do is "exec sp_Requirements_Crosstab". I cannot figure this out in the Hibernate.
Additionally how do I use Hibernate to call on a stored procedure where the columns will dynamically change with time? I want to stress this because according to my research Hibernate seem to require that all colume objects are defined. In my case I cant. Although the crosstab object will be limited to X months, the fact is that the month columns will change over time. Is there an equivalent to a Select all statement in HQL? I have invested time to try understand persistence and hibernate but I am still hitting a wall with what I originally thought would be a trivial task. Before anyone responds with a generic link to hibernate.org I must say that I have looked at this site and the documentation concerning the call for a stored procedure seemed light. Also I think getting input from the community would be truly valuable