Re: [sqlite] Android : UNIQUE makes my DB go crazy

[Clemens Ladisch]

Sorin Grecu wrote:
> I'm having an issue with my app.

Already solved:

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Android : UNIQUE makes my DB go crazy

[Sorin Grecu]

I'm having an issue with my app.
What my app does is this : gets some data from a couple of edittexts(3 per 
row,created dynamically) and puts them in a database.

What i want the database to do is this : take the `product name`,the `quantity` 
and the `price` and put them in the table.The name should be `UNIQUE`(it will 
be used to power an autocomplete,it needs to be unique not to have duplicates 
in the AC list).The price in the database must be the last price inserted for 
that product(for example,if `Cheese` at 3$ is inserted and after that `Cheese` 
at 2.5$ in the database we will find 2.5$).The quantity has to be summed up(if 
i enter Cheese in quantity 3 and then again Cheese in quantity 4 in the 
database we will find 7).



Now,my issue : Lets say i enter this in my shopping list :

     1.  Hhhh42.5
     2. Dddd31
     3. Eeee2   2
     4. Aaaa    5 3.5

In my database I will find this :

    4.      Aaaa42.5
    2.    Dddd 31
    3.    Eeee22
    1.    Hhhh53.5


So,the issue is that it arranges the product name column alphabetically but the 
other columns remain in the same order,the one i entered in the edittexts.
I did some tests,if i remove the `UNIQUE` from the product name column,it will 
enter it as it should but of course,it will create duplicates,which i don't 
need.I don't get it,what's wrong ? why does `UNIQUE` trigger this ? 

Here's my code :

My table creation :

    public class SQLiteCountryAssistant extends SQLiteOpenHelper {
private static final String DB_NAME = "usingsqlite.db";
private static final int DB_VERSION_NUMBER = 1;
private static final String DB_TABLE_NAME = "countries";
private static final String DB_COLUMN_1_NAME = "country_name";
private static final String DB_COLUMN_2_NAME = "country_counter";
private static final String DB_COLUMN_3_NAME = "country_price";

private static final String DB_CREATE_SCRIPT = "create table "
+ DB_TABLE_NAME
+ " (_id INTEGER PRIMARY KEY,country_name text unique, country_quantity REAL 
DEFAULT '0',country_price REAL);) ";
private SQLiteDatabase sqliteDBInstance = null;

public SQLiteCountryAssistant(Context context) {
super(context, DB_NAME, null, DB_VERSION_NUMBER);
}

    @Override
public void onCreate(SQLiteDatabase sqliteDBInstance) {
Log.i("onCreate", "Creating the database...");
sqliteDBInstance.execSQL(DB_CREATE_SCRIPT);
}

My insert method :


    public void insertCountry(String countryName, String countryPrice,
String countryQuantity) {

sqliteDBInstance.execSQL("INSERT OR IGNORE INTO " + DB_TABLE_NAME
+ "(country_name, country_quantity, country_price) VALUES('"
+ countryName + "','0', '" + countryPrice + "')");
sqliteDBInstance.execSQL("UPDATE " + DB_TABLE_NAME
+ " SET country_name='" + countryName
+ "', country_quantity=country_quantity+'" + countryQuantity
+ "' WHERE country_name='" + countryName + "';");
sqliteDBInstance.execSQL("UPDATE " + DB_TABLE_NAME
+ " SET country_name='" + countryName + "', country_price='"
+ countryPrice + "' WHERE country_name='" + countryName + "';");
}


And this is how i call the insert method :


for (int g = 0; g < allcant.size() - 1; g++) {
if (prod[g] != "0.0") {
sqlliteCountryAssistant.insertCountry(prod[g],pret[g],cant[g]);
}

Also,please excuse my messy code,i've started learning android with no 
programming background like a month ago.I just got my bachelors degree in 
Sociology so yea,i'm an absolute beginner.If there is way to do it better then 
i did and i'm pretty sure there is,please,show me the way,heh.

Thanks and have a good day !
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users