Hi guy's I need to know how can I (insert into) a created table cause I'm getting a lot of errors :

        Connection con = DriverManager.getConnection(DB_URL,username,password);
        //query for the offer Validity
        String query = "SELECT chargingtime,CHARGINGPARTYNUMBER,SUBSCRIBERID,OFFERNAME,OFFERID,prepaidbalance,LIFECYCLE_DAYS,LVL,OPERATIONID FROM ncs_sub_unsub";

        try (Statement stmt = con.createStatement()) {
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                String SUBSCRIBERI = rs.getString("SUBSCRIBERID");
                String OFFERNAME = rs.getString("OFFERNAME");
                String OFFERID = rs.getString("OFFERID");
                String LIFECYCLE_DAYS = rs.getString("LIFECYCLE_DAYS");
                int LEVEL1 = rs.getInt("LVL");
                String CHARGINGPARTY =rs.getString("CHARGINGPARTYNUMBER");
                int OPERATIONID= rs.getInt("OPERATIONID");
                float prepaid_balance = rs.getFloat("PREPAIDBALANCE");
                LocalDate CHARGINGTIME1 = rs.getObject("CHARGINGTIME", LocalDate.class);
                LocalDate CHARGINGTIME7 = CHARGINGTIME1.plusDays(7);
                LocalDate CHARGINGTIME14 = CHARGINGTIME1.plusDays(14);
                //if the level is one , the time should be 7 days
                if(LEVEL1 == 1){
                    if((OPERATIONID == 4050001 && CHARGINGTIME1.isBefore(CHARGINGTIME7)) || CHARGINGTIME1.isEqual(CHARGINGTIME7)){
                     String q = String.valueOf(stmt.executeUpdate("INSERT INTO aggregation VALUES (SUBSCRIBERI,CHARGINGPARTY,OPERATIONID,OFFERNAME,prepaid_balance,LIFECYCLE_DAYS,LEVEL1,'S')"));
                    } else if ((OPERATIONID == 4050018 && CHARGINGTIME7.isAfter(CHARGINGTIME1)) || CHARGINGTIME7.isEqual(CHARGINGTIME1)) {
                        String q = String.valueOf(stmt.executeUpdate("INSERT INTO aggregation VALUES (SUBSCRIBERI,CHARGINGPARTY,OPERATIONID,OFFERNAME,prepaid_balance,LIFECYCLE_DAYS,LEVEL1,'U')"));
                    }else {
                        String q = String.valueOf(stmt.executeUpdate("INSERT INTO aggregation VALUES (SUBSCRIBERI,CHARGINGPARTY,OPERATIONID,OFFERNAME,prepaid_balance,LIFECYCLE_DAYS,LEVEL1,'E')"));
                    }

                }

            }
            }// end of try
        catch (SQLException e){
            e.printStackTrace();
        }


        }

    public static void main(String[] args) throws SQLException {
        ViewTable();


    }
}

The error : Caused by: Error : 984, Position : 111, Sql = INSERT INTO aggregation VALUES (SUBSCRIBERI,CHARGINGPARTY,OPERATIONID,OFFERNAME,prepaid_balance,LIFECYCLE_DAYS,LEVEL1,'U'), OriginalSql = INSERT INTO aggregation VALUES (SUBSCRIBERI,CHARGINGPARTY,OPERATIONID,OFFERNAME,prepaid_balance,LIFECYCLE_DAYS,LEVEL1,'U'), Error Msg = ORA-00984: column not allowed here

CodePudding user response：

It looks like the Insert sql query is wrong.

I think you are trying to insert String values without '' and String values into float columns.

INSERT INTO aggregation VALUES (SUBSCRIBERI,CHARGINGPARTY,OPERATIONID,OFFERNAME,prepaid_balance,LIFECYCLE_DAYS,LEVEL1,'U')

This is not a valid sql query since prepaid_balance is not a number and SUBSCRIBERI is not a string (try 'SUBSCRIBERI' etc)

CodePudding user response：

stmt.executeUpdate("INSERT INTO aggregation VALUES (SUBSCRIBERI,CHARGINGPARTY,OPERATIONID,OFFERNAME,prepaid_balance,LIFECYCLE_DAYS,LEVEL1,'U')"));

This doesn't work. You can't use java variable names inside a string and expect the system to figure it out. That string is based verbatim to the SQL JDBC driver which is not capable of doing that sort of thing.

What you want is [A] not executeUpdate, it can't do this. You want prepareStatement. The statement would be something like:

"INSERT INTO aggregation VALUES (?,?,?,?,?,?,?,'U')"

and then you use the various .setX methods that PreparedStatement has to set the values: .setString(1, SUBSCRIBERI);, and so on for all the other question marks. Once you've done all that, you can run .executeUpdate() on the prepared statement object.

There are thousands of tutorials out on the web that show you how to use PreparedStatement here.