mercredi 30 septembre 2015

How to save different data from different class to different database?

Is it possible to save the data from different classes and then save them into different database by one click?

Information.java

 protected void onCreate(Bundle savedInstanceState) {
        Button button=(Button)findViewById(R.id.button6);
        super.onCreate(savedInstanceState);
        setContentView(R.layout.information);
        txtDate = (EditText) findViewById(R.id.editText5);
        String date=txtDate.getText().toString();
        button.setOnClickListener(new View.OnClickListener() {
            public void onClick(View arg0) {
                Intent intent = new Intent(context, WorkForce.class);
                startActivity(intent);
            }
        });
}

WorkForce.java

txtDate1 = (EditText) findViewById(R.id.editText6);
 Button btnSaved=(Button)findViewById(R.id.button5);
        btnSaved.setOnClickListener(new View.OnClickListener() {
            public void onClick(View arg0) {
                String date=txtDate1.getText().toString();
             ts.insertTimeSheet(date);  //data from class Information
              s1.insertData(date1);

            }
        });

The logic of WHERE Clause along with > operator and the sub-query

I don't get the logic for the query 3 as below, and hope someone could give me some idea. For the query 3, SELECT ID, NAME, AGE, SALARY FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY < 20000);

The sub-query would find out the result where the salary < 20000 first, and that is what query2 shown as below. And then the parent query would find out the result where using all the age's record from the table COMPANY(total of 7 record: 18,19,22,23,24,29,37) to compare with the age's result from sub-query(total of 4 record: 18,19,23,29) and then show the greater record based on age.

I expect the result should only show the ID 7 only like below, since only this record is met the condition. The greater age from the result of sub-query(query 2) is 29, so only this record the age is over 29.


ID          NAME        AGE         SALARY  
7           Vicky       37          32500.0 

Unfortunately my expectation is not met, and it show me the result like query 3 as below.

I hope to understand the logic how its work for query 3, and hope someone could assist.

1.sqlite> SELECT ID, NAME, AGE, SALARY FROM COMPANY;


ID          NAME        AGE         SALARY 
1           John        24          21000.0   
2           Davy        22          20000.0   
3           Kenny       19          9700.0    
4           Henry       23          13555.0   
5           Sam         18          17000.0   
6           Ray         29          8000.0    
7           Vicky       37          32500.0   

2.sqlite> SELECT ID, NAME, AGE, SALARY FROM COMPANY WHERE SALARY < 20000;


ID          NAME        AGE         SALARY  
3           Kenny       19          9700.0    
4           Henry       23          13555.0   
5           Sam         18          17000.0   
6           Ray         29          8000.0    

3.sqlite> SELECT ID, NAME, AGE, SALARY FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY < 20000);


ID          NAME        AGE         SALARY    
1           John        24          21000.0   
2           Davy        22          20000.0   
4           Henry       23          13555.0   
6           Ray         29          8000.0    
7           Vicky       37          32500.0   

Top 50% of unsorted column in SQL

How can I get the top 50% of a column without sorting the column in SQL?

I've already seen finding top 50% using group by, top, and limit but can I accomplish this without sorting at all?

Protecting local proprietary data in an Xamarin app

I have a Xamarin.Forms app that uses a local SqLite database as its source for data. The data is proprietary, so I want to protect it so that if someone gets access to the database file, they would have to decrypt it to access the data.

I also want to limit the number of queries users can make against the database so that at a certain point they have to purchase the ability to use more of the data (in-app purchase).

I want to avoid making network calls as much as possible to minimize impact to the user's data plan and allow the app to work well in conditions where there is poor or no connectivity. So, I want the data stored in a local database (perhaps in SqLite).

I'm curious how different people would approach this problem to protect the data and at the same time minimize network usage.

Here is kind of what I was thinking (if it's possible):

1) Let the user download/install the app.

2) On first load, the app will upload a key based on the device id and the user's current purchase information. Then it will download a SqLite database file that has been encrypted using the uploaded key.

3) When the user reaches their limit of queries, the database file is deleted. If they purchase more data, then a new key is uploaded and a new encrypted database is downloaded to be used.

Thoughts? Is there a better way?

Doing math calculations in a SQLite where clause

With Android's SQLite, is it possible to do math calculations on column values in a where clause? For example, say I want to select only the rows that have even values in their column named mColumnName as follows:

query(mTable, mColumns, mColumnName+"%2=?", new String[]{"0"}, null, null, null, null)

Will that work?

Best database design for multiple entity types

I'm working on a web app and I have to design it's database. There's a part that didn't come very straightforward to me, so after some thinking and research I came with multiple ideas. Still neither seems completely suitable, so I'm not sure which one to implement and why.

The simplified problem looks as follows: I have a table Teacher. There are 2 types of teachers, according to the relations with their Fields and Subjects:

  1. A Teacher that's related to a Field, the Field is obligatory related to a Category
  2. A Teacher that's not related to a Field, but directly to a Category

My initial idea was to have two nullable foreign keys, one to the table Field, and the other to the table Category. But in this case, how can I make sure that exactly one is null, and the other one is not?

The other idea is to create a hierarchy, with two types of Teacher tables derived from the table Teacher (is-a relation), but I couldn't find any useful tutorial on this.

I'm developing the app using Django with SQLite db

Returning top 3 count values in sql queries

I have grouped my table based on a column field, and simultaneously displayed count value for each group. For example

user   |    count(user)
user 1 |     4
user 2 |     3
user 3 |     3
user 4 |     3
user 5 |     2

Now I need to display only top 3 users count in this case user 1, user2, user 3, user 4.

I have written the grouping logic but when I try to order values of count by desc and then restrict the result to 3(Limit 3), I only get user 1,2, and 3. Which is not correct because user 4 also comes in top 3 count.

Android, cannot re-open already closed connection for query

I am trying to read data from an SQLite database so that a CursorAdapter can format it into my list view items, but I am receiving an error when I try to use a rawQuery saying that I am attempting to re-open an already closed object. I've looked into this and this error mostly occurs when people try to use their cursor objects after having closed the cursor, but my cursor close is at the end of the method and a new one is created on each call, and the db.close() method has been removed, but to no avail. I've included the relevant classes.

MainActivityFragment

@Override
public View onCreateView(LayoutInflater inflater, ViewGroup container,
                         Bundle savedInstanceState) {
    //Primary View Inflator
    final View rootView = inflater.inflate(R.layout.fragment_main, container, false);

    //Databse instatiation
    final MySQLiteHelper mDbHelper = new MySQLiteHelper(getActivity());

button2.setOnClickListener(new View.OnClickListener() {
        public void onClick(View v) {

        if((noteTitle.getText().toString().trim()).equals("")){
            Toast.makeText(getActivity().getBaseContext(), "No Title Specified", Toast.LENGTH_SHORT).show();
        }else {
            String noteTitleString = noteTitle.getText().toString();
            String noteBodyString = noteBody.getText().toString();

            Toast.makeText(getActivity().getBaseContext(), "Added: " + noteTitleString, Toast.LENGTH_SHORT).show();

            //Clear text fields and hide keyboard
            noteTitle.getText().clear();
            noteBody.getText().clear();
            final InputMethodManager imm = (InputMethodManager)
                    getActivity().getSystemService(Context.INPUT_METHOD_SERVICE);
            imm.hideSoftInputFromWindow(getView().getWindowToken(), 0);

            //Write new row into database and refresh listview
            addNotes(mDbHelper, noteTitleString, noteBodyString);
            readNotes(rootView);
        }
        }
    });
}

private void addNotes(MySQLiteHelper mDbHelper, String title, String body){

    // Gets the data repository in write mode
    SQLiteDatabase db = mDbHelper.getWritableDatabase();

// Create a new map of values, where column names are the keys
    ContentValues values = new ContentValues();
    values.put(FeedEntry.COLUMN_NAME_TITLE, title);
    values.put(FeedEntry.COLUMN_NAME_BODY, body);

// Insert the new row, returning the primary key value of the new row
    db.insert(
            FeedEntry.TABLE_NAME,
            null,
            values);

//        db.close();
}

private void readNotes(View rootView){

    MySQLiteHelper mDbHelper = new MySQLiteHelper(getActivity());
    SQLiteDatabase db = mDbHelper.getWritableDatabase(); //changed from readable

    // Query for items from the database and get a cursor back
    Cursor notesCursor = db.rawQuery("SELECT  * FROM notes", null);

    // Find ListView to populate
    ListView notelv = (ListView) rootView.findViewById(R.id.note_list_view);
    // Setup cursor adapter using cursor from last step
    final NotesListAdapter noteAdapter = new NotesListAdapter(rootView.getContext(), notesCursor, 0);
    //// Attach cursor adapter to the ListView
    notelv.setAdapter(noteAdapter);

    notesCursor.close();

}

MySQLiteHelper

public class MySQLiteHelper extends SQLiteOpenHelper {

private static final String TEXT_TYPE = " TEXT";
private static final String INT_TYPE = " INTEGER";
private static final String COMMA_SEP = ",";
private static final String SQL_CREATE_ENTRIES =
        "CREATE TABLE " + FeedEntry.TABLE_NAME + " (" +
                FeedEntry._ID + " INTEGER PRIMARY KEY," +
                FeedEntry.COLUMN_NAME_TITLE + TEXT_TYPE + COMMA_SEP +
                FeedEntry.COLUMN_NAME_BODY + TEXT_TYPE +
        " )";

private static final String SQL_DELETE_ENTRIES =
        "DROP TABLE IF EXISTS " + FeedEntry.TABLE_NAME;



// If you change the database schema, you must increment the database version.
public static final int DATABASE_VERSION = 1;
public static final String DATABASE_NAME = "FeedReader.db";

public MySQLiteHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
public void onCreate(SQLiteDatabase db) {
    db.execSQL(SQL_CREATE_ENTRIES);
}
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // This database is only a cache for online data, so its upgrade policy is
    // to simply to discard the data and start over
    db.execSQL(SQL_DELETE_ENTRIES);
    onCreate(db);
}
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    onUpgrade(db, oldVersion, newVersion);
}
}

Error Stack

Process: com.example.ggould.scribble, PID: 8408
java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteQuery: SELECT  * FROM notes
        at android.database.sqlite.SQLiteClosable.acquireReference(SQLiteClosable.java:55)
        at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:58)
        at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:152)
        at android.database.sqlite.SQLiteCursor.onMove(SQLiteCursor.java:124)
        at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:214)
        at android.widget.CursorAdapter.getView(CursorAdapter.java:245)
        at android.widget.AbsListView.obtainView(AbsListView.java:2344)
        at android.widget.ListView.measureHeightOfChildren(ListView.java:1270)
        at android.widget.ListView.onMeasure(ListView.java:1182)
        at android.view.View.measure(View.java:17430)
        at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:5463)
        at android.widget.LinearLayout.measureChildBeforeLayout(LinearLayout.java:1436)
        at android.widget.LinearLayout.measureVertical(LinearLayout.java:722)
        at android.widget.LinearLayout.onMeasure(LinearLayout.java:613)
        at android.view.View.measure(View.java:17430)
        at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:5463)
        at android.widget.FrameLayout.onMeasure(FrameLayout.java:430)
        at android.support.v7.internal.widget.ContentFrameLayout.onMeasure(ContentFrameLayout.java:124)
        at android.view.View.measure(View.java:17430)
        at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:5463)
        at android.support.v7.internal.widget.ActionBarOverlayLayout.onMeasure(ActionBarOverlayLayout.java:393)
        at android.view.View.measure(View.java:17430)
        at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:5463)
        at android.widget.FrameLayout.onMeasure(FrameLayout.java:430)
        at android.view.View.measure(View.java:17430)
        at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:5463)
        at android.widget.LinearLayout.measureChildBeforeLayout(LinearLayout.java:1436)
        at android.widget.LinearLayout.measureVertical(LinearLayout.java:722)
        at android.widget.LinearLayout.onMeasure(LinearLayout.java:613)
        at android.view.View.measure(View.java:17430)
        at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:5463)
        at android.widget.FrameLayout.onMeasure(FrameLayout.java:430)
        at com.android.internal.policy.impl.PhoneWindow$DecorView.onMeasure(PhoneWindow.java:2560)
        at android.view.View.measure(View.java:17430)
        at android.view.ViewRootImpl.performMeasure(ViewRootImpl.java:2001)
        at android.view.ViewRootImpl.measureHierarchy(ViewRootImpl.java:1166)
        at android.view.ViewRootImpl.performTraversals(ViewRootImpl.java:1372)
        at android.view.ViewRootImpl.doTraversal(ViewRootImpl.java:1054)
        at android.view.ViewRootImpl$TraversalRunnable.run(ViewRootImpl.java:5779)
        at android.view.Choreographer$CallbackRecord.run(Choreographer.java:767)
        at android.view.Choreographer.doCallbacks(Choreographer.java:580)
        at android.view.Choreographer.doFrame(Choreographer.java:550)
        at android.view.Choreographer$FrameDisplayEventReceiver.run(Choreographer.java:753)
        at android.os.Handler.handleCallback(Handler.java:739)
        at android.os.Handler.dispatchMessage(Handler.java:95)
        at android.os.Looper.loop(Looper.java:135)
        at android.app.ActivityThread.main(ActivityThread.java:5221)
        at java.lang.reflect.Method.invoke(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:372)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:899)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:694)

Viewing Items in a ListView from Database

I'm developing an android application in Android Studio. In this function i wanted to save items in a ListView into a Database and View it in a listView on another java page. Adding the elements in arraylist into database part ok for now as the logcat its working. Now i wants to view it on another page. I've implemented a code for that. But when i clicked view button its not giving any response. Below i've posted the Code of DBAdapter class and java class. Thanks :)

viewHistory.java

public class viewHistoryScr extends Activity implements View.OnClickListener{
private Button viewHist;
private Button back;
private ListView historyView;
DBUserAdapter dbUserAdapter = new DBUserAdapter(viewHistoryScr.this);
String username = DBUserAdapter.KEY_USERNAME;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.historyitmscr);
    viewHist = (Button) findViewById(R.id.viewHistory);
    back = (Button) findViewById(R.id.goBack);
    historyView = (ListView) findViewById(R.id.historyList);
    viewHist.setOnClickListener(this);
    back.setOnClickListener(this);
}

private void viewHistoryItm(){
    Cursor cursor = dbUserAdapter.viewItm(username);
    List<String> values = new ArrayList<String>();
    if (cursor != null && cursor.moveToFirst()){
        do {
            String itmName = cursor.getString(Integer.parseInt(DBUserAdapter.KEY_ITMNAME));

            //String[]values = new String[]{itmName};
            values.add(itmName);
            ArrayAdapter<String> adapter = new ArrayAdapter<String>(this, R.layout.historyitmscr, R.id.historyList, values);
            historyView.setAdapter(adapter);
            cursor.close();
        }
        while (cursor.moveToNext());
    }
}

@Override
public void onClick(View view) {
    if (view.getId() == R.id.addItems) {
        try {
            dbUserAdapter.open();
            viewHistoryItm();
            dbUserAdapter.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    else if (view.getId() == R.id.goBack){
        Intent i = new Intent(viewHistoryScr.this, MainActivity.class);
        startActivity(i);
    }
}
}


DBUserAdapter.java


public class DBUserAdapter{
public static final String KEY_ROWID = "_id";
public static final String KEY_USERNAME = "username";
public static final String KEY_PASSWORD = "password";
public static final String KEY_PASSHINT = "passHint";
public static final String KEY_ITMNAME = "itmName";
//public static final String KEY_ITMNAME = ""
public static final String TAG = "DBAdapter";

String userN = KEY_USERNAME;

String manualUser = KEY_USERNAME;

public static final String DATABASE_NAME = "usersdb";
public static final String DATABASE_TABLE = "userInfo";
public static final String ITM_DATABASE_NAME = "manualItm";
public static final int DATABASE_VERSION = 320;

public static final String DATABASE_CREATE = "CREATE TABLE "+DATABASE_TABLE+"(username TEXT NOT NULL, password TEXT NOT NULL, passHint TEXT NOT NULL);";
public static final String ITM_DATABASE_CREATE = "CREATE TABLE "+ITM_DATABASE_NAME+"(username TEXT NOT NULL, itmName TEXT NOT NULL)";

private Context context = null;
private DatabaseHelper dbHelper;
public SQLiteDatabase db;

public DBUserAdapter(Context context){
    this.context = context;
    dbHelper = new DatabaseHelper(context);
}

private static class DatabaseHelper extends SQLiteOpenHelper{
    DatabaseHelper(Context context){
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE);
        db.execSQL(ITM_DATABASE_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(TAG, "Upgrading Database from Version "+oldVersion+" to "+newVersion+", Which will Destroy all old Data");
        db.execSQL("DROP TABLE IF EXISTS userInfo");
        db.execSQL("DROP TABLE IF EXISTS manualItm");
        onCreate(db);
    }
}

public void open() throws SQLException{
    db = dbHelper.getWritableDatabase();
}

public void close(){
    db.close();
}

public SQLiteDatabase getDatabaseInstance(){
    return db;
}

public boolean AddUser(String username, String password, String passHint){
    try {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_USERNAME, username);
        initialValues.put(KEY_PASSWORD, password);
        initialValues.put(KEY_PASSHINT, passHint);
        db.insert(DATABASE_TABLE, null, initialValues);
        db.close();
        return true;
    }
    catch (Exception e){
        e.printStackTrace();
    }
    return false;
}

public boolean Login(String username, String password) throws SQLException{
    Cursor cursor = db.rawQuery("SELECT * FROM " + DATABASE_TABLE + " WHERE username=? AND password=?", new String[]{username, password});
        if(cursor != null){
        if (cursor.getCount() > 0){
            return true;
        }
    }
    return false;
}

public boolean register(String username, String password, String passHint)throws SQLException{
    Cursor cursor = db.rawQuery("INSERT INTO " + DATABASE_TABLE + " VALUES('?', '?', '?', '?');", new String[]{username, password, passHint});
    if(cursor != null){
        if(cursor.getCount() > 0){
            return true;
        }
    }
    return false;
}


public void insertItm(ArrayList<String>itemsList){
    try{
        db = dbHelper.getWritableDatabase();
        for (int a = 0; a < itemsList.size(); a++){
            ContentValues contentValues = new ContentValues();
            contentValues.put(KEY_ITMNAME, itemsList.get(a));
            contentValues.put(KEY_USERNAME, manualUser);
            db.insert(ITM_DATABASE_NAME, null, contentValues);
        }
        db.close();
    }
    catch (Exception ex){
        Log.e("Error in Adding Items", ex.toString());
    }
}

//public boolean viewItm(String userN){
    //String WHERE = KEY_USERNAME + "=" + username;
 //   Cursor cursor = db.rawQuery("SELECT * FROM " + ITM_DATABASE_NAME + " WHERE username=?", new String[]{userN});
//    if(cursor != null){
 //       cursor.moveToFirst();
 //       return true;
//    }
//    return false;
//}

public Cursor viewItm(String userN){
    Cursor cursor = db.rawQuery("SELECT * FROM " + ITM_DATABASE_NAME + " WHERE username=?", new String[]{userN});
    if (cursor != null){
        cursor.moveToFirst();
    }
    return cursor;
}
}

Add SQLite database data to Listview

I checked out this tutorial: http://ift.tt/1e3HL0t

Now I want to load this data in a Listview. Here is my try:

private ArrayAdapter<Contact> adapter;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);


        DatabaseHandler db = new DatabaseHandler(this);

        /**
         * CRUD Operations
         * */
        // Inserting Contacts
        Log.d("Insert: ", "Inserting ..");
        db.addContact(new Contact("Ravi", "9100000000"));
        db.addContact(new Contact("Srinivas", "9199999999"));
        db.addContact(new Contact("Tommy", "9522222222"));
        db.addContact(new Contact("Karthik", "9533333333"));

        // Reading all contacts
        Log.d("Reading: ", "Reading all contacts..");
        List<Contact> contacts = db.getAllContacts();

        for (Contact cn : contacts) {
            String log = "Id: " + cn.getID() + " ,Name: " + cn.getName() + " ,Phone: " + cn.getPhoneNumber();
            // Writing Contacts to log
            Log.d("Name: ", log);

        }


        // create the adapter using the cursor pointing to the desired data
        //as well as the layout information
        adapter = new ArrayAdapter<Contact>(getApplicationContext(), R.layout.list_view, contacts);


        ListView listView = (ListView) findViewById(R.id.list);
        // Assign adapter to ListView
        listView.setAdapter(dataAdapter);

    }

So the Class from the Contact you can see in the url from androidhive.

sqlite query with second joining

i have table MESSAGES :

enter image description here

holding messages from users conversations

each conversation looks like this:

enter image description here

now i have query which returns me a list of last message ( *last is defined as max timestamps / max message id ) from each conversation

enter image description here

every row in MESSAGES TABLE holds info about one side of conversation who sends message eg. me or other person

when i QUERY I for all conversations i need to know second side of conversation so i need to add to this QUERY II a select / join statement

LEFT JOIN 
 (  SELECT message_conversationId, message_from as message_from2, message_avatar_pic_id as message_avatar_pic_id_from2
    FROM Messages  msg_from
    WHERE message_from <> 'me' COLLATE NOCASE 
    AND user_id == 1
    GROUP BY message_conversationId
  ) not_me ON message_conversationId = not_me.message_conversationId

to have this result:

enter image description here

How to combine those two queries I + II ?

Sqlite foreign key not working

I followed a tutorial online on how to create a database. I have 4 tables, drive over, tyres, error and vehicle. I also added foreign keys to it and enabled it using the onConfigure method. When I run this code, and insert some data in, the Pragma foreign key is still equal to 0. I am really confused and don't know how to proceed to.

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class Database extends SQLiteOpenHelper {

  public static final String DATABASE_NAME = "allan303.db";
  public static final int DATABASE_VERSION = 1;

  public static final String TABLE_DRIVE_OVER = "drive_over";
  public static final String DRIVE_OVER_ID = "drive_over_id";
  public static final String DATE = "date";
  public static final String VECHILE_LOG = "vechile_log";
  public static final String ERROR_LOG = "error_log";

  public static final String TABLE_VECHILES = "vechiles";
  public static final String VECHILES_ID = "vechiles_id";
  public static final String MODEL = "model";
  public static final String YEAR = "year";
  public static final String NUMBER_PLATE = "number_plate";

  public static final String TABLE_TYRES = "tyres";
  public static final String TYRES_ID = "tyres_id";
  public static final String DRIVE = "drive_log";
  public static final String AXLE = "axle";
  public static final String TYRE = "tyre";
  public static final String PRESSURE = "pressure";
  public static final String UNITS = "units";

  public static final String TABLE_ERRORS = "errors";
  public static final String ERRORS_ID = "errors_id";
  public static final String MESSAGE = "message";


  private static final String CREATE_TABLE_VECHILES = "create table " +      TABLE_VECHILES + " ("+ VECHILES_ID + " integer primary key, "
        + MODEL + " text, " + YEAR + " text, " + NUMBER_PLATE + " text " +   ");";


  private static final String CREATE_TABLE_ERRORS = "create table " + TABLE_ERRORS + " ("+ ERRORS_ID + " integer primary key, " +
        MESSAGE + " text " + ");";

  private static final String CREATE_TABLE_DRIVE_OVER = "create table " + TABLE_DRIVE_OVER+ " (" + DRIVE_OVER_ID + " integer primary key autoincrement, "
        + DATE + " text, " + VECHILE_LOG+ " integer, " + ERROR_LOG + " integer," + " FOREIGN KEY ("+VECHILE_LOG+") REFERENCES "+TABLE_VECHILES+"("+VECHILES_ID+"), "
        + " FOREIGN KEY ("+ERROR_LOG+") REFERENCES "+TABLE_ERRORS+"("+ERRORS_ID+"));";

 private static final String CREATE_TABLE_TYRES = "create table "
        + TABLE_TYRES + " ("
        + TYRES_ID + " integer primary key autoincrement, "
        + DRIVE + " integer, "
        + AXLE + " integer, "
        + TYRE + " integer, "
        + PRESSURE + " integer, "
        + UNITS + " text, "
        + " FOREIGN KEY ("+DRIVE+") REFERENCES "+TABLE_DRIVE_OVER+"("+DRIVE_OVER_ID+"));";


 private static Database sInstance;

 public static synchronized Database getInstance(Context context){
    if(sInstance == null){
        sInstance = new Database(context.getApplicationContext());
    }
    return sInstance;
}

  public Database(Context context){
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

 @Override
 public void onConfigure(SQLiteDatabase db){
    super.onConfigure(db);
    db.setForeignKeyConstraintsEnabled(true);
}


  @Override
  public void onCreate(SQLiteDatabase db){
      db.execSQL(CREATE_TABLE_DRIVE_OVER);
      db.execSQL(CREATE_TABLE_VECHILES);
      db.execSQL(CREATE_TABLE_TYRES);
      db.execSQL(CREATE_TABLE_ERRORS);
}

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
    if(oldVersion != newVersion){
        db.execSQL("DROP TABLE IF EXISTS" + TABLE_DRIVE_OVER);
        db.execSQL("DROP TABLE IF EXISTS" + TABLE_VECHILES);
        db.execSQL("DROP TABLE IF EXISTS" + TABLE_TYRES);
        db.execSQL("DROP TABLE IF EXISTS" + TABLE_ERRORS);
        onCreate(db);
    }
}

How to pass value from an activity to another in Android [duplicate]

This question already has an answer here:

This function is to create buttons programmatically according to the number of goals. I created them successfully however i want to pass the goal_id assigned when a certain button is clicked to another activity.

FOR EXAMPLE: if the user created 3 goals

button 1 : goal 1

button 2 : goal 2

button 3 : goal 3

assuming the user clicked BUTTON 3, it will get the goal_id of Goal 3 and passed it to another activity. <-- This i don't know how.

VARIABLES:

private Button b;
    private LinearLayout goals;
    private ArrayList<Integer> goalidArray;

    public TaskListActivity task;
    public int tempHolder;

    //database variables
    MyDBAdapter dbhandler;

OnCreate Function

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

        goals = (LinearLayout)findViewById(R.id.goalList);
        task = new TaskListActivity();
        goalidArray = new ArrayList<Integer>();
    //create database
    dbhandler = new MyDBAdapter(this);

    back(); // to go back button

    displayAllGoals();

}


public void displayAllGoals(){
            //get a list of all goals
            List<Goals> allGoals = dbhandler.getAllGoals();


            for (Goals goal : allGoals){

                b = new Button(this);
                b.setLayoutParams(new LinearLayout.LayoutParams(LinearLayout.LayoutParams.MATCH_PARENT, LinearLayout.LayoutParams.MATCH_PARENT));
                b.setText(goal.getGoalName());
                b.setId(goal.getGoalId());
                b.setTextColor(Color.BLACK);

                //adding it on the array
                goalidArray.add(goal.getGoalId());
                goals.addView(b);

                // when button is clicked
                b.setOnClickListener(new View.OnClickListener() {
                    int clickedid = b.getId();
                    int i = 0;
                    int tempid;
                    //get total no of goals
                    long goal_counts = dbhandler.getGoalsCount();
                    @Override
                    public void onClick(View v) {
                       while( i < goal_counts-1){
                           if(goalidArray.get(i) == clickedid){
                               tempid = i;
                               Intent myIntent = new Intent(ViewGoalsActivity.this, TaskListActivity.class);
                               String temp = Integer.toString(goalidArray.get(tempid));
                               MessageTo.message(ViewGoalsActivity.this,temp); // its like a TOAST, to see if i was able to get the goal id
                               //Create the bundle
                               Bundle bundle = new Bundle();
                               //Add your data to bundle
                               bundle.putString("goalid",temp);
                               //Add the bundle to the intent
                               myIntent.putExtras(bundle);
                               startActivity(myIntent);
                           }else
                               i++;
                       }

                    }
            });
        }}

It will go error when i clicked a button. The emulator lags and appears to be stuck.

TO THE ONE WHO POST THIS AS A DUPLICATE didn't even read the whole thing. This is different from the others.

How to make Sqlite works for Android with Unity and SQLiteUnityKit

I'm working on a unity app, essentially for Android at the moment.

To make this app i need to use SQL and it looks like SQLite is well supported on every platform.

Despite this, i'm having trouble to make it work properly.

First here is what i used SQLiteUnityKit

It looks like a lot of people have been using this framework for building SQL app.

Here is the code i got to try and make it work from Doc

public class DatabaseManager : MonoBehaviour
{
SqliteDatabase sqlDB;

public Text Firstname;

void Awake() 
{
    string dbPath = System.IO.Path.Combine (Application.persistentDataPath, "game.db");
    var dbTemplatePath = System.IO.Path.Combine(Application.streamingAssetsPath, "default.db");

    if (!System.IO.File.Exists(dbPath)) {
        if (Application.platform == RuntimePlatform.Android)
        {
            WWW reader = new WWW(dbTemplatePath);
            while ( !reader.isDone) {}
            System.IO.File.WriteAllBytes(dbPath, reader.bytes);
        } else {
            System.IO.File.Copy(dbTemplatePath, dbPath, true);
        }
    }
    sqlDB = new SqliteDatabase(dbPath);
    //Insert
    string query = "insert into person values('B', 'David')";
    sqlDB.ExecuteNonQuery(query);
    query = "insert into person values('B', 'Alex')";
    sqlDB.ExecuteNonQuery(query);
    //Query
    var result = sqlDB.ExecuteQuery("SELECT * FROM person");
    var row = result.Rows[1];
    Debug.Log("lastname=" + (string)row["lastname"]);
    Debug.Log("firstname=" + (string)row["firstname"]);
    Firstname.text = (string)row ["firstname"];
}
}

The problem is that basically this code works fine when i launch the project as you can see:

enter image description here

But in my Android Emulator:

enter image description here

There is never a name coming. It just stays with "my lastname" which is nothing more than a placeholder..

They added the Android support and a lot of people have been using it so i guess i do something wrong but i can't figure out what it is..

Thank you guys for helping !

Get multiple rows from sqlite table IOS

I'm trying to fetch multiple rows from sqlite table by id using FMDB. Here is my code.

FMResultSet *results = [db executeQuery: @"SELECT * FROM brands WHERE brand_id = ?", arrayOfNumbers];

The number of items in array and their values can be different each time.

Looking for DB Query to fetch ids. Thanks in advance!

objective-c using local database on iphone and sync it webservice

I am trying to use sqlite in my ios application.It will keep data in device with sqlite.And it must sync data from webservice.

Actually my friend is an android developer and he used it with syncadapter in android.What should I do ? What will you suggest to me ?

Thank You

Error while calling sql statement

i am getting an weirded error, I have my code to get data from local db.my query is as follows

SELECT * FROM Survey_Details Where feedBack_status="YES"

When i fire above query the function gives proper output.But when i pass NO it gives me an error.Following is my function

-(NSMutableArray*)selectFromDBAndAddtoArrayForFeedbackStatus:(NSString *)feedBackStatus
{
   NSMutableArray *TableArray = [[NSMutableArray alloc]init];
   sqlite3_stmt    *statement;


const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &passionDB) == SQLITE_OK)
{

    NSString *insertSQL = [NSString stringWithFormat: @"SELECT * FROM Survey_Details Where feedBack_status != \"%@\"",feedBackStatus];
    NSLog(@"insert stmnt %@", insertSQL);
    const char *insert_stmt = [insertSQL UTF8String];


if(sqlite3_prepare_v2(passionDB, insert_stmt, -1, &statement, nil)== SQLITE_OK)
    {
        NSLog(@"SQLITE_OK");
        if (SQLITE_ROW != sqlite3_step(passionDB))
        {

        }
        else{

        }
        while(sqlite3_step(statement) == SQLITE_ROW)
        {
            NSMutableArray *editTableArray = [[NSMutableArray alloc]init];

            NSString *title = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];

            NSString *name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];

            NSString *surname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];

            NSString *time = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 14)];

            // NSString *user_ID = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];

            NSNumber *user_ID = [NSNumber numberWithInt:(int)sqlite3_column_int(statement, 1)];


            [editTableArray addObject:title];
            [editTableArray addObject:name];
            [editTableArray addObject:surname];
            [editTableArray addObject:time];
            [editTableArray addObject:user_ID];

            NSString *imagePath;

            if ([feedBackStatus isEqualToString:@"YES"])
            {

                if(sqlite3_column_text(statement, 13) != nil)
                {
                    imagePath = [NSString stringWithUTF8String:(char    *)sqlite3_column_text(statement, 13)];
                }
                else
                {
                    imagePath = @"none";
                }
                [editTableArray addObject:imagePath];
            }


            [TableArray addObject:editTableArray];
            editTableArray=nil;

        }

        NSLog(@"TableArray1 is %@",TableArray);
    }

    if (sqlite3_step(statement) == SQLITE_DONE)

    {
        NSLog(@"sqlite is done");
        NSLog(@"Error %s while sqlite3_step ", sqlite3_errmsg(passionDB));
    } else {
        NSLog(@"sqlite is not  done");
        NSLog(@"Error %s while sqlite3_step ", sqlite3_errmsg(passionDB));
    }
    sqlite3_finalize(statement);
    sqlite3_close(passionDB);
}
NSLog(@"TableArray2 is %@",TableArray);
return TableArray;
}

Merging and averaging tables in a database (Python)

I would like to merge and average the values in different database tables using Python. My database database.db contains tables named in the following way: 'STATE'_'TOWN'. For example, some table names are:

CALIFORNIA_SANFRAN

CALIFORNIA_LOSANGELES

CALIFORNIA_SANDIEGO

...

To illustrate, the CALIFORNIA_SANFRAN table contains the following (example) data:

Date (PRIMARY KEY)      Morning    Day    Evening   Night
01.01.2014              0.5        0.2     0.2      0.1
02.01.2014              0.6        0.1     0.2      0.2
...

and the CALIFORNIA_LOSANGELES table contains:

Date (PRIMARY KEY)      Morning    Day    Evening   Night
01.01.2014              0.3        0.4     0.4      0.3
02.01.2014              0.4        0.3     0.1      0.2
...

ect.

From these tables I would like to obtain one table named after the State (CALIFORNIA) in a separate database (let's call it database2.db) with the averages of all the values in tables CALIFORNIA_SANFRAN, CALIFORNIA_LOSANGELES, CALIFORNIA_SANDIEGO for each date. I.e. I want database2.db, table 'CALIFORNIA' to contain something like:

 Date (PRIMARY KEY)      Morning    Day    Evening   Night
    01.01.2014              0.4     0.3     0.3      0.2
    02.01.2014              0.5     0.2     0.15     0.2
    ....

I haven't been able to find anything that I can use online so far so any help would be greatly appreciated.

PS. I've used and am most familiar with using SQLite to work with databases from Python.

Save Data in Sqlite onUpgrade

I have a SQLite Database file in assets which I copy to a local Sqlite Database. The SQlite Database file has more than 600 entrys and I do not know which entrys get updated exactly.

When I get the new SQLite file I change the Databaseversion number, delete the local database and copy the new Database into a new local database.

In the SQLite Database are two tables where the user can change things in the local database.

Now I want to upgrade the whole database without loosing the data from the two user tables.

I thought I could read the data from the two tables (into corresponding object lists) , upgrade the database and add the objects again.

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    List[] savedlist = savebeforeUpgrading();
    myContext.deleteDatabase(DATABASE_NAME);

    onCreate(db);        
}

Up to this point everything is working fine. I get a List with my objects. Now there is the problem.

When I try to add the objects back into the new Database inside the onUpgrade method I get either "IllegalStateException: getDatabase called recursively" or "SQLiteReadOnlyDatabaseException: attempt to write a readonly database " when I use the SQLiteDatabase from the onUpgrade method.

How can I get around this problem and save the two tables during the onUpgrade process?

creating a spatialite database programmatically

I am trying to create a new spatialite database in a c# program. I couldn't find a way to create an empty spatialite database having all spatial view and functions.

I tried creating an sqlite database, then use it as a spatialite database by loading the extension module (libspatialite4.dll). With this way i can create spatial columns and insert spatial data successfully but i cannot create any Spatial Indexes (No error but simply no index is created when i run the CreateSpatialIndex command. Also, there are no spatialite related objects in database, probably because i created it as an sqlite database.

Anyone knows how to create an empty spatialite database inside a c# program or how to install spatialite related db objects to an existing sqlite database?

How to convert arbitrary database to sqlite android by creating manager classes?

I know that the elegant way of handing a SQLite database in android is through creating manager classes that hold table constants and operations quote: http://ift.tt/1hyiL4m.

Does there exist a tool that will automatically generate these classes for an arbitrary, or at least some type of database D and once the android SQLite database E is generated, transfer the data from D to E.

Maybe a tool that at least uses the database schema, to generate the manager classes and I can program the part of transfering data for myself?

Thanks!

Default column value during and after adding a column

If I specify a default when adding a non-null column to a table ...

ALTER TABLE foo
ADD COLUMN bar INT DEFAULT 42 NOT NULL

... does the bar column continue to have a default value, or is the default only used while adding the column?

Android Studio - Retrieve Data From SQLite Database and insert into Spinner

I'm relatively new to Android Studio so sorry if this is a quick fix

I'm trying to retrieve a column from my database put all records from that column into an arrayList to that then the data in the list can be used to populate a spinner

The code I have so far is as follows;

try {

            //String sql = "INSERT INTO Inventory(NAME,AMOUNT,PRICE,AMOUNTLEFT,WEIGHT) VALUES('Apple','30','1.99','28','1.01')";
            //db.execSQL(sql);
            Cursor c = db.rawQuery("SELECT NAME FROM Inventory", null);
            if (c.getCount() == 0)
            {
                showMessage("Welcome!", "Please Add Items To Your Inventory");
            }
            List InventoryList = new ArrayList();
            Integer i=0;
            if(c.moveToFirst())
            {
                do {
                    InventoryList.add(i, c.getColumnIndex("1"));
                    i+=1;
                }while(c.moveToNext());
            }
            c.close();
            db.close();
            flav1InvSpinner = (Spinner)findViewById(R.id.Combo_InvChoice1);
            ArrayAdapter<String> dataAdapter = new ArrayAdapter<String>(this, android.R.layout.simple_spinner_item, InventoryList);
            dataAdapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
            flav1InvSpinner.setAdapter(dataAdapter);




        }
        catch(Exception e)
        {
            showMessage("Error", "Cannot Connect To Database \n" + e.getMessage() );
        }

However the combo box is only displaying -1 when the emulator is running I'd expect it to display 'Apple'

Any suggestions as to what I've done wrong / missed

Thanks

SQlite Single-thread Mode and Transactional Locks

I am a bit confused between two concepts, namely, Threading modes of SQlite and Database locks. Is there a relation between the threading mode and the database locks? The documentation states "Single-thread. In this mode, all mutexes are disabled and SQLite is unsafe to use in more than a single thread at once.". Does it mean that no locks are applied in the case of Single-thread mode? What does it actually means to be "unsafe to use". Or are these entirely different concepts at different layers of abstractions and I am mixing them up!!

Use non monoBehaviour class in unity

I'm going through a bug or at least something i don't really get.

I'm trying to use sqlite database in my unity project.

Here is the error i get:

enter image description here

The DatabaseManager class is a MonoBehaviour class.

Here is what it looks like:

using UnityEngine;
using System.Collections;

public class DatabaseManager : MonoBehaviour
{
SQLiteDatabase sqlDB;

void Awake() 
{
    string dbPath = System.IO.Path.Combine (Application.persistentDataPath, "game.db");
    var dbTemplatePath = System.IO.Path.Combine(Application.streamingAssetsPath, "default.db");

    if (!System.IO.File.Exists(dbPath)) {
        // game database does not exists, copy default db as template
        if (Application.platform == RuntimePlatform.Android)
        {
            // Must use WWW for streaming asset
            WWW reader = new WWW(dbTemplatePath);
            while ( !reader.isDone) {}
            System.IO.File.WriteAllBytes(dbPath, reader.bytes);
        } else {
            System.IO.File.Copy(dbTemplatePath, dbPath, true);
        }       
    }
    sqlDB = new SqliteDatabase(dbPath);
}
}

The thing is that if i got it right, the fact that i put my scripts under the assets folder should be enough for them to work together and recognize classes in other files. Now the problem is simple i my DatabaseManager class does not recognize the SqliteDatabase class which is implemented in SqliteDatabase.cs

Have someone had this issue or did i miss something on how to reference a non MonoBehaviour script in a MonoBehaviour one ?

EDIT:

I used this tutorial : Here

Thanks !

SQLiteman help needed

Hi I need some help for a uni assignment, I need to answer 2 questions: 1) List the titles of videos that are available in more than one format together with the number of different formats available for each title.

MY query was: SELECT title FROM video WHERE video_id IN ( SELECT video_id, COUNT(format_id) AS AvaliableFormats FROM video_format GROUP BY title HAVING COUNT(format_id) > 1);

But the result says "Query Error: only a single result allowed for a SELECT that is part of an expression"- I'm not sure what this means or what I need to do to fix it?

Q 2 5) List the titles of videos that have an average cost (across formats) that is more than $2.

My Query

SELECT title FROM video WHERE video_id IN ( SELECT video_id FROM video_format
GROUP BY title HAVING AVG(cost) > 2);

But it only brings up one result (row there should be 2).

If anyone can please help I would really really really appreciate it!

Merging tables from two different databases (Python)

I have two databases Database1.db and Database2.db. The databases contain tables with matching names and matching columns (and the Primary key is the 'Date' column in both). The only difference between the two is that the entries in Database1 are from 2013 and the entries in Database2 are from 2014. I would like to merge these two databases so that all the 2013 and 2014 data ends up in one table in a third database (let's call it Database3.db).

To be clear, here is what the databases I'm working with currently contain and what I want the third resulting database to contain:

Database1.db:

Table Name: GERMANY_BERLIN

Date            Morning    Day     Evening    Night
01.01.2013      0.5        0.2      0.2       0.1
02.01.2013      0.4        0.3      0.1       0.2
...

Database2.db:

Table Name: GERMANY_BERLIN

Date            Morning    Day     Evening    Night
01.01.2014      0.6        0.2      0.1       0.1
02.01.2014      0.5        0.2      0.3       0.0
...

I would like to have create a resulting Database3 with the following data:

Database2.db:

Table Name: GERMANY_BERLIN

Date            Morning    Day     Evening    Night
01.01.2013      0.5        0.2      0.2       0.1
02.01.2013      0.4        0.3      0.1       0.2
01.01.2014      0.6        0.2      0.1       0.1
02.01.2014      0.5        0.2      0.3       0.0
...

I haven't been able to find anything directly helpful on this online yet (perhaps JOINS could be used somehow? bhttp://www.tutorialspoint.com/sqlite/sqlite_using_joins.htm) so any suggestions would be greatly appreciated!

PS. SQLite has been used to create the existing databases and is the database-related Python library that I'm most familiar with

How to save image in database using sqlite database

MAIN

please help me am try to store image as blob in sqlite but i got like string format i don't know what i am doing wrong..

public byte[] photo=null;
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    Button insert=(Button)findViewById(R.id.insert);

    final SQLiteDatabase db;
    db=openOrCreateDatabase("demo", Context.MODE_PRIVATE, null);
    db.execSQL("create table if not exists demo1(Dp BLOB );");
    insert.setOnClickListener(new View.OnClickListener() {

        @Override
        public void onClick(View arg0) {
            // TODO Auto-generated method stub
            Bitmap b=BitmapFactory.decodeResource(getResources(), R.drawable.ic_launcher);
            //ContentValues cv=new ContentValues();
            ByteArrayOutputStream bos=new ByteArrayOutputStream();
            b.compress(Bitmap.CompressFormat.PNG, 90, bos);
            photo=bos.toByteArray();
            db.execSQL("INSERT INTO demo1(Dp)VALUES('"+photo+"')");
        }
    });
}

Column vs Table Primary Key constraint in a SQLite table

I'm medling with SQLite databases, and from the documentation :

Each table in SQLite may have at most one PRIMARY KEY. If the keywords PRIMARY KEY are added to a column definition, then the primary key for the table consists of that single column. Or, if a PRIMARY KEY clause is specified as a table-constraint, then the primary key of the table consists of the list of columns specified as part of the PRIMARY KEY clause.

My question is when there's a single column to be used as a PRIMARY KEY, is there a fundamental difference between the two following commands ?

CREATE TABLE t (x INTEGER, y TEXT ,  PRIMARY KEY (x));

CREATE TABLE t (x INTEGER PRIMARY KEY, t TEXT);

Insert the newset value in a table and drop the old ones

I have a method which saves somes records for a machine in a table. However, I may insert by accident multiple times the records for the same machine in a day, so I want for this day to save the newest insert and drop the old ones. I thought one solution:

String sq = "SELECT date, idMachine "
          + "FROM MACHINES_RECORDS WHERE date = ? AND idMachine = ?";

 try {       
        Class.forName(typeDB);
        c = DriverManager.getConnection(path);            
        stm = c.prepareStatement(sq);  
        ResultSet rs = stm.executeQuery();

        if (rs.next()) {                
           do {
                 //call an another method to drop this value
               } while(rs.next());
         }else {
               //call an another method to insert this value
         }       
        }catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            if (stm != null)
                stm.close();
            if (c != null)
        c.close();
  }

Is there any better/smartest solution from this, so I can make all theses actions in the same method?

Android eclipse SQLite syntax error

Good day friends. I am developing simple search function on my android project.I am using the 'like %selectionArgs%' in my query but I always get error like this (1) near "s": syntax error.

This is my code for that function:

public Cursor getWord(String[] selectionArgs){
String selection = FIELD_NAME + " like ? ";
if(selectionArgs!=null){
        selectionArgs[0] = "%" + selectionArgs[0] + "%";        
    }       
SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
queryBuilder.setProjectionMap(mAliasMap);
queryBuilder.setTables(TABLE_NAME);
    Cursor c = queryBuilder.query(mDictionaryDBOpenHelper.getReadableDatabase(), 
    new String[] { FIELD_ID, 
    SearchManager.SUGGEST_COLUMN_TEXT_1 , 
    SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID } ,
    selection, 
    selectionArgs,
    null,
    null,
    FIELD_NAME + "asc",
    "10"
    );

    return c;
}

iOS 9 Command failed due to signal: Segmentation fault: 11

I recently upgraded to Xcode 7. And my code that was working fine on 6.3 and iOS 8.4 now will not compile with a segmentation fault.

I am using Stephencelis library of SQLite. It seems to be causing the problem.

I would appreciate any help, any ideas. Below is the last part of the error msg:

  1. While type-checking getter for columnNames at /Users/luben/Downloads/SQLite.swift-master/SQLite/Query.swift:740:22
  2. While type-checking declaration 0x7fbab3ee2d68 at /Users/luben/Downloads/SQLite.swift-master/SQLite/Query.swift:740:51
  3. While type-checking expression at [/Users/luben/Downloads/SQLite.swift-master/SQLite/Query.swift:740:51 - line:774:7] RangeText="{ var (columnNames, idx) = (String: Int, 0) column: for each in self.query.columns ?? [Expression(literal: "*")] { let pair = each.expression.SQL.characters.split { $0 == "." }.map { String($0) } let (tableName, column) = (pair.count > 1 ? pair.first : nil, pair.last!)

        func expandGlob(namespace: Bool) -> Query -> Void {
            return { table in
                var query = Query(table.database, table.tableName.unaliased)
                if let columns = table.columns { query.columns = columns  }
                var names = query.selectStatement.columnNames.map { quote(identifier: $0) }
                if namespace { names = names.map { "\(table.tableName.SQL).\($0)" } }
                for name in names { columnNames[name] = idx++ }
            }
        }
    
        if column == "*" {
            let tables = [self.query.select(*)] + self.query.joins.map { $0.table }
            if let tableName = tableName {
                for table in tables {
                    if table.tableName.SQL == tableName {
                        expandGlob(true)(table)
                        continue column
                    }
                }
                assertionFailure("no such table: \(tableName)")
            }
            tables.map(expandGlob(self.query.joins.count > 0))
            continue
        }
    
        columnNames[each.expression.SQL] = idx++
    }
    return columnNames
    
    

    }()"

How to get exact SQL statement sent to database

I want to print out the exact SQL statement sent to the database for logging purposes. I have looked at sqlite3_sql but it doesn't actually print out the SQL statement, just the original SQL I use as input to sqlite3_prepare_v2.

Here is my function to add a value to a row which I want to also print the sql statement.

int add_value(sqlite3* db, sqlite3_stmt * stmt, const char* oid, const char* value)
{
    char sql[256] = {0};
    sprintf(sql, "UPDATE table1 SET Value=? WHERE Field1=\"%s\"", oid);
    printf("%s\n", sql);

    int ret = ::sqlite3_prepare_v2(
        db,
        sql,
        strlen(sql),
        &stmt,
        0 );

    /* How do I print out the sql statement to be sent to the database? */

    ret = ::sqlite3_bind_text( stmt, 1, value, strlen(value), 0 );
    ret = sqlite3_step(stmt);

    return ret;
}

How export / import database on mobile phone

I'm working on an android application. I would like to know how i can export / import a sqlite database between the mobile and the computer.

It is possible ?

If someone lost his phone, i want that he can recover the data in the phone, thank's to the copy which is present on the computer.

If you are an another idea which can help me, tell me :)

Thank you !

how to store data from sqlite to google excel sheet

Hello guys I am new in android basically I made a 3 columns in sqlite and i am storing user input in sqlite and I want when device get wifi it will upload all data to google excel sheet accordingly with column on specific user account.

I am looking for reply.

Thanks.

SQLITE_ERROR: Connection is closed when connecting from Spark via JDBC to SQLite database

I am using Apache Spark 1.5 and trying to connect to a local SQLite database named clinton.db. Creating a data frame from a table of the database works fine but when I do some operations on the created object, I get the error below which says "SQL error or missing database (Connection is closed)". Funny thing is that I get the result of the operation nevertheless. Any idea what I can do to solve the problem, i.e., avoid the error?

Start command for spark-shell:

../spark/bin/spark-shell --master local[8] --jars ../libraries/sqlite-jdbc-3.8.11.1.jar --classpath ../libraries/sqlite-jdbc-3.8.11.1.jar

Reading from the database:

val emails = sqlContext.read.format("jdbc").options(Map("url" -> "jdbc:sqlite:../data/clinton.sqlite", "dbtable" -> "Emails")).load()

Simple count (fails):

emails.count

Error:

15/09/30 09:06:39 WARN JDBCRDD: Exception closing statement java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (Connection is closed) at org.sqlite.core.DB.newSQLException(DB.java:890) at org.sqlite.core.CoreStatement.internalClose(CoreStatement.java:109) at org.sqlite.jdbc3.JDBC3Statement.close(JDBC3Statement.java:35) at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$$anon$1.org$apache$spark$sql$execution$datasources$jdbc$JDBCRDD$$anon$$close(JDBCRDD.scala:454) at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$$anon$1$$anonfun$8.apply(JDBCRDD.scala:358) at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$$anon$1$$anonfun$8.apply(JDBCRDD.scala:358) at org.apache.spark.TaskContextImpl$$anon$1.onTaskCompletion(TaskContextImpl.scala:60) at org.apache.spark.TaskContextImpl$$anonfun$markTaskCompleted$1.apply(TaskContextImpl.scala:79) at org.apache.spark.TaskContextImpl$$anonfun$markTaskCompleted$1.apply(TaskContextImpl.scala:77) at scala.collection.mutable.ResizableArray$class.foreach(ResizableArray.scala:59) at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:47) at org.apache.spark.TaskContextImpl.markTaskCompleted(TaskContextImpl.scala:77) at org.apache.spark.scheduler.Task.run(Task.scala:90) at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:214) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) res1: Long = 7945

mardi 29 septembre 2015

$DBH = new PDO("sqlite:my/database/path/database.db");

I am hoping someone out there can give me an example of the code explaining the parameters and also where to find the location of the "path" parameter in

$DBH = new PDO("sqlite:my/database/path/database.db");

iOS how to handle sqlite DB schema change in the next version of app [duplicate]

This question already has an answer here:

I have an app that has sqlite db and the app is installed in many devices.

Now I have a need to change the sqlite db schema i.e I am going to add some tables and change columns in existing table.

The apps that are already running has their DB in documents directory now how can I go about applying the new db changes.

Is there any call back method available that we can get after an app is updated..

so that I can handle the situation easily..

any help is appreciated

thanks

How to retrieved image from sqlite to list view

My code here EmployeeAppActivity

My code working well in list view without image. i need for employee photo in list view, i getting error for when call image from database to list view please help me.

// Primitive Variables
String selected_ID = "";

// Widget GUI Declare
EditText txtEname, txtDesig, txtSalary;
Button btnAddEmployee, btnUpdate, btnDelete;
ListView lvEmployees;

// DB Objects
DBHelper helper;
SQLiteDatabase db;

// Adapter Object
SimpleCursorAdapter adapter;

/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    // Init DB Objects
    helper = new DBHelper(this);

    // Widget GUI Init
    txtEname = (EditText) findViewById(R.id.txtEname);
    txtDesig = (EditText) findViewById(R.id.txtDesig);
    txtSalary = (EditText) findViewById(R.id.txtSalary);
    lvEmployees = (ListView) findViewById(R.id.lvEmployees);

    btnAddEmployee = (Button) findViewById(R.id.btnAdd);
    btnUpdate = (Button) findViewById(R.id.btnUpdate);
    btnDelete = (Button) findViewById(R.id.btnDelete);

    // Attached Listener
    btnAddEmployee.setOnClickListener(this);
    btnUpdate.setOnClickListener(this);
    btnDelete.setOnClickListener(this);
    lvEmployees.setOnItemClickListener(new OnItemClickListener() {

        @Override
        public void onItemClick(AdapterView<?> adapter, View v,
                int position, long id) {

            String name, desig, salary;
            byte[] empphoto;

            // Display Selected Row of Listview into EditText widget

            Cursor row = (Cursor) adapter.getItemAtPosition(position);
            selected_ID = row.getString(0);
            name = row.getString(1);
            desig = row.getString(2);
            salary = row.getString(3);
            empphoto=row.getBlob(4);

            txtEname.setText(name);
            txtDesig.setText(desig);
            txtSalary.setText(salary);
        }
    });

    // Fetch Data from database
    fetchData();
}

@Override
public void onClick(View v) {

    // Perform CRUD Operation

    if (v == btnAddEmployee) {

        // Add Record with help of ContentValues and DBHelper class object

        Bitmap b=BitmapFactory.decodeResource(getResources(), R.drawable.icon);
        //Bitmap b=BitmapFactory.decodeFile(imagepath);
        //ContentValues cv=new ContentValues();
        ByteArrayOutputStream bos=new ByteArrayOutputStream();
        b.compress(Bitmap.CompressFormat.JPEG, 90, bos);
        byte[] photo=bos.toByteArray();
        ContentValues values = new ContentValues();
        values.put(DBHelper.C_ENAME, txtEname.getText().toString());
        values.put(DBHelper.C_DESIGNATION, txtDesig.getText().toString());
        values.put(DBHelper.C_SALARY, txtSalary.getText().toString());
        values.put(DBHelper.C_Dp, photo);

        // Call insert method of SQLiteDatabase Class and close after
        // performing task
        db = helper.getWritableDatabase();
        db.insert(DBHelper.TABLE, null, values);
        db.close();

        clearFields();
        Toast.makeText(this, "Employee Added Successfully",
                Toast.LENGTH_LONG).show();

        // Fetch Data from database and display into listview
        fetchData();

    }
    if (v == btnUpdate) {

        // Update Record with help of ContentValues and DBHelper class
        // object

        ContentValues values = new ContentValues();
        values.put(DBHelper.C_ENAME, txtEname.getText().toString());
        values.put(DBHelper.C_DESIGNATION, txtDesig.getText().toString());
        values.put(DBHelper.C_SALARY, txtSalary.getText().toString());

        // Call update method of SQLiteDatabase Class and close after
        // performing task
        db = helper.getWritableDatabase();
        db.update(DBHelper.TABLE, values, DBHelper.C_ID + "=?",
                new String[] { selected_ID });
        db.close();

        // Fetch Data from database and display into listview
        fetchData();
        Toast.makeText(this, "Record Updated Successfully",
                Toast.LENGTH_LONG).show();
        clearFields();

    }
    if (v == btnDelete) {

        // Call delete method of SQLiteDatabase Class to delete record and
        // close after performing task
        db = helper.getWritableDatabase();
        db.delete(DBHelper.TABLE, DBHelper.C_ID + "=?",
                new String[] { selected_ID });
        db.close();

        // Fetch Data from database and display into listview
        fetchData();
        Toast.makeText(this, "Record Deleted Successfully",
                Toast.LENGTH_LONG).show();
        clearFields();

    }

}

// Clear Fields
private void clearFields() {
    txtEname.setText("");
    txtDesig.setText("");
    txtSalary.setText("");
}

// Fetch Fresh data from database and display into listview
private void fetchData() {

    db = helper.getReadableDatabase();
    Cursor c = db.query(DBHelper.TABLE, null, null, null, null, null, null);
    adapter = new SimpleCursorAdapter(
            this,
            R.layout.row,
            c,
            new String[] { DBHelper.C_ENAME, DBHelper.C_SALARY, DBHelper.C_DESIGNATION ,DBHelper.C_Dp},

            new int[] { R.id.lblEname, R.id.lblSalary, R.id.lblDesignation,R.id.empdp });
    lvEmployees.setAdapter(adapter);
}

DBHelper class here

static final String DATABASE = "empapp.db";
static final int VERSION = 1;
static final String TABLE = "emp";

static final String C_ID = "_id";
static final String C_ENAME = "ename";
static final String C_DESIGNATION = "designation";
static final String C_SALARY = "salary";
static final String C_Dp = "photo";

// Override constructor
public DBHelper(Context context) {
    super(context, DATABASE, null, VERSION);

}

// Override onCreate method
@Override
public void onCreate(SQLiteDatabase db) {

    // Create Employee table with following fields
    // _ID, ENAME, DESIGNATION and SALARY
    db.execSQL("CREATE TABLE " + TABLE + " ( " + C_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT, " + C_ENAME + " text, "
            + C_DESIGNATION + " text, " + C_SALARY + " text, " + C_Dp+ " BLOB )");
}

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

    // Drop old version table
    db.execSQL("Drop table " + TABLE);

    // Create New Version table
    onCreate(db);
}

Logcat error

09-30 03:45:38.139: E/AndroidRuntime(30628): FATAL EXCEPTION: main

09-30 03:45:38.139: E/AndroidRuntime(30628): android.database.sqlite.SQLiteException: unknown error (code 0): Unable to convert BLOB to string 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.database.CursorWindow.nativeGetString(Native Method) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.database.CursorWindow.getString(CursorWindow.java:434) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.database.AbstractWindowedCursor.getString(AbstractWindowedCursor.java:51) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.widget.SimpleCursorAdapter.bindView(SimpleCursorAdapter.java:150) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.widget.CursorAdapter.getView(CursorAdapter.java:250) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.widget.AbsListView.obtainView(AbsListView.java:2159) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.widget.ListView.makeAndAddView(ListView.java:1831) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.widget.ListView.fillDown(ListView.java:674) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.widget.ListView.fillFromTop(ListView.java:735) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.widget.ListView.layoutChildren(ListView.java:1652) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.widget.AbsListView.onLayout(AbsListView.java:1994) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.view.View.layout(View.java:14003) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.view.ViewGroup.layout(ViewGroup.java:4375) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.widget.LinearLayout.setChildFrame(LinearLayout.java:1663) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.widget.LinearLayout.layoutVertical(LinearLayout.java:1521) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.widget.LinearLayout.onLayout(LinearLayout.java:1434) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.view.View.layout(View.java:14003) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.view.ViewGroup.layout(ViewGroup.java:4375) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.widget.FrameLayout.onLayout(FrameLayout.java:448) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.view.View.layout(View.java:14003) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.view.ViewGroup.layout(ViewGroup.java:4375) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.widget.LinearLayout.setChildFrame(LinearLayout.java:1663) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.widget.LinearLayout.layoutVertical(LinearLayout.java:1521) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.widget.LinearLayout.onLayout(LinearLayout.java:1434) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.view.View.layout(View.java:14003) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.view.ViewGroup.layout(ViewGroup.java:4375) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.widget.FrameLayout.onLayout(FrameLayout.java:448) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.view.View.layout(View.java:14003) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.view.ViewGroup.layout(ViewGroup.java:4375) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.view.ViewRootImpl.performLayout(ViewRootImpl.java:1892) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.view.ViewRootImpl.performTraversals(ViewRootImpl.java:1711) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.view.ViewRootImpl.doTraversal(ViewRootImpl.java:989) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.view.ViewRootImpl$TraversalRunnable.run(ViewRootImpl.java:4351) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.view.Choreographer$CallbackRecord.run(Choreographer.java:749) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.view.Choreographer.doCallbacks(Choreographer.java:562) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.view.Choreographer.doFrame(Choreographer.java:532) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.view.Choreographer$FrameDisplayEventReceiver.run(Choreographer.java:735) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.os.Handler.handleCallback(Handler.java:725) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.os.Handler.dispatchMessage(Handler.java:92) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.os.Looper.loop(Looper.java:137) 09-30 03:45:38.139: E/AndroidRuntime(30628): at android.app.ActivityThread.main(ActivityThread.java:5039) 09-30 03:45:38.139: E/AndroidRuntime(30628): at java.lang.reflect.Method.invokeNative(Native Method) 09-30 03:45:38.139: E/AndroidRuntime(30628): at java.lang.reflect.Method.invoke(Method.java:511) 09-30 03:45:38.139: E/AndroidRuntime(30628): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793) 09-30 03:45:38.139: E/AndroidRuntime(30628): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560) 09-30 03:45:38.139: E/AndroidRuntime(30628): at dalvik.system.NativeStart.main(Native Method)

What's the difference in sql for table name aliasing with and without AS? [duplicate]

This question already has an answer here:

Say I have a table states with each state's abbreviation. The following two queries give the same result:

SELECT name
FROM states s
WHERE s.abbrev='CA';


SELECT name
FROM states AS s
WHERE s.abbrev='CA';

Both give California in the output.

It seems that the query works just fine with or without AS. So my question is, what difference does the keyword AS make, and what are the cases when we must or must not use AS?

HTTP Post Upload Multiple Files from iOS to PHP

In my iOS app, I'm building an NSData to use as the body for an NSMutableURLRequest so that I can upload multiple files in one HTTP POST.

The contents of my post body look like this (with the file data removed and just replaced with the byte count):

multipart/form-data; charset=utf-8; boundary=0xKhTmLbOuNdArY
--0xKhTmLbOuNdArY
Content-Disposition: form-data; name="email"
myemailaddress@gmail.com
--0xKhTmLbOuNdArY
Content-Disposition: form-data; name="sqlite"; filename="backup.MyApp.v1.1.3-to-v1.1.3.1443578420.sqlite"
Content-Type: application/octet-stream

// ... data length: 880640

--0xKhTmLbOuNdArY--
Content-Disposition: form-data; name="sqliteshm"; filename="backup.MyApp.v1.1.3-to-v1.1.3.1443578420.sqlite-shm"
Content-Type: application/octet-stream

// ... data length: 32768

--0xKhTmLbOuNdArY--
Content-Disposition: form-data; name="sqlitewal"; filename="backup.MyApp.v1.1.3-to-v1.1.3.1443578420.sqlite-wal"
Content-Type: application/octet-stream

// ... data length: 3901672

--0xKhTmLbOuNdArY--

However, on the PHP side when I receive this post, I'm only seeing the first of the three files. If I put the one named "sqlite" first, then on the PHP side, I only see the "sqlite" file. If I put the one named "sqliteshm" first, then I only see the "sqliteshm" file in the $_FILES array.

array (
'sqliteshm' => 
    array (
      'name' => 'backup.MyApp.v1.1.3-to-v1.1.3.1443578420.sqlite-shm',
      'type' => 'application/octet-stream',
      'tmp_name' => '/private/var/tmp/phpk1wyWb',
      'error' => 0,
      'size' => 32768,
    ),
)

The file size matches up, regardless of which one I put first, but only the first file ever shows up on the PHP side.

Do I need to do something special in order to receive multiple files on the PHP side?

Or am I sending the multiple files incorrect from iOS?

Save Items in ListView in to the Database

I'm developing an android application using SQLite Database. I have a itemList which user can enter food items into the List. I want to save the items of each listview seperately (one listview data in a line). And i'm using an Arraylist to store data which user enters into the System. Can someone help me to do this. Below i've posted my XML page and Java Class. Thanks in advance!! :)

manualListScr.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://ift.tt/nIICcg"
android:orientation="vertical" android:layout_width="match_parent"
android:layout_height="match_parent">

<EditText
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:id="@+id/itmName"
    android:hint="Enter Item"
    android:textSize="24dp"
    android:layout_alignParentTop="true"
    android:layout_centerHorizontal="true"/>

<Button
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:id="@+id/addItems"
    android:text="Add Item"
    android:layout_below="@+id/itmName"
    android:layout_alignParentLeft="true"/>

<TextView
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:text="Manual Item List"
    android:id="@+id/itmHeader"
    android:layout_below="@+id/addItems"
    android:background="#5e5e5e"
    android:textColor="#FFFFFF"
    android:textSize="14dp"/>

<ListView
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:id="@+id/itmList"
    android:layout_below="@+id/itmHeader"
    android:layout_centerHorizontal="true">

</ListView>


manualList.java

public class manualInput extends Activity implements View.OnClickListener{
private Button addButton;
private EditText editText;
private ListView listView;
ArrayList<String> itmList = new ArrayList<String>();
ArrayAdapter<String> adapter;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.manualinputscr);
    addButton = (Button) findViewById(R.id.addItems);
    addButton.setOnClickListener(this);
    editText = (EditText) findViewById(R.id.itmName);
    adapter = new ArrayAdapter<String>(this, android.R.layout.simple_expandable_list_item_1,itmList);
    listView = (ListView) findViewById(R.id.itmList);
    listView.setAdapter(adapter);
    registerForContextMenu(listView);
}

@Override
public void onCreateContextMenu(ContextMenu menu, View v, ContextMenu.ContextMenuInfo menuInfo) {
    super.onCreateContextMenu(menu, v, menuInfo);
    MenuInflater inflater = getMenuInflater();
    inflater.inflate(R.menu.context_menu, menu);
}

@Override
public boolean onContextItemSelected(MenuItem item) {
    //return super.onContextItemSelected(item);
    AdapterView.AdapterContextMenuInfo info = (AdapterView.AdapterContextMenuInfo)item.getMenuInfo();
    switch (item.getItemId()){
        case R.id.editItem:
            //
            return true;
        case R.id.deleteItem:
            adapter.remove(adapter.getItem(info.position));
            Toast.makeText(this, "Item Deleted", Toast.LENGTH_SHORT).show();
            return true;
        default:
            return super.onContextItemSelected(item);
    }
}

public void onClick(View view){
    String input = editText.getText().toString();
    if(input.length() > 0){
        adapter.add(input);
    }
}
}

How get row counts of a table sqlite in android

I tried this code but it will always go error : java.lang.NullPointerException

I dont know why. I have records in my table Goals.

This is my code.

public long getGoalsCount() {
        SQLiteDatabase db=dbhandler.getWritableDatabase();
        long numRows = DatabaseUtils.queryNumEntries(db, MyDBHandler.TABLE_GOALS);
        return numRows;
    }

When i called out the function

public void displayAllGoals(){
        long goal_counts = dbhandler.getGoalsCount(); // The error points her that it means it doesnt return any value. And that it's null
        MessageTo.message(ViewGoalsActivity.this,Long.toString(goal_counts)); //To display the value

    }

OnCreate Function

protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_view_goals);

        displayAllGoals();

    }

I can pinpoint what's wrong in my code. I tried other sources but its still the same error. I'm a newbie in Android.

java.lang.RuntimeException: Unable to start activity ComponentInfo{com.citu.organizer/com.citu.organizer.ViewGoalsActivity}: java.lang.NullPointerException

at com.citu.organizer.ViewGoalsActivity.displayAllGoals(ViewGoalsActivity.java:57)
            at com.citu.organizer.ViewGoalsActivity.onCreate(ViewGoalsActivity.java:38)

unable to generate random words from SQLite database in android

Background: Every time the user starts a new game with another player, he is given 4 words that are taken from the SQLite database, which remain in the current game activity until the game is finished. If the user has multiple games, each of those games will have a different set of words.

problem: I am able to generate a random set of words, but the problem is that my app generates the set of words only once, and this set is used in all the games of the current user, and any other user I log in as.

Here is my code:

  public class WordsListDatabaseHelper extends SQLiteOpenHelper {

protected static Set<String> mSelectedWords;
private static final String DB_NAME = "GAME_TABLE";
private static final int DB_VERSION = 1;
protected static LinkedList<String> mCopy;
public static int gameNumber = 0;


WordsListDatabaseHelper(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE GAME ("
                    + "_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + "SELECTED_WORDS TEXT, "
                    + "GAME_NUMBER INTEGER);"
    );



    Collection<String> wordList = new LinkedList<String>();
    mSelectedWords = new LinkedHashSet<String>();

    wordList.add("ant");
    wordList.add("almond");
      //lots more words

    mCopy = new LinkedList<String>(wordList);

    Gson gson = new Gson();
    String wordsContainer = gson.toJson(mCopy);
    insertWordList(db, wordsContainer, gameNumber);

}

private static void insertWordList(SQLiteDatabase db, String wordsContainer, int gameNumber) {
    ContentValues wordValues = new ContentValues();

    Gson gson = new Gson();
    Collections.shuffle(mCopy);
    mSelectedWords.addAll(mCopy.subList(1,7));
    wordsContainer = gson.toJson(mSelectedWords);

    wordValues.put("SELECTED_WORDS", wordsContainer);
    wordValues.put("GAME_NUMBER", gameNumber);
    db.insert("GAME", null, wordValues);
}

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

}

Here is the relevant code from FindingOpponentsActivity, which randomly matches the user with an opponent. When the match is created, the user is taken to StartGameActivity.

  Intent intentRand = new Intent(FindingOpponentActivity.this, StartGameActivity. .class);
                    int gameNum = WordsListDatabaseHelper.gameNumber;
                    intentRand.putExtra(
                            StartGameActivity.EXTRA_RAND_OPPONENT,
                            mOpponent.getObjectId());

                    intentRand.putExtra(
                            StartGameActivity.EXTRA_GAME_NUMBER,
                            gameNum);

                    sendPushNotification();
                    startActivity(intentRand);
                }

And now, this is the code in the onCreate() method of StartGameActivity:

  Intent intent = getIntent();
    mGameNum = intent.getIntExtra(EXTRA_GAME_NUMBER, 0);


    //create a cursor

    try {
        SQLiteOpenHelper wordsListDatabaseHelper = new WordsListDatabaseHelper(this);
        SQLiteDatabase db = wordsListDatabaseHelper.getReadableDatabase();
        Cursor cursor = db.query("GAME",
                new String[] {"SELECTED_WORDS"},
                "GAME_NUMBER =? ",
                new String[]{Integer.toString(mGameNum)},
                null, null, null);

        //move to the first record in the Cursor

        if (cursor.moveToFirst()) {
            //get wordList
            String savedWordList  = cursor.getString(0);

            Gson gson = new Gson();
            Type type = new TypeToken<ArrayList<String>>() {}.getType();
            ArrayList<String> finalWordList = gson.fromJson(savedWordList, type);
            mCopy = new LinkedList<String>();
            mCopy.addAll(finalWordList);


            word1 = (TextView) findViewById(R.id.word1);
            word1.setText(mCopy.get(1));

            word2 = (TextView) findViewById(R.id.word2);
            word2.setText(mCopy.get(2));

What I want is that every time a new game begins, the table has a new entry: a new GAME_NUMBER and wordsContainer (which contains the random words). Thus, each game of the user has a unique identifier (GAME_NUMBER). I think what I am doing wrong is not creating a new entry every time a new game begins, and this is what's causing the same list to appear, but I am totally lost as to how to implement the solution.

Logical expressions in full text search in SQLite executed with PHP PDO

The logical expressions does not work as expected while full text search in SQLite database. Tested on XAMPP-PORTABLE 1.8.3 [PHP: 5.5.6].

Example data

There is table clubs with columns

  • name for the football club
  • city for the city
  • nations for the abbreviations of players' nationalities

What is important, the cities with be used for OR statements (example, London or Liverpool). The nationalities will be used for AND statements.

The PHP script that creates such table

<?php
$db = new  PDO('sqlite:mydb.sqlite3');
$sql = "CREATE VIRTUAL TABLE clubs USING fts4 " .
       "(name, city, nations)";
$db->exec($sql);

$sql = "DELETE FROM clubs";
$db->exec($sql);

$teams = array(
  array(
    "name" => "FC Chelsea", 
    "city" => "London",
    "nations" => "ENG BEL BIH FRA SEN ESP GHA SRB NIG COL" 
  ),
  array(
    "name" => "FC Arsenal", 
    "city" => "London",
    "nations" => "ENG COL CZE FRA GER BRA ESP WAL CHI CRC" 
  ),
  array(
    "name" => "Tottenham Hotspur", 
    "city" => "London",
    "nations" => "ENG FRA NED BEL ARG AUT WAL ALG DEN CAM KOR" 
  ),
  array(
    "name" => "West Ham United", 
    "city" => "London",
    "nations" => "ENG ESP IRL SUI NZL WAL ITA CAN SCO CAM SEN FRA ARG ECU" 
  ),
  array(
    "name" => "Manchester City", 
    "city" => "Manchester",
    "nations" => "ENG ARG BEL FRA SRB BRA CIV ESP NIG" 
  ),
  array(
    "name" => "Manchester United", 
    "city" => "Manchester",
    "nations" => "ENG ESP ARG NED ITA ECU FRA BEL GER BRA" 
  ),
  array(
    "name" => "FC Everton", 
    "city" => "Liverpool",
    "nations" => "ENG ESP USA ARG CRC IRL BIH RSA BEL SCO URU" 
  ),
  array(
    "name" => "FC Liverpool", 
    "city" => "Liverpool",
    "nations" => "ENG BRA HUN FRA CRO ESP SVK CIV GER WAL POR BEL" 
  ),
);

for($i=0; $i<count($teams); $i++) {
  $sql = "INSERT INTO clubs VALUES (?, ?, ?)";
  $stmt = $db->prepare($sql);
  $stmt->execute(
    array(
      $teams[$i]["name"], $teams[$i]["city"], $teams[$i]["nations"]
    )     
  );
}

Examples of logical expressions

The following function wil be used for the display of the results

function doQuery($db, $sql, $desc) {
  $arr = array();
  $q = $db->query($sql);
  $rows = $q->fetchAll(PDO::FETCH_ASSOC);
  echo "<tt>" . $sql . "</tt><br/>";
  echo "<b> " . $desc . " (" . count($rows) . "</b>): " ;
  foreach($rows as $row) {
    $arr[] = $row['name'];
  }
  $results = implode(", ", $arr);
  echo $results . "<br/>";  
}

In the following examples the results will be displayed with italic to distinguish them from the other text.

One logical expression statement

1.1. Search clubs with Spanish players

$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('nations:ESP')
SQL;
doQuery($db, $sql, "ESP");

There are found all teams except Tottenham

ESP (7): FC Chelsea, FC Arsenal, West Ham United, Manchester City, Manchester United, FC Everton, FC Liverpool

1.2. Search clubs with Argentine players

$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('nations:ARG')
SQL;
doQuery($db, $sql, "ARG");

Results are also correct.

ARG (5): Tottenham Hotspur, West Ham United, Manchester City, Manchester United, FC Everton

Two logical expressions statement

2.1. Search clubs from London OR Liverpool

$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('city:London OR city:Liverpool')
SQL;
doQuery($db, $sql, "London or Liverpool");

No problem.

London or Liverpool (6): FC Chelsea, FC Arsenal, Tottenham Hotspur, West Ham United, FC Everton, FC Liverpool

2.2. Search clubs with Spanish players AND Argentine players

$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('nations:ARG nations:ESP')
SQL;
doQuery($db, $sql, "ARG ESP");

Implicit AND operator works fine.

ARG ESP (4): West Ham United, Manchester City, Manchester United, FC Everton

but when go for explicit AND

2.3. Search clubs with Spanish players AND Argentine players (explicit)

$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('nations:ARG AND nations:ESP')
SQL;
doQuery($db, $sql, "ARG and ESP");

No club is found.

ARG and ESP (0):

The explicit AND does not seem to work.

Three logical expressions statement

Now the results are unexpected

3.1. Search clubs from London OR clubs from Liverpool with Argentine players

$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('city:London OR city:Liverpool nations:ARG')
SQL;
doQuery($db, $sql, "London or Liverpool and ARG");

We would have expected all London teams selected, but instead we get only those with Argentine players

London or Liverpool and ARG (3): Tottenham Hotspur, West Ham United, FC Everton

The same result is obtained for query

SELECT * FROM clubs WHERE clubs MATCH ('city:Liverpool OR city:London nations:ARG')

It looks like OR is executed before AND: (city:Liverpool OR city:London) AND nations:ARG. But what happens when the brackets are really used around OR-statement.

3.2. Search clubs (from London OR clubs from Liverpool) with Argentine players (brackets)

$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('(city:Liverpool OR city:London) nations:ARG')
SQL;
doQuery($db, $sql, "(London or Liverpool) and ARG");

No club is found.

(London or Liverpool) and ARG (0):

What if the sequence of logical expressions changes

3.3 Search clubs from Liverpool with Argentine players or clubs from London.

$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('city:Liverpool nations:ARG OR city:London')
SQL;
doQuery($db, $sql, "Liverpool and ARG or London");

Only team from Liverpool with Argentine players is found.

Liverpool and ARG or London (1): FC Everton

3.4. Same search with switched nations:ARG and city:Liverpool.

$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('nations:ARG city:Liverpool OR city:London')
SQL;
doQuery($db, $sql, "ARG and Liverpool or London");

The same result as in the 3.1. example.

ARG and Liverpool or London (3): Tottenham Hotspur, West Ham United, FC Everton

Question

I got no clue how logical expressions are interpreted. Can anybody explain?

SQLite not loading data upon activity starting in android

Problem: The user is matched with another user, and the game begins. The user is given some words, which are taken from the SQLite database. The problem is, when the activity starts, the area where the words should be is empty. The log does not show any errors. Here is the relevant code.

The database class where all the words are kept:

public class WordsListDatabaseHelper extends SQLiteOpenHelper {

protected static Set<String> mSelectedWords;
private static final String DB_NAME = "GAME_TABLE";
private static final int DB_VERSION = 1;
protected static LinkedList<String> mCopy;
public static int gameNumber = 0;


WordsListDatabaseHelper(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE GAME ("
                    + "_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + "SELECTED_WORDS TEXT, "
                    + "GAME_NUMBER INTEGER);"
    );



    Collection<String> wordList = new LinkedList<String>();
    mSelectedWords = new LinkedHashSet<String>();

    wordList.add("ant");
    wordList.add("almond");
      //lots more words

    mCopy = new LinkedList<String>(wordList);

    Gson gson = new Gson();
    String wordsContainer = gson.toJson(mCopy);
    insertWordList(db, wordsContainer, gameNumber);

}

private static void insertWordList(SQLiteDatabase db, String wordsContainer, int gameNumber) {
    ContentValues wordValues = new ContentValues();

    Gson gson = new Gson();
    Collections.shuffle(mCopy);
    mSelectedWords.addAll(mCopy.subList(1,7));
    wordsContainer = gson.toJson(mSelectedWords);

    wordValues.put("SELECTED_WORDS", wordsContainer);
    wordValues.put("GAME_NUMBER", gameNumber);
    db.insert("GAME", null, wordValues);
}

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

}

Here is the relevant code from FindingOpponentsActivity, which randomly matches the user with an opponent. When the match is created, the user is taken to StartGameActivity.

  Intent intentRand = new Intent(FindingOpponentActivity.this, StartGameActivity. .class);
                    int gameNum = WordsListDatabaseHelper.gameNumber;
                    intentRand.putExtra(
                            StartGameActivity.EXTRA_RAND_OPPONENT,
                            mOpponent.getObjectId());

                    intentRand.putExtra(
                            StartGameActivity.EXTRA_GAME_NUMBER,
                            gameNum);

                    sendPushNotification();
                    startActivity(intentRand);
                }

And now, this is the code in the onCreate() method of StartGameActivity:

  Intent intent = getIntent();
    mGameNum = intent.getIntExtra(EXTRA_GAME_NUMBER, 0);


    //create a cursor

    try {
        SQLiteOpenHelper wordsListDatabaseHelper = new WordsListDatabaseHelper(this);
        SQLiteDatabase db = wordsListDatabaseHelper.getReadableDatabase();
        Cursor cursor = db.query("GAME",
                new String[] {"SELECTED_WORDS"},
                "GAME_NUMBER =? ",
                new String[]{Integer.toString(mGameNum)},
                null, null, null);

        //move to the first record in the Cursor

        if (cursor.moveToFirst()) {
            //get wordList
            String savedWordList  = cursor.getString(0);

            Gson gson = new Gson();
            Type type = new TypeToken<ArrayList<String>>() {}.getType();
            ArrayList<String> finalWordList = gson.fromJson(savedWordList, type);
            mCopy = new LinkedList<String>();
            mCopy.addAll(finalWordList);


            word1 = (TextView) findViewById(R.id.word1);
            word1.setText(mCopy.get(1));

            word2 = (TextView) findViewById(R.id.word2);
            word2.setText(mCopy.get(2));

Please let me know if there is any more code that I need to post.

EDIT I forgot to mention that I was getting that the log was registering this error message:

 09-29 13:48:42.572  13689-13720/? E/SQLiteLog﹕ (1) no such table: mmsconfig

I checked over and over again for the problem, but I am unable to find where I am going wrong.

Android dev: Sync database

This is my first time writing an Android app that utilizes a database.

I have created a test database in the assets folder. I then have the app copy the database to the database folder: /data/data/myapplication1/databases/myDB.db

I realize now that the assets folders are read only, but how can I sync my database so I can verify, using SQLite browser, the changes I have made?

Should I just move my test DB from assets to a different location? Is there something better?

Or is there a way I can view the DB in the databases folder? (which I believe is protected so it is not visible to other programs)

How to do class-level queries for recursive @hybrid_property in SQLAlchemy

I have two classes in SQLAlchemy such as:

class Local(Base):
    id = Column(Unicode, primary_key=True)
    denom = Column(String)
    parent = Column(String)
    children = relationship("Local", backref=backref('parent', remote_side=[id]))

    @hybrid_property
    def anp(self):
        if self.denom.startswith(u"ANP_"):
            return self.denom[:6]
        elif self.parent:
            return self.parent.anp
        return None


class Equipament(Base):
    id = Column(Unicode, primary_key=True)
    local = relationship("Local", backref=backref('eqps', order_by=id))

    @hybrid_property
    def anp(self):
        if self.local is None:
            return None
        else:
            return self.local.anp

This works when checking for the anp property such as when equipment_instance.anp, but how do I build a proper query when trying to list all available equipments which meet a certain criteria for the property?

For example, I may try:

>>> loc = db.session.query(db.Local).first()
>>> loc.anp
u'ANP_MF'

But I can't do:

>>> loc = db.session.query(db.Local).filter(db.Local.anp==u'ANP_MF').first()
Traceback (most recent call last):
...
TypeError: Boolean value of this clause is not defined

Alternatively, I might use:

>>> db.session.query(db.Local).filter(db.Local.denom.like(u"ANP_%")).first()

But then I'd have to recurse into its children and join results manually.

So, is it possible to fix this so a simpler query might be used? I'm using SQLAlchemy 1.0.5 with SQLite.