[jira] [Commented] (DBCP-587) DBCP and Transparent Application Continuity

["Kirk Hill (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DBCP-587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17616703#comment-17616703 ] 

Kirk Hill commented on DBCP-587:
--------------------------------

{code:java}
// code placeholder
import org.apache.commons.dbcp2.BasicDataSource;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class BasicDB{
    private static BasicDataSource dataSource;    private static BasicDataSource getDataSource() {        {            if (dataSource == null)
            {
                BasicDataSource ds = new BasicDataSource();
                ds.setUrl("jdbc:oracle:thin:@pdb_tac");
                ds.setUsername("hr");
                ds.setPassword("my_password");
                //ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
                ds.setDriverClassName("oracle.jdbc.replay.OracleDataSourceImpl");
                //ds.setDriverClass("oracle.jdbc.replay.OracleDataSourceImpl");
                ds.setDefaultAutoCommit(false);                ds.setInitialSize(5);
                ds.setMinIdle(5);
                ds.setMaxIdle(10);
                ds.setMaxOpenPreparedStatements(100);                dataSource = ds;
            }
            return dataSource;
        }    }    final static String ORACLE_WALLET= "c:/my_wallet";
    private void pressAnyKeyToContinue()
    {
        System.out.print("Press any key to continue...");
        try { System.in.read(); }
        catch(Exception e) { e.printStackTrace(); }
    }
    public String getInstanceName(Connection conn) throws SQLException {
        PreparedStatement pstmt = conn.prepareStatement("select instance_name from v$instance");
        String r = new String();        for(ResultSet result = pstmt.executeQuery(); result.next(); r = result.getString("instance_name")) {
        }        pstmt.close();
        return r;
    }    public String getStatValue(Connection conn, String statName) throws SQLException {
        String q = "select value from v$statname sn, v$sesstat ss where sn.statistic#=ss.statistic# and sn.name=? and ss.sid=SYS_CONTEXT('USERENV','SID')";
        String r = null;
        PreparedStatement pstmt = conn.prepareStatement(q);
        pstmt.setString(1, statName);        for(ResultSet rs = pstmt.executeQuery(); rs.next(); r = rs.getString("value")) {
        }        pstmt.close();
        return r;
    }    private void doTx(Connection c, int numValue) throws SQLException {
        String updsql = "UPDATE test SET v=UPPER(v) WHERE id=?";
        PreparedStatement pstmt = null;
        pstmt = c.prepareStatement(updsql);
        c.setAutoCommit(false);        for(int i = 0; i < numValue; ++i) {
            pstmt.setInt(1, i);
            pstmt.executeUpdate();
        }        c.commit();
        pstmt.close();
    }
    public static void main(String[] args) throws SQLException {
        //Connection conn = null;
        BasicDB self = new BasicDB();
        int numValue = 5000;        String statname = "CPU used by this session";        try {
            System.setProperty("oracle.net.tns_admin", "C:/oracle/product/19.0.0/client_1/network/admin");
            System.setProperty("oracle.net.wallet_location", "(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${ORACLE_WALLET})))");
           BasicDataSource dataSource = BasicDB.getDataSource();
           Connection conn = dataSource.getConnection();
            PrintStream var10000 = System.out;
            String var10001 = self.getInstanceName(conn);
            var10000.println("Instance Name = " + var10001);            System.out.println("Performing transactions");
            self.pressAnyKeyToContinue();
            self.doTx(conn, numValue);            var10001 = self.getInstanceName(conn);
            var10000.println("Instance Name = " + var10001);        } catch (SQLException e) {
            System.out.println("BasicDB - " + "SQLException occurred : "
                    + e.getMessage());
        }    }
} {code}
The above is a copy of my testing code.  If I use the driver oracle.jdbc.driver.OracleDriver I can connect but the minute I test the Transparent Application Continuity on a two node Oracle RAC with the app connected to the SCAN listener I should be able to shutdown the node that the app is connected to and my Oracle SCAN listener should simply switch the app over to the other node.  This doesn't work using DBCP to create the connection pools.  If we could use the oracle.jdbc.replay.OracleDataSourceImpl driver then it would allow this to function properly. 

> DBCP and Transparent Application Continuity
> -------------------------------------------
>
>                 Key: DBCP-587
>                 URL: https://issues.apache.org/jira/browse/DBCP-587
>             Project: Commons DBCP
>          Issue Type: Bug
>    Affects Versions: 2.9.0
>            Reporter: Kirk Hill
>            Priority: Major
>
> Oracle databases have a high-availability setup that uses an item called Transparent Application Continuity.  It requires using the following driver class name for "oracle.jdbc.replay.OracleDataSourceImpl"  When I attempt to use this driver I get the following error message.  
> SQLException occurred : Cannot create JDBC driver of class 'oracle.jdbc.replay.OracleDataSourceImpl' 
> Having this as a way to create connection pools would greatly enhance your product.  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)