is Stroed Procedures possible in OFBiz?
Locked
 
|
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?
|
|
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- > |
|
|
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-
|
Re: is Stroed Procedures possible in OFBiz?
|
|
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?
|
|
In reply to this post by su2
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 [hidden email] ------------------------------------------------- direct: +91 - 942.592.6892 On Tue, Oct 6, 2009 at 7:14 PM, su2 <[hidden email]> 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. > > |
|
|
In reply to this post by Chris Snow-3
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-
|
Re: is Stroed Procedures possible in OFBiz?
|
|
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- >>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > |
|
|
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.
«
Return to OFBiz - User
|
1 view|%1 views
|