JohnMurhy
Reputation: 321
SQLite Android insert returns -1
I am creating a Database using SQLite on my Android App. I have successfully created a table called "alerts", and I am able to insert and read data from it. The problem is when I create the second table called "interruptions".
When I try to insert, no exception is given. But the result of
valuesID = db.insert(TABLE_NAME_INTERRUPTS, null, values);
is -1.
Then when I try to read from that specific table cursor.moveToFirst() returns false.
I am using the same code that I used before to create the previous table.
When I debug my app after the following code,
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
I can see the columns of the table. I believe that the table is created, but somehow there is a problem when inserting. I don't know if it is related to the data types that I am using.
I have 2 tables: "alerts" and "interruptions". For every table I have a method to "insert" and to "get" data. Everything works fine for the "alerts" table. The other one doesn't.
Here is my SQLiteOpenHelper:
public class DataBaseManagement extends SQLiteOpenHelper{
private static final String DATABASE_NAME = "partum.db";
public static final String TABLE_NAME_ALERTS = "alerts";
public static final String TABLE_NAME_INTERRUPTS = "interruptions";
private static final int DATABASE_VERSION = 1;
public DataBaseManagement(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_TABLE_NAME_ALERTS);
db.execSQL(CREATE_TABLE_NAME_INTERRUPTS);
}
@Override
public void onOpen(SQLiteDatabase db) {
super.onOpen(db);
db.execSQL("PRAGMA foreign_keys=ON");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_NAME_ALERTS);
db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_NAME_INTERRUPTS);
onCreate(db);
}
private static final String CREATE_TABLE_NAME_ALERTS = "create table "
+ TABLE_NAME_ALERTS + " ( "
+ "id_alert" + " integer primary key, "
+ "msg" + " TEXT, "
+ "id_animal" + " TEXT, "
+ "status" + " TEXT, "
+ "id_box" + " TEXT, "
+ "date_inserted" + " TEXT, "
+ "type_alert" + " TEXT);";
private static final String CREATE_TABLE_NAME_INTERRUPTS = "create table "
+ TABLE_NAME_INTERRUPTS + " ( "
+ "id_interrupt" + " integer primary key, "
+ "status" + " TEXT, "
+ "duration" + " TEXT, "
+ "frequency" + " TEXT, "
+ "id_box" + " TEXT, "
+ "id_mother" + " TEXT, "
+ "id_lot" + " TEXT, "
+ "date_birth" + " TEXT, "
+ "hour_birth" + " TEXT, "
+ "id_birth" + " TEXT, "
+ "id_row" + " TEXT, "
+ "id_wean" + " TEXT, "
+ "mummified" + " TEXT, "
+ "number_children_alive" + " TEXT, "
+ "number_children_dead" + " TEXT, "
+ "id_interruption_event" + " TEXT, "
+ "date_time_event" + " TEXT);";
public int insertAlerts(String[] dataAlerts){
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor;
long valuesID;
ContentValues values = new ContentValues(); // for readings
values.put("id_alert", dataAlerts[4]);
values.put("msg", dataAlerts[0]);
values.put("id_animal", dataAlerts[1]);
values.put("status", dataAlerts[2]);
values.put("id_box", dataAlerts[3]);
values.put("date_inserted", dataAlerts[5]);
values.put("type_alert", dataAlerts[6]);
//check if DB has more than 50 entries
cursor = db.rawQuery("SELECT COUNT(id_alert) AS count FROM alerts", null);
if(cursor.moveToFirst())
{
if(cursor.getInt(0) > 49){
// Log.d("database", "database limit exceeded");
cursor = db.query(TABLE_NAME_ALERTS, null, null, null, null, null, null);
//delete first row
if(cursor.moveToFirst()) {
String rowId = cursor.getString(cursor.getColumnIndex("id_alert"));
db.delete(TABLE_NAME_ALERTS, "id_alert" + "=?", new String[]{rowId});
}
cursor.close();
}
}
// Inserting Row in Data Values
valuesID = db.insert(TABLE_NAME_ALERTS, null, values);
return (int) valuesID;
}
public ArrayList<ArrayList<String>> getDataAlerts(){
ArrayList<ArrayList<String>> tableRows = new ArrayList<ArrayList<String>>();
tableRows.clear();
// Select All Query
String selectQuery = "SELECT * FROM " + TABLE_NAME_ALERTS;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
ArrayList<String> rowValues = new ArrayList<String>();
rowValues.clear();
rowValues.add(cursor.getString(0));
rowValues.add(cursor.getString(1));
rowValues.add(cursor.getString(2));
rowValues.add(cursor.getString(3));
rowValues.add(cursor.getString(4));
rowValues.add(cursor.getString(5));
rowValues.add(cursor.getString(6));
System.out.println("Row Values: " + rowValues);
// Adding row to list
tableRows.add(rowValues);
System.out.println("Table Rows:" + tableRows);
} while (cursor.moveToNext());
}
return tableRows;
}
public int insertInterrupts(String[] dataInterrupts){
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor;
long valuesID;
ContentValues values = new ContentValues(); // for readings
values.put("id_interrupt", dataInterrupts[15]);
values.put("status", dataInterrupts[0]);
values.put("duration", dataInterrupts[1]);
values.put("frequency", dataInterrupts[2]);
values.put("id_box", dataInterrupts[3]);
values.put("id_mother", dataInterrupts[4]);
values.put("id_lot", dataInterrupts[5]);
values.put("date_birth", dataInterrupts[6]);
values.put("hour_birth", dataInterrupts[7]);
values.put("id_birth", dataInterrupts[8]);
values.put("id_row", dataInterrupts[9]);
values.put("date_wean", dataInterrupts[10]);
values.put("mummified", dataInterrupts[11]);
values.put("number_children_alive", dataInterrupts[12]);
values.put("number_children_dead", dataInterrupts[13]);
values.put("id_interruption_event", dataInterrupts[14]);
values.put("date_time_event", dataInterrupts[16]);
//check if DB has more than 50 entries
cursor = db.rawQuery("SELECT COUNT(id_interrupt) AS count FROM interruptions", null);
if(cursor.moveToFirst())
{
if(cursor.getInt(0) > 49){
// Log.d("database", "database limit exceeded");
cursor = db.query(TABLE_NAME_INTERRUPTS, null, null, null, null, null, null);
//delete first row
if(cursor.moveToFirst()) {
String rowId = cursor.getString(cursor.getColumnIndex("id_interrupt"));
db.delete(TABLE_NAME_INTERRUPTS, "id_interrupt" + "=?", new String[]{rowId});
}
cursor.close();
}
}
// Inserting Row in Data Values
valuesID = db.insert(TABLE_NAME_INTERRUPTS, null, values);
return (int) valuesID;
}
public ArrayList<ArrayList<String>> getDataInterrupts(){
ArrayList<ArrayList<String>> tableRows = new ArrayList<ArrayList<String>>();
tableRows.clear();
// Select All Query
String selectQuery = "SELECT * FROM " + TABLE_NAME_INTERRUPTS;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
ArrayList<String> rowValues = new ArrayList<String>();
rowValues.clear();
rowValues.add(cursor.getString(0));
rowValues.add(cursor.getString(1));
rowValues.add(cursor.getString(2));
rowValues.add(cursor.getString(3));
rowValues.add(cursor.getString(4));
rowValues.add(cursor.getString(5));
rowValues.add(cursor.getString(6));
rowValues.add(cursor.getString(7));
rowValues.add(cursor.getString(8));
rowValues.add(cursor.getString(9));
rowValues.add(cursor.getString(10));
rowValues.add(cursor.getString(11));
rowValues.add(cursor.getString(12));
rowValues.add(cursor.getString(13));
rowValues.add(cursor.getString(14));
rowValues.add(cursor.getString(15));
rowValues.add(cursor.getString(16));
System.out.println("Row Values: " + rowValues);
// Adding row to list
tableRows.add(rowValues);
System.out.println("Table Rows:" + tableRows);
} while (cursor.moveToNext());
}
return tableRows;
}
}
If someone could help me I would appreciate. Thank you.
Upvotes: 1
Views: 827
Answers (1)
sberezin
Reputation: 3296
Look, there is such a line in your code:
values.put("date_wean", dataInterrupts[10]);
but there is no column with such a name!
That's why it's important to USE CONSTANTS, not hard-coded strings, to access fields etc
Upvotes: 2
Related Questions
- While Inserting records in SQLite returns -1
- SQLiteDatabase insert() method always return -1
- SQLite Insert fails
- SQLLITE insert statement fails in android app
- Android sqlite Data not being inserted in database
- Android - SQlite insert not inserting
- SQLiteDatabase insert function returns -1
- Trouble with insert()
- Insert Data to android sqlite database doesn't work
- SQLite.insert return -1 but data inserted into database