Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1095
  • Last Modified:

SQL statement must not be empty or undefined

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
gbcbr
Asked:
gbcbr
  • 32
  • 25
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
gbcbrAuthor Commented:
<<<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
 
CEHJCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gbcbrAuthor Commented:
=>
          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
 
CEHJCommented:
I don't think you did it. For one thing, i don't see the string '"=> insertData sql" in the output
0
 
gbcbrAuthor Commented:
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
 
gbcbrAuthor Commented:
So, this problem is solved and we return to connection problem
0
 
CEHJCommented:
Yes, looks like you're passing a null Connection to the ConnInsert ctor
0
 
gbcbrAuthor Commented:
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
 
CEHJCommented:
... and the reason for that is that there's no Connection initialised in InsertData
0
 
gbcbrAuthor Commented:
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
 
CEHJCommented:
>>but when I initialize connection in InsertData

But you don't. That's the problem - you have a null connection
0
 
gbcbrAuthor Commented:
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
 
CEHJCommented:
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
 
CEHJCommented:
You should probably insert the following ctor and use it


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

Open in new window

0
 
gbcbrAuthor Commented:
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
 
CEHJCommented:
See my last comment
0
 
gbcbrAuthor Commented:
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
 
CEHJCommented:
ConnInsert is a completely different class. We're talking about InsertData
0
 
gbcbrAuthor Commented:
Without it app stops working
0
 
gbcbrAuthor Commented:
You suggest create this constructor into InsertData?
I'll try and post the result
0
 
CEHJCommented:
>>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
 
gbcbrAuthor Commented:
Nothing changed, the same output
0
 
gbcbrAuthor Commented:
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
 
gbcbrAuthor Commented:
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
 
CEHJCommented:
>>Nothing changed, the same output

How and where are you calling the new ctor?
0
 
gbcbrAuthor Commented:
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
 
CEHJCommented:
Yes - that should stop the NPE
0
 
gbcbrAuthor Commented:
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
 
CEHJCommented:
Please post *as file attachments* the latest versions of the following:

ConnInsert.java
InsertData.java
ServerConnect.java
0
 
gbcbrAuthor Commented:
This is all package zip dbfx-data.zip
0
 
CEHJCommented:
>>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
 
gbcbrAuthor Commented:
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
 
CEHJCommented:
>>public Connection conn;

Is never initialised

0
 
gbcbrAuthor Commented:
But it's abstract class, how it's possible to initialize it?
0
 
CEHJCommented:
>>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
 
gbcbrAuthor Commented:
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
 
CEHJCommented:
>>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
 
gbcbrAuthor Commented:
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
 
gbcbrAuthor Commented:
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
 
gbcbrAuthor Commented:
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
 
CEHJCommented:
>>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
 
gbcbrAuthor Commented:

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
 
gbcbrAuthor Commented:
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
 
CEHJCommented:
>>
                        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
 
gbcbrAuthor Commented:
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
 
CEHJCommented:
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
 
gbcbrAuthor Commented:
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
 
CEHJCommented:
>>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
 
gbcbrAuthor Commented:
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
 
gbcbrAuthor Commented:
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
 
CEHJCommented:
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
 
gbcbrAuthor Commented:
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
 
CEHJCommented:
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
 
gbcbrAuthor Commented:
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
 
CEHJCommented:
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
 
gbcbrAuthor Commented:
Thank you for your time spent on this question, I understood that I have to use Connection pool for this case.
0
 
CEHJCommented:
:)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 32
  • 25
Tackle projects and never again get stuck behind a technical roadblock.
Join Now