java.lang.ArrayIndexOutOfBoundsException: 2

Question

I'm trying to connect java to sql. Though I'm a newbie with javam I can't seem to find out why.

java.lang.ArrayIndexOutOfBoundsException: 2

Can you help me and give me idea how to figure it out?

try{
       Connection con = dbConnection();

       String lname = this.last.getText();
       String fname = this.first.getText();
       String mname = this.mid.getText();
       String ad = this.add.getText();
       String bd = this.bday.getText();
       String ag = this.edad.getText();
       String nom = this.no.getText();
       String per = this.person.getText();

       String query = "INSERT INTO Personal Category    (Lastname,Firstname,Middle,Address,Birthday,Age,No,Person) VALUES (?,?)"; //to insert to database

       PreparedStatement pre;

       pre = con.prepareStatement(query);


       pre.setString(1, lname);
       pre.setString(2, fname);
       pre.setString(3, mname);
       pre.setString(4, ad);
       pre.setString(5, bd);
       pre.setString(6, ag);
       pre.setString(7, nom);
       pre.setString(8, per);


       pre.execute();//execute

       con.close();


    }catch (Exception e){

        System.out.println(e);

    }

}

Is the table name "Personal Category", with a space in between? — Vikdor, Sep 24 '12 at 16:27
Step through the code with the debugger. Then you'll see which line it's failing on. — Tony Ennis, Sep 24 '12 at 16:30

Denys Séguret · Answer 1 · 2012-09-24T16:59:32.950

5

You cannot set more prepared statement properties than you have '?' in your query.

"ArrayIndexOutOfBoundsException: 2" means it fails at the third setString (the internal array is zero-based as all java arrays), which is normal given you have only two '?' in the query.

EDIT :

You probably have an error due to the space in your table name. Try with

INSERT INTO [Personal Category]    (Lastname, ...

edited Sep 24 '12 at 16:59

answered Sep 24 '12 at 16:23

Denys Séguret

372,613
87
782
758

i'd already changed the VALUES(?,?,?,?,?,?,?,?)... though its says now that "java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement." still thanks to your replies =) – user1694994 Sep 24 '12 at 16:54
See edit for other problem. I think that you may escape with [] the names of the tables with space (if you really need to have some...). – Denys Séguret Sep 24 '12 at 17:00
Note that I'm not used to Access. You may have to use `'Personal Category'` or `"Personal Category"`. – Denys Séguret Sep 24 '12 at 17:11

score 3 · Answer 2 · edited Sep 24 '12 at 19:45

3

You only have two ? in your VALUES() declaration. You need to have as many as you have column names.

Try

String query = "INSERT INTO Personal Category    (Lastname,Firstname,Middle,Address,Birthday,Age,No,Person) VALUES (?,?,?,?,?,?,?,?)"; //to insert to database

edited Sep 24 '12 at 19:45

Victor

8,309
14
80
129

answered Sep 24 '12 at 16:24

asteri

11,402
13
60
84

score 1 · Answer 3 · answered Sep 24 '12 at 16:24

You need more question marks - six more, to be precise.

String query = "INSERT INTO Personal Category    (Lastname,Firstname,Middle,Address,Birthday,Age,No,Person) VALUES (?,?,?,?,?,?,?,?)";

JDBC interprets each question mark as a placeholder for a parameter, i.e. a promise by your program to supply a value after preparing the statement. When you call setString, setInt, setLong, etc. on your prepared statement, a corresponding numbered placeholder must exist. Otherwise, java.lang.ArrayIndexOutOfBoundsException is thrown.

score 0 · Answer 4 · answered Sep 24 '12 at 16:26

0

You should have as many placeholders (?) as the number of parameters to be passed to that INSERT statement.

String query = "INSERT INTO PersonalCategory (Lastname, Firstname, Middle, " + 
"Address, Birthday, Age, No, Person) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"

answered Sep 24 '12 at 16:26

Vikdor

23,934
10
61
84

i'd already changed the VALUES(?,?,?,?,?,?,?,?)... though its says now that "java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement." still thanks to your replies =) – user1694994 Sep 24 '12 at 16:52

score 0 · Answer 5 · answered Sep 24 '12 at 16:28

0

String query = "INSERT INTO Personal Category (Lastname,Firstname,Middle,Address,Birthday,Age,No,Person) VALUES (?,?)";

The problem is with the Number of arguments to be passed in the VALUES, You have passed only 2 arguments where it needs to be 8.

answered Sep 24 '12 at 16:28

Kumar Vivek Mitra

33,294
6
48
75

i'd already changed the VALUES(?,?,?,?,?,?,?,?)... though its says now that "java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement." still thanks to your replies =) – user1694994 Sep 24 '12 at 16:54
There may be a problem with your Table Name.. There is a space in between. You can't have a space in between words of your Table name. Check that and you need to remove that. – Rohit Jain Sep 24 '12 at 16:57

score 0 · Answer 6 · answered Sep 24 '12 at 17:31

you have got the answer to "ArrayIndexOutOfBound" issue. I will answer to your second question "SQLException" and give some advice to improve your code.

That SQLException is happening because your table name I guess. There are 2 words in your table name and that might the confusing the compiler.

Advices

you are executing preparedStatement without catching its output value. It will give you an boolean value as the output. Then only you can find what kind of thing has been executed.
In here, it seems like you are ONLY INSERTING data, and you are sure you are ONLY INSERTING DATA. So, if it is, use executeUpdate() rather than execute() method.
Always close the connection inside a final() block. This way, you can always make sure the connection is closed whether the code fails or not. By doing this, Database rush will be managed smoothly, by not keeping unnecessary connections open.

Lets stay with good software engineering concepts :)

java.lang.ArrayIndexOutOfBoundsException: 2

6 Answers6

Linked