11

Is it possible to insert a table of integers like int[] numbers into an SQLite Table ?

How to create the column where I put this table?

this line of code:

values.put(MySqlHelper.COLUMN_CHILDS, numbers);

Returns me an error like:

Change type of `numbers` to `String`
Andrew Thompson
  • 168,117
  • 40
  • 217
  • 433
Hossam Oukli
  • 1,296
  • 3
  • 19
  • 42
  • If there is an array type in SQLLite and the driver is providiing that to u, u can be. Otherwise, u should write/read it by yourself as u are writing into db as String – Neron May 14 '13 at 10:26
  • 1
    See [this](http://stackoverflow.com/questions/3005231/how-to-store-array-in-one-column-in-sqlite3) answer, SQLite does not support arrays. You'll have to use a workaround like storing it as text, or using foreign key and another table. – Dmitry Kuskov May 14 '13 at 10:30

2 Answers2

17

There is no array datatype in SQLite. You have two options:

Create 2 different tables, a "primary" table and a "numbers" table, where the numbers refer to a record (row) on the "primary" table via a foreign key.

(easier but not ideal): As @CloudyMarble suggests, Use Arrays.toString(numbers) (or a StringBuilder if for some reason that doesn't fit your needs) to put all of the numbers in your array into a comma-separated String and store that in your database. You can then pull that value back from the database into a Java array like so:

 String[] s = stringFromDB.split(",");
 int[] numbers = new int[s.length];
 for (int curr = 0; curr < s.length; curr++)
     numbers[curr] = Integer.parseInt(s[curr]);
drew moore
  • 31,565
  • 17
  • 75
  • 112
  • I didn't know about the second solution, but i was thinking about creating similar methods, but what makes you think that it's not ideal? – Hossam Oukli May 14 '13 at 10:35
  • It's just not really scalable. If your project is fairly small, it'll work fine. But all those string operations are expensive and they leave significant room for error, so if you have to do a lot of them it will be more efficient to use the first solution. – drew moore May 14 '13 at 10:38
  • 6
    But Arrays.toString() puts [] around the string (as commas between them). Your split snippet doesn't remove them. I don't htink ParseInt can handle the []? – Tom Sep 13 '13 at 16:43
4

insert your numbers array as a string:

Arrays.toString(numbers);

And on reading split the array using the split method and convert elements to integer as @drewmore's answer show.

CloudyMarble
  • 36,908
  • 70
  • 97
  • 130