CODEWITHSUNDEEP
androidsqliteandroid-sqlite
schwembo
schwembo

Reputation: 19

Cant get SQLite rawquery with strftime() to work in Android App

Im very new to Android / SQLite programming and trying around for hours now but i can't get it to work.

I have a Table in SQLite with Dates which are in format YYYY-MM-DD.

Now i want to make a Query where i get a Cursor to all Elements of a Certain month and i can't get it to work. The Dates are stored in the column "KEY_DATUM" of the TABLE_BIER.

Thank you in advance for any hints how to set up such a Query.

my Method for the Query:

public Cursor getCursorFuerMonat(int monat){
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor= db.rawQuery("SELECT "+columns+"FROM "+TABLE_BIER+" WHERE strftime('%m',"+KEY_DATUM+") = ?",new String[]{""+monat});
    cursor.moveToFirst();
    return cursor;
}

The Variable declarations:

public class DatabaseHandler extends SQLiteOpenHelper {
//Version der Datenbank
private static final int DATABASE_VERSION = 1;
// Name der Datenbank
private static final String DATABASE_NAME = "bierZaehlDB";

// Name der Tabelle
private static final String TABLE_BIER = "bierTabelle";

// Namen der Spalten
private static final String KEY_ID = "_id";
private static final String KEY_DATUM = "SpalteDatum";
private static final String KEY_ANZAHL = "SpalteAnzahl";
private String[] columns = {
        KEY_ID, KEY_ANZAHL, KEY_DATUM
};

Upvotes: 0

Views: 152

Answers (1)

FrankPl
FrankPl

Reputation: 591

First of all, adding an array (like columns) to a string uses the implicit array to sting conversion, which is the string [_id,SpalteDatum,SpalteAnzahl] including the square brackets.

And then, strftime('%m', ...) extracts a two digit month from the date field, hence for e. g. February you would get the string "02", not "2". You could use String.format() to format a number like that.

And finally, you are missing a space before the FROM.

I am not sure why you need to put table/column names in variables at all. Are you assuming you will execute the same code for other tables/columns ever in future? I would hard-code them into the string:

public Cursor getCursorFuerMonat(int monat){
    SQLiteDatabase db = this.getReadableDatabase();
    String select = "SELECT _id, SpalteDatum, SpalteAnzahl " + 
                    "  FROM bierTabelle " + 
                    " WHERE strftime('%m', SpalteDatum) = ?";
    Cursor cursor= db.rawQuery(select, new String[]{String.format("%02d", monat)});
    cursor.moveToFirst();
    return cursor;
}

Upvotes: 1

Related Questions