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-
