How to Store Array in One Column in SQLite3

How to read one column from SQLite and store in Array

my friend a few be careful.

this code is right but in line you mistake :
[array addObject:_dataDictionary2];

instead array put ModiArray

/*==================================================================
 METHOD FOR GETTING MIDIFIED FROM DATABASE
 ==================================================================*/
- (NSMutableArray*)readingModified
{
     ModiArray = [[NSMutableArray alloc] init];
    // Setup the database object
    sqlite3 *database2;
    // Open the database from the users filessytem
    if(sqlite3_open([[self DatabaseSqlite] UTF8String], &database2) == SQLITE_OK)
    {
        // Setup the SQL Statement and compile it for faster access
        //SQLIte Statement
        NSString *sqlStatement_userInfo =[NSString stringWithFormat:@"Select ParentID from Table1"];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(database2, [sqlStatement_userInfo UTF8String], -1, &compiledStatement, NULL) == SQLITE_OK)
        {
            // Loop through the results and add them to the feeds array
            while(sqlite3_step(compiledStatement) == SQLITE_ROW)
            {
                // Init the Data Dictionary
                NSMutableDictionary *_dataDictionary2=[[NSMutableDictionary alloc] init];
                NSString *_recordParentID = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 0)];
                [_dataDictionary2 setObject:[NSString stringWithFormat:@"%@",_recordModified] forKey:@"ParentID"];
                [ModiArray addObject:_dataDictionary2];
            }
        }
        else
        {
            NSLog(@"No Data Found");
        }
        // Release the compiled statement from memory
        sqlite3_finalize(compiledStatement);
    }
    sqlite3_close(database2);

    return ModiArray;
}

Store array in SQLite that is referenced in another table

There is one special case that this schema cannot handle: it is not possible to store an array of size zero.
This might not be a concern in practice, but it shows that the database is not fully normalized.

A foreign key always references a single parent record.
Therefore, what is missing is a table that has a single record for each array.
Implementing this would result in a schema like this:

CREATE TABLE array
(
    id integer PRIMARY KEY
    -- no other properties
);
CREATE TABLE array_points
(
    array_id integer REFERENCES array(id),
    position integer,
    x, y, [...],
    PRIMARY KEY (array_id, position)
) WITHOUT ROWID; -- see http://www.sqlite.org/withoutrowid.html
CREATE TABLE foo
(
    [...],
    array_id integer REFERENCES array(id)
);

The additional table requires more effort to manage, but now you have the ability to generate array IDs through autoincrementing.

Store array by array in SQLite-Database

Try adding every n or so rows in inside one Transaction(assuming failure is not an issue, example if some rows fail to insert, you can go on without rolling back previous ones).
Declare a counter oustide the loop:

int n = 1000; //commit every 1000 rows, or you can tweak
int count = 0; //counter for rows inserted

Start transaction in the outer loop. increase and check the counter in the inner loop

if(count % n == 0){
  //commit the transaction
}
count++

(Dont't forget to reopen Transaction again in the outer loop)

http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html

How to store a simple string array in SQLite 3?

You could convert the array into a string to store it in the database. When you pull it from the database, convert it back into an array.

myModel.myDbObject = array.join("")

Then when pulling from the database...

array[] = myModel.myDbObject.split("")

That's essentially what I had to do for one of my Ruby on Rails applications.

Or for array elements longer than just one letter

myModel.myDbObject = array.join(",")

Pulling from database

array[] = myModel.myDbObject = array.split(",")

Inserting an array into sqlite3 python

You can store an array in a single string field, if you somehow genereate a string representation of it, e.g. sing the pickle module. Then, when you read the line, you can unpickle it. Pickle converts many different complex objects (but not all) into a string, that the object can be restored of. But: that is most likely not what you want to do (you wont be able to do anything with the data in the tabel, except selecting the lines and then unpickle the array. You wont be able to search.

If you want to have anything of varying length (or fixed length, but many instances of similiar things), you would not want to put that in a column or multiple columns. Thing vertically, not horizontally there, meaning: don't thing about columns, think about rows. For storing a vector with any amount of components, a table is a good tool.

It is a little difficult to explain from the little detail you give, but you should think about creating a second table and putting all the names there for every row of your first table. You'd need some key in your first table, that you can use for your second table, too:

c.execute("CREATE TABLE first_table(int id, varchar(255) text, additional fields)")
c.execute("CREATE TABLE names_table(int id, int num, varchar(255) name)")

With this you can still store whatever information you have except the names in first_table and store the array of names in names_table, just use the same id as in first_table and num to store the index positions inside the array. You can then later get back the array by doing someting like

SELECT name FROM names_table 
WHERE id=?
ORDER BY num

to read the array of names for any of your rows in first_table.

That's a pretty normal way to store arrays in a DB.

---

Related Topics