· Oct 4, 2018

Can't save Json String to SQL Table

Hi everyone,

I need submit a object have a json string as property of class, but I get an issue, object can't save to SQL table if I take json data to json property. I try making a test class with only json property and get the same issue, no data row in SQL table after run ClassMethod . Some one know what's problem, please help me. Here my test class:

Class DEMO.TestDocSource Extends %Persistent

Property jsonStorer As %String;

ClassMethod saveJson()
Set jsonStr = "{"
_"""k0"": {"
_"""k2"":[ {"
_"""k4"": {"
Write "data test json: "_jsonStr

Set docObj = ##class(REX.CMS.TestDocSource).%New()
Set docObj.jsonStorer = jsonStr
Do docObj.%Save()

I try writting docObj.%Save() and debug to class, it would be write:

%ValidateObject+3^DEMO.TestDocSource.1Target has exited debugger

I'm not sure it will be useful, I don't know what does it mean!

Discussion (2)1
Log in or sign up to continue

Hi Hieu,

This is a very common problem that will trip you up the first few times you work with persistent strings.

The primary issue is that persistent strings have a default max length of 50, and in your example your are trying to save 164 characters.

When you call %Save(), the internal code will first check the object is valid, if it is not then it will return an error.

This is where you need a little more defensive code to catch these errors and bubble them up to your error handling code.

The %Save() method returns what we call a status code, it is either a 1 or a 0, if its a 0 then it was also include a status code error text.

So in your code you might do something like this

Set sc=docObj.%Save()
If $$$ISERR(sc) Write !,##class(%SYSTEM.Status).GetOneErrorText(sc)

I've assigned the status code returned by %Save() to a variable called sc. It's fairly common to see developers use the naming convention for a status code variable of sc or tSC, but you can of course call it anything your wish within reason.

If you just Write sc it will look a little garbled and would probably make out the line it failed which you spotted in your debugger. In my example I have used a macro called $$$ISERR(sc) which checks to see if the status code passed or failed, if it fails then it will use the %SYSTEM.Status class which has several helpful method to write out a more friendly display of the error. This will have something like "length longer than MAXLEN allowed of 50", which will then point you to the validation failure.

OK, so lets fix the problem by increasing the %String size. You can do this two ways, the first would be to use the code inspector, if your in studio its a tab on the side panel, click on it and select Property from the top left drop down box. You will then see the property listed, click on this and you will see a list of all the settings you can apply to this property. You will see MAXLEN, you can click on its value and increase it to say 10000. You will notice this will change the code to this...

Property jsonStorer As %String(MAXLEN = 10000);

The second way is to just manually type this setting to the property yourself.

When you now save the JSON it will pass validation and appear in your persistent store.

There are a couple more things to consider. What you probably want to do is bubble up these validation errors either via return values, or by throwing an error. So you might do...

Quit docObj.%Save()

You can then call your saveJson() method like so..

Set sc=##class(DEMO.TestDocSource).saveJson()

and then deal with the sc status code in your outer calling code. For instance if you were writing an HTML form to submit the JSON, you might return your own error response that would then be handled with an HTML form error message about exceeding the length.

You should also consider that there is a limitation to how long strings can be, in older versions of Caché without long string support enabled the limitation is around 32K, modern version with long string support is around 3.6MB, take a look here...

In general you will want to consider using streams instead of strings for properties that are larger than this.

The other consideration is that generating JSON by hand is going to trip you up in many ways. My guess is your are just testing out an idea here, but you will want to use a built in function to produce and correctly escape the JSON for you. If you search for JSON here on the DC site then you will see lots of different ways to achieve this safely.

On a final note you might want to go through the documentation a few times on Caché objects as this will explain some of these points in more detail, you will want to look at this section...

Good luck and welcome to the world of Caché persistence, it might seem a little quirky at first but you will learn to love it!!