Rawquery(Query, Selectionargs)

rawQuery(query, selectionArgs)

rawQuery("SELECT id, name FROM people WHERE name = ? AND id = ?", new String[] {"David", "2"});

You pass a string array with an equal number of elements as you have "?"

How to Convert rawQuery() to query() with LIKE selectionArgs

If possible, it's best to use the query() method, as it handles properly escaping the input.

In your case, it would be something like this:

Cursor res = mDatabase.query(tableName, 
     null, 
     colomn1 + " like ? and " + colomn2 + " like ?", 
     new String[]{string1, string2}, 
     null, null, null);

Android sqlite rawquery selection args problems

From developer.android.com, you only can put "?" on where clause, not for columns you want to query:

• selectionArgs: You may include ?s in where clause in the query, which will be replaced by the values from selectionArgs. The values will be bound as Strings.

Is rawQuery broken when using selectionArgs with INNER JOIN

Resolution

In the hope of helping others who land on this question when working on a similar problem, here is what I determined. Every query that was failing (returning 0 rows) had two things in common.

1. All the queries involved an inner join in which a column in the joined table was to be used to restrict the returned rows (via inner_table.column=? with selectionArgs used to provide the replacement value);
2. Every inner table column that was to be used to restrict the returned rows was the result of an aggregate function of some kind (count, sum).

The fact that all failing queries had an inner join blinded me to the fact that all the columns for which parameter replacement was failing were aggregate functions.

These are the SQL commands used to define and populate the tables involved in the queries:

CREATE TABLE IF NOT EXISTS `organism` (
  `org_id` INT NOT NULL,
  `org_name` INT NULL,
  `mat_id` INT NULL,
  `pat_id` INT NULL,
  `map_id` INT NOT NULL,
  `bbch_stage` INT NULL,
  -- other columns not relevant to problem
  PRIMARY KEY (`org_id`),
  -- constraints not relevant to problem
);
-- indexes not relevant to problem

CREATE TABLE IF NOT EXISTS `gene_sequence` (
  `org_id` INT NOT NULL,
  `gene_id` INT NOT NULL,
  `allele_id` INT NOT NULL,
  -- constraints not relevant to problem
);
-- indexes not relevant to problem

CREATE TABLE IF NOT EXISTS `organism_phenotype` (
  `org_id` INT NOT NULL,
  `map_id` INT NOT NULL,
  `phenogroup_id` INT NOT NULL,
  `phenotype_id` INT NOT NULL,
  -- constraints not relevant to problem
);
-- indexes not relevant to problem

INSERT INTO `organism`
   (`org_id`, `org_name`, `mat_id`, `pat_id`,  `map_id`, `bbch_stage` /* other columns omitted for brevity */)
   VALUES (0, "homozygous dominant starter plant", null, null, 0, 9 /* other columns omitted for brevity */);
-- Yes, gene_sequence contains duplicate records; this is valid for the
-- problem domain space 
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 0, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 0, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 1, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 1, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 2, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 2, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 3, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 3, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 4, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 4, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 5, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 5, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 6, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 6, 0);
INSERT INTO `organism_phenotype` (`org_id`,`map_id`,`phenogroup_id`,`phenotype_id`) VALUES (0, 0, 0, 0);
INSERT INTO `organism_phenotype` (`org_id`,`map_id`,`phenogroup_id`,`phenotype_id`) VALUES (0, 0, 1, 0);
INSERT INTO `organism_phenotype` (`org_id`,`map_id`,`phenogroup_id`,`phenotype_id`) VALUES (0, 0, 2, 0);
INSERT INTO `organism_phenotype` (`org_id`,`map_id`,`phenogroup_id`,`phenotype_id`) VALUES (0, 0, 3, 0);
INSERT INTO `organism_phenotype` (`org_id`,`map_id`,`phenogroup_id`,`phenotype_id`) VALUES (0, 0, 4, 0);
INSERT INTO `organism_phenotype` (`org_id`,`map_id`,`phenogroup_id`,`phenotype_id`) VALUES (0, 0, 5, 0);
INSERT INTO `organism_phenotype` (`org_id`,`map_id`,`phenogroup_id`,`phenotype_id`) VALUES (0, 0, 6, 0);

When searching for an organism from the database, the user may specify one or more phenotype values or alleles the organism must exhibit/possess. These restrictions are applied by building temporary tables based on the specified conditions. For phenotype, the generated query string looks like:

CREATE TEMP TABLE pheno_temp1 AS
  SELECT org_id,count(org_id) AS 'pheno_count'
    FROM organism_phenotype
    WHERE (phenogroup_id=? AND phenotype_id=?)
      -- the OR clause appears optionally for each phenotype specified
      -- beyond the first
      OR (phenogroup_id=? AND phenotype_id=?)
    GROUP BY org_id

The string is passed in to rawQuery with a String[] specifying the replacement values for the parameters.

So, if the user desires a list of all pea plants exhibiting purple flowers (phenogroup=0,phenotype=0) and axial flower placement (phenogroup=1,phenotype=0) the software will define the query string and selectionArgs as follows:

String queryString =
  "CREATE TEMP TABLE pheno_temp1 AS" +
    " SELECT org_id,count(org_id) AS 'pheno_count'" +
      " FROM organism_phenotype" +
      " WHERE (phenogroup_id=? AND phenotype_id=?)" +
        " OR (phenogroup_id=? AND phenotype_id=?)" +
      " GROUP BY org_id";
String[] selectionArgs = {"0","0","1","0"};

This works (mostly) as expected and creates a table with the expected contents:

table pheno_temp1
org_id   pheno_count
--------------------
     0             2

The process for filtering on alleles is similar but involves two levels of aggregation and a DISTINCT operation (and since the underlying problem is the same, I omit those details here).

After building the temporary tables, the next step of the process is to select org_id values meeting all the user's criteria by joining organism to pheno_temp1 and geno_temp2 (the final work table from allele filtering). The query for this step would typically look as follows:

SELECT organism.org_id FROM organism
  INNER JOIN pheno_temp1 ON organism.org_id=pheno_temp1.org_id
  INNER JOIN geno_temp2 ON organism.org_id=geno_temp2.org_id
    WHERE
      -- the query will include one or more of these clauses depending
      -- on the user's selections; the software takes care of adding the
      -- appropriate clauses and necessary glue (AND) between clauses
      pheno_temp1.pheno_count=?
      AND geno_temp2.allele_sum=?
      -- the query will include at most one of the birth_date clauses
      AND organism.birth_date<?
      --AND organism.birth_date=?
      --AND organism.birth_date>?
      --AND organism.birth_date BETWEEN ? AND ?
      -- the bbch_stage clause includes one parameter marker for each
      -- bbch stage selected by the user
      AND organism.bbch_stage IN (?,?,?)
      -- maternal and paternal lineage
      AND organism.mat_id=?
      AND organism.pat_id=?
    ORDER BY organism.org_id

If the user has requested a single phenotype (so phenocount = 1) and a single bbch stage (9), the sql query string and selection arguments would be defined as follows:

String queryString =
  "SELECT organism.org_id FROM organism" +
    " INNER JOIN pheno_temp1 ON organism.org_id=pheno_temp1.org_id" +
      " WHERE pheno_temp1.pheno_count=?" +
        " AND organism.bbch_stage IN (?)" +
      " ORDER BY organism.org_id";
String[] selectionArgs = {"1","9"};

The SQL query and the selection arguments are both correct; the data in organism and pheno_temp1 suggest that the rawQuery call should return a single row with org_id=0 (well strictly speaking a cursor on a result set containing a single row for the organism record with org_id=0, but I'm going to assume from this point forward that everyone reading this knows what I mean).

Here's the problem: the query returns no rows. For some reason the aggregate columns from the CREATE TABLE AS SELECT queries cannot be used with parameter markers in rawQuery() (and in fact my testing showed they cannot be used in query() either).

The solution turns out to be changing the way the temporary tables are built for the phenotype and allele filtering. Instead of using a CREATE TABLE AS SELECT statement, the table is explicitly created in an earlier step:

_mendelDatabase.execSQL("CREATE TEMP TABLE pheno_temp1 ('org_id' INT NOT NULL,'pheno_count' INT NOT NULL");

The query string to populate this table is then changed to:

String queryString =
  "INSERT INTO pheno_temp1 (org_id,pheno_count)" +
    " SELECT org_id,count(org_id) AS 'pheno_count'" +
      " FROM organism_phenotype" +
      " WHERE (phenogroup_id=? AND phenotype_id=?)" +
        " OR (phenogroup_id=? AND phenotype_id=?)" +
      " GROUP BY org_id";

(No change is necessary to selectionArgs.)

One final observation: many (not all) of the questions about parameter markers and selection arguments imply that they may only be used when the underlying table columns are string types. This is demonstrably not true; all the code above is pulled directly from the app where I uncovered this problem and most of the columns used with parameter replacement are integer types; I attest that with the change made to how the temporary table is created, the code works properly.

I'd like to acknowledge CL's time and effort. Although CL did not provide the actual resolution to the problem, the back-and-forth did eventually lead to a test case that pointed in direction of the answer (and in fairness to CL the original question did not state that the inner join columns were from aggregate functions).

Using selectionArgs doesn't work in a query

You have to append the % to the selectionArgs itself:

selectionArgs = new String[] { searchString + "%" };

Cursor c = db.rawQuery("SELECT column FROM table WHERE column=?", selectionArgs);

Note: Accordingly % and _ in the searchString string still work as wildcards!

Android: SQL rawQuery with wildcard (%)

You have to append the % to the selectionArgs itself:

selectionArgs = new String[] { searchString + "%" };
Cursor c = db.rawQuery("SELECT column FROM table WHERE column=?", selectionArgs);

Note: Accordingly % and _ in the searchString string still work as wildcards!

SQLite rawquery selectionArgs not working

When you work with cursors in Android, remember to use the moveToFirst function before reading the data. Otherwise it will result in ArrayOutOfBounds exceptions.

---

Related Topics