SQLite strange behavior

user_10094102 0
Viewed 205

I want to retrieve a particular cell from a table. I want alarm_music(TEXT) where I have the hours(INTEGER) and the minutes(INTEGER)(In the where clause) of the alarm stored in the alarm table.
code where the query is fired

mBhelperClass = new AlarmsDBhelperClass(this);
        db= mBhelperClass.getWritableDatabase();
        Cursor cursor = db.rawQuery("SELECT musicPath FROM alarms WHERE hours="+hour+" AND minutes="+min,null);
        Log.d("gaurnagSnooze",""+cursor.getCount());
        if (cursor.moveToFirst()) {
            songPath = cursor.getString(cursor.getColumnIndex(AlarmsDBhelperClass.MUSIC_PATH));
            Log.d("if executed! ","songpath initialized!");
        }
        cursor.close();

the moveToFirst() returns FALSE meaning no rows in the table.
but when I fire SELECT musicPath FROM alarms the moveToFirst() returns TRUE.
how is this possible?

android-sqlite android
4 Answers

Problem: The cursor.moveToFirst() returned FALSE meaning no Rows existing.

Solution: While inserting rows in the table I used a 24hour clock format Calendar's instance from the time picker and stored in the table. But when I tried to retrieve the value from the database using hours as the where clause I used 12Hour clock format Calendar's instance. which would for sure yield no output hence no rows returned as cursor and cursor.moveToFirst() returning FLASE.

user_100941020

This query is unsafe it may result on unwanted result. However, if you want to use unsafe mode change below query 

"SELECT musicPath FROM alarms WHERE hours="+hour+" AND minutes="+min

To

"SELECT musicPath FROM alarms WHERE hours= '"+hour+"' AND minutes= '"+min+"'"

add ' around the whereArgs.

Although it's correct but it's not recommended(unsafe)

it's recommended(safe) to include ? in where clause in the query and replace them by values from where args.

Cursor cursor = db.rawQuery("SELECT musicPath FROM alarms WHERE hours = ? AND minutes = ? ",new String[]{hour,min});
user_60717290

Maybe this answer will not help with your problem, but I hope it will help you with the code style.

You can interface like this for all you select queries

public interface SelectQuery {

    Cursor execute(SQLiteDatabase db);
}

And here your query's implementation by this interface

public class MusicPathsByHoursAndMinutesQuery implements SelectQuery {

    private static final String TAG = MusicPathsByHoursAndMinutesQuery.class.getSimpleName();

    private static final String PRECOMPILED_QUERY_STATEMENT =
            "SELECT musicPath FROM alarms WHERE hours = ? and minutes = ?";

    private final int hours;
    private final int minutes;

    public MusicPathsByHoursAndMinutesQuery(int hours, int minutes) {
        this.hours = hours;
        this.minutes = minutes;
    }

    @Override
    public Cursor execute(SQLiteDatabase db) {
        final Cursor cursor = db.rawQuery(PRECOMPILED_QUERY_STATEMENT, getQueryArgs());

        if (BuildConfig.DEBUG) {
            Log.d(TAG, "Query: " + PRECOMPILED_QUERY_STATEMENT);
            Log.d(TAG, "With args: " + Arrays.toString(getQueryArgs()));
            Log.d(TAG, "Returned cursor with " + cursor.getCount() + " rows.");
        }

        return cursor;
    }

    private String[] getQueryArgs() {
        return new String[]{
                String.valueOf(hours),
                String.valueOf(minutes)
        };
    }
}

And its usage (logging also enabled in debug mode)

Cursor cursor = new MusicPathsByHoursAndMinutesQuery(hours, minutes).execute(db);
user_39698800

try select all

Cursor cursor = db.rawQuery("SELECT musicPath FROM alarms WHERE 1=1",null);

then create your where clause like some body mentioned

your query was wrong

Cursor cursor = db.rawQuery("SELECT musicPath FROM alarms WHERE hours="+hour+" AND minutes="+min,null);
as min was not inside double quote +min

you could have done this 

   String sql = "SELECT musicPath FROM alarms WHERE hours="+hour+" AND minutes="+min + " ;";

 Cursor cursor = db.rawQuery("SELECT musicPath FROM alarms WHERE hours = ? AND minutes = ? ",new String[]{hour,min});

Sample:

try (Cursor cursor = db.rawQuery(
                    "select * from " + SQLiteHelper.TABLE_USER +
                            " where " + SQLiteHelper.USER_CLM_REVOKE_TX_ID + " = ?",
                    new String[]{revokeTxId})) {
                if (cursor.moveToFirst()) {
                    return createUserChannelFromCursor(cursor);
                } else {
                    return null;
                }
            }
user_22387450
Related