2

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
user1456713
  • 65
  • 7

1 Answers1

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)}}");
Zlatko
  • 18,936
  • 14
  • 70
  • 123
  • As I'm sure you can tell, I'm completely new to SQLite. Could you give me an example? Thank u – user1456713 Feb 05 '13 at 19:45
  • even with that, it still will not work... you cannot successfully serialize the function and make this work – Aaron Saunders Feb 06 '13 at 00:51
  • 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. – Zlatko Feb 07 '13 at 07:49
  • Thanks a million, this has bn very helpful. Any ideas ir links on how to deserialize? – user1456713 Feb 07 '13 at 08:08
  • Yeah, plenty even here at SO: http://stackoverflow.com/questions/6487167/deserialize-from-json-to-javascript-object, http://stackoverflow.com/questions/4935632/how-to-parse-json-in-javascript, http://stackoverflow.com/questions/7395686/how-can-i-serialize-a-function-in-javascript – Zlatko Feb 07 '13 at 08:27
  • An invalid SQL statement error is still being returned with the following code: db.execute("INSERT INTO myTable(column!) VALUES("{\"name\":\"John Smith\",\"age\":30}")"); What am I doing wrong? – user1456713 Feb 07 '13 at 22:07
  • Two questions: can you verify that myTable really exists? Also, is your table called "column!" with that exclamation mark? – Zlatko Feb 07 '13 at 23:28
  • sqlite> insert into myTable (person) values ('{\"name\":\"john doe\", \"getName\": function (){return name;}'); sqlite> select * from mytable; {\"name\":\"john doe\", \"getName\": function (){return name;} sqlite> – Zlatko Feb 08 '13 at 00:03
  • The problem seemed to be that i was opening fhe daabase with the Titanium. Database.openFile method. Once i uses titanium.Database.open everything worked. Also, to serialize a variable to be written to the db, JSON.stringify(JSON.stringify(object)) seemed to work; and JSON.parse(JSON.parse(objectstring)) to deserialize. Dont know why found out by accident to be honest. Thank u v much for ur help – user1456713 Feb 10 '13 at 09:32