SQLite Queries and editing?

Put your problem here if it does not fit any of the other categories.

SQLite Queries and editing?

Postby Dead_Jester » Fri Nov 05, 2010 10:25 pm

Ok so I am completely new to SQLite databases. I have gone through tutorials and have finally figured out how to build a database and to insert data into it but I am stuck after that.

This is generally what my database looks like:
Code: Select all
_names          _location            _price         _lotsOwned
"one"            "Atlanta"             120              132
"two"            "New York"            290              5
"three"          "Dallas"              4000             4
...


Now that I have my database set up I need to retrieve and manipulate that data.

Say I want to retrieve _lotOwned for "one", how would I query the database?
What if i wanted to find the _name of the one in "Dallas"?

I also need to edit this data.

How would I change the _lotsOwned of "one"?


I have been digging and digging through online tutorials and I find that they are all too vague that I cant quite follow how to do what I am looking for or they are far to specific that I cant convert it into what I need.

Any help would be appreciated. Thanks.
Dead_Jester
Freshman
Freshman
 
Posts: 7
Joined: Fri Nov 05, 2010 10:06 pm

Top

Re: SQLite Queries and editing?

Postby urbantrad » Sat Nov 06, 2010 1:18 pm

First, open the database using
Code: Select all
SQLiteDatabase db = mDatabaseOpenHelper.getWritableDatabase();

(If only reading data, you can use getReadableDatabase())

For reading data:
Code: Select all
String table = "tableName";
String[] columns = new String[] {"_lotOwned"};
String selection = "_names = ?";
String[] selectionArgs = new String[] {"one"};
Cursor cursor = db.query(table, columns, selection, selectionArgs,
            null, null, null);

You can then use the cursor to navigate through the results. In place of the null arguments, you can use GROUPBY, HAVING and ORDER clauses as normal in SQL.

For editing data:
Code: Select all
String table = "tableName";
ContentValues values = new ContentValues();
values.put("_lotOwned", -123);
String whereClause = "_names = ?";
String[] whereArgs = new String[] {"one"};
db.update(table, values, whereClause, whereArgs);

I think this one speaks for itself. You can also put "one" in the whereClause directly instead of the question mark, but when you have many variables, using the list keeps the code much more readable.

Finally, you should close the database using
Code: Select all
db.close();


I hope i made things clearer :)

EDIT: if you want more reading, i learnt SQLite using this guide: http://www.screaming-penguin.com/node/7742
urbantrad
Senior Developer
Senior Developer
 
Posts: 104
Joined: Thu Sep 09, 2010 10:19 pm

Re: SQLite Queries and editing?

Postby Dead_Jester » Sat Nov 06, 2010 9:24 pm

Thanks very much. This helps me quite a bit. Now I understand enough of it to make my own helper class as recommended by the API.

1 more question though. My app is going to obtain quite a bit of information needed at runtime from the database on various activities. Is there a way I can open the database from the main activity and read/write to it from various other activities without opening and closing in each activity separately? It seems that doing so would be unnecessarily expensive in system memory.

Thanks again.
Dead_Jester
Freshman
Freshman
 
Posts: 7
Joined: Fri Nov 05, 2010 10:06 pm

Re: SQLite Queries and editing?

Postby urbantrad » Sun Nov 07, 2010 12:51 am

What you possibly could do, is open an instance of the writable database on creation of your helper class, and keep it in a field there, then reuse it every time (and pass the same helper class around to all your activities). I do believe it is safer/cleaner/better to only have it open when you do a transaction, and close it in between. If you have many transactions to do at once, you could make a function that handles them in batch without opening and closing the database all the time.
urbantrad
Senior Developer
Senior Developer
 
Posts: 104
Joined: Thu Sep 09, 2010 10:19 pm

Re: SQLite Queries and editing?

Postby Dead_Jester » Mon Nov 08, 2010 10:03 pm

Thanks for the help and the advice.

After looking over my app again and really looking at when, how, and how often I use the database, I determined that while it will be used by nearly every activity it is used for only short transactions and not on every execution of the activity. Based on this observation and your advice I decided that it would be best to just open and close the database as needed. I have been playing around with it a little I am beginning to become comfortable with at least the basics of SQLite.
Dead_Jester
Freshman
Freshman
 
Posts: 7
Joined: Fri Nov 05, 2010 10:06 pm

Re: SQLite Queries and editing?

Postby Dead_Jester » Tue Nov 09, 2010 1:38 am

My previous questions gave me the information needed to complete the app that I am currently working on but i have a few more questions about SQLite in general that I would like to know for future applications that I might use a database in.

1: How do I determine within my code whether or not the table "tableName" exists?
I have searched everywhere and cannot find a clear answer on this one. I know you can call execSQL("CREATE TABLE IF NOT EXISTS "... but say for instance I just wanted a boolean value telling me "true" it exists or "false" it does not exist?

2: How does "execSQL("CREATE TABLE IF NOT EXISTS ..." work?
Does it examine the table name to determine whether it exists or does it examine the table name, the column names, and data types? For example if tableName exists with the columns "_one", "_two",and "_three" all VARCHAR's and you called:
Code: Select all
db.execSQL("CREATE TABLE IF NOT EXISTS " + tableName + " (_four VARCHAR, _five INT, _six INT);"

how would the program respond?

3: How would you determine the number of rows/columns within a table?
Within code how could you discover that the table has 4 columns and 11 rows?

4: How do you append an additional column to and existing table?
If you needed to add a column to the table while keeping the existing table data intact, how would you do so?


I know I am asking quite a bit here but I would greatly appreciate and answers you can provide.
Dead_Jester
Freshman
Freshman
 
Posts: 7
Joined: Fri Nov 05, 2010 10:06 pm

Top

Re: SQLite Queries and editing?

Postby urbantrad » Tue Nov 09, 2010 10:18 am

I'm afraid i don't have enough experience to answer all of those, only #3 i can answer for sure.

3: in the Cursor you have methods getColumnCount() returnining the number of colums in a result, and getCount() returning the number of rows. If you query to return an entire table, this will be the dimensions of the table.

EDIT: 4: you can probably do this using the SLQ "alter table" command
Code: Select all
ALTER TABLE tableName ADD COLUMN rating int;
urbantrad
Senior Developer
Senior Developer
 
Posts: 104
Joined: Thu Sep 09, 2010 10:19 pm

Re: SQLite Queries and editing?

Postby Dead_Jester » Thu Nov 11, 2010 9:31 pm

Thank you very much. I tested #2 and discovered the information i needed. I now have the answers for questions 2-4 that I asked but I still have no idea about number 1. Please help me with the answer for this.
Dead_Jester wrote:1: How do I determine within my code whether or not the table "tableName" exists?
I have searched everywhere and cannot find a clear answer on this one. I know you can call execSQL("CREATE TABLE IF NOT EXISTS "... but say for instance I just wanted a boolean value telling me "true" it exists or "false" it does not exist?


Thank again.
Dead_Jester
Freshman
Freshman
 
Posts: 7
Joined: Fri Nov 05, 2010 10:06 pm

Re: SQLite Queries and editing?

Postby urbantrad » Fri Nov 12, 2010 1:53 pm

Code: Select all
public boolean tableExists(String name) {
   try {
      mDataBaseOpenHelper.getReadableDatabase().query(name, null, null, null, null, null, null);
   } catch (SQLiteException e) {
      return false;
   }
   return true;
}


probably not the most efficient way of doing so, but at least it seems to work.
urbantrad
Senior Developer
Senior Developer
 
Posts: 104
Joined: Thu Sep 09, 2010 10:19 pm

Top

Return to Other Coding-Problems

Who is online

Users browsing this forum: No registered users and 17 guests