Inserting Javascript Object into Titanium SQLite Database

Question

If I have a javascipt object:

 var person = new Object();
 person.name = "newPerson";
 person.occupation = "Programmer";
 person.stateName = function(){
 console.log(this.name);
};

Using the SQLite based Titanium.Database API, how can I store this object in a single field? The below is working fine:

var db = Titanium.Database.openFile(Titanium.Filesystem.getFile(
Titanium.Filesystem.getApplicationDataDirectory(),'myDatabase.db'));   
db.execute("CREATE TABLE IF NOT EXISTS myTable (persons TEXT)");

I want to then store the object in a table field:

db.execute("INSERT INTO myTable (persons) VALUES(?)", JSON.stringify(person));

But the below is returned:

SQL Statement invalid or database missing
[21:55:35:300] [Titanium.Database.DB] [Error] Exception executing: INSERT INTO myTable
(person) VALUES(?), Error was: SQL Statement invalid or database missing

Answer 1

Your sql is wrong. You have to do two things:

1. Escape your "s in the stringified json.

2. Enclose that string in quotes in your SQL statement.

What you are doing is the equivalent of:

db.execute("INSERT INTO myTable (persons) VALUES({name: "newPerson, stateName: function() {console.log(this.name)})");

Even simpler: INSERT INTO myTable (persons) VALUES( {name:"Joe"} );

The name part may or may not be in quotes, depends on where you do this. (Better to add them, to make your stored object a true JSON.)

What you want to do is INSERT INTO myTable(persons) VALUES ( "{\"name\": \"Joe\"} " );

Note that you had VALUES(something) without quotes and you need VALUES("something") with quotes. Also you have to escape any quotes in your something (and do other stuff, but that's another topic.)

So your statement should look more like this:

INSERT INTO myTable(persons) VALUES("{\"name\":\"newPerson\", \"stateName\": function stateName(){console.log(this.name)}}");