Welcome!

Welcome to the official BlackBerry Support Community Forums.

This is your resource to discuss support topics with your peers, and learn from each other.

inside custom component

Adobe AIR Development

Reply
Developer
Posts: 58
Registered: ‎02-13-2009
My Device: Not Specified
Accepted Solution

Using SQLite to store JSON object

Hi, 

 

Can anyone guide on how to store a JSON object returned after using the Adobe provide JSON parser which returns the native object 

 

like

 

resultSmiley Surprisedbject = JSON.decode(data)

 

Where data is a JSON string.

 

The returned object 'result' is a complex object but would like to store this object as it is in the SQLite database.

 

I have tried BLOB for the column type but does not seem to be working to store this object. Ideally I would prefer to use this way to avoid major code revamp as I am currently using the SharedObject method. 

 

Any ideas??

 

 

Developer
Posts: 6,541
Registered: ‎10-27-2010
My Device: HTC One, PlayBook, LE Z10, DE Q10
My Carrier: Verizon

Re: Using SQLite to store JSON object

Can you just store the data string as a BLOB?

Developer
Posts: 6,473
Registered: ‎12-08-2010
My Device: PlayBook, Z10
My Carrier: none

Re: Using SQLite to store JSON object

I would have thought you could just use a TEXT field instead of BLOB, as JSON data does not contain any binary data, and -- this is the part I'm not sure about -- I would expect the flash.data routines to encode Strings (which are Unicode) as UTF-8 when writing to the database.  Has anyone checked this?


Peter Hansen -- (BB10 and dev-related blog posts at http://peterhansen.ca.)
Author of White Noise and Battery Guru for BB10 and for PlayBook | Get more from your battery!
Developer
Posts: 6,541
Registered: ‎10-27-2010
My Device: HTC One, PlayBook, LE Z10, DE Q10
My Carrier: Verizon

Re: Using SQLite to store JSON object

You're right, a TEXT field should be large enouph.  Worse case, yo spit it across multiple records and splice it back together if the content is much larger than what TEXT can hold.

Developer
Posts: 6,473
Registered: ‎12-08-2010
My Device: PlayBook, Z10
My Carrier: none

Re: Using SQLite to store JSON object

I just confirmed that in fact with a TEXT column, a String containing Unicode will be encoded as UTF-8 when stored in the database, so there's no need for BLOB when storing JSON-encoded data.

 

Also note that with SQLite, the limits for TEXT and BLOB are the same, currently 2,147,483,647 bytes (i.e. 2**31 - 1), so you can store pretty much anything there. 


Peter Hansen -- (BB10 and dev-related blog posts at http://peterhansen.ca.)
Author of White Noise and Battery Guru for BB10 and for PlayBook | Get more from your battery!
Developer
Posts: 58
Registered: ‎02-13-2009
My Device: Not Specified

Re: Using SQLite to store JSON object

thanks jtegen,Peter for your valuable feedback.

 

You all have a point. I have used BLOB which also allows text data for storing JSON strings. I could easily very well switch to TEXT storage class.

 

Here is how I have resolved the issue.

 

Basically the text string I get back is a complex object (list of parent objects containing several child objects and so on) after decoding the JSON string. What I am interested is storing only parts of the  parent object i.e several child objects in various rows. 

 

So all it involved was to modify the persistence layer without making any modification to the UI layer (that is independent of what storage mechanism I use) 

 

Right at the time of saving the JSON object data in SQLite database, I use JSON.encode to convert the native object to a string but also ensure I use the escape function to escape some characters such as apostrophies etc which will be an issue if your data contains such characters. Then while retrieving the data and right before storing in the DataProvider, I used JSON.decode but before doing so, I have used unescape function to retain the data in its original format to be presented to the UI.

 

This method is working great for now, although I am not sure about the performance hit as there will be some here as I am performing the encode and decode of JSON strings more often. Also I would expect a performance hit as I have heard SharedObject is much more efficient than SQLite but its a tradeoff, as I want to ensure I can handle the large amounts of data which could be stored on the device over time. 

Developer
Posts: 6,473
Registered: ‎12-08-2010
My Device: PlayBook, Z10
My Carrier: none

Re: Using SQLite to store JSON object

 


samasrinivas wrote:

You all have a point. I have used BLOB which also allows text data for storing JSON strings. I could easily very well switch to TEXT storage class.


There's probably no downside to using BLOB, because SQLite doesn't really differentiate, but if nothing else for maintainability it would make more sense to me to use TEXT, since JSON data is purely textual.  No big deal either way.

 


samasrinivas wrote: 

Right at the time of saving the JSON object data in SQLite database, I use JSON.encode to convert the native object to a string but also ensure I use the escape function to escape some characters such as apostrophies etc which will be an issue if your data contains such characters.


The JSON.encode routine will initially escape any characters that it has to in order to make it so the encoded data can always be "round-tripped" back to the original objects, so having apostrophes or such in your original data won't lead to any problems there.

 

As for the SQL side of things, if you properly use parameters in statements you also should not need to escape anything if it's just to avoid conflicts with the SQL syntax.  Using the parameter feature has many benefits, including letting you avoid any risk of a "SQL injection" attack.  Even if this isn't necessary for your particular situation (because none of the data is in any way user-entered or retrieved from an untrusted system) it's critical that developers understand the risk and know when and how to avoid such problems.

 

If it's neither of the above that was concerning you, I can't think why you would need the extra escape operation.


Peter Hansen -- (BB10 and dev-related blog posts at http://peterhansen.ca.)
Author of White Noise and Battery Guru for BB10 and for PlayBook | Get more from your battery!
Developer
Posts: 58
Registered: ‎02-13-2009
My Device: Not Specified

Re: Using SQLite to store JSON object

Peter,

-----------------------------------------------------------------------------------------------------------------------------------

peter9477 wrote:

The JSON.encode routine will initially escape any characters that it has to in order to make it so the encoded data can always be "round-tripped" back to the original objects, so having apostrophes or such in your original data won't lead to any problems there.

------------------------------------------------------------------------------------------------------------------------

 

Unless I am missing something, after I perform JSON.encode, I am storing the resulting string in one of the columns in the SQLite DB. But I was getting exceptions complaining about apostropies (which I have conformed with the trace that indeed the data contains an apostrophy). And eventually the data is not being persisted. So I had to use the escape(JSON.encode(object)) to avoid this issue.

 

-----------------------------------------------------------------------------------------------------------------------------

peter9477 wrote:

As for the SQL side of things, if you properly use parameters in statements you also should

not need to escape anything if it's just to avoid conflicts with the SQL syntax. 

Using the parameter feature has many benefits, including letting you avoid any risk

of a "SQL injection" attack.  Even if this isn't necessary for your particular

situation (because none of the data is in any way user-entered or retrieved from an

untrusted system) it's critical that developers understand the risk and know when and

how to avoid such problems.

---------------------------------------------------------------------------------------------------------------------------------

 

I did not try this approach and sounds like this is the proper way to avoid any potential conflicts with SQL syntax. As the complexity of application grows, I would like to revise my code to use this technique next time. Thanks for sharing this.

Developer
Posts: 6,473
Registered: ‎12-08-2010
My Device: PlayBook, Z10
My Carrier: none

Re: Using SQLite to store JSON object

 


samasrinivas wrote: 

Unless I am missing something, after I perform JSON.encode, I am storing the resulting string in one of the columns in the SQLite DB. But I was getting exceptions complaining about apostropies (which I have conformed with the trace that indeed the data contains an apostrophy). And eventually the data is not being persisted. So I had to use the escape(JSON.encode(object)) to avoid this issue.


If you are merely appending/concatenating the JSON string to a SQL statement, something like the following, then you would see that error, because of course the JSON data probably contains lots and lots of apostophes.

 

 

// BAD example 
// assumes myobject and myconn already exist
var json_text:String = JSON.encode(myobject);
var stmt:SQLStatement = new SQLStatement();
stmt.sqlConnection = myconn;
stmt.text = "INSERT INTO test VALUES ('" + json_text + "')";
stmt.execute();

Encoding that once more with escape(json_text) is really unnecessary though, and will not only take extra time but will expand the size of the text a fair bit. 

 

Here's what you really ought to do instead.  (I don't think any programmer should do the above, as it's very bad practice and has led directly to thousands of serious security breaches.  True, using escape() avoids that, but most of the time people aren't forced into doing that the way you are with the output of JSON.encode.)

 

 

// GOOD example
// assumes myobject and myconn already exist var json_text:String = JSON.encode(myobject); var stmt:SQLStatement = new SQLStatement(); stmt.sqlConnection = myconn; stmt.text = "INSERT INTO test VALUES (:bar)"; stmt.parameters[':bar'] = json_text; stmt.execute();

As you can see, it's really quite trivial to do it that way, and probably easier than always trying to get your quotation marks right.  Make your life easier, your code safer and faster, and your storage requirements smaller, and use parameters.

 


Peter Hansen -- (BB10 and dev-related blog posts at http://peterhansen.ca.)
Author of White Noise and Battery Guru for BB10 and for PlayBook | Get more from your battery!
Highlighted
Developer
Posts: 6,541
Registered: ‎10-27-2010
My Device: HTC One, PlayBook, LE Z10, DE Q10
My Carrier: Verizon

Re: Using SQLite to store JSON object

For another thought, I typically take user supplied text and base64 encode it before adding it to the DB.  this avoids those kinds of problems.  The drawback is a slight performance hit, but minor compared to SQL problems with the data.