Android : db.execsql in android fails, i've exhausted all possibilities

on Friday, December 5, 2014


The SQLite query in my program fails. I have no idea why. What's more mysterious is that I've managed to export the database and tried running the same exact SQLite query on my computer and it works fine.


Here's the SQLite database that's in my program: https://www.dropbox.com/s/tvz14rrgzufu5fy/backupname.db?dl=0


Here's the query:



UPDATE Log SET Productivity = 5 WHERE Time = 2523;


And here's the Java code for the Android program:



import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.RatingBar;

import org.joda.time.LocalDateTime;


public class TimeRecorder extends Activity {
int Year, Month, Day, Hour;
private SQLiteDatabase db;
private DatabaseHelper dbh;
Intent mIntent;
RatingBar bar1;
RatingBar bar2;
EditText tag1;
EditText tag2;
EditText tag3;
EditText tag4;
EditText description;
int Time;
LocalDateTime rightNow;


@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_time_recorder);
mIntent = getIntent();
dbh = new DatabaseHelper(this);
db = dbh.getWritableDatabase();
dbh.fill(db);
rightNow = LocalDateTime.now();
int Year = mIntent.getIntExtra("Year",rightNow.getYear());
int Month = mIntent.getIntExtra("Month",rightNow.getMonthOfYear());
int Day = mIntent.getIntExtra("Day",rightNow.getDayOfMonth());
int Hour = mIntent.getIntExtra("Hour",rightNow.getHourOfDay());
String query = "SELECT Productivity, Energy, Long, Lat, Tag1, Tag2, Tag3, Tag4, Description, Time FROM Log " +
"WHERE Year = ? AND Month = ? AND Day = ? AND Hour = ?";
String[] theArgs = {Integer.toString(Year), Integer.toString(Month), Integer.toString(Day), Integer.toString(Hour)};
Cursor tmp = db.rawQuery(query, theArgs);
tmp.moveToFirst();
bar1 = (RatingBar) findViewById(R.id.ratingBar);
bar2 = (RatingBar) findViewById(R.id.ratingBar2);
tag1 = (EditText) findViewById(R.id.tag1);
tag2 = (EditText) findViewById(R.id.tag2);
tag3 = (EditText) findViewById(R.id.tag3);
tag4 = (EditText) findViewById(R.id.tag4);
bar1.setNumStars(tmp.getInt(0));
bar2.setNumStars(tmp.getInt(1));
String tmp2 = tmp.getString(4);
if (tmp2 != null){
tag1.setText(tmp2);
}
tmp2 = tmp.getString(5);
if (tmp2 != null){
tag2.setText(tmp2);
}
tmp2 = tmp.getString(6);
if (tmp2 != null){
tag3.setText(tmp2);
}
tmp2 = tmp.getString(7);
if (tmp2 != null){
tag4.setText(tmp2);
}
tmp2 = tmp.getString(8);
if (tmp2 != null){
description.setText(tmp2);
}
Time = tmp.getInt(9);
tmp.close();
Button submit = (Button) findViewById(R.id.submit);
submit.setOnClickListener(new OnClickListener() {

@Override
public void onClick(View v) {
//String query2 = "UPDATE Log SET Productivity = " + Integer.toString(bar1.getNumStars()) + ", Energy = " +
// Integer.toString(bar2.getNumStars()) +" WHERE Time = " + Integer.toString(Time) +";";
String query2 = "UPDATE Log SET Productivity = " + Integer.toString(bar1.getNumStars()) + " WHERE Time = " + Integer.toString(Time) +";";
Log.i("TAG", query2);
db.execSQL(query2);
db.close();
finish();

}
});




db.close();
}


@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.menu_time_recorder, menu);
return true;
}

@Override
public boolean onOptionsItemSelected(MenuItem item) {
// Handle action bar item clicks here. The action bar will
// automatically handle clicks on the Home/Up button, so long
// as you specify a parent activity in AndroidManifest.xml.
int id = item.getItemId();

//noinspection SimplifiableIfStatement
if (id == R.id.action_settings) {
return true;
}

return super.onOptionsItemSelected(item);
}
}


Here's the error message I'm getting:



4560-4560/apps.abcd.jj.recordtimeeveryhour I/TAG﹕ UPDATE Log SET Productivity = 5 WHERE Time = 2523;
12-05 21:52:02.083 4560-4560/apps.abcd.jj.recordtimeeveryhour D/AndroidRuntime﹕ Shutting down VM
12-05 21:52:02.083 4560-4560/apps.abcd.jj.recordtimeeveryhour W/dalvikvm﹕ threadid=1: thread exiting with uncaught exception (group=0x40abe228)
12-05 21:52:02.093 4560-4560/apps.abcd.jj.recordtimeeveryhour E/AndroidRuntime﹕ FATAL EXCEPTION: main
java.lang.NullPointerException
at android.database.sqlite.SQLiteStatement.releaseAndUnlock(SQLiteStatement.java:302)
at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:99)
at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:2031)
at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1971)
at apps.abcd.jj.recordtimeeveryhour.TimeRecorder$1.onClick(TimeRecorder.java:93)


Line 93 is: db.execSQL(query2);


Any ideas what's going on? Logcat reported the query being performed. Try performing that query on the exact same database I've attached in the Dropbox link.. it works fine! So why doesn't it work in my Android program?


0 comments:

Post a Comment