I'm doing a project in Android Studio and I'm having trouble inserting records into two of the tables. The idea is to first insert data in one of the tables (LibrosTotales) and according to the option chosen in a RadioGroup,is inserted into one table or another (LibrosLeídos and LibrosPendientes). Although the registration in the main table is successful but in the other two it does not occur. I've checked it through some rawQuery to see if there are any logs. Don't know if the problem is in the database itself or there is something wrong with my program's logic. Here I leave the code of the database:
package Programa
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
class DBSQLiteHelper (context: Context?, name: String?, factory: SQLiteDatabase.CursorFactory?, version: Int) :
SQLiteOpenHelper(context, name, factory, version) {
override fun onCreate(db: SQLiteDatabase) {
db.execSQL(
"CREATE TABLE '$librosTotales' ( "
"id INTEGER PRIMARY KEY AUTOINCREMENT,"
"titulo TEXT NOT NULL,"
"autor TEXT NOT NULL,"
"comentario TEXT,"
"leidoono BOOLEAN)"
)
db.execSQL(
"CREATE TABLE '$librosLeidos' ( "
"id_leidos INTEGER PRIMARY KEY REFERENCES librosTotales(id),"
"titulo TEXT NOT NULL,"
"autor TEXT NOT NULL,"
"comentario TEXT)"
)
db.execSQL(
"CREATE TABLE '$librosPendientes' ( "
"id_pendientes INTEGER PRIMARY KEY REFERENCES librosTotales(id),"
"titulo TEXT NOT NULL,"
"autor TEXT NOT NULL,"
"comentario TEXT)"
)
}
override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
db.execSQL("DROP TABLE $librosTotales")
db.execSQL("DROP TABLE $librosLeidos")
db.execSQL("DROP TABLE $librosPendientes")
onCreate(db)
}
companion object {
private const val DATABASE_VERSION = 1
private const val NAME_DATABASE = "LIBROS"
private const val librosTotales = "librosTotales"
private const val librosLeidos = "librosLeidos"
private const val librosPendientes = "librosPendientes"
}
}
This is the class in which I intend to perform the insertion (some of the code I commented to check for logs using console messages:
package Programa
import android.content.ContentValues
import android.content.ContentValues.TAG
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.os.Bundle
import android.util.Log
import android.view.inputmethod.InputMethodManager
import android.widget.Button
import android.widget.EditText
import android.widget.RadioGroup
import android.widget.Toast
import androidx.appcompat.app.AppCompatActivity
import com.example.organizarlibros.R
class annadirLibro : AppCompatActivity() {
var autor:EditText?=null;
var titulo: EditText?=null;
var comentario:EditText?=null;
var leidoono:RadioGroup?=null;
lateinit var con:DBSQLiteHelper;
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_annadir_libro);
val buttonAnnadir = findViewById<Button>(R.id.buttonAnnadir)
val introduct = ContentValues()
val con = DBSQLiteHelper(this, "librosTotales", null, 2)
var baseDatos = con.writableDatabase;
var leerono = false;
fun enviarDatos(){
introduct.put("autor", autor?.getText().toString())
introduct.put("titulo", titulo?.getText().toString())
introduct.put("comentario", comentario?.getText().toString())
if (leidoono?.checkedRadioButtonId ==R.id.leido) {
introduct.put("leidoono", "Leído")
leerono=true
// baseDatos.insert("librosTotales", null, introduct)
// introduct.put("autor", autor?.getText().toString())
// introduct.put("titulo", titulo?.getText().toString())
// introduct.put("comentario", comentario?.getText().toString())
// baseDatos.insert("librosLeidos", null, introduct)
}
else if(leidoono?.checkedRadioButtonId ==R.id.noleido){
introduct.put("leidoono", "No Leído")
leerono=false
//baseDatos.insert("librosTotales", null, introduct)
//introduct.put("autor", autor?.getText().toString())
//introduct.put("titulo", titulo?.getText().toString())
//introduct.put("comentario", comentario?.getText().toString())
//baseDatos.insert("LibrosPendientes", null, introduct)
};
baseDatos.insert("librosTotales", null, introduct)
// val comprobar = baseDatos.rawQuery("SELECT leidoono from librosLeidos", null)
if(leerono){
introduct.put("autor", autor?.getText().toString())
introduct.put("titulo", titulo?.getText().toString())
introduct.put("comentario", comentario?.getText().toString())
baseDatos.insert("librosLeidos", null, introduct)
}else{
introduct.put("autor", autor?.getText().toString())
introduct.put("titulo", titulo?.getText().toString())
introduct.put("comentario", comentario?.getText().toString())
baseDatos.insert("librosPendientes", null, introduct)
}
var contar = baseDatos.rawQuery("SELECT * from librosLeidos", null)
if (contar.moveToFirst()){
Log.i(TAG, "sí hay tabla")
}else{
Log.i(TAG, "no hay tabla")
}
// baseDatos.close()
}
buttonAnnadir.setOnClickListener(){
enviarDatos();
//onRadioButtonClicked();
// elegirTabla();
val cerrarTablero: InputMethodManager =
getSystemService(Context.INPUT_METHOD_SERVICE) as InputMethodManager
cerrarTablero.hideSoftInputFromWindow(buttonAnnadir.windowToken, 0)
// if( baseDatos != null){
Toast.makeText(this, "Enviando datos", Toast.LENGTH_LONG).show()
}
// else{
// Toast.makeText(this, "No se pudo enviar datos", Toast.LENGTH_LONG).show()
}
}
This is the corresponding layout:
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:id="@ id/linearLayout"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:background="#A59C9C"
android:orientation="vertical"
app:layout_behavior="@string/appbar_scrolling_view_behavior">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="vertical">
<TextView
android:id="@ id/textView1"
android:layout_width="match_parent"
android:layout_height="58dp"
android:layout_weight="150 "
android:text="POR FAVOR, INTRODUZCA LOS DATOS"
android:textSize="20sp" />
<EditText
android:id="@ id/Autor"
android:layout_width="match_parent"
android:layout_height="50dp"
android:layout_weight="150 "
android:ems="10"
android:inputType="textEmailAddress"
android:hint="Autor" />
<EditText
android:id="@ id/Titulo"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:width="1000dp"
android:height="50dp"
android:hint="Título"
android:textSize="20dp" />
</LinearLayout>
<RadioGroup xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="vertical">
<RadioButton android:id="@ id/leido"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="LEÍDO" />
<RadioButton android:id="@ id/noleido"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="PENDIENTE" />
</RadioGroup>
<EditText
android:id="@ id/edicion_text"
android:layout_width="match_parent"
android:layout_height="150dp"
android:hint="Introduzca aquí las notas"
android:inputType="text|textMultiLine"
android:gravity="top"/>
<Button
android:id="@ id/buttonAnnadir"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="ACEPTAR"
app:layout_constraintBottom_toBottomOf="parent"
app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent" />
<androidx.coordinatorlayout.widget.CoordinatorLayout
xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:background="#A59C9C"
android:visibility="visible"
tools:context=".librosLeidos"
tools:visibility="visible">
</androidx.coordinatorlayout.widget.CoordinatorLayout>
</LinearLayout>
CodePudding user response：
Your first issue is that you are declaring variables for the Views using:-
var autor: EditText?=null;
var titulo: EditText?=null;
var comentario:EditText?=null;
var leidoono: RadioGroup?=null;
But subsequently you are not actually instantiating them so they remain null.
So when it comes to inserting you are inserting null values.
This can be seen by setting breakpoints (this being a very useful debugging action) and then running the App in debug mode.
e.g. 
- the blue circle when clicked and after inputting some data and clicking on the button :-
Then the debug window appears when the breakpoint is reached :-
It can be seen that (as highlighted) that all values are null, so at best a row will be inserted with null values.
If you then click Resume Program (F9) the debugger will then stop at the 3rd breakpoint, skipping the 2nd as leerono is false (so that logic, as such is correct) :-
- again all values are null.
However, if you now add the following code to instantiate the Views :-
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_annadir_libro) // EXISTING CODE
autor = this.findViewById(R.id.Autor) //<<<<<<<<<< ADDED
titulo = this.findViewById(R.id.Titulo) //<<<<<<<<<< ADDED
comentario = this.findViewById(R.id.edicion_text) //<<<<<<<<<< ADDED
val buttonAnnadir = findViewById<Button>(R.id.buttonAnnadir)// EXISTING CODE
and then run in debug mode (after uninstalling the App to clear the database) then at the first breakpoint:-
Then you can see that the data entered is then retrieved and would be inserted (assuming that the insert works).
Stage 2 - Checking that data is entered and retrieved
rather than a bland
if (contar.moveToFirst()){
Log.i(TAG, "sí hay tabla")
}else{
Log.i(TAG, "no hay tabla")
}
whihc will only tell if a row has ever been inserted into the librosTotales table. I would suggest a more comprehensive test/logging.
So change to be :-
var contar = baseDatos.rawQuery("SELECT * FROM librosTotales",null)
DatabaseUtils.dumpCursor(contar)
contar = baseDatos.rawQuery("SELECT * from librosLeidos", null)
DatabaseUtils.dumpCursor(contar)
contar = baseDatos.rawQuery("SELECT * FROM librosPendientes",null)
DatabaseUtils.dumpCursor(contar)
contar = baseDatos.rawQuery("SELECT * FROM librosTotales", null)
if (contar.moveToFirst()){
Log.i(TAG, "sí hay tabla")
}else{
Log.i(TAG, "no hay tabla")
}
contar = baseDatos.rawQuery("SELECT * FROM sqlite_master", null)
DatabaseUtils.dumpCursor(contar)
This, will write out all data from each table to the log and also the schema.
After uninstalling the App and running with two sets of data (for each radio button being clicked) then :-
2022-06-24 10:51:00.997 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@c91b3ab
2022-06-24 10:51:00.998 I/System.out: 0 {
2022-06-24 10:51:00.998 I/System.out: id=1
2022-06-24 10:51:00.998 I/System.out: titulo=The Title
2022-06-24 10:51:00.998 I/System.out: autor=Fred Bloggs
2022-06-24 10:51:00.998 I/System.out: comentario=blah blah blah
2022-06-24 10:51:00.998 I/System.out: leidoono=null
2022-06-24 10:51:00.998 I/System.out: }
2022-06-24 10:51:00.998 I/System.out: 1 {
2022-06-24 10:51:00.998 I/System.out: id=2
2022-06-24 10:51:00.998 I/System.out: titulo=Another Title
2022-06-24 10:51:00.998 I/System.out: autor=Jane Doe
2022-06-24 10:51:00.998 I/System.out: comentario=blah blah blah again
2022-06-24 10:51:00.998 I/System.out: leidoono=null
2022-06-24 10:51:00.998 I/System.out: }
2022-06-24 10:51:00.998 I/System.out: <<<<<
2022-06-24 10:51:00.999 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@9ca08
2022-06-24 10:51:00.999 I/System.out: 0 {
2022-06-24 10:51:00.999 I/System.out: id_pendientes=1
2022-06-24 10:51:00.999 I/System.out: titulo=The Title
2022-06-24 10:51:00.999 I/System.out: autor=Fred Bloggs
2022-06-24 10:51:00.999 I/System.out: comentario=blah blah blah
2022-06-24 10:51:00.999 I/System.out: }
2022-06-24 10:51:01.000 I/System.out: 1 {
2022-06-24 10:51:01.000 I/System.out: id_pendientes=2
2022-06-24 10:51:01.000 I/System.out: titulo=Another Title
2022-06-24 10:51:01.000 I/System.out: autor=Jane Doe
2022-06-24 10:51:01.000 I/System.out: comentario=blah blah blah again
2022-06-24 10:51:01.000 I/System.out: }
2022-06-24 10:51:01.000 I/System.out: <<<<<
2022-06-24 10:51:01.001 I/MYLOG: sí hay tabla
This indicates and issue with the logic as both have been added to the librosPenientes table.
This after testing in debug mode, is the same issue, that is leidoono is null.
So adding an id to the RadioGroup in the layout :-
and instantiating with leidoono = this.findViewById(R.id.rg) and then re-installing and rerunning with two sets of data, appears to work BUT looking at the log and you see a trapped error for each insert e.g. :-
2022-06-24 10:58:41.400 31105-31105/a.a.so72719667kotlinsqliteparentandchildren E/SQLiteLog: (1) table librosLeidos has no column named leidoono in "INSERT INTO librosLeidos(comentario,titulo,autor,leidoono) VALUES (?,?,?,?)"
2022-06-24 10:58:41.402 31105-31105/a.a.so72719667kotlinsqliteparentandchildren E/SQLiteDatabase: Error inserting comentario=blah blah blah titulo=Title autor=Fred Bloggs leidoono=Leído
android.database.sqlite.SQLiteException: table librosLeidos has no column named leidoono (code 1 SQLITE_ERROR): , while compiling: INSERT INTO librosLeidos(comentario,titulo,autor,leidoono) VALUES (?,?,?,?)
i.e. no column named leidoono, which is true, that column only exists in the librosTotales table not the others.
This is happening because the ContentValues is being reused and the data for that column is in it.
The fix for this is to clear the content values and reapply the data.
make the changes, uninstall the App and Rerun and :-
2022-06-24 11:27:42.639 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@9b3bd44
2022-06-24 11:27:42.639 I/System.out: 0 {
2022-06-24 11:27:42.639 I/System.out: id=1
2022-06-24 11:27:42.640 I/System.out: titulo=The Title
2022-06-24 11:27:42.640 I/System.out: autor=Fred Bloggs
2022-06-24 11:27:42.640 I/System.out: comentario=blah blah blah
2022-06-24 11:27:42.640 I/System.out: leidoono=Leído
2022-06-24 11:27:42.640 I/System.out: }
2022-06-24 11:27:42.640 I/System.out: 1 {
2022-06-24 11:27:42.640 I/System.out: id=2
2022-06-24 11:27:42.640 I/System.out: titulo=Another Title
2022-06-24 11:27:42.640 I/System.out: autor=Jane Doe
2022-06-24 11:27:42.640 I/System.out: comentario=blah blah blah again
2022-06-24 11:27:42.640 I/System.out: leidoono=No Leído
2022-06-24 11:27:42.640 I/System.out: }
2022-06-24 11:27:42.640 I/System.out: <<<<<
2022-06-24 11:27:42.640 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@bca7e2d
2022-06-24 11:27:42.640 I/System.out: 0 {
2022-06-24 11:27:42.641 I/System.out: id_leidos=1
2022-06-24 11:27:42.641 I/System.out: titulo=The Title
2022-06-24 11:27:42.641 I/System.out: autor=Fred Bloggs
2022-06-24 11:27:42.641 I/System.out: comentario=blah blah blah
2022-06-24 11:27:42.641 I/System.out: }
2022-06-24 11:27:42.641 I/System.out: <<<<<
2022-06-24 11:27:42.641 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@b779f62
2022-06-24 11:27:42.641 I/System.out: 0 {
2022-06-24 11:27:42.641 I/System.out: id_pendientes=1
2022-06-24 11:27:42.641 I/System.out: titulo=Another Title
2022-06-24 11:27:42.641 I/System.out: autor=Jane Doe
2022-06-24 11:27:42.641 I/System.out: comentario=blah blah blah again
2022-06-24 11:27:42.641 I/System.out: }
2022-06-24 11:27:42.641 I/System.out: <<<<<
2022-06-24 11:27:42.642 I/MYLOG: sí hay tabla
All now looks OK.
CodePudding user response：
Thank you very much, I tried to do as in your example and it's true, the result is null, even once I have initialized the variables
That's a fragment from my LogCat:
I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@c9eff41
I/System.out: <<<<<
>>>>> Dumping cursor android.database.sqlite.SQLiteCursor@ebeace6
I/System.out: <<<<<
I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@fc1e227
I/System.out: <<<<<
I/ContentValues: no hay tabla
I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@99f5cd4
I/System.out: 0 {
type=table
name=android_metadata
I/System.out: tbl_name=android_metadata
rootpage=3
sql=CREATE TABLE android_metadata (locale TEXT)
}
1 {
type=table
name=librosTotales
tbl_name=librosTotales
rootpage=4
sql=CREATE TABLE 'librosTotales' ( id INTEGER PRIMARY KEY AUTOINCREMENT,titulo TEXT NOT NULL,autor TEXT NOT NULL,comentario TEXT,leidoono BOOLEAN)
}
2 {
type=table
name=sqlite_sequence
tbl_name=sqlite_sequence
rootpage=5
sql=CREATE TABLE sqlite_sequence(name,seq)
}
3 {
type=table
name=librosLeidos
tbl_name=librosLeidos
rootpage=6
sql=CREATE TABLE 'librosLeidos' ( id_leidos INTEGER PRIMARY KEY REFERENCES librosTotales(id),titulo TEXT NOT NULL,autor TEXT NOT NULL,comentario TEXT)
}
4 {
type=table
name=librosPendientes
tbl_name=librosPendientes
rootpage=7
sql=CREATE TABLE 'librosPendientes' ( id_pendientes INTEGER PRIMARY KEY REFERENCES librosTotales(id),titulo TEXT NOT NULL,autor TEXT NOT NULL,comentario TEXT)
}
<<<<<
I/ContentValues: no hay tabla
```