Working with the SQLite-Database - Cursors

Rashi · by **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.

20kr10 · by **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,

Uradox · by **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()

luthepa1 · by **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

 
package com.drp.dbexample;
 
//import java.io.FileNotFoundException;
 
import java.util.ArrayList;
 
import android.app.ListActivity;
 
import android.database.Cursor;
 
import android.database.sqlite.SQLiteDatabase;
 
import android.os.Bundle;
 
import android.widget.ArrayAdapter;
 
import android.widget.EditText;
 
public class mainDBExample extends ListActivity {
 
        private final String MY_DATABASE_NAME = "db_DrP";
 
        private final String MY_DATABASE_TABLE = "Users";
 
        /** Called when the activity is first created. */
 
        @Override
 
        public void onCreate(Bundle icicle) {
 
                super.onCreate(icicle);
 
                EditText et = new EditText(this);
 
                et.setSelection(et.getText().length());
 
                /* Will hold the 'Output' we want to display at the end. */
 
                ArrayList<String> results = new ArrayList<String>();
 
                SQLiteDatabase myDB = null;
 
                try {
 
                        /* Create the Database (no Errors if it already exists) */ 
 
                        /* Open the DB and remember it */
 
                        myDB = this.openOrCreateDatabase(MY_DATABASE_NAME, MODE_PRIVATE, null); 
 
                        /* Create a Table in the Database. */ 
 
                        myDB.execSQL("CREATE TABLE IF NOT EXISTS "
 
                                                        + MY_DATABASE_TABLE
 
                                                        + " (LastName VARCHAR, FirstName VARCHAR,"
 
                                                        + " Country VARCHAR, Age INT(3));");
 
                        /* Add two DataSets to the Table. */
 
                        myDB.execSQL("INSERT INTO "
 
                                                        + MY_DATABASE_TABLE
 
                                                        + " (LastName, FirstName, Country, Age)"
 
                                                        + " VALUES ('Gramlich', 'Nicolas', 'Germany', 20);");
 
                        myDB.execSQL("INSERT INTO "
 
                                                        + MY_DATABASE_TABLE
 
                                                        + " (LastName, FirstName, Country, Age)"
 
                                                        + " VALUES ('Doe', 'John', 'US', 34);");
 
                        /* Query for some results with Selection and Projection. */
 
                        Cursor c = myDB.rawQuery("SELECT FirstName,Age" +
 
                                                                " FROM " + MY_DATABASE_TABLE 
 
                                                                + " WHERE Age > 10 LIMIT 7;",
 
                                                                null);
 
                        /* Get the indices of the Columns we will need */
 
                        int firstNameColumn = c.getColumnIndex("FirstName");
 
                        int ageColumn = c.getColumnIndex("Age");
 
                        /* Check if our result was valid. */
 
                        if (c != null) {
 
                                /* Check if at least one Result was returned. */ 
 
                                if (c.isFirst()) {
 
                                        int i = 0;
 
                                        /* Loop through all Results */ 
 
                                        do {
 
                                                i++;
 
                                                /* Retrieve the values of the Entry
 
                                                 * the Cursor is pointing to. */
 
                                                String firstName = c.getString(firstNameColumn);
 
                                                int age = c.getInt(ageColumn);
 
                                                /* We can also receive the Name 
 
                                                 * of a Column by its Index.
 
                                                 * Makes no sense, as we already 
 
                                                 * know the Name, but just to show we can <img src="http://www.anddev.org/images/smilies/wink.png" alt=";)" title="Wink" /> */
 
                                                String ageColumName = c.getColumnName(ageColumn);
 
                                                /* Add current Entry to results. */
 
                                                results.add("" + i + ": " + firstName 
 
                                                                        + " (" + ageColumName + ": " + age + ")");
 
                                        } while (c.moveToNext());
 
                                }
 
                        }
 
                } finally {
 
                        if (myDB != null)
 
                                myDB.close();
 
                }
 
                this.setListAdapter(new ArrayAdapter<String>(this,
 
                                android.R.layout.simple_list_item_1, results));
 
        }
 
}
 
Parsed in 0.031 seconds,  using GeSHi 1.0.8.4

Syntax: [ Download ] [ Hide ]

Using xml Syntax Highlighting

 
<?xml version="1.0" encoding="utf-8"?>
 
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
 
        android:orientation="vertical" android:layout_width="fill_parent"
 
        android:layout_height="fill_parent">
 
        <TextView android:id="@+id/TextView01" 
 
                        android:layout_height="wrap_content"
 
                        android:layout_width="fill_parent" 
 
                        android:text="Hello DrP, Database Example" />
 
</LinearLayout>
 
<?xml version="1.0" encoding="utf-8"?>
 
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
 
      package="com.drp.dbexample"
 
      android:versionCode="1"
 
      android:versionName="1.0">
 
    <application android:icon="@drawable/icon" android:label="@string/app_name">
 
        <activity android:name=".mainDBExample"
 
                  android:label="@string/app_name">
 
            <intent-filter>
 
                <action android:name="android.intent.action.MAIN" />
 
                <category android:name="android.intent.category.LAUNCHER" />
 
            </intent-filter>
 
        </activity>
 
    </application>
 
    <uses-sdk android:minSdkVersion="4" />
 
</manifest> 
 
Parsed in 0.004 seconds,  using GeSHi 1.0.8.4

luthepa1 · by **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?).

nmc · by **nmc** » Thu Oct 15, 2009 10:06 am

First,

if (c.isFirst()) {
...

should be

Syntax: [ Download ] [ Hide ]

Using java Syntax Highlighting

 
if (c.isBeforeFirst()) {
 
    c.moveToFirst();
 
    ...
 
Parsed in 0.012 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 :-)

luthepa1 · by **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?

nmc · by **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.

nickteojl · by **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?

luthepa1 · by **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.

Syntax: [ Download ] [ Hide ]

Using java Syntax Highlighting

 
                SQLiteDatabase myDB = null;
 
                try {
 
                        /* Create the Database (no Errors if it already exists) */ 
 
                        /* Open the DB and remember it */
 
                        myDB = this.openOrCreateDatabase(MY_DATABASE_NAME, MODE_PRIVATE, null); 
 
                        /* Create a Table in the Database. */ 
 
                        myDB.execSQL("CREATE TABLE IF NOT EXISTS "
 
                                                        + MY_DATABASE_TABLE
 
                                                        + " (LastName VARCHAR, FirstName VARCHAR,"
 
                                                        + " Country VARCHAR, Age INT(3));");
 
Parsed in 0.011 seconds,  using GeSHi 1.0.8.4

nmc · by **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

nickteojl · by **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?

nmc · by **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.

nickteojl · by **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

myDB.execSQL("CREATE TABLE IF NOT EXISTS "
 
                                                        + MY_DATABASE_TABLE
 
                                                        + " (food_id VARCHAR, food_name VARCHAR,"
 
                                                        + " price VARCHAR);");
Parsed in 0.011 seconds,  using GeSHi 1.0.8.4

but when i try

Syntax: [ Download ] [ Hide ]

Using java Syntax Highlighting

myDB.execSQL("INSERT INTO "
 
                                                        + MY_DATABASE_TABLE
 
                                                        + " (food_id, food_name, price)"
 
                                                        + " VALUES ('001', 'beef', '12');");
Parsed in 0.011 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!

BharathMohan · by **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........

Working with the SQLite-Database - Cursors

New classes in sdk1.5

cant see the listview output?

Who is online