problem with read data from SQLite database

Bruce.lntu · by **Bruce.lntu** » Thu Nov 06, 2008 1:46 pm

Hi all,

when I code a ListActivity to list 3 rows with reading data from the database, it showed me an error saying: with an unexpected result. The application can't run OK.

with the code as below:

Syntax: [ Download ] [ Hide ]

Using java Syntax Highlighting

package cn.com.child;
 
import android.content.ContentProvider;
 
import android.content.ContentValues;
 
import android.content.Context;
 
import android.database.Cursor;
 
import android.database.SQLException;
 
import android.database.sqlite.SQLiteDatabase;
 
import android.database.sqlite.SQLiteOpenHelper;
 
import android.net.Uri;
 
import android.util.Log;
 
public class DataProvider extends ContentProvider {
 
        private static final String DATABASE_NAME="bus.db";
 
        private static final int DATABASE_VERSION=2;
 
        private static final String STATION_TABLE_NAME="station";
 
        private static class DatabaseHelper extends SQLiteOpenHelper{                   
 
                 DatabaseHelper(Context context) {
 
                        super(context, DATABASE_NAME, null, DATABASE_VERSION);
 
                        // TODO Auto-generated constructor stub
 
                }       
 
                @Override
 
                public void onCreate(SQLiteDatabase db) {
 
                        // TODO Auto-generated method stub
 
                        db.execSQL("CREATE TABLE"+STATION_TABLE_NAME+"("+"s_id"+"TEXT,"+"s_name"+"TEXT"+");");
 
                        String sql_1="insert into"+STATION_TABLE_NAME+"(s_id,s_name)values('1','hcz');";
 
                        String sql_2="insert into"+STATION_TABLE_NAME+"(s_id,s_name)values('2','lsdx');";
 
                        String sql_3="insert into"+STATION_TABLE_NAME+"(s_id,s_name)values('3','xal');";
 
                        try {
 
                                db.execSQL(sql_1);
 
                                db.execSQL(sql_2);
 
                                db.execSQL(sql_3);
 
                        } catch (SQLException e) {
 
                                Log.e("ERROR",e.toString());
 
                        }
 
                }
 
                @Override
 
                public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 
                        // TODO Auto-generated method stub                      
 
                }               
 
        }
 
        private DatabaseHelper mOpenHelper;
 
        @Override
 
        public int delete(Uri uri, String selection, String[] selectionArgs) {
 
                // TODO Auto-generated method stub
 
                return 0;
 
        }
 
        @Override
 
        public String getType(Uri uri) {
 
                // TODO Auto-generated method stub      
 
                return null;
 
        }
 
        @Override
 
        public Uri insert(Uri uri, ContentValues values) {
 
                // TODO Auto-generated method stub
 
                return uri;
 
        }
 
        @Override
 
        public boolean onCreate() {
 
                // TODO Auto-generated method stub
 
                mOpenHelper = new DatabaseHelper(getContext());
 
        return true;
 
        }
 
        @Override
 
        public Cursor query(Uri uri, String[] projection, String selection,
 
                        String[] selectionArgs, String sortOrder) {             
 
                // Get the database and run the query
 
                SQLiteDatabase db = mOpenHelper.getReadableDatabase();
 
                Cursor c = db.query(STATION_TABLE_NAME,projection, null,null,null,null,null);
 
                return c;
 
        }
 
        @Override
 
        public int update(Uri uri, ContentValues values, String selection,
 
                        String[] selectionArgs) {
 
                // TODO Auto-generated method stub
 
                return 0;        
 
        }
 
}
 
Parsed in 0.059 seconds,  using GeSHi 1.0.8.4

and the data ListActivity

Syntax: [ Download ] [ Hide ]

Using java Syntax Highlighting

package cn.com.child;
 
import java.util.ArrayList;
 
import java.util.HashMap;
 
import java.util.Map;
 
import android.app.ListActivity;
 
import android.content.Intent;
 
import android.database.Cursor;
 
import android.net.Uri;
 
import android.os.Bundle;
 
import android.view.View;
 
import android.widget.ListView;
 
import android.widget.SimpleAdapter;
 
public class data extends ListActivity {
 
        public static final String AUTHORITY="cn.com.provider.child";
 
        public static final Uri CONTENT_URI=Uri.parse("content://"+AUTHORITY+"/station");
 
        private static final String [] PROJECTION=new String []{
 
                "s_id","s_name"};
 
        @Override
 
        protected void onCreate(Bundle savedInstanceState) {
 
                // TODO Auto-generated method stub
 
                super.onCreate(savedInstanceState);
 
//Return the intent that started this activity.
 
                Intent intent=getIntent();
 
                if(intent.getData()==null)
 
                        intent.setData(CONTENT_URI);
 
                Cursor cur=getContentResolver().query(getIntent().getData(),PROJECTION,null,null,null); 
 
                ArrayList<Map<String,Object>> coll =new ArrayList<Map<String,Object>>();
 
                Map<String,Object>item;
 
                cur.moveToFirst();
 
                while(!cur.isAfterLast()){
 
                        item=new HashMap<String, Object>();
 
                        item.put("c1",cur.getString(0)+","+cur.getString(1));
 
                        coll.add(item);
 
                        cur.moveToNext();
 
                }
 
                this.setListAdapter(new SimpleAdapter(this,coll,android.R.layout.simple_list_item_1,new String[]{"c1"},new int[]{
 
                                android.R.id.text1}));                  
 
        }
 
        @Override                               
 
        protected void onListItemClick(ListView l, View v, int position, long id) {
 
                // TODO Auto-generated method stub
 
//              super.onListItemClick(l, v, position, id);
 
                finish();       
 
        }
 
}
 
Parsed in 0.039 seconds,  using GeSHi 1.0.8.4

with the XML;

Syntax: [ Download ] [ Hide ]

Using xml Syntax Highlighting

<?xml version="1.0" encoding="utf-8"?>
 
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
 
        package="cn.com.child" android:versionCode="1"
 
        android:versionName="1.0.0">
 
        <application android:icon="@drawable/icon"
 
                android:label="@string/app_name">
 
                <activity android:name=".data"
 
                        android:label="@string/app_name">
 
                        <intent-filter>
 
                                <action android:name="android.intent.action.MAIN" />
 
                                <category
 
                                        android:name="android.intent.category.LAUNCHER" />
 
                        </intent-filter>
 
                </activity>
 
                <provider android:name="DataProvider"
 
                        android:authorities="cn.com.provider.child">
 
                </provider>
 
        </application>
 
</manifest>
 
Parsed in 0.003 seconds,  using GeSHi 1.0.8.4

someone tell me where is the error. I also have read the database tutorials written by plusminus months before, but still can't find the error. please someone help

rmanromga · by **rmanromga** » Thu Nov 06, 2008 5:08 pm

Hello Bruce,

Have you tried using the debugger? This will help you narrow down where or what statement on your code is causing the error..then you can post it back on the forum... without doing that, you kind of force someone to try and run your whole code/project to help you which obviously is not feasible... unfortunately when an Android program crashes, the message shown on the screen "the program has quit with unexpected results" does not help much. I suggest using the debugger and looking at the call stacks and variables, and you will probably be able to narrow down what part of your code is throwing an exception....

MrSnowflake · by **MrSnowflake** » Thu Nov 06, 2008 5:16 pm

Just take a look at your Log, that will make it a lot more clear.

rmanromga · by **rmanromga** » Thu Nov 06, 2008 5:23 pm

Also, I just noticed a couple of things right off the bat that are problematic with your SQL create/insert strings...

Syntax: [ Download ] [ Hide ]

Using java Syntax Highlighting

package cn.com.child;
 
                @Override
 
                public void onCreate(SQLiteDatabase db) {
 
                        // TODO Auto-generated method stub
 
                        db.execSQL("CREATE TABLE"+STATION_TABLE_NAME+"("+"s_id"+"TEXT,"+"s_name"+"TEXT"+");");
 
                        String sql_1="insert into" <img src="http://www.anddev.org/images/smilies/yikes.png" alt=":o" title="Surprised" /> +STATION_TABLE_NAME+"(s_id,s_name)values('1','hcz');";
 
                        String sql_2="insert into" <img src="http://www.anddev.org/images/smilies/yikes.png" alt=":o" title="Surprised" /> +STATION_TABLE_NAME+"(s_id,s_name)values('2','lsdx');";
 
                        String sql_3="insert into" <img src="http://www.anddev.org/images/smilies/yikes.png" alt=":o" title="Surprised" /> +STATION_TABLE_NAME+"(s_id,s_name)values('3','xal');";
 
                        try {
 
                                db.execSQL(sql_1);
 
                                db.execSQL(sql_2);
 
                                db.execSQL(sql_3);
 
                        } catch (SQLException e) {
 
                                Log.e("ERROR",e.toString());
 
                        }
 
                }
 
Parsed in 0.038 seconds,  using GeSHi 1.0.8.4

You need to be careful when concatenating your strings for example you have:

Syntax: [ Download ] [ Hide ]

Using java Syntax Highlighting

 
String sql_1 = "insert into" + STATION_TABLE_NAME +"(s_id,s_name)values('1', 'hcz');";
Parsed in 0.035 seconds,  using GeSHi 1.0.8.4

This line should be:

Syntax: [ Download ] [ Hide ]

Using java Syntax Highlighting

String sql_1 = "insert into " + STATION_TABLE_NAME + " (s_id, s_name) values('1', 'hcz');";
 
Parsed in 0.035 seconds,  using GeSHi 1.0.8.4

Note the space that i have after the "into " and the space I have before the " (s_id"...
These are the things I mentioned above, that the debugger would help you catch because if you looked at the variable sql_1 in the debugger while the program is running, the sql statement would not make sense and thus throw an exception... remember the debugger IS YOUR FRIEND

Bruce.lntu · by **Bruce.lntu** » Fri Nov 07, 2008 2:25 am

MrSnowflake wrote:Just take a look at your Log, that will make it a lot more clear.

Hi, Snow

when I run the debugger, it shows me the log, I found the error "Failure 1 (near "TABLEstation": syntax error) on 0x7c310 when preparing 'CREATE TABLEstation(s_idTEXTs_nameTEXT);'.",then I check out and discover the error ,and then it works well.

really thank you.

Regards
Bruce

Bruce.lntu · by **Bruce.lntu** » Fri Nov 07, 2008 2:29 am

rmanromga wrote:Hello Bruce,

Have you tried using the debugger? This will help you narrow down where or what statement on your code is causing the error..then you can post it back on the forum... without doing that, you kind of force someone to try and run your whole code/project to help you which obviously is not feasible... unfortunately when an Android program crashes, the message shown on the screen "the program has quit with unexpected results" does not help much. I suggest using the debugger and looking at the call stacks and variables, and you will probably be able to narrow down what part of your code is throwing an exception....

Hi rmanromga,

it's the time really thank you for mentioning me code and debug wieh debugger, this really help me a lot, better teach than give. thank you once more time.

Regards
Bruce

yasmin · by **yasmin** » Tue Nov 18, 2008 5:17 am

Bruce.lntu wrote:
rmanromga wrote:Hello Bruce,

Have you tried using the debugger? This will help you narrow down where or what statement on your code is causing the error..then you can post it back on the forum... without doing that, you kind of force someone to try and run your whole code/project to help you which obviously is not feasible... unfortunately when an Android program crashes, the message shown on the screen "the program has quit with unexpected results" does not help much. I suggest using the debugger and looking at the call stacks and variables, and you will probably be able to narrow down what part of your code is throwing an exception....

Hi rmanromga,

it's the time really thank you for mentioning me code and debug wieh debugger, this really help me a lot, better teach than give. thank you once more time.

Regards
Bruce

Hi Bruce,

Nice to meet you here. I'm learning android. I want to know about Content Provider.

I think your entire project code will help me to know about the COntent Provider..

So plz... post ur entire source code to my mail id..

yasminussain@gmail.com

Thanks,
Yasmin

Bruce.lntu · by **Bruce.lntu** » Tue Nov 18, 2008 8:14 am

Hi Yasmin,
The code is just above, pay attention to the "space" .

Syntax: [ Download ] [ Hide ]

Using java Syntax Highlighting

 db.execSQL("CREATE TABLE "+STATION_TABLE_NAME+"(" + " s_id " + " TEXT, " + " s_name " + " TEXT " + ");"); 
 
               String sql_1="insert into "+STATION_TABLE_NAME+"(s_id,s_name)values('1','hcz');";
Parsed in 0.036 seconds,  using GeSHi 1.0.8.4

wish it can help you

Bruce

yasmin · by **yasmin** » Tue Nov 18, 2008 12:59 pm

Bruce.lntu wrote:Hi Yasmin,
The code is just above, pay attention to the "space" .
Syntax: [ Download ] [ Hide ]
Syntax: [ Download ] [ Show ]
Using java Syntax Highlighting
db.execSQL("CREATE TABLE "+STATION_TABLE_NAME+"(" + " s_id " + " TEXT, " + " s_name " + " TEXT " + ");");

String sql_1="insert into "+STATION_TABLE_NAME+"(s_id,s_name)values('1','hcz');";

Parsed in 0.036 seconds, using GeSHi 1.0.8.4

wish it can help you

Bruce

Hi,

I got error "Source not found"

In the following line " Cursor cur = managedQuery(uri,PROJECTION, null,null, null); " within Data.java

I've included the Data.java & DataProvider.java under the package "com.aspire.android.dataprovider".

So, what I need to include in "AUTHORITY".

I didn't change anything except the package in your code.

Please help..or send ur application as zip file.

Thanks for your time.

Thanks Again!!!
Yasmin

yasmin · by **yasmin** » Tue Nov 18, 2008 1:27 pm

yasmin wrote:
Bruce.lntu wrote:Hi Yasmin,
The code is just above, pay attention to the "space" .
Syntax: [ Download ] [ Hide ]
Syntax: [ Download ] [ Show ]
Using java Syntax Highlighting
db.execSQL("CREATE TABLE "+STATION_TABLE_NAME+"(" + " s_id " + " TEXT, " + " s_name " + " TEXT " + ");");

String sql_1="insert into "+STATION_TABLE_NAME+"(s_id,s_name)values('1','hcz');";

Parsed in 0.037 seconds, using GeSHi 1.0.8.4

wish it can help you

Bruce

Hi,

I got error "Source not found"

In the following line " Cursor cur = managedQuery(uri,PROJECTION, null,null, null); " within Data.java

I've included the Data.java & DataProvider.java under the package "com.aspire.android.dataprovider".

So, what I need to include in "AUTHORITY".

I didn't change anything except the package in your code.

Please help..or send ur application as zip file.

Thanks for your time.

Thanks Again!!!
Yasmin

Hi Bruce,

Reason for my error is cur = null;

I hope I'm mentioning the AUTHORITY worngly. What AUTHORITY refers....

How can I check the table "station" was created & 3 records are inserted?

If I know this then I can solve the issue easily.

Thanks for your time.

Bruce.lntu · by **Bruce.lntu** » Wed Nov 19, 2008 10:46 am

Hi,

just have a look this page,it may help you

http://www.anddev.org/tinytut_-_pull-pu ... -t113.html

or you can check it by shell commands.

Bruce

problem with read data from SQLite database

problem with read data from SQLite database

Re: problem with read data from SQLite database

Hi..Can U plz.... post your entire executable application

Thanks for Ur Reply...

Re: Thanks for Ur Reply...

Who is online