INSERT query in sqlite working in browser,but not in android

Go To StackoverFlow.com

0

i have been really spendin a lot of time on this code but i am not able to any reason why database is not working

I want to insert just one row.So I created this func:

void insertfav(String k) {

    myDataBase = khol();
    Cursor cur = myDataBase.rawQuery("SELECT " + colname + " from "
            + deptTable + " WHERE " + col3 + "=" + "'" + k + "'",
            new String[] {});
    cur.moveToFirst();
    String area = cur.getString(cur.getColumnIndexOrThrow(colname));
    cur = myDataBase.rawQuery("SELECT " + col2 + " from " + deptTable
            + " WHERE " + col3 + "=" + "'" + k + "'", new String[] {});
    cur.moveToFirst();
    String disease = cur.getString(cur.getColumnIndexOrThrow(col2));
    myDataBase.rawQuery(
            "INSERT INTO " + favtable + " ("+colname+", "+col2+", "+col3+")"
                    + "Values (" + "'" + area + "'" + ", " + "'" + disease
                    + "'" + ", " + "'" + k + "'" + ")", new String[] {});
    myDataBase.close();
}

This query is working fine when I run it in the sqlite browser but when this function is actually called while running the app in emulator, nothing gets inserted.

khol() is a function which opens the database.This is the code of khol() func:

public SQLiteDatabase khol() throws SQLException {

    // Open the database
    String myPath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(myPath, null,
            SQLiteDatabase.OPEN_READWRITE);
    return myDataBase;
}

where DB_PATH and DB_NAME are:

private static String DB_PATH = "/data/data/example.SQLitetest/";

private static String DB_NAME = "desicures.db";

although khol() is working properly in other queries but might be i am doing something wrong here...

Plz help me out...

2012-04-04 18:01
by user182022
Do you have any idea which part of the query isn't working? Could you trace out the generated SQL perhaps - Joachim Isaksson 2012-04-04 18:08
also.. do you see some errors in the logcat - Ovidiu Latcu 2012-04-04 18:09
the INSERT INTO query is not inserting the data into database. - user182022 2012-04-04 18:10
@Ovidiu Latcu..There are no errors in logca - user182022 2012-04-04 18:11
@user182022 : try to use execSQL instead of rawQueryρяσѕρєя K 2012-04-04 18:11
Start by watching the value the SQL you're actually running. In a debugger or with window.alert(). It's probably malformed by the value of one or more parameter strings - Seva Alekseyev 2012-04-04 18:28


0

Three things about your code

  • execSQL instead of rawQuery
  • use ? and params in the String[] - that's safer and can handle k = "ha'ck"
  • be nice and close Cursors

Like about so:

myDataBase = khol();
Cursor cur = myDataBase.rawQuery("SELECT " + colname + " from "
        + deptTable + " WHERE " + col3 + "=?",
        new String[] { k });
cur.moveToFirst();
String area = cur.getString(cur.getColumnIndexOrThrow(colname));
cur.close();
cur = myDataBase.rawQuery("SELECT " + col2 + " from " + deptTable
        + " WHERE " + col3 + "=?", new String[] { k });
cur.moveToFirst();
String disease = cur.getString(cur.getColumnIndexOrThrow(col2));
cur.close();

// either
myDataBase.execSQL(
        "INSERT INTO " + favtable + " ("+colname+", "+col2+", "+col3+")"
                + "Values (?, ?, ?)",
                new String[] { area, disease, k });
// or 
ContentValues cv = new ContentValues();
cv.put(colname, area);
cv.put(col2, disease);
cv.put(col3, k);
long newRowId = myDataBase.insert(favtable, colname, cv);

myDataBase.close();

And in addition you might need to check if Cursors are null / contain values. cur.getString could either give you a NullPointerException or an error because there is no Column (if your db has no data).

Also your query should not need multiple queries, you can request multiple columns at once:

cur = myDataBase.rawQuery("SELECT " + colname + "," + col2 + " from "
        + deptTable + " WHERE " + col3 + "=?", new String[] { k, k });
if (cur.moveToFirst()) {
    // starting at 0 columns are ordered in the way you select them
    String area = cur.getString(0);
    String disease = cur.getString(1);
}
2012-04-04 18:26
by zapl


1

You probably want to try using myDatabase.insert() instead of rawQuery.

2012-04-04 18:12
by Aurora


1

as doc says about rawQuery:

Runs the provided SQL and returns a Cursor over the result set.

and for execSQL(String sql) :

Execute a single SQL statement that is NOT a SELECT or any other SQL statement 
that returns data.

    Execute a single SQL statement that is not a query. For example, CREATE TABLE, 
DELETE,INSERT, etc. Multiple statements separated by ;s are not supported. it takes a 
write lock

try to use execSQL instead of rawQuery.like

myDataBase.execSQL(
            "INSERT INTO " + favtable + " ("+colname+", "+col2+", "+col3+")"
                    + "Values (" + "'" + area + "'" + ", " + "'" + disease
                    + "'" + ", " + "'" + k + "'" + ");");
2012-04-04 18:20
by ρяσѕρєя K
it also worked fine..thanks. - user182022 2012-04-04 18:33
then why u un-accept my ans - ρяσѕρєя K 2012-04-04 18:34
coz Chintan answered first. it was his solution that solved my problem first..and i thought i can accept more than one answer..that's why only..i am sorry. - user182022 2012-04-04 19:06
it's ok but see my comment time on your question and @Chintan answer - ρяσѕρєя K 2012-04-04 19:07
or u have vote optio - ρяσѕρєя K 2012-04-04 19:08
at that time i selected Chintan's answer coz he even explained the other method but your answer on how i can implement execSQL came later..hey i will post one another ques..and will select your answer if it will be the best irrespective of time constraint..no hard feelings mate. - user182022 2012-04-04 19:11
ko done im waiting.. but my comment come first from all answers so plz always response to comments when anyone give to your question. - ρяσѕρєя K 2012-04-04 19:18


0

the method rawQuery() returns cursor, you can not make insert using this method, so

myDataBase.rawQuery(
        "INSERT INTO " + favtable + " ("+colname+", "+col2+", "+col3+")"
                + "Values (" + "'" + area + "'" + ", " + "'" + disease
                + "'" + ", " + "'" + k + "'" + ")", new String[] {});

is wrong method.

you can use the insert() method, you can try followin

ContentValues values = new ContentValues();
values.put(colname , area);
values.put(col2 , disease);
values.put(col3 , k);
long rowID = db.insert(favtable, null, values);
System.out.println("Added Row No: "+rowID);
2012-04-04 18:17
by Chintan Raghwani
Thanks a lot..it worked fine..: - user182022 2012-04-04 18:23
Ads