Parametrized query android sqlite not working?

Question

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

Pratik Mandrekar · Accepted Answer · 2013-05-01 07:09:41Z

1

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

edited May 1, 2013 at 7:09

answered Apr 30, 2013 at 21:07

Pratik Mandrekar

9,5764 gold badges49 silver badges67 bronze badges

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

Comments

Ashwini Bhangi · Accepted Answer · 2013-04-30 19:15:27Z

0

Try this

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

answered Apr 30, 2013 at 19:15

Ashwini Bhangi

2911 silver badge6 bronze badges

Comments

gssi · Accepted Answer · 2013-04-30 19:36:39Z

0

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

answered Apr 30, 2013 at 19:36

gssi

5,0875 gold badges25 silver badges23 bronze badges

Collectives™ on Stack Overflow

Parametrized query android sqlite not working?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related