You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by su2 <sh...@pexsupply.com> on 2009/10/06 14:55:25 UTC

is Stroed Procedures possible in OFBiz?

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--tp25768161p25768161.html
Sent from the OFBiz - User mailing list archive at Nabble.com.


Re: is Stroed Procedures possible in OFBiz?

Posted by Chris Snow <sn...@snowconsulting.co.uk>.
I wasn't aware that list forms can display lists with detail and summary 
level data...

su2 wrote:
> Unfortunately, my  whole display page is in Form Widget with "Form Type =
> list", So I think the best way for this case is to have Query with sum().
> Correct me if I am wrong. Because at this point, I am not sure whether I can
> convert my entire Form widget into Freemarker.
>
> Thank you for your help.
> Su- 
>
>
>
>  You can correct me if I am wrong.
>  
>
> Chris Snow-3 wrote:
>   
>> One method (not necessarily the best) is to output the list in 
>> freemarker.  You can assign a variable in freemarker to the value you 
>> are summing.
>>
>> <#assign totalAmount = 0>
>> <table>
>> <#list context.detailList as row>
>>    <tr>
>>      <td>${row.itemDescription}</td>
>>      <td>${row.itemAmount}</td>
>>    </tr>
>>    <#assign totalAmount = totalAmount + row.itemAmount>       
>> </#list>
>>    <tr>
>>       <td>TOTAL</td>
>>       <td>${totalAmount}</td>
>>    </tr>
>>
>>
>>
>> su2 wrote:
>>     
>>> Hi Chris,
>>>
>>> It is a list/result of multiple table joins.
>>>
>>> I have requirement of displaying both aggregated (summed) values as well
>>> as
>>> to display the detail (unsummed) records.
>>>
>>> I have little idea to get detail/unsummed record as list using <form
>>> type=list>. But I do not have any clue how to use sum(column name) with
>>> the
>>> java delegator.
>>>
>>> I have following simple Query - SELECT with WHERE clause. But I need to
>>> have
>>> SUM(OTCMAdjustmentAmount). (Here OrderAndTax is a view-entity created
>>> using
>>> two other view-entities.)
>>>
>>> --------------------------------------------------------------------------------------
>>> List fieldsToSelect = UtilMisc.toList("OTCMAdjustmentAmount");
>>>         	
>>>         	EntityConditionList whereConditions = new
>>> EntityConditionList(UtilMisc.toList(
>>>                     new EntityExpr("OTCMOrderNo", EntityOperator.EQUALS,
>>> OTCMOrderNo),
>>>                     new EntityExpr("OTCMAdjustmentAmountType",
>>> EntityOperator.EQUALS, "SALES_TAX")                    
>>>                   ), EntityOperator.AND); 
>>>         	
>>>         	orderTaxObject = delegator.findByCondition("OrderAndTax",
>>> whereConditions, null, fieldsToSelect, null, null); 
>>> --------------------------------------------------------------------------------------
>>>
>>> I really appreciate your help.
>>>
>>> Thank you.
>>> Su-
>>>
>>>
>>>
>>> Chris Snow-3 wrote:
>>>   
>>>       
>>>> 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-
>>>>>>>   
>>>>>>>       
>>>>>>>           
>>>>>>>               
>>>>>>     
>>>>>>         
>>>>>>             
>>>>>   
>>>>>       
>>>>>           
>>>>     
>>>>         
>>>   
>>>       
>>
>>     
>
>   


Re: is Stroed Procedures possible in OFBiz?

Posted by su2 <sh...@pexsupply.com>.
Unfortunately, my  whole display page is in Form Widget with "Form Type =
list", So I think the best way for this case is to have Query with sum().
Correct me if I am wrong. Because at this point, I am not sure whether I can
convert my entire Form widget into Freemarker.

Thank you for your help.
Su- 



 You can correct me if I am wrong.
 

Chris Snow-3 wrote:
> 
> One method (not necessarily the best) is to output the list in 
> freemarker.  You can assign a variable in freemarker to the value you 
> are summing.
> 
> <#assign totalAmount = 0>
> <table>
> <#list context.detailList as row>
>    <tr>
>      <td>${row.itemDescription}</td>
>      <td>${row.itemAmount}</td>
>    </tr>
>    <#assign totalAmount = totalAmount + row.itemAmount>       
> </#list>
>    <tr>
>       <td>TOTAL</td>
>       <td>${totalAmount}</td>
>    </tr>
> 
> 
> 
> su2 wrote:
>> Hi Chris,
>>
>> It is a list/result of multiple table joins.
>>
>> I have requirement of displaying both aggregated (summed) values as well
>> as
>> to display the detail (unsummed) records.
>>
>> I have little idea to get detail/unsummed record as list using <form
>> type=list>. But I do not have any clue how to use sum(column name) with
>> the
>> java delegator.
>>
>> I have following simple Query - SELECT with WHERE clause. But I need to
>> have
>> SUM(OTCMAdjustmentAmount). (Here OrderAndTax is a view-entity created
>> using
>> two other view-entities.)
>>
>> --------------------------------------------------------------------------------------
>> List fieldsToSelect = UtilMisc.toList("OTCMAdjustmentAmount");
>>         	
>>         	EntityConditionList whereConditions = new
>> EntityConditionList(UtilMisc.toList(
>>                     new EntityExpr("OTCMOrderNo", EntityOperator.EQUALS,
>> OTCMOrderNo),
>>                     new EntityExpr("OTCMAdjustmentAmountType",
>> EntityOperator.EQUALS, "SALES_TAX")                    
>>                   ), EntityOperator.AND); 
>>         	
>>         	orderTaxObject = delegator.findByCondition("OrderAndTax",
>> whereConditions, null, fieldsToSelect, null, null); 
>> --------------------------------------------------------------------------------------
>>
>> I really appreciate your help.
>>
>> Thank you.
>> Su-
>>
>>
>>
>> Chris Snow-3 wrote:
>>   
>>> 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-
>>>>>>   
>>>>>>       
>>>>>>           
>>>>>     
>>>>>         
>>>>   
>>>>       
>>>
>>>     
>>
>>   
> 
> 
> 

-- 
View this message in context: http://www.nabble.com/is-Stroed-Procedures-possible-in-OFBiz--tp25768161p25769827.html
Sent from the OFBiz - User mailing list archive at Nabble.com.


Re: is Stroed Procedures possible in OFBiz?

Posted by Chris Snow <sn...@snowconsulting.co.uk>.
One method (not necessarily the best) is to output the list in 
freemarker.  You can assign a variable in freemarker to the value you 
are summing.

<#assign totalAmount = 0>
<table>
<#list context.detailList as row>
   <tr>
     <td>${row.itemDescription}</td>
     <td>${row.itemAmount}</td>
   </tr>
   <#assign totalAmount = totalAmount + row.itemAmount>       
</#list>
   <tr>
      <td>TOTAL</td>
      <td>${totalAmount}</td>
   </tr>



su2 wrote:
> Hi Chris,
>
> It is a list/result of multiple table joins.
>
> I have requirement of displaying both aggregated (summed) values as well as
> to display the detail (unsummed) records.
>
> I have little idea to get detail/unsummed record as list using <form
> type=list>. But I do not have any clue how to use sum(column name) with the
> java delegator.
>
> I have following simple Query - SELECT with WHERE clause. But I need to have
> SUM(OTCMAdjustmentAmount). (Here OrderAndTax is a view-entity created using
> two other view-entities.)
>
> --------------------------------------------------------------------------------------
> List fieldsToSelect = UtilMisc.toList("OTCMAdjustmentAmount");
>         	
>         	EntityConditionList whereConditions = new
> EntityConditionList(UtilMisc.toList(
>                     new EntityExpr("OTCMOrderNo", EntityOperator.EQUALS,
> OTCMOrderNo),
>                     new EntityExpr("OTCMAdjustmentAmountType",
> EntityOperator.EQUALS, "SALES_TAX")                    
>                   ), EntityOperator.AND); 
>         	
>         	orderTaxObject = delegator.findByCondition("OrderAndTax",
> whereConditions, null, fieldsToSelect, null, null); 
> --------------------------------------------------------------------------------------
>
> I really appreciate your help.
>
> Thank you.
> Su-
>
>
>
> Chris Snow-3 wrote:
>   
>> 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-
>>>>>   
>>>>>       
>>>>>           
>>>>     
>>>>         
>>>   
>>>       
>>
>>     
>
>   


Re: is Stroed Procedures possible in OFBiz?

Posted by su2 <sh...@pexsupply.com>.
Hi Chris,

It is a list/result of multiple table joins.

I have requirement of displaying both aggregated (summed) values as well as
to display the detail (unsummed) records.

I have little idea to get detail/unsummed record as list using <form
type=list>. But I do not have any clue how to use sum(column name) with the
java delegator.

I have following simple Query - SELECT with WHERE clause. But I need to have
SUM(OTCMAdjustmentAmount). (Here OrderAndTax is a view-entity created using
two other view-entities.)

--------------------------------------------------------------------------------------
List fieldsToSelect = UtilMisc.toList("OTCMAdjustmentAmount");
        	
        	EntityConditionList whereConditions = new
EntityConditionList(UtilMisc.toList(
                    new EntityExpr("OTCMOrderNo", EntityOperator.EQUALS,
OTCMOrderNo),
                    new EntityExpr("OTCMAdjustmentAmountType",
EntityOperator.EQUALS, "SALES_TAX")                    
                  ), EntityOperator.AND); 
        	
        	orderTaxObject = delegator.findByCondition("OrderAndTax",
whereConditions, null, fieldsToSelect, null, null); 
--------------------------------------------------------------------------------------

I really appreciate your help.

Thank you.
Su-



Chris Snow-3 wrote:
> 
> 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-
>>>>   
>>>>       
>>>
>>>     
>>
>>   
> 
> 
> 

-- 
View this message in context: http://www.nabble.com/is-Stroed-Procedures-possible-in-OFBiz--tp25768161p25769503.html
Sent from the OFBiz - User mailing list archive at Nabble.com.


Re: is Stroed Procedures possible in OFBiz?

Posted by Chris Snow <sn...@snowconsulting.co.uk>.
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-
>>>   
>>>       
>>
>>     
>
>   


Re: is Stroed Procedures possible in OFBiz?

Posted by su2 <sh...@pexsupply.com>.
Hi Mridul,

Thanks for the reply. But <complex-alias> does not work if I want it in a
view-entity which is created using  other view-entities(I think somebody
mentioned on my that post earlier that its a bug and he will look into it
and if not resolved he will create JIRA issue). It just works if i need to
create a view-entity using two entities.

If I could use <complex-alias>, then my life would have been much easier.

I have following simple Query - SELECT with WHERE clause in a Java Method.
But I need to have
SUM(OTCMAdjustmentAmount). (Here OrderAndTax is a view-entity created using
two other view-entities.)

--------------------------------------------------------------------------------------
List fieldsToSelect = UtilMisc.toList("OTCMAdjustmentAmount");
        	
        	EntityConditionList whereConditions = new
EntityConditionList(UtilMisc.toList(
                    new EntityExpr("OTCMOrderNo", EntityOperator.EQUALS,
OTCMOrderNo),
                    new EntityExpr("OTCMAdjustmentAmountType",
EntityOperator.EQUALS, "SALES_TAX")                    
                  ), EntityOperator.AND); 
        	
        	orderTaxObject = delegator.findByCondition("OrderAndTax",
whereConditions, null, fieldsToSelect, null, null); 
--------------------------------------------------------------------------------------

I really appreciate your help.

Thank you.
Shuchi-



Mridul Pathak-2 wrote:
> 
> Shuchi,
> 
> One way is to define a view entity, in your component's entity-model.xml
> using <view-entity> tag.  You can use
> <alias><complex-alias></complex-alias></alias> child tags for your summing
> requirement.  This view will also be accessible in webtools.  In your code
> you can then use delegator methods to retrieve the list from this view
> entity.
> -- 
> Thanks & Regards
> Mridul Pathak
> Hotwax Media
> http://www.hotwaxmedia.com
> mridul.pathak@hotwaxmedia.com
> -------------------------------------------------
> direct: +91 - 942.592.6892
> 
> 
> On Tue, Oct 6, 2009 at 7:14 PM, su2 <sh...@pexsupply.com> 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-
>> >>
>> >
>> >
>> >
>>
>> --
>> 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.
>>
>>
> 
> 

-- 
View this message in context: http://www.nabble.com/is-Stroed-Procedures-possible-in-OFBiz--tp25768161p25770141.html
Sent from the OFBiz - User mailing list archive at Nabble.com.


Re: is Stroed Procedures possible in OFBiz?

Posted by Mridul Pathak <mr...@hotwaxmedia.com>.
Shuchi,

One way is to define a view entity, in your component's entity-model.xml
using <view-entity> tag.  You can use
<alias><complex-alias></complex-alias></alias> child tags for your summing
requirement.  This view will also be accessible in webtools.  In your code
you can then use delegator methods to retrieve the list from this view
entity.
-- 
Thanks & Regards
Mridul Pathak
Hotwax Media
http://www.hotwaxmedia.com
mridul.pathak@hotwaxmedia.com
-------------------------------------------------
direct: +91 - 942.592.6892


On Tue, Oct 6, 2009 at 7:14 PM, su2 <sh...@pexsupply.com> 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-
> >>
> >
> >
> >
>
> --
> 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.
>
>

Re: is Stroed Procedures possible in OFBiz?

Posted by su2 <sh...@pexsupply.com>.
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.


Re: is Stroed Procedures possible in OFBiz?

Posted by Chris Snow <sn...@snowconsulting.co.uk>.
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-
>