Databases
StackOverflow2013
Postsdbo
POGet from SQLite DB email and score of a user and send the score to each user respectively
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGet from SQLite DB email and score of a user and send the score to each user respectively
    primarykey
    data
    text
    <p>I have created an application in Android that needs to send emails to all contacts saved in the database as you click on a SendMailbutton, to sending email to each contact with respective score,</p> <p>the idea was to slide the couples (email,score) of each user in the database and pass it to a specific method that sends the email</p> <p>I implemented the send method in order to receive the email address and the score associated I tested the send eMail method and works properly,</p> <p>The only problem is that I can not define a way suitable for pass all the email address and the associated scores of each user at the click of the button.</p> <p><strong>My DatabaseHelper class is the follow:</strong></p> <pre><code>import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; public class DatabaseHelper extends SQLiteOpenHelper { static final String dbName="demoDB"; static final String PlayerTable="Players"; static final String colID="PlayerID"; static final String colName="PlayerName"; static final String colEmail="Email"; static final String colScore="Score"; static final String colDept="Dept"; static final String deptTable="Dept"; static final String colDeptID="DeptID"; static final String colDeptName="DeptName"; static final String viewEmps="ViewEmps"; public DatabaseHelper(Context context) { super(context, dbName, null,33); // TODO Auto-generated constructor stub } @Override public void onCreate(SQLiteDatabase db) { // TODO Auto-generated method stub db.execSQL("CREATE TABLE "+deptTable+" ("+colDeptID+ " INTEGER PRIMARY KEY , "+ colDeptName+ " TEXT)"); db.execSQL("CREATE TABLE "+PlayerTable+" ("+colID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+ colName+" TEXT, "+colEmail+" TEXT, "+colScore+" Integer, "+colDept+" INTEGER NOT NULL ,FOREIGN KEY ("+colDept+") REFERENCES "+deptTable+" ("+colDeptID+"));"); db.execSQL("CREATE TRIGGER fk_empdept_deptid " + " BEFORE INSERT "+ " ON "+PlayerTable+ " FOR EACH ROW BEGIN"+ " SELECT CASE WHEN ((SELECT "+colDeptID+" FROM "+deptTable+" WHERE "+colDeptID+"=new."+colDept+" ) IS NULL)"+ " THEN RAISE (ABORT,'Foreign Key Violation') END;"+ " END;"); db.execSQL("CREATE VIEW "+viewEmps+ " AS SELECT "+PlayerTable+"."+colID+" AS _id,"+ " "+PlayerTable+"."+colName+","+ " "+PlayerTable+"."+colEmail+","+ " "+PlayerTable+"."+colScore+","+ " "+deptTable+"."+colDeptName+""+ " FROM "+PlayerTable+" JOIN "+deptTable+ " ON "+PlayerTable+"."+colDept+" ="+deptTable+"."+colDeptID ); //Inserts pre-defined sections InsertDepts(db); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub db.execSQL("DROP TABLE IF EXISTS "+PlayerTable); db.execSQL("DROP TABLE IF EXISTS "+deptTable); db.execSQL("DROP TRIGGER IF EXISTS dept_id_trigger"); db.execSQL("DROP TRIGGER IF EXISTS dept_id_trigger22"); db.execSQL("DROP TRIGGER IF EXISTS fk_empdept_deptid"); db.execSQL("DROP VIEW IF EXISTS "+viewEmps); onCreate(db); } void AddPlayer(Player emp) { SQLiteDatabase db= this.getWritableDatabase(); ContentValues cv=new ContentValues(); cv.put(colName, emp.getName()); cv.put(colName, emp.getEmail()); cv.put(colScore, emp.getScore()); cv.put(colDept, emp.getDept()); //cv.put(colDept,2); db.insert(PlayerTable, colName, cv); db.close(); } int getPlayerCount() { SQLiteDatabase db=this.getWritableDatabase(); Cursor cur= db.rawQuery("Select * from "+PlayerTable, null); int x= cur.getCount(); cur.close(); return x; } Cursor getAllPlayers() { SQLiteDatabase db=this.getWritableDatabase(); //Cursor cur= db.rawQuery("Select "+colID+" as _id , "+colName+", "+colEmail+","+colScore+" from "+PlayerTable, new String [] {}); Cursor cur= db.rawQuery("SELECT * FROM "+viewEmps,null); return cur; } Cursor getAllDepts() { SQLiteDatabase db=this.getReadableDatabase(); Cursor cur=db.rawQuery("SELECT "+colDeptID+" as _id, "+colDeptName+" from "+deptTable,new String [] {}); return cur; } void InsertDepts(SQLiteDatabase db) { ContentValues cv=new ContentValues(); cv.put(colDeptID, 1); cv.put(colDeptName, "Sales"); db.insert(deptTable, colDeptID, cv); cv.put(colDeptID, 2); cv.put(colDeptName, "IT"); db.insert(deptTable, colDeptID, cv); cv.put(colDeptID, 3); cv.put(colDeptName, "HR"); db.insert(deptTable, colDeptID, cv); db.insert(deptTable, colDeptID, cv); } public String GetDept(int ID) { SQLiteDatabase db=this.getReadableDatabase(); String[] params=new String[]{String.valueOf(ID)}; Cursor c=db.rawQuery("SELECT "+colDeptName+" FROM"+ deptTable+" WHERE "+colDeptID+"=?",params); c.moveToFirst(); int index= c.getColumnIndex(colDeptName); return c.getString(index); } public Cursor getEmpByDept(String Dept) { SQLiteDatabase db=this.getReadableDatabase(); String [] columns=new String[]{"_id",colName,colEmail,colScore,colDeptName}; Cursor c=db.query(viewEmps, columns, colDeptName+"=?", new String[]{Dept}, null, null, null); return c; } public int GetDeptID(String Dept) { SQLiteDatabase db=this.getReadableDatabase(); Cursor c=db.query(deptTable, new String[]{colDeptID+" as _id",colDeptName},colDeptName+"=?", new String[]{Dept}, null, null, null); //Cursor c=db.rawQuery("SELECT "+colDeptID+" as _id FROM "+deptTable+" WHERE "+colDeptName+"=?", new String []{Dept}); c.moveToFirst(); return c.getInt(c.getColumnIndex("_id")); } public int UpdateEmp(Player emp) { SQLiteDatabase db=this.getWritableDatabase(); ContentValues cv=new ContentValues(); cv.put(colName, emp.getName()); cv.put(colScore, emp.getScore()); cv.put(colDept, emp.getDept()); return db.update(PlayerTable, cv, colID+"=?", new String []{String.valueOf(emp.getID())}); } public void DeleteEmp(Player emp) { SQLiteDatabase db=this.getWritableDatabase(); db.delete(PlayerTable,colID+"=?", new String [] {String.valueOf(emp.getID())}); db.close(); } } </code></pre> <p>Help me please. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAndreaF
    1. USAndreaF
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COneed more insight of your database structure to make a correct solution
      singulars
      1. POGet from SQLite DB email and score of a user and send the score to each user respectively
      1. USSergey Benner
    2. COOk, I have updated my question adding the DatabaseHelperClass. Any suggestion??
      singulars
      1. POGet from SQLite DB email and score of a user and send the score to each user respectively
      1. USAndreaF
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload