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

Zlatko · Accepted Answer · 2013-02-07 07:48:57Z

1

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

Escape your "s in the stringified json.
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)}}");

edited Feb 7, 2013 at 7:48

answered Feb 4, 2013 at 22:33

Zlatko

19.7k14 gold badges76 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

user1456713 Over a year ago

As I'm sure you can tell, I'm completely new to SQLite. Could you give me an example? Thank u

Aaron Saunders Over a year ago

even with that, it still will not work... you cannot successfully serialize the function and make this work

Zlatko Over a year ago

Also, as the comment from @AaronSaunders says, you might also think on how will you deserialize this JSON once you get it out of the database. So you might want to either rethink the object or something, but again, that is another question.

user1456713 Over a year ago

Thanks a million, this has bn very helpful. Any ideas ir links on how to deserialize?

Zlatko Over a year ago

Yeah, plenty even here at SO: stackoverflow.com/questions/6487167/…, stackoverflow.com/questions/4935632/…, stackoverflow.com/questions/7395686/…

|

Collectives™ on Stack Overflow

Inserting Javascript Object into Titanium SQLite Database

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related