How to check if there is no same row in DB?

Question

I have two method's in my SQLite Database in which i check if there is in a table certain column that is the same as a String after it i print all other columns on the same row and with the second method i check if a column from the first method equals to a data stored in a column of another table.

But i'm having issues when i check for a data that is not in the database here is an example:

TABLE CODART_BARCODE

CODART_CODART     CODART_BARCODE    CODART_PXC
      123              1234             1

TABLE CODART_ART

 DESCR_ART           PVEN_ART            PACQ_ART     CODART_ART
   PIZZ                1.50                12              123

So if in an EditText i insert 123 that equals to CODART_CODART and there is also 123 in CODART_ART from the other table i will print "PIZZ 1.50 12" but if i insert in the EditText 12356 the app crash because there is no same data in DB how can i prevent that app crash? i mean if there is no same data can i make a Toast that says "no data" or something like this but not making the app crash?

Here are the two methods from DB:

        public String dbRawSearch(String id) {
            StringBuilder dbString = new StringBuilder();
            SQLiteDatabase db = this.getWritableDatabase();
            String query = "SELECT * FROM " + TABLE_CODART + " WHERE CODART_BARCODE = " + id;
            //Cursor points to a location in your results
            @SuppressLint("Recycle") Cursor c = db.rawQuery(query, null);
            //Move to the first row in your results
            c.moveToFirst();
            //Position after the last row means the end of the results
            while (!c.isAfterLast()) {
                if (c.getString(c.getColumnIndex("CODART_BARCODE")) != null) {
                    dbString.append(c.getString(c.getColumnIndex("CODART_CODART"))).append("\n");
                    dbString.append(c.getString(c.getColumnIndex("CODART_BARCODE"))).append("\n");
                    dbString.append(c.getString(c.getColumnIndex("CODART_PXC"))).append("\n");
                }
                c.moveToNext();
            }
            db.close();
            return dbString.toString();
        }


        // FETCH codArt from Articoli

public String dbRawArticoli(String id){
        StringBuilder dbString = new StringBuilder();
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "SELECT * FROM " + TABLE_ART + " WHERE CODART_ART = " + id;
        Cursor c = db.rawQuery(query, null);
        c.moveToFirst();

    while (!c.isAfterLast()) {
        if (c.getString(c.getColumnIndex("CODART_ART")) != null) {
            dbString.append(c.getString(c.getColumnIndex("DESCR_ART"))).append("\n");
            dbString.append(c.getString(c.getColumnIndex("PVEN_ART"))).append("\n");
            dbString.append(c.getString(c.getColumnIndex("PACQ_ART"))).append("\n");
        }
        c.moveToNext();
    }
    db.close();
    return dbString.toString();

}

Answer 1

Your issue is that you are not correctly enclosing the search argument and thus if the value is non numeric then SQLite will consider that you are comparing a column, hence the no column found.

Lets say assuming you use :-

 String result1 = yourdbHelper.dbRawSearch("123");

Then the resultant SQL will be :-

 SELECT * FROM CODART WHERE CODART_BARCODE = 123;

That is fine as the search is looking for a number.

However if you used:-

 String result1 = yourdbHelper.dbRawSearch("Fred");

Then the resultant SQL will be :-

 SELECT * FROM CODART WHERE CODART_BARCODE = FRED

This would fail because FRED is non-numeric, and is therefore interpreted as saying SELECT all columns from the table CODART where the column named COADRT has the same value as the column named FRED, there is no column named FRED.

The result is that you get an error along the lines of :-

06-11 11:34:12.653 1373-1373/soanswers.soanswers E/AndroidRuntime: FATAL EXCEPTION: main
    java.lang.RuntimeException: Unable to start activity ComponentInfo{soanswers.soanswers/soanswers.soanswers.MainActivity}: android.database.sqlite.SQLiteException: no such column: FRED (code 1): , while compiling: SELECT * FROM CODART WHERE CODART_BARCODE = FRED
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2059)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
        at android.app.ActivityThread.access$600(ActivityThread.java:130)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        at android.os.Looper.loop(Looper.java:137)
        at android.app.ActivityThread.main(ActivityThread.java:4745)
        at java.lang.reflect.Method.invokeNative(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:511)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
        at dalvik.system.NativeStart.main(Native Method)

The Fix

The resolution is simple, and that is to enclose the argument being searched for in single quotes so that the SQL is then :-

SELECT * FROM CODART WHERE CODART_BARCODE = 'FRED'

• Note that is just one example. However you will need to makes similar changes to both methods (dbRawSearch and dbRawArticoli), as shown :-

To do this you could change :-

String query = "SELECT * FROM " + TABLE_CODART + " WHERE CODART_BARCODE = " + id;

to :-

String query = "SELECT * FROM " + TABLE_CODART + " WHERE CODART_BARCODE = '" + id + "'";

and also change :-

String query = "SELECT * FROM " + TABLE_ART + " WHERE CODART_ART = " + id;

to :-

String query = "SELECT * FROM " + TABLE_ART + " WHERE CODART_ART = '" + id + "'";

Additional

However, there are SQLiteDatabase convenience methods that simplify building queries which also enclose/convert data accordingly.

One of these is the query method (as used in the following).

Rather than

1. moving to the first row and then
2. checking to see if you are then at the last row and then
3. using a moveToNext then going back to 2

in a do while loop, as all of the Cursor move??? methods return

• true if the move could be made
• otherwise false

you can simplify matters using :-

while(yourcursor.moveToNext) {
   .... process the current row
}

As such the following methods could be considered

• Note the 2 at the end of the method name is just to distinguish them from the originals

:-

public String dbRawSearch2(String id) {
    StringBuilder dbString = new StringBuilder();
    String whereclause = "CODART_BARCODE=?";
    String[] whereargs = new String[]{id};
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor c = db.query(TABLE_CODART,null,whereclause,whereargs,null,null,null);
    while (c.moveToNext()) {
        dbString.append(c.getString(c.getColumnIndex("CODART_CODART"))).append("\n");
        dbString.append(c.getString(c.getColumnIndex("CODART_BARCODE"))).append("\n");
        dbString.append(c.getString(c.getColumnIndex("CODART_PXC"))).append("\n");
    }
    c.close(); //<<<< Should always close cursors when finished with them
    db.close();
    return dbString.toString();
}

public String dbRawArticoli2(String id) {
    StringBuilder dbString = new StringBuilder();
    String whereclause = "CODART_ART=?";
    String[] whereargs = new String[]{id};
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor c= db.query(TABLE_ART,null,whereclause,whereargs,null,null,null);
    while (c.moveToNext()) {
        dbString.append(c.getString(c.getColumnIndex("DESCR_ART"))).append("\n");
        dbString.append(c.getString(c.getColumnIndex("PVEN_ART"))).append("\n");
        dbString.append(c.getString(c.getColumnIndex("PACQ_ART"))).append("\n");
    }
    c.close();
    db.close();
    return dbString.toString();
}

Answer 2

Change this part :
//Move to the first row in your results
            c.moveToFirst();
            //Position after the last row means the end of the results
            while (!c.isAfterLast()) {
                if (c.getString(c.getColumnIndex("CODART_BARCODE")) != null) {
                    dbString.append(c.getString(c.getColumnIndex("CODART_CODART"))).append("\n");
                    dbString.append(c.getString(c.getColumnIndex("CODART_BARCODE"))).append("\n");
                    dbString.append(c.getString(c.getColumnIndex("CODART_PXC"))).append("\n");
                }
                c.moveToNext();
            }

To :
//Move to the first row in your results
            if(c!= null && c.moveToFirst())
{
            //Position after the last row means the end of the results
            while (!c.isAfterLast()) {
                if (c.getString(c.getColumnIndex("CODART_BARCODE")) != null) {
                    dbString.append(c.getString(c.getColumnIndex("CODART_CODART"))).append("\n");
                    dbString.append(c.getString(c.getColumnIndex("CODART_BARCODE"))).append("\n");
                    dbString.append(c.getString(c.getColumnIndex("CODART_PXC"))).append("\n");
                }
                c.moveToNext();
            }
}

Explanation: In the case where there is no same data available you don't have the result set to get the string or column index from the result set.

Answer 3

you should use wether your cursor is null or not and its size

if (c != null) {
    if (c.getCount() > 0) {
       return "your string";         
    }
}
return "";// In case no record found

In blank case give proper msg to the end user.