0

I am having issues creating a SQLite-database using a text file. I am supposed to create a members list with the following: (member) number, first name, last name, address, and telephone number (if any). I am using methods to create the db, but I can't seem to get it to work properly.

The text file is populated like this (the '...' is supposed to mean that there are others in the list):

1;John;Doe;Downing, Virginia;123456
2;Jane;Doe;Obispo 2, California;342532
...
...
14;Rich;Damons;Av.5, NY
...
...

As you can probably see, some of the members do not have a telephone number, which is what I think is causing the db to not be populated. The reason I believe this, is that I tried making my own list with all the info and that seems to work, but removing the telephone numbers gives me the following error Index 4 out of bounds for length 4

This is the method:

    private static void createNewTable() throws Exception {
        String url = "jdbc:sqlite:members.db";
        Connection con = DriverManager.getConnection(url);
        Statement stmt = con.createStatement();
        Scanner reader = new Scanner(new File("member_list.txt"));

        String sql = "create table Member(No integer primary key, " +
                "First varchar(50), " +
                "Last varchar(50), " +
                "Address varchar(50), " +
                "Telephone integer);";
        stmt.executeUpdate(sql);

        try (reader) {
            while (reader.hasNextLine()) {
                String[] db = reader.nextLine().split("[;]");
                String fName = db[1], lName = db[2], address = db[3];
                int no = parseInt(db[0]), tel = parseInt(db[4]);
                if (db.length == 5) {
                    sql = "insert into Member values(" + no + ",'" + fName + "','" + lName + "','" + address + "'," + tel + ");";
                    stmt.executeUpdate(sql);
                } else {
                    sql = "insert into Member values(" + no + ",'" + fName + "','" + lName + "','" + address + "'," + null + ");";
                    stmt.executeUpdate(sql);
                }
            }
        }

        con.close();
        out.println("Start: Creates table");
        showMessageDialog(null, "Start: Creates table");
    }

Any help is greatly appreciated!

I tried creating my own member list with all the information, which worked; I tried creating a member list without telephone numbers, which did not work.

5
  • After you do the string split, check the length and act accordingly. Commented Apr 5, 2022 at 14:35
  • 2
    Don't concatenate strings to make a SQL statement. What if one of the people is named Robert'; drop table Member--? Commented Apr 5, 2022 at 14:37
  • fyi split("[;]") can be replaced with split(";"). Commented Apr 5, 2022 at 14:48
  • @DavidConrad Thanks for the tip. How can I work around this? I'm sorry, but English isn't my native language. Commented Apr 5, 2022 at 15:16
  • Use a PreparedStatement. Commented Apr 6, 2022 at 20:48

2 Answers 2

1

For entries that do not have a telephone number, the length of the string is 4. Hence, when you try to access db[4] you get ArrayIndexOutofBound exception.

Initialise the tel variable inside the if condition.

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

1 Comment

That sure did the trick! Appreciate it!
1

The result of your split (db) will only have a length of 4 for members without a phone number, meaning its highest index will be 3.

int no = parseInt(db[0]), tel = parseInt(db[4]);

tries to access an index that is out of bounds for members without a phone number. Consider moving the declaration tel = parseInt(db[4]) inside the if statement that deals with members who do have a phone number, since it will only relevant for those cases anyways.

1 Comment

Thank you for the thorough explanation! It makes sense now that you've explained it, but I was stuck with it for a long time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.