Re: is Stroed Procedures possible in OFBiz?

[Chris Snow]

Hi Su,  how are you using the returned data? Is it a table list, or just 
a single form?

If it is a list, do you want to display just the aggregated (summed) 
values, or do you want to display the detail (unsummed) records too?

su2 wrote:
Hi Chris,

Thank you for the response.

My query has multiple joins with different tables and also I would like to
use sum(column name) and (column 1 + column 2) as column 3.

Is it possible to use sum(column name) in java method with delegator?

Also, the stored procedure I want to use in jasper report.

Thank you for the help.
Su-


Chris Snow-3 wrote:
  
Hi Su,

I use SQL Views for complex queries:

    <entity entity-name="ClearanceReport"
            table-name="vClearanceReport"
            package-name="uk.co.dhales"
            title="Clearance Report Entity"
            no-auto-stamp="true"
            never-cache="true"           
            >
        <field name="strIsrCompany" col-name="strIsrCompany" 
type="long-varchar"/>
        <field name="strIsrBranch" col-name="strIsrBranch" 
type="long-varchar"/>
        <field name="strIsrCode" col-name="strIsrCode"
type="long-varchar"/>
        <field name="ingUplNumber" col-name="ingUplNumber"
type="numeric"/>
        ...
        <prim-key field="ingUplNumber"/>
    </entity>

The table name vClearanceReport is actually a SQL View. The attributes 
no-auto-stamp and never-cache where needed.

Also, here is a service that is using a SQL Stored procedure:

public class DHSequenceService {
   
    public static Map getNextSequenceValue(DispatchContext ctx, Map 
context) {
        Connection conn = null;
        CallableStatement statement = null;
      
        String sql = "{call getNextSequenceValue(?,?)}";
               
        Long seqValue;
        try{
                conn = ConnectionFactory.getConnection("dhmssql");
                if (conn == null) {
                        throw new Exception("No dhmssql connection 
configured");
                }
                statement = conn.prepareCall (sql);
                String seqTable = (String)context.get("seqTable");

                statement.registerOutParameter(1, Types.INTEGER);
                statement.setString(2,  seqTable);
                statement.execute();
                seqValue = new Long(statement.getLong(1));
                statement.close();
        } catch (Exception e){
                Debug.logError(e, "Error running SQL - ", 
DHSequenceService.class.getName());
                return ServiceUtil.returnError("Error running SQL" + e);
        }
        finally {
                if (statement != null)
                        try {
                                statement.close();
                        } catch (Exception e) {}
                if (conn != null)
                        try {
                                conn.close();
                        } catch (Exception e) {}
        }
        Map resultMap = ServiceUtil.returnSuccess();
        resultMap.put("seqValue", seqValue);
        return resultMap;
    }
}

Note that by using SQL Views/Stored Procedures you are limiting the 
portability of your application to other databases.

Cheers,

Chris

su2 wrote:
    
Hello Friends,

I need to write quite complex query which is little easier to write as
Stored Procedures. So I would like to know whether is it possible to
write
and access stored procedures in OFBiz with mySQL?

If its possible, can i have example or reference for how to do that ?

Thank you for the help in advance.

Su-