I'm new in core data I'm converting my sql query to fetch the coredata results but not able to convert this query.
SELECT COUNT (DISTINCT ZUSER) AS Users,ZHOUR ,SUM ( ZLIKE) As Likes FROM ZUSERLIKE where ZDAY=28 AND ZMONTH=2 AND ZYEAR=2015 GROUP BY ZHOUR
Any expert can help me out the problem I'm facing at COUNT (DISTINCT ZUSER) this point.
I have an android application that first fetch some data from the web (any updates that might available for an Item) and write the data and after that read the fetched data from the database. The problem is in some cases the reading operation with android cursor returns an empty cursor but we know that the data exist because you updated that exact row some seconds ago!
In fact you can't request an online update for some data that not exist. I searched a lot but no result. I thought I might be from reading and writing at the same time but It wasn't.
below is the code that do this work :
protected Biz doInBackground(Void... params) {
Bundle bundle = BizDetailsActivity.this.getIntent().getExtras();
// bizID = bundle.getLong(BIZ);
BizDataSource ds = new BizDataSource(BizDetailsActivity.this);
ds.openWritable();
long ID = bundle.getLong(BIZ);
if (NetworkHelper.IsConnected(BizDetailsActivity.this)) {
// HERE we write data if avaiable
getBizOnline(ID, ds);
}
//read data from database
biz = ds.getBiz(ID, data.getCurrentUser());
ds.close();
return biz;
}
Code for writing/updating data :
public long updateBiz(long ID, String title, String description,
String address, String phone, String mobile, String fax,
String owner, long city, String website, double latitude,
double longitude, int addDate, String expireDate, long gallery,
String logo, float rate) {
ContentValues values = new ContentValues();
values.put(columns[1], title);
values.put(columns[3], description);
values.put(columns[4], address);
values.put(columns[5], city);
if (gallery > 0) {
values.put(columns[6], gallery);
}
values.put(columns[7], latitude);
values.put(columns[8], longitude);
values.put(columns[9], phone);
values.put(columns[10], mobile);
values.put(columns[11], fax);
values.put(columns[12], website);
values.put(columns[13], owner);
values.put(columns[14], logo);
values.put(columns[15], addDate);
values.put(columns[16], expireDate);
values.put(columns[19], rate);
long result;
try {
result = database.updateWithOnConflict("Bizes", values, "ID = ?",
new String[] { ID + "" },SQLiteDatabase.CONFLICT_FAIL);
} catch (Exception e) {
Log.d("UPDATE_BIZ", e.getMessage());
result = -1;
}
return result;
}
Code for getting data :
public Biz getBiz(long id, long UserId) {
Gallery gallery = null;
City city = null;
BizCategory category = null;
Cursor c = database.query("Bizes", columns, "ID=?", new String[] { id + "" }, null, null, null);
if (c.moveToFirst() == false) {
// here we got empty cursor !!!!
c.close();
return null;
} else {
....
}
}
02-28 14:35:52.946 1923-1937/com.example.android.sunshine.app E/AndroidRuntime﹕ FATAL EXCEPTION: ModernAsyncTask #1
Process: com.example.android.sunshine.app, PID: 1923
java.lang.RuntimeException: An error occured while executing doInBackground()
at android.support.v4.content.ModernAsyncTask$3.done(ModernAsyncTask.java:137)
at java.util.concurrent.FutureTask.finishCompletion(FutureTask.java:355)
at java.util.concurrent.FutureTask.setException(FutureTask.java:222)
at java.util.concurrent.FutureTask.run(FutureTask.java:242)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1112)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:587)
at java.lang.Thread.run(Thread.java:841)
Caused by: android.database.sqlite.SQLiteException: no such column: locationlocation_setting (code 1): , while compiling: SELECT weather._id, date, short_desc, max, min, location_setting, weather_id, coord_lat, coord_long FROM weather INNER JOIN location ON weatherlocation_id = location._id WHERE (locationlocation_setting = ? AND date >= ? ) ORDER BY date ASC
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
at android.database.sqlite.SQLiteQueryBuilder.query(SQLiteQueryBuilder.java:400)
at android.database.sqlite.SQLiteQueryBuilder.query(SQLiteQueryBuilder.java:294)
at com.example.android.sunshine.app.data.WeatherProvider.getWeatherByLocationSetting(WeatherProvider.java:66)
at com.example.android.sunshine.app.data.WeatherProvider.query(WeatherProvider.java:126)
at android.content.ContentProvider.query(ContentProvider.java:857)
at android.content.ContentProvider$Transport.query(ContentProvider.java:200)
at android.content.ContentResolver.query(ContentResolver.java:461)
at android.content.ContentResolver.query(ContentResolver.java:404)
at android.support.v4.content.CursorLoader.loadInBackground(CursorLoader.java:49)
at android.support.v4.content.CursorLoader.loadInBackground(CursorLoader.java:35)
at android.support.v4.content.AsyncTaskLoader.onLoadInBackground(AsyncTaskLoader.java:242)
at android.support.v4.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:51)
at android.support.v4.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:40)
at android.support.v4.content.ModernAsyncTask$2.call(ModernAsyncTask.java:123)
at java.util.concurrent.FutureTask.run(FutureTask.java:237)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1112)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:587)
at java.lang.Thread.run(Thread.java:841)
In my project I have one SqlCypher Database say A.db and which is used by the app.
At some point I will get latest data which I have in another SqlCypher db, say B.db.
Both databases has identical structure (tables).
Now how can I replace the entire data of Database A from Database B. All the table name and structure are same.
How can I achieve this using any Query?, or Should I go for a StreamReader..
It is a Xamarin Project which uses ORMLite.
I have a android app who use SQLite and sqlite.net pcl library (with xamarin and c#)
This is my table definition (just the id part)
[Table("ConsumoMensual")]
public class ConsumoMensual
{
[PrimaryKey, AutoIncrement]
public int? id {get;set;} //also try with int and int?
...
I insert data with this code, and returns 1
public void RegistroLlamada(MOD.phoneScrapView v){
ConsumoMensual c = findRegistroMes (v.FechaLlamada);
bool update = true;
if (c == null) {
update = false;//this is because always get 0
c = new ConsumoMensual ();
}
c.annio = v.FechaLlamada.Year;
c.mes = v.FechaLlamada.Month;
c.minutosMismaCompania += minutosMismaCompania (v);
c.minutosNumerosGratuitos+= ValidoGratuitos (v);
c.minutosOtraCompania+= minutosOtraCompania (v);
lock (locker) {
if (update)
database.Update (c);//crash here (ID is null)
else
database.Insert (c);//returns 1 here
}
}
but when i read it, i got zero in the id
public ConsumoMensual findRegistroMes(DateTime fecha)
{
lock (locker) {
return (from i in database.Table<ConsumoMensual> ()
where i.mes == fecha.Month && i.annio == fecha.Year
select i).FirstOrDefault ();
}
}
this is the interface code
namespace PlanControlDAL
{
public interface ISQLite
{
SQLiteConnection GetConnection();
}
}
and this is the class who implements the interface public class SQLite_Android : ISQLite { public SQLite_Android () { }
#region ISQLite implementation
public SQLite.Net.SQLiteConnection GetConnection ()
{
var sqliteFilename = "planControl.db3";
string documentsPath = System.Environment.GetFolderPath (System.Environment.SpecialFolder.Personal); // Documents folder
var path = Path.Combine(documentsPath, sqliteFilename);
// This is where we copy in the prepopulated database
Console.WriteLine (path);
if (!File.Exists(path))
{
/*var s = Forms.Context.Resources.OpenRawResource(Resource.Raw.totalSQLite); // RESOURCE NAME ###*/
// create a write stream
/*using (StreamReader sr = new StreamReader (Resource.Raw.totalSQLite)) {
FileStream writeStream = new FileStream (path, FileMode.OpenOrCreate, FileAccess.Write);
// write to the stream
ReadWriteStream (sr, writeStream);
}*/
}
var plat = new SQLite.Net.Platform.XamarinAndroid.SQLitePlatformAndroid();
var conn = new SQLite.Net.SQLiteConnection(plat, path);
// Return the database connection
return conn;
}
#endregion
/// <summary>
/// helper method to get the database out of /raw/ and into the user filesystem
/// </summary>
void ReadWriteStream(Stream readStream, Stream writeStream)
{
int Length = 256;
Byte[] buffer = new Byte[Length];
int bytesRead = readStream.Read(buffer, 0, Length);
// write the required bytes
while (bytesRead > 0)
{
writeStream.Write(buffer, 0, bytesRead);
bytesRead = readStream.Read(buffer, 0, Length);
}
readStream.Close();
writeStream.Close();
}
}
I'm follow the demos to the letter and i keep getting zero (or null depends if int or int?) what's doing wrong?
Ps. I use the 2.5.1 nuget version of sqlite.net pcl & 2.5.1 sqlite.net.plataform.xamarin.android
I know there are other questions with similar titles, but non of them solved my issue. I am trying to develop a notes app, I want to show the notes titles in a listview, and when the user clicks on one item the details of the note will appear where he/she can edit it. I am using loader and simplecursoradapter to do that, but after editing a note and clicking save, nothing appears in the list. Here is the code of the main activity where I use the loader inside the fragment,
public class NotesList extends ActionBarActivity {
//go back to home.
@Override
public void onBackPressed() {
Intent intent = new Intent(Intent.ACTION_MAIN);
intent.addCategory(Intent.CATEGORY_HOME);
intent.setFlags(Intent.FLAG_ACTIVITY_NEW_TASK);
startActivity(intent);
}
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main_notes_activity); //inflates main_notes_activity as our activity will contain only one
//child which is the fragment.
if (savedInstanceState == null) {
/* if no bundle is found, use fragment manager to make operations on fragments, note that
the fragment we are creating is a dynamic fragment since it's not defined in the xml file. That's why we are using the fragment
manager with begin transaction method since these are used for dynamic fragments, then we add a new fragment using add
method after that we commit the change.*/
getSupportFragmentManager().beginTransaction()
.add(R.id.container, new NotesFragment())
.commit();
}
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.list_menu, menu);
return true; /*true= yes we do have an option menu.*/
}
@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) {//open the settings activity to enable the user to change the settings.
//open settings activity via intent here.
startActivity(new Intent (this, Settings.class));
return true;
}
if (id==R.id.insert){ //open the details activity where the user can enter their notes and save it.
Intent intent = new Intent (this,NotesDetails.class );
startActivity(intent);
return true; //this line is necessary
}//end if
return super.onOptionsItemSelected(item);
}
/**
* A placeholder fragment containing a simple view.
*/
public static class NotesFragment extends Fragment implements LoaderManager.LoaderCallbacks <Cursor> {
private String [] from = {NotesTable.COLUMN_SUMMARY}; //we only need to biew the name of the note the main list.
private int [] to = {R.id.label}; //we will mao the summary col returned from the loadercursor to the label text view.
SimpleCursorAdapter mSimpleCursorAdapter;
private static final int LOADER_ID=0;//give our loader an id of 0.
/*DONT FORGET LOADER MANAGER*/
//maybe error is here.
@Override
public void onActivityCreated (Bundle savedInstances)
{
super.onActivityCreated(savedInstances);
mSimpleCursorAdapter=new SimpleCursorAdapter(getActivity(),R.layout.notes_row,null, from, to,0);
getLoaderManager().initLoader(LOADER_ID, null, this); //once this is done onCreateLoader will be called.
}
@Override
public View onCreateView(LayoutInflater inflater, ViewGroup container,
Bundle savedInstanceState) {
View rootView = inflater.inflate(R.layout.fragment_todo_list, container, false);
ListView listView = (ListView) rootView.findViewById(R.id.notes_list); //findViewById must be called using the rootView because we are inside a fragment.
listView.setAdapter(mSimpleCursorAdapter);
listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> adapterView, View view, int position, long l) {
Cursor cursor = mSimpleCursorAdapter.getCursor();
if (cursor != null && cursor.moveToPosition(position)) {
String category= cursor.getString(1);
String summary= cursor.getString(2);
String description=cursor.getString(3);
String [] retrievedData= {category, summary, description};
Intent intent = new Intent (getActivity(),NotesDetails.class) ;
intent.putExtra(Intent.EXTRA_TEXT, retrievedData);
startActivity(intent);
}
}
});
return rootView;
}
@Override
public Loader<Cursor> onCreateLoader(int id, Bundle args) {
//put the query by which you will be able to observe data here.
String [] projection ={NotesTable._ID,NotesTable.COLUMN_CATEGORY,NotesTable.COLUMN_SUMMARY, NotesTable.COLUMN_DESCRIPTION};
CursorLoader cursorLoader= new CursorLoader(getActivity(),NotesTable.NOTES_URI,projection,null,null,null);
return cursorLoader;
}
@Override
public void onLoadFinished(Loader<Cursor> loader, Cursor data) {
mSimpleCursorAdapter.swapCursor(data);
}
@Override
public void onLoaderReset(Loader<Cursor> loader) {
mSimpleCursorAdapter.swapCursor(null);
}
}
}
Here is the detailed activity where the user clicks on save:
public class NotesDetails extends ActionBarActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
final ContentResolver resolver = getApplicationContext().getContentResolver();
super.onCreate(savedInstanceState);
setContentView(R.layout.notes_edit);
Button saveButton = (Button) findViewById(R.id.todo_edit_button);
final EditText noteSummary = (EditText) findViewById(R.id.todo_edit_summary);
final EditText noteDescription = (EditText) findViewById(R.id.todo_edit_description);
final Spinner noteCategory= (Spinner) findViewById(R.id.category);
if (getIntent().getStringArrayExtra(Intent.EXTRA_TEXT)!=null){
String [] receivedData=getIntent().getStringArrayExtra(Intent.EXTRA_TEXT);
if (receivedData [0].equalsIgnoreCase("important")){
noteCategory.setSelection(0);}//end if
else {
noteCategory.setSelection(1);} //end else
noteSummary.setText(receivedData[1]);
noteDescription.setText(receivedData[2]);
}//end if (feasible only if the received intent contains an array of string, which happens if the user clicks onm one of the items in the simplecursoradapter view)
saveButton.setOnClickListener(new View.OnClickListener() {
String summary= null;
String description= null;
String category= null;
@Override
public void onClick(View view) { //this will happen every time you click the button.
summary= noteSummary.getText().toString();
description= noteDescription.getText().toString();
category = noteCategory.getSelectedItem().toString();
Toast toast ;
if (summary.isEmpty()) {
toast = Toast.makeText(NotesDetails.this, "Summary cannot be empty", Toast.LENGTH_SHORT);
toast.show();
}
else {
ContentValues values = new ContentValues();
values.put(NotesTable.COLUMN_CATEGORY, category);
values.put(NotesTable.COLUMN_SUMMARY, summary);
values.put(NotesTable.COLUMN_DESCRIPTION, description);
//start inserting into the db via provider.
Uri uri =resolver.insert(NotesTable.NOTES_URI, values);
Intent intent = new Intent(NotesDetails.this, NotesList.class);
startActivity(intent);
}
}//end onClick
}); //end onClickListener
}//ok done.
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.list_menu, menu);
return false; /*true= yes we do have an option menu.*/
}
@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) {
//open settings activity via intent here.
startActivity(new Intent(this, Settings.class));
return true;
}
return super.onOptionsItemSelected(item);
}}
Provider code:
public class NotesProvider extends ContentProvider{
// database
private static TodoDatabaseHelper mdatabaseHelper; //used to retrieve an obj representation of the database.
// used for the UriMacher
private static final int NOTES = 10; //in case an entire table were requested to be returned.
private static final int NOTES_ID = 20; //in case a specific item with a specific uri needs to be returned.
//Let's build the UriMatcher:
static UriMatcher sUriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
static {
sUriMatcher.addURI(NotesContract.CONTENT_AUTHORITY, NotesContract.NOTES_PATH, NOTES); //match an entire table.
sUriMatcher.addURI(NotesContract.CONTENT_AUTHORITY, NotesContract.NOTES_PATH +"/#", NOTES_ID);
}//end matching static block.
@Override
public boolean onCreate() {
//get a dbHelper obj,
mdatabaseHelper = new TodoDatabaseHelper(getContext()); //why? Because this will help us retrieve a readable/writable db on which we can make operations like CRUD.
return true; //we were successfully able to create an obj representation of the DB.
}
@Override
public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
//here we should use the uri matcher to know how we can conduct the query.
SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
queryBuilder.setTables(NotesContract.NotesTable.TABLE_NAME); //could be used for joins also.
verifyColumns (projection); //verify if the columns are valid.
int match= sUriMatcher.match(uri); //save the int associated with this uri in a var to be able to use it in the switch.
Cursor cursor=null; //initialize cursor, it will be used to return the result of the query.
SQLiteDatabase db= mdatabaseHelper.getReadableDatabase(); //we need this because we use an SQLiteQueryBuilder.
switch (match) //start the switch
{
case NOTES:
// call method query and send the params.
cursor= queryBuilder.query(db, projection, selection,
selectionArgs, null, null, sortOrder);
break; //don't go to next case.
case NOTES_ID:
//first initialize the where clause in the queyBuilder:
queryBuilder.appendWhere(NotesContract.NotesTable._ID + "=" + ContentUris.parseId(uri));
//invoke the query method:
cursor= queryBuilder.query(db, projection, selection,
selectionArgs, null, null, sortOrder);
break;
default:
throw new IllegalArgumentException("Unknown URI: " + uri);
}//end switch
/*the queries above could be built using mSQLiteHelper.getReadableDatabase.query(..)*/
cursor.setNotificationUri(getContext().getContentResolver(), uri); //very important and required, to update the ui when the data change.
return cursor; //return the query result.
}//end query.
@Override
public String getType(Uri uri) {
return null;
}
@Override
public Uri insert(Uri uri, ContentValues contentValues) {
int match = sUriMatcher.match(uri);
SQLiteDatabase db = mdatabaseHelper.getWritableDatabase();
long id = 0;
switch (match) {
case NOTES:
id = db.insert(NotesContract.NotesTable.TABLE_NAME, null, contentValues);
break;
default:
throw new IllegalArgumentException("Unknown URI: " + uri);
}
getContext().getContentResolver().notifyChange(uri, null); //notify observers about any update.
return Uri.parse(NotesContract.NOTES_PATH + "/" + id);
}
@Override
public int delete(Uri uri, String selection, String[] selectionArgs) {
int match = sUriMatcher.match(uri);
SQLiteDatabase db = mdatabaseHelper.getWritableDatabase();
int rowsDeleted = 0;
switch (match) {
case NOTES:
rowsDeleted = db.delete(NotesContract.NotesTable.TABLE_NAME, selection, selectionArgs);
break;
case NOTES_ID:
if (selection.isEmpty()){
String whereClause= NotesContract.NotesTable._ID + "=" + ContentUris.parseId(uri);
rowsDeleted = db.delete(NotesContract.NotesTable.TABLE_NAME, whereClause, null);}
else{
rowsDeleted = db.delete(NotesContract.NotesTable.TABLE_NAME,
NotesContract.NotesTable._ID + "=" + ContentUris.parseId(uri)
+ " and " + selection,
selectionArgs);
}//end else
break;
default:
throw new IllegalArgumentException("Unknown URI: " + uri);
}//end switch
getContext().getContentResolver().notifyChange(uri, null); //if any delete happens notify the observers.
return rowsDeleted;
}//end delete
@Override
public int update(Uri uri, ContentValues contentValues, String selection, String[] selectionArgs) {
SQLiteDatabase db = mdatabaseHelper.getWritableDatabase(); //return a writable obj from the db.
int rowsUpdated = 0;
int match= sUriMatcher.match(uri);
switch (match) {
case NOTES:
rowsUpdated = db.update(NotesContract.NotesTable.TABLE_NAME,
contentValues,
selection,
selectionArgs);
break;
case NOTES_ID:
long id = ContentUris.parseId(uri);
if (TextUtils.isEmpty(selection)) {
rowsUpdated = db.update(NotesContract.NotesTable.TABLE_NAME,
contentValues,
NotesContract.NotesTable._ID + "=" + id,
null);
} else {
rowsUpdated = db.update(NotesContract.NotesTable.TABLE_NAME,
contentValues,
NotesContract.NotesTable._ID + "=" + id
+ " and "
+ selection,
selectionArgs);
}
break;
default:
throw new IllegalArgumentException("Unknown URI: " + uri);
}
getContext().getContentResolver().notifyChange(uri, null);
return rowsUpdated;
}
private void verifyColumns (String [] cols) {
//lets get the columns in the Db and compare.
String[] existed = {NotesContract.NotesTable._ID, NotesContract.NotesTable.COLUMN_CATEGORY,
NotesContract.NotesTable.COLUMN_DESCRIPTION,
NotesContract.NotesTable.COLUMN_SUMMARY};
if (cols != null) {
ArrayList<String> existedCols = new ArrayList<String>(Arrays.asList(existed)); //this is how we convert an array to an arraylist.
ArrayList<String> requestedCols = new ArrayList<String>(Arrays.asList(cols));
// check if all columns which are requested are available
if (!existedCols.containsAll(requestedCols)) {
throw new IllegalArgumentException("Some columns don't exist in the database");
}//end if.
}//end outer if.
}//end verification.
public static Cursor searchDescription (String key)
{
String desc []= {"%"+key +"%"};
SQLiteDatabase db = mdatabaseHelper.getReadableDatabase();
Cursor rtCursor= db.rawQuery("select * from " + NotesContract.NotesTable.TABLE_NAME+ " where " +
NotesContract.NotesTable.COLUMN_DESCRIPTION + " like ?", desc);
return rtCursor;
}//end searchDescription
}//end Provider.
Contract code:
public class NotesContract {
public static final String CONTENT_AUTHORITY="com.project.android.notes.data.NotesProvider"; /*this is
the path in which the content provider resides. Remember we are making an agreement on the underlying data
scheme, and the ways by which we can access it*/
//Now, building the uri from the authority string.
public static final Uri BASE_CONTENT_URI= Uri.parse("content://" + CONTENT_AUTHORITY);/*DON'T FORGET "CONTENT://" IT DEFINED THE SCHEME*/
//Next, we define one string that reflects the table name, in order to add it to a path uri later.
public static final String NOTES_PATH= "notes";
public static final class NotesTable implements BaseColumns {
//first we build the uri of this table using the NOTES_PATH string defined earlier.
public static Uri NOTES_URI = BASE_CONTENT_URI.buildUpon().appendPath(NOTES_PATH).build(); //this can be used later as a base on which
//we can build upon another parts of the uri.
//Now, define the columns:
// no need to define a specific ID column, as any class that implements BaseColumns have _ID automatically.
public static final String TABLE_NAME = "notes";
public static final String COLUMN_CATEGORY = "category";
public static final String COLUMN_SUMMARY = "summary";
public static final String COLUMN_DESCRIPTION = "description";
public static Uri buildUriWithId (long id)
{
Uri uriWithId = ContentUris.withAppendedId(NOTES_URI, id);
//withAppendedId takes a table uri and a row id, then creates a full uri from them.
return uriWithId;
}//end buildUriWithId
//Let's define the mime types that can be returned from the table.
//These data can be either a set of items or a single item.
public static final String CONTENT_TYPE =
"vnd.android.cursor.dir/" + CONTENT_AUTHORITY + "/" + NOTES_PATH; //this is a set of items.
public static final String CONTENT_ITEM_TYPE =
"vnd.android.cursor.item/" + CONTENT_AUTHORITY + "/" + NOTES_PATH; //this is a single item.
}//end inner class.
}//end Contract class.
DB Helper code:
public class TodoDatabaseHelper extends SQLiteOpenHelper {
//fields needed:
public static final String DB_NAME="notes.db";
public static final int DB_VERSION= 1;
public final String LOG_TAG= TodoDatabaseHelper.class.getSimpleName(); //used for log statements in the onUpgrade.
//the create query:
private static final String DATABASE_CREATE = "create table "
+ NotesTable.TABLE_NAME
+ "("
+ NotesTable._ID + " integer primary key autoincrement, " //again as I said in the contract, no need to define a specific ID column.
+ NotesTable.COLUMN_CATEGORY + " text not null, "
+ NotesTable.COLUMN_SUMMARY + " text not null,"
+ NotesTable.COLUMN_DESCRIPTION
+ " text not null"
+ ");";
//end create query
public TodoDatabaseHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION); //if DB_VERSION is not static this won't work.
}//end constructor.
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
//first we want to have SQLiteDb object to be able to create a database using the create command.
sqLiteDatabase.execSQL(DATABASE_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
Log.w(LOG_TAG, "Upgrading database from version "
+ oldVersion + " to " + newVersion
+ ", which will destroy all old data"); /*the class that contains the table is NotesTable, the name
assigned to the table is TABLE_NAME*/
sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + NotesTable.TABLE_NAME);
onCreate(sqLiteDatabase); //after clearing the table fill it again by calling onCreate.
}
}
When I edit a note and click on the save button I get this log:
02-28 10:00:06.827: E/SQLiteLog(27827): (257) Open fd: 65, file: /data/data/com.project.android.notes/databases/notes.db-journal
02-28 10:00:06.827: E/SQLiteLog(27827): (257) Close fd: 65
02-28 10:00:06.829: E/SQLiteLog(27827): (257) Open fd: 65, file: /data/data/com.project.android.notes/databases/notes.db-journal
02-28 10:00:06.859: E/SQLiteLog(27827): (257) Close fd: 65
02-28 10:00:06.958: E/SQLiteLog(27827): (257) Open fd: 69, file: /data/data/com.project.android.notes/databases/notes.db-journal
02-28 10:00:06.958: E/SQLiteLog(27827): (257) Close fd: 69
02-28 10:00:07.564: E/HwSystemManager(1208): :ACTION_BATTERY_CHANGED pluged =2
02-28 10:00:10.107: E/(180): AudioCloseDumpPCMFile file== NULL
02-28 10:00:10.107: E/(180): AudioCloseDumpPCMFile file== NULL
02-28 10:00:11.614: E/MCA(28234): Here call up the service!
02-28 10:00:11.614: E/MCA(28234): LT passed!
Please help me I've been spending too long time trying to solve the problem, but nothing worked.
Any help will be appreciated.
Thanks
I have a strange problem with a scenario related to SQLite update, hopefully will get some help here.
my table tbl1 looks like below (sqlfiddle)
id col1 col2 col3
1 0 1 0
2 1 0 0
3 0 0 1
4 1 1 1
5 0 0 0
My requirement is that I get some positive number from user for each column and I need to update the rows of a particular column if they contain zero and finally I should count the number of rows updated and difference between the user input and updated count I should add it to the last row
Let me explain with example:
Say user inputs 10 for col1. col1 has 3 rows containing zero so I will update them to 1 and finally the diff i.e. (10 - 3 = 7) I should update in the last row
I hope I did not confuse, after update I expect the table to look like
id col1 col2 col3
1 1 1 0
2 1 0 0
3 1 0 1
4 1 1 1
5 8 0 0
Sql query update tbl1 set col1=1 where col1=0 and id in (select id from tbl1 where col1=0 limit 10)
The above query updates all 0s to 1 but how do I add the remaining to last row?
