SQLite in Android error insert

Question

I need to run the following code every 30 seconds.

public void push(Context context,String codigoEvento){
    SessionManager manager = new SessionManager();
    folioEvento = manager.getValue(context,"folioEvento");
    nombreCliente = manager.getValue(context,"nombreCliente");
    nombreUser = manager.getValue(context, "nombreUser");
    hashUser = manager.getValue(context, "hashUsuario");
    if(!(folioEvento.equals("") || nombreCliente.equals(""))){
        Connection cn = new Connection();
        if(cn.isNetworkAvailable(context)){
        String ids = "";
        SQLiteDatabase db = TicketsBaseDatos.getInstance(context).getWritableDatabase();
        Cursor c = db.rawQuery("SELECT inscripcion_id FROM Inscripcion WHERE validado=1 and sincronizado = 1",null);
        if (c.moveToFirst()) {
             do {
                ids += c.getString(0)+",";
                ContentValues valores = new ContentValues();
                valores.put("sincronizado","1");
                String[] args = new String[]{c.getString(0), codigoEvento};
                db.update("Inscripcion", valores, "inscripcion_id=? AND codigo_evento=?", args);
             } while(c.moveToNext());
        }
        db.close();
        c.close();
        JSONParser jParser = new JSONParser();
        JSONObject json = jParser.getJSONFromUrl(Config.URL_BASE+nombreCliente+"/"+Config.URL_PUSH+nombreUser+"/"+hashUser+"/"+folioEvento+"/"+ids);
        try {
              String status = json.getString("status");
              if(status.equals("1")){
                  System.out.println("BIEN");
              }
              else{
                  System.out.println("MAL");
              }
          } catch (JSONException e) {
            e.printStackTrace();
          }
        }
    }
}

Following that, the user will be able to download data from a Web service, is the following code, this task, I do it within a AsyncTask :

public void sincronizarBD(Context context, String codigoEvento) {
    Connection cn = new Connection();
    if(cn.isNetworkAvailable(context)){
        SessionManager manager = new SessionManager();
        folioEvento = manager.getValue(context,"folioEvento");
        nombreCliente = manager.getValue(context,"nombreCliente");
        codigoUser = manager.getValue(context,"codigoUser");
        nombreUser = manager.getValue(context, "nombreUser");
        hashUser = manager.getValue(context, "hashUsuario");
        JSONParser jParser = new JSONParser();
        BaseDeDatos nueva = new BaseDeDatos();
        try {
              do{
                  int recorrido = 0;
                  JSONObject json = jParser.getJSONFromUrl(Config.URL_BASE+nombreCliente+"/"+Config.URL_DESCARGA_BD+nombreUser+"/"+hashUser+"/"+folioEvento+"/"+start+"/"+cantidad);
                  JSONArray array = json.getJSONArray("Inscripciones");
                  for (int i = 0; i < array.length(); i++) {
                          recorrido++;
                          JSONObject rowParticipante = array.getJSONObject(i);
                              SQLiteDatabase db = TicketsBaseDatos.getInstance(context).getWritableDatabase();
                              System.out.println(i);
                              ContentValues nuevoRegistro = new ContentValues();
                              nuevoRegistro.put("hash", rowParticipante.getString("hash"));
                              nuevoRegistro.put("codigo_evento", rowParticipante.getString("codigo_evento"));
                              nuevoRegistro.put("ticket",rowParticipante.getString("ticket"));
                              nuevoRegistro.put("nombre",rowParticipante.getString("nombre"));
                              nuevoRegistro.put("inscripcion_id",rowParticipante.getString("inscripcion_id"));
                              nuevoRegistro.put("validado", rowParticipante.getInt("validado"));
                              nuevoRegistro.put("sincronizado", 0);
                              nuevoRegistro.put("numero", rowParticipante.getString("numero"));
                              nuevoRegistro.put("categoria", rowParticipante.getString("categoria"));
                              nuevoRegistro.put("codigo_usuario", codigoUser);
                              nuevoRegistro.put("rut", rowParticipante.getString("rut_clean"));
                              nuevoRegistro.put("talla", rowParticipante.getString("talla"));
                              System.out.println("Guardado con exito");
                              db.insert("Inscripcion", null, nuevoRegistro);
                              nuevoRegistro.clear();
                              db.close();
                  }
                  if(recorrido>0){
                      start = start + cantidad;
                  } else {
                      continuar = false;
                  }
              }while(continuar == true);
      } catch (JSONException e) {
        e.printStackTrace();
      }
    }
}

Here is the error when I do "pull" while download in "sincronizarBD" throws me error.

03-28 02:30:14.547: E/AndroidRuntime(16117): FATAL EXCEPTION: AsyncTask #3

03-28 02:30:14.547: E/AndroidRuntime(16117): java.lang.RuntimeException: An error occured while executing doInBackground() 03-28 02:30:14.547: E/AndroidRuntime(16117): at android.os.AsyncTask$3.done(AsyncTask.java:299) 03-28 02:30:14.547: E/AndroidRuntime(16117): at java.util.concurrent.FutureTask.finishCompletion(FutureTask.java:352) 03-28 02:30:14.547: E/AndroidRuntime(16117): at java.util.concurrent.FutureTask.setException(FutureTask.java:219) 03-28 02:30:14.547: E/AndroidRuntime(16117): at java.util.concurrent.FutureTask.run(FutureTask.java:239) 03-28 02:30:14.547: E/AndroidRuntime(16117): at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1080) 03-28 02:30:14.547: E/AndroidRuntime(16117): at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:573) 03-28 02:30:14.547: E/AndroidRuntime(16117): at java.lang.Thread.run(Thread.java:856) 03-28 02:30:14.547: E/AndroidRuntime(16117): Caused by: java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase: /data/data/com.androidaz.scanner/databases/Eventrid 03-28 02:30:14.547: E/AndroidRuntime(16117): at android.database.sqlite.SQLiteClosable.acquireReference(SQLiteClosable.java:55) 03-28 02:30:14.547: E/AndroidRuntime(16117): at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1543) 03-28 02:30:14.547: E/AndroidRuntime(16117): at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1445) 03-28 02:30:14.547: E/AndroidRuntime(16117): at com.androidaz.scanner.Sincronizacion.sincronizarBD(Sincronizacion.java:73) 03-28 02:30:14.547: E/AndroidRuntime(16117): at com.androidaz.scanner.MenuEvento$sincronizarBD.doInBackground(MenuEvento.java:205) 03-28 02:30:14.547: E/AndroidRuntime(16117): at com.androidaz.scanner.MenuEvento$sincronizarBD.doInBackground(MenuEvento.java:1) 03-28 02:30:14.547: E/AndroidRuntime(16117): at android.os.AsyncTask$2.call(AsyncTask.java:287) 03-28 02:30:14.547: E/AndroidRuntime(16117): at java.util.concurrent.FutureTask.run(FutureTask.java:234)

My SQLhelper

public class TicketsBaseDatos extends SQLiteOpenHelper {

String sqlCreateBDEvent = "CREATE TABLE IF NOT EXISTS Evento  (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, codigo_evento TEXT , nombre TEXT, codigo_usuario TEXT, folio TEXT, nombre_cliente TEXT)";
String sqlCreateBDInscripcion = "CREATE TABLE IF NOT EXISTS Inscripcion (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, hash TEXT, codigo_evento TEXT, ticket TEXT, nombre TEXT, inscripcion_id TEXT, validado int, sincronizado int, numero TEXT, categoria TEXT, codigo_usuario TEXT, rut TEXT, talla TEXT)";
private static TicketsBaseDatos sInstance;
public static TicketsBaseDatos getInstance(Context context) {
    if (sInstance == null) {
      sInstance = new TicketsBaseDatos(context.getApplicationContext());
    }
    return sInstance;
  }


public TicketsBaseDatos(Context ctx) {
    super(ctx, "Eventrid", null, 1);
}

}

To run for a while my push action, occupy Timer :

@Override
public int onStartCommand(Intent intent, int flags, int startId) {
    Sincronizacion s = new Sincronizacion();
    SessionManager ss = new SessionManager();
    try{
        codigoEvento = ss.getValue(this,"codigoEvento");
        folioEvento = ss.getValue(this,"folioEvento");
        nombreCliente = ss.getValue(this,"nombreCliente");
    if((codigoEvento != null) && (!codigoEvento.equals("") && (folioEvento != null) && (!folioEvento.equals("") && (nombreCliente != null) && (!nombreCliente.equals(""))))){
            s.push(getApplicationContext(), codigoEvento);
            s.pull(getApplicationContext(), codigoEvento);
            Toast.makeText(this, "PULL Y PUSH", Toast.LENGTH_SHORT).show();
        }else{
            Toast.makeText(this, "Push sin codigo", Toast.LENGTH_SHORT).show();
        }
    }catch(Exception e){
        Toast.makeText(this, "NO HAY CODIGOS", Toast.LENGTH_SHORT).show();
    }
    return START_STICKY;
}


cronTimerTask = new TimerTask() {
     @Override
     public void run() {
         startService(new Intent(getBaseContext(), CronPush.class));
     }
};
cron = new Timer();
cron.schedule(cronTimerTask, 0, 30000);

Because I threw this error? Any solution?

score 0 · Answer 1 · answered Mar 27 '14 at 19:20

0

This exception is thrown because you have closed the db connection and then you try to do something else with it, the problem in your code is on this lines:

for(..){
    SQLiteDatabase db = TicketsBaseDatos.getInstance(context).getWritableDatabase();                           
    ...
    db.insert("Inscripcion", null, nuevoRegistro);
    nuevoRegistro.clear();
    db.close();
}

You should not ask a the db each time on your for loop, its better (and faster) to request it only one time and close it at the end like this:

SQLiteDatabase db = TicketsBaseDatos.getInstance(context).getWritableDatabase();                           
for(..){
            ...
    db.insert("Inscripcion", null, nuevoRegistro);
    nuevoRegistro.clear();
}
db.close();

answered Mar 27 '14 at 19:20

JavierSP1209

899
8
17

I made the changes you mentioned but the error persists. – Nico1991 Mar 27 '14 at 19:27
Ok, i have not notice the while loop outside the for, could you move both getWritable... and db.close outside this loop? – JavierSP1209 Mar 27 '14 at 19:39
Does not work, I have a timer running push the action, be that the problem? edit my post – Nico1991 Mar 27 '14 at 19:44
Yes! As Emanuel Moecklin said the problem is that you are using a SingleTon for the DB object so when other thread comes the DB its closed, you can use a new instance for each thread or use locks instead – JavierSP1209 Mar 27 '14 at 21:56
The problem is that not close, so I still throws errors. For example in one case occupied a progressdialog, not close the database the app freezes and stops working. – Nico1991 Mar 28 '14 at 12:41

score 0 · Answer 2 · edited May 23 '17 at 10:26

You're using a Singleton for your SQLiteOpenHelper which is a great idea if you want to access the db in multiple threads but not if you close it every once in a while. While one thread might close the db (like your push method), another thread (like your AsyncTask) tries to use the same - now closed - database.

There are two approaches to solve this:

Don't close the database unless it's no longer needed. This could be never for a ContentProvider (see When should I call close() on SQLiteOpenHelper used by ContentProvider), or in onDestroy() when it comes to Activities or Fragments.
Keep track of the close and open statements and only close it once all threads have finished what they are supposed to do. There's e.g. this https://github.com/d4rxh4wx/MultiThreadSQLiteOpenHelper or you could just add your own synchronization mechanism using Locks (e.g. a ReentrantReadWriteLock).

Google for SQLiteOpenHelper multithreading and you'll find plenty of posts about the issue you're facing.

I think you're absolutely right, try something. I put as singleton data based only on "sincronizarBD" and where more care of the common and usual way. But I still pitching erroreses now insertion. android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5) — Nico1991, Mar 27 '14 at 20:24
My guess is that you're now opening two SQLiteOpenHelper which would open the same database concurrently and that will inevitably fail but without seeing some actual code that's just fishing in the dark. — Emanuel Moecklin, Mar 28 '14 at 00:11

SQLite in Android error insert

2 Answers2