Hello,
I'm trying to connect to a 2nd external database (not opentaps) where I only
have read only rights. It's an adserver that powers the ads on our site.
We only have read permissions and can't alter the tables at all. our normal
opentaps databse uses postgres, but the adserver uses MySQL.
What i've tried is to create a new datasource in the
framework/entity/config/entityengine.xml.
I then wrote a service which uses the ConnectionFactory class. The problem is
that I get a Java Heap Space error the first time the service runs.
After that the service will run succesfully without any errors, until the next
time ofbiz get started, then I get the heap space error once, and then it works
fine again.
I don't want to do just strait JDBC becauseI want to take advantage of the
connection pooling within opentaps.
Any help would be appreciated. The service and datasource are included.
-- datasource from entityengine.xml --
<datasource name="mysql-advertpro"
helper-class="org.ofbiz.entity.datasource.GenericHelperDAO"
field-type-name="mysql"
check-on-start="true"
add-missing-on-start="false"
check-pks-on-start="false"
use-foreign-keys="true"
join-style="ansi-no-parenthesis"
alias-view-columns="false"
drop-fk-use-foreign-key-keyword="true"
table-type="MyISAM"
character-set="latin1"
collate="latin1_general_cs">
<inline-jdbc
jdbc-driver="com.mysql.jdbc.Driver"
jdbc-uri="jdbc:mysql://<ipaddress>/advertpro?autoReconnect=true"
jdbc-username="<username>"
jdbc-password="<password>"
isolation-level="ReadCommitted"
pool-minsize="2"
pool-maxsize="20"/>
</datasource>
-- Custom Service --
public static Map getBannerCampaignDetails(DispatchContext ctx, Map context) {
Connection conn = null;
PreparedStatement statement = null;
ResultSet rs = null;
FastList bannerList = FastList.newInstance();
String sql = "MySQL String Goes Here";
try{
conn =
ConnectionFactory.getConnection("mysql-advertpro");
if (conn == null) {
throw new Exception("No advertpro connection
configured");
}
statement = conn.prepareStatement(sql);
statement.setString(1, (String) context.get("partyId"));
rs = statement.executeQuery();
while(rs.next()){
FastMap row = FastMap.newInstance();
row.put("views", rs.getString("views"));
row.put("name", rs.getString("name"));
row.put("campaignId",
rs.getString("campaignId"));
row.put("maxviews", rs.getString("maxviews"));
row.put("bannerType",
rs.getString("bannerType"));
row.put("width", rs.getString("width"));
row.put("height", rs.getString("height"));
row.put("viewsRemaining",
rs.getString("viewsRemaining"));
row.put("startdate", rs.getString("startdate"));
row.put("stopdate", rs.getString("stopdate"));
bannerList.add(row);
}
statement.close();
rs.close();
} catch (Exception e){
ServiceUtil.returnError("Error getting connection to
database" + e);
}
finally {
if (statement != null)
try {
statement.close();
} catch (Exception e) {}
if (rs != null)
try {
rs.close();
} catch (Exception e) {}
if (conn != null)
try {
conn.close();
} catch (Exception e) {}
}
if(bannerList.size() > 0){
return UtilMisc.toMap("bannerList", bannerList);
} else {
return UtilMisc.toMap("bannerList",
FastList.newInstance());
}
}
