Post your feature requests hereBack to Forum List
>>
Back to Topic List
>>
VIEW ALL POSTS
Stored Procedure Support
Posted Friday, 27 March 2009 By Josh Martin
Modified: Friday, 27 March 2009
In addition to tables and views, it's critical to support stored procedures to retrieve data. This allows more complex reports to be hidden by stored procedures.
Please note that some databases (MySql) allow SELECT FROM PROCEDURE syntax, while others (Oracle) rely on cursors (ref cursors). It's critical that both are supported - i.e. I should be able to point to an Oracle procedure, pass in parameters, and get the ref cursor results back as a table. Here's a short example of how results are returned from an Oracle stored procedure:
PROCEDURE sample (
p_1 IN VARCHAR2,
p_2 IN VARCHAR2,
refcur OUT sys_refcursor
)
IS
BEGIN
OPEN cur_worker FOR
SELECT * FROM Whatever
WHERE Col1 = p_1
AND Col2 = p_2;
END sample;
(This could also be inside an Oracle package).
Thanks,
Josh
Please note that some databases (MySql) allow SELECT FROM PROCEDURE syntax, while others (Oracle) rely on cursors (ref cursors). It's critical that both are supported - i.e. I should be able to point to an Oracle procedure, pass in parameters, and get the ref cursor results back as a table. Here's a short example of how results are returned from an Oracle stored procedure:
PROCEDURE sample (
p_1 IN VARCHAR2,
p_2 IN VARCHAR2,
refcur OUT sys_refcursor
)
IS
BEGIN
OPEN cur_worker FOR
SELECT * FROM Whatever
WHERE Col1 = p_1
AND Col2 = p_2;
END sample;
(This could also be inside an Oracle package).
Thanks,
Josh
Stored Procedure Support
Posted Monday, 30 March 2009 By Mihai
Modified: Tuesday, 31 March 2009
We will evaluate this and see what it implies under the hood.
Re: Stored Procedure Support
Posted Tuesday, 31 March 2009 By Josh Martin
Mihai -
That would be great. Our current product uses JReport, and we are considering moving to BIRT. However, we really only need table-based reporting, and NextReports looks perfect. Several of our "queries" have some business logic (if/else/etc.) in them, and procedures are the way we implement this.
The other way we could do this was to allow a Java class to be the "data generator". I.e. rather than a report using a SQL query to get its data, if we could provide a Java class (that implemented a NextReports interface), we could let that class connect to the database, return the results, perform the logic, etc. E.g.
package nextreports;
public interface DataProvider {
/** Provides the connection to the provider */
public setConnection(Connection c);
/** Passes the report so the parameters can be used */
public List getResults(Report r);
}
Just a first thought as an example...
Josh
That would be great. Our current product uses JReport, and we are considering moving to BIRT. However, we really only need table-based reporting, and NextReports looks perfect. Several of our "queries" have some business logic (if/else/etc.) in them, and procedures are the way we implement this.
The other way we could do this was to allow a Java class to be the "data generator". I.e. rather than a report using a SQL query to get its data, if we could provide a Java class (that implemented a NextReports interface), we could let that class connect to the database, return the results, perform the logic, etc. E.g.
package nextreports;
public interface DataProvider {
/** Provides the connection to the provider */
public setConnection(Connection c);
/** Passes the report so the parameters can be used */
public List getResults(Report r);
}
Just a first thought as an example...
Josh
Stored Procedure Support
Posted Friday, 03 April 2009 By Mihai
We will add stored procedure support in version 2.2.
In the query editor you will be allowed to write :
for Oracle :
call my_schema.my_procedure(${P1}, ... , ${Pn}, ?)
for MySql:
call my_schema.my_procedure(${P1}, ... , ${Pn})
where P1, ... , Pn are parameters defined in the query. Every such parameter will have also a previewValue which must be used inside the procedure to exit as fast as possible. That is necessary because when a new report is created from a query , we must look for the report columns and we do not want the procedure to make some business for such cases.
Only the out parameter is mandatory for Oracle. The IN parameters number may be zero.
For MySql there is no out parameter.
In the query editor you will be allowed to write :
for Oracle :
call my_schema.my_procedure(${P1}, ... , ${Pn}, ?)
for MySql:
call my_schema.my_procedure(${P1}, ... , ${Pn})
where P1, ... , Pn are parameters defined in the query. Every such parameter will have also a previewValue which must be used inside the procedure to exit as fast as possible. That is necessary because when a new report is created from a query , we must look for the report columns and we do not want the procedure to make some business for such cases.
Only the out parameter is mandatory for Oracle. The IN parameters number may be zero.
For MySql there is no out parameter.