?
Solved

DB connection problem

Posted on 2011-02-14
34
Medium Priority
?
457 Views
Last Modified: 2012-05-11
I have app which connect to DB using pool:
public class ConnectionPool implements Runnable
{
    // Number of initial connections to make.
    private int m_InitialConnectionCount = 2;
    // A list of available connections for use.
    private Vector m_AvailableConnections = new Vector();
    // A list of connections being used currently.
    private Vector m_UsedConnections = new Vector();
    // The URL string used to connect to the database
    private String m_URLString = null;
    // The username used to connect to the database
    private String m_UserName = null;
    // The password used to connect to the database
    private String m_Password = null;
    // The cleanup thread
    private Thread m_CleanupThread = null;


    //Constructor
    public ConnectionPool(String urlString, String user, String passwd) throws SQLException
    {
        // Initialize the required parameters
        m_URLString = urlString;
        m_UserName = user;
        m_Password = passwd;

        for(int cnt=0; cnt<m_InitialConnectionCount; cnt++)
        {
            // Add a new connection to the available list.
            m_AvailableConnections.addElement(getConnection());
        }

        // Create the cleanup thread
        m_CleanupThread = new Thread(this);
        m_CleanupThread.start();
    }

    private Connection getConnection() throws SQLException
    {
        return DriverManager.getConnection(m_URLString, m_UserName, m_Password);
    }

    public synchronized Connection checkout() throws SQLException
    {
        Connection newConnxn = null;

        if(m_AvailableConnections.isEmpty())
        {
            // Im out of connections. Create one more.
             newConnxn = getConnection();
            // Add this connection to the "Used" list.
             m_UsedConnections.addElement(newConnxn);
            // We dont have to do anything else since this is
            // a new connection.
        }
        else
        {
            // Connections exist !
            // Get a connection object
            newConnxn = (Connection)m_AvailableConnections.lastElement();
            // Remove it from the available list.
            m_AvailableConnections.removeElement(newConnxn);
            // Add it to the used list.
            m_UsedConnections.addElement(newConnxn);
        }

        // Either way, we should have a connection object now.
        return newConnxn;
    }


    public synchronized void checkin(Connection c)
    {
        if(c != null)
        {
            // Remove from used list.
            m_UsedConnections.removeElement(c);
            // Add to the available list
            m_AvailableConnections.addElement(c);
        }
    }

    public int availableCount()
    {
        return m_AvailableConnections.size();
    }

    public void run()
    {
        try
        {
            while(true)
            {
                synchronized(this)
                {
                    while(m_AvailableConnections.size() > m_InitialConnectionCount)
                    {
                        // Clean up extra available connections.
                        Connection c = (Connection)m_AvailableConnections.lastElement();
                        m_AvailableConnections.removeElement(c);

                        // Close the connection to the database.
                        c.close();
                    }

                    // Clean up is done
                }

                System.out.println("CLEANUP : Available Connections : " + availableCount());

                // Now sleep for 1 minute
                Thread.sleep(60000 * 1);
            }
        }
        catch(SQLException sqle)
        {
            sqle.printStackTrace();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
}

Open in new window

It's execute two queries select and insert.
public class ConnectDB {

    Statement statement;
    ResultSet rs;
    PreparedStatement ps;

    double[] outY = new double[10];
    Timestamp[] timest = new Timestamp[10];
   private ConnectionPool pool = null;

     public ConnectDB() throws SQLException {
        pool = new ConnectionPool("jdbc:oracle:thin:@10.1.1.7:1521:dbfx", "xx", "xx");
    }

     public ResultSet select(String sql) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException  {
        Connection conn = pool.checkout();
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
         System.out.println(" ResultSet =>  " + rs + " : " + new java.util.Date());
        return rs;
    }

    public void insert(double[] outY, Timestamp[] timest) throws SQLException {

        Connection conn = pool.checkout();
        PreparedStatement pstat = conn.prepareStatement

    ("INSERT INTO RBF_DATA (OUTY0, OUTY1, OUTY2, OUTY3, OUTY4, OUTY5, OUTY6, OUTY7, OUTY8, OUTY9, TIMEST) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
    for (int i = 0; i <= 9; i++) {
        pstat.setFloat( i+1, (float) outY[i]);
    }
    pstat.setTimestamp( 11, timest[0]);
    ResultSet rset = pstat.executeQuery();
    System.out.println(" PreparedStatement =>  " + pstat + " : " + new java.util.Date());

        pool.checkin(conn);

        pstat.close();
    }
}

Open in new window

It starts work normally, but after 3-4 minutes generate this error:
run:
CLEANUP : Available Connections : 2
          switch on the Timer
CLEANUP : Available Connections : 2
=>
 ResultSet =>  oracle.jdbc.driver.OracleResultSetImpl@367c218e : Mon Feb 14 11:36:17 EET 2011
 PreparedStatement =>  oracle.jdbc.driver.OraclePreparedStatementWrapper@50a69b6b : Mon Feb 14 11:36:17 EET 2011
 ResultSet =>  oracle.jdbc.driver.OracleResultSetImpl@6a25b72a : Mon Feb 14 11:36:18 EET 2011
 PreparedStatement =>  oracle.jdbc.driver.OraclePreparedStatementWrapper@521c5cd7 : Mon Feb 14 11:36:18 EET 2011
...............................
ResultSet =>  oracle.jdbc.driver.OracleResultSetImpl@61bf8785 : Mon Feb 14 11:40:31 EET 2011
 PreparedStatement =>  oracle.jdbc.driver.OraclePreparedStatementWrapper@655a5ee5 : Mon Feb 14 11:40:31 EET 2011
 ResultSet =>  oracle.jdbc.driver.OracleResultSetImpl@14562534 : Mon Feb 14 11:40:32 EET 2011
 PreparedStatement =>  oracle.jdbc.driver.OraclePreparedStatementWrapper@458e439a : Mon Feb 14 11:40:32 EET 2011
14.02.2011 11:40:32 connect.SelectData run
SEVERE: null
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found
 
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:412)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:531)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:221)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:503)
        at java.sql.DriverManager.getConnection(DriverManager.java:582)
        at java.sql.DriverManager.getConnection(DriverManager.java:185)
        at connect.ConnectionPool.getConnection(ConnectionPool.java:55)
        at connect.ConnectionPool.checkout(ConnectionPool.java:65)
        at connect.ConnectDB.select(ConnectDB.java:25)
        at connect.SelectData.run(SelectData.java:29)
        at java.util.TimerThread.mainLoop(Timer.java:512)
        at java.util.TimerThread.run(Timer.java:462)

Open in new window

Please advice where is the problem?
Another app which use similar pool and just insert data in the table from which this app select it works OK non-stop during workdays.
0
Comment
Question by:gbcbr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 18
  • 8
  • 8
34 Comments
 
LVL 26

Expert Comment

by:ksivananth
ID: 34886626
make sure you release the connection and preparedstatement after your select!
0
 

Author Comment

by:gbcbr
ID: 34886664
insert method exactly the same with other app which is work without problem:
public class ConnectDB {

    Statement statement;
   private ConnectionPool pool = null;

     public ConnectDB() throws SQLException {
        pool = new ConnectionPool("jdbc:oracle:thin:@10.1.1.7:1521:dbfx", "xx", "xx");
    }

    public void insert(String sql) throws SQLException {

        Connection conn = pool.checkout();
        statement = (Statement) conn.createStatement();
        int st = statement.executeUpdate(sql);

        pool.checkin(conn);

        statement.close();
    }
}

Open in new window

and this code, as I suppose, release connection to the pool
pool.checkin(conn);

Open in new window

0
 
LVL 26

Accepted Solution

by:
ksivananth earned 1600 total points
ID: 34886668
insert looks fine, I asked you to check that in select!
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:gbcbr
ID: 34886831
I add
pool.checkin(conn);
into select method, it starts work better, at least doesn't stack, any way sometimes appear ORA-01000 and ORA-00604, but app continue to work.
public ResultSet select(String sql) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException  {
        Connection conn = pool.checkout();
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
         System.out.println(" ResultSet =>  " + rs + " : " + new java.util.Date());
         pool.checkin(conn);
        return rs;
    }

Open in new window

ResultSet =>  oracle.jdbc.driver.OracleResultSetImpl@1aee75b7 : Mon Feb 14 12:41:31 EET 2011
 PreparedStatement =>  oracle.jdbc.driver.OraclePreparedStatementWrapper@1d724f31 : Mon Feb 14 12:41:31 EET 2011
 ResultSet =>  oracle.jdbc.driver.OracleResultSetImpl@3bad2911 : Mon Feb 14 12:41:32 EET 2011
 PreparedStatement =>  oracle.jdbc.driver.OraclePreparedStatementWrapper@656339b2 : Mon Feb 14 12:41:32 EET 2011
14.02.2011 12:41:32 connect.SelectData run
SEVERE: null
java.sql.SQLException: ORA-00604: ¿¿¿¿¿¿ ¿¿ ¿¿¿¿¿¿¿¿¿¿¿ SQL-¿¿¿¿¿¿ 1
ORA-01000: ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿
ORA-00604: ¿¿¿¿¿¿ ¿¿ ¿¿¿¿¿¿¿¿¿¿¿ SQL-¿¿¿¿¿¿ 1
ORA-01000: ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿
ORA-01000: ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
        at connect.ConnectDB.select(ConnectDB.java:27)
        at connect.SelectData.run(SelectData.java:29)
        at java.util.TimerThread.mainLoop(Timer.java:512)
        at java.util.TimerThread.run(Timer.java:462)
 ResultSet =>  oracle.jdbc.driver.OracleResultSetImpl@559dbc5c : Mon Feb 14 12:41:34 EET 2011
 PreparedStatement =>  oracle.jdbc.driver.OraclePreparedStatementWrapper@66b967ed : Mon Feb 14 12:41:34 EET 2011
 ResultSet =>  oracle.jdbc.driver.OracleResultSetImpl@16562d4b : Mon Feb 14 12:41:35 EET 2011
 PreparedStatement =>  oracle.jdbc.driver.OraclePreparedStatementWrapper@73b7a261 : Mon Feb 14 12:41:35 EET 2011

Open in new window

0
 

Author Comment

by:gbcbr
ID: 34887072
I check open cursors and found more than 3000 open but 99% of them inactive.
I can't find how to close all inactive cursors and how to close them after execution.
Please advice
0
 
LVL 26

Expert Comment

by:ksivananth
ID: 34887081
>>I check open cursors and found more than 3000 open but 99% of them inactive.

close the restultset in the select after the data retrieved from it
0
 

Author Comment

by:gbcbr
ID: 34887101
Tried to this before, but because if it's return method, if I place rs.close(); after return rs; I have - unreachable statement,
if before - Closed result set.
 
public ResultSet select(String sql) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException  {
        Connection conn = pool.checkout();
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
         System.out.println(" ResultSet =>  " + rs + " : " + new java.util.Date());
         rs.close();
         pool.checkin(conn);
        return rs;
//        rs.close();

    }

Open in new window

Please advice
0
 
LVL 26

Expert Comment

by:ksivananth
ID: 34887114
you need to close it after you processed the data, i.e., in the caller( the method which calls select )
0
 

Author Comment

by:gbcbr
ID: 34887130
select called by Timer
public class Main {

       private static Timer timer;


    public static void main(String[] args) throws Exception {

        
        try {
            timer = new Timer();
            timer.schedule(new SelectData(), 10000, 1000);

            System.out.println("          switch on the Timer");
            System.out.println("=>");

        } catch (Exception ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null,
                                                          ex);
        }

    }

Open in new window

How to close rs from here?
0
 
LVL 26

Expert Comment

by:ksivananth
ID: 34887138
where it is called in SelectData, if don't know, post SelectData
0
 

Author Comment

by:gbcbr
ID: 34887144
public SelectData() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, FieldNotFound, NotDefinedException, Exception {

        conn = new ConnectDB();
        aBot = new AlgoBot();
    }

    String[] symbol = new String[10];
    double[] openBid = new double[10];
    double[] openAsk = new double[10];
    Timestamp[] timest = new Timestamp[10];

    public void run() {
        try {
            ResultSet rs =
                    conn.select("SELECT * FROM EURUSD where timest = ( select max( timest ) from EURUSD ) and rownum = 1 union all "
                    + "SELECT * FROM EURCHF where timest = ( select max( timest ) from EURCHF ) and rownum = 1 union all "
                    + "SELECT * FROM EURGBP where timest = ( select max( timest ) from EURGBP ) and rownum = 1 union all "
                    + "SELECT * FROM USDCHF where timest = ( select max( timest ) from USDCHF ) and rownum = 1 union all "
                    + "SELECT * FROM GBPUSD where timest = ( select max( timest ) from GBPUSD ) and rownum = 1 union all "
                    + "SELECT * FROM GBPCHF where timest = ( select max( timest ) from GBPCHF ) and rownum = 1 union all "
                    + "SELECT * FROM EURJPY where timest = ( select max( timest ) from EURJPY ) and rownum = 1 union all "
                    + "SELECT * FROM USDJPY where timest = ( select max( timest ) from USDJPY ) and rownum = 1 union all "
                    + "SELECT * FROM GBPJPY where timest = ( select max( timest ) from GBPJPY ) and rownum = 1 union all "
                    + "SELECT * FROM CHFJPY where timest = ( select max( timest ) from CHFJPY ) and rownum = 1");

            while (rs.next()) {

                String s = rs.getString("symbol");
                Timestamp ts = rs.getTimestamp("TIMEST");
                double bpx = rs.getDouble("BIDPX");
                double apx = rs.getDouble("ASKPX");

                if (s.equals("EUR/USD")) {

                    timest[0] = ts;
                    openBid[0] = bpx;
                    openAsk[0] = apx;

                } else if (s.equals("EUR/CHF")) {

                    timest[1] = ts;
                    openBid[1] = bpx;
                    openAsk[1] = apx;

                } else if (s.equals("EUR/GBP")) {

                    timest[2] = ts;
                    openBid[2] = bpx;
                    openAsk[2] = apx;

                } else if (s.equals("USD/CHF")) {

                    timest[3] = ts;
                    openBid[3] = bpx;
                    openAsk[3] = apx;

                } else if (s.equals("GBP/USD")) {

                    timest[4] = ts;
                    openBid[4] = bpx;
                    openAsk[4] = apx;

                } else if (s.equals("GBP/CHF")) {

                    timest[5] = ts;
                    openBid[5] = bpx;
                    openAsk[5] = apx;

                } else if (s.equals("EUR/JPY")) {

                    timest[6] = ts;
                    openBid[6] = bpx;
                    openAsk[6] = apx;

                } else if (s.equals("USD/JPY")) {

                    timest[7] = ts;
                    openBid[7] = bpx;
                    openAsk[7] = apx;

                } else if (s.equals("GBP/JPY")) {

                    timest[8] = ts;
                    openBid[8] = bpx;
                    openAsk[8] = apx;

                } else if (s.equals("CHF/JPY")) {

                    timest[9] = ts;
                    openBid[9] = bpx;
                    openAsk[9] = apx;

                    equals(rs);

                    aBot.algoBot(openBid, openAsk, timest);

                } else {
              }
            }
            rs.close();
        } catch (Exception ex) {
            Logger.getLogger(SelectData.class.getName()).log(Level.SEVERE,
                    null, ex);
        }
    }
}

Open in new window

0
 
LVL 26

Assisted Solution

by:ksivananth
ksivananth earned 1600 total points
ID: 34887168
close is there, could be that there were exceptions! try this changes,

>>try {
            ResultSet rs =
                    conn.select
>>

replace it with

ResultSet rs = null ;
try {
            rs =
                    conn.select

and then

>>} catch (Exception ex) {
            Logger.getLogger(SelectData.class.getName()).log(Level.SEVERE,
                    null, ex);
        }
>>

with

} catch (Exception ex) {
try{
  if ( rs != null ) rs.close() ;
}catch( SQLException sqle ){
            Logger.getLogger(SelectData.class.getName()).log(Level.SEVERE,
                    "Error while closing resultset", ex);
}
            Logger.getLogger(SelectData.class.getName()).log(Level.SEVERE,
                    null, ex);
        }
0
 

Author Comment

by:gbcbr
ID: 34887268
Still have permanent increasing number of cursors, since start app, looks like rs closed, but cursor not.
COUNT(*)
----------
       583

SQL> select count(*) from v$open_cursor;

  COUNT(*)
----------
       584

SQL> select count(*) from v$open_cursor;

  COUNT(*)
----------
       636

SQL> select count(*) from v$open_cursor;

  COUNT(*)
----------
       665

SQL> select count(*) from v$open_cursor;

  COUNT(*)
----------
       713

SQL> select count(*) from v$open_cursor;

  COUNT(*)
----------
       783

Open in new window

0
 
LVL 26

Expert Comment

by:ksivananth
ID: 34887281
check some other code calling it!
0
 

Author Comment

by:gbcbr
ID: 34887564
But it calling from two methods only, I even move  
pstat.close();
 pool.checkin(conn);

Open in new window

so, it close PreparedStatement before checkin from pool.
I make some statistical analysis: this error  
ResultSet =>  oracle.jdbc.driver.OracleResultSetImpl@3611c755 : Mon Feb 14 15:02:54 EET 2011
 PreparedStatement =>  oracle.jdbc.driver.OraclePreparedStatementWrapper@4935b92e : Mon Feb 14 15:02:54 EET 2011
14.02.2011 15:02:55 connect.SelectData run
SEVERE: null
java.sql.SQLException: ORA-00604: ¿¿¿¿¿¿ ¿¿ ¿¿¿¿¿¿¿¿¿¿¿ SQL-¿¿¿¿¿¿ 1
ORA-01000: ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿
ORA-00604: ¿¿¿¿¿¿ ¿¿ ¿¿¿¿¿¿¿¿¿¿¿ SQL-¿¿¿¿¿¿ 1
ORA-01000: ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿
ORA-01000: ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
        at connect.ConnectDB.select(ConnectDB.java:27)
        at connect.SelectData.run(SelectData.java:31)
        at java.util.TimerThread.mainLoop(Timer.java:512)
        at java.util.TimerThread.run(Timer.java:462)
 ResultSet =>  oracle.jdbc.driver.OracleResultSetImpl@4d9cfefb : Mon Feb 14 15:02:57 EET 2011
 PreparedStatement =>  oracle.jdbc.driver.OraclePreparedStatementWrapper@7e28388b : Mon Feb 14 15:02:57 EET 2011

Open in new window

appear exactly every 5 minutes, like timer 52:54; 57:54; 02:54; 07:54 etc
and increase number of cursors from 213 to 324 per each period.
So, every 5 minutes I lost one set of second's data and after everything continue work normally.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34887568
I'm more or a .Net person than a Java person so take this as more of an observation.

If you call "conn = new ConnectDB();", aren't you getting a 'new' connection each time?  I don't see where you close/dispose of the connection object.
0
 

Author Comment

by:gbcbr
ID: 34887697
I suppose that you are right. Maybe really SelectData call ConnectDB each time and leave connection not closed?
0
 

Author Comment

by:gbcbr
ID: 34887864
How to enter into this pool without calling ConnectDB each second?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34887886
Check the ConnectDB code to verify this opens a new connection.  If it does, you will just need a CloseDB function at the end of the code you are writing.
0
 

Author Comment

by:gbcbr
ID: 34887949
public ResultSet select(String sql) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        Connection conn = pool.checkout();

        System.out.println(" Pool =>  " + pool + " : " + new java.util.Date());
        System.out.println(" Conn =>  " + conn + " : " + new java.util.Date());
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        System.out.println(" ResultSet =>  " + rs + " : " + new java.util.Date());
        pool.checkin(conn);
        return rs;


    }

Open in new window

CLEANUP : Available Connections : 4
          switch on the Timer
CLEANUP : Available Connections : 4
=>
 Pool =>  connect.ConnectionPool@16e9b4bb : Mon Feb 14 16:12:16 EET 2011
 Conn =>  oracle.jdbc.driver.T4CConnection@2f93c0cf : Mon Feb 14 16:12:16 EET 2011
 ResultSet =>  oracle.jdbc.driver.OracleResultSetImpl@5aacc14 : Mon Feb 14 16:12:16 EET 2011
 PreparedStatement =>  oracle.jdbc.driver.OraclePreparedStatementWrapper@63b5a40a : Mon Feb 14 16:12:16 EET 2011
 Pool =>  connect.ConnectionPool@16e9b4bb : Mon Feb 14 16:12:17 EET 2011
 Conn =>  oracle.jdbc.driver.T4CConnection@2f93c0cf : Mon Feb 14 16:12:17 EET 2011
 ResultSet =>  oracle.jdbc.driver.OracleResultSetImpl@2a24bfaa : Mon Feb 14 16:12:17 EET 2011
 PreparedStatement =>  oracle.jdbc.driver.OraclePreparedStatementWrapper@52a34783 : Mon Feb 14 16:12:17 EET 2011
 Pool =>  connect.ConnectionPool@16e9b4bb : Mon Feb 14 16:12:18 EET 2011
 Conn =>  oracle.jdbc.driver.T4CConnection@2f93c0cf : Mon Feb 14 16:12:18 EET 2011
 ResultSet =>  oracle.jdbc.driver.OracleResultSetImpl@5dac13d7 : Mon Feb 14 16:12:18 EET 2011
 PreparedStatement =>  oracle.jdbc.driver.OraclePreparedStatementWrapper@3d89acb5 : Mon Feb 14 16:12:18 EET 2011
 Pool =>  connect.ConnectionPool@16e9b4bb : Mon Feb 14 16:12:19 EET 2011
 Conn =>  oracle.jdbc.driver.T4CConnection@2f93c0cf : Mon Feb 14 16:12:19 EET 2011
 ResultSet =>  oracle.jdbc.driver.OracleResultSetImpl@19a9bea3 : Mon Feb 14 16:12:19 EET 2011
 PreparedStatement =>  oracle.jdbc.driver.OraclePreparedStatementWrapper@6dcd2197 : Mon Feb 14 16:12:19 EET 2011
 Pool =>  connect.ConnectionPool@16e9b4bb : Mon Feb 14 16:12:20 EET 2011
 Conn =>  oracle.jdbc.driver.T4CConnection@2f93c0cf : Mon Feb 14 16:12:20 EET 2011
 ResultSet =>  oracle.jdbc.driver.OracleResultSetImpl@365878d2 : Mon Feb 14 16:12:20 EET 2011
 PreparedStatement =>  oracle.jdbc.driver.OraclePreparedStatementWrapper@5b5b55bc : Mon Feb 14 16:12:20 EET 2011

Open in new window

Look like it use the same connection, probably this is not the problem root.
0
 
LVL 26

Assisted Solution

by:ksivananth
ksivananth earned 1600 total points
ID: 34888177
try changing

>>} catch (Exception ex) {
try{
  if ( rs != null ) rs.close() ;
}catch( SQLException sqle ){
            Logger.getLogger(SelectData.class.getName()).log(Level.SEVERE,
                    "Error while closing resultset", ex);
}
            Logger.getLogger(SelectData.class.getName()).log(Level.SEVERE,
                    null, ex);
        }
>>

to

} catch (Exception ex) {
            Logger.getLogger(SelectData.class.getName()).log(Level.SEVERE,
                    null, ex);
        }finally{
try{
  if ( rs != null ) rs.close() ;
}catch( SQLException sqle ){
            Logger.getLogger(SelectData.class.getName()).log(Level.SEVERE,
                    "Error while closing resultset", ex);
}
}
0
 

Author Comment

by:gbcbr
ID: 34888566
This also show no error, but I make some other test and I suppose that rs.close(); doesn't work!
} else if (s.equals("CHF/JPY")) {

                    timest[9] = ts;
                    openBid[9] = bpx;
                    openAsk[9] = apx;

                    equals(rs);

                    aBot.algoBot(openBid, openAsk, timest);

                } else {
                }
            }

            System.out.println(" ResultSet 1 =>  " + rs + " : " + new java.util.Date());
            
            rs.close();
            System.out.println(" ResultSet 2 =>  " + rs + " : " + new java.util.Date());

        } catch (Exception ex) {
            Logger.getLogger(SelectData.class.getName()).log(Level.SEVERE,
                    null, ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                    System.out.println(" ResultSet 3 =>  " + rs + " : " + new java.util.Date());
                }
            } catch (SQLException sqle) {
                Logger.getLogger(SelectData.class.getName()).log(Level.SEVERE,
                        "Error while closing resultset", sqle);
            }
        }

Open in new window

run:
CLEANUP : Available Connections : 4
          switch on the Timer
=>
CLEANUP : Available Connections : 4
 Pool =>  connect.ConnectionPool@46dab859 : Mon Feb 14 17:31:09 EET 2011
 Conn =>  oracle.jdbc.driver.T4CConnection@64633e5e : Mon Feb 14 17:31:09 EET 2011
 ResultSet =>  oracle.jdbc.driver.OracleResultSetImpl@71a2f5b1 : Mon Feb 14 17:31:09 EET 2011
 PreparedStatement =>  oracle.jdbc.driver.OraclePreparedStatementWrapper@16fbcb70 : Mon Feb 14 17:31:09 EET 2011
 ResultSet 1 =>  oracle.jdbc.driver.OracleResultSetImpl@71a2f5b1 : Mon Feb 14 17:31:09 EET 2011
 ResultSet 2 =>  oracle.jdbc.driver.OracleResultSetImpl@71a2f5b1 : Mon Feb 14 17:31:09 EET 2011
 ResultSet 3 =>  oracle.jdbc.driver.OracleResultSetImpl@71a2f5b1 : Mon Feb 14 17:31:09 EET 2011
 Pool =>  connect.ConnectionPool@46dab859 : Mon Feb 14 17:31:10 EET 2011
 Conn =>  oracle.jdbc.driver.T4CConnection@64633e5e : Mon Feb 14 17:31:10 EET 2011
 ResultSet =>  oracle.jdbc.driver.OracleResultSetImpl@6ce931d9 : Mon Feb 14 17:31:10 EET 2011
 PreparedStatement =>  oracle.jdbc.driver.OraclePreparedStatementWrapper@41c7d56b : Mon Feb 14 17:31:10 EET 2011
 ResultSet 1 =>  oracle.jdbc.driver.OracleResultSetImpl@6ce931d9 : Mon Feb 14 17:31:10 EET 2011
 ResultSet 2 =>  oracle.jdbc.driver.OracleResultSetImpl@6ce931d9 : Mon Feb 14 17:31:10 EET 2011
 ResultSet 3 =>  oracle.jdbc.driver.OracleResultSetImpl@6ce931d9 : Mon Feb 14 17:31:10 EET 2011
 Pool =>  connect.ConnectionPool@46dab859 : Mon Feb 14 17:31:11 EET 2011
 Conn =>  oracle.jdbc.driver.T4CConnection@64633e5e : Mon Feb 14 17:31:11 EET 2011
 ResultSet =>  oracle.jdbc.driver.OracleResultSetImpl@268c858a : Mon Feb 14 17:31:11 EET 2011
 PreparedStatement =>  oracle.jdbc.driver.OraclePreparedStatementWrapper@66f9104a : Mon Feb 14 17:31:11 EET 2011
 ResultSet 1 =>  oracle.jdbc.driver.OracleResultSetImpl@268c858a : Mon Feb 14 17:31:11 EET 2011
 ResultSet 2 =>  oracle.jdbc.driver.OracleResultSetImpl@268c858a : Mon Feb 14 17:31:11 EET 2011
 ResultSet 3 =>  oracle.jdbc.driver.OracleResultSetImpl@268c858a : Mon Feb 14 17:31:11 EET 2011

Open in new window

As I understand after rs.close(); and especially after finally rs has to be null.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34888616
I'm lost now.  The latest code you posted doesn't show "conn = new ConnectDB();" so I'm not sure what code you are running now.

Does the logging you are showing from loops within the code or completely separate executions from separate processes?
0
 

Author Comment

by:gbcbr
ID: 34888648
Latest snippet is the end of SelectData class
public class SelectData extends TimerTask {

    private ConnectDB conn;
    AlgoBot aBot;
    private ConnectionPool pool = null;

    public SelectData() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, FieldNotFound, NotDefinedException, Exception {

        conn = new ConnectDB();
        aBot = new AlgoBot();
    }
    String[] symbol = new String[10];
    double[] openBid = new double[10];
    double[] openAsk = new double[10];
    Timestamp[] timest = new Timestamp[10];

    public void run() {

        ResultSet rs = null;

        try {
            rs =
                    conn.select("SELECT * FROM EURUSD where timest = ( select max( timest ) from EURUSD ) and rownum = 1 union all "
                    + "SELECT * FROM EURCHF where timest = ( select max( timest ) from EURCHF ) and rownum = 1 union all "
                    + "SELECT * FROM EURGBP where timest = ( select max( timest ) from EURGBP ) and rownum = 1 union all "
                    + "SELECT * FROM USDCHF where timest = ( select max( timest ) from USDCHF ) and rownum = 1 union all "
                    + "SELECT * FROM GBPUSD where timest = ( select max( timest ) from GBPUSD ) and rownum = 1 union all "
                    + "SELECT * FROM GBPCHF where timest = ( select max( timest ) from GBPCHF ) and rownum = 1 union all "
                    + "SELECT * FROM EURJPY where timest = ( select max( timest ) from EURJPY ) and rownum = 1 union all "
                    + "SELECT * FROM USDJPY where timest = ( select max( timest ) from USDJPY ) and rownum = 1 union all "
                    + "SELECT * FROM GBPJPY where timest = ( select max( timest ) from GBPJPY ) and rownum = 1 union all "
                    + "SELECT * FROM CHFJPY where timest = ( select max( timest ) from CHFJPY ) and rownum = 1");

            while (rs.next()) {

                String s = rs.getString("symbol");
                Timestamp ts = rs.getTimestamp("TIMEST");
                double bpx = rs.getDouble("BIDPX");
                double apx = rs.getDouble("ASKPX");

                if (s.equals("EUR/USD")) {

                    timest[0] = ts;
                    openBid[0] = bpx;
                    openAsk[0] = apx;

                } else if (s.equals("EUR/CHF")) {

                    timest[1] = ts;
                    openBid[1] = bpx;
                    openAsk[1] = apx;

                } else if (s.equals("EUR/GBP")) {

                    timest[2] = ts;
                    openBid[2] = bpx;
                    openAsk[2] = apx;

                } else if (s.equals("USD/CHF")) {

                    timest[3] = ts;
                    openBid[3] = bpx;
                    openAsk[3] = apx;

                } else if (s.equals("GBP/USD")) {

                    timest[4] = ts;
                    openBid[4] = bpx;
                    openAsk[4] = apx;

                } else if (s.equals("GBP/CHF")) {

                    timest[5] = ts;
                    openBid[5] = bpx;
                    openAsk[5] = apx;

                } else if (s.equals("EUR/JPY")) {

                    timest[6] = ts;
                    openBid[6] = bpx;
                    openAsk[6] = apx;

                } else if (s.equals("USD/JPY")) {

                    timest[7] = ts;
                    openBid[7] = bpx;
                    openAsk[7] = apx;

                } else if (s.equals("GBP/JPY")) {

                    timest[8] = ts;
                    openBid[8] = bpx;
                    openAsk[8] = apx;

                } else if (s.equals("CHF/JPY")) {

                    timest[9] = ts;
                    openBid[9] = bpx;
                    openAsk[9] = apx;

                    equals(rs);

                    aBot.algoBot(openBid, openAsk, timest);

                } else {
                }
            }

            System.out.println(" ResultSet 1 =>  " + rs + " : " + new java.util.Date());
            
            rs.close();
            System.out.println(" ResultSet 2 =>  " + rs + " : " + new java.util.Date());

        } catch (Exception ex) {
            Logger.getLogger(SelectData.class.getName()).log(Level.SEVERE,
                    null, ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                    System.out.println(" ResultSet 3 =>  " + rs + " : " + new java.util.Date());
                }
            } catch (SQLException sqle) {
                Logger.getLogger(SelectData.class.getName()).log(Level.SEVERE,
                        "Error while closing resultset", sqle);
            }
        }

Open in new window

and this is ConnectDB class
public class ConnectDB {

    Statement statement;
    ResultSet rs;
    PreparedStatement ps;
    double[] outY = new double[10];
    Timestamp[] timest = new Timestamp[10];
    private ConnectionPool pool = null;

    public ConnectDB() throws SQLException {
        pool = new ConnectionPool("jdbc:oracle:thin:@10.1.1.7:1521:dbfx", "xx", "xx");
    }

    public ResultSet select(String sql) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        Connection conn = pool.checkout();

        System.out.println(" Pool =>  " + pool + " : " + new java.util.Date());
        System.out.println(" Conn =>  " + conn + " : " + new java.util.Date());
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        System.out.println(" ResultSet =>  " + rs + " : " + new java.util.Date());
        pool.checkin(conn);
        return rs;


    }

    public void insert(double[] outY, Timestamp[] timest) throws SQLException {

        Connection conn = pool.checkout();
        PreparedStatement pstat = conn.prepareStatement("INSERT INTO RBF_DATA (OUTY0, OUTY1, OUTY2, OUTY3, OUTY4, OUTY5, OUTY6, OUTY7, OUTY8, OUTY9, TIMEST) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
        for (int i = 0; i <= 9; i++) {
            pstat.setFloat(i + 1, (float) outY[i]);
        }
        pstat.setTimestamp(11, timest[0]);
        ResultSet rset = pstat.executeQuery();
        System.out.println(" PreparedStatement =>  " + pstat + " : " + new java.util.Date());
        pstat.close();
        pool.checkin(conn);

    }
}

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34888715
Thanks for all that.

Nothing is jumping out at me.  I see "CLEANUP : Available Connections : 4"

Is this the same before and after you run this code?
0
 

Author Comment

by:gbcbr
ID: 34888763
Yes, now pool has 4 connections inside, from the beginning it always shows 4 and after it shows less up to 0 and permanently make cleaning unused connection. Before it was 2 but I increase up to 4 because I suppose that 2 is not enough for this traffic.
0
 

Author Comment

by:gbcbr
ID: 34888777
But why after rs.close(); I still have rs alive?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34888789
If you have 4 before this code and 4 after this code, what makes you think it is this code that is using up all the connections?

I also don't see a commit after that insert.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34888802
>>But why after rs.close(); I still have rs alive?

Strictly from a .Net perspective (remember, I'm not a Java person):  You've closed it but the object still exists.  In .Net there is a dispose method that actually removes the object.
0
 

Author Comment

by:gbcbr
ID: 34888856
How it possible
} finally {
            try {
                if (rs != null) {
                    rs.close();
                    System.out.println(" ResultSet 3 =>  " + rs + " : " + new java.util.Date());
                }
            } catch (SQLException sqle) {
                Logger.getLogger(SelectData.class.getName()).log(Level.SEVERE,
                        "Error while closing resultset", sqle);
            }

Open in new window

ResultSet 3 =>  oracle.jdbc.driver.OracleResultSetImpl@455dd32a : Mon Feb 14 17:31:14 EET 2011 means rs != null,
but no exception message appear.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 total points
ID: 34888941
I'll have to step aside and let Java programmers help you here.  From the docs, the close should immediately free up the objects:

http://download.oracle.com/javase/1.4.2/docs/api/java/sql/ResultSet.html
0
 

Author Comment

by:gbcbr
ID: 34889019
from this link
>> java.sql
Interface ResultSet
A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results. <<

So, rs doesn't need to be closed in force, because as soon new rs will come it will close previous automatically.
But why mine doesn't close?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34889046
>>But why mine doesn't close?

Sorry.  I can't help since I'm not a Java person.  Hopefully one of the other Experts here can continue helping.

You might also want to contact Oracle Support to see if they can help.
0
 

Author Comment

by:gbcbr
ID: 34889119
>> ksivananth
Generally question solved, data recording into DB, not as I like, but generally workable. Thank you.
>> slightwv
Thank you for your time and help. Negative result is also result. I found for me many new ideas.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses
Course of the Month9 days, 21 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question