CODEWITHSUNDEEP
androiddatabasesqlitecursor
Pratik Mandrekar
Pratik Mandrekar

Reputation: 9568

Parametrized query android sqlite not working?

I have two queries, one with a ? which I substitute via the selection args and the other where the entire query has been prepared as a string. The query with ? does not seem to return any result and neither any error.

String query1 = "strftime('"
                + Constants.SQLITE_DATABAE_STRFORMAT
                + "', "+ COLUMN_NAME_DATE_CREATED +") BETWEEN strftime('"
                + Constants.SQLITE_DATABAE_STRFORMAT + "', '"+ sdf.format(fromDate) +"') AND strftime('"
                + Constants.SQLITE_DATABAE_STRFORMAT + "', '"+ sdf.format(toDate)+"')";

The query above returns results as expected.

String query2 = "strftime('"
                + Constants.SQLITE_DATABASE_STRFORMAT
                + "', "+ "?" +") BETWEEN strftime('"
                + Constants.SQLITE_DATABASE_STRFORMAT + "', "+ "?" +") AND strftime('"
                + Constants.SQLITE_DATABASE_STRFORMAT + "', "+ "?"+")";




    String[] selectionArgs = new String[] {
            COLUMN_NAME_DATE_CREATED,
            sdf.format(fromDate), sdf.format(toDate) };

Query 2 above returns no results. The cursor is created as shown below with the difference that for query 1 the selectionArgs is set to an empty array. 

Cursor cursor = queryBuilder.query(database.getReadableDatabase(),
                projection, selection, selectionArgs, null, null, sortOrder);

Upvotes: 0

Views: 341

Answers (3)

Pratik Mandrekar
Pratik Mandrekar

Reputation: 9568

The problem is that parameterized query with a strftime works only with the time stamps parameterized and not the column name for some reason.

So the below works 

String whereClause = "strftime('"
                + Constants.SQLITE_DATABASE_STRFORMAT + "', " + COLUMN_NAME_DATE_CREATED
                + ") BETWEEN strftime('" + Constants.SQLITE_DATABASE_STRFORMAT
                + "', " + "?" + ") AND strftime('"
                + Constants.SQLITE_DATABASE_STRFORMAT + "', " + "?" + ")";

String[] selectionArgs = new String[] {
        sdf.format(fromDate), sdf.format(toDate) };

Upvotes: 1

gssi
gssi

Reputation: 5071

You might also want to check out the spelling errors in query1 - i.e. 'DATABAE'

Upvotes: 0

Ashwini Bhangi
Ashwini Bhangi

Reputation: 291

Try this 

     String query2 = "strftime('"
              + Constants.SQLITE_DATABASE_STRFORMAT
              + "', "+ "?" +") BETWEEN strftime('"
              + Constants.SQLITE_DATABASE_STRFORMAT + "', "+ "?" +"' ) AND strftime('"
              + Constants.SQLITE_DATABASE_STRFORMAT + "', "+ "?"+"' )";

Upvotes: 0

Related Questions