Power By 专否社区

How to compare text data values that are in a sqlite database?

Go To StackoverFlow.com

1

Here I'm comparing text values in a sqlite database with strings(section,branch, etc...). Is it the correct way of comparing? I'm getting no such column error in query() statement.

public Cursor getAllRows(String year, String section, String branch,
                         String subject, String date) {    

  return db.query(
      DATABASE_TABLE,
      null, 
      KEY_YEAR + "=" + year + 
        " AND "+ KEY_SECTION + "=" + section +
        " AND "+ KEY_BRANCH + "=" + branch + 
        " AND "+ KEY_SUBJECT + "=" + subject + 
        " AND "+ KEY_DATE + "=" + date,
      null, 
      null, 
      null, 
      null
  );
}

logcat 

04-04 10:31:51.568: ERROR/AndroidRuntime(21928):
   Caused by:
     android.database.sqlite.SQLiteException:
       no such column: CSE: ,
       while compiling:
         SELECT * 
         FROM student 
         WHERE year=2 AND branch like CSE 
         AND subject=SE AND date=04-Apr-2012

04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:80)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:46)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:42)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1345)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1229)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1184)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1264)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at sharath.android.trail.DBAdapter.getAllRows(DBAdapter.java:100)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at sharath.android.trail.MytableActivity.onCreate(MytableActivity.java:52)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2627)
04-04 10:31:51.568: ERROR/AndroidRuntime(21928): ... 20 more

Actually CSE is one value that belongs to branch column, thanks in advance.

2012-04-04 04:48
by Sharath Vollala
post your logcat. - ngesh 2012-04-04 04:52
@sandy i have posted logca - Sharath Vollala 2012-04-04 05:06

2

String constants in a query need to be enclosed with single-quote (') characters. You are interpolating the strings without the quotes, so they are interpreted as column references.

Generally it is a bad idea to interpolate string values directly. You should create a statement with bind parameters.

This will automatically escape and quote the strings:

public Cursor getAllRows(String year, String section, String branch,
    String subject, String date)
{
    return db.rawQuery("SELECT * FROM " +
         DATABASE_TABLE + " WHERE " +
         KEY_YEAR + "=? AND " +
         KEY_SECTION + "=? AND " +
         KEY_BRANCH + "=? AND " +
         KEY_SUBJECT + "=? AND "+
         KEY_DATE + "=?",
         new String[] { year, section, branch, subject, date }
    );
}
2012-04-04 05:10
by Ted Hopp
thanks a lot... one more doubt i has if i have one integer value how can i bind in selectionArg - Sharath Vollala 2012-04-04 06:02
@kumar - You can convert it to a String with Integer.toString(value) - Ted Hopp 2012-04-04 14:31
thanks a lot............ - Sharath Vollala 2012-04-04 18:22

0

can you tell me KEY_YEAR,KEY_SECTION,KEY_BRANCH,KEY_SUBJECT,KEY_DATE are strings which represents column names or direct column names if this were strings which represents column names then

Please try this

    return db.query(DATABASE_TABLE,null, 
    KEY_YEAR + "='" + year +"' "
    "AND "+ KEY_SECTION + "='" + section +"' "
    "AND "+ KEY_BRANCH + "='" + branch + "' "
    "AND "+ KEY_SUBJECT + "='" + subject +"' "
    "AND "+ KEY_DATE + "='" + date+"'",
    null, 
    null, 
    null, 
    null);

or if this is direct column names please try this

return db.query(DATABASE_TABLE,null, 
         "KEY_YEAR='"+ year +"' AND"
         "KEY_SECTION='"+section +"' AND"
         "KEY_BRANCH='"+branch + "' AND"
         "KEY_SUBJECT='"+subject +"' AND"
         "KEY_DATE='"+date+"'",
         null, 
         null, 
         null, 
         null);
2012-04-04 05:41
by Manju
Ads