Hi!

I am not sure if I understood your questions. But here is an explanation that may help you...

If you want to run a SQL query filtering by a date

Let's take Sample.Person class on the SAMPLES namespace as an example. There is a DOB (date of birth) field of type %Date. This stores dates in the $Horolog format of Caché (an integer that counts the number of dates since 12/32/1940.

If your date is in the format DD/MM/YYYY (for instance), you can use TO_DATE() function to run your query and convert this date string to the $Horolog number:

select * from Sample.Person where DOB=TO_DATE('27/11/1950','DD/MM/YYYY')

That will work independently of the runtime mode you are on (Display, ODBC or Logical).

On the other hand, if you are running your query with Runtime Select Mode ODBC, you could reformat your date string to the ODBC format (YYYY-MM-DD) and don't use TO_DATE():

select * from Sample.Person where DOB='1950-11-27'

That still is converting the string '1950-11-27' to the internal $Horolog number that is:

USER>w $ZDateH("1950-11-27",3)

40142

If you already has the date on the internal $Horolog format you could run your query using Runtime Select Mode Logical:

select * from Sample.Person where DOB=40142

You can try these queries on the management portal. Just remember changing the 

If you are using dynamic queries with %Library.ResultSet or %SQL.Statement, set the Runtime Mode (%SelectMode property on %SQL.Statement) before running your query.

If you want to find records from a moving window of 30 days

The previous query brought, on my system, the person "Jafari,Zeke K.".  He was born on 1950-11-27. The following query will bring all people that was born on '1950-11-27' and 30 days before '1950-11-27'. I will use DATE_ADD function to calculate this window. I have also selected ODBC Runtime Select Mode to run the query:

select Name, DOB from Sample.Person where DOB between DATEADD(dd,-30,'1950-11-27') and '1950-11-27'

Two people will appear on my system: Jafari and Quixote. Quixote was born '1950-11-04'. That is inside the window. 

Moving window with current_date

You can use current_date to write queries such as "who has been born between today and 365 days ago?":

select Name, DOB from Sample.Person where DOB between DATEADD(dd,-365,current_date) and current_date

Using greater than or less than

You can also use >, >=, < or <= with dates like this:

select Name, DOB from Sample.Person where DOB >= DATEADD(dd,-365,current_date) 

Just be careful with the Runtime Select Mode. The following works with ODBC Runtime Select Mode, but won't work with Display or Logical Mode:

select Name, DOB from Sample.Person where DOB >= DATEADD(dd,-30,'1950-11-27') and DOB<='1950-11-27'

To make this work with Logical Mode, you would have to apply TO_DATE to the dates first:

select Name, DOB from Sample.Person where DOB >= DATEADD(dd,-30,TO_DATE('1950-11-27','YYYY-MM-DD')) and DOB<=TO_DATE('1950-11-27','YYYY-MM-DD')

To make it work with display mode, format the date accordingly to your NLS configuration. Mine would be 'DD/MM/YYYY' because I am using a spanish location.

This is a quick an dirty code I just wrote that can convert simple JSON strings to XML. Sometimes, the JSON will be simple enough for simple code like this... I am not a JSON expert but maybe this can be a good starting point for something better.

This will work only on Caché 2015.2+.

Call the Test() method of the following class:

Class Util.JSONToXML Extends %RegisteredObject
{

ClassMethod Test()
{
    Set tSC = $System.Status.OK()
    Try
    {
        Set oJSON={"Prop1":"Value1","Prop2":2}
        Set tSC = ..JSONToXML(oJSON.%ToJSON(), "Test1", .tXML1)
        Quit:$System.Status.IsError(tSC)
        Write tXML1
        
        Write !!
        Set oJSON2={"Prop1":"Value1","Prop2":2,"List":["Item1","Item2","Item3"]}
        Set tSC = ..JSONToXML(oJSON2.%ToJSON(), "Test2", .tXML2)
        Quit:$System.Status.IsError(tSC)
        Write tXML2
        
        Write !!
        Set oJSON3={
                "name":"John",
                "age":30,
                "cars": [
                    { "name":"Ford", "models":[ "Fiesta", "Focus", "Mustang" ] },
                    { "name":"BMW", "models":[ "320", "X3", "X5" ] },
                    { "name":"Fiat", "models":[ "500", "Panda" ] }
                ]
             }
        Set tSC = ..JSONToXML(oJSON3.%ToJSON(), "Test3", .tXML3)
        Quit:$System.Status.IsError(tSC)
        Write tXML3

    }
    Catch (oException)
    {
        Set tSC =oException.AsStatus()
    }
    
    Do $System.Status.DisplayError(tSC)
}

ClassMethod JSONToXML(pJSONString As %String, pRootElementName As %String, Output pXMLString As %String) As %Status
{
        Set tSC = $System.Status.OK()
        Try
        {
            Set oJSON = ##class(%Library.DynamicObject).%FromJSON(pJSONString)
            
            Set pXMLString="<?xml version=""1.0"" encoding=""utf-8""?>"_$C(13,10)
            Set pXMLString=pXMLString_"<"_pRootElementName_">"_$C(13,10)
            
            Set tSC = ..ConvertFromJSONObjectToXMLString(oJSON, .pXMLString)
            Quit:$System.Status.IsError(tSC)
            
            Set pXMLString=pXMLString_"</"_pRootElementName_">"_$C(13,10)
        }
        Catch (oException)
        {
            Set tSC = oException.AsStatus()
        }
        
        Quit tSC
}

ClassMethod ConvertFromJSONObjectToXMLString(pJSONObject As %Library.DynamicAbstractObject, Output pXMLString As %String) As %Status
{
        Set tSC = $System.Status.OK()
        Try
        {
            Set iterator = pJSONObject.%GetIterator()
            
            While iterator.%GetNext(.key, .value)
            {
                Set tXMLKey=$TR(key," ")
                Set pXMLString=pXMLString_"<"_tXMLKey_">"
                
                If value'=""
                {
                    If '$IsObject(value)
                    {
                        Set pXMLString=pXMLString_value
                    }
                    Else
                    {
                        Set pXMLString=pXMLString_$C(13,10)
                        If value.%ClassName()="%DynamicObject"
                        {
                            Set tSC = ..ConvertFromJSONObjectToXMLString(value, .pXMLString)
                            Quit:$System.Status.IsError(tSC)                            
                        }
                        ElseIf value.%ClassName()="%DynamicArray"
                        {
                            Set arrayIterator = value.%GetIterator()
                                        
                            While arrayIterator.%GetNext(.arrayKey, .arrayValue)
                            {
                                Set pXMLString=pXMLString_"<"_tXMLKey_"Item key="""_arrayKey_""">"
                                If '$IsObject(arrayValue)
                                {
                                    Set pXMLString=pXMLString_arrayValue
                                }
                                Else
                                {                                    
                                    Set tSC = ..ConvertFromJSONObjectToXMLString(arrayValue, .pXMLString)
                                    Quit:$System.Status.IsError(tSC)                            
                                }
                                Set pXMLString=pXMLString_"</"_tXMLKey_"Item>"_$C(13,10)
                            }
                            Quit:$System.Status.IsError(tSC)
                        }
                    }
                }
                
                Set pXMLString=pXMLString_"</"_tXMLKey_">"_$C(13,10)
            } //While
        }
        Catch (oException)
        {
            Set tSC = oException.AsStatus()
        }
        
        Quit tSC
}

}

Here is the output:

Do ##class(Util.JSONToXML).Test()
<?xml version="1.0" encoding="utf-8"?>
<Test1>
<Prop1>Value1</Prop1>
<Prop2>2</Prop2>
</Test1>
 
 
<?xml version="1.0" encoding="utf-8"?>
<Test2>
<Prop1>Value1</Prop1>
<Prop2>2</Prop2>
<List>
<ListItem key="0">Item1</ListItem>
<ListItem key="1">Item2</ListItem>
<ListItem key="2">Item3</ListItem>
</List>
</Test2>
 
 
<?xml version="1.0" encoding="utf-8"?>
<Test3>
<name>John</name>
<age>30</age>
<cars>
<carsItem key="0"><name>Ford</name>
<models>
<modelsItem key="0">Fiesta</modelsItem>
<modelsItem key="1">Focus</modelsItem>
<modelsItem key="2">Mustang</modelsItem>
</models>
</carsItem>
<carsItem key="1"><name>BMW</name>
<models>
<modelsItem key="0">320</modelsItem>
<modelsItem key="1">X3</modelsItem>
<modelsItem key="2">X5</modelsItem>
</models>
</carsItem>
<carsItem key="2"><name>Fiat</name>
<models>
<modelsItem key="0">500</modelsItem>
<modelsItem key="1">Panda</modelsItem>
</models>
</carsItem>
</cars>
</Test3>

Hi!

If you are not using OS single sign-on, this shell script should do it:

#!/bin/bash

csession AUPOLDEVENS <<EOFF
SuperUser
superuserpassword
ZN "%SYS"
Do ^SECURITY
1
3




halt
EOFF

Where:

• SuperUser - Is your username
• superuserpassword - Is your SuperUser password

I have chosen SECURITY menu options 1, then option 3. Then I hit ENTER until I exited ^SECURITY routine and terminated the session with the halt command.

If you are using OS single sign-on, remove these two first lines since Caché won't ask for them.

The blank lines after number 3 are the ENTERs you enter to go up into the menu hierarchy until you exit.

The halt is necessary to avoid an error such as the following:

ERROR: <ENDOFFILE>SYSTEMIMPORTALL+212^SECURITY
%SYS>
<ENDOFFILE>
<ERRTRAP>

You can do more complex stuff with this technique such as validate errors and return unix error codes to your shell so that you can know if the operation was successful or not:

#!/bin/bash

csession INSTANCENAME <<EOFF
ZN "MYNAMESPACE"

Set tSC = ##class(SomeClass).SomeMethod()
If $System.Status.IsError(tSC) Do $System.Status.DisplayError(tSC) Do $zu(4,$j,1) ;Failure!

Do $zu(4,$j,0) ;OK!
EOFF

The $zu(4,$j,rc) will halt the session and return the return code on rc to your shell script. As you can notice, the Halt command is not necessary when using this $zu function.

I hope that helps!

Kind regards,

AS

Hi!

     Assuming you meant "BPL" (Business Process Language) instead of "DTL" (Data Transformation Langue):

     If you simply want your Business Operation to try forever until it gets it done:

•  On the BPL, make a synchronous call or make an asynchronous call with a sync activity for it
•  On the BO, set FailureTime=-1. Also, try to understand the "Reply Code Actions" setting of your Business Operation. You don't want to retry for all kinds of errors. You probably want to retry for some errors and failure for others. If you set FailureTime=-1 and your Reply Code  Actions decides to retry for that kind of error, it will retry forever until it gets it done. If your Reply code Actions decides to fail for other types of errors, it will return an error to your Business Process.
•  If you know that, for some errors, the BO will return a failure, protect the call you just did on your BPL with a scope action so you can capture this and take additional actions.

More about "Reply Code Actions" here.

Kind regards,

Amir Samary

Hi Eduard!

Here is a simple way of finding it out:

select top 1 TimeLogged from ens_util.log

where configname='ABC_HL7FileService' 

and SourceMethod='Start' 

and Type='4' --Info

order by %ID desc

Relative cost = 329.11
    Read bitmap index Ens_Util.Log.ConfigName, using the given %SQLUPPER(ConfigName), and looping on ID.

    For each row:
    - Read master map Ens_Util.Log.IDKEY, using the given idkey value.
    - Output the row.

The documentation is very nice at explaining it:

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

Hi!

You don't actually need to configure a certificate on your Apache or even to encrypt the communication between Apache and the SuperServer with SSL/TLS.

You can create a CSP Application that is Unauthenticated and give it privileges to do whatever your web services need to do (Application Roles - more info here). I would also configure a "Permitted Classes" with a pattern to only allow your specific web services to be called. I would also block CSP/ZEN and DeepSee on this CSP Application.

More info on configuring CSP Applications here.

Then, for each web service you want to publish on this application (that is mentioned on the Permitted Classes), you will create a Web Service Security Policy using an existing Caché Studio wizard for that (more info here).

The wizard will allow you to choose from a set of options and several variations for each option on securing your web service. You may choose from the combobox "Mutual X.509 Certificates Security". Here is the description for this option:

This policy requires all peers to sign the message body and timestamp, as well as WS-Addressing headers, if included. It also optionally encrypts the message body with the public key of the peer's certificate.

You can configure Caché PKI (Public Key Infrastructure) to have your own CA (Certificate Authority) and generate the certificates that your server and clients will use.

This guarantees that only a client that has the certificate given by you will be able to authenticate and call this web service. The body of the call will be encrypted. 

If you restrict the entry points of this "Unauthenticated" csp application using "Permitted Classes" and if these permitted classes are web services protected by these policies, you are good to go. Remember to give to this application the privileges (Application Roles) for your service to be able to run properly (privilege on the database resource, SQL tables, etc.).

This doesn't require a username token. If you still want to use a username/password token, you can require that using the same wizard. Here is an additional description that the wizard provides:

Include Encrypted UsernameToken: This policy may optionally require the client to send a Username Token (with username and password). The Username Token must be specified at runtime. To specify the Username Token, set the Username and Password properties or add an instance of %SOAP.Security.UsernameToken to the Security header with the default $$$SOAPWSPasswordText type.

If you decide to do that, make sure your CSP application is configure for authentication  "Password" and do not check "Unauthenticated". 

Also, don't forget to use a real Apache web server. My point is that you don't need to configure your apache or its connection to the super server with a SSL certificate for all this to work. Caché will do the work, not Apache. Apache will receive a SOAP call that won't be totally encrypted. But If you look into it, you will notice that the body is encrypted, the header includes a signed timestamp, the username/password token will be encrypted, etc, etc. So, although this is not HTTPS, the certificates are being used to do all sort of things in the header and the body of the call that will give you a lot more protection that plain HTTPS.

But please, don't get me wrong. You need HTTPS if you are building an HTML web application or if you are using other kinds of web services such as REST, that don't have all the alternative enterprise security provided by SOAP. SOAP can stand alone, secure, without HTTPS. Your web application can't.