every second selecting data from table

I have a table EURUSD_1C in Oracle 11g which has every second rows with data.

CREATE TABLE "LIONFX"."EURUSD_1C"
  (
    "SYMBOL" VARCHAR2(20 BYTE),
    "BIDPX" FLOAT(126),
    "ASKPX" FLOAT(126),
    "TIMEST" TIMESTAMP (6) NOT NULL ENABLE,

I try to get this every second data with two following classes:
 
public class ConnectDB {

    String databaseurl = "jdbc:oracle:thin:@10.1.1.7:1521:orcl";
    String user = "--";
    String password = "--";
    String driverName = "oracle.jdbc.OracleDriver";

    public Connection init() {
        
        Connection conn = null;
        try {
            Class.forName(driverName).newInstance();
            conn = (Connection) DriverManager.getConnection(databaseurl, user, password);
        } catch (InstantiationException e) {
        } catch (IllegalAccessException e) {
        } catch (ClassNotFoundException e) {
        } catch (SQLException e) {
        }
        return conn;
    }

    public ResultSet select(String sql) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {

        Class.forName(driverName).newInstance();
        Connection conn = (Connection) DriverManager.getConnection(databaseurl, user, password);
        Statement stat = (Statement) conn.createStatement(
        ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_UPDATABLE);

        ResultSet rs = stat.executeQuery(sql);
        while (rs.next())
        System.out.println("select sql" + "  new" +  sql);
        return rs;
    }

    public void destroy(Connection con) {
        try {
            con.close();
        } catch (SQLException e) {
        }
    }
}

Open in new window

public class Listener_1C {

    ConnectDB conn = new ConnectDB();
    public static Listener_1C lsn_1C;
    String sql = null;

     public void listener_1C(ITransportable message) throws ClassNotFoundException,
                                                            SQLException,
                                                            InstantiationException,
                                                            IllegalAccessException,
                                                            FieldNotFound,
                                                            NotDefinedException,
                                                            Exception {
    
    sql  = " SELECT FROM EURUSD_1C " + "(SYMBOL, BIDPX, ASKPX, TIMEST)";
    
    conn.select(sql);
}

public static void main(String[] args)
{
   lsn_1C = new Listener_1C();


        System.out.println("DB listener started");
}
}

Open in new window

But I have output only - DB listener started

Please advice
gbcbrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CEHJCommented:
Instead of

>>conn.select(sql);

try
conn.init();
conn.select(sql);

Open in new window

gbcbrAuthor Commented:
DB listener started
BUILD SUCCESSFUL (total time: 0 seconds)
CEHJCommented:
You're ignoring all exceptions, that's why you're not seeing any output - don't ;) printStackTrace throughout
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

gbcbrAuthor Commented:
You mean:
try {
    sql  = " SELECT FROM EURUSD_1C " + "(SYMBOL, BIDPX, ASKPX, TIMEST)";
    conn.init();
    conn.select(sql);

    } catch (Exception e) {
         }
CEHJCommented:
All over
gbcbrAuthor Commented:
public void listener_1C(ITransportable message)

                                                     throws ClassNotFoundException,
                                                            SQLException,
                                                            InstantiationException,
                                                            IllegalAccessException,
                                                            FieldNotFound,
                                                            NotDefinedException,
                                                            Exception {
         try {

    sql  = " SELECT FROM EURUSD_1C " + "(SYMBOL, BIDPX, ASKPX, TIMEST)";
    conn.init();
    conn.select(sql);

         }
         catch (ClassNotFoundException ex) {
         }
         catch (SQLException ex) {
         }
         catch (InstantiationException ex) {
         }
         catch (IllegalAccessException ex) {
         }
         catch (FieldNotFound ex) {
         }
         catch (NotDefinedException ex) {
         }
         catch (Exception e) {
         }
}

but for FieldNotFound and NotDefinedException I have error that these ex never thrown in body of correspondent try statement;
CEHJCommented:
You need to read up on exceptions:

http://www.javaworld.com/javaworld/jw-07-1998/jw-07-exceptions.html

One of the worst things you can do is to ignore exceptions - that's why you had such a mysterious result before
gbcbrAuthor Commented:
OK, in public ResultSet select(String sql) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException
I have 4 exceptions
In public void listener_1C(ITransportable message)
                                                        {
         try {

    sql  = " SELECT FROM EURUSD_1C " + "(SYMBOL, BIDPX, ASKPX, TIMEST)";
    conn.init();
    conn.select(sql);

         }
         catch (ClassNotFoundException ex) {
         }
         catch (SQLException ex) {
         }
         catch (InstantiationException ex) {
         }
         catch (IllegalAccessException ex) {
         }
}

But result still the same, and also it's complete the job, but I need to get reading from table every second.

DB listener started
BUILD SUCCESSFUL (total time: 0 seconds)
CEHJCommented:
>>I have 4 exceptions

You have 4 exception *handlers*, and every one of them is ignoring the exception
gbcbrAuthor Commented:
But when I try ti catch them here  public ResultSet select(String sql) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
      try {
        Class.forName(driverName).newInstance();
        Connection conn = (Connection) DriverManager.getConnection(databaseurl, user, password);
        Statement stat = (Statement) conn.createStatement(
        ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = stat.executeQuery(sql);
        while (rs.next())
        System.out.println("select sql" + "  new" +  sql);

        return rs;
       
        } catch (SQLException e) {
        } catch (InstantiationException e) {
        } catch (IllegalAccessException e) {
        } catch (ClassNotFoundException e) {
        }
    }
I have error: missing return statement;

When I put them before return it's another error: can't find variable rs;
CEHJCommented:
>>public void listener_1C(ITransportable message)

is the method i was talking about, not the select method
gbcbrAuthor Commented:
Yes, as I understood, public ResultSet select(String sql) has 4 throws, which I catch in public void listener_1C(ITransportable message);
I even adjust catches according to throws, really I don't understand where is mistake?
gbcbrAuthor Commented:
The problem not in exceptions.
I try to start code:

public void listener_1C(ITransportable message)
                                                                                                      {
         try {

        System.out.println("Start SQL query");

    sql  = " SELECT FROM EURUSD_1C " + "(SYMBOL, BIDPX, ASKPX, TIMEST)";
    conn.init();
    conn.select(sql);

         }
         catch (SQLException exception) {
             System.out.println("SQL ex");
         }
         catch (ClassNotFoundException ex) {
             System.out.println("Class ex");
         }
         catch (IllegalAccessException ex) {
             System.out.println("Access ex");
         }
         catch (InstantiationException ex) {
             System.out.println("Inst ex");
         }

}

result the same:
DB listener started
BUILD SUCCESSFUL (total time: 0 seconds)

process even don't pass try {

please advice
CEHJCommented:
Please show all and *any* output you're getting
Mick BarryJava DeveloperCommented:
you don't need all those try/catch's, they just result in calling class not knowing about failures

you don't need to call init() eith, so get rid of that

you're problem is you don't even call that method, should be:

lsn_1C = new Listener_1C();
lsn_1C.listener_1C(null);

make the changes to your original code
gbcbrAuthor Commented:
Thank you very much, very clear. I pass this stage, but only once.
How to get this data permanently with one second interval, because in EURUSD_1C every second created new row with new data?
gbcbrAuthor Commented:
Unfortunately I was happy too early.
I pass execution up to query but after I have errorStart SQL query
Exception in thread "main" java.sql.SQLException: Executable operator SQL can't be empty or undefined.
        at oracle.jdbc.driver.OracleSql.initialize(OracleSql.java:113)
        at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1460)
        at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:389)
        at lionfx.fxcm.ConnectDB.select(ConnectDB.java:62)
        at lionfx.fxcm.Listener_1C.listener_1C(Listener_1C.java:69)
        at lionfx.fxcm.Listener_1C.main(Listener_1C.java:111)
Java Result: 1
BUILD SUCCESSFUL (total time: 1 second)

at lionfx.fxcm.ConnectDB.select(ConnectDB.java:62)
    ResultSet rs = stat.executeQuery(eurusd);

 at lionfx.fxcm.Listener_1C.listener_1C(Listener_1C.java:69)
 conn.select(eurusd);

at lionfx.fxcm.Listener_1C.main(Listener_1C.java:111)
lsn_1C.listener_1C(null);
String eurusd = null;

    public void listener_1C(ITransportable message)
            throws
            ClassNotFoundException,
            SQLException,
            InstantiationException,
            IllegalAccessException {
        System.out.println("Start SQL query");

        conn.select(eurusd);

       eurusd = " SELECT *  FROM EURUSD_1C ";
    }

    public static void main(String[] args) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {

        lsn_1C = new Listener_1C();
        lsn_1C.listener_1C(null);

        System.out.println("DB listener started");

    }

------------------------------------

public ResultSet select(String eurusd) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {

        Class.forName(driverName).newInstance();
        Connection conn = (Connection) DriverManager.getConnection(databaseurl, user, password);
        Statement stat = (Statement) conn.createStatement(
        ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = stat.executeQuery(eurusd);
        while (rs.next())
        System.out.println("select EUR/USD" + "  new  " +  eurusd);

        return rs;

        }

Open in new window

CEHJCommented:
That's good - you're now getting exceptions:

>>
      conn.select(eurusd);

       eurusd = " SELECT *  FROM EURUSD_1C ";
>>

Switch the order of the above lines
gbcbrAuthor Commented:
>>
   Switch the order of the above lines
>>

Please explain what you mean?
CEHJCommented:

       eurusd = " SELECT *  FROM EURUSD_1C ";
      conn.select(eurusd);

Open in new window

gbcbrAuthor Commented:
Now better, but no data at all, only messages.

run:
DB listener started
Start SQL query
Connect to DB
BUILD SUCCESSFUL (total time: 1 second)
System.out.println("Start SQL query");

        eurusd = " SELECT *  FROM EURUSD_1C ";
        
        System.out.println("Connect to DB");

        conn.select(eurusd);

Open in new window

CEHJCommented:
Can you place the following as the first line of the select method to make sure it's called. Let me know what's printed
System.out.println("Starting select at " + new java.util.Date());

Open in new window

gbcbrAuthor Commented:
DB listener started
Starting select at Thu Oct 21 21:03:15 EEST 2010
Start SQL query
Connect to DB
BUILD SUCCESSFUL (total time: 1 second)
CEHJCommented:
That's good. Could you now please post your current select method?
gbcbrAuthor Commented:

String eurusd = null;

    public void listener_1C(ITransportable message)
            throws
            ClassNotFoundException,
            SQLException,
            InstantiationException,
            IllegalAccessException {

        System.out.println("Starting select at " + new java.util.Date());

        System.out.println("Start SQL query");

        eurusd = " SELECT *  FROM EURUSD_1C ";
        
        System.out.println("Connect to DB");

        conn.select(eurusd);
    }

Open in new window

CEHJCommented:
No, that's the listener_1C method. I want to see the select() method
gbcbrAuthor Commented:

public ResultSet select(String eurusd) throws SQLException, ClassNotFoundException,      IllegalAccessException, InstantiationException {

        Class.forName(driverName).newInstance();
        Connection conn = (Connection) DriverManager.getConnection(databaseurl, user, password);
        Statement stat = (Statement) conn.createStatement(
        ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = stat.executeQuery(eurusd);
        while (rs.next())
        System.out.println("select EUR/USD" + "  new  " +  eurusd);

        return rs;

        }

Open in new window

CEHJCommented:
Everything's changed. You don't want to putting the connect code in there too. Can you please post the complete code?
gbcbrAuthor Commented:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author gbcbr
 */
public class ConnectDB {

    String databaseurl = "jdbc:oracle:thin:@10.1.1.7:1521:orcl";
    String user = "LIONFX";
    String password = "";
    String driverName = "oracle.jdbc.OracleDriver";

    public Connection init() {
        
        Connection conn = null;
        try {
            Class.forName(driverName).newInstance();
            conn = (Connection) DriverManager.getConnection(databaseurl, user, password);
        } catch (InstantiationException e) {
        } catch (IllegalAccessException e) {
        } catch (ClassNotFoundException e) {
        } catch (SQLException e) {
        }
        return conn;
    }

    public void insert(String sql) throws SQLException {
        try {
            Class.forName(driverName).newInstance();
            Connection conn = (Connection) DriverManager.getConnection(databaseurl, user, password);
            Statement statement = (Statement) conn.createStatement();
            int rs = statement.executeUpdate(sql);
            System.out.println("insert sql" + "  new" +  sql);
            statement.close();

        } catch (InstantiationException e) {
        } catch (IllegalAccessException e) {
        } catch (ClassNotFoundException e) {
        }
    }

    public ResultSet select(String eurusd) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {

        Class.forName(driverName).newInstance();
        Connection conn = (Connection) DriverManager.getConnection(databaseurl, user, password);
        Statement stat = (Statement) conn.createStatement(
        ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = stat.executeQuery(eurusd);
        while (rs.next())
        System.out.println("select EUR/USD" + "  new  " +  eurusd);

        return rs;

        }
    
    public void destroy(Connection con) {
        try {
            con.close();
        } catch (SQLException e) {
        }
    }
}

Open in new window

gbcbrAuthor Commented:
This code load stream data to my Oracle 11g DB, I sort it, clean and finally it comes to the tables _1C from which I have to take them and send for analysis.
CEHJCommented:
We need to see the code with the main method in it too
Mick BarryJava DeveloperCommented:
>         eurusd = " SELECT *  FROM EURUSD_1C ";

your selecting from EURUSD_1C, and it appears there is no data in there
check the database for data
Mick BarryJava DeveloperCommented:
> We need to see the code with the main method in it too

its already been posted (and corrected)
CEHJCommented:
>>your selecting from EURUSD_1C, and it appears there is no data in there

Even if that were the case, that state of affairs would not produce the output you're getting
CEHJCommented:
Try the following. Use a file called connection.properties when you need to (not right away)
import java.io.IOException;
import java.io.InputStream;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.Properties;


/**
 *
 * @author gbcbr
 */
public class ConnectDB {
    private String databaseurl = "jdbc:oracle:thin:@10.1.1.7:1521:orcl";
    private String user = "LIONFX";
    private String password = "";
    private String driverName = "oracle.jdbc.OracleDriver";

    public Connection init() {
        InputStream in = null;
        Connection conn = null;

        try {
            Class.forName(driverName).newInstance();

            in = ConnectDB.class.getResourceAsStream("/connection.properties");

            if (in != null) {
                Properties props = new Properties();
                props.load(in);
                conn = (Connection) DriverManager.getConnection(props.getProperty(
                            "url"), props);
            } else {
                conn = (Connection) DriverManager.getConnection(databaseurl,
                        user, password);
            }
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                in.close();
            } catch (Exception e) { /* ignore */
            }
        }

        return conn;
    }

    public void insert(Connection conn, String sql) throws SQLException {
        Statement statement = (Statement) conn.createStatement();
        int rs = statement.executeUpdate(sql);
        System.out.println("insert sql" + "  new" + sql);
        statement.close();
    }

    public ResultSet select(Connection conn, String query)
        throws SQLException {
        Statement statement = (Statement) conn.createStatement();

        return statement.executeQuery(query);
    }

    public void print(ResultSet rs) throws SQLException {
        String sep = "";

        // Get the column names
        ResultSetMetaData metaData = rs.getMetaData();
        int numberOfColumns = metaData.getColumnCount();

        for (int column = 0; column < numberOfColumns; column++) {
            System.out.print(sep);
            System.out.print(metaData.getColumnLabel(column + 1));
            sep = ",";
        }

        System.out.println();

        // Get all rows.
        while (rs.next()) {
            sep = "";

            for (int i = 1; i <= numberOfColumns; i++) {
                System.out.print(sep);
                System.out.print("" + rs.getObject(i));
                sep = ",";
            }

            System.out.println();
        }
    }

    public void destroy(Connection con) {
        try {
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        Connection conn = null;
        ResultSet rs = null;

        try {
            ConnectDB cdb = new ConnectDB();
            conn = cdb.init();
            rs = cdb.select(conn, "SELECT * FROM EURUSD_1C");

            cdb.print(rs);

            try {
                ;
            } finally {
                try {
                    rs.close();
                } catch (Exception e) { /* ignore */
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                conn.close();
            } catch (Exception e) { /* ignore */
            }
        }
    }
}

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gbcbrAuthor Commented:
Perfect job:
run:
SYMBOL,BIDPX,ASKPX,TIMEST
EUR/USD,1.39395,1.39412,2010-10-22 13:34:32.0
EUR/USD,1.39398,1.39415,2010-10-22 13:34:33.0
EUR/USD,1.39397,1.39414,2010-10-22 13:34:34.0
EUR/USD,1.39398,1.39415,2010-10-22 13:34:35.0
EUR/USD,1.39396,1.39413,2010-10-22 13:34:36.0
EUR/USD,1.39402,1.39419,2010-10-22 13:34:37.0
EUR/USD,1.39405,1.39422,2010-10-22 13:34:38.0
EUR/USD,1.39403,1.3942,2010-10-22 13:34:39.0
EUR/USD,1.39403,1.3942,2010-10-22 13:34:40.0
BUILD SUCCESSFUL (total time: 4 seconds)

Now we need to make it live, updatable every second, and generally we need only last row every second.
Don't finish build at the and of table.
CEHJCommented:
:-)
gbcbrAuthor Commented:
@objects
This question was multitasks, I think I have to split it in few separate questions, so when I come back for review solutions, I have separate solution for each task. On this stage at least we can see data from table. Next two questions will be about permanent getting actual data and binding these data to Java variables.

Thank you for attention.
Mick BarryJava DeveloperCommented:
I realize that, but I pointed out your problems in my earlier comment which you ignored and instead accepted a comment (that had copied and included what I had already suggested).

The code you are using is also now far more complicated than it needs to be.

Would appreciate it if you could reopen the question and split the points.
Let me know if you need any assistance
gbcbrAuthor Commented:
@objects
I marked your comment
<<< lsn_1C = new Listener_1C();
lsn_1C.listener_1C(null); >>> 
as a very helpful, but it didn't resolve the problem with showing data.
I have no problem to reopen question, but I don't know how to do it.
Mick BarryJava DeveloperCommented:
> as a very helpful, but it didn't resolve the problem with showing data.

there was more than 1 problem

will get it reopened for you
CEHJCommented:
>>either because you asked for it directly, or because someone else suggested it and it appears you agreed.

Interesting, quomodo, that this is now an acceptable procedure ...
 
gbcbrAuthor Commented:
Thank you everybody for the help
CEHJCommented:
:)
Mick BarryJava DeveloperCommented:
thanks buddy
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.