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 0

You can simply read all the bytes of the pdf file and store them in the blob field of sqlite DB, but a better solution which is highly recommended is to store the files on the Internal Storage and in your DB just store the file's path. You have also to check if there is enough memory storage for you.

android - how to save pdf file in SQLite - Stack Overflow

android sqlite
Rectangle 27 0

You cannot directly open files from assets folder. Instead, you need to copy the sqlite database of your assets folder into an internal/external storage and later use the File path to open the file.

Try out below code to read the sqlite database from assests and copy it into sdcard to use it.

public class DataBaseHelper extends SQLiteOpenHelper {
    private Context mycontext;

    //private String DB_PATH = mycontext.getApplicationContext().getPackageName()+"/databases/";
    private static String DB_NAME = "(datbasename).sqlite";//the extension may be .sqlite or .db
    public SQLiteDatabase myDataBase;
    /*private String DB_PATH = "/data/data/"
                        + mycontext.getApplicationContext().getPackageName()
                        + "/databases/";*/

    public DataBaseHelper(Context context) throws IOException {
        super(context,DB_NAME,null,1);
        this.mycontext=context;
        boolean dbexist = checkdatabase();
        if (dbexist) {
            //System.out.println("Database exists");
            opendatabase(); 
        } else {
            System.out.println("Database doesn't exist");
            createdatabase();
        }
    }

    public void createdatabase() throws IOException {
        boolean dbexist = checkdatabase();
        if(dbexist) {
            //System.out.println(" Database exists.");
        } else {
            this.getReadableDatabase();
            try {
                copydatabase();
            } catch(IOException e) {
                throw new Error("Error copying database");
            }
        }
    }   

    private boolean checkdatabase() {
        //SQLiteDatabase checkdb = null;
        boolean checkdb = false;
        try {
            String myPath = DB_PATH + DB_NAME;
            File dbfile = new File(myPath);
            //checkdb = SQLiteDatabase.openDatabase(myPath,null,SQLiteDatabase.OPEN_READWRITE);
            checkdb = dbfile.exists();
        } catch(SQLiteException e) {
            System.out.println("Database doesn't exist");
        }
        return checkdb;
    }

    private void copydatabase() throws IOException {
        //Open your local db as the input stream
        InputStream myinput = mycontext.getAssets().open(DB_NAME);

        // Path to the just created empty db
        String outfilename = DB_PATH + DB_NAME;

        //Open the empty db as the output stream
        OutputStream myoutput = new FileOutputStream("/data/data/(packagename)/databases   /(datbasename).sqlite");

        // transfer byte to inputfile to outputfile
        byte[] buffer = new byte[1024];
        int length;
        while ((length = myinput.read(buffer))>0) {
            myoutput.write(buffer,0,length);
        }

        //Close the streams
        myoutput.flush();
        myoutput.close();
        myinput.close();
    }

    public void opendatabase() throws SQLException {
        //Open the database
        String mypath = DB_PATH + DB_NAME;
        myDataBase = SQLiteDatabase.openDatabase(mypath, null, SQLiteDatabase.OPEN_READWRITE);
    }

    public synchronized void close() {
        if(myDataBase != null) {
            myDataBase.close();
        }
        super.close();
    }

}

You can write the code to of queries in the same class only.. and by creating an instance of the class call the method to run the query. @UsmanRiaz

android - Reading sqlite file from asset folder - Stack Overflow

android
Rectangle 27 0

File currentDB = new File(data, currentDBPath);
File currentDB = getDatabasePath("Backup.db");

Hardcoding the database path is not a good idea since it is not guaranteed to be the same on all devices. Using getDatabasePath() will return the path on which the database was created. So if the database does exist, the correct path to it will be returned.

To restore a database you have previously exported, do exactly as you did for the export. The only difference is that you need to switch the source and destination around so that the source is now your backup file path and the destination is now the database file path. The database will then be overwritten with the backup:

source = new FileInputStream(backupDB).getChannel();
destination = new FileOutputStream(currentDB).getChannel();

ok thanks and to import it from the same directory?

You can use getParent() or getParentFile() on the File returned to get the directory the database is in. Did using getDatabasePath() fix your error?

yes, getDatabasePath () is fine. but I would like some advice or a good example to import from sd card.

Please see my edit. It is a simple case of doing the copy in the reverse direction.

sqlite - Android Path internal Storage - Stack Overflow

android sqlite path backup
Rectangle 27 0

/data/data/info.androidhive.slidingmenu/cartella/Backup.db

a valid path? You can use DDMS to check in your app data that Backup.db exist

sqlite - Android Path internal Storage - Stack Overflow

android sqlite path backup
Rectangle 27 0

You can simply read all the bytes of the pdf file and store them in the blob field of sqlite DB, but a better solution which is highly recommended is to store the files on the Internal Storage and in your DB just store the file's path. You have also to check if there is enough memory storage for you.

android - how to save pdf file in SQLite - Stack Overflow

android sqlite
Rectangle 27 0

"i just wanna know how to get the path of a file stored in my internal storage??" Have you tried getFilesDir()? Example.

String path = this.getFilesDir().toString()+"/"+nameOfFile;

sorry to ask but i don't know how to use it in my action listener.I am a noob ,sorry to ask such a silly question

In your button click listener? the "this" keyword wont find getFilesDir() because its now referring to the Button. "this" is just the context, so you could access it like this as well Visitor_pass.this.getFilesDir(), and i think this will work too getBaseContext().getFilesDir(); and getApplicationContext().getFilesDir();

android - Saving path of pdf file in internal storage into Sqlite - St...

android pdf android-sqlite
Rectangle 27 0

Environment.getExternalStorageDirectory()

This method returns the primary external storage directory. For example, if you have a folder called /pdf, so the path of your file will be:

Environment.getExternalStorageDirectory() + "/pdf/" + <pd_file_name>

Don't forget the part about permissions

android - Saving path of pdf file in internal storage into Sqlite - St...

android pdf android-sqlite