Search code examples
javaandroidsqliteandroid-sqlite

Android app reading String columns correctly but not the third INTEGER (sqlite)

I have a strange issue that I cannot resolve for the life of me.

The sqlite database is simple. Two rows that are each String and one that is INTEGER.

enter image description here

The table only has a single record in:

enter image description here

   public PubList readDatabase(String currentPostcode) {
        databaseReader dbReader;
        dbReader = new databaseReader(this);

        try {
            dbReader.updateDataBase();
        } catch (IOException mIOException) {
            throw new Error("Unable to update database");
        }

        try {
            pubDatabase = dbReader.getReadableDatabase();
        } catch (SQLException mSQLException) {
            throw new Error("SQLException");
        }


        String query = "SELECT Town,PostcodeArea,NumberOfPubs FROM PubLists WHERE PostcodeArea = '" + currentPostcode.substring(0, 4) + "'";
        final Cursor cursor = pubDatabase.rawQuery(query, null);
        if (cursor.moveToNext()) {
            currentTown.setListName(cursor.getString(cursor.getColumnIndex("Town")));
            currentTown.setPostcode(cursor.getString(cursor.getColumnIndex("PostcodeArea")));
            currentTown.setNumberOfPubs(cursor.getInt(cursor.getColumnIndex("NumberOfPubs")));
        }
        cursor.close();
        return currentTown;
    }

With some debugging I can confirm both of the String columns are being pulled correctly, but the int column, which should be 23, ALWAYS returns 3 which I have no idea where the 3 is coming from.

Would love some pointers.

Edit: As asked, here is the code to upload a publist to the database. This code is ran from a javaFX application on a desktop.

public void uploadPubList(PubList pubList){
        String sql = "INSERT OR REPLACE INTO main.PubLists(Town,PostCodeArea,NumberOfPubs) VALUES(?,?,?)";
        try {
            Connection conn = this.connect();
            for (Pub pub : pubList.getPubs()) {
                PreparedStatement pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, pubList.getName());
                pstmt.setString(2, pubList.getPostCodeArea());
                pstmt.setInt(3, pubList.getPubs().size());
                pstmt.executeUpdate();
                insertPub(pubList, pub);
            }

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
Solution

  • In uploadPubList() why are you iterating over pubList.getPubs()?
    You want to insert only 1 row: the name of the town, the post code area and the number of pubs, right?
    Also, this line:

    insertPub(pubList, pub);

    what does it do?
    Remove the for loop and keep these:

    public void uploadPubList(PubList pubList){
    String sql = "INSERT OR REPLACE INTO main.PubLists(Town,PostCodeArea,NumberOfPubs) VALUES(?,?,?)";
    try {
        Connection conn = this.connect();
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, pubList.getName());
        pstmt.setString(2, pubList.getPostCodeArea());
        pstmt.setInt(3, pubList.getPubs().size());
        pstmt.executeUpdate();
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }
}

    Uninstall the app from the device so the database is deleted and rerun.