Solved

DB connection problem

Posted on 2011-02-14
34
440 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
  • 18
  • 8
  • 8
34 Comments
 
LVL 26

Expert Comment

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

Author Comment

by:gbcbr
Comment Utility
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 400 total points
Comment Utility
insert looks fine, I asked you to check that in select!
0
 

Author Comment

by:gbcbr
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
where it is called in SelectData, if don't know, post SelectData
0
 

Author Comment

by:gbcbr
Comment Utility
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 400 total points
Comment Utility
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
Comment Utility
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
Comment Utility
check some other code calling it!
0
 

Author Comment

by:gbcbr
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
I suppose that you are right. Maybe really SelectData call ConnectDB each time and leave connection not closed?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:gbcbr
Comment Utility
How to enter into this pool without calling ConnectDB each second?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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 400 total points
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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
Comment Utility
But why after rs.close(); I still have rs alive?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
>> 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now