Multiple tables in SQLite

Problems with WiFi, SQLite ,Bluetooth, WiMax, Proxies, etc...

Multiple tables in SQLite

Postby megabot » Sun Mar 15, 2009 1:09 pm

Hello,

I've just finished read the SQLite tutorial by plusminus. One thing remains unclear for me though.
Creating a database with 1 table goes pretty well now.

Right now I am trying to figure out how to create a database with multiple tables in SQLite, but I am unable to do this.
What is the best way of creating a database with multiple tables?

Thanks in advance.
megabot
Freshman
Freshman
 
Posts: 8
Joined: Sun Mar 15, 2009 12:55 pm

Top

Postby kolch9 » Fri Jun 26, 2009 4:19 pm

hi!!!have you find a solution???because i've the same problem!!i need to create and manipulate different tables...if you know something,let me know please!!thanks!!!!!!

stefano
kolch9
Junior Developer
Junior Developer
 
Posts: 11
Joined: Wed Feb 18, 2009 2:17 pm

Postby nmc » Fri Jun 26, 2009 6:08 pm

If you are able to create one table, whats the problem with creating a second/third one?
nmc
Senior Developer
Senior Developer
 
Posts: 154
Joined: Thu Nov 27, 2008 8:30 pm
Location: Germany

Postby kolch9 » Fri Jun 26, 2009 6:33 pm

fortunally i can create the tables now,but my new problem is to delete the rows of the tables..i've a table patients and one clinicinfo,where are stored informations about the patients..now if i delete a row of a patient i want to delete the informations in clinicinfo too..i use this function:

private void deleteNote() {
Bundle extras = getIntent().getExtras();
if (extras != null) {
mRowId = null;
mRowId = extras.getLong(Dbadapter.KEY_ID);
}
dbHelper.removeEntryPatient(mRowId);
dbHelper.removeEntryClinic(mRowId);
finish();
}

where
public boolean removeEntryPatient(long _id){
return db.delete(DATABASE_TABLE, KEY_ID + "=" + _id ,null)>0;
}

public boolean removeEntryClinic(long _id){
return db.delete(DATABASE_TABLE_INFO, CLINICKEY_ID + "=" + _id ,null)>0;
}

if i try to remove only the patients row,it works..if i try to delete both or only the clinicinfo's row, i have an error..i think that i need to join them,but i don't know how...i didn't find anything on line that helps me..have you got any ideas??thanks again!!
kolch9
Junior Developer
Junior Developer
 
Posts: 11
Joined: Wed Feb 18, 2009 2:17 pm

Postby cadlg » Fri Jun 26, 2009 7:00 pm

kolch9 wrote:hi!!!have you find a solution???because i've the same problem!!i need to create and manipulate different tables...if you know something,let me know please!!thanks!!!!!!


Why don't you tell us what exactly is the problem ?
How are you trying to create the tables, and what are the results ?
are you getting errors ? what errors ?


kolch9 wrote:if i try to remove only the patients row,it works..if i try to delete both or only the clinicinfo's row, i have an error..i think that i need to join them,but i don't know how


The first thing we would need to know is what is the error message you are getting.

Are you enforcing foreign key constraints ? (I think sqlite does not enforce them, but it can be achieved by triggers).
If so, you may need to delete the patient first, and then the clinic.

Provide more details, please.
cadlg
Experienced Developer
Experienced Developer
 
Posts: 84
Joined: Wed Feb 20, 2008 12:33 am
Location: Guatemala

Postby nmc » Fri Jun 26, 2009 7:01 pm

A simple

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.  
  2. db.rawQuery("delete from patient where pid="+ID, null);
  3.  
  4. db.rawQuery("delete from clinicinfo where pid="+ID, null);
  5.  
  6.  
Parsed in 0.031 seconds, using GeSHi 1.0.8.4


should do the trick;

AFAIK you cant do something like 'delete from patient,clinic where pid=2'
or 'delete from patient left join clinic on (patient.pid=clinic.pid) where pid=2'

Maybe your code doesnt work due to different IDs or columnnames?
I would check that first ;-)

According to you post your tables will look like
patient(pid, name, cid ...)
clinicinfo(cid, ...)
so when you want to delete a patient, you have to
delete pid from patient
delete cid from clinicinfo
(or repeat pid in clinicinfo)


May I ask what kind of project you are working on?
nmc
Senior Developer
Senior Developer
 
Posts: 154
Joined: Thu Nov 27, 2008 8:30 pm
Location: Germany

Top

Postby kolch9 » Sat Jun 27, 2009 11:06 am

I'm sorry if i wasn't so detailed..i'll try this time!!!:) however i'm working on my thesis..i've to do an application that permit to send data about a soldier(if it's used in a battle field) or a patient(if it's used in a hospital) to a central server..now i'm at the beginning so i've several problems,and i've to apologize if i do stupid questions and for my english too!!!!!i know that i've to improve.. :roll: however i created the tables without problems,i went to the DDMS file explorer and i saw that the tables were created and that i can save data in them..now,as i said,if y try to delete a row of patients i can do it..if i try to delete one of clinicinfo,i have an error,the tipical error "the application(process mhealth2.android)has stopped unexpetedly. please try again.". mhealth2 it's the name of my application. now to be more clear i'll post the classe interested in this problem:

CLASS PATIENT(where i have the data of the patients)
public class Patient extends Activity {


private static final int DIALOG_ERROR = 1;
private static final int DIALOG_DELETE = 2;
private static final int DIALOG_OPTIONS = 3;

private static final int SAVE_ID = Menu.FIRST;
private static final int CLEAN_ID = Menu.FIRST + 1;
private static final int BACK_ID = Menu.FIRST + 2;
private static final int DELETE_ID = Menu.FIRST + 3;
private static final int OPTIONS_ID = Menu.FIRST + 4;

private EditText mRegText;
private EditText mNameText;
private EditText mSurnameText;
private EditText mNationText;
private EditText mReligionText;
private EditText mServiceText;
private EditText mUnitText;
private EditText mRankText;
private EditText mAgeText;
private EditText mDateText;
private EditText mBloodText;
private EditText mAllergiesText;
private Spinner mSexSpinner;
private CheckBox mPendlingCheck;
private Integer priority;
private String checked;
private Dbadapter myDbHelper;
private Long mRowId;
private String flag;
Dbadapter dbHelper;

private CheckBox star1;
private CheckBox star2;
private CheckBox star3;
private CheckBox star4;
private CheckBox star5;

private void setSex(String sex){
if(sex.compareTo("M")==0)
mSexSpinner.setSelection(0);
else
mSexSpinner.setSelection(1);
}

private void setChecked(String pen){
checked=pen;
if(checked.compareTo("true")==0)
mPendlingCheck.setChecked(true);
else
mPendlingCheck.setChecked(false);
}

private void setPriority(int p) {
priority =new Integer(p);
for (int i=1; i<=5; i++) {
boolean b = false;
if (i<=priority) b = true;
if (i==1) star1.setChecked(b);
if (i==2) star2.setChecked(b);
if (i==3) star3.setChecked(b);
if (i==4) star4.setChecked(b);
if (i==5) star5.setChecked(b);
}
}


@Override
protected Dialog onCreateDialog(int id) {
switch (id) {
case DIALOG_ERROR:
return new AlertDialog.Builder(this)
.setIcon(R.drawable.alert_dialog_icon)
.setTitle(R.string.error)
.setPositiveButton(R.string.alert_dialog_ok, new DialogInterface.OnClickListener() {
public void onClick(DialogInterface dialog, int whichButton) {
}
})
.create();
case DIALOG_DELETE:
return new AlertDialog.Builder(this)
.setIcon(R.drawable.alert_dialog_icon)
.setTitle(R.string.question_delete)
.setPositiveButton(R.string.alert_dialog_ok, new DialogInterface.OnClickListener() {
public void onClick(DialogInterface dialog, int whichButton) {
deleteNote();
}
})
.setNegativeButton(R.string.alert_dialog_cancel, new DialogInterface.OnClickListener() {
public void onClick(DialogInterface dialog, int whichButton) {
}
})
.create();
case DIALOG_OPTIONS:
return new AlertDialog.Builder(this)
.setIcon(R.drawable.alert_dialog_icon)
.setTitle(R.string.error2)
.setPositiveButton(R.string.alert_dialog_ok, new DialogInterface.OnClickListener() {
public void onClick(DialogInterface dialog, int whichButton) {
}
})
.create();
}
return null;
}


/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.patient);
myDbHelper = new Dbadapter(this);
myDbHelper.open();


mRegText = (EditText) findViewById(R.id.regn);
mNameText = (EditText) findViewById(R.id.name);
mSurnameText = (EditText) findViewById(R.id.surname);
mNationText = (EditText) findViewById(R.id.nazione);
mReligionText = (EditText) findViewById(R.id.religion);
mServiceText = (EditText) findViewById(R.id.service);
mUnitText = (EditText) findViewById(R.id.unit);
mDateText = (EditText) findViewById(R.id.date);
mRankText = (EditText) findViewById(R.id.rank);
mBloodText = (EditText) findViewById(R.id.blood);
mAgeText = (EditText) findViewById(R.id.age);
mAllergiesText = (EditText) findViewById(R.id.allergies);
mPendlingCheck = (CheckBox) findViewById(R.id.pendling);
mSexSpinner = (Spinner)findViewById(R.id.spinnersex) ;


final Spinner spinner = (Spinner) findViewById(R.id.spinnersex);
ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,android.R.layout.simple_spinner_item, Gender);
adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
spinner.setAdapter(adapter);

star1 = (CheckBox) findViewById(R.id.star1);
star2 = (CheckBox) findViewById(R.id.star2);
star3 = (CheckBox) findViewById(R.id.star3);
star4 = (CheckBox) findViewById(R.id.star4);
star5 = (CheckBox) findViewById(R.id.star5);

populateFields();

View.OnClickListener onClickStars = new View.OnClickListener() {

public void onClick(View view) {
if (view.equals(star1)) {
setPriority(1);
} else if (view.equals(star2)) {
setPriority(2);
} else if (view.equals(star3)) {
setPriority(3);
} else if (view.equals(star4)) {
setPriority(4);
} else if (view.equals(star5)) {
setPriority(5);
}
}

};

star1.setOnClickListener(onClickStars);
star2.setOnClickListener(onClickStars);
star3.setOnClickListener(onClickStars);
star4.setOnClickListener(onClickStars);
star5.setOnClickListener(onClickStars);

}

static final String[] Gender = new String[]{
"M","F"
};


private void populateFields() {

mRowId = null;
Bundle extras = getIntent().getExtras();
if (extras != null) {


String regn = extras.getString(Dbadapter.KEY_REGN);
String name = extras.getString(Dbadapter.KEY_NAME);
String surname = extras.getString(Dbadapter.KEY_SURNAME);
String priority = extras.getString(Dbadapter.KEY_PRIORITY);
String nation = extras.getString(Dbadapter.KEY_NATION);
String religion = extras.getString(Dbadapter.KEY_RELIGION);
String service = extras.getString(Dbadapter.KEY_SERVICE);
String unit = extras.getString(Dbadapter.KEY_UNIT);
String dtg = extras.getString(Dbadapter.KEY_DTG);
String rank = extras.getString(Dbadapter.KEY_RANK);
String age = extras.getString(Dbadapter.KEY_AGE);
String sex = extras.getString(Dbadapter.KEY_SEX);
String blood = extras.getString(Dbadapter.KEY_BLOOD);
String pending = extras.getString(Dbadapter.KEY_PENDING);
String allergies = extras.getString(Dbadapter.KEY_ALLERGIES);
flag=extras.getString(Dbadapter.FLAG);
mRowId = extras.getLong(Dbadapter.KEY_ID);

int pr = Integer.parseInt(priority);

mRegText.setText(regn);
mNameText.setText(name);
mSurnameText.setText(surname);
mNationText.setText(nation);
mReligionText.setText(religion);
mServiceText.setText(service);
mUnitText.setText(unit);
mDateText.setText(dtg);
mRankText.setText(rank);
mAgeText.setText(age);
setSex(sex);
mBloodText.setText(blood);
setChecked(pending);
mAllergiesText.setText(allergies);
setPriority(pr);
}
}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
super.onCreateOptionsMenu(menu);
menu.add(0, SAVE_ID, 0, R.string.confirm)
.setIcon(android.R.drawable.ic_menu_save)
.setAlphabeticShortcut('E');
menu.add(0, CLEAN_ID, 0, R.string.clean)
.setIcon(android.R.drawable.ic_menu_recent_history)
.setAlphabeticShortcut('D');
menu.add(0, BACK_ID, 0, R.string.back)
.setIcon(android.R.drawable.ic_menu_revert)
.setAlphabeticShortcut('B');
menu.add(0, OPTIONS_ID, 0, R.string.options)
.setIcon(android.R.drawable.ic_menu_agenda)
.setAlphabeticShortcut('O');
menu.add(0, DELETE_ID, 0, R.string.delete)
.setIcon(android.R.drawable.ic_menu_delete)
.setAlphabeticShortcut('R');
return true;
}

@Override
public boolean onMenuItemSelected(int featureId, MenuItem item) {
switch(item.getItemId()) {

case SAVE_ID:
if (mRegText.getText().toString().equals("") ||
mNameText.getText().toString().equals("") ||
mSurnameText.getText().toString().equals("")) {
showDialog(DIALOG_ERROR);
} else {

String sex,pen;
if(mSexSpinner.getSelectedItemPosition()== 0)
sex=new String("M");
else
sex=new String("F");

if(mPendlingCheck.isChecked())
pen=new String("true");
else
pen=new String("false");

if(flag.compareTo("0")==0){
flag="2";
myDbHelper.insertEntryPatient(mRegText.getText().toString(),
mNameText.getText().toString(),mSurnameText.getText().toString(),
priority, mNationText.getText().toString(),mReligionText.getText().toString(),
mServiceText.getText().toString(),mUnitText.getText().toString(),
mDateText.getText().toString(),mRankText.getText().toString(),
mAgeText.getText().toString(),sex,mBloodText.getText().toString(),
pen,mAllergiesText.getText().toString());

}else if(flag.compareTo("1")==0) {

Bundle extras = getIntent().getExtras();
if (extras != null) {
mRowId = null;
mRowId = extras.getLong(Dbadapter.KEY_ID);
}
myDbHelper.updateNotePatient(mRowId,mRegText.getText().toString(),
mNameText.getText().toString(),mSurnameText.getText().toString(),
priority, mNationText.getText().toString(),mReligionText.getText().toString(),
mServiceText.getText().toString(),mUnitText.getText().toString(),
mDateText.getText().toString(),mRankText.getText().toString(),
mAgeText.getText().toString(),sex,mBloodText.getText().toString(),
pen,mAllergiesText.getText().toString());

}
}
return true;
case CLEAN_ID:
mRegText.setText("");
mNameText.setText("");
mSurnameText.setText("");
mNationText.setText("");
mReligionText.setText("");
mServiceText.setText("");
mUnitText.setText("");
mRankText.setText("");
mAgeText.setText("");
mBloodText.setText("");
mDateText.setText("");
mAllergiesText.setText("");
return true;
case BACK_ID:
setResult(RESULT_OK);
finish();
return true;
case DELETE_ID:
showDialog(DIALOG_DELETE);
return true;
case OPTIONS_ID:
//don't permit to go to options if the patient isn't saved
if(flag.compareTo("2")==0 || flag.compareTo("1")==0 ){
Intent intent = new Intent();
intent.setClass(Patient.this, Options.class);
intent.putExtra(Dbadapter.KEY_ID, mRowId);
intent.putExtra(Dbadapter.KEY_REGN, mRegText.getText().toString());
startActivity(intent);
finish();
}else{
showDialog(DIALOG_OPTIONS);
}
return true;
}

return super.onMenuItemSelected(featureId, item);
}
// here is where i call the functions to delete
private void deleteNote() {
Bundle extras = getIntent().getExtras();
if (extras != null) {
mRowId = null;
mRowId = extras.getLong(Dbadapter.KEY_ID);
}
dbHelper.removeEntryPatient(mRowId);
//dbHelper.removeEntryClinic(mRowId);
finish();
}

@Override
protected void onPause() {
super.onPause();
saveState();
}

@Override
protected void onResume() {
super.onResume();
populateFields();
}

private void saveState() {
}

}

CLASS CLINCINFO(where i add other informations about the patient that are saved in a different table)
public class ClinicInfo extends Activity {

private EditText mRegText;
private EditText mIdText;
private EditText mDateText;
private Spinner mObsSpinner;
private Dbadapter myDbHelper;
Long mRowId;

private void setObs(String obs){
if(obs.compareTo("Panic")==0)
mObsSpinner.setSelection(0);
else
mObsSpinner.setSelection(1);
}


/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.clinicinfo);
myDbHelper = new Dbadapter(this);
myDbHelper.open();


/* Bundle b = this.getIntent().getExtras();
String obs = b.getString("OBSSPINNER");

if(obs.compareTo("Panic")==0)
((Spinner)findViewById(R.id.spinnerobs)).setSelection(0);
else
((Spinner)findViewById(R.id.spinnerobs)).setSelection(1);
*/
mRegText = (EditText) findViewById(R.id.regn2);
mIdText = (EditText) findViewById(R.id.ident2);
mDateText = (EditText) findViewById(R.id.date);
mObsSpinner = (Spinner)findViewById(R.id.spinnerobs) ;

final Spinner spinner = (Spinner) findViewById(R.id.spinnerobs);
ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,android.R.layout.simple_spinner_item, Obs);
adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
spinner.setAdapter(adapter);

Button botonHome = (Button)findViewById(R.id.home );
botonHome.setOnClickListener(pulsarBotonHome);

Button botonSave = (Button)findViewById(R.id.save );
botonSave.setOnClickListener(pulsarBotonSave);

Button botonBack = (Button)findViewById(R.id.back );
botonBack.setOnClickListener(pulsarBotonBack);

populateFields();


}

static final String[] Obs = new String[]{
"Panic","Shock","BOH?!"
};

private OnClickListener pulsarBotonHome = new OnClickListener()
{
public void onClick(View v)
{
Intent intent = new Intent();
intent.setClass(ClinicInfo.this, Mhealth2.class);
startActivity(intent);
finish();
}
};

private OnClickListener pulsarBotonSave = new OnClickListener()
{
public void onClick(View v)
{
myDbHelper.insertEntryClinic(mRegText.getText().toString(),
mDateText.getText().toString(),"hh");

}
};

private OnClickListener pulsarBotonBack = new OnClickListener()
{
public void onClick(View v)
{
/*Bundle extras = getIntent().getExtras();
if (extras != null) {
mRowId = null;
mRowId = extras.getLong(Dbadapter.KEY_ID);
}
myDbHelper.removeEntryClinic(mRowId);*/
Intent intent = new Intent();
intent.setClass(ClinicInfo.this, Options.class);
startActivity(intent);
finish();
}
};

private void populateFields() {

Bundle extras = getIntent().getExtras();
if (extras != null) {
String regn = extras.getString(Dbadapter.KEY_REGN);
mRegText.setText(regn);
String id = extras.getString(Dbadapter.CLINICKEY_ID);
mIdText.setText(id);
}

}
}

CLASS DBADAPTER(where i define the metods that i can use on a database and where i create the db)
public class Dbadapter {

//database properties
private static final String DATABASE_NAME="myDb.db";
private static final String DATABASE_TABLE="patients";
private static final String DATABASE_TABLE_INFO="clinicinfo";
private static final int DATABASE_VERSION=1;

//table patient properties
public static final String KEY_ID="_id";
public static final String KEY_REGN="regn";
public static final String KEY_NAME="name";
public static final String KEY_SURNAME="surname";
public static final String KEY_PRIORITY="priority";
public static final String KEY_NATION="nation";
public static final String KEY_RELIGION="religion";
public static final String KEY_SERVICE="service";
public static final String KEY_UNIT="unit";
public static final String KEY_DTG="dtg";
public static final String KEY_RANK="rank";
public static final String KEY_AGE="age";
public static final String KEY_SEX="sex";
public static final String KEY_BLOOD="blood";
public static final String KEY_PENDING="pending";
public static final String KEY_ALLERGIES="allergies";
public static final String FLAG="";

//table clinicinfo properties
public static final String CLINICKEY_ID="_id";
public static final String CLINICKEY_REGN="regn";
public static final String CLINICKEY_DTG="dtg";
public static final String CLINICKEY_OBS="obs";

//create script patient
private static final String DATABASE_CREATE= "create table " +
DATABASE_TABLE + " (" +
KEY_ID + " integer primary key autoincrement, " +
KEY_REGN + " text not null, " +
KEY_NAME + " text not null, " +
KEY_SURNAME + " text not null, " +
KEY_PRIORITY + " integer not null, " +
KEY_NATION + " text not null, " +
KEY_RELIGION + " text not null, " +
KEY_SERVICE + " text not null, " +
KEY_UNIT + " text not null, " +
KEY_DTG + " text not null, " +
KEY_RANK + " text not null, " +
KEY_AGE + " text not null, " +
KEY_SEX + " text not null, " +
KEY_BLOOD + " text not null, " +
KEY_PENDING + " text not null, " +
KEY_ALLERGIES + " text not null "
+ " );" ;

//create script patient
private static final String DATABASE_CREATE_INFO= "create table " +
DATABASE_TABLE_INFO + " (" +
CLINICKEY_ID + " integer references patients, " +
CLINICKEY_REGN + " text not null, " +
CLINICKEY_DTG + " text not null, " +
CLINICKEY_OBS + " text not null " + ");";

private SQLiteDatabase db;
private final Context context;
private MyDbHelper myDbHelper;

//a internal class myDbHelper which will take care of creating and updating the database.
//This class will be based on the SQLiteOpenHelper.
public static class MyDbHelper extends SQLiteOpenHelper {

public MyDbHelper(Context context, String name, CursorFactory cursor,
int version){
super(context, name, cursor, version);
}

@Override
//only gets called if the database does not exist
public void onCreate(SQLiteDatabase _db){
_db.execSQL(DATABASE_CREATE);
_db.execSQL(DATABASE_CREATE_INFO);
}

@Override
//only gets called if the database does not exist
public void onUpgrade(SQLiteDatabase _db, int _OldVersion, int NewVersion){
//drop old one
_db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
_db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_INFO);
//create new one
onCreate(_db);
}
}

public Dbadapter(Context _context){
context=_context;
myDbHelper= new MyDbHelper(context,DATABASE_NAME,null,DATABASE_VERSION);
}

public Dbadapter open() throws SQLException{
db=myDbHelper.getWritableDatabase();
return this;
}

public void createDb(SQLiteDatabase _db) throws SQLException{
myDbHelper.onCreate(_db);
}

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

/**************************************************************/
//PATIENTS TABLE SECTION
/**************************************************************/

public long insertEntryPatient(String regn, String name, String surname, Integer priority,
String nation, String religion,String service, String unit, String dtg,
String rank, String age, String sex,String blood,String pending, String allergies){

ContentValues contentValues=new ContentValues();
contentValues.put(KEY_REGN, regn);
contentValues.put(KEY_NAME, name);
contentValues.put(KEY_SURNAME, surname);
contentValues.put(KEY_PRIORITY, priority);
contentValues.put(KEY_NATION, nation);
contentValues.put(KEY_RELIGION, religion);
contentValues.put(KEY_SERVICE, service);
contentValues.put(KEY_UNIT, unit);
contentValues.put(KEY_DTG, dtg);
contentValues.put(KEY_RANK, rank);
contentValues.put(KEY_AGE, age);
contentValues.put(KEY_SEX, sex);
contentValues.put(KEY_BLOOD, blood);
contentValues.put(KEY_PENDING, pending);
contentValues.put(KEY_ALLERGIES, allergies);

return db.insert(DATABASE_TABLE, null, contentValues);
}

//A very short method that deletes the row with a certain id.
//Only one row can be deleted at one time.
public boolean removeEntryPatient(long _id){
return db.delete(DATABASE_TABLE, KEY_ID + "=" + _id ,null)>0;
}

//getAllEntries returns a Cursor that can we used to step through all results.
public Cursor getAllEntriesPatient(int order){
switch(order) {

case 1:
return db.query(DATABASE_TABLE, new String []{KEY_ID,KEY_REGN,
KEY_NAME,KEY_SURNAME,KEY_PRIORITY,KEY_NATION, KEY_RELIGION,
KEY_UNIT,KEY_SERVICE,KEY_DTG,KEY_RANK,KEY_AGE,KEY_BLOOD,
KEY_SEX,KEY_PENDING,KEY_ALLERGIES}, null , null,
null, null, KEY_SURNAME );
case 2:
return db.query(DATABASE_TABLE, new String []{KEY_ID,KEY_REGN,
KEY_NAME,KEY_SURNAME, KEY_PRIORITY,KEY_NATION, KEY_RELIGION,
KEY_UNIT,KEY_SERVICE,KEY_DTG,KEY_RANK,KEY_AGE,KEY_BLOOD,
KEY_SEX,KEY_PENDING,KEY_ALLERGIES}, null , null,
null, null, KEY_SURNAME + " DESC");

case 3:
return db.query(DATABASE_TABLE, new String []{KEY_ID,KEY_REGN,
KEY_NAME,KEY_SURNAME,KEY_PRIORITY,KEY_NATION, KEY_RELIGION,
KEY_UNIT,KEY_SERVICE,KEY_DTG,KEY_RANK,KEY_AGE,KEY_BLOOD,
KEY_SEX,KEY_PENDING,KEY_ALLERGIES}, null , null,
null, null, KEY_PRIORITY);
case 4:
return db.query(DATABASE_TABLE, new String []{KEY_ID,KEY_REGN,
KEY_NAME,KEY_SURNAME,KEY_PRIORITY,KEY_NATION, KEY_RELIGION,
KEY_UNIT,KEY_SERVICE,KEY_DTG,KEY_RANK,KEY_AGE,KEY_BLOOD,
KEY_SEX,KEY_PENDING,KEY_ALLERGIES}, null , null,
null, null, KEY_PRIORITY + " DESC");
}
return db.query(DATABASE_TABLE, new String []{KEY_ID,KEY_REGN,
KEY_NAME,KEY_SURNAME,KEY_PRIORITY,KEY_NATION, KEY_RELIGION,
KEY_UNIT,KEY_SERVICE,KEY_DTG,KEY_RANK,KEY_AGE,KEY_BLOOD,
KEY_SEX,KEY_PENDING,KEY_ALLERGIES}, null , null, null, null, null);
}

public boolean updateNotePatient(long rowId, String regn, String name, String surname,
Integer priority, String nation, String religion, String service,
String unit, String dtg, String rank, String age,String sex,
String blood, String pending, String allergies) {

ContentValues args = new ContentValues();
args.put(KEY_REGN, regn);
args.put(KEY_NAME, name);
args.put(KEY_SURNAME, surname);
args.put(KEY_PRIORITY, priority);
args.put(KEY_NATION, nation);
args.put(KEY_RELIGION, religion);
args.put(KEY_SERVICE, service);
args.put(KEY_UNIT, unit);
args.put(KEY_DTG, dtg);
args.put(KEY_RANK, rank);
args.put(KEY_AGE, age);
args.put(KEY_SEX, sex);
args.put(KEY_BLOOD, blood);
args.put(KEY_PENDING, pending);
args.put(KEY_ALLERGIES, allergies);

return db.update(DATABASE_TABLE, args, KEY_ID + "=" + rowId, null) > 0;
}

/**************************************************************/
//CLINICINFO TABLE SECTION
/**************************************************************/

public long insertEntryClinic(String regn, String dtg, String obs){

ContentValues contentValues=new ContentValues();
contentValues.put(CLINICKEY_REGN, regn);
contentValues.put(CLINICKEY_DTG, dtg);
contentValues.put(CLINICKEY_OBS, obs);

return db.insert(DATABASE_TABLE_INFO, null, contentValues);
}

public boolean removeEntryClinic(long _id){
return db.delete(DATABASE_TABLE_INFO, CLINICKEY_ID + "=" + _id ,null)>0;
}
}[color=] [/color]

THANKS FOR YOUR HELP AND FOR THE PATIENCE!!!!!!!!!!!!!!!!!!!!!!!
kolch9
Junior Developer
Junior Developer
 
Posts: 11
Joined: Wed Feb 18, 2009 2:17 pm

Postby nmc » Sat Jun 27, 2009 12:09 pm

the application(process mhealth2.android)has stopped unexpetedly. please try again.


- Open the debug perspective and look at the messages you got there.
- run your project in the debugger and use breakpoints to find the line causing the crash

I have never used 'references' in sqlite table layouts, maybe this implicit foreign key causes the trouble because of the missing actions?
Try '... references patients(_id) ON DELETE CASCADE', so the clinicinfo entry will be automatically deleted when you delete the patient.
nmc
Senior Developer
Senior Developer
 
Posts: 154
Joined: Thu Nov 27, 2008 8:30 pm
Location: Germany

Postby cadlg » Mon Jun 29, 2009 3:38 pm

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. dbHelper.removeEntryPatient(mRowId);
  2. //dbHelper.removeEntryClinic(mRowId);
Parsed in 0.031 seconds, using GeSHi 1.0.8.4


Also, I don't think you can use the same ID to delete both, the clinics and the patient. You could have, for example, 5 clinics, and just one patient. When you use the above code to delete the clinic with id = 5, you will also try to delete a patient with id = 5, which could not exist.

On the other hand. I don't really understand this database design.

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. private static final String DATABASE_CREATE_INFO= "create table " +
  2. DATABASE_TABLE_INFO + " (" +
  3. CLINICKEY_ID + " integer references patients, " +
  4. CLINICKEY_REGN + " text not null, " +
  5. CLINICKEY_DTG + " text not null, " +
  6. CLINICKEY_OBS + " text not null " + ");";
Parsed in 0.035 seconds, using GeSHi 1.0.8.4


What is the purpose of the CLINIC ID referencing the PATIENTS table ?? If it is really the CLINIC ID, then it has nothing to do with the PATIENT ID in the PATIENTS table. Maybe you need the CLINIC ID and a PATIENT ID in the CLINIC table, and if that's the case, it is the PATIENT ID in the CLINIC TABLE which should reference the PATIENTS table.
cadlg
Experienced Developer
Experienced Developer
 
Posts: 84
Joined: Wed Feb 20, 2008 12:33 am
Location: Guatemala

Postby kolch9 » Tue Jun 30, 2009 10:14 am

Look this is the error that i have when i try to delete..I'm sorry but i don't know how to see the errors in details. And from how i set the application there is a clinic row for each patients row. if you want i can post all the code if can help you. I tried to change the db structure too,according to cadlg but continues to don't work :cry:
Attachments
err.JPG
err.JPG (56.48 KiB) Viewed 2459 times
kolch9
Junior Developer
Junior Developer
 
Posts: 11
Joined: Wed Feb 18, 2009 2:17 pm

Postby kolch9 » Tue Jun 30, 2009 11:40 am

I've seen another thing..i tried to delete the clinic row by the CLINICKEY_REGN in the Clinic window,and it works!!!!!!!! if i try from the Patient window(that is where i need to delete it),it doesn't work instead..i don't know why!!have any ideas of what could be the problem?Thanks again!!!!!
kolch9
Junior Developer
Junior Developer
 
Posts: 11
Joined: Wed Feb 18, 2009 2:17 pm

Top

Return to Networking & Database Problems

Who is online

Users browsing this forum: No registered users and 2 guests