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

Question

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.

The table only has a single record in:

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

set number of pubs is just a standard setter. Value in cursor is just 3 — AlwaysNeedingHelp
– AlwaysNeedingHelp, Commented May 2, 2019 at 12:17
Posted the code in the OP. It's ran from a desktop application. By running this method and then looking at the Database in dataGrip I can confirm the value of 23 is correctly uploaded. — AlwaysNeedingHelp
– AlwaysNeedingHelp, Commented May 2, 2019 at 12:26

forpas · Accepted Answer · 2019-05-02 12:51:14Z

1

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.

edited May 2, 2019 at 12:51

answered May 2, 2019 at 12:44

forpas

165k10 gold badges51 silver badges85 bronze badges

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

4 Comments

AlwaysNeedingHelp Over a year ago

Thanks forpas for taking a look we found the solution and it was a sillier error than 'missing a semi-colon bug'. Turns out the database we were looking at on dataGrip was not the same database the app was using. We were looking at a database with the same name we had used for testing earlier. Stupid mistake :') sorry for wasting your time!

forpas Over a year ago

Fine if it is solved. Also if this answer did not provide a solution you should not mark it as the right answer. But I'm still curious as why you have that loop in your code.

AlwaysNeedingHelp Over a year ago

Looking at the code the pub list bits should be pulled out of the for loop, that is my own error. PubLists have a list of Pub objects that are to be uploaded to a separate table, hence looping through and running insertPub() on each one (which does the uploading).

forpas Over a year ago

Yes but this executes the sql insert statement many times: once for each pub. Use the loop only to for insertPub() then.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related