Friday, September 30, 2011

Accessing SQLite Data With Android Cursors


SQLite is an open-source server-less database engine. SQLite supports transacations and has no configuration required. SQLite adds powerful data storage to mobile and embedded apps without a large footprint.

Creating and connecting to a database

First import android.databse.sqlite.SQLiteDatabase into your application. Then use the openOrCreateDatabase() method to create or connect to a database. Create a new project in Eclipse called TestingData and select the API version of your choice. Use the package name higherpass.TestingData with an activity TestingData and click finish.
package higherpass.TestingData;

import android.app.Activity;
import android.os.Bundle;
import android.database.sqlite.SQLiteDatabase; 

public class TestingData extends Activity {
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        
        SQLiteDatabase db;
        db = openOrCreateDatabase(
         "TestingData.db"
         , SQLiteDatabase.CREATE_IF_NECESSARY
         , null
        );
     }
}

Add the android.database.sqlite.SQLiteDatabase to the standard imports from the new project. After the standard layout setup initialize a SQLiteDatabase variable to hold the database instance. Next use the openOrCreateDatabase() method to open the database. The openOrCreateDatabase() method expects the database file first, followed by the permissions to open the database with, and an optional cursor factory builder.

Where does Android store SQLite databases?

Android stores SQLite databases in /data/data/[application package name]/databases.

sqlite3 from adb shell

bash-3.1$ /usr/local/android-sdk-linux/tools/adb devices
List of devices attached 
emulator-5554   device
bash-3.1$ /usr/local/android-sdk-linux/tools/adb -s emulator-5554 shell
# ls /data/data/higherpass.TestingData/databases
TestingData.db
# sqlite3 /data/data/higherpass.TestingData/databases/TestingData.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> .tables
android_metadata  tbl_countries     tbl_states      

The Google Android SDK comes with a utility adb. The adb tool can be used to browse and modify the filesystem of attached emulators and physical devices. This example is a bit ahead of where we are as we haven't created the databases yet.

Setting database properties

There are a few database properties that should be set after connecting to the database. Use the setVersion(), setLocale(), and setLockingEnabled() methods to set these properties. These will be demonstrated in the creating tables example.

Creating Tables

Tables are created by executing statements on the database. The queries should be executed with the execSQL() statement.
package higherpass.TestingData;

import java.util.Locale;

import android.app.Activity;
import android.os.Bundle;
import android.database.sqlite.SQLiteDatabase;

public class TestingData extends Activity {
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        
        SQLiteDatabase db;
        
        db = openOrCreateDatabase(
         "TestingData.db"
         , SQLiteDatabase.CREATE_IF_NECESSARY
         , null
         );
        db.setVersion(1);
        db.setLocale(Locale.getDefault());
        db.setLockingEnabled(true);
        
        final String CREATE_TABLE_COUNTRIES =
         "CREATE TABLE tbl_countries ("
         + "id INTEGER PRIMARY KEY AUTOINCREMENT,"
         + "country_name TEXT);";
        final String CREATE_TABLE_STATES = 
         "CREATE TABLE tbl_states ("
         + "id INTEGER PRIMARY KEY AUTOINCREMENT,"
         + "state_name TEXT,"
         + "country_id INTEGER NOT NULL CONSTRAINT "
         + "contry_id REFERENCES tbl_contries(id) "
         + "ON DELETE CASCADE);";
        db.execSQL(CREATE_TABLE_COUNTRIES);
        db.execSQL(CREATE_TABLE_STATES);
        final String CREATE_TRIGGER_STATES = 
         "CREATE TRIGGER fk_insert_state BEFORE "
         + "INSERT on tbl_states"
         + "FOR EACH ROW "
         + "BEGIN "
         + "SELECT RAISE(ROLLBACK, 'insert on table "
         + ""tbl_states" voilates foreign key constraint "
         + ""fk_insert_state"') WHERE (SELECT id FROM "
         + "tbl_countries WHERE id = NEW.country_id) IS NULL; "
         + "END;";
        db.execSQL(CREATE_TRIGGER_STATES);
    }
}

As before open the database with openOrCreateDatabase(). Now configure the database connection with setVersion to set the database version. The setLocale method sets the default locale for the database and setLockingEnabled enables locking on the database. Next we setup final String variables to hold the SQLite table creation statements and execute them with execSQL.
Additionally we manually have to create triggers to handle the foreign key relationships between the table. In a production application there would also need to be foreign key triggers to handle row updates and deletes. The foreign key triggers are executed with execSQL just like the table creation.

Inserting records

Android comes with a series of classes that simplify database usage. Use a ContentValues instance to create a series of table field to data matchings that will be passed into an insert() method. Android has created similar methods for updating and deleting records.
        ContentValues values = new ContentValues();
        values.put("country_name", "US");
        long countryId = db.insert("tbl_countries", null, values);
        ContentValues stateValues = new ContentValues();
        stateValues.put("state_name", "Texas");
        stateValues.put("country_id", Long.toString(countryId));
        try {
            db.insertOrThrow("tbl_states", null, stateValues);
        } catch (Exception e) {
            //catch code
        }

Append this code to the previous example. First create a ContentValues object to store the data to insert and use the put method to load the data. Then use the insert() method to perform the insert query into SQLite. The insert() function expects three parameters, the table name, null, and the ContentValues pairs. Also a long is returned by the insert() function. This long will hold the primary key of the inserted row.
Next we create a new ContentValues pair for the state and perform a second insert using the insertOrThrow() method. Using insertOrThrow() will throw an exception if the insert isn't successful and must be surrounded by a try/catch block. You'll notice that currently the application dies with an unhandled exception because the tables we're trying to create already exist. Go back into the adb shell and attach to the SQLite database for the application and drop the tables.
sqlite> drop table tbl_countries;
sqlite> drop table tbl_states;

Updating data

Updating records is handled with the update() method. The update() function supports WHERE syntax similar to other SQL engines. The update() method expects the table name, a ContentValues instance similar to insert with the fields to update. Also allowed are optional WHERE syntax, add a String containing the WHERE statement as parameter 3. Use the ? to designate an argument replacement in the WHERE clause with the replacements passed as an array in parameter 4 to update.
       ContentValues updateCountry = new ContentValues();
       updateCountry.put("country_name", "United States");
       db.update("tbl_countries", updateCountry, "id=?", new String[] {Long.toString(countryId)});
First remove the table create statements from the code. We don't need to keep creating and dropping tables. Now create a new ContentValues instance, updateCountry, to hold the data to be updated. Then use the update() method to update the table. The where clause in parameter 3 uses replacement of the ? with the values stored in parameter 4. If multiple ? existed in the where statement they would be replaced in order by the values of the array.From the adb shell attach to the database and execute select * FROM tbl_countries; inside sqlite3.
bash-3.1$ /usr/local/android-sdk-linux/tools/adb -s emulator-5554 shell
# sqlite3 /data/data/higherpass.TestingData/databases/TestingData.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> select * FROM tbl_countries;
1|United States

Deleting data

Once data is no longer needed it can be removed from the database with the delete() method. The delete() method expects 3 parameters, the database name, a WHERE clause, and an argument array for the WHERE clause. To delete all records from a table pass null for the WHERE clause and WHERE clause argument array.
       db.delete("tbl_states", "id=?", new String[] {Long.toString(countryId)});

Simply call the delete() method to remove records from the SQLite database. The delete method expects, the table name, and optionally a where clause and where clause argument replacement arrays as parameters. The where clause and argument replacement array work just as with update where ? is replaced by the values in the array.

Source from :-http://www.higherpass.com

Wednesday, September 21, 2011

how to emulate an SD card with the Android emulator


Here are the required step to use an SDCard :

1) Create the emulated SDCard on your real computer :


Google provides a tool just for that, in their SDK/tools directory called mksdcard.
just give it the size of the SdCard you want to create ( in kilobytes or megabytes ) , the name of the resulting file, and you're done :
Open a command prompt, go to your SDK/tool directory, and type :
mksdcard 64M MyNewSdCard
and that's it !


2) Using the emulated SDCard with the emulator

To use the emulated SDCard in the emulator, simply launch the emulator with the '-sdcardEmulatedCard_File_Path' parameter :

So, from the command prompt in the SDK/Tool directory, type :
emulator -sdcard MyNewSdCard

3) Using the emulated SDCard with the emulator from Eclipse :

From eclipse, open the menu entry :
Run / RunConfigurations...

Then on the right panel, choose 'target'
And in the Additionnal Emulator Command Line Options, add
'-sdcard fullPath_to_your_SdCard_File'


And that'all !

4) How to explore the emulated SdCard content and push / pull files from your computer :

Within Eclipse :

Open the DDMS perspective :
Window / Open Perspective / Other... / DDMS

Now select the file explorer tab ( in the right panel ), and you have acces to a classic explorer of your emulated phone, including the sdcard !

Now to push a file from your computer, or pull a file from the emulated device, you have two discrete icons in the upper right corner :

Just use them to to browse and select on your computer the file to pull or push !

Without Eclipse :

Just launch DDMS from the command prompt in the SDK / tools directory.
The file explorer is in the device menu...

Monday, September 5, 2011

Connecting to Remote MySQL database


Introduction

               In this post we can see how to connect remote MySQL database throw android application

Connect to MySQL

We can code the data in JSON format, between Android and PHP with the easy to use built in JSON functions in both languages.
               

I present some sample code, which selects data from a database depending on a given condition and creates a log message on the android side with the received data.
Lets suppose that we have a MySQL database named PeopleData, and a table int created, with the following SQL:
CREATE TABLE people (                
   
      id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
   
      name VARCHAR( 100 ) NOT NULL ,
   
      sex BOOL NOT NULL DEFAULT '1',
   
      birthyear INT NOT NULL
   
      )


PHP File


The PHP code will be very simple:
    - connect to the database
    - run an SQL query, with a WHERE block depending on data from POST/GET values
    - output it in JSON format


For example we will have this functionality in the getAllPeopleBornAfter.php file:
<?php 

      mysql_connect("127.0.0.1","root","xxpasswordxx");

      mysql_select_db("peopledata");

      $q=mysql_query("SELECT * FROM people WHERE birthyear>'".$_REQUEST['year']."'");

      while($e=mysql_fetch_assoc($q))

              $output[]=$e;

           print(json_encode($output));
     
    mysql_close();
?>




Android Part

The Android part is only a bit more complicated:
    - use a HttpPost to get the data, sending the year value
    - convert response to string
    - parse JSON data, and use it as you want






package com.connector;


import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;


import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;


import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.widget.LinearLayout;
import android.widget.TextView;




public class whitehat extends Activity {
/** Called when the activity is first created. */
   
   TextView txt;
@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    // Create a crude view - this should really be set via the layout resources  
    // but since its an example saves declaring them in the XML.  
    LinearLayout rootLayout = new LinearLayout(getApplicationContext());  
    txt = new TextView(getApplicationContext());  
    rootLayout.addView(txt);  
    setContentView(rootLayout);  


    // Set the text and call the connect function.  
    txt.setText("Connecting..."); 
  //call the method to run the data retreival
    txt.setText(getServerData(KEY_121)); 






}
public static final String KEY_121 = "http://xx.xx.xxx.xxx/hellomysql/mysqlcon.php"; //i use my real ip here






private String getServerData(String returnString) {
    
   InputStream is = null;
    
   String result = "";
    //the year data to send
    ArrayList<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>();
    nameValuePairs.add(new BasicNameValuePair("year","1970"));


    //http post
    try{
            HttpClient httpclient = new DefaultHttpClient();
            HttpPost httppost = new HttpPost(KEY_121);
            httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
            HttpResponse response = httpclient.execute(httppost);
            HttpEntity entity = response.getEntity();
            is = entity.getContent();


    }catch(Exception e){
            Log.e("log_tag", "Error in http connection "+e.toString());
    }


    //convert response to string
    try{
            BufferedReader reader = new BufferedReader(new InputStreamReader(is,"iso-8859-1"),8);
            StringBuilder sb = new StringBuilder();
            String line = null;
            while ((line = reader.readLine()) != null) {
                    sb.append(line + "\n");
            }
            is.close();
            result=sb.toString();
    }catch(Exception e){
            Log.e("log_tag", "Error converting result "+e.toString());
    }
    //parse json data
    try{
            JSONArray jArray = new JSONArray(result);
            for(int i=0;i<jArray.length();i++){
                    JSONObject json_data = jArray.getJSONObject(i);
                    Log.i("log_tag","id: "+json_data.getInt("id")+
                            ", name: "+json_data.getString("name")+
                            ", sex: "+json_data.getInt("sex")+
                            ", birthyear: "+json_data.getInt("birthyear")
                    );
                    //Get an output to the screen
                    returnString += "\n\t" + jArray.getJSONObject(i); 
            }
    }catch(JSONException e){
            Log.e("log_tag", "Error parsing data "+e.toString());
    }
    return returnString; 
}    
    
}