CODEWITHSUNDEEP
javasqlprepared-statement
user3588388
user3588388

Reputation: 37

Java Prepared Statement not working with my loop

I am new to this and trying to add some dates into a Oracle database using prepared statements. this is my code:

CODE REMOVED, code updated.

This produces the following: 

INSERT into TEST_RESULTS12233 (DB_ID, DATED1, DATED2, DATED3, DATED4) values (t1_seq.nextval, to_date(?, 'dd/mm/yyyy hh24.mi'), to_date(?, 'dd/mm/yyyy hh24.mi'), to_date(?, 'dd/mm/yyyy hh24.mi'), to_date(?, 'dd/mm/yyyy hh24.mi'))
Event1
Event2
Event3
Event4
Exception in thread "main" java.sql.BatchUpdateException: ORA-01858: a non-numeric character was found where a numeric was expected

at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10500)
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:230)
at sandpit.testsand.main(testsand.java:85)

If i replace pstatement in setString with Event1, the batch executes. Does anyone know how I can get this to work?? Thanks

Here is the full code as requested: 

     //MORE CODE AS REQUESTED 


     String inserttotable = "INSERT into TEST_RESULTS12233 (DB_ID";
     String stateclose = ")";
     String insertvalues =(" values (t1_seq.nextval");
     StringBuilder insertBuilder = new StringBuilder();
     StringBuilder valueBuilder = new StringBuilder();
     int timestoloop = 4;

     for (i=1; i<=timestoloop; i++)

     {           
         insertBuilder.append(", DATED"+i);
         valueBuilder.append(", to_date(?, 'dd/mm/yyyy hh24.mi')");

     }

     String finalinsert = inserttotable + insertBuilder.toString()+ stateclose;
     String finalvalues = insertvalues + valueBuilder.toString() + stateclose;
     String insertsql = finalinsert + finalvalues;
     System.out.println(insertsql);
     String insertresults = insertsql;

        PreparedStatement prepState = conn.prepareStatement(insertresults);


    String Event1 = "22122014 13.26";
    String Event2 = "22122014 13.27";
    String Event3 = "22122014 13.28";
    String Event4 = "22122014 13.29"; 


     for (i=1; i<=timestoloop; i++)

     {           
        String pstatement = "Event"+i;
        System.out.println(pstatement);
        prepState.setString(i, pstatement);


     }

     prepState.addBatch();
     prepState.executeBatch();

Upvotes: 0

Views: 750

Answers (2)

lgraham076
lgraham076

Reputation: 81

I believe you mentioned you already tried an arraylist but it should look something like this:

List<String> events=new ArrayList<String>();
    events.add("22122014 13.26");
    events.add("22122014 13.27");
    events.add("22122014 13.28");
    events.add("22122014 13.29");


     for (i=1; i<=timestoloop; i++)

     {           
        String pstatement = "Event"+i;
        System.out.println(pstatement);
        prepState.setString(i, events.get(i));
        prepState.addBatch();
     }

You were using the string "Event1" from your variable rather than the actual variable. prepState.setString(i,"event"+i) does not equal prepState.setString(i,event1). One simply returns a string one is simply a variable holding the string value, the latter being the one you want.

Upvotes: 0

Keerthivasan
Keerthivasan

Reputation: 12880

You are making a mess by creating the variable name dynamically by appending the counter as a String literal (Event1) and setting them to the prepared statement as a String input. You are actually setting the String literals like (Event1) instead of Values (22122014 13.26). This is not the way you should do.You may have to set the values directly like 

 prepState.setString(1, Event1);
 prepState.setString(2, Event2);
 prepState.setString(3, Event3);
 prepState.setString(4, Event4);

I'd suggest you to go for a List of String literals having all the Event values. Iterate them and set them in the Prepared statement and execute the query.

List<String> eventValues = new ArrayList<String>();

Upvotes: 3

Related Questions