Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.javascript > #22001

Re: JSON to access error

Show all headers | View raw

On Wed, 13 Nov 2013 12:04:34 +1100, Andrew Poulos wrote:

> I'm trying to update an MS Access db using ASP and JScript with this
> SQL. The value for "notes" is a JSON string posted via AJAX.
> 
> UPDATE tbl_Responses SET notes=
> '{
>      "loc": "",
>      "suspend_data": "{'assess':{},'limit':1,'loc':'','progress':'1'}",
>      "comments": "",
>      "completion": "completed",
>      "scoEntry": "resume",
>      "userID": 11,
>      "lessonID": 2
> }'
> WHERE ResponseID=67;

> Why is the "suspend_data" line causing the error?

Because the suspend data line is inside a single quoted string, and it 
contains single quotes, the first one of which terminates the single 
quoted string.

So:

UPDATE tbl_Responses SET notes = '{ "loc": "", "suspend_data": "{'

assess <-- I guess this is the failure point, because the sql doesn't 
recognise the word assess in this position as any sort of expected 
keyword / value / function.

But if assess was a valid access sql function, the next problem is no () 
containing an argument list, even an empty one, after it.

After assess, you have the following (as far as ms access sql is 
concerned series of apparent tokens and strings:

':{},'
limit
':1,'
loc
':'
','
progress
':'
1

and then a nice long string:

'}", "comments": "", "completion": "completed", "scoEntry": "resume", 
"userID": 11, "lessonID": 2 }'

followed by a where clause:

WHERE ResponseID=67;

Which altogether amount to something that the sql parser is turning it's 
nose up at because it doesn't fit the expected form of any sql statement 
the parser knows how to process.

As others have already said, you should probably convert the json object 
into some normalised data form and store that data in the database, 
rather than the json string.

You may need multiple tables depending on (a) whether you can have 
multiple different notes.suspend_data records associated with the same 
notes record, and (b) whether you can have multiple 
notes.suspend_data.assess records associated with the same 
notes.suspend_data record.

However that's getting into database normalization, which is probably 
going way off topic here.

Note that it may be possible to do the following, but it's not guaranteed 
to work:

sql = "UPDATE tbl_Responses SET notes = '{" 
      + Replace( json_string, "'", "''" ) 
      + "}' WHERE ResponseID=67;";

Which should replace all the ' inside the json string with '', which, if 
various web resources are to believed, will stop them being seen as sql 
string delimeters.

Not sure if you need the {} wrapper round the json string in there or 
not, or whether your json string already contains it, and this assumes 
that your asp is written in visual basic.

-- 
Denis McMahon, denismfmcmahon@gmail.com

Back to comp.lang.javascript | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

JSON to access error Andrew Poulos <ap_prog@hotmail.com> - 2013-11-13 12:04 +1100
  Re: JSON to access error Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2013-11-13 02:45 +0100
    Re: JSON to access error "Michael Haufe (TNO)" <tno@thenewobjective.com> - 2013-11-12 19:05 -0800
  Re: JSON to access error Denis McMahon <denismfmcmahon@gmail.com> - 2013-11-14 00:56 +0000

csiph-web