Rectangle 27 2

(12) I deleted a lot of data but the database file did not get any sma...

No. When you delete information from an SQLite database, the unused disk space is added to an internal "free-list" and is reused the next time you insert data. The disk space is not lost. But neither is it returned to the operating system.

If you delete a lot of data and want to shrink the database file, run the VACUUM command. VACUUM will reconstruct the database from scratch. This will leave the database with an empty free-list and a file that is minimal in size. Note, however, that the VACUUM can take some time to run and it can use up to twice as much temporary disk space as the original file while it is running.

An alternative to using the VACUUM command is auto-vacuum mode, enabled using the auto_vacuum pragma.

ruby on rails - Why does my sqlite3 database not decrease in size? - S...

ruby-on-rails sqlite3 paperclip
Rectangle 27 3

insert()
dbHelper.insert_user(name, mobile);
DatabaseHelper dbHelper;

@Reena - Where have you written code for initialization of DatabaseHelper dbHelper ?

android - NullPointerException in sqlite database while inserting data...

android sqlite
Rectangle 27 123

public class DatabaseHelper extends SQLiteOpenHelper {
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "database_name";

    // Table Names
    private static final String DB_TABLE = "table_image";

    // column names
    private static final String KEY_NAME = "image_name";
    private static final String KEY_IMAGE = "image_data";

    // Table create statement
    private static final String CREATE_TABLE_IMAGE = "CREATE TABLE " + DB_TABLE + "("+ 
                       KEY_NAME + " TEXT," + 
                       KEY_IMAGE + " BLOB);";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        // creating table
        db.execSQL(CREATE_TABLE_IMAGE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // on upgrade drop older tables
        db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE);

        // create new table
        onCreate(db);
    }
}

Insert in the Database:

public void addEntry( String name, byte[] image) throws SQLiteException{
    SQLiteDatabase database = this.getWritableDatabase();
    ContentValues cv = new  ContentValues();
    cv.put(KEY_NAME,    name);
    cv.put(KEY_IMAGE,   image);
    database.insert( DB_TABLE, null, cv );
}
byte[] image = cursor.getBlob(1);
  • Before inserting into database, you need to convert your Bitmap image into byte array first then apply it using database query.
  • When retrieving from database, you certainly have a byte array of image, what you need to do is to convert byte array back to original image. So, you have to make use of BitmapFactory to decode.

Below is an Utility class which I hope could help you:

public class DbBitmapUtility {

    // convert from bitmap to byte array
    public static byte[] getBytes(Bitmap bitmap) {
        ByteArrayOutputStream stream = new ByteArrayOutputStream();
        bitmap.compress(CompressFormat.PNG, 0, stream);
        return stream.toByteArray();
    }

    // convert from byte array to bitmap
    public static Bitmap getImage(byte[] image) {
        return BitmapFactory.decodeByteArray(image, 0, image.length);
    }
}

Further reading If you are not familiar how to insert and retrieve into a database, go through this tutorial.

@Lazy Ninja, add a quote(") before CREATE TABLE, i'm not able to do this minor edit.

@LazyNinja, I am using your answer to help me store and retreive bitmap images and strings. This post is so helpful! I had a question about how to "Insert into the Database". On the line "database.insert( DB_TABLE, null, cv );", how are you defining database?

@Natalie use SQLiteDatabase database = this.getWritableDatabase(); I have added it in the source code. And I think the following link could be useful: mrbool.com/how-to-insert-data-into-a-sqlite-database-in-android/

Very clear and helpful.Thanks a ton @LazyNinja

How to store(bitmap image) and retrieve image from sqlite database in ...

android image sqlite bitmapimage
Rectangle 27 3

Interestingly enough, when I extract the SQLite database from my device, it seems to have all of the data.

My guess is that what has happened is that:

  • You started off with your app uninstalled
  • You installed your app
  • You ran your app, invoking this method that inserted three rows in a newly-created database, which succeeded
  • You ran your app again, invoking this method that tried to insert three rows into the database that already exists with the rows from the prior run, and you fail the uniqueness constraint

It is your job to make sure that either the data you are trying to insert does not exist, or that you do not violate any uniqueness constraints. Since it is unclear what purpose your "method that inserts rows based on a query from a database" is serving, it is difficult to provide you with more concrete advice.

It seems I was attempting my insert method twice without realizing it when the application first installed.

Android SQLite PRIMARY KEY must be unique exception when primary keys ...

android sqlite primary-key
Rectangle 27 3

Interestingly enough, when I extract the SQLite database from my device, it seems to have all of the data.

My guess is that what has happened is that:

  • You started off with your app uninstalled
  • You installed your app
  • You ran your app, invoking this method that inserted three rows in a newly-created database, which succeeded
  • You ran your app again, invoking this method that tried to insert three rows into the database that already exists with the rows from the prior run, and you fail the uniqueness constraint

It is your job to make sure that either the data you are trying to insert does not exist, or that you do not violate any uniqueness constraints. Since it is unclear what purpose your "method that inserts rows based on a query from a database" is serving, it is difficult to provide you with more concrete advice.

It seems I was attempting my insert method twice without realizing it when the application first installed.

Android SQLite PRIMARY KEY must be unique exception when primary keys ...

android sqlite primary-key
Rectangle 27 1

You can store your data in any format: SQLite or Text file with XML/JSON/CSV. But personally I would store it in SQLite. The reason is following. You can create two parallel tasks, one of which collects data into SQLite database and the other sends bunches of that data to the server asynchronously when it's possible. SQLite is more convenient to organize this than text files, since you always work with records.

About sending a bunches of data to the server. You can send your data as a JSON inside a BODY of a HTTP POST request. Then on a server you should read this data and save it to the server database in one SQL statement. This is definitely possible, it's called a batch INSERT statement.

This is example of sending JSON data in a BODY of HTTP POST request:

HttpPost httpPost = new HttpPost(urlString);
String bodyJsonString = this.getBodyJsonString();
httpPost.setEntity(new StringEntity(bodyJsonString, HTTP.UTF_8));

@Override
String getBodyJsonString() {
    JSONObject jsonObject = new JSONObject();

    try {
        jsonObject.put("text", this.message);
        jsonObject.put("theme", this.theme);
    } catch (JSONException e) {
        e.printStackTrace();
    }

    String resultString = jsonObject.toString();
    Log.v(Helper.TAG, "resultString = " + resultString);

    return resultString;
}

Fine.. I respect your reputations and accepts this as an answer. Thanks for the assistance.

Text files are also very slow comparing to SQLite. Since the data structure in which you store your data is a string. For example, if you have a string like "234 345 656 2 3425 234" and want to find if 345 is there, it will be very slow, comparing to the situation, when you have those records organized in a table of database, because there they are arranged to something like a binary tree data structure, which has O(log(2)(n)) complexity, but linear search has O(n) complexity.

xml - which is the best practice to store location data on android dev...

android xml location
Rectangle 27 30

If you want to package static data with your application, I recommend preparing the database at development time (using any UI or csv-import command you like) and shipping the sqlite file inside the assets folder. You can then simply copy the entire sqlite file onto the device when your application is first run. These posts take you through this idea which is most likely the fastest way to setup a database (file copy speed).

Thanks for the reply Josef, I have tried to ship the sqlite3 .db file in the assets folder of the .apk (like reigndesign post outlines). The issue I'm having with the reigndesign solution is that the onUpgrade() does not get called when my database version # changes. In fact, the only way I can get it to replace the existing database with a newer version is to work around the onUpgrade() and add a statement that explicitly deletes the database just before the createDataBase() is called (which basically forces you to recreate the database every time the Activity is run... =(

As for using transactions that may be a workable solution. I was able to get the onUpgrad() to be call consistency when I implemented this solution (screaming-penguin.com/node/7742) but a simple sql import statement is still ideal considering the upgrade and tweaks necessary to get the reigndesign solution to work (i.e. the '_id' column and the 'android_metadata' table). While the tweaks are not difficult to implement I fear it adds unnecessary dependencies to how the Android platform expects their databases to be created. If that were ever to change so would our solution...

Shipping with a prebuilt sqlite3 file is NOT good practice. Manufacturers can implement/utilise whatever technology they want under the hood but must adhere to the sqlite3 interfaces - so your prebuilt sqlite3 may not work on some devices. So, best/safest practice is to always let your device create the database, then bulk import to it.

Populate Android Database From CSV file? - Stack Overflow

database android sqlite import sqlite3
Rectangle 27 45

Saving state is a kludge at best as far as I'm concerned. If you need to save persistent data, just use an SQLite database. Android makes it SOOO easy.

import java.util.Date;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class dataHelper {

    private static final String DATABASE_NAME = "autoMate.db";
    private static final int DATABASE_VERSION = 1;

    private Context context;
    private SQLiteDatabase db;
    private OpenHelper oh ;

    public dataHelper(Context context) {
        this.context = context;
        this.oh = new OpenHelper(this.context);
        this.db = oh.getWritableDatabase();
    }

    public void close()
    {
        db.close();
        oh.close();
        db = null;
        oh = null;
        SQLiteDatabase.releaseMemory();
    }


    public void setCode(String codeName, Object codeValue, String codeDataType)
    {
        Cursor codeRow = db.rawQuery("SELECT * FROM code WHERE codeName = '"+  codeName + "'", null);
        String cv = "" ;

        if (codeDataType.toLowerCase().trim().equals("long") == true)
        {
            cv = String.valueOf(codeValue);
        }
        else if (codeDataType.toLowerCase().trim().equals("int") == true)
        {
            cv = String.valueOf(codeValue);
        }
        else if (codeDataType.toLowerCase().trim().equals("date") == true)
        {
            cv = String.valueOf(((Date)codeValue).getTime());
        }
        else if (codeDataType.toLowerCase().trim().equals("boolean") == true)
        {
            String.valueOf(codeValue);
        }
        else
        {
            cv = String.valueOf(codeValue);
        }

        if(codeRow.getCount() > 0) //exists-- update
        {
            db.execSQL("update code set codeValue = '" + cv +
                "' where codeName = '" + codeName + "'");
        }
        else // does not exist, insert
        {
            db.execSQL("INSERT INTO code (codeName, codeValue, codeDataType) VALUES(" +
                    "'" + codeName + "'," +
                    "'" + cv + "'," +
                    "'" + codeDataType + "')" );
        }
    }

    public Object getCode(String codeName, Object defaultValue)
    {
        //Check to see if it already exists
        String codeValue = "";
        String codeDataType = "";
        boolean found = false;
        Cursor codeRow  = db.rawQuery("SELECT * FROM code WHERE codeName = '"+  codeName + "'", null);
        if (codeRow.moveToFirst())
        {
            codeValue = codeRow.getString(codeRow.getColumnIndex("codeValue"));
            codeDataType = codeRow.getString(codeRow.getColumnIndex("codeDataType"));
            found = true;
        }

        if (found == false)
        {
            return defaultValue;
        }
        else if (codeDataType.toLowerCase().trim().equals("long") == true)
        {
            if (codeValue.equals("") == true)
            {
                return (long)0;
            }
            return Long.parseLong(codeValue);
        }
        else if (codeDataType.toLowerCase().trim().equals("int") == true)
        {
            if (codeValue.equals("") == true)
            {
                return (int)0;
            }
            return Integer.parseInt(codeValue);
        }
        else if (codeDataType.toLowerCase().trim().equals("date") == true)
        {
            if (codeValue.equals("") == true)
            {
                return null;
            }
            return new Date(Long.parseLong(codeValue));
        }
        else if (codeDataType.toLowerCase().trim().equals("boolean") == true)
        {
            if (codeValue.equals("") == true)
            {
                return false;
            }
            return Boolean.parseBoolean(codeValue);
        }
        else
        {
            return (String)codeValue;
        }
    }


    private static class OpenHelper extends SQLiteOpenHelper {

        OpenHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL("CREATE TABLE IF  NOT EXISTS code" +
            "(id INTEGER PRIMARY KEY, codeName TEXT, codeValue TEXT, codeDataType TEXT)");
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        }
    }
}

A simple call after that

dataHelper dh = new dataHelper(getBaseContext());
String status = (String) dh.getCode("appState", "safetyDisabled");
Date serviceStart = (Date) dh.getCode("serviceStartTime", null);
dh.close();
dh = null;

Because it takes too long to load an SQLite database, considering that this is on the critical path to showing the user the app's UI. I have not actually timed it, so I'm happy to be corrected, but surely loading and opening a database file won't be fast?

Thank you so much for providing a solution a newbie can cut and paste into their app and use right away! @Tom As far as speed goes it takes about seven seconds to store 1000 pairs, but you can do it in an AsyncTask. However, you need to add a finally { cursor.close() } or it will crash from memory leak while doing this.

I came across this and while it seems neat I'm hesitant to try utilizing this on Google Glass, which is the device I'm working on/with lately.

Saving Android Activity state using Save Instance State - Stack Overfl...

android android-activity application-state
Rectangle 27 45

Saving state is a kludge at best as far as I'm concerned. If you need to save persistent data, just use an SQLite database. Android makes it SOOO easy.

import java.util.Date;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class dataHelper {

    private static final String DATABASE_NAME = "autoMate.db";
    private static final int DATABASE_VERSION = 1;

    private Context context;
    private SQLiteDatabase db;
    private OpenHelper oh ;

    public dataHelper(Context context) {
        this.context = context;
        this.oh = new OpenHelper(this.context);
        this.db = oh.getWritableDatabase();
    }

    public void close()
    {
        db.close();
        oh.close();
        db = null;
        oh = null;
        SQLiteDatabase.releaseMemory();
    }


    public void setCode(String codeName, Object codeValue, String codeDataType)
    {
        Cursor codeRow = db.rawQuery("SELECT * FROM code WHERE codeName = '"+  codeName + "'", null);
        String cv = "" ;

        if (codeDataType.toLowerCase().trim().equals("long") == true)
        {
            cv = String.valueOf(codeValue);
        }
        else if (codeDataType.toLowerCase().trim().equals("int") == true)
        {
            cv = String.valueOf(codeValue);
        }
        else if (codeDataType.toLowerCase().trim().equals("date") == true)
        {
            cv = String.valueOf(((Date)codeValue).getTime());
        }
        else if (codeDataType.toLowerCase().trim().equals("boolean") == true)
        {
            String.valueOf(codeValue);
        }
        else
        {
            cv = String.valueOf(codeValue);
        }

        if(codeRow.getCount() > 0) //exists-- update
        {
            db.execSQL("update code set codeValue = '" + cv +
                "' where codeName = '" + codeName + "'");
        }
        else // does not exist, insert
        {
            db.execSQL("INSERT INTO code (codeName, codeValue, codeDataType) VALUES(" +
                    "'" + codeName + "'," +
                    "'" + cv + "'," +
                    "'" + codeDataType + "')" );
        }
    }

    public Object getCode(String codeName, Object defaultValue)
    {
        //Check to see if it already exists
        String codeValue = "";
        String codeDataType = "";
        boolean found = false;
        Cursor codeRow  = db.rawQuery("SELECT * FROM code WHERE codeName = '"+  codeName + "'", null);
        if (codeRow.moveToFirst())
        {
            codeValue = codeRow.getString(codeRow.getColumnIndex("codeValue"));
            codeDataType = codeRow.getString(codeRow.getColumnIndex("codeDataType"));
            found = true;
        }

        if (found == false)
        {
            return defaultValue;
        }
        else if (codeDataType.toLowerCase().trim().equals("long") == true)
        {
            if (codeValue.equals("") == true)
            {
                return (long)0;
            }
            return Long.parseLong(codeValue);
        }
        else if (codeDataType.toLowerCase().trim().equals("int") == true)
        {
            if (codeValue.equals("") == true)
            {
                return (int)0;
            }
            return Integer.parseInt(codeValue);
        }
        else if (codeDataType.toLowerCase().trim().equals("date") == true)
        {
            if (codeValue.equals("") == true)
            {
                return null;
            }
            return new Date(Long.parseLong(codeValue));
        }
        else if (codeDataType.toLowerCase().trim().equals("boolean") == true)
        {
            if (codeValue.equals("") == true)
            {
                return false;
            }
            return Boolean.parseBoolean(codeValue);
        }
        else
        {
            return (String)codeValue;
        }
    }


    private static class OpenHelper extends SQLiteOpenHelper {

        OpenHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL("CREATE TABLE IF  NOT EXISTS code" +
            "(id INTEGER PRIMARY KEY, codeName TEXT, codeValue TEXT, codeDataType TEXT)");
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        }
    }
}

A simple call after that

dataHelper dh = new dataHelper(getBaseContext());
String status = (String) dh.getCode("appState", "safetyDisabled");
Date serviceStart = (Date) dh.getCode("serviceStartTime", null);
dh.close();
dh = null;

Because it takes too long to load an SQLite database, considering that this is on the critical path to showing the user the app's UI. I have not actually timed it, so I'm happy to be corrected, but surely loading and opening a database file won't be fast?

Thank you so much for providing a solution a newbie can cut and paste into their app and use right away! @Tom As far as speed goes it takes about seven seconds to store 1000 pairs, but you can do it in an AsyncTask. However, you need to add a finally { cursor.close() } or it will crash from memory leak while doing this.

I came across this and while it seems neat I'm hesitant to try utilizing this on Google Glass, which is the device I'm working on/with lately.

Saving Android Activity state using Save Instance State - Stack Overfl...

android android-activity application-state
Rectangle 27 45

While it is true that there is no ALTER COLUMN, if you only want to rename the column, drop the NOT NULL constraint, or change the data type, you can use the following set of commands:

PRAGMA writable_schema = 1;
UPDATE SQLITE_MASTER SET SQL = 'CREATE TABLE BOOKS ( title TEXT NOT NULL, publication_date TEXT)' WHERE NAME = 'BOOKS';
PRAGMA writable_schema = 0;

You will need to either close and reopen your connection or vacuum the database to reload the changes into the schema.

Y:\> sqlite3 booktest  
SQLite version 3.7.4  
Enter ".help" for instructions  
Enter SQL statements terminated with a ";"  
sqlite> create table BOOKS ( title TEXT NOT NULL, publication_date TEXT NOT NULL);  
sqlite> insert into BOOKS VALUES ("NULLTEST",null);  
Error: BOOKS.publication_date may not be NULL  
sqlite> PRAGMA writable_schema = 1; 
sqlite> UPDATE SQLITE_MASTER SET SQL = 'CREATE TABLE BOOKS ( title TEXT NOT NULL, publication_date TEXT)' WHERE NAME = 'BOOKS';  
sqlite> PRAGMA writable_schema = 0;  
sqlite> .q  

Y:\> sqlite3 booktest  
SQLite version 3.7.4  
Enter ".help" for instructions  
Enter SQL statements terminated with a ";"  
sqlite> insert into BOOKS VALUES ("NULLTEST",null);  
sqlite> .q

alter table SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.

Although this is dangerous, would it be the best-performing way of renaming a column (better than making a new table, copying the data, and deleting the old table)? Also, is it really that dangerous? It seems prone to mistakes, but unless I'm mistaken, the UPDATE statement is wrapped in a transaction, so it should be safe from sudden power failures, out-of-memory exceptions, etc.

Yes extremely fast -- Dangerous only means "Make sure you have a backup first"

The sqlite file format is very simple and that's why this operation is valid. The file format has only two sets of information about a table: The actual CREATE TABLE command as plain text, and the rows, whose values are appearing in the order of the fields from the CREATE command. Which means that the sqlite code opens the database, it parses each CREATE command and dynamically builds its column information in memory. So, any command that alters the CREATE command in a way that ends up with the same number of columns will work, even if you change their type or constraints.

@ThomasTempelmann However, adding constraints that are not fulfilled by the dataset will yield to problems because the query planner assumes that constraints hold.

How do I rename a column in a SQLite database table? - Stack Overflow

sqlite alter-table
Rectangle 27 5

Currently there is no way to precreate an SQLite database to ship with your apk. The best you can do is save the appropriate SQL as a resource and run them from your application. Yes, this leads to duplication of data (same information exists as a resrouce and as a database) but there is no other way right now. The only mitigating factor is the apk file is compressed. My experience is 908KB compresses to less than 268KB.

The thread below has the best discussion/solution I have found with good sample code.

I stored my CREATE statement as a string resource to be read with Context.getString() and ran it with SQLiteDatabse.execSQL().

I stored the data for my inserts in res/raw/inserts.sql (I created the sql file, 7000+ lines). Using the technique from the link above I entered a loop, read the file line by line and concactenated the data onto "INSERT INTO tbl VALUE " and did another SQLiteDatabase.execSQL(). No sense in saving 7000 "INSERT INTO tbl VALUE "s when they can just be concactenated on.

It takes about twenty seconds on the emulator, I do not know how long this would take on a real phone, but it only happens once, when the user first starts the application.

How about pulling the SQL script from the web on the first run? This way there is no need to duplicate data.

Yes, but the device will need to be connected to the internet. That's a serious drawback in some apps.

android - Ship an application with a database - Stack Overflow

android android-sqlite android-database
Rectangle 27 192

There are two options for creating and updating databases.

One is to create a database externally, then place it in the assets folder of the project and then copy the entire database from there. This is much quicker if the database has a lot of tables and other components. Upgrades are triggered by changing the database version number in the res/values/strings.xml file. Upgrades would then be accomplished by creating a new database externally, replacing the old database in the assets folder with the new database, saving the old database in internal storage under another name, copying the new database from the assets folder into internal storage, transferring all of the data from the old database (that was renamed earlier) into the new database and finally deleting the old database. You can create a database originally by using the SQLite Manager FireFox plugin to execute your creation sql statements.

The other option is to create a database internally from a sql file. This is not as quick but the delay would probably be unnoticeable to the users if the database has only a few tables. Upgrades are triggered by changing the database version number in the res/values/strings.xml file. Upgrades would then be accomplished by processing an upgrade sql file. The data in the database will remain unchanged except when its container is removed, for example dropping a table.

Here is a sample create_database.sql file. It is to be placed in the assets folder of the project for the internal method or copied into the "Execute SQL' of SQLite Manager to create the database for the external method. (NOTE: Notice the comment about the table required by Android.)

--Android requires a table named 'android_metadata' with a 'locale' column
CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US');
INSERT INTO "android_metadata" VALUES ('en_US');

CREATE TABLE "kitchen_table";
CREATE TABLE "coffee_table";
CREATE TABLE "pool_table";
CREATE TABLE "dining_room_table";
CREATE TABLE "card_table";

Here is a sample update_database.sql file. It is to be placed in the assets folder of the project for the internal method or copied into the "Execute SQL' of SQLite Manager to create the database for the external method. (NOTE: Notice that all three types of SQL comments will be ignored by the sql parser that is included in this example.)

--CREATE TABLE "kitchen_table";  This is one type of comment in sql.  It is ignored by parseSql.
/*
 * CREATE TABLE "coffee_table"; This is a second type of comment in sql.  It is ignored by parseSql.
 */
{
CREATE TABLE "pool_table";  This is a third type of comment in sql.  It is ignored by parseSql.
}
/* CREATE TABLE "dining_room_table"; This is a second type of comment in sql.  It is ignored by parseSql. */
{ CREATE TABLE "card_table"; This is a third type of comment in sql.  It is ignored by parseSql. }

--DROP TABLE "picnic_table"; Uncomment this if picnic table was previously created and now is being replaced.
CREATE TABLE "picnic_table" ("plates" TEXT);
INSERT INTO "picnic_table" VALUES ('paper');

Here is an entry to add to the /res/values/strings.xml file for the database version number.

<item type="string" name="databaseVersion" format="integer">1</item>

Here is an activity that accesses the database and then uses it. (Note: You might want to run the database code in a separate thread if it uses a lot of resources.)

Here is the database helper class where the database is created or updated if necessary. (NOTE: Android requires that you create a class that extends SQLiteOpenHelper in order to work with a Sqlite database.)

Here's the FileHelper class that contains methods for byte stream copying files and parsing sql files.

package android.example;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.Reader;
import java.nio.channels.FileChannel;

/**
 * @author Danny Remington - MacroSolve
 * 
 *         Helper class for common tasks using files.
 * 
 */
public class FileHelper {
    /**
     * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy
     * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then
     * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and
     * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both
     * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this
     * operation.
     * 
     * @param fromFile
     *            - InputStream for the file to copy from.
     * @param toFile
     *            - InputStream for the file to copy to.
     */
    public static void copyFile(InputStream fromFile, OutputStream toFile) throws IOException {
        // transfer bytes from the inputfile to the outputfile
        byte[] buffer = new byte[1024];
        int length;

        try {
            while ((length = fromFile.read(buffer)) > 0) {
                toFile.write(buffer, 0, length);
            }
        }
        // Close the streams
        finally {
            try {
                if (toFile != null) {
                    try {
                        toFile.flush();
                    } finally {
                        toFile.close();
                    }
            }
            } finally {
                if (fromFile != null) {
                    fromFile.close();
                }
            }
        }
    }

    /**
     * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy
     * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then
     * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and
     * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both
     * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this
     * operation.
     * 
     * @param fromFile
     *            - String specifying the path of the file to copy from.
     * @param toFile
     *            - String specifying the path of the file to copy to.
     */
    public static void copyFile(String fromFile, String toFile) throws IOException {
        copyFile(new FileInputStream(fromFile), new FileOutputStream(toFile));
    }

    /**
     * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy
     * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then
     * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and
     * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both
     * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this
     * operation.
     * 
     * @param fromFile
     *            - File for the file to copy from.
     * @param toFile
     *            - File for the file to copy to.
     */
    public static void copyFile(File fromFile, File toFile) throws IOException {
        copyFile(new FileInputStream(fromFile), new FileOutputStream(toFile));
    }

    /**
     * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy
     * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then
     * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and
     * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both
     * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this
     * operation.
     * 
     * @param fromFile
     *            - FileInputStream for the file to copy from.
     * @param toFile
     *            - FileInputStream for the file to copy to.
     */
    public static void copyFile(FileInputStream fromFile, FileOutputStream toFile) throws IOException {
        FileChannel fromChannel = fromFile.getChannel();
        FileChannel toChannel = toFile.getChannel();

        try {
            fromChannel.transferTo(0, fromChannel.size(), toChannel);
        } finally {
            try {
                if (fromChannel != null) {
                    fromChannel.close();
                }
            } finally {
                if (toChannel != null) {
                    toChannel.close();
                }
            }
        }
    }

    /**
     * Parses a file containing sql statements into a String array that contains
     * only the sql statements. Comments and white spaces in the file are not
     * parsed into the String array. Note the file must not contained malformed
     * comments and all sql statements must end with a semi-colon ";" in order
     * for the file to be parsed correctly. The sql statements in the String
     * array will not end with a semi-colon ";".
     * 
     * @param sqlFile
     *            - String containing the path for the file that contains sql
     *            statements.
     * 
     * @return String array containing the sql statements.
     */
    public static String[] parseSqlFile(String sqlFile) throws IOException {
        return parseSqlFile(new BufferedReader(new FileReader(sqlFile)));
    }

    /**
     * Parses a file containing sql statements into a String array that contains
     * only the sql statements. Comments and white spaces in the file are not
     * parsed into the String array. Note the file must not contained malformed
     * comments and all sql statements must end with a semi-colon ";" in order
     * for the file to be parsed correctly. The sql statements in the String
     * array will not end with a semi-colon ";".
     * 
     * @param sqlFile
     *            - InputStream for the file that contains sql statements.
     * 
     * @return String array containing the sql statements.
     */
    public static String[] parseSqlFile(InputStream sqlFile) throws IOException {
        return parseSqlFile(new BufferedReader(new InputStreamReader(sqlFile)));
    }

    /**
     * Parses a file containing sql statements into a String array that contains
     * only the sql statements. Comments and white spaces in the file are not
     * parsed into the String array. Note the file must not contained malformed
     * comments and all sql statements must end with a semi-colon ";" in order
     * for the file to be parsed correctly. The sql statements in the String
     * array will not end with a semi-colon ";".
     * 
     * @param sqlFile
     *            - Reader for the file that contains sql statements.
     * 
     * @return String array containing the sql statements.
     */
    public static String[] parseSqlFile(Reader sqlFile) throws IOException {
        return parseSqlFile(new BufferedReader(sqlFile));
    }

    /**
     * Parses a file containing sql statements into a String array that contains
     * only the sql statements. Comments and white spaces in the file are not
     * parsed into the String array. Note the file must not contained malformed
     * comments and all sql statements must end with a semi-colon ";" in order
     * for the file to be parsed correctly. The sql statements in the String
     * array will not end with a semi-colon ";".
     * 
     * @param sqlFile
     *            - BufferedReader for the file that contains sql statements.
     * 
     * @return String array containing the sql statements.
     */
    public static String[] parseSqlFile(BufferedReader sqlFile) throws IOException {
        String line;
        StringBuilder sql = new StringBuilder();
        String multiLineComment = null;

        while ((line = sqlFile.readLine()) != null) {
            line = line.trim();

            // Check for start of multi-line comment
            if (multiLineComment == null) {
                // Check for first multi-line comment type
                if (line.startsWith("/*")) {
                    if (!line.endsWith("}")) {
                        multiLineComment = "/*";
                    }
                // Check for second multi-line comment type
                } else if (line.startsWith("{")) {
                    if (!line.endsWith("}")) {
                        multiLineComment = "{";
                }
                // Append line if line is not empty or a single line comment
                } else if (!line.startsWith("--") && !line.equals("")) {
                    sql.append(line);
                } // Check for matching end comment
            } else if (multiLineComment.equals("/*")) {
                if (line.endsWith("*/")) {
                    multiLineComment = null;
                }
            // Check for matching end comment
            } else if (multiLineComment.equals("{")) {
                if (line.endsWith("}")) {
                    multiLineComment = null;
                }
            }

        }

        sqlFile.close();

        return sql.toString().split(";");
    }

}

i used the above code to to upgrade my db "upgrade_database.sql contains insert statement. some of the values have semicoln like insert into table_a values ('ss','ddd','aaaa;aaa'); " when i run the i noticed above mention insert not getting esecute because of the semicoln in the values any ides how to fix this.

There is a third option - copy the db from the web. I've done this and it goes fairly quickly for a 4 meg db. It also solves the issue with 2.3, for which the first solution (copy db) doesn't work.

Danny And Austyn - Your solution was perfect. I was having trouble with my home brewed solution and stumbled on yours. It really hit the spot. Thanks for taking the time to provide it.

I much prefer this answer against the top voted and accepted one. It has all information in one place (no see this link parts) and mentioned some Android specifics that I had no idea existed(like CREATE TABLE "android_metadata"). Also examples are written in great detail which is a plus. It's almost a copy paste solution which is not always good but explanations between the code are great.

agree with above comment. this answer is super helpful. the comments are amazing

android - Ship an application with a database - Stack Overflow

android android-sqlite android-database
Rectangle 27 10

Yes, sql can do this, but with a different syntax. The sqlite documentation is pretty good, by the way. It will also tell you that the only way to insert several row is use a select statement as the source of the data to be inserted.

sql - Is it possible to insert multiple rows at a time in an SQLite da...

sql sqlite syntax
Rectangle 27 9

Yes, sql can do this, but with a different syntax. The sqlite documentation is pretty good, by the way. It will also tell you that the only way to insert several row is use a select statement as the source of the data to be inserted.

sql - Is it possible to insert multiple rows at a time in an SQLite da...

sql sqlite syntax
Rectangle 27 8

For performance reason you can group every few calls (let say ~20) into one transaction:

myDb.beginTransaction();
   for(<your loop definition>){ myDb.execSQL(<your insert statement>) }
myDb.setTransactionSuccessful();
myDb.endTransaction();

The main idea is to not write physical database file on every inserted row, but every few rows. On other had as long as inserted data is not persisted on "drive" it's in the memory. For small data sets you can just start transaction, make all inserts and end transaction in one block.

Using prepared statement instead of standard statement is also a good idea, as the SQL interpreter needs to parse query only once - more information can be found here: How do I use prepared statements in SQlite in Android?

What's wrong with that?

u can use myDb.insert method

myDb.setTransactionSuccessful();
endTransaction()

Android : Insert multiple rows into sqlite database not working - Stac...

android sqlite
Rectangle 27 5

The problem with using transaction is that you lock the table also for reading. So if you have really much data to insert and you need to access to your data, for exemple a preview or so, this way doesn't work well.

The problem with the other solution is that you lose the order of the inserting

insert into mytable (col)
select 'c'
union 
select 'd'
union 
select 'a'
union 
select 'b';

In the sqlite the data will be store a,b,c,d...

sql - Is it possible to insert multiple rows at a time in an SQLite da...

sql sqlite syntax
Rectangle 27 5

The problem with using transaction is that you lock the table also for reading. So if you have really much data to insert and you need to access to your data, for exemple a preview or so, this way doesn't work well.

The problem with the other solution is that you lose the order of the inserting

insert into mytable (col)
select 'c'
union 
select 'd'
union 
select 'a'
union 
select 'b';

In the sqlite the data will be store a,b,c,d...

sql - Is it possible to insert multiple rows at a time in an SQLite da...

sql sqlite syntax
Rectangle 27 8

Sqlite3 can't do that directly in SQL except via a SELECT, and while SELECT can return a "row" of expressions, I know of no way to make it return a phony column.

However, the CLI can do it:

.import FILE TABLE     Import data from FILE into TABLE
.separator STRING      Change separator used by output mode and .import

$ sqlite3 /tmp/test.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> create table abc (a);
sqlite> .import /dev/tty abc
1
2
3
99
^D
sqlite> select * from abc;
1
2
3
99
sqlite>

If you do put a loop around an INSERT, rather than using the CLI .import command, then be sure to follow the advice in the sqlite FAQ for INSERT speed:

By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN...COMMIT then all the inserts are grouped into a single transaction. The time needed to commit the transaction is amortized over all the enclosed insert statements and so the time per insert statement is greatly reduced.

Another option is to run PRAGMA synchronous=OFF. This command will cause SQLite to not wait on data to reach the disk surface, which will make write operations appear to be much faster. But if you lose power in the middle of a transaction, your database file might go corrupt.

If you look at the source code for SQLite's .import command, it's just a loop, reading a line from the input file (or tty) and then an INSERT statement for that line. Unfortunately not significantly improved efficiency.

Digital Ross, if you put a large number of INSERT statements in a single transaction to you then want to put some sort of a "break" command in the error handler so that if one of the INSERT statements fails it won't stop the loop?

sql - Is it possible to insert multiple rows at a time in an SQLite da...

sql sqlite syntax
Rectangle 27 8

Sqlite3 can't do that directly in SQL except via a SELECT, and while SELECT can return a "row" of expressions, I know of no way to make it return a phony column.

However, the CLI can do it:

.import FILE TABLE     Import data from FILE into TABLE
.separator STRING      Change separator used by output mode and .import

$ sqlite3 /tmp/test.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> create table abc (a);
sqlite> .import /dev/tty abc
1
2
3
99
^D
sqlite> select * from abc;
1
2
3
99
sqlite>

If you do put a loop around an INSERT, rather than using the CLI .import command, then be sure to follow the advice in the sqlite FAQ for INSERT speed:

By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN...COMMIT then all the inserts are grouped into a single transaction. The time needed to commit the transaction is amortized over all the enclosed insert statements and so the time per insert statement is greatly reduced.

Another option is to run PRAGMA synchronous=OFF. This command will cause SQLite to not wait on data to reach the disk surface, which will make write operations appear to be much faster. But if you lose power in the middle of a transaction, your database file might go corrupt.

If you look at the source code for SQLite's .import command, it's just a loop, reading a line from the input file (or tty) and then an INSERT statement for that line. Unfortunately not significantly improved efficiency.

Digital Ross, if you put a large number of INSERT statements in a single transaction to you then want to put some sort of a "break" command in the error handler so that if one of the INSERT statements fails it won't stop the loop?

sql - Is it possible to insert multiple rows at a time in an SQLite da...

sql sqlite syntax
Rectangle 27 20

UTF-8 is the default encoding of SQLite databases. This shows up in situations like "SELECT CAST(x'52C3B373' AS TEXT);". However, the SQLite C library doesn't actually check whether a string inserted into a DB is valid UTF-8.

If you insert a Python unicode object (or str object in 3.x), the Python sqlite3 library will automatically convert it to UTF-8. But if you insert a str object, it will just assume the string is UTF-8, because Python 2.x "str" doesn't know its encoding. This is one reason to prefer Unicode strings.

db.create_function('FIXENCODING', 1, lambda s: str(s).decode('latin-1'))
db.execute("UPDATE TheTable SET TextColumn=FIXENCODING(CAST(TextColumn AS BLOB))")

for every text column in your database.

That is really useful. I don't need it right now because I'm just trying to understand this stuff and my db is disposable, but still that deserves an upvote for usefulness.

I've written a short script that uses this technique to re-encode all text, clob, and char columns, in all tables of the target database. stackoverflow.com/a/29048500/1191425 .

SQLite, python, unicode, and non-utf data - Stack Overflow

python sqlite unicode utf-8