Using SQLiteOpenHelper to run statements from SQL

Question

When I try to insert short portions of my data by copying over the relevant SQL statements into a String variable containing the SQL statements that SQLiteOpenHelper must run, everything works fine. Here is my query:

String POPULATE_TABLE =
            "INSERT INTO `en_sahih` (`indexID`, `chapterNo`, `verseNo`, `verseText`) VALUES\n" +
            "(1, 1, 1, 'This is verse 1'),\n" +
            "(2, 1, 2, 'This is verse 2'),\n" +
            "(3, 1, 3, 'This is verse 3'),\n" +
            "(4, 1, 4, 'This is verse 4'),\n" +
            "(5, 1, 5, 'This is verse 5'),\n" +
            "(6, 1, 6, 'This is verse 6'),\n" +
            "(7, 1, 7, 'This is verse 7');";
    db.execSQL(POPULATE_TABLE);

But when I copy over the entire 6k records into the string, which is probably not the ideal way to go about this, Android studio tells me the String is too long. How can I easily take the data that I already have in the form of SQL statements and put it into an .sql file and execute them in the new database that I've set up in my app. Any suggestions on how to do this?

export your data in excel or csv and then put it in assets folder and use transaction to insert record. — USKMobility, Jul 27 '16 at 18:32
If you got your .sql file from a dump you should have separate INSERT statements. Then you could save this to a file, read line by line and execute each. Just a thought. — MPelletier, Jul 27 '16 at 20:47
You should try to export your database into a CSV or XML file. You can then use some functions to insert the data back into your database. — Razor, Jul 27 '16 at 21:03
You can create a SQLite database in your desktop computer and add all the data and then link it to your Android app. — frogatto, Jul 27 '16 at 21:24

score 2 · Accepted Answer · edited May 23 '17 at 11:45

save records in text file (by delimiting each record with crlf)

 // records.txt
 1, 1, 1, 'This is verse 1'
 2, 1, 2, 'This is verse 2'

then you can read records from file using some Reader implementation (example BufferedReader)

file -> file input stream -> reader

How can I read a text file in Android?

 // create the file class object pointing to text file 
 File file = new File(some_path,"records.txt");

 // initialize reader class object providing file object
 BufferedReader br = new BufferedReader(new FileReader(file));

process data using while loop -> Reader.readline() method

 while ((line = Reader.readLine()) != null) {

     // a/ do insert 
     // b/ store data in some prim array 
     //    or via some list implementation (`ArrayList<String>`) 
     // then use insert or bulk

 }

I love the comprehensiveness of this answer @ceph3us Thanks a lot. However, there are two things: 1. doing 6k inserts using this approach might slow things down, how do I deal with that? and 2. Since I will be inserting using multiple copies of this file (different languages), having text files as well as the database itself is going to create redundancy and double the mount of space my app takes up on a device. Is it possible to programmatically delete a text file once I'm done with the inserts?

for first pleas read:

inserting of 6k records could take few seconds :)

see https://www.sqlite.org/faq.html#q19

as for second use

 File.delete();

or

RandomAccessFile.setLength(0);

from author :

does your application uses network communication ??

if yes - consider to use some web hosting account or vps as external source for your app data - then the distributed size will be small. - you also can keep data not outdated (some sort of updates are done on server side - in your case records to insert - you reduce the need to make an application update on client side) - basicaly move to server/client application

I love the comprehensiveness of this answer @ceph3us Thanks a lot. However, there are two things: 1. doing 6k inserts using this approach might slow things down, how do I deal with that? and 2. Since I will be inserting using multiple copies of this file (different languages), having text files as well as the database itself is going to create redundancy and double the mount of space my app takes up on a device. Is it possible to programmatically delete a text file once I'm done with the inserts? — Shahraiz T., Jul 28 '16 at 09:37

score 0 · Answer 2 · edited May 23 '17 at 11:53

You best option is to use XML or something similar like CSV. You can easily put all of the data from your table, arrange it with the appropriate tags and then access to retrieve the data and insert into your other database. If you're not sure on what XML looks like:

<parent attribute="something">
    <child attribute="something">
        <!-- More tags or data here //-->
    </child>
</parent>

If you're not sure on how to achieve this, I suggest you have a look at this page which has a question about exporting a database into an XML file. See how you go.

Using SQLiteOpenHelper to run statements from SQL

2 Answers2