Android sqlite insert data SQLiteException

Question

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE);
        // providers data
//      db.execSQL("insert  into "
//              + TABLE_PROVIDERS
//              + " (_id, provider_id, parent_id, title, image, sh_n, enable, visible, image_bg, type, is_group, hide_in_search, limit_min, limit_max, off_on_line, invoice_search) "
//              + "VALUES (1,600,101,'Источники оплаты','',1,1,0,'','',1,1,0,0,0,0)");

        ContentValues cv = new ContentValues();
        cv.put("_id", 1);
        cv.put("provider_id", 600);
        cv.put("parent_id", 101);
        cv.put("title", "Bla");
        cv.put("image", "bla");
        cv.put("sh_n", 1);
        cv.put("enable", 1);
        cv.put("visible", 1);
        cv.put("image_bg", "");
        cv.put("type", "");
        cv.put("is_group", 1);
        cv.put("hide_in_search", 1);
        cv.put("limit_min", 10);
        cv.put("limit_max", 10000);
        cv.put("off_on_line", 1);
        cv.put("invoice_search", 1);

        db.insertOrThrow(TABLE_PROVIDERS, null, cv);
}

Now I'm doing as was suggested, inserting row by row:

import java.util.*;

import android.content.*;
import android.database.*;
import android.database.sqlite.*;

public class ProviderDataSource {

    private SQLiteDatabase database;
    private MySQLiteHelper dbHelper;

    public ProviderDataSource(Context context) {
        dbHelper = new MySQLiteHelper(context);
    }

    public void open() throws SQLException {
        database = dbHelper.getWritableDatabase();
    }

    public void close() {
        dbHelper.close();
    }

    public List<Provider> getProvidersByParentId(long parentId) {

        List<Provider> providersList = new ArrayList<Provider>();

        Cursor cursor = database.query(MySQLiteHelper.TABLE_PROVIDERS, new String[] { "provider_id", "title", "image" }, " parent_id=" + parentId,
                null, null, null, null);

        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            Provider provider = cursorToProvider(cursor);
            providersList.add(provider);
            cursor.moveToNext();
        }
        cursor.close();
        System.out.println("getProvidersByParentId");
        return providersList;
    }

    private Provider cursorToProvider(Cursor cursor) {

        Provider provider = new Provider();
        provider.setId(cursor.getInt(1));
        provider.setTitle(cursor.getString(3));
        provider.setImg(cursor.getString(4));

        return provider;

    }

}

It doesn't work. It seems like the row wasn`t inserted and the array returnd by getProvidersByParentId method is empty. Parent_id as argument is 101.

Yeah thanks, Ravi in the post below suggested me this with an array of ContentValues. — Arthur Kushman
– Arthur Kushman, Commented Apr 10, 2012 at 4:44

Community · Accepted Answer · 2017-05-23 12:27:49Z

4

Your insert statement is wrong. In SQLite, you cant insert multiple records by separating them with comma, in fact you need to prepare separate insert commands for that. But if your SQLite version is 3.7.11 then its possible.

Read this.

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Apr 9, 2012 at 11:08

waqaslam

68.3k17 gold badges172 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arthur Kushman Over a year ago

Ok, what if I have more than 1-10 rows to insert, like dump for example with 10000 rows - what should I do in this situation?

waqaslam Over a year ago

Begin an SQLite Transaction, use for-loop to add records and finally commit that transaction. It will be real fast

Avi Kumar · Accepted Answer · 2012-04-09 11:29:56Z

You can do it with a for loop like this

// add a button click event like

String[] arr1 = new String[n]; String[] arr2 = new String[n]; String[] arr3 = new String[n];

addButton.setOnClickListener
            (
                new View.OnClickListener()
                {
                    @Override public void onClick(View v) {


                               for(int i=1;1<4;i++){


            try
            {
                // ask the database manager to add a row given the two strings

                //db object of database manager class 

                db.addValues
                (
                    arr1[i] , 
                    arr2[i] , 
                    arr3[i] , 

                );


                           }
catch (Exception e)
            {
                Log.e("Add Error", e.toString());
                e.printStackTrace();
            }

                                }

                                     );

// function of insertion

public void addValues(String Col1, String Col2,
                          String col3)

{

ContentValues values = new ContentValues();




        values.put(Columns1, Col1);
        values.put(Columns2, Col2);
        values.put(Columns3, Col3);

try{
            db.insert(Course_Info_Table, null, values);


}
        catch(Exception e)
        {

        }

}

Ravi1187342 · Accepted Answer · 2012-04-10 10:28:15Z

0

use ContentValues to insert data into table like this.

SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();

        cv.put("_id", 1);
        cv.put("provider_id", 600);
        cv.put("parent_id", 0);

        cv.put("title", "Источники оплаты");
        cv.put("image", "");
        cv.put("sh_n", 1);
        cv.put("enable", 1);
        cv.put("visible", 0);
        cv.put("image_bg", "");
        cv.put("type", "");
        cv.put("is_group", 1);
        cv.put("hide_in_search", 1);
        cv.put("limit_min", 0);
        cv.put("limit_max", 0);
        cv.put("off_on_line", 0);
        cv.put("invoice_search", 0);
        db.insertOrThrow(TABLE_PROVIDERS, null, cv);
          db.close();

for multiple records call this methods multiple times.

public void insertMydata(int _id, int provider_id, int parent_id /*....add more parameters as you required */){

                SQLiteDatabase db = this.getWritableDatabase();
            ContentValues cv = new ContentValues();

            cv.put("_id", _id);
            cv.put("provider_id", provider_id);
            cv.put("parent_id", parent_id);
               /*
                 ......
                 ..... 
                 put more code here for other columns
                 ......
               */
                db.insertOrThrow(TABLE_PROVIDERS, null, cv);
                db.close();


}

edited Apr 10, 2012 at 10:28

answered Apr 9, 2012 at 11:46

Ravi1187342

1,2777 silver badges14 bronze badges

6 Comments

Arthur Kushman Over a year ago

Thank You very much, it seems like real desision - I'll try it later.

Arthur Kushman Over a year ago

Ok, what if I have more than 1-10 rows to insert, like dump for example with 10000 rows - what should I do in this situation?

Ravi1187342 Over a year ago

I have edited my post. put all code in a Method and call method multiple times for multiple rows you want to insert.

Arthur Kushman Over a year ago

That's ok but I will need to call this method 10000 times with multiple params and insert it by hands (not dynamically). I mean, I`ve got mysql dump and need to insert the data into SQLite. It is not user action etc.

Ravi1187342 Over a year ago

call this method 10000 times in a loop or manually for 10000 rows.

|

Collectives™ on Stack Overflow

Android sqlite insert data SQLiteException

3 Answers 3

2 Comments

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related