Board index » Android Tutorials with Source Code » Novice Tutorials

Skip to content

Working with the SQLite-Database - Cursors

Basic Tutorials concerning: GUI, Views, Activites, XML, Layouts, Intents, ...
Topic locked

Postby Rashi » Sun Sep 13, 2009 1:58 pm

This is code is giving me problems in createDatabase() and openDatabse methods.
Am i supposed to write two functions for it?

Rashi.
Rashi
Developer
Developer
 
Posts: 32
Joined: Thu Sep 10, 2009 11:28 am
Top
Top

New classes in sdk1.5

Postby 20kr10 » Fri Sep 18, 2009 2:49 pm

Does anyone knows the updated class names from sdk 1.0 m5-rc14 to sdk 1.5_r1.
As I am using the sdk 1.5_r1 version, there are lots of errors whenever I try to run the sample codes.

Pls, if anyone knows the site where the one-to-one correspondence is denoted between the older classes and newer classes, do let me know.

Thanks,
20kr10
Once Poster
Once Poster
 
Posts: 1
Joined: Sat Aug 08, 2009 3:50 pm
Location: India
Top

Postby Uradox » Sat Oct 10, 2009 10:25 am

Rashi wrote:This is code is giving me problems in createDatabase() and openDatabse methods.
Am i supposed to write two functions for it?

Rashi.


You may have worked it out by now but those two methods are replaced with openOrCreateDatabase()
Uradox
Once Poster
Once Poster
 
Posts: 1
Joined: Sat Oct 10, 2009 10:24 am
Top

cant see the listview output?

Postby luthepa1 » Thu Oct 15, 2009 12:14 am

Hi all,

I am noob to say the least. Today is the start of say 4 since I first started looking at learning Java/Android and since I first installed the v1.6 Android SDK. But loving it so far!!!

I have edited the code and now have it error free in the eclipse IDE (code below). I run it to start in the emulator and the app starts up and I can see the textview at the top of the app but there is no listview with the results pulled out of the database (names and ages). I must be missing something really small. I am assuming that the list view is created through the code? Or do I need to add a listview to the layout/main.xml??? Can anyone please help me make the final link to getting my app to work please? Love this android development and finding this site is a goldmine!!!

Thanks in advance, Paul.

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.  
  2. package com.drp.dbexample;
  3.  
  4.  
  5.  
  6. //import java.io.FileNotFoundException;
  7.  
  8. import java.util.ArrayList;
  9.  
  10.  
  11.  
  12. import android.app.ListActivity;
  13.  
  14. import android.database.Cursor;
  15.  
  16. import android.database.sqlite.SQLiteDatabase;
  17.  
  18. import android.os.Bundle;
  19.  
  20. import android.widget.ArrayAdapter;
  21.  
  22. import android.widget.EditText;
  23.  
  24.  
  25.  
  26. public class mainDBExample extends ListActivity {
  27.  
  28.  
  29.  
  30.         private final String MY_DATABASE_NAME = "db_DrP";
  31.  
  32.         private final String MY_DATABASE_TABLE = "Users";
  33.  
  34.  
  35.  
  36.         /** Called when the activity is first created. */
  37.  
  38.         @Override
  39.  
  40.         public void onCreate(Bundle icicle) {
  41.  
  42.                 super.onCreate(icicle);
  43.  
  44.                
  45.  
  46.                 EditText et = new EditText(this);
  47.  
  48.                 et.setSelection(et.getText().length());
  49.  
  50.                 /* Will hold the 'Output' we want to display at the end. */
  51.  
  52.                 ArrayList<String> results = new ArrayList<String>();
  53.  
  54.  
  55.  
  56.                 SQLiteDatabase myDB = null;
  57.  
  58.                 try {
  59.  
  60.                         /* Create the Database (no Errors if it already exists) */
  61.  
  62.                         /* Open the DB and remember it */
  63.  
  64.                         myDB = this.openOrCreateDatabase(MY_DATABASE_NAME, MODE_PRIVATE, null);
  65.  
  66.  
  67.  
  68.                         /* Create a Table in the Database. */
  69.  
  70.                         myDB.execSQL("CREATE TABLE IF NOT EXISTS "
  71.  
  72.                                                         + MY_DATABASE_TABLE
  73.  
  74.                                                         + " (LastName VARCHAR, FirstName VARCHAR,"
  75.  
  76.                                                         + " Country VARCHAR, Age INT(3));");
  77.  
  78.  
  79.  
  80.                         /* Add two DataSets to the Table. */
  81.  
  82.                         myDB.execSQL("INSERT INTO "
  83.  
  84.                                                         + MY_DATABASE_TABLE
  85.  
  86.                                                         + " (LastName, FirstName, Country, Age)"
  87.  
  88.                                                         + " VALUES ('Gramlich', 'Nicolas', 'Germany', 20);");
  89.  
  90.                         myDB.execSQL("INSERT INTO "
  91.  
  92.                                                         + MY_DATABASE_TABLE
  93.  
  94.                                                         + " (LastName, FirstName, Country, Age)"
  95.  
  96.                                                         + " VALUES ('Doe', 'John', 'US', 34);");
  97.  
  98.  
  99.  
  100.                         /* Query for some results with Selection and Projection. */
  101.  
  102.                         Cursor c = myDB.rawQuery("SELECT FirstName,Age" +
  103.  
  104.                                                                 " FROM " + MY_DATABASE_TABLE
  105.  
  106.                                                                 + " WHERE Age > 10 LIMIT 7;",
  107.  
  108.                                                                 null);
  109.  
  110.                        
  111.  
  112.                         /* Get the indices of the Columns we will need */
  113.  
  114.                         int firstNameColumn = c.getColumnIndex("FirstName");
  115.  
  116.                         int ageColumn = c.getColumnIndex("Age");
  117.  
  118.                        
  119.  
  120.                         /* Check if our result was valid. */
  121.  
  122.                         if (c != null) {
  123.  
  124.                                 /* Check if at least one Result was returned. */
  125.  
  126.                                 if (c.isFirst()) {
  127.  
  128.                                         int i = 0;
  129.  
  130.                                         /* Loop through all Results */
  131.  
  132.                                         do {
  133.  
  134.                                                 i++;
  135.  
  136.                                                 /* Retrieve the values of the Entry
  137.  
  138.                                                  * the Cursor is pointing to. */
  139.  
  140.                                                 String firstName = c.getString(firstNameColumn);
  141.  
  142.                                                 int age = c.getInt(ageColumn);
  143.  
  144.                                                 /* We can also receive the Name
  145.  
  146.                                                  * of a Column by its Index.
  147.  
  148.                                                  * Makes no sense, as we already
  149.  
  150.                                                  * know the Name, but just to show we can <img src="http://www.anddev.org/images/smilies/wink.png" alt=";)" title="Wink" /> */
  151.  
  152.                                                 String ageColumName = c.getColumnName(ageColumn);
  153.  
  154.                                                
  155.  
  156.                                                 /* Add current Entry to results. */
  157.  
  158.                                                 results.add("" + i + ": " + firstName
  159.  
  160.                                                                         + " (" + ageColumName + ": " + age + ")");
  161.  
  162.                                         } while (c.moveToNext());
  163.  
  164.                                 }
  165.  
  166.                         }
  167.  
  168.  
  169.  
  170.                 } finally {
  171.  
  172.                         if (myDB != null)
  173.  
  174.                                 myDB.close();
  175.  
  176.                 }
  177.  
  178.  
  179.  
  180.                 this.setListAdapter(new ArrayAdapter<String>(this,
  181.  
  182.                                 android.R.layout.simple_list_item_1, results));
  183.  
  184.         }
  185.  
  186. }
  187.  
  188.  
Parsed in 0.015 seconds, using GeSHi 1.0.8.4

Syntax: [ Download ] [ Hide ]
Using xml Syntax Highlighting
  1.  
  2. <?xml version="1.0" encoding="utf-8"?>
  3.  
  4. <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
  5.  
  6.         android:orientation="vertical" android:layout_width="fill_parent"
  7.  
  8.         android:layout_height="fill_parent">
  9.  
  10.  
  11.  
  12.         <TextView android:id="@+id/TextView01"
  13.  
  14.                         android:layout_height="wrap_content"
  15.  
  16.                         android:layout_width="fill_parent"
  17.  
  18.                         android:text="Hello DrP, Database Example" />
  19.  
  20.  
  21.  
  22.  
  23.  
  24. </LinearLayout>
  25.  
  26.  
  27.  
  28.  
  29.  
  30. <?xml version="1.0" encoding="utf-8"?>
  31.  
  32. <manifest xmlns:android="http://schemas.android.com/apk/res/android"
  33.  
  34.      package="com.drp.dbexample"
  35.  
  36.      android:versionCode="1"
  37.  
  38.      android:versionName="1.0">
  39.  
  40.     <application android:icon="@drawable/icon" android:label="@string/app_name">
  41.  
  42.         <activity android:name=".mainDBExample"
  43.  
  44.                  android:label="@string/app_name">
  45.  
  46.             <intent-filter>
  47.  
  48.                 <action android:name="android.intent.action.MAIN" />
  49.  
  50.                 <category android:name="android.intent.category.LAUNCHER" />
  51.  
  52.             </intent-filter>
  53.  
  54.         </activity>
  55.  
  56.  
  57.  
  58.     </application>
  59.  
  60.     <uses-sdk android:minSdkVersion="4" />
  61.  
  62.  
  63.  
  64. </manifest>
  65.  
  66.  
  67.  
  68.  
Parsed in 0.002 seconds, using GeSHi 1.0.8.4
luthepa1
Junior Developer
Junior Developer
 
Posts: 12
Joined: Wed Oct 14, 2009 11:51 pm
Top

Postby luthepa1 » Thu Oct 15, 2009 3:11 am

well found out that my array is not filling. I added a integer count to hold the item count of that array and then display it via Toast,makeText and it shows a item count of 0. So now to review the code to fill array (probably incorrect SQL commands to retrieve data?).
luthepa1
Junior Developer
Junior Developer
 
Posts: 12
Joined: Wed Oct 14, 2009 11:51 pm
Top

Postby nmc » Thu Oct 15, 2009 10:06 am

First,
if (c.isFirst()) {
...


should be
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.  
  2. if (c.isBeforeFirst()) {
  3.  
  4.     c.moveToFirst();
  5.  
  6.     ...
  7.  
  8.  
Parsed in 0.010 seconds, using GeSHi 1.0.8.4


Then, i am not sure that you can get the column-index before the first record is fetched.

Last, your layout is missing a list-view with a certain id ("list", IIRC - i never use listactivity)

Hope that helps getting your code to work :-)
nmc
Senior Developer
Senior Developer
 
Posts: 154
Joined: Thu Nov 27, 2008 8:30 pm
Location: Germany
Top
Top

Postby luthepa1 » Thu Oct 15, 2009 10:47 am

Thanks nmc!!! That change worked! But now it does something strange to me. Its supposed to make 2 entries in the listview. But what its doingis making the first 2 entries then repeating until there is a total of 7 items in the list. I have a lot to learn for sure on SQL. I tried removing the "LIMIT 7" statement from the SQL query and that results in the same output except its now 31 items in the list - WAIT - I just tried loading again and found that with each execution of the app it is appending the database which makes sense because the code adds the 2 entries regardless of whether the db existed or not. So forgot the problem I just mentioned. Its not a problem.

I have another question however. What if I dont want to create the listview at runtime through code but I use the eclipse IDE to drag n drop a listview onto the main.xml layout. How do I then add entries to the list view at runtime? Is there a way I can code something like ListView01.Items.Add("hello world") ?

Thanks.

P.S. Also where do I find the database to manually delete on the system? I believe that android has a private folder or something where I need to go and delete the database?
luthepa1
Junior Developer
Junior Developer
 
Posts: 12
Joined: Wed Oct 14, 2009 11:51 pm
Top

Postby nmc » Thu Oct 15, 2009 7:35 pm

What if I dont want to create the listview at runtime through code but I use the eclipse IDE to drag n drop a listview onto the main.xml layout. How do I then add entries to the list view at runtime?

I dont see you creating a list-view by code ;)
Your code creates a list-adapter and assigns that to the listview maintained by your list-activity, so the list-view itself must be created via .xml


Is there a way I can code something like ListView01.Items.Add("hello world") ?

I think you could make your own list-adapter, providing functions like that.
nmc
Senior Developer
Senior Developer
 
Posts: 154
Joined: Thu Nov 27, 2008 8:30 pm
Location: Germany
Top

Postby nickteojl » Fri Oct 16, 2009 6:35 am

hi guys, im currently doing my final year project in school, and i really need some help regarding databases urgently. i've followed plusminus's guide, but i always get an error at

this.createDatabase(MY_DATABASE_NAME, 1, MODE_PRIVATE, null);
myDB = this.openDatabase(MY_DATABASE_NAME, null);

there will be a red line under createDatabase and openDatabase. can anyone guide me as to how to solve this problem please?
nickteojl
Freshman
Freshman
 
Posts: 3
Joined: Fri Oct 16, 2009 6:31 am
Top

Postby luthepa1 » Fri Oct 16, 2009 9:06 am

Hi nickteojl,

I am just a freshman to android (only started looking at android and java at the start of this week) but I had the same problem as you have. Somewhere in the first couple of pages of this post is the answer to use openOrCreateDatabase instead. Here is just a section of some code from the example here. You remove the createDatabase and openDatabase statements/code work and replace with the one line. As I say I am new to all this so there might be other ways. :D

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.  
  2.                 SQLiteDatabase myDB = null;
  3.  
  4.                 try {
  5.  
  6.                         /* Create the Database (no Errors if it already exists) */
  7.  
  8.                         /* Open the DB and remember it */
  9.  
  10.                         myDB = this.openOrCreateDatabase(MY_DATABASE_NAME, MODE_PRIVATE, null);
  11.  
  12.  
  13.  
  14.                         /* Create a Table in the Database. */
  15.  
  16.                         myDB.execSQL("CREATE TABLE IF NOT EXISTS "
  17.  
  18.                                                         + MY_DATABASE_TABLE
  19.  
  20.                                                         + " (LastName VARCHAR, FirstName VARCHAR,"
  21.  
  22.                                                         + " Country VARCHAR, Age INT(3));");
  23.  
  24.  
Parsed in 0.010 seconds, using GeSHi 1.0.8.4
luthepa1
Junior Developer
Junior Developer
 
Posts: 12
Joined: Wed Oct 14, 2009 11:51 pm
Top

Postby nmc » Fri Oct 16, 2009 10:16 am

As I say I am new to all this so there might be other ways.

The way you do it is completely correct for almost all cases.

@nickteojl: reading the docs always proves as helpfull when running into a problem ;)
SDK-Reference for SQLiteDatabase
nmc
Senior Developer
Senior Developer
 
Posts: 154
Joined: Thu Nov 27, 2008 8:30 pm
Location: Germany
Top

Postby nickteojl » Mon Oct 19, 2009 7:02 am

thanks so much, guys, for your help!

i've changed the openDatabase and createDatabase to openOrCreateDatabase, and it works fine now. however, it seems that i'm unable to insert any data into the table. i can create the table fine, but when i try to insert anything, the application crashes. does anyone have any ideas?
nickteojl
Freshman
Freshman
 
Posts: 3
Joined: Fri Oct 16, 2009 6:31 am
Top

Postby nmc » Mon Oct 19, 2009 9:05 am

i can create the table fine, but when i try to insert anything, the application crashes. does anyone have any ideas?

You could try to add a column "_id integer autoincrement" to you table;
since i always use rawQuery() i am not sure about this but it seems that some of the "higher" functions require this field (at least, CursorAdapters arent working w/o this).
So maybe that solves your problem.
nmc
Senior Developer
Senior Developer
 
Posts: 154
Joined: Thu Nov 27, 2008 8:30 pm
Location: Germany
Top

Postby nickteojl » Wed Oct 21, 2009 5:01 am

nmc wrote:
i can create the table fine, but when i try to insert anything, the application crashes. does anyone have any ideas?

You could try to add a column "_id integer autoincrement" to you table;
since i always use rawQuery() i am not sure about this but it seems that some of the "higher" functions require this field (at least, CursorAdapters arent working w/o this).
So maybe that solves your problem.


i tried adding the column, but the application crashes as well. i am able to execute

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. myDB.execSQL("CREATE TABLE IF NOT EXISTS "
  2.  
  3.                                                         + MY_DATABASE_TABLE
  4.  
  5.                                                         + " (food_id VARCHAR, food_name VARCHAR,"
  6.  
  7.                                                         + " price VARCHAR);");
Parsed in 0.010 seconds, using GeSHi 1.0.8.4


but when i try
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. myDB.execSQL("INSERT INTO "
  2.  
  3.                                                         + MY_DATABASE_TABLE
  4.  
  5.                                                         + " (food_id, food_name, price)"
  6.  
  7.                                                         + " VALUES ('001', 'beef', '12');");
Parsed in 0.010 seconds, using GeSHi 1.0.8.4


the application crashes on runtime.

sorry for the constant stream of questions, but i'm truly new to this and need desperate help. thanks to everyone who can help me!
nickteojl
Freshman
Freshman
 
Posts: 3
Joined: Fri Oct 16, 2009 6:31 am
Top

Postby BharathMohan » Tue Nov 17, 2009 7:47 am

hi can any tell me how to insert an array in a single row column in database table........ :(
BharathMohan
Freshman
Freshman
 
Posts: 3
Joined: Tue Nov 10, 2009 10:52 am
Top
Top
PreviousNext
Topic locked

Return to Novice Tutorials

Who is online

Users browsing this forum: No registered users and 2 guests

Board index The team • Delete all board cookies • All times are UTC + 1 hour