[Dancer-users] storing JSON in a db for later use

[Puneet Kishor]

Actually, I had pursued this problem a while back on this list, received 
helpful suggestions, implemented a solution, and all was working. 
Except, recently (a few days ago) when it stopped working. I can't quite 
pinpoint what caused the program to break, but I am now writing to 
elicit suggestions afresh so I can create a robust solution.

I am deploying all the nice touches that Dancer offers, namely,

	use Dancer::Serializer::Mutable;
	use Dancer::Plugin::REST;
	prepare_serializer_for_format;

I have a longish JSON object in the browser that I stringify and send 
back to the server with the help of jQuery. I store this string into a 
sqlite db. The string looks like


[{"data":"{"year":"1900","harvest_area":"126800.00", .. (very long string)

At a later time, I retrieve the above value, send it back to the browser 
where it is reconstructed back into a JSON object which can be used. 
Except, ever since I started using the automatic serializer, the darn 
thing has stopped working. The JSON object is not fully reconstructed. 
The above string is received by the browser as

[{"data":"{\"year\":\"1900\",\"harvest_area\":\"126800.00\", .. (very 
long string)

The automatically generated headers received are

Request Header
--------------
Accept:application/json, text/javascript, */*; q=0.01

Response Header
--------------
Content-Type:text/xml; charset=UTF-8


I think it is those escaped double quotes in the response (\"year\") are 
creating trouble for me. The value of the "data" key in the JSON object 
should be another dataset, but comes off as a string instead.

As I said above, this was working when I didn't use the automatic 
serializers (D::S::Mutable and D::P::REST with 
`prepare_serializer_for_format`). I used to retrieve the values from the 
db, transform them with from_json() to a Perl data structure, created a 
data structure to return, convert it to a string with to_json() and send 
it back to the browser.

     $sth = __PACKAGE__->SUPER::db()->prepare(qq{
         SELECT id, name, data, poly
         FROM foo
     });
     $sth->execute;

     my @str = ();
     while (my ($id, $name, $data, $poly) = $sth->fetchrow_array) {
         push @str, {
             id   => $id,
             name => $name,
             data => from_json($data),
             poly => from_json($poly)
         }
     }

     return to_json(\@str);

Now I don't use the from_json() and the to_json() calls. Needless to 
say, if I use those calls, I get an error on the server side that

{"error":"malformed JSON string, neither array, object, number, string 
or atom, at character offset 0 (before \"(end of string)\") at (eval 64) 
line 163.\n"}

and, if I don't use from_json() and to_json(), I get

[{"data":"{\"year\":\"1900\",\"harvest_area\":\"126800.00\", ..

which stops my application on the browser side.

So, I am stuck.

If the above is too confusing, I ask this simple question --

How do I reliably send a stringified JSON object to Dancer (I use the 
canonical json2.js from Crockford's site), store it in a sqlite table, 
retrieve it, have Dancer stringify it, send it back to the browser, and 
reconstruct the original JSON object 100%? I want round-trip browser to 
db back to the browser.

Suggestions?



-- 
Puneet Kishor http://punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Fellow http://creativecommons.org/about/people/fellows#puneetkishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---------------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
===========================================================================