android.database.sqlite.SQLiteException: no such table:

Question

i'm trying to make an Android app with an SQlite database, but when i try to make a query to the table "clientes" shows me a RuntimeException. I have tried many things, I have found many solutions, but none works.

THe main class:

public class AndroidBaseDatos extends Activity {

private TextView texto;
private String nombre;
private DBHelper BD;
/**Array donde guardamos los nuevos dispositivos encontrados**/
private ArrayAdapter<String> query;
private ListView queryResult;

UsuariosSQLiteHelper usdbh =
        new UsuariosSQLiteHelper(this, "DBUsuarios", null, 1);
    SQLiteDatabase db;


@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_android_base_datos);

    texto = (TextView) findViewById(R.id.textView1);
    queryResult = (ListView) findViewById(R.id.listView1);
    BD=new DBHelper(this);
    BD.open();
}

public void onStart() {
    super.onStart();        
    //Base de datos database
            String[] campos = new String[] {"nombre", "apellidos", "edad"};
            String[] args = new String[] {"2"};
             db=usdbh.getReadableDatabase();
            Cursor c = db.query("clientes", campos, "_id=?", args, null, null, null);
             //Cursor c = db.query("Usuarios", campos, null, null, null, null, null);

    // Inicializa el array
            query = new ArrayAdapter<String>(this, R.layout.nombre_dispositivo);

 // Establece el Listview para los dispositivos nuevos


    //Nos aseguramos de que existe al menos un registro
    if (c.moveToFirst()) {
         //Recorremos el cursor hasta que no haya más registros
         do {
              nombre = c.getString(0);
              String apellidos = c.getString(1);
              int edad = Integer.parseInt(c.getString(2));
              Log.d("obteniendo datos", nombre);
              query.add(nombre);
              query.add(apellidos);
              query.add(String.valueOf(edad));
              //String email = c.getString(1);
         } while(c.moveToNext());
    }
    texto.setText(nombre);
    queryResult.setAdapter(query);
    db.close();
}

and the class that extends SQLiteOpenHelper

public class DBHelper extends SQLiteOpenHelper {

// Ruta por defecto de las bases de datos en el sistema Android
private static String DB_PATH = "/data/data/com.example.bbdd/databases/";

private static String DB_NAME = "database.db";

private SQLiteDatabase myDataBase;

private final Context myContext;

// Array de strings para su uso en los diferentes métodos

/**
 * Constructor Toma referencia hacia el contexto de la aplicación que lo
 * invoca para poder acceder a los 'assets' y 'resources' de la aplicación.
 * Crea un objeto DBOpenHelper que nos permitirá controlar la apertura de la
 * base de datos.
 * 
 * @param context
 */
public DBHelper(Context context) {

    super(context, DB_NAME, null, 1);
    myContext = context;
    myDataBase = this.getReadableDatabase();

}

/**
 * Crea una base de datos vacía en el sistema y la reescribe con nuestro
 * fichero de base de datos.
 * */
public void createDataBase() throws IOException {

    boolean dbExist = checkDataBase();
    SQLiteDatabase db_Read = null;

    if (dbExist) {
        // la base de datos existe y no hacemos nada.
        Log.i("creando base de datos", "Ya existe");
        //copyDataBase();
    } else {
        // Llamando a este método se crea la base de datos vacía en la ruta
        // por defecto del sistema
        // de nuestra aplicación por lo que podremos sobreescribirla con
        // nuestra base de datos.

        db_Read = this.getReadableDatabase();
        db_Read.close();
        Log.i("creando base de datos", "Creando la base de datos");

        try {

            copyDataBase();

        } catch (IOException e) {
            throw new Error("Error copiando Base de Datos");
        }
    }

}

/**
 * Comprueba si la base de datos existe para evitar copiar siempre el
 * fichero cada vez que se abra la aplicación.
 * 
 * @return true si existe, false si no existe
 */
private boolean checkDataBase() {

    SQLiteDatabase checkDB = null;

    try {

        String myPath = DB_PATH + DB_NAME;
        checkDB = SQLiteDatabase.openDatabase(myPath, null,
                SQLiteDatabase.OPEN_READONLY);

    } catch (SQLiteException e) {

        // si llegamos aqui es porque la base de datos no existe todavía.

    }
    if (checkDB != null) {

        checkDB.close();

    }
    return checkDB != null ? true : false;
}

/**
 * Copia nuestra base de datos desde la carpeta assets a la recién creada
 * base de datos en la carpeta de sistema, desde dónde podremos acceder a
 * ella. Esto se hace con bytestream.
 * */
private void copyDataBase() throws IOException {
    Log.i("copiando base de datos", "Copiando...");
    // Abrimos el fichero de base de datos como entrada
    InputStream myInput = myContext.getAssets().open(DB_NAME);

    Log.i("copiando base de datos", myContext.getAssets().open(DB_NAME).toString());
    // Ruta a la base de datos vacía recién creada
    String outFileName = DB_PATH + DB_NAME;
    Log.i("copiando base de datos", DB_PATH + DB_NAME);

    // Abrimos la base de datos vacía como salida
    OutputStream myOutput = new FileOutputStream(outFileName);

    // Transferimos los bytes desde el fichero de entrada al de salida
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer)) > 0) {
        myOutput.write(buffer, 0, length);
    }

    // Liberamos los streams
    myOutput.flush();
    myOutput.close();
    myInput.close();

}

public void open() throws SQLException {

    // Abre la base de datos
    try {
        createDataBase();
    } catch (IOException e) {
        throw new Error("Ha sido imposible crear la Base de Datos");
    }

    String myPath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(myPath, null,
            SQLiteDatabase.OPEN_READONLY);

}

@Override
public synchronized void close() {
    if (myDataBase != null)
        myDataBase.close();
    super.close();
}

@Override
public void onCreate(SQLiteDatabase db) {

}

public void crearTabla(){
    String sqlCreate = "CREATE TABLE clientes (_id INTEGER, nombre TEXT, apellidos TEXT, edad INTEGER)";
    myDataBase.execSQL(sqlCreate);
    myDataBase.execSQL("INSERT INTO clientes (_id, nombre, apellidos, edad) " +
            "VALUES (2, 'Alberto', 'Redondo', 27)");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}

Here it's the debug information.

> 02-23 02:18:25.072: E/Trace(4081): error opening trace file: No such file or directory (2)
02-23 02:18:26.370: I/creando base de datos(4081): Ya existe
02-23 02:18:26.410: E/SQLiteLog(4081): (1) no such table: clientes
02-23 02:18:26.410: D/AndroidRuntime(4081): Shutting down VM
02-23 02:18:26.421: W/dalvikvm(4081): threadid=1: thread exiting with uncaught exception (group=0x40a13300)
02-23 02:18:26.450: E/AndroidRuntime(4081): FATAL EXCEPTION: main
02-23 02:18:26.450: E/AndroidRuntime(4081): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.bbdd/com.example.bbdd.AndroidBaseDatos}: android.database.sqlite.SQLiteException: no such table: clientes (code 1): , while compiling: SELECT nombre, apellidos, edad FROM clientes WHERE _id=?
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2059)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.app.ActivityThread.access$600(ActivityThread.java:130)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.os.Handler.dispatchMessage(Handler.java:99)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.os.Looper.loop(Looper.java:137)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.app.ActivityThread.main(ActivityThread.java:4745)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at java.lang.reflect.Method.invokeNative(Native Method)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at java.lang.reflect.Method.invoke(Method.java:511)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at dalvik.system.NativeStart.main(Native Method)
02-23 02:18:26.450: E/AndroidRuntime(4081): Caused by: android.database.sqlite.SQLiteException: no such table: clientes (code 1): , while compiling: SELECT nombre, apellidos, edad FROM clientes WHERE _id=?
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1161)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1032)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1200)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at com.example.bbdd.AndroidBaseDatos.onStart(AndroidBaseDatos.java:90)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.app.Instrumentation.callActivityOnStart(Instrumentation.java:1163)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.app.Activity.performStart(Activity.java:5018)
02-23 02:18:26.450: E/AndroidRuntime(4081):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2032)
02-23 02:18:26.450: E/AndroidRuntime(4081):     ... 11 more
02-23 02:23:26.573: I/Process(4081): Sending signal. PID: 4081 SIG: 9

The table it's on a db file name database, and when I open it with the shell the table clientes it's created, and if try to create it again shows an SQLException that says that the table alredy exists, as i check before, but i cant show any query made to any table in any database.

Thank you.

before i can help you, i want to know if you are intending to copy an already created database file from the assets folder OR you are creating a fresh one using sql create statements? .. cause i can see you are doing both in your code, which doesn't make sense! — kdehairy
– kdehairy, Commented Feb 25, 2013 at 9:33
My first idea it's to copy all the information from a database file, but when the error appeared I tried to create a new one to see if the query has something wrong and thats why both codes are mixed — alberto_redo
– alberto_redo, Commented Feb 25, 2013 at 19:33

kdehairy · Accepted Answer · 2013-12-02 22:56:34Z

2

First: remove the code that creates the database. that is, stick with your first idea ( copying the database from assets folder).
second: use sqlite3 to open and test the validity of your database file (that is, test your queries) on your desktop. if it is valid and tests succeeded, proceed..
third: it is easier and more straight forward to use this implementation (it's my blog)

please let me know if something went wrong.

edited Dec 2, 2013 at 22:56

answered Feb 26, 2013 at 11:15

kdehairy

2,7301 gold badge22 silver badges29 bronze badges

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

2 Comments

Ricardo Over a year ago

The link is broken :(

kdehairy Over a year ago

@Ricardo sorry for that, I updated the link. It should work now. please let me know if you find any issues.

ebarrenechea · Accepted Answer · 2013-02-23 03:29:21Z

0

It seems that you never call crearTabla to create your table from anywhere in your code. You should at least call it from onCreate on your SQLiteOpenHelper.

answered Feb 23, 2013 at 3:29

ebarrenechea

3,7951 gold badge33 silver badges37 bronze badges

1 Comment

alberto_redo Over a year ago

I don't call crear Tabla because when I do it an SQLException appears which says that the table clientes already exists.

GrIsHu · Accepted Answer · 2013-02-23 04:18:12Z

simple creation insertion and updation code try like this may be helpful...

 public class MainActivity extends Activity implements OnClickListener {

SQLiteDatabase database;
private static final String DBNAME="Example";
Button btn1,btn2;
EditText et1,et2;
int id=0;
String name=null;
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    et1=(EditText) findViewById(R.id.editText1);
    et2=(EditText) findViewById(R.id.editText2);


    database=openOrCreateDatabase(DBNAME, MODE_PRIVATE, null);
    btn1=(Button) findViewById(R.id.button1);
    btn2=(Button) findViewById(R.id.button2);
    btn2.setOnClickListener(this);
    btn1.setOnClickListener(this);


}

@Override
public void onClick(View v) {

    switch(v.getId()){
    case R.id.button1:
        id =Integer.parseInt(et1.getText().toString());
        name=et2.getText().toString();
        try{
            database.execSQL("CREATE TABLE IF NOT EXISTS tabl(_id INTEGER PRIMARY KEY,id NUMERIC,name TEXT)");
            Log.v("hi", "table created");
            database.beginTransaction();
            database.execSQL("INSERT INTO tabl(id,name) VALUES ("+id+",'"+name+"');");
            database.setTransactionSuccessful();
            database.endTransaction();
        }
        catch(Exception e){
            Log.v("hi", e.toString());  
        }
        break;
    case R.id.button2:
        id =Integer.parseInt(et1.getText().toString());
        name=et2.getText().toString();
        try{
            database.beginTransaction();
            database.execSQL("UPDATE tabl SET name='"+name+"' WHERE id="+id+";");
            database.setTransactionSuccessful();
            database.endTransaction();
        }
        catch(Exception e){
            Log.v("hi", e.toString());
        }
    }
}
}

and resource

    <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"     xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" tools:context=".MainActivity">
    <EditText android:id="@+id/editText1" android:layout_width="wrap_content"        android:layout_height="wrap_content" android:layout_alignParentRight="true" android:layout_alignParentTop="true" android:layout_marginRight="91dp" android:layout_marginTop="64dp" android:ems="10">
     <requestFocus /> 
     </EditText>
     <EditText android:id="@+id/editText2" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignLeft="@+id/editText1" android:layout_below="@+id/editText1" android:layout_marginLeft="50dp" android:layout_marginTop="36dp" android:ems="10" /> 
     <Button android:id="@+id/button1" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignLeft="@+id/editText2" android:layout_centerVertical="true" android:text="Button" /> 
     <Button android:id="@+id/button2" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignBaseline="@+id/button1" android:layout_alignBottom="@+id/button1" android:layout_alignRight="@+id/editText2" android:text="Button" /> 
     </RelativeLayout>

try like this., may be helpful

Collectives™ on Stack Overflow

android.database.sqlite.SQLiteException: no such table:

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related