Shahraiz T. Shahraiz T. - 26 days ago 14
Android Question

Using SQLiteOpenHelper to run statements from SQL

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?

Answer
  1. 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'
    
  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));
    
  3. 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?

  1. for first pleas read:

    inserting of 6k records could take few seconds :)

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

  2. 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

Comments