Error in rawQuery() in SQLite

Question

public class DataBaseHelper extends SQLiteOpenHelper

 {
 public static final String DATABASE_NAME="register.db";
 public static final String TABLE_NAME="registration";
 public static final String COL_1="ID";
 public static final String COL_2="FirstName";
 public static final String COL_3="LastName";
 public static final String COL_4="Password";
 public static final String COL_5="Email";
 public static final String COL_6="Phone";

 public DataBaseHelper(Context context) {
    super(context,DATABASE_NAME,null,1);}

 @Override
 public void onCreate(SQLiteDatabase sqLiteDatabase) {
    sqLiteDatabase.execSQL("create table "+TABLE_NAME+"(ID INTEGER PRIMARY 
     KEY AUTOINCREMENT, FirstName TEXT,LastName TEXT,Password TEXT,Email 
     TEXT,Phone TEXT)");}

 @Override
 public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
    sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " +TABLE_NAME); //Drop 
    older table if exists
    onCreate(sqLiteDatabase);
}

 public int checkusercredentials(String email,String password){

    Log.i("INISDE","CHECK USER ");
    SQLiteDatabase db=this.getWritableDatabase();
    try{
       int i=0;
       Cursor c=null;
       c=db.rawQuery("select * from "+TABLE_NAME+"where "+ "Email=? and 
       Password=?",new String[]{email,password});
        c.moveToNext();
        i=c.getCount();
        c.close();
        return i;
    }catch(Exception e)
    {e.printStackTrace();}

    return 0;
}

In my Code, checkusercredentials is called when user clicks SIGNIN button.

The Error that I face is in c=db.rawQuery("select * from "+TABLE_NAME+"where "+ "Email=? and Password=?",new String[]{email,password});

I get an error in the '=' symbol after Email and the error reads Expected compound operator,join operator,..., WHERE got'='

The Logcat reads FATAL EXCEPTION: main

Process: com.example.android.login, PID: 26858

android.database.sqlite.SQLiteException: near "=": syntax error (code 1): , 
while compiling: SELECT * FROM registrationWHERE Email=? AND Password=?

at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native 
Method)
                                                                                 at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)

at 
android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)

at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)

at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)

at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)

at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)

at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
                                                                           at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1255)
                                                                           at com.example.android.login.DataBaseHelper.checkusercredentials(DataBaseHelper.java:62)
                                                                           at com.example.android.login.MainActivity$1.onClick(MainActivity.java:43)
                                                                           at android.view.View.performClick(View.java:5207)
                                                                           at android.view.View$PerformClick.run(View.java:21177)
                                                                           at android.os.Handler.handleCallback(Handler.java:739)
                                                                           at android.os.Handler.dispatchMessage(Handler.java:95)
                                                                           at android.os.Looper.loop(Looper.java:148)
                                                                           at android.app.ActivityThread.main(ActivityThread.java:5438)
                                                                           at java.lang.reflect.Method.invoke(Native Method)
                                                                           at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:738)
                                                                           at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:628)

WHAT IS THE ERROR?

missing space here: `"+TABLE_NAME+"where "` – Alex K. Jan 09 '18 at 15:07 — Alex K., Jan 09 '18 at 15:07
Q: `WHAT IS THE ERROR?` A: **Distraction**. – Phantômaxx Jan 09 '18 at 16:23 — Phantômaxx, Jan 09 '18 at 16:23

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

The issue is due to a space missing between the table name and the WHERE keyword thus it thinks that the table name is registrationWHERE.

So you need to change c=db.rawQuery("select * from "+TABLE_NAME+"where "+ "Email=? and Password=?",new String[]{email,password});

to

    c=db.rawQuery("select * from "+TABLE_NAME+" where "+ "Email=? and Password=?",new String[]{email,password});

If you then encounter or are encountering no such column delete the App's data and rerun the App. This would then cause the onCreate method to be invoked and re-create the table. Not understanding that the onCreate method only automatically runs when the database is created, is a very common cause of column's not existing as code is changed in the onCreate method to add columns but the method doesn't run.

If you then still encounter problems then edit your question and include a description along with the stack trace from the log.

EDIT

It appears that the second error is a bug (resolved in Android Studio 3.1) as per - <expr> expected, got '?'.

There are two was around this as per

    Cursor c; // No need for Cursor c = null; so just Cursor c;

    // Get-around 1 - pass where clause as a String.
    String whereclause = " WHERE Email=? AND Password=?"; // <<<<use string for the WHERE clause
    c = db.rawQuery("select * from "+
            TABLE_NAME+
            whereclause,//<<<< use string for the WHERE clause
        new String[]{email,password}
    );


    // Get-around 2 use query method as opposed to rawQuery method 
    c = db.query(TABLE_NAME,
        null,
        "Email=? AND Password=?",
        new String[]{email,password},
        null,null,null 
    );

Thanks a Lot Brother. Although it is showing a red underline under '=' symbol signifying error, There are no error reports in the LogCat and the Program works fine. — WALEED AHMED, Jan 10 '18 at 04:56
uhhm I see what you mean. You could use `c = db.query(TABLE_NAME,null,"Email=? AND Password=?",new String[]{email,password},null,null,null );` as an alternative. Not sure of the cause though, never seen that before, also I think there's another recent question re the ** expected, got '?'**. [Might be this](https://stackoverflow.com/questions/47021237/expr-expected-got) — MikeT, Jan 10 '18 at 05:13

score 0 · Answer 2 · answered Mar 09 '18 at 06:50

I did it like that...

String queryString = "SELECT * FROM " + DatabaseHelper.TABLE_NAME + " WHERE " //
                        + DatabaseHelper.COL_5 + "=? AND " + DatabaseHelper.COL_4 + "=?";
                cursor = db.rawQuery(queryString, new String[]{userName, passLogin});

and its working fine now

Error in rawQuery() in SQLite

2 Answers2

EDIT