ewom2468 ewom2468 - 17 days ago 6
Android Question

SQLite query keeps crashing

I have this query that returns a particular record based on some parameters passed within a form in my application. This is where I get the problem:

String[] args={"act_email,act_password"};
Cursor cursor= db.query(DB_TABLE_NAME,new String[]{"email","password"},"email"+" like" + " %?%"+"AND"+" password "+" like" + " %?%",new String[]{"act_email,act_password"},null,null, null);


Here is some code that I found online:

Cursor cursor= db.rawQuery("SELECT email,password FROM users WHERE email LIKE '%' || ? || '%' and password LIKE '%' || ? || '%'",args);


This code does not cause any problems but it does need some optimisation mainly because it always returns an empty value. Any suggestions on how to solve this problem?

Answer

Your args are incorrect as it is missing the quote marks between the two elements... you must have 2 separate strings between the curly brackets. This should read:

String[] args= {"act_email","act_password"};

Edit (Start)

Secondly you are missing the ' text qualifiers in your query selection string, for text fields these should be of the form:

field='?'

SQLite appears to handle the handle the quotes on its own, so all you need to do provide the where clause in the form:

field=? [ AND field2=? ... AND fieldn=? ]

and then supply the parameters in an args array. It appears that Androids OpenDatabaseHelper class handles the text qualifiers automatically depending on type.

Edit (End)

Please note that parameterized queries in using SQLite on Android in this way do not like % wilcards in the query string. To use wildcards place them in the arg value instead and this will be substituted in during execution. So your args become:

String[] args= {"%act_email%","%act_password%"};

OR if you want to use variables here:

String[] args= {"%" + act_email + "%", "%" + act_password+ "%"};

So the full code should look something like this:

String[] fields = {"email","password"};
String[] args= {"%" + act_email + "%", "%" + act_password+ "%"};

//do not use like for usr & pwd
String where = "email like ? AND password like ?";

Cursor cursor= db.query(DB_TABLE_NAME, fields , where, args,null, null, null);

Although I shoulld point out in the case of email and password you should not use like.

Change the where clause to:

String where = "email=? AND password =?";//do not use like for usr & pwd