asked on

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

Guy Hengel [angelIII / a3]

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 ...