Thursday, 20 December 2012

Difference between DELETE,TRUNCATE and DROP commands

DELETE

The DELETE command is used to remove rows from a table.
After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction
to make the change permanent or to undo it.

Note that this operation will cause all DELETE triggers on the table to fire.

TRUNCATE

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired.
As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE.

DROP

The DROP command removes a table from the database. All the tables' rows,
indexes and privileges will also be removed. No DML triggers will be fired.This operation cannot be rolled back

Tuesday, 18 December 2012

Difference Between query and rawQuery in Android

In Android  Executing Queries using below methods

    Execute db.rawQuery method
    Execute db.query method


To execute a raw query to retrieve all departments:

Cursor getAllDepts()
  {
   SQLiteDatabase db=this.getReadableDatabase();
   Cursor cur=db.rawQuery("SELECT "+colDeptID+" as _id,
        "+colDeptName+" from "+deptTable,new String [] {});

   return cur;
  }

The rawQuery method has two parameters:

    String query: The select statement
    String[] selection args: The arguments if a WHERE clause is included in the select statement

Notes
The result of a query is returned in Cursor object.In a select statement if the primary key column (the id column) of the table has a name other than _id, then you have to use an alias in the form SELECT [Column Name] as _id cause the Cursor object always expects that the primary key column has the name _id or it will throw an exception .

Another way to perform a query is to use a db.query method. A query to select all employees in a certain department from a view would be like this:

public Cursor getEmpByDept(String Dept) {
   SQLiteDatabase db=this.getReadableDatabase();
   String [] columns=new String[]{"_id",colName,colAge,colDeptName};
   Cursor c=db.query(viewEmps, columns, colDeptName+"=?",
        new String[]{Dept}, null, null, null);
   return c;
  }

The db.query has the following parameters:

    String Table Name: The name of the table to run the query against
    String [ ] columns: The projection of the query, i.e., the columns to retrieve
    String WHERE clause: where clause, if none pass null
    String [ ] selection args: The parameters of the WHERE clause
    String Group by: A string specifying group by clause
    String Having: A string specifying HAVING clause
    String Order By by: A string Order By by clause

Content From:-shuklaxyz

Android SQLite Database Viewer or Debuging with Stetho

Every Android Developer uses SQLite Database to store data into the Android Application data. But to view the data in SQLite have a lot of...