All
Accepted answers
Ashok Kumar Thangavel · Jan 26, 2024 go to post

Useful SQL JSON functions. I would like to include one more Aggregate function SQL JSON_ARRAYAGG into this list.

Create JSON object and push into JSON array based on condition or entire rows

select JSON_ARRAYAGG(JSON_OBJECT('Id':Id,'Name':Name,'phoneNumber':Phone,'State':state)) As JSON from Sample.Person

Ashok Kumar Thangavel · Jan 24, 2024 go to post

Thanks @Julius Kavay ,

Ok. Yes, both global and private global storage at the first time of initialization.  Why does it consumes minimum 8 byte(seems default and Is this Default byte size) regardless of single character(1 byte) in $storage. It consumes same or more than same byte when store in to global. Is this actual physical storage size(bytes) for the global.

 LEARNING>Set ^TEST="a"
 LEARNING>d ^%GSIZE
Global ^TEST
directory: c:\intersystems\irishealth\mgr\learning\
Page: 1                           GLOBAL SIZE                        24 Jan 2024
                                                                        11:26 AM
      Global        Blocks       Bytes Used  Packing   Contig.
      --------    --------  ---------------  -------   -------
      TEST               1               12      0 %         0
      TOTAL         Blocks       Bytes Used  Packing   Contig.
      --------    --------  ---------------  -------   -------
                         1               12      0 %         0
                                        <RETURN> to continue or '^' to STOP:
Ashok Kumar Thangavel · Jan 21, 2024 go to post

Hello @Andy Stobirski 

Have you created a web application? If not, You have to create a web application. Configure the necessary details such as namespace, url add your PCRest.disp in dispatch class and assign roles. Save the application and call your RESTFul api from postman.

/// Says Hello
ClassMethod Hello() As %Stream.Object
{
	return {"status":"ok","message":"working"}
}

Ashok Kumar Thangavel · Dec 22, 2023 go to post

Yes Of course, We can store the object id's directly to the list of object property by using ( ex: $lb($lb("1"),$lb("2")) )  it. However we stored the values through objects. Basically the basic behaviour of storing the primary object automatically stores it's reference objects by default(DeepSave).So, I don't need to save multiple objects manually. Eventually it revokes both primary and reference object in failure state. No transactions involved in the code logic. If I save the secondary objects before storing the primary creates discrepancy in my data, Incase of failure. I thought to implement the same flow via SQL if possible.

Ashok Kumar Thangavel · Dec 21, 2023 go to post

Hello @David Hockenbroch

No, I've a object property as a list like Property CodeTable As list of Sample.CodeTable in my class definition. and inserting values through object refer the code below. Now I expect to insert list of object via SQL instead of object.

Class Samples.NewClass Extends %Persistent
{

Property Name As %String;

Property codetable As list Of Sample.CodeTable;

Property mycList As list Of %String;

Property Notes As %Stream.GlobalCharacter;

ClassMethod c1()
{
	set obj = ..%New()
	set codetable = ##class(Sample.CodeTable).%New()
	set codetable.Code="V"
	do obj.codetable.Insert(codetable) ;insert my Code table object as a list of 
	set codetable = ##class(Sample.CodeTable).%New()
	set codetable.Code="X"
	do obj.codetable.Insert(codetable)
	set tSC = obj.%Save()
}
}
Ashok Kumar Thangavel · Dec 21, 2023 go to post

The form-data is actually resides in MimeData property and the query parameters are in Data property in %request object. You can you use GetMimeData method to retrieve a single from-data or use NextMimeData  for series fetch and The form-data value is in the form of stream. Get method is used to get query parameters from the %request.


ClassMethod SampleCode() As %Status
{
	write "query parameter: ",%request.Get("testParam")
	set mime=""
	while 1{
		set mime = %request.NextMimeData(mime) q:mime=""
		write mime,!
	}
	return $$$OK
}
Ashok Kumar Thangavel · Dec 19, 2023 go to post

Hello @Nimisha Joseph 

Can you try the below

XData BPL [ XMLNamespace = "http://www.intersystems.com/bpl" ]
{
<process language='objectscript' request='Ens.Request' response='Ens.Response' height='2000' width='2000' >
<context>
<property name='Forename' type='%String' instantiate='0' >
<parameters>
<parameter name='MAXLEN'  value='50' />
</parameters>
</property>
<property name='Surname' type='%String' instantiate='0' >
<parameters>
<parameter name='MAXLEN'  value='50' />
</parameters>
</property>
<property name='ReportDiscipline' type='%String' instantiate='0' >
<parameters>
<parameter name='MAXLEN'  value='50' />
</parameters>
</property>
<property name='tResult' type='%SQL.StatementResult' instantiate='0' />
</context>
<sequence xend='200' yend='950' >
<trace name='TEMP trace element' value='"In business process "_request.StringValue' xpos='200' ypos='250' />
<assign property="context.ReportDiscipline" value="request.StringValue" action="set" xpos='200' ypos='350' />
<trace value='"ReportDiscipline value: "_context.ReportDiscipline' xpos='200' ypos='450' />
<assign property="context.tResult" value="##Class(%SQL.Statement).%ExecDirect(,&quot;select * from table&quot;)" action="set" xpos='200' ypos='550' />
<while condition='context.tResult.%Next()' xpos='200' ypos='650' xend='200' yend='450' >
<assign property="context.surname" value="context.tResult.%Get(&quot;surname&quot;)" action="set" xpos='200' ypos='250' />
</while>
<trace value='"SQLCODE: "_SQLCODE' xpos='200' ypos='750' />
<trace value='"returned value: "_context.Forename' xpos='200' ypos='850' />
</sequence>
</process>
}
Ashok Kumar Thangavel · Dec 7, 2023 go to post

Hello @Denys Kondratiuk 

you can use below code to convert the response data to JSON object

ClassMethod test()
{
	set response = ##Class(%DynamicAbstractObject).%FromJSON(%request.HttpResponse.Data)
	set iter = response.%GetIterator() 
	while iter.%GetNext(.key,.obj) { ;looping the array object
		Write "Name: ", obj.Name, !
        Write "Gender: ", obj.Gender, !
	}
}
Ashok Kumar Thangavel · Nov 30, 2023 go to post

Hello @Michael Wood 

If those values are not part of the response. Then don't need to worry about it. Code will skip by default. Incase if the property is not defined . Then you have to declare the %JSONIGNOREINVALIDFIELD value as 1 to prevent from that ERROR #9406: Unexpected format for value of field.

Parameter %JSONIGNOREINVALIDFIELD As BOOLEAN = 1;
Ashok Kumar Thangavel · Nov 30, 2023 go to post

Hello @Enrico Parisi 

That's right, However Instead of creating a custom datatype to fix this. create/use the auto generated method for that property "OPDTLogicalToXSD" and implement the code logic.

ClassMethod OPDTLogicalToXSD(%val As %TimeStamp) As %String [ ServerOnly = 1 ]
{
	Set %val=##class(%Library.TimeStamp).LogicalToXSD(%val)
	Quit $translate(%val,"TZ"," ")
}
Ashok Kumar Thangavel · Nov 30, 2023 go to post

Hello @water huang 

As You can create a class method with name of your property " OPDTLogicalToXSD" and add the code conversion for the datetime as mentioned by @Enrico Parisi at XML export time.

It's suitable for both XML and JSON adaptor.

Sample code.

Class Samples.NewClass2 Extends (%Persistent, %Populate, %JSON.Adaptor, %XML.Adaptor)
{
Property OPDT As %Library.DateTime;

ClassMethod OPDTLogicalToXSD(%val As %TimeStamp) As %String [ ServerOnly = 1 ]
{
	Set %val=##class(%Library.TimeStamp).LogicalToXSD(%val)
	Quit $translate(%val,"TZ"," ")
}
}

output

 <NewClass2><OPDT>2023-11-30 11:07:02</OPDT></NewClass2>
Ashok Kumar Thangavel · Nov 29, 2023 go to post

Hello @Michael Wood

It won't allow to import the array values directly from the response/payload If you're using %JSONImport  in %JSON.Adaptor due to the datatype validation while import. So, You may define the list of properties in separate class definition and use that Property Messages As list of class if the array has object values and It allows to import. Or if it's literal string inside an array then it allows without any issues. 

Sample: Literal string values inside array

Class Samples.NewClass2 Extends (%Persistent, %Populate, %JSON.Adaptor)
{

Property email As list Of %String;

Parameter %JSONIGNOREINVALIDFILED = 1;

Property FirstName As %String;

Property LastName As %String;

ClassMethod test()
{
    set json={"FirstName":"test","LastName":"lastname","email":["test@gmail.com","test2@gmail.com"]}
    set obj= ..%New()
    set st= obj.%JSONImport(json)
    if $$$ISERR(st) write $SYSTEM.OBJ.DisplayError(st)
    write obj.%JSONExport()
}

}

output

LEARNING>d ##Class(Samples.NewClass2).test()
{"email":["test@gmail.com","test2@gmail.com"],"FirstName":"test","LastName":"lastname"}
Ashok Kumar Thangavel · Nov 28, 2023 go to post

Hi @Nimisha Joseph 

The query is executed directly when you call. Check this SQL.Statement

set tResult = ##class(%SQL.Statement).%ExecDirect(,"select name,age from Sample.Person")

"And result will be stored in tResult variable. Try executing the below in trace or assign and see 

write tResult.%SQLCODE
write tStatement