How do I insert data into table right after the database is

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

How do I insert data into table right after the database is

Postby calebrogers » Sat Nov 07, 2009 12:48 am

Hi,

I'm new to android development, and this forum, and I'm basically just trying out different things to see what I can do. The docs are a little thin in some areas...

My question is this: How do I insert a few records into a newly created database table when the app is installed? I was using the Notepad example from Google, and just hacking it apart to see what kind of different functionality I can get out of it. It seems that shipping an application with data already in a database is a trouble spot for android.

I've seen tutorials where someone creates a database on the emulator, and then uses that as a resource, and then imports it when the database is created, but that's not really what I'm after. I want to be able to run a few insert queries right when the database is created.

I tried a db.rawQuery right after the db.execSQL(DATABASE_CREATE) statement, but that didn't work. If I put the insert queries in the apps onCreate function, then that will insert the data every time the app loads, right? I don't want duplicate data.

So my basic problem is shipping an application with a pre-populated database. Besides the pre-baked database resource idea (that really isn't something I want to do), I haven't yet found any other suitable tutorials. There are 3 ways I can add data to my table (as far as I can see):

1) run insert queries the first time the app runs
2) download the data from a web server and run insert queries once, the first time the app runs
3) have a flat file with data, read it in, and run insert queries on the data, the first time the app runs.

And since I'm thinking about it, how do you tell your code to do something only the first time an app is run?

Thanks for any input you guys have! I look forward to learning a lot from this forum, and then giving back to it once I figure out what I'm doing.

Caleb
calebrogers
Junior Developer
Junior Developer
 
Posts: 22
Joined: Sat Nov 07, 2009 12:34 am

Top

Postby nmc » Sat Nov 07, 2009 1:55 am

If I put the insert queries in the apps onCreate function, then that will insert the data every time the app loads, right?

That depends on your layout - if the duplicate insert statements produce a primary key conflict, then you wont get dupes ;)
The clean way to do this (if you insist on doing it in onCreate) is to check wether yout table is empty - insert if it is, return otherwise.
(sql: "select count(*) from yourTable" -> 0=empty)
nmc
Senior Developer
Senior Developer
 
Posts: 154
Joined: Thu Nov 27, 2008 8:30 pm
Location: Germany

Postby calebrogers » Sat Nov 07, 2009 2:36 am

Well, it's not that I "insist" on it, but it's something I want to be able to do, since I can see how it could be applied here and there. Just to make sure I'm understanding you:

In my onCreate function for the main class of the app, after I open the database, I check to see if the table is empty, and if so, run my insert queries. Is that what you're saying?
calebrogers
Junior Developer
Junior Developer
 
Posts: 22
Joined: Sat Nov 07, 2009 12:34 am

Postby nmc » Sat Nov 07, 2009 12:21 pm

In my onCreate function for the main class of the app, after I open the database, I check to see if the table is empty, and if so, run my insert queries. Is that what you're saying?

Yes.
Doenst sound logical for you?
nmc
Senior Developer
Senior Developer
 
Posts: 154
Joined: Thu Nov 27, 2008 8:30 pm
Location: Germany

Postby Quetzalcoatl » Sat Nov 07, 2009 12:30 pm

Hi Cale,

Is there some reason that stops you from using a database as a resource? It seems a bit odd to insert items into a database on installation if you know what these items are beforehand.
Unless you're extracting some information from the device or something and storing it for later?

If there is no need to create it then you might as well just use the resource database.

Cheers,
Steve
Image
Quetzalcoatl
Senior Developer
Senior Developer
 
Posts: 129
Joined: Sat Oct 18, 2008 8:21 pm
Location: near Manchester, UK

Postby calebrogers » Sat Nov 07, 2009 3:09 pm

Based on the tutorial I read, having a pre-baked database as a resource creates a duplicate data set within the app. This isn't really a problem for a small database, but I couldn't help but think that there must be a different way to do it. So if I have a small set of initial data I want to present to the user, would it not be fast and efficient to just insert that data the first time the app is launched?

There's always more than one way to do something, so I'm just kicking some alternatives around, trying learn the platform.
calebrogers
Junior Developer
Junior Developer
 
Posts: 22
Joined: Sat Nov 07, 2009 12:34 am

Top

Postby I_Artist » Sat Nov 07, 2009 8:27 pm

Hi Caleb,

I have exactly the same need as you. The evolution of my app brought me to load my 'provided' data from an XML file. The issue with this is that my XML resource is read-only. But this suits me because I can always let the user modify these 'provided' values and insert those modified values in the database. Leaving me with the original XML file if ever there is a need to revert.

Cheers!
The I_Artist

follow me on Twitter: @I_Artist
follow Android Development on Twitter: #androiddev

Show the world that you are an #androiddev, wear the badge http://www.twibbon.com/join/androiddev
User avatar
I_Artist
Developer
Developer
 
Posts: 33
Joined: Fri Oct 16, 2009 3:49 am
Location: St-Eustache, Quebec

Postby calebrogers » Sun Nov 08, 2009 5:10 pm

OK, I tried something, and it seems like it would work on a logical level, but it keeps crashing my application just as soon as it loads. Maybe someone can take a look at my functions and point out my mistakes. Here is the conditional statement in my onCreate activity for the app.

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.  if(mDbHelper.recordCheck() < 1){
  2.  
  3.                 mDbHelper.insertInitialData();
  4.  
  5.         }
Parsed in 0.030 seconds, using GeSHi 1.0.8.4


Remember, I am using the Notepad example as a starting point, and just modifying it. I think I'm still a bit confused on how to use Cursors properly... Anyway, I've got the main application class, and then a database adapter class to handle all of the database interfacing. I added the recordCheck and insertInitialData functions to the db class. here are those two functions.

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. // check to see if there are any records in the database
  2.  
  3.     public int recordCheck() {
  4.  
  5.         int recordCount = 0;
  6.  
  7.         Cursor countCursor;
  8.  
  9.        
  10.  
  11.         // count the records currently in the table
  12.  
  13.         countCursor = mDb.rawQuery("select count(*} from notes", null);
  14.  
  15.        
  16.  
  17.         recordCount = countCursor.getCount();
  18.  
  19.        
  20.  
  21.         return recordCount;
  22.  
  23.     }
  24.  
  25.    
  26.  
  27.     // insert the initial data set (run only once, if the database is empty)
  28.  
  29.     public void insertInitialData() {
  30.  
  31.         // insert data
  32.  
  33.         ContentValues initialValues = new ContentValues();
  34.  
  35.         initialValues.put(KEY_TITLE, "Test 1");
  36.  
  37.         initialValues.put(KEY_TITLE, "Test 2");
  38.  
  39.         initialValues.put(KEY_TITLE, "Test 3");
  40.  
  41.         initialValues.put(KEY_TITLE, "Test 4");
  42.  
  43.        
  44.  
  45.         mDb.insert(DATABASE_TABLE, null, initialValues);
  46.  
  47.     }
Parsed in 0.033 seconds, using GeSHi 1.0.8.4


The app crashes every single time I try to load it. I tried commenting out all but one initialValues.put() statements, but got the same result. When I comment out my initial if statement -- the first code block above -- the app loads just fine. So this leads me to believe that while I don't have a syntax error in either of the two functions I added, something is breaking.

Does anyone see anything wrong with them?

Thanks,

Caleb
calebrogers
Junior Developer
Junior Developer
 
Posts: 22
Joined: Sat Nov 07, 2009 12:34 am

Postby mdownie » Sun Nov 08, 2009 7:01 pm

You seem to have a syntax error in your recordCheck() function. Try this:
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.  
  2. // check to see if there are any records in the database
  3.  
  4.     public int recordCheck() {
  5.  
  6.      int recordCount = 0;
  7.  
  8.      Cursor countCursor;
  9.  
  10.      
  11.  
  12.      // count the records currently in the table
  13.  
  14.      countCursor = mDb.rawQuery("select count(*) from notes", null);
  15.  
  16.      
  17.  
  18.      recordCount = countCursor.getCount();
  19.  
  20.      
  21.  
  22.      return recordCount;
  23.  
  24.     }
  25.  
  26.  
Parsed in 0.035 seconds, using GeSHi 1.0.8.4

You had countCursor = mDb.rawQuery("select count(*} from notes", null);, but it should be countCursor = mDb.rawQuery("select count(*) from notes", null);. Let me know if this resolves your issue.
mdownie
Junior Developer
Junior Developer
 
Posts: 20
Joined: Tue Nov 25, 2008 6:54 pm
Location: Las Vegas

Postby calebrogers » Sun Nov 08, 2009 7:30 pm

Wow...that was kind of a silly syntax error. After looking at it, it seemed fine, but clearly wasn't. The curly bracket didn't look different enough for me to catch. Oh well.

Anyway, the program no longer crashes. The values don't get entered, however. So, now there's something else wrong with it. This is odd, because I'm just using the exact same function for adding a new note that is in the Notepad example, but I'm just supplying my own hard-coded test item.

Or, another thing that could be wrong is what my recordCount function is returning. Maybe I'm not using the Cursor correctly when I get it and then when I get the count of it? My conditional statement checks for < 1, so maybe that's where the problem is?

Thanks,

Caleb
calebrogers
Junior Developer
Junior Developer
 
Posts: 22
Joined: Sat Nov 07, 2009 12:34 am

Postby mdownie » Sun Nov 08, 2009 7:40 pm

I think I know what the problem is. Try using this:

countCursor = mDb.rawQuery("select * from notes", null);

Cursor.getCount() returns the number of rows from the query, but you are not getting rows in your query. See if this works. Your logic seems fine, but I don't think the query that you are using is returning what you are expecting. Let me know if it works. Thanks.
mdownie
Junior Developer
Junior Developer
 
Posts: 20
Joined: Tue Nov 25, 2008 6:54 pm
Location: Las Vegas

Postby calebrogers » Sun Nov 08, 2009 8:22 pm

That worked! Awesome. The result wasn't what I was after, however. This is just due to my overall ignorance of the functions and such at this point. The initialValues.put() function doesn't work like an array or anything. So with my original setup, only the last item was inserted. So I changed it like this, and it worked:

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.  
  2. // insert the initial data set (run only once, if the database is empty)
  3.  
  4.     public void insertInitialData() {
  5.  
  6.         // insert data
  7.  
  8.         ContentValues initialValues = new ContentValues();
  9.  
  10.         initialValues.put(KEY_TITLE, "Test 1");
  11.  
  12.         mDb.insert(DATABASE_TABLE, null, initialValues);
  13.  
  14.        
  15.  
  16.         initialValues.put(KEY_TITLE, "Test 2");
  17.  
  18.         mDb.insert(DATABASE_TABLE, null, initialValues);
  19.  
  20.        
  21.  
  22.         initialValues.put(KEY_TITLE, "Test 3");
  23.  
  24.         mDb.insert(DATABASE_TABLE, null, initialValues);
  25.  
  26.        
  27.  
  28.         initialValues.put(KEY_TITLE, "Test 4");
  29.  
  30.         mDb.insert(DATABASE_TABLE, null, initialValues);
  31.  
  32.     }
  33.  
  34.  
Parsed in 0.037 seconds, using GeSHi 1.0.8.4


I'm not sure how efficient this would be with 500 or so records, but it does get the job done for what I was trying to do. Thanks for all the help!

Caleb
calebrogers
Junior Developer
Junior Developer
 
Posts: 22
Joined: Sat Nov 07, 2009 12:34 am

Postby mdownie » Sun Nov 08, 2009 8:28 pm

You can make it only one statement. You are using the same key though for each value. So you are overwriting the same key every time. Try something like this maybe:

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.  
  2. // insert the initial data set (run only once, if the database is empty)
  3.  
  4.     public void insertInitialData() {
  5.  
  6.      // insert data
  7.  
  8.      ContentValues initialValues = new ContentValues();
  9.  
  10.         initialValues.put("key1", "Test 1");
  11.  
  12.         initialValues.put("key2", "Test 2");
  13.  
  14.         initialValues.put("key3", "Test 3");
  15.  
  16.         initialValues.put("key4", "Test 4");
  17.  
  18.         mDb.insert(DATABASE_TABLE, null, initialValues);
  19.  
  20.     }
  21.  
  22.  
Parsed in 0.037 seconds, using GeSHi 1.0.8.4
mdownie
Junior Developer
Junior Developer
 
Posts: 20
Joined: Tue Nov 25, 2008 6:54 pm
Location: Las Vegas

Postby divestoclimb » Thu Nov 19, 2009 3:04 am

There's a much easier way. Just add the code to your SQLiteOpenHelper class.

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.     private static class DatabaseHelper extends SQLiteOpenHelper {
  2.  
  3.  
  4.  
  5.         DatabaseHelper(Context context) {
  6.  
  7.             super(context, DATABASE_NAME, null, DATABASE_VERSION);
  8.  
  9.         }
  10.  
  11.  
  12.  
  13.         @Override
  14.  
  15.         public void onCreate(SQLiteDatabase db) {
  16.  
  17.  
  18.  
  19.             db.execSQL(DATABASE_CREATE);
  20.  
  21.             // Add db.insert() statements here
  22.  
  23.         }
  24.  
  25.     }
Parsed in 0.036 seconds, using GeSHi 1.0.8.4

The best way I've found is to build static methods in your DbAdapter to insert data, then call those.
divestoclimb
Developer
Developer
 
Posts: 33
Joined: Mon May 11, 2009 7:46 pm

Postby iaindownie » Thu Dec 31, 2009 1:26 pm

Hi,

I've picked this topic up because I had the same problem as the original poster - how to prepopulate a static database so that you aren't reliant on XML or Java for holding the data.

I had independently worked out the same solution as above, check to see if data in DB, if not add it (so it only happens once).

However, this seems like a huge waste of resources/phone memory - you effectively have to have all the data in the Java, and then in the database too after the first time application launched.

Has anyone else worked out a better solution to this?

The SQLITE database is great for the to-do and notepad tutorials where people are creating the content themselves, but what about people who are creating more information based or reference applications for the phone?

Thanks
Iain
User avatar
iaindownie
Experienced Developer
Experienced Developer
 
Posts: 84
Joined: Mon Dec 29, 2008 4:08 pm
Location: Thetford, Norfolk, UK

Top
Next

Return to Networking & Database Problems

Who is online

Users browsing this forum: No registered users and 5 guests