Java Prepared Statement not working with my loop

Question

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();

Please share some more code about the table structure and prepared statements and query. — Braj
– Braj, Commented May 8, 2014 at 15:03
If you want to set a date in a database, then you should create a Timestamp and use setTimestamp(), not setString(). setString() is for setting... strings. — JB Nizet
– JB Nizet, Commented May 8, 2014 at 15:10
Using setString(1, "Event1") is not the same as setString(1, Event1)... — Mark Rotteveel
– Mark Rotteveel, Commented May 8, 2014 at 15:15
So how can I use Event1 instead of hardcoding Event1 into my code? — user3588388
– user3588388, Commented May 8, 2014 at 15:19

Keerthivasan · Accepted Answer · 2014-05-08 15:12:29Z

3

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>();

edited May 8, 2014 at 15:12

answered May 8, 2014 at 15:06

Keerthivasan

12.9k2 gold badges34 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Braj Over a year ago

Is it not equivalent to the loop that OP is used?

JB Nizet Over a year ago

@Braj: no. Event1 is a variable whose value is "22122014 13.26". pStatement is a variable whose value is "Event1".

Keerthivasan Over a year ago

No, it is not. the loop is having a serious bug. OP creates Event1,Event2 etc. and sets it to the prepared statement instead of values like 22122014 13.29

JB Nizet Over a year ago

@user3588388 if you sometimes have 10 values and sometimes 100, then it means you have an array or list of values. Then loop over the array or list, and insert each value in the list. What your code tries to insert is the name of a variable. That doesn't make any sense.

Haifeng Zhang Over a year ago

@user3588388 You should put prepState.addBatch(); into the for loop

|

lgraham076 · Accepted Answer · 2014-05-08 17:07:09Z

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.

Collectives™ on Stack Overflow

Java Prepared Statement not working with my loop

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related