Working with the SQLite-Database - Cursors

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

Postby szeldon » Sun May 18, 2008 11:04 pm

I'm not completly sure, but I think that you should close your cursor after using it.
szeldon.com
szeldon
Freshman
Freshman
 
Posts: 5
Joined: Thu Feb 14, 2008 7:53 pm

Top

Postby nithin.warier » Wed May 28, 2008 1:58 pm

Hi plusminus,

how we can get the maximum value of an attribute in a table, in terminal , i checked and its working,

the query is -> select max(key) from Testing;

but how we can do this in Android.

please reply immediately

Thanks
Nithin
nithin.warier
Experienced Developer
Experienced Developer
 
Posts: 87
Joined: Thu Feb 28, 2008 12:05 pm
Location: Malappuram Kerala India

Postby nithin.warier » Wed May 28, 2008 2:59 pm

Here, the return value is an integer, but for Sqlitedatabase.query() its returning cursor, so how can we get the integer value.

thanks
Nithin
nithin.warier
Experienced Developer
Experienced Developer
 
Posts: 87
Joined: Thu Feb 28, 2008 12:05 pm
Location: Malappuram Kerala India

Null Pointer Exception while creating table

Postby Niketa » Mon Sep 29, 2008 12:54 pm

hi,
I have implement this example. but i got an error.

I have write the code just for create database, create table, insert values in it.

When I run application it displays nothing.

But when i tried to open my database from command prompt Like:

ls data/data/MyPackageNm

Then it gives only a Lib folder rather than database and Lib both.

So, i tried to found error via Log cat and put my code in try and catch.

I got an error while creating table.

its gave Null Pointer exception.

Please any one help me.

My Log cat is like that and i have use this sample example as my code.

Thanks.

Code: Select all
uid=10032 gids={}
09-29 15:36:09.727: DEBUG/dalvikvm(813): LinearAlloc 0x0 used 538516 of 4194304 (12%)
09-29 15:36:09.837: INFO/jdwp(822): received file descriptor 10 from ADB
09-29 15:36:10.128: INFO/Niketa(822): database created
09-29 15:36:10.137: INFO/Niketa(822): database opened
09-29 15:36:10.137: INFO/Niketa(822): ------null
09-29 15:36:10.147: INFO/Niketa(822): ++++++java.lang.NullPointerException
09-29 15:36:10.337: INFO/ActivityManager(52): Displayed activity com.db/.DataBaseWork: 754 ms
09-29 15:36:15.507: DEBUG/dalvikvm(675): GC freed 44 objects / 1800 bytes in 151ms
09-29 15:36:15.507: DEBUG/dalvikvm(118): GC freed 3 objects / 72 bytes in 143ms
09-29 15:36:15.527: DEBUG/dalvikvm(92): GC freed 1539 objects / 70560 bytes in


My java code is:
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.  
  2. package com.db;
  3.  
  4.  
  5.  
  6. //import java.io.FileNotFoundException;
  7.  
  8. import java.util.ArrayList;
  9.  
  10.  
  11.  
  12. import android.app.Activity;
  13.  
  14. import android.database.Cursor;
  15.  
  16. import android.database.SQLException;
  17.  
  18. import android.database.sqlite.SQLiteDatabase;
  19.  
  20. import android.database.sqlite.SQLiteException;
  21.  
  22. import android.os.Bundle;
  23.  
  24. import android.util.Log;
  25.  
  26. import android.widget.ArrayAdapter;
  27.  
  28.  
  29.  
  30. public class DataBaseWork extends Activity {
  31.  
  32.         private final String MY_DATABASE_NAME = "LoginDB";
  33.  
  34.     private final String MY_DATABASE_TABLE = "Login1";
  35.  
  36.     private final String tag="Niketa";
  37.  
  38.  
  39.  
  40.  
  41.  
  42.     @Override
  43.  
  44.     public void onCreate(Bundle icicle) {
  45.  
  46.          super.onCreate(icicle);
  47.  
  48.  
  49.  
  50.          ArrayList<String> results = new ArrayList<String>();
  51.  
  52.          SQLiteDatabase myDB = null;
  53.  
  54.          try {
  55.  
  56.                
  57.  
  58.               /* Create the Database (no Errors if it already exists) */
  59.  
  60.               this.createDatabase(MY_DATABASE_NAME, 1, MODE_PRIVATE, null);
  61.  
  62.               Log.i(tag,"database created");
  63.  
  64.  
  65.  
  66.               /* Open the DB and remember it */
  67.  
  68.                
  69.  
  70.               myDB = this.openDatabase(MY_DATABASE_NAME, null);
  71.  
  72.               Log.i(tag,"database opened ");
  73.  
  74.                
  75.  
  76.               /* Create a Table in the Database. */
  77.  
  78.               try
  79.  
  80.               {
  81.  
  82.               myDB.execSQL("CREATE TABLE IF NOT EXISTS "
  83.  
  84.                                   + "LoginDB.Login1"
  85.  
  86.                                   + " (UserNm TEXT, PassWd TEXT)"
  87.  
  88.                                   +";");
  89.  
  90.               Log.i(tag,"table created ");
  91.  
  92.               }
  93.  
  94.           /*    catch(SQLException e1)
  95.  
  96.               {
  97.  
  98.                   Log.i(tag,"SQL EXception"+e1);
  99.  
  100.               }*/
  101.  
  102.              
  103.  
  104.              
  105.  
  106.               catch(Exception e)
  107.  
  108.               {
  109.  
  110.                   Log.i(tag,"------"+e.getMessage());
  111.  
  112.                   Log.i(tag,"++++++"+e);
  113.  
  114.               }
  115.  
  116.              
  117.  
  118.             /*  catch(SQLiteException se)
  119.  
  120.               {
  121.  
  122.                   Log.i(tag,"SQLite Error--------"+se);
  123.  
  124.               }*/
  125.  
  126.               /* Add two DataSets to the Table. */
  127.  
  128.               myDB.execSQL("INSERT INTO "
  129.  
  130.                                   + MY_DATABASE_TABLE
  131.  
  132.                                   + " (UserNm, PassWd)"
  133.  
  134.                                   + " VALUES ('abc', 'def');");
  135.  
  136.               Log.i(tag,"value inserted ");
  137.  
  138.              
  139.  
  140.              
  141.  
  142.               /* Query for some results with Selection and Projection. */
  143.  
  144.               Cursor c = myDB.rawQuery("SELECT UserNm,PassWd" +
  145.  
  146.                                        " FROM " + MY_DATABASE_TABLE
  147.  
  148.                                        + ";",
  149.  
  150.                                        null);
  151.  
  152.               Log.i(tag,"value seleted ");
  153.  
  154.              
  155.  
  156.               /* Get the indices of the Columns we will need */
  157.  
  158.               int firstNameColumn = c.getColumnIndex("UserNm");
  159.  
  160.               int ageColumn = c.getColumnIndex("PassWd");
  161.  
  162.              
  163.  
  164.               /* Check if our result was valid. */
  165.  
  166.               if (c != null) {
  167.  
  168.                    /* Check if at least one Result was returned. */
  169.  
  170.                   if (c.isFirst()) {
  171.  
  172.                         int i = 0;
  173.  
  174.                      
  175.  
  176.                         do {
  177.  
  178.                              i++;
  179.  
  180.                      
  181.  
  182.                              String firstName = c.getString(firstNameColumn);
  183.  
  184.                              int age = c.getInt(ageColumn);
  185.  
  186.                              String ageColumName = c.getColumnName(ageColumn);
  187.  
  188.                              
  189.  
  190.                              results.add("" + i + ": " + firstName
  191.  
  192.                                             + " (" + ageColumName + ": " + age + ")");
  193.  
  194.                         } while (c!=null);
  195.  
  196.                    }
  197.  
  198.               }
  199.  
  200.          }
  201.  
  202.          catch (Exception e)
  203.  
  204.          {
  205.  
  206.                  Log.i(tag,"Error----->"+e);
  207.  
  208.          }
  209.  
  210.          finally
  211.  
  212.          {
  213.  
  214.               if (myDB != null)
  215.  
  216.                    myDB.close();
  217.  
  218.          }
  219.  
  220.          this.setListAdapter(new ArrayAdapter<String>(this,
  221.  
  222.                  android.R.layout.simple_list_item_1, results));
  223.  
  224.   }
  225.  
  226.  
  227.  
  228.  
  229.  
  230.         private void setListAdapter(ArrayAdapter<String> arrayAdapter)
  231.  
  232.         {
  233.  
  234.         }
  235.  
  236.  
  237.  
  238.         private SQLiteDatabase openDatabase(String my_database_name2, Object object)
  239.  
  240.         {
  241.  
  242.                 return null;
  243.  
  244.         }
  245.  
  246.  
  247.  
  248.         private void createDatabase(String my_database_name2, int i,
  249.  
  250.                         int modePrivate, Object object)
  251.  
  252.         {
  253.  
  254.        
  255.  
  256.                
  257.  
  258.         }
  259.  
  260. }
  261.  
  262.  
Parsed in 0.050 seconds, using GeSHi 1.0.8.4
Niketa
Developer
Developer
 
Posts: 36
Joined: Wed Sep 17, 2008 12:37 pm

Postby nithin.warier » Mon Sep 29, 2008 1:12 pm

hi,

try c.moveToFirst();
before if(c != null) and try

Thanks
nithin
nithin.warier
Experienced Developer
Experienced Developer
 
Posts: 87
Joined: Thu Feb 28, 2008 12:05 pm
Location: Malappuram Kerala India

its still not working

Postby Niketa » Mon Sep 29, 2008 1:45 pm

hi,

Thanks for the help.

But its still not working.
Niketa
Developer
Developer
 
Posts: 36
Joined: Wed Sep 17, 2008 12:37 pm

Top

Postby Rahul » Wed Oct 15, 2008 7:28 am

When i try running this code

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


AndroidManifest.xml
Syntax: [ Download ] [ Hide ]
Using xml Syntax Highlighting
  1.  
  2. <?xml version="1.0" encoding="utf-8"?>
  3.  
  4. <manifest xmlns:android="http://schemas.android.com/apk/res/android"
  5.  
  6.    package="tp.proj.dat">
  7.  
  8.     <application android:icon="@drawable/icon">
  9.  
  10.         <activity android:name=".SQLite" android:label="@string/app_name">
  11.  
  12.             <intent-filter>
  13.  
  14.                 <action android:name="android.intent.action.MAIN" />
  15.  
  16.                 <category android:name="android.intent.category.LAUNCHER" />
  17.  
  18.             </intent-filter>
  19.  
  20.         </activity>
  21.  
  22.     </application>
  23.  
  24. </manifest>
Parsed in 0.002 seconds, using GeSHi 1.0.8.4


i get the following error
application org.andev.android.databasework(Processorg.anddev.android.databasework)has stopped unexpectedly.Please try again


Please help me..
Rahul
Experienced Developer
Experienced Developer
 
Posts: 89
Joined: Wed Oct 01, 2008 11:16 am

Postby bino » Wed Oct 15, 2008 1:52 pm

To Rahul, you should change some places in your code:

Replace:
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.       this.createDatabase(MY_DATABASE_NAME, 1, MODE_PRIVATE, null);
  2.  
  3.       myDB = this.openDatabase(MY_DATABASE_NAME, null);
Parsed in 0.034 seconds, using GeSHi 1.0.8.4

By:
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.       myDB = this.openOrCreateDatabase(MY_DATABASE_NAME, MODE_PRIVATE, null);
Parsed in 0.035 seconds, using GeSHi 1.0.8.4

And, of course, we don't need the two methods (just simply delete them):
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.       private SQLiteDatabase openDatabase
  2.  
  3.       private void createDatabase
Parsed in 0.036 seconds, using GeSHi 1.0.8.4


Replace:
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.       while(c!=null);
Parsed in 0.036 seconds, using GeSHi 1.0.8.4

By:
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.       while(c.moveToNext());
Parsed in 0.035 seconds, using GeSHi 1.0.8.4


In the myDB.rawQuery(...), I read from the SDK 1.0 that you should not add the ";" at the end of the query.

By the way, I found somethings look strange in your Manifest file, the package name and the activity name are not the same in your class (please check it again).

I am not very good in expressing in English.
User avatar
bino
Freshman
Freshman
 
Posts: 9
Joined: Mon Oct 13, 2008 7:15 am

Postby Rahul » Wed Oct 15, 2008 2:39 pm

@ Bino

Thank you very much...That solved my problem... :D


The query works with and without adding ';' i think the ';' does not make any difference as you said
Rahul
Experienced Developer
Experienced Developer
 
Posts: 89
Joined: Wed Oct 01, 2008 11:16 am

clarification

Postby jcook2004 » Sun Nov 02, 2008 9:04 am

With this method, what happens when the application is closed? Is the database then delete? It seems like on every start the database gets re-created.
Heres my question, what if I insert some information into the database - i then close the application. What happens to that data? When I reopen it, is that inserted data still there?

Does this method re-create the database every time?
I would like to know an efficient way of creating a database one time (@ the install/initial launch of the app), and then having the ability to append data to that database and having it available for retrieval at later dates.

Thanks for the help! you're the man!
jcook2004
Freshman
Freshman
 
Posts: 6
Joined: Sun Nov 02, 2008 8:59 am

Postby dkkundudolan » Fri Dec 26, 2008 6:24 am

Hi bino,

I already changed the options what you had mentioned for the above post.
But still its showing "The application has stopped unexpectedly. Please try again".
I am using sdk v1.0. please can you or anybody can help? waiting for reply.... :!:

Thanks.
dkkundudolan
Experienced Developer
Experienced Developer
 
Posts: 83
Joined: Mon Mar 03, 2008 12:50 pm

Postby dkkundudolan » Fri Dec 26, 2008 8:01 am

Hi All,

I solved the probs.....

Thanks.
dkkundudolan
Experienced Developer
Experienced Developer
 
Posts: 83
Joined: Mon Mar 03, 2008 12:50 pm

Postby roland909 » Sat Jan 03, 2009 5:20 pm

Hi folks,

as this howto seems to be outdated...I wanted to ask if any of you has a few suggestions/tipps to get it working with the actual SDK...? Just to ask in advance...because I'll start learning/working on it this weekend.

Have a nice weekend! :D
roland909
Freshman
Freshman
 
Posts: 2
Joined: Sun Dec 28, 2008 2:37 am
Location: Frankfurt am Main

Postby kali » Mon Feb 16, 2009 1:32 pm

hai friend,
i have dowmload your whole code
but it gives error at
myDB.createDataBase();
&
myDB.openDataBase();
is giving error "the method is undefined"
pls help
thanks in advance,
kali
Experienced Developer
Experienced Developer
 
Posts: 62
Joined: Tue Jan 27, 2009 1:31 pm

error

Postby dhaiwat » Wed Feb 18, 2009 8:07 am

hello +- i write above cod and i create my database also

but i get error at show the record on screen when statement like

this.setListAdapter(new ArrayAdapter<String>(this,
android.R.layout.simple_list_item_1, results));

hope u will get my problem and relpy best...
dhaiwat
Developer
Developer
 
Posts: 28
Joined: Tue Feb 10, 2009 6:49 am

Top
PreviousNext

Return to Novice Tutorials

Who is online

Users browsing this forum: No registered users and 5 guests