Hi all,

Im running cache Database configuration. Here is the issue being observed:

1. If is run Default cache server and cluster (seperate JVM) im observing that the storing to DB is failing with Connection Exception
2. where-as if run only the cluster program alone without the Cache Server im not facing any issues, the data is getting store properly, Unsure what is going wrong here ?

Here is the configuration snippets:

cache-config.xml
<?xml version="1.0"?>

<cache-config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://xmlns.oracle.com/coherence/coherence-cache-config"
xsi:schemaLocation="http://xmlns.oracle.com/coherence/coherence-cache-config coherence-cache-config.xsd">
<caching-scheme-mapping>
<cache-mapping>
<cache-name>dbexample</cache-name>
<scheme-name>distributed</scheme-name>
</cache-mapping>
</caching-scheme-mapping>

<caching-schemes>
<distributed-scheme>
<scheme-name>distributed</scheme-name>
<service-name>DistributedCache</service-name>
<thread-count>4</thread-count>
<request-timeout>60s</request-timeout>
<backing-map-scheme>
<read-write-backing-map-scheme>
<internal-cache-scheme>
<local-scheme>
<scheme-name>SampleMemoryScheme</scheme-name>
</local-scheme>
</internal-cache-scheme>
<cachestore-scheme>
<class-scheme>
<class-name>com.coherence.KnDBCacheStore</class-name>
</class-scheme>
</cachestore-scheme>
</read-write-backing-map-scheme>
</backing-map-scheme>
<autostart>true</autostart>
</distributed-scheme>
<local-scheme>
<scheme-name>LocalSizeLimited</scheme-name>
<eviction-policy>LRU</eviction-policy>
<high-units>1000</high-units>
<expiry-delay>1h</expiry-delay>
</local-scheme>
</caching-schemes>
</cache-config>

==========================

tangosol-coherence-override.xml

<?xml version='1.0'?>

<coherence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://xmlns.oracle.com/coherence/coherence-operational-config"
xsi:schemaLocation="http://xmlns.oracle.com/coherence/coherence-operational-config coherence-operational-config.xsd">
<cluster-config>
<member-identity>
<cluster-name system-property="tangosol.coherence.cluster">kn_test</cluster-name>
</member-identity>
<unicast-listener>
<well-known-addresses>
<socket-address id="1">
<address>192.168.7.3</address>
<port>8088</port>
</socket-address>
<socket-address id="2">
<address>192.168.7.4</address>
<port>8088</port>
</socket-address>
</well-known-addresses>
</unicast-listener>
</cluster-config>

<configurable-cache-factory-config>
<init-params>
<init-param>
<param-type>java.lang.String</param-type>
<param-value system-property="tangosol.coherence.cacheconfig">cache-config.xml</param-value>
</init-param>
</init-params>
</configurable-cache-factory-config>
</coherence>


package com.coherence;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import com.tangosol.net.cache.CacheStore;
import com.tangosol.util.Base;

public class KnDBCacheStore extends Base implements CacheStore {

     protected Connection conn;
     protected String tableName = "DG.SUBSCRIBERINFO";
     String localIPAddr = "192.168.7.3";
     String localDBDSN = "DG_010031";
     String localDBUid = "dbuser";
     String localDBPwd = "dbuser";
     String localDBPort = "53389";

     private static final String DB_DRIVER = "com.timesten.jdbc.TimesTenDriver";
     String localConnURL = "jdbc:timesten:client:TTC_Server=" + localIPAddr
               + ";TTC_Server_DSN=" + localDBDSN + ";UID=" + localDBUid + ";PWD="
               + localDBPwd + ";TCP_PORT=" + localDBPort + ";TTC_Timeout=180";

     protected void configureConn() {
          try {
               Class.forName(DB_DRIVER);
               System.out.println("DB Connection URL :" + localConnURL);
               conn = DriverManager.getConnection(localConnURL);
          } catch (Exception e) {
               e.printStackTrace();
          }
     }

     /**
     * Obtain the name of the table this CacheStore is persisting to.
     *
     * @return the name of the table this CacheStore is persisting to
     */
     public String getTableName() {
          return tableName;
     }

     /**
     * Obtain the connection being used to connect to the database.
     *
     * @return the connection used to connect to the database
     */
     public Connection getConnection() {
          return conn;
     }

     @Override
     public Object load(Object key) {
          Object value = null;
          Connection conn = getConnection();
          String sqlQry = "SELECT SUBSCRNAME FROM " + getTableName()
                    + " WHERE MDN=?";
          try {
               PreparedStatement pStmt = conn.prepareStatement(sqlQry);
               pStmt.setString(1, String.valueOf(key));
               ResultSet rs = pStmt.executeQuery();
               if (rs.next()) {
                    value = rs.getString("SUBSCRNAME");
               }
          } catch (Exception e) {
               e.printStackTrace();
          }
          return value;
     }

     @Override
     public Map loadAll(Collection arg0) {
          throw new UnsupportedOperationException();
     }

     @Override
     public void erase(Object key) {
          Connection con = getConnection();
          String sSQL = "DELETE FROM " + getTableName() + " WHERE MDN=?";
          try {
               PreparedStatement stmt = con.prepareStatement(sSQL);

               stmt.setString(1, String.valueOf(key));
               stmt.executeUpdate();
               stmt.close();
          } catch (SQLException e) {
               throw ensureRuntimeException(e, "Erase failed: key=" + key);
          }

     }

     @Override
     public void eraseAll(Collection arg0) {
          throw new UnsupportedOperationException();

     }

     @Override
     public void store(Object key, Object value) {
          Connection con = getConnection();
          String sTable = getTableName();
          String sSQL;

          // the following is very inefficient; it is recommended to use DB
          // specific functionality that is, REPLACE for MySQL or MERGE for Oracle
          if (load(key) != null) {
               // key exists - update
               sSQL = "UPDATE " + sTable + " SET SUBSCRNAME = ? where MDN = ?";
          } else {
               // new key - insert
               sSQL = "INSERT INTO " + sTable + " (SUBSCRNAME, MDN) VALUES (?,?)";
          }
          try {
               PreparedStatement stmt = con.prepareStatement(sSQL);
               int i = 0;
               stmt.setString(++i, String.valueOf(value));
               stmt.setString(++i, String.valueOf(key));
               stmt.executeUpdate();
               stmt.close();
          } catch (SQLException e) {
               throw ensureRuntimeException(e, "Store failed: key=" + key);
          }

     }

     @Override
     public void storeAll(Map arg0) {
          throw new UnsupportedOperationException();

     }

     /**
     * Iterate all keys in the underlying store.
     *
     * @return a read-only iterator of the keys in the underlying store
     */
     public Iterator<Object> keys() {
          Connection con = getConnection();
          String sSQL = "SELECT MDN FROM " + getTableName();
          List<Object> list = new LinkedList<Object>();

          try {
               PreparedStatement stmt = con.prepareStatement(sSQL);
               ResultSet rslt = stmt.executeQuery();
               while (rslt.next()) {
                    Object oKey = rslt.getString(1);
                    list.add(oKey);
               }
               stmt.close();
          } catch (SQLException e) {
               throw ensureRuntimeException(e, "Iterator failed");
          }

          return list.iterator();
     }

}