Solved

SQL statement must not be empty or undefined

Posted on 2010-11-23
58
781 Views
Last Modified: 2012-08-14
New model of inserting data into DB
Class Main call method init() in ConnectDB class.
After establishing connection with DB, init() calls method insert(sql) in ConnInsert.
Problem is that sql hided by some reasons.
 
public class InsertData {

    ConnInsert aConnInsert;
    public ConnectDB connect;
    public Connection conn;
    MarketDataSnapshot incomingQuote;
    public String sql;
    public Statement statement;

    public void insertData(ITransportable message, boolean printMarketData) throws SQLException,
            ClassNotFoundException,
            InstantiationException,
            IllegalAccessException,
            NotDefinedException,
            FieldNotFound,
            Exception {

        if (message instanceof MarketDataSnapshot) {
            incomingQuote = (MarketDataSnapshot) message;
               String symbol = incomingQuote.getInstrument().getSymbol();

                    System.out.println("=>  insertData  incomingQuote  " + incomingQuote + " : " + new java.util.Date());
                    System.out.println();
                 
                    try {
                        sql =
                                " INSERT INTO STREAM_DATA "
                                + "(SYMBOL, BIDPX, ASKPX, TIMEST) "
                                + "VALUES "
                                + "('"
                                + incomingQuote.getInstrument().getSymbol()
                                + "','"
                                + incomingQuote.getBidOpen()
                                + "','" 
                                + incomingQuote.getAskOpen()
                                + "','"
                                + incomingQuote.getOpenTimestamp()
                                + "')";

                        aConnInsert = new ConnInsert(sql, conn);
                        aConnInsert.insert(sql);

                    System.out.println("=> insertData sql  " + sql + " : " + new java.util.Date());
                    System.out.println();

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

Open in new window

public class ConnInsert {

    public Connection conn;
    public ConnectDB connect;
    public Statement statement;
    public String sql;

    public ConnInsert(String sql, Connection conn ){

        this.sql = sql;
        this.conn = conn;
    }

     public void insert(String sql) throws SQLException {
 
        System.out.println(conn + " : Connected (insert) to DB at : " + new java.util.Date());
        System.out.println();
       
        statement = (Statement) conn.createStatement();
         
        int st = statement.executeUpdate(sql);

        System.out.println("=>  insert sql  " + sql + " : " + new java.util.Date());
                    System.out.println();

        statement.close();
    }
}

Open in new window

output:
 
Started at  : Tue Nov 23 16:29:36 EET 2010
=>
oracle.jdbc.driver.T4CConnection@509f5011 : Connected (init) to DB at : Tue Nov 23 16:29:37 EET 2010

oracle.jdbc.driver.T4CConnection@509f5011 : Connected (insert) to DB at : Tue Nov 23 16:29:37 EET 2010

Exception in thread "main" java.sql.SQLException: ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ SQL ¿¿ ¿¿¿¿¿¿ ¿¿¿¿ ¿¿¿¿¿¿ ¿¿¿ ¿¿¿¿¿¿¿¿¿¿¿¿¿¿
        at oracle.jdbc.driver.OracleSql.initialize(OracleSql.java:113)
        at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1805)
        at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1779)
        at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:277)
        at dbfx_data.ConnInsert.insert(ConnInsert.java:35)
        at dbfx_data.ConnectDB.init(ConnectDB.java:33)
        at dbfx_data.Main.main(Main.java:33)
Java Result: 1
BUILD SUCCESSFUL (total time: 2 seconds)

Open in new window

at dbfx_data.ConnInsert.insert(ConnInsert.java:35)=>int st = statement.executeUpdate(sql);

Please advice
0
Comment
Question by:gbcbr
  • 32
  • 25
58 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34197273
this looks like some of the variables that are used to build that sql statement ad-hoc are null, which results in a null value ...

apart from that, you should look up bind variables to also avoid sql injection ...
0
 

Author Comment

by:gbcbr
ID: 34197379
<<<his looks like some of the variables that are used to build that sql statement ad-hoc are null, which results in a null value ...>>>
no, everything OK. When I use the same sql statement in this method:
 
public void insert(String sql) throws SQLException {
        try {
            Class.forName(driverName).newInstance();
            conn = (Connection) DriverManager.getConnection(databaseurl, user, password);
            statement = (Statement) conn.createStatement();
            int st = statement.executeUpdate(sql);
            destroy();
            statement.close();

Open in new window

everything works perfect:
<= EUR/USD,  1.34051,  1.34068,  2010-11-23 15:33:13.0 : EUR/JPY,  111.052,  111.072,  2010-11-23 15:33:12.0 : USD/JPY,  82.838,  82.856,  2010-11-23 15:33:13.0 : Tue Nov 23 17:33:19 EET 2010
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34198668
Please change this to the below and post what is printed just before you get the exception

>>
               aConnInsert.insert(sql);
                System.out.println("=> insertData sql  " + sql + " : " + new java.util.Date());
>>
System.out.println("=> insertData sql  " + sql + " : " + new java.util.Date());

               aConnInsert.insert(sql);

Open in new window

0
 

Author Comment

by:gbcbr
ID: 34198979
=>
          Started at  : Tue Nov 23 20:17:33 EET 2010
=>
oracle.jdbc.driver.T4CConnection@b41b571 : Connected (init) to DB at : Tue Nov 23 20:17:34 EET 2010

oracle.jdbc.driver.T4CConnection@b41b571 : Connected (insert) to DB at : Tue Nov 23 20:17:34 EET 2010

23.11.2010 20:17:34 dbfx_data.Main connectDB
SEVERE: null
java.sql.SQLException: ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ SQL ¿¿ ¿¿¿¿¿¿ ¿¿¿¿ ¿¿¿¿¿¿ ¿¿¿ ¿¿¿¿¿¿¿¿¿¿¿¿¿¿
          dbFX data server 
=>
          dbFX: started logging in
=>
        at oracle.jdbc.driver.OracleSql.initialize(OracleSql.java:113)
        at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1805)
        at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1779)
        at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:277)
        at dbfx_data.ConnInsert.insert(ConnInsert.java:37)
        at dbfx_data.Init.init(Init.java:37)
        at dbfx_data.Main.connectDB(Main.java:38)
        at dbfx_data.Main.main(Main.java:30)
>>> requestTradingSessionStatus = DB_U100D1_C89l1lfVHgjDIJBel8dpw68pgnZjRe8qSwGjTnjjKmT1PJY9AUijQp-9
=>
          dbFX: done logging in at : Tue Nov 23 20:17:42 EET 2010
=>
null : Connected (insert) to DB at : Tue Nov 23 20:17:43 EET 2010

null : Connected (insert) to DB at : Tue Nov 23 20:17:43 EET 2010

null : Connected (insert) to DB at : Tue Nov 23 20:17:43 EET 2010

Open in new window

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34199028
I don't think you did it. For one thing, i don't see the string '"=> insertData sql" in the output
0
 

Author Comment

by:gbcbr
ID: 34199121
Sorry, it's was lower in output

=> insertData sql   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('CAD/CHF','0.9718','0.97235','20101123-18:29:31') : Tue Nov 23 20:29:32 EET 2010

null : Connected (insert) to DB at : Tue Nov 23 20:29:32 EET 2010

=> insertData sql   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/CAD','1.3708','1.37131','20101123-18:29:31') : Tue Nov 23 20:29:32 EET 2010

null : Connected (insert) to DB at : Tue Nov 23 20:29:32 EET 2010

but no data inserted in DB, this is only declaration, because insert has null connection to DB
0
 

Author Comment

by:gbcbr
ID: 34199135
So, this problem is solved and we return to connection problem
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34199160
Yes, looks like you're passing a null Connection to the ConnInsert ctor
0
 

Author Comment

by:gbcbr
ID: 34199174
Fresh full output:
 
run:
          dbFX data started
=>
          Started at  : Tue Nov 23 20:36:53 EET 2010
=>
oracle.jdbc.driver.T4CConnection@39e87719 : Connected (init) to DB at : Tue Nov 23 20:36:53 EET 2010

oracle.jdbc.driver.T4CConnection@39e87719 : Connected (insert) to DB at : Tue Nov 23 20:36:53 EET 2010

23.11.2010 20:36:54 dbfx_data.Main connectDB
SEVERE: null
java.sql.SQLException: ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ SQL ¿¿ ¿¿¿¿¿¿ ¿¿¿¿ ¿¿¿¿¿¿ ¿¿¿ ¿¿¿¿¿¿¿¿¿¿¿¿¿¿
          dbFX data server 
=>
          dbFX: started logging in
=>
        at oracle.jdbc.driver.OracleSql.initialize(OracleSql.java:113)
        at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1805)
        at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1779)
        at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:277)
        at dbfx_data.ConnInsert.insert(ConnInsert.java:37)
        at dbfx_data.Init.init(Init.java:37)
        at dbfx_data.Main.connectDB(Main.java:38)
        at dbfx_data.Main.main(Main.java:30)
>>> requestTradingSessionStatus = DB_U100D1_X0vkNW5ZLiCqP5uJmUgPcSJ3OqouIZq7H3B9M9tgiu5IbcHmHKNauR-9
=>
          dbFX: done logging in at : Tue Nov 23 20:37:05 EET 2010
=>
=> insertData sql   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/GBP','0.84724','0.84752','20101123-18:37:05') : Tue Nov 23 20:37:06 EET 2010

null : Connected (insert) to DB at : Tue Nov 23 20:37:06 EET 2010

=> insertData sql   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('AUD/USD','0.97429','0.97451','20101123-18:37:05') : Tue Nov 23 20:37:06 EET 2010

null : Connected (insert) to DB at : Tue Nov 23 20:37:06 EET 2010

=> insertData sql   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/AUD','1.37484','1.37526','20101123-18:37:05') : Tue Nov 23 20:37:06 EET 2010

null : Connected (insert) to DB at : Tue Nov 23 20:37:06 EET 2010

23.11.2010 20:37:06 dbfx_data.ServerConnect$1 messageArrived
SEVERE: null
java.lang.NullPointerException
        at dbfx_data.ConnInsert.insert(ConnInsert.java:36)
        at dbfx_data.InsertData.insertData(InsertData.java:67)
        at dbfx_data.ServerConnect$1.messageArrived(ServerConnect.java:70)
        at com.fxcm.internal.transport.FXCMGateway.update(FXCMGateway.java:769)
        at com.fxcm.messaging.util.fix.FIXUserSession$BackToUserQueue.run(FIXUserSession.java:607)
        at java.lang.Thread.run(Thread.java:680)
23.11.2010 20:37:06 dbfx_data.ServerConnect$1 messageArrived
SEVERE: null
java.lang.NullPointerException
        at dbfx_data.ConnInsert.insert(ConnInsert.java:36)
        at dbfx_data.InsertData.insertData(InsertData.java:67)
        at dbfx_data.ServerConnect$1.messageArrived(ServerConnect.java:70)
        at com.fxcm.internal.transport.FXCMGateway.update(FXCMGateway.java:769)
        at com.fxcm.messaging.util.fix.FIXUserSession$BackToUserQueue.run(FIXUserSession.java:607)
        at java.lang.Thread.run(Thread.java:680)

Open in new window

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34199183
... and the reason for that is that there's no Connection initialised in InsertData
0
 

Author Comment

by:gbcbr
ID: 34199213
but when I initialize connection in InsertData it's ignore open connection and starts open new for new data incoming, so I come back to initial point.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34199409
>>but when I initialize connection in InsertData

But you don't. That's the problem - you have a null connection
0
 

Author Comment

by:gbcbr
ID: 34199542
no, connection exist, it was created by init and sent to insert
 
oracle.jdbc.driver.T4CConnection@39e87719 : Connected (init) to DB at : Tue Nov 23 20:36:53 EET 2010

oracle.jdbc.driver.T4CConnection@39e87719 : Connected (insert) to DB at : Tue Nov 23 20:36:53 EET 2010

Open in new window

but insert doesn't use it
null : Connected (insert) to DB at : Tue Nov 23 20:37:06 EET 2010

Open in new window

this is the problem, why it doesn't use existing connection
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34199724
InsertData can/will only use its

>>public Connection conn;

and nowhere is that initialised

That should be private btw (although not relevant to the error)
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34199865
You should probably insert the following ctor and use it


public InsertData(Connection conn) {

	this.conn = conn;

    }

Open in new window

0
 

Author Comment

by:gbcbr
ID: 34199870
but init use Connection conn for connecting to DB.
Maybe I wrong, but according to my logic which I use when make this variant of app:
1. I create connection to DB with init,  send it into insert, so it can use it
 
System.out.println(conn + " : Connected (insert) to DB at : " + new java.util.Date());
        System.out.println();
       
        statement = (Statement) conn.createStatement();

Open in new window

and you can see that connection visible into insert at the beginning.
2. Because I don't close connection it has to stay bind to insert and when new data arrive it reuse this connection.
For me is the main question in this procedure, why insert doesn't reuse this connection
 
oracle.jdbc.driver.T4CConnection@39e87719 : Connected (insert) to DB at : Tue Nov 23 20:36:53 EET 2010

Open in new window

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34199889
See my last comment
0
 

Author Comment

by:gbcbr
ID: 34200003
I already use it
public class ConnInsert {

    public Connection conn;
    public Init connect;
    public Statement statement;
    public String sql;

    public ConnInsert(String sql, Connection conn) {

        this.sql = sql;
        this.conn = conn;
    }

    public void insert(String sql) throws SQLException {

        System.out.println(conn + " : Connected (insert) to DB at : " + new java.util.Date());
        System.out.println();
        System.out.println("=>   sql-insert  " + sql + " : " + new java.util.Date());
        System.out.println();

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

        connect = new Init();
        connect.init();


        statement.close();
    }
}

Open in new window

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34200022
ConnInsert is a completely different class. We're talking about InsertData
0
 

Author Comment

by:gbcbr
ID: 34200029
Without it app stops working
0
 

Author Comment

by:gbcbr
ID: 34200042
You suggest create this constructor into InsertData?
I'll try and post the result
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34200082
>>Without it app stops working

That may be the case. It's just irrelevant to the current problem.

>>You suggest create this constructor into InsertData?

Yes - of course it wont make any difference if it's not used properly ;)
0
 

Author Comment

by:gbcbr
ID: 34200113
Nothing changed, the same output
0
 

Author Comment

by:gbcbr
ID: 34200162
public class InsertData {

    ConnInsert aConnInsert;
    public Init connect;
    public Connection conn;
    MarketDataSnapshot incomingQuote;
    public String sql;
    public Statement statement;

    public InsertData(Connection conn) {
	this.conn = conn;
    }

    public void insertData(ITransportable message, boolean printMarketData) throws SQLException,
            ClassNotFoundException,
            InstantiationException,
            IllegalAccessException,
            NotDefinedException,
            FieldNotFound,
            Exception {

        if (message instanceof MarketDataSnapshot) {
            incomingQuote = (MarketDataSnapshot) message;
               String symbol = incomingQuote.getInstrument().getSymbol();
                  try {
                        sql =
                                " INSERT INTO STREAM_DATA "
                                + "(SYMBOL, BIDPX, ASKPX, TIMEST) "
                                + "VALUES "
                                + "('"
                                + incomingQuote.getInstrument().getSymbol()
                                + "','"
                                + incomingQuote.getBidOpen()
                                + "','" 
                                + incomingQuote.getAskOpen()
                                + "','"
                                + incomingQuote.getOpenTimestamp()
                                + "')";


                        aConnInsert = new ConnInsert(sql, conn);

                    System.out.println("=> insertData sql  " + sql + " : " + new java.util.Date());
                    System.out.println();

                       aConnInsert.insert(sql);

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

Open in new window

0
 

Author Comment

by:gbcbr
ID: 34200213
When I comment ctor into ConnInsert, output shows:
oracle.jdbc.driver.T4CConnection@39e87719 : Connected (init) to DB at
null : Connected (insert) to DB at
so this connection invisible for insert and after this app close:
BUILD SUCCESSFUL (total time: 3 seconds)
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34201567
>>Nothing changed, the same output

How and where are you calling the new ctor?
0
 

Author Comment

by:gbcbr
ID: 34203013
I call it from this class:
public class ServerConnect {

    
    MarketDataSnapshot incomingQuote;
    private static String tradingSessionStatusID;
    private static boolean printMarketData = false;
    final IGateway fxcmGateway;
    public static InsertData aInsertData;
    public Connection conn;

    public ServerConnect(String[] args) throws ClassNotFoundException, SQLException,
            InstantiationException,
            IllegalAccessException,
            FieldNotFound, Exception {

        /*
        step 1: get an instance of IGateway from the GatewayFactory
         */

        System.out.println("          dbFX data server ");
        System.out.println("=>");

        fxcmGateway = GatewayFactory.createGateway();

        /*
        step 2: register a generic message listener
        with the gateway, this listener in particular gets
        all messages that are related to the trading platform
        MarketDataSnapshot,OrderSingle,ExecutionReport, etc...
         */

        fxcmGateway.registerGenericMessageListener(new IGenericMessageListener() {

            public void messageArrived(ITransportable message) {

                if (message instanceof MarketDataSnapshot) {
                    try {

                        aInsertData = new InsertData(conn);
                        aInsertData.insertData(message, printMarketData);

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

        /*
        step 3: register a status message listener,
        this listener recieves messages
        pertaining to the status of your current session.
         */

        fxcmGateway.registerStatusMessageListener(new IStatusMessageListener() {

            @Override
            public void messageArrived(ISessionStatus status) {
                String statusMessage = status.getStatusMessage();

                if (statusMessage != null
                        && statusMessage.trim().length() > 0) {
//                    System.out.println(System.currentTimeMillis() + " client: inc status1 msg = " + statusMessage);
                }
            }
        });

        try {

            /*
            step 4: call login on the gateway, this method takes
            an instance of FXCMLoginProperties which takes
            4 parameters: username,password,terminal and server
            or path to a Hosts.xml file which it uses for resolving
            servers. As soon as the login  method executes your
            listeners begin receiving asynch messages from
            the FXCM servers.
             */

            String userName = args[0];
            String password = args[1];
            String terminal = args[2];
            String server = args[3];

            FXCMLoginProperties properties = new FXCMLoginProperties(userName, password, terminal, server, null);

            System.out.println("          dbFX: started logging in");
            System.out.println("=>");

            fxcmGateway.login(properties);

            tradingSessionStatusID = fxcmGateway.requestTradingSessionStatus();
            System.out.println(">>> requestTradingSessionStatus = " + tradingSessionStatusID);

            /*
            step 5: remember to call fxcmGateway.logout(); when you are
            done with your connection and wish to logout
             */

            System.out.println("=>");
            System.out.println("          dbFX: done logging in at" + " : " + new java.util.Date());
            System.out.println("=>");

            printMarketData = !printMarketData;

            BufferedReader in = new BufferedReader(new InputStreamReader(System.in));

            while (true) {
                String str = in.readLine();

                if (str != null) {

                    if ("e".equalsIgnoreCase(str.trim())) {

                        System.out.println("          Stopped at " + new java.util.Date());
                        System.out.println("=>");
                        fxcmGateway.logout();

                        System.exit(0);
                    }
                }
            }
        } catch (Exception e) {
        }
    }
}

Open in new window

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34204106
Yes - that should stop the NPE
0
 

Author Comment

by:gbcbr
ID: 34204192
But it doesn't:
 
run:
          dbFX data started
=>
          Started at  : Wed Nov 24 12:37:17 EET 2010
=>
oracle.jdbc.driver.T4CConnection@509f5011 : Connected (init) to DB at : Wed Nov 24 12:37:18 EET 2010

oracle.jdbc.driver.T4CConnection@509f5011 : Connected (insert) to DB at : Wed Nov 24 12:37:18 EET 2010

=>   sql-insert  null : Wed Nov 24 12:37:18 EET 2010

24.11.2010 12:37:18 dbfx_data.Main connectDB
SEVERE: null
java.sql.SQLException: ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ SQL ¿¿ ¿¿¿¿¿¿ ¿¿¿¿ ¿¿¿¿¿¿ ¿¿¿ ¿¿¿¿¿¿¿¿¿¿¿¿¿¿
          dbFX data server 
=>
          dbFX: started logging in
=>
        at oracle.jdbc.driver.OracleSql.initialize(OracleSql.java:113)
        at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1805)
        at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1779)
        at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:277)
        at dbfx_data.ConnInsert.insert(ConnInsert.java:36)
        at dbfx_data.Init.init(Init.java:37)
        at dbfx_data.Main.connectDB(Main.java:38)
        at dbfx_data.Main.main(Main.java:30)
>>> requestTradingSessionStatus = DB_U100D1_ZpJ6W47tGn2n3IwiMnFdJ7yhVi7ngClIAiBzPu06XQ44mHsCmKXkH6-9
=>
          dbFX: done logging in at : Wed Nov 24 12:37:26 EET 2010
=>
=> insertData sql   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/NZD','1.75295','1.75375','20101124-10:37:26') : Wed Nov 24 12:37:27 EET 2010

null : Connected (insert) to DB at : Wed Nov 24 12:37:27 EET 2010

=>   sql-insert   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/NZD','1.75295','1.75375','20101124-10:37:26') : Wed Nov 24 12:37:27 EET 2010
24.11.2010 12:37:27 dbfx_data.ServerConnect$1 messageArrived
SEVERE: null
java.lang.NullPointerException
        at dbfx_data.ConnInsert.insert(ConnInsert.java:35)
        at dbfx_data.InsertData.insertData(InsertData.java:62)
        at dbfx_data.ServerConnect$1.messageArrived(ServerConnect.java:72)
        at com.fxcm.internal.transport.FXCMGateway.update(FXCMGateway.java:769)

=> insertData sql   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('GBP/JPY','131.195','131.228','20101124-10:37:26') : Wed Nov 24 12:37:27 EET 2010

        at com.fxcm.messaging.util.fix.FIXUserSession$BackToUserQueue.run(FIXUserSession.java:607)
null : Connected (insert) to DB at : Wed Nov 24 12:37:27 EET 2010

=>   sql-insert   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('GBP/JPY','131.195','131.228','20101124-10:37:26') : Wed Nov 24 12:37:27 EET 2010

=> insertData sql   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('CAD/JPY','81.59','81.618','20101124-10:37:26') : Wed Nov 24 12:37:27 EET 2010

        at java.lang.Thread.run(Thread.java:680)
24.11.2010 12:37:27 dbfx_data.ServerConnect$1 messageArrived
SEVERE: null
java.lang.NullPointerException
        at dbfx_data.ConnInsert.insert(ConnInsert.java:35)
        at dbfx_data.InsertData.insertData(InsertData.java:62)
        at dbfx_data.ServerConnect$1.messageArrived(ServerConnect.java:72)
        at com.fxcm.internal.transport.FXCMGateway.update(FXCMGateway.java:769)
        at com.fxcm.messaging.util.fix.FIXUserSession$BackToUserQueue.run(FIXUserSession.java:607)
        at java.lang.Thread.run(Thread.java:680)
null : Connected (insert) to DB at : Wed Nov 24 12:37:27 EET 2010

=>   sql-insert   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('CAD/JPY','81.59','81.618','20101124-10:37:26') : Wed Nov 24 12:37:27 EET 2010

=> insertData sql   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('AUD/JPY','81.325','81.36','20101124-10:37:26') : Wed Nov 24 12:37:27 EET 2010

null : Connected (insert) to DB at : Wed Nov 24 12:37:27 EET 2010

=>   sql-insert   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('AUD/JPY','81.325','81.36','20101124-10:37:26') : Wed Nov 24 12:37:27 EET 2010

=> insertData sql   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('USD/JPY','83.301','83.319','20101124-10:37:26') : Wed Nov 24 12:37:27 EET 2010

null : Connected (insert) to DB at : Wed Nov 24 12:37:27 EET 2010

Open in new window

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 86

Expert Comment

by:CEHJ
ID: 34204267
Please post *as file attachments* the latest versions of the following:

ConnInsert.java
InsertData.java
ServerConnect.java
0
 

Author Comment

by:gbcbr
ID: 34204415
This is all package zip dbfx-data.zip
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34204686
>>Yes - that should stop the NPE

Sorry - i lost concentration there. See my comment:

>>>>
>>You suggest create this constructor into InsertData?

Yes - of course it wont make any difference if it's not used properly ;)
>>>>

It *isn't* being used properly, because ServerConnect.conn isn't initialised either
0
 

Author Comment

by:gbcbr
ID: 34204976
we have  
public class ServerConnect {   
   ..........
    public static InsertData aInsertData;
    public Connection conn;

    public ServerConnect(String[] args) throws ClassNotFoundException, SQLException,
            ......................

            public void messageArrived(ITransportable message) {

                if (message instanceof MarketDataSnapshot) {
                    try {

                        aInsertData = new InsertData(conn);
                        aInsertData.insertData(message, printMarketData);

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

Open in new window

what we have to initialize?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34205025
>>public Connection conn;

Is never initialised

0
 

Author Comment

by:gbcbr
ID: 34205084
But it's abstract class, how it's possible to initialize it?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34205110
>>But it's abstract class, how it's possible to initialize it?

The same way any Connection reference is initialised - by getting a connection from the db and assigning it to it
0
 

Author Comment

by:gbcbr
ID: 34205305
in this way it's initialized into Init class:
 
conn = (Connection) DriverManager.getConnection(databaseurl, user, password);

Open in new window

so, it is not enough?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34205578
>>so, it is not enough?

It's really very simple: if you have an object you wish to use, you can't use it if it hasn't been initialised. If you do, you'll get an NPE
0
 

Author Comment

by:gbcbr
ID: 34206588
But I need only one connection to db, I initialize Connection conn; into separated class Init and declare it in all other classes which use this connection.
I can't initialize conn into ServerConnect by the same way like Init.
0
 

Author Comment

by:gbcbr
ID: 34207094
I initialize conn at ServerConnect and as I expect I come back to the initial point.
Means, everything works well with one huge exception - each incoming data open own connection :-(
0
 

Author Comment

by:gbcbr
ID: 34207328
This is the reason why I want initialize conn out of any class which get stream data, because each new data initialize new connection.
The main question is how to order to reuse old connection?
Started at  : Wed Nov 24 19:38:23 EET 2010
=>
          dbFX data server 
=>
          dbFX: started logging in
=>
>>> requestTradingSessionStatus = DB_U100D1_gspRDk1dsqRBRgWGKehI7wr1x9V37yLNyuX0WFHCla0RZ2zdSF12Td-9
=>
          dbFX: done logging in at : Wed Nov 24 19:38:31 EET 2010
=>
oracle.jdbc.driver.T4CConnection@56e2ecc7 : Connected (insert) to DB at : Wed Nov 24 19:38:33 EET 2010

=>   sql-insert   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('USD/SEK','6.941','6.9442','20101124-17:38:32') : Wed Nov 24 19:38:33 EET 2010

oracle.jdbc.driver.T4CConnection@74002515 : Connected (insert) to DB at : Wed Nov 24 19:38:33 EET 2010

=>   sql-insert   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/USD','1.33616','1.33633','20101124-17:38:32') : Wed Nov 24 19:38:33 EET 2010

oracle.jdbc.driver.T4CConnection@39518cc : Connected (insert) to DB at : Wed Nov 24 19:38:34 EET 2010

=>   sql-insert   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('CAD/CHF','0.98385','0.98435','20101124-17:38:33') : Wed Nov 24 19:38:34 EET 2010

oracle.jdbc.driver.T4CConnection@576504fa : Connected (insert) to DB at : Wed Nov 24 19:38:34 EET 2010

=>   sql-insert   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/CAD','1.3487','1.3492','20101124-17:38:33') : Wed Nov 24 19:38:34 EET 2010

Open in new window

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34207341
>>I initialize conn at ServerConnect and as I expect I come back to the initial point.

Please post the altered code followed by the exception stack trace
0
 

Author Comment

by:gbcbr
ID: 34207442

public class ServerConnect {



    ..........



    public ServerConnect(String[] args) throws ClassNotFoundException, SQLException,

            InstantiationException,

            IllegalAccessException,

            FieldNotFound, Exception {



        fxcmGateway = GatewayFactory.createGateway();



        fxcmGateway.registerGenericMessageListener(new IGenericMessageListener() {



            public void messageArrived(ITransportable message) {



                if (message instanceof MarketDataSnapshot) {



                    try {

                        Class.forName(driverName);

                        conn = (Connection) DriverManager.getConnection(databaseurl, user, password);

                        aInsertData = new InsertData(conn);

                        try {

                            aInsertData.insertData(message, printMarketData);

                        } catch (InstantiationException ex) {

                            Logger.getLogger(ServerConnect.class.getName()).log(Level.SEVERE, null, ex);

                        } catch (IllegalAccessException ex) {

                            Logger.getLogger(ServerConnect.class.getName()).log(Level.SEVERE, null, ex);

                        } catch (NotDefinedException ex) {

                            Logger.getLogger(ServerConnect.class.getName()).log(Level.SEVERE, null, ex);

                        } catch (FieldNotFound ex) {

                            Logger.getLogger(ServerConnect.class.getName()).log(Level.SEVERE, null, ex);

                        } catch (Exception ex) {

                            Logger.getLogger(ServerConnect.class.getName()).log(Level.SEVERE, null, ex);

                        }

                    }catch (ClassNotFoundException ex) {

                        Logger.getLogger(ServerConnect.class.getName()).log(Level.SEVERE, null, ex);

                    } catch (SQLException ex) {

                        Logger.getLogger(ServerConnect.class.getName()).log(Level.SEVERE, null, ex);

                    }

                }

            }

        });

Open in new window

0
 

Author Comment

by:gbcbr
ID: 34208045
I suppose that I found root of the problem.
Two processes close each other.
Let's say in Main class I create two methods serverConnect and init.
When I call them from the main method:
serverConnect(args);
init();
I have NPE
when:
init();
serverConnect(args);
SQLException = null

So, they close each other.
Please advice
 
public class Main {
    .......................
    public static void main(String[] args) throws Exception {

//        init();
        serverConnect(args);
        init();
    }

    public static void serverConnect(String[] args) throws Exception {
        try {
            aServerConnect = new ServerConnect(args);

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

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

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

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

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

            public static  Connection init() throws SQLException {

        try {

            Class.forName(driverName);
            conn = (Connection) DriverManager.getConnection(databaseurl, user, password);
            System.out.println(conn + " : Connected (init) to DB at : " + new java.util.Date());
            System.out.println();

            aConnInsert = new ConnInsert(conn);
            aConnInsert.insert(sql);

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

        } catch (SQLException ex) {
            Logger.getLogger(ServerConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return (Connection) conn;
    }
}

Open in new window

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34209218
>>
                        conn = (Connection) DriverManager.getConnection(databaseurl, user, password);
                        aInsertData = new InsertData(conn);
>>

Yes, that's an improvement, but you didn't post the current stack trace
0
 

Author Comment

by:gbcbr
ID: 34211637
25.11.2010 9:24:27 dbfx_data.ServerConnect$1 messageArrived
SEVERE: null
java.lang.NullPointerException
        at dbfx_data.ConnInsert.insert(ConnInsert.java:36)
        at dbfx_data.InsertData.insertData(InsertData.java:63)
        at dbfx_data.ServerConnect$1.messageArrived(ServerConnect.java:62)
        at com.fxcm.internal.transport.FXCMGateway.update(FXCMGateway.java:769)
        at com.fxcm.messaging.util.fix.FIXUserSession$BackToUserQueue.run(FIXUserSession.java:607)
        at java.lang.Thread.run(Thread.java:680)

Open in new window

ConnInsert.java:36  statement = (Statement) conn.createStatement();
InsertData.java:63 aConnInsert.insert(sql);
SreverConnect.java:62 aInsertData.insertData(message, printMarketData);

Open in new window

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34211655
The Connection object is probably not getting passed along all the way to ConnInsert, so the reference ('conn') is null when you try to use it
0
 

Author Comment

by:gbcbr
ID: 34211963
Yes, I see that error starts from ServerConnect class, but if I initialize it in this class we come into problem with new connection for each quote.
In previous version I initialize conn directly into insert method, without any other special references. Only once, and everything works perfect, but with big delay for opening and closing new connections.
Now, I want to initialize only once from the class which has not incoming data, so it will not initiate creating new connection.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34211993
>>Yes, I see that error starts from ServerConnect class, but if I initialize it in this class we come into problem with new connection for each quote.

No-one said anything about *where* it's initialised. The point is, irrespective of where it's initialised, you CANNOT USE an uninitialised Connection in ServerConnect
0
 

Author Comment

by:gbcbr
ID: 34212359
Please look at here, this is previous WORKING (slowly) version, I just removed, unnecessary for this case, part of code for easy reading:
 
public class Main {

    private static ServerConnect aServerConnect;

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

        serverConnect(args);              
    }

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

            aServerConnect = new ServerConnect(args);

        } catch 
    }
-------------------------------------------

public class ServerConnect {

    private static List accounts = new ArrayList();
    private static InsertData aInsertData;
    MarketDataSnapshot incomingQuote;
    private static String accountMassID;
    private static String openOrderMassID;
    private static String openPositionMassID;
    private static String closedPositionMassID;
    private static String tradingSessionStatusID;
    private static TradingSessionStatus tradingSessionStatus;
    private static boolean printMarketData = false;
    final IGateway fxcmGateway;
    RequestForPositionsAck rfpa;
    ClosedPositionReport cpr;
    PositionReport pr;
    ExecutionReport ep;
    CollateralReport cr;
    BusinessMessageReject bmr;
    
   
    public ServerConnect(String[] args) throws ClassNotFoundException, SQLException,
            InstantiationException,
            IllegalAccessException,
            FieldNotFound, Exception {

        fxcmGateway.registerGenericMessageListener(new IGenericMessageListener() {

            public void messageArrived(ITransportable message) {

                if (message instanceof MarketDataSnapshot) {
                    try {

                        aInsertData = new InsertData();
                        aInsertData.insertData(message, printMarketData);


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

public class InsertData {

    MarketDataSnapshot incomingQuote;
    ConnectDB conn = new ConnectDB();
    String sql = null;

    public void insertData(ITransportable message, boolean printMarketData) throws SQLException,
            ClassNotFoundException,
            InstantiationException,
            IllegalAccessException,
            NotDefinedException,
            FieldNotFound,
            Exception {

        if (message instanceof MarketDataSnapshot) {      
                    try {
                        sql =
                                " INSERT INTO STREAM_DATA "
                                .......

                    conn.insert(sql);
-------------------------------------------------------------

public class ConnectDB {

   ................
    
    Connection conn;
    ResultSet rs;
    Statement statement;
    PreparedStatement ps;

    public void insert(String sql) throws SQLException {
        try {
            Class.forName(driverName).newInstance();
            conn = (Connection) DriverManager.getConnection(databaseurl, user, password);
            statement = (Statement) conn.createStatement();
            int st = statement.executeUpdate(sql);
            destroy();
            statement.close();

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


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

Open in new window

You can see that nowhere except of insert method of ConnectDB class I call conn. This class works alone for all db jobs: connect to db, update records, close conn.
But when I move out from this method conn initialization, initialize it into Main class and call aConnInsert.insert;
Initially conn come into insert(sql) as we can see
public void insert(Connection conn, String sql) throws SQLException {

        System.out.println(conn + " : Connected (insert) to DB at : " + new java.util.Date());
        System.out.println();
        System.out.println("=>   sql-insert  " + sql + " : " + new java.util.Date());
        System.out.println();

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

        statement.close();
----------------------------------------------

Started at  : Thu Nov 25 14:32:41 EET 2010
=>
oracle.jdbc.driver.T4CConnection@509f5011 : Connected (init) to DB at : Thu Nov 25 14:32:42 EET 2010

oracle.jdbc.driver.T4CConnection@509f5011 : Connected (insert) to DB at : Thu Nov 25 14:32:42 EET 2010

=>   sql-insert  null : Thu Nov 25 14:32:42 EET 2010

25.11.2010 14:32:42 dbfx_data.Main init
SEVERE: null
java.sql.SQLException: ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ SQL ¿¿ ¿¿¿¿¿¿ ¿¿¿¿ ¿¿¿¿¿¿ ¿¿¿ ¿¿¿¿¿¿¿¿¿¿¿¿¿¿
          dbFX data server 
=>
          dbFX: started logging in
=>
        at oracle.jdbc.driver.OracleSql.initialize(OracleSql.java:113)
        at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1805)
        at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1779)
        at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:277)
        at dbfx_data.ConnInsert.insert(ConnInsert.java:37)
        at dbfx_data.Main.init(Main.java:77)
        at dbfx_data.Main.main(Main.java:41)
>>> requestTradingSessionStatus = DB_U100D1_2VXESJHiuFNHxc8203jr9wI9zjBpyw0gHclnX4viqOG6WgrByAuR75-8
=>
          dbFX: done logging in at : Thu Nov 25 14:32:48 EET 2010
=>
null : Connected (insert) to DB at : Thu Nov 25 14:32:49 EET 2010

Open in new window

But sql message still not come because server still don't have any incoming data, when server complete connection to external data server, sql messages start coming and on this stage conn starts to be invisible for insert method.
Means, something close viewing conn from Main/init for ConnInsert/insert.
This what I understood from all my experiments, but unfortunately I don't understand WHY this happened.
0
 

Author Comment

by:gbcbr
ID: 34212738
Can advice some other way to insert data into db apart of existing?
 
statement = (Statement) conn.createStatement();
        int st = statement.executeUpdate(sql);

Open in new window

Maybe (Statement) close conn.createStatement and in this reason it starts to be invisible?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34212786
That's a completely different situation. That uses ConnectDB, which contains its OWN Connection. In the previous code you were attempting to use a plain Connection
0
 

Author Comment

by:gbcbr
ID: 34212933
But what is the solution?
My name is not Columbus and I don't believe that I'm opening America with this simple application.
If my constructions is wrong, please advice some other way to solve this question.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34213732
Well, your basic problem is that your design started off on a bad footing. You never actually listened to my advice at the time and the result is you've stacked more and more problems on top of each other. The more code you write, the worse it will get.

I would recommend you use a proper connection pool. At the moment, you're using a mix of some home-brewed connection wrapper and normal Connection instances. Anything that needs to use a Connection should be able to get a reference to the pool to get one
0
 

Author Comment

by:gbcbr
ID: 34213770
Thank you for the advice, but you never recommend me the proper way, only said that some my steps is wrong.
I told you that I got my first Java book at this August, what I use now is the bullshit which I try to convert into normal code.
So, sometimes, I need proper advice, not only critical comments that I'm on the wrong way.
I work on this problem one week and now I understand that I have to change general conception, but, at the moment, I don't know for what to change. If I'll change it for another wrong conception it will cost me another one week.
Please advice how to organize reusable connection properly.
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 500 total points
ID: 34216651
You should probably make a start by preparing to use a connection pool by setting up ConnectDB so it's used throughout, wherever a Connection is needed. Don't get a connection in any other way
0
 

Author Closing Comment

by:gbcbr
ID: 34218394
Thank you for your time spent on this question, I understood that I have to use Connection pool for this case.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34218632
:)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Python Assistance 7 36
Free Alternative to JIRA 4 58
JDeveloper 12c for 32 bit 4 35
eclipse formatting 6 43
For customizing the look of your lightweight component and making it look lucid like it was made of glass. Or: how to make your component more Apple-ish ;) This tip assumes your component to be of rectangular shape and completely opaque. (COD…
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…

759 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

19 Experts available now in Live!

Get 1:1 Help Now