create table with strings and arrays in sqlite by developing in android

Question

I would like to create a sqlite database starting from some input data contained in a xml file. The xml file contains items like:

<item>rome line1 1 3 4 5 ...</item>

I would like to have a table with three columns, the first two are string columns, the third one an array of integers (or strings). Is it possible to realise this in android studio? How do I put the values to initialize the table?

here is a sample of how I want the table to be:

    public static abstract class FeedEntry implements BaseColumns {
    public static final String TABLE_NAME = "entry";
    public static final String COLUMN_NAME_ENTRY_ID = "entryid";
    public static final String COLUMN_NAME_TITLE = "title";
    public static final String[] COLUMN_NAME_SCHEDULE = "schedule";
}

Here is the function to initialize the table

        String[] myArray = res.getStringArray(R.array.my_array);
    for (String item : myArray) {
        String[] split = item.split("\\s+");

        values.put(FeedReaderContract.FeedEntry.COLUMN_NAME_ENTRY_ID, split[0]);
        values.put(FeedReaderContract.FeedEntry.COLUMN_NAME_TITLE, split[1]);

        for (int i = 2; i < myArray.length - 2; i++) {
            values.put(FeedReaderContract.FeedEntry.COLUMN_NAME_SCHEDULE, Integer.parseInt(split[i]));
        }
        db.insert(FeedReaderContract.FeedEntry.TABLE_NAME, null, values);

    }

here is the array.xml file

<?xml version="1.0" encoding="utf-8"?>

<string-array name="my_array">

    <item>rome line1 1 3 5 7 11 190</item>


</string-array>

However, in what I have now there are some incompatible types, for example I am not able to put an array of integers in values, when I initialize the table.

you can put your values in CSV format in third column in SQLite. Split it by ',' character when you retrieve your value from third column — Viral Thakker, Oct 06 '15 at 08:31
@Viral could you post me a small example of what you have in mind? — Helios83, Oct 06 '15 at 08:33
For eg, I have an array of integer -> 1 2 3 5 6 7 8, Now I want to insert it into single column, So I'll use CSV format to store values (like 1,2,3,5,6,7,8), And when I retrieve value (which is in CSV), I'll split it by "," character — Viral Thakker, Oct 06 '15 at 08:36
I don't know, Though you can convert your array in csv format before adding to xml.This might help you to reduce processing in getting data from xml — Viral Thakker, Oct 06 '15 at 08:46
Have a look at this [Answer](http://stackoverflow.com/questions/9053685/android-sqlite-saving-string-array). You might get some idea.. — Shree Krishna, Oct 06 '15 at 08:52

score 0 · Answer 1 · edited Mar 16 '17 at 16:56

0

Contrary to the popular belief the data type is not restricted in sqlite to the creation of the database, at runtime you can insert string where you have declared int. i suggest you declare each column type as blob type refer this link for more sql lite db https://www.sqlite.org/datatype3.html

edited Mar 16 '17 at 16:56

marc_s

732,580
175
1,330
1,459

answered Oct 06 '15 at 08:36

Aniruddha K.M

7,361
3
43
52

Haresh Chhelana · Accepted Answer · 2015-10-07T07:33:48.793

0

Store schedule as String instead of String[] with ',' separator value :

public static final String COLUMN_NAME_SCHEDULE = "schedule";

String schedule="";
for (int i = 2; i < split.length; i++) {
    if(schedule.trim().length()>0){
        schedule += split[i]+",";
    }else{
        schedule = split[i];
    }
}

edited Oct 07 '15 at 07:33

answered Oct 06 '15 at 08:40

Haresh Chhelana

24,720
5
57
67

I understand the idea, but in your code there should be a small mistake, because my app crashes... – Helios83 Oct 06 '15 at 09:24
I think it is an out of bounds error. the reason is that myArray.length is the length of characters of the string, and not the length of group of characters without the whitespaces. how can I specify the latter one instead of the former one? – Helios83 Oct 06 '15 at 15:29

create table with strings and arrays in sqlite by developing in android

2 Answers2