AllAccepted answers
Guillaume Rongier · Sep 8, 2021

Hi Rubén,

Another proposition on IRIS 2021.1+ can be this one with the use of the new window (OVER) function :

ClassMethod getPersonsPagWindow(iAge As %Integer, sortField As %String = 1, sortOrder As %String = 2, pageSize As %String = 20, pageIndex As %String = 1) As %DynamicObject
{
    set out = []
    set vFrom = ((pageIndex -1 ) * pageSize)+1
    set vTo = vFrom + (pageSize-1)

    set sql = "SELECT * "_
                "FROM ( SELECT persons.* "_
                "        , ROW_NUMBER() OVER (ORDER By "_sortField_" "_ $CASE(sortOrder,1:"ASC",2:"DESC",:"ASC")_
                "    ) rn "_
                "        FROM Sample.Person persons where Age > ? "_
                "    ) tmp "_
                "WHERE rn between "_vFrom_" and "_vTo_" "_
                "ORDER By "_sortField_" "_ $CASE(sortOrder,1:"ASC",2:"DESC",:"ASC")

    Set rs=##class(%ResultSet).%New("%DynamicQuery:SQL")
    set sc = rs.Prepare(sql)
    set sc = rs.Execute(iAge) If $$$ISERR(sc) Do DisplayError^%apiOBJ(sc) Quit

    while rs.%Next() {
        Do out.%Push({
                "pid": (rs.%Get("ID")),
                "ssn" : (rs.%Get("SSN")),
                "lastname" : (rs.%Get("LastName")) ,
                "givenname":    (rs.%Get("GivenName")),
                "secondaryname":       (rs.%Get("SecondaryName")) ,
                "gender": (rs.%Get("Gender")),
                "age": (rs.%Get("Age") )
                })
    }

    set outJson = []
    Do outJson.%Push({
                "pageSize":(pageSize),
                "pageIndex":(pageIndex),
                "fromIndex":(vFrom),
                "toIndex":(vTo),
                "resultSet":(out)
                })
    return outJson
}

I bench the two solutions on a dataset of 100 000 rows without index with a result of 20 elements on page 1 and here are the results :

"getPersonsPag timed : 1,647 secondes"
"getPersonsPagWindow timed : 0,247 secondes"

I guess that the window function is faster because you don't have to fetch all the data in a global before paging.

Guillaume Rongier · Aug 19, 2021

Hi Gregor,

First off all, try to connect to MySql directly by a shell command :

echo "select 1" | isql -v my-connector

Expected response :

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select 1
+---------------------+
| 1                   |
+---------------------+
| 1                   |
+---------------------+
SQLRowCount returns 1
1 rows fetched

Where /etc/odbc.ini :

[my-connector]
Description           = MySQL connection to  database
Driver                = MySQL
Database              = example
Server                = localhost
User                  = example
Password              = example
Port                  = 3306
Socket                = /var/run/mysqld/mysqld.sock

and /etc/odbcinst.ini

[MySQL]
Description = ODBC for MySQL
Driver = /usr/local/lib/libmyodbc8a.so
Setup = /usr/local/lib/libmyodbc8w.so
FileUsage = 1

If you successfully connected to your mysql database, then you can use it in IRIS/Caché/Ensemble :

Here is an example with %SQLGatewayConnection

set gc=##class(%SQLGatewayConnection).%New()
set pDSN="my-connector"
set sc=gc.Connect(pDSN,"example","example")
set sc=gc.AllocateStatement(.hstmt) 
set pQuery= "select 1"
set sc=gc.Prepare(hstmt,pQuery)
set sc=gc.Execute(hstmt)
set sc=gc.Fetch(hstmt)
set sc=gc.GetData(hstmt, 1, 1, .val)
zw val
set sc=gc.CloseCursor(hstmt)
set sc=gc.Disconnect()

To go further check those links :
- https://docs.intersystems.com/irisforhealthlatest/csp/docbook/DocBook.UI...
- https://docs.intersystems.com/irisforhealthlatest/csp/docbook/DocBook.UI...

Or even better check this training, it's with an JDBC connector but most part is applicable has DSN will fit your odbc config.
- https://github.com/grongierisc/formation-template

Guillaume Rongier · Aug 19, 2021

Hi Eric,

First you are using &sql who is for internal SQL use : doc

If you want to do an external query to a remote database you can do it with Ensemble :

Include EnsSQLTypes
 
Class Batch.Example.SqlInsertOperation Extends Ens.BusinessOperation
{
 
Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";
 
Property Adapter As EnsLib.SQL.OutboundAdapter;
 
Parameter INVOCATION = "Queue";
 
Method SetResultSetView(pRequest As Ens.StringRequest, Output pResponse As Ens.StringResponse) As %Status
{
    set tStatus = $$$OK
    
    try{
                    
        set pResponse = ##class(Ens.StringResponse).%New()
    
        set SqlInsertView = "INSERT into ODS_Products (ID,ProductName,Date_Alimentation) values (?,?,TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'))"
 
        set param(1) = 1
        set param(1,"SqlType")=$$$SqlInteger
 
        set param(2) = ##class(%PopulateUtils).Name()
        set param(2,"SqlType")=$$$SqlVarchar
            
        set param(3) = $ZDATETIME($NOW(),3)
        set param(3,"SqlType")=$$$SqlVarchar
 
        set param = 3
            
        $$$ThrowOnError(..Adapter.ExecuteUpdateBatchParamArray(.nrows,SqlInsertView,.param))
                                
    }
    catch exp
    {
        Set tStatus = exp.AsStatus()
    }
 
    Quit tStatus
}
 
XData MessageMap
{
<MapItems>
    <MapItem MessageType="Ens.StringRequest">
        <Method>SetResultSetView</Method>
    </MapItem>
</MapItems>
}
 
}

Or with the %SQLGatewayConnection :

    //Create new Gateway connection object
   set gc=##class(%SQLGatewayConnection).%New()
   If gc=$$$NULLOREF quit $$$ERROR($$$GeneralError,"Cannot create %SQLGatewayConnection.")
       
   //Make connection to target DSN
   s pDSN="Samples"
   s usr="_system"
   s pwd="SYS"
   set sc=gc.Connect(pDSN,usr,pwd,0)
   If $$$ISERR(sc) quit sc
   if gc.ConnectionHandle="" quit $$$ERROR($$$GeneralError,"Connection failed")
       
   set sc=gc.AllocateStatement(.hstmt)
   if $$$ISERR(sc) quit sc
       
   //Prepare statement for execution
   set pQuery= "select * from Sample.Person"
   set sc=gc.Prepare(hstmt,pQuery)
   if $$$ISERR(sc) quit sc
     //Execute statement
   set sc=gc.Execute(hstmt)
   if $$$ISERR(sc) quit sc
Guillaume Rongier · Jun 23, 2021

It depends on what you are looking for.

To create applications to expose data on IRIS with custom APIs and make custom applications, you can use IRIS Studio and/or VsCode with the IRIS extension.

For database administration, you can use the management portal.

For 100% SQL administration, I recommend DbBeaver.

Guillaume Rongier · Jun 17, 2021

You are right Benjamin, the R gateway go through the Java gateway with two helper classes :

  • com.intersystems.rgateway.Helper
  • org.rosuda.REngine.Rserve

An example can be found here :

If I may, I prefer the approach of Eduard for the R gateway : https://github.com/intersystems-community/RGateway who by pass the java gateway and directly use socket connection to the R Server.

@Eduard Lebedyuk : you are right no documentation a this time for the R Gateway.

Guillaume Rongier · May 26, 2021

You can't work directly in HSLIB, you have to create your own namespace to work with.

In a terminal :

zn "HSLIB"
// Install a Foundation namespace and change to it
Do ##class(HS.HC.Util.Installer).InstallFoundation("NEW_NAMESPACE")
zn "NEW_NAMESPACE"

If you are not familiar with Namespace, HSLIB, Ensemble first check for training here :

https://learning.intersystems.com/course/view.php?id=243

Guillaume Rongier · May 25, 2021

Hi Xiong,

To convert FHIR R4 to SDA you have to use this helper class :

HS.FHIR.DTL.Util.HC.FHIR.SDA3.Process

This class take in input a message of this kind : HS.FHIRServer.Interop.Request or HS.Message.FHIR.Request.

So if you want to convert an json FHIR file to SDA, you have to read the file from a business service cast you file to one of this message and send it to the helper class.

Here is and Business service example of reading fhir json files :

Class BS.FHIRFileService Extends Ens.BusinessService
{

Parameter ADAPTER = "EnsLib.File.InboundAdapter";

Property TargetConfigNames As %String(MAXLEN = 1000) [ InitialExpression = "BusinessProcess" ];

Parameter SETTINGS = "TargetConfigNames:Basic:selector?multiSelect=1&context={Ens.ContextSearch/ProductionItems?targets=1&productionName=@productionId}";

Method OnProcessInput(pDocIn As %RegisteredObject, Output pDocOut As %RegisteredObject) As %Status
{
    set status = $$$OK

    try {

        set pInput = ##class(HS.FHIRServer.Interop.Request).%New()
        set tQuickStream = ##class(HS.SDA3.QuickStream).%New()
        do tQuickStream.CopyFrom(pDocIn)
        set pInput.QuickStreamId= tQuickStream.Id


        for iTarget=1:1:$L(..TargetConfigNames, ",") {
            set tOneTarget=$ZStrip($P(..TargetConfigNames,",",iTarget),"<>W")  Continue:""=tOneTarget
            $$$ThrowOnError(..SendRequestSync(tOneTarget,pInput,.pDocOut))
        }
    } catch ex {
        set status = ex.AsStatus()
    }

    Quit status
}

}

You can find more information from this documentation :

https://docs.intersystems.com/irisforhealthlatest/csp/docbook/Doc.View.c...

Guillaume Rongier · May 18, 2021

Hi Lucas,

A simple solution to you question can be this :

Property numDossiersMER As list Of %Integer(SQLPROJECTION = "table/column", STORAGEDEFAULT = "array");

Index numDossiersMERIdx On numDossiersMER(ELEMENTS);

With those parameters you can achieve :

  • 'As list Of %Integer' allows you to use the Insert() in ObjectScript and 'for some %element' in SQL command
  • 'SQLPROJECTION = "table/column"' allows you to display the table as a column (note, the column does not appear in a select * it must be specified : select numDossierMER, numDossiersMER from User_TestList_Data.Titre )
  • 'STORAGEDEFAULT = "array"' allows a separate table for the normalized representation
  • 'Index numDossiersMERIdx On numDossiersMER(ELEMENTS);' bring the ability to use index on values with this SQL query :
select numDossierMER, numDossiersMER from User_TestList_Data.Titre
where
for some %element(numDossiersMER) (%Value in (345))
Guillaume Rongier · Apr 29, 2021

Hi Cindy,

To deploy your services, routes and plugins to another environment you can use CI/CD with postman (newman)

Some examples here :

Another possibility is to use deck (decK helps manage Kong’s configuration in a declarative fashion) from kong

I haven't tried this one, I can't give you feedback on it.

Guillaume Rongier · Apr 16, 2021

I tried your module as a replacement for the Manifest installer, see this example:

https://github.com/grongierisc/intersystems-iris-dev-template

However, I'm stuck for the part with ZPM.
The problem is, with your module, I can create a database, its rights, etc., but how to tell ZPM to load the classes in the freshly installed NameSpace?


What are the possible solutions?

We have today many options to load configuration IRIS, are they in competition?

How to federate these modules who are all complementary?

Guillaume Rongier · Apr 12, 2021

If I understand correctly, you want to replace an HTTP Header basic auth with a bearer token.
If the bearer token is static, you can implement a solution like this:
- https://github.com/grongierisc/iam-training/tree/training#6-third-add-ou...

If it is dynamic, I think you will have to develop your own plug-in:
- https://github.com/grongierisc/iam-training/tree/training#11-plugins

A good base to start working on could be this plugin:
- https://github.com/grongierisc/kong-plugin-jwt-crafter