CODEWITHSUNDEEP
javaoracle-databasejdbcprepared-statement
SaintLike
SaintLike

Reputation: 9569

Invalid column index in preparedStatement

I'm trying to insert a value in a DB table and I keep stumbling on the Invalid Column Index error.

Here's a code sample:

        String insertNewAlarmStat =
            "insert into alarmes (id_alarm, alarm_key, id_notif, sever, urgency, date_hour_start, date_hour_modif, date_hour_end, " +
                    "state, state_rec, date_hour_rec,  id_user_rec, id_system_rec, " +
                    "type, cause, " +
                    "num_events, id_entity_g, type_entity_g, " +
                    "desc_entity_g, problem, " +
                    "time_urg_act, max_urg_act, time_end, time_arq, lim, rec_oblig, dn, num_events_ps, id_alarm_o, id_notif_o, text_ad, domain, date_hour_reg) " +
                    "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE, SYSDATE, SYSDATE, SYSDATE, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE)";
    PreparedStatement prpstmt = null ;
    try {
        prpstmt = conn.prepareStatement(insertNewAlarmStat);

        prpstmt.setInt(1, randomNumberGenerator());
        prpstmt.setString(2, UUID.randomUUID().toString());
        prpstmt.setString(3, UUID.randomUUID().toString());
        prpstmt.setInt(4, randomNumberGenerator());
        prpstmt.setInt(5, 8);
        prpstmt.setInt(6, 8524);
        prpstmt.setString(7, UUID.randomUUID().toString());
        prpstmt.setString(8, UUID.randomUUID().toString());
        prpstmt.setString(9, UUID.randomUUID().toString());
        prpstmt.setString(10, UUID.randomUUID().toString());
        prpstmt.setString(11, "KABOOM");
        prpstmt.setInt(12, 8);
        prpstmt.setDate(13, getCurrentDate());
        prpstmt.setDate(14, getCurrentDate());
        prpstmt.setDate(15, getCurrentDate());
        prpstmt.setDate(16, getCurrentDate());
        prpstmt.setInt(17, 43);
        prpstmt.setString(18, UUID.randomUUID().toString());
        prpstmt.setString(19, UUID.randomUUID().toString());
        prpstmt.setString(20, UUID.randomUUID().toString());
        prpstmt.setString(21, UUID.randomUUID().toString());
        prpstmt.setInt(22, 2);
        prpstmt.setInt(23, 224);
        prpstmt.setInt(24, 2);
        prpstmt.setInt(25, 224);            
        prpstmt.setInt(26, 2);
        prpstmt.setInt(27, 4);          
        prpstmt.setInt(28, 2);  
        prpstmt.setString(29, null);
        prpstmt.setString(30, UUID.randomUUID().toString());
        prpstmt.setString(31, UUID.randomUUID().toString());            
        prpstmt.setInt(32, 2);  

        prpstmt.execute();

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

I've tried to remove the prpstmt from 13 to 16 but it complains that those indexes are missing, I've tried other stuff as well but I can't get this to work.

What am I doing wrong?

Upvotes: 1

Views: 2403

Answers (3)

rgettman
rgettman

Reputation: 178253

There are 33 columns in this insert statement. There are 33 values -- 28 ? place holders and 5 literal values (sysdate). The indexes in the setXYZ methods refer to the index of the ? placeholders, not the index into the values of the insert statement.

For the sysdate values, you don't need to do anything, so remove those calls to setDate. But for values beyond the first 4 sysdates, you just need to use the next value, which is 13, not 17.

prpstmt.setInt(1, randomNumberGenerator());
// snip
prpstmt.setInt(12, 8);
// calls to `setDate(13-16, getCurrentDate());` removed
prpstmt.setInt(13, 43);  // changed 17 to 13
// snip
prpstmt.setInt(28, 2);   // changed 32 to 28

What is sysdate?

sysdate is an Oracle function that returns the current date/timestamp, equivalent to Calendar.getInstance() in Java. For the purposes of JDBC, it's a value that is already supplied in the insert statement.

Upvotes: 1

Elliott Frisch
Elliott Frisch

Reputation: 201409

Your query String contains 28 question marks, I wrote a program to count for you

String str = "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE, SYSDATE, SYSDATE, "
    + "SYSDATE, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE)";
int count = 0;
for (char ch : str.toCharArray()) {
    if (ch == '?') {
        count++;
    }
}
System.out.println(count);

So, you can't bind 32 parameters. I think your 

 prpstmt.setDate(13, getCurrentDate());
 prpstmt.setDate(14, getCurrentDate());
 prpstmt.setDate(15, getCurrentDate());
 prpstmt.setDate(16, getCurrentDate());

Are hard coded to SYSDATE in the query. So, remove the four SYSDATE(s) in the middle of the query

"(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
+ "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE)";

And you'd have 32.

Upvotes: 0

Shankar
Shankar

Reputation: 879

remove 

prpstmt.setDate(13, getCurrentDate());
prpstmt.setDate(14, getCurrentDate());
prpstmt.setDate(15, getCurrentDate());
prpstmt.setDate(16, getCurrentDate());

and instead of 17, start from 13 again..

?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE, SYSDATE, SYSDATE, SYSDATE, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE

1, 2, 3, 4, 5, 6, 7, 8, 9,10,11,12,SYSDATE, SYSDATE, SYSDATE, SYSDATE,13,14.... etc

Upvotes: 2

Related Questions