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-
>>
>
>
>
--
View this message in context:
http://www.nabble.com/is-Stroed-Procedures-possible-in-OFBiz--tp25768161p25768992.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
