All
Accepted answers
Guillaume Rongier · Sep 20, 2018 go to post

Hi Marc,

I'm intereded by your sample code for this kind of optimisation even if we have to use a different EnsLib.SQL.GatewayResultSet. Furthermore, we are currently looking for the same kind of optimisation in the other way, insert in JDBC batch mode. (https://www.tutorialspoint.com/jdbc/jdbc-batch-processing.htm) I get in touch with our Sales Engineer and we will continu to discuss this by email.

When we will have improvement, i'll update this theard.

Guillaume Rongier · Jan 22, 2019 go to post

Hi Benjamin,

In some cases we have to use Ensemble as an ETL not an ESB.

So we extract lot of data, transform and load them throw the JDBC SQL Adapter in EnsLib. To do so, we dont do it line by line throw messages but ResultSet by ResultSet (one ResultSet can have more than 500 000 lines and 30 colones). This pattern work well.

But we have start to have time treatment issue. Our process took more than 8 hours. When we analyze it, what it cost time is the select and insert treatment.

We solved the problem of select treatment with this post : https://community.intersystems.com/post/jdbc-large-query-optimisation

So now, we are looking for a way to improve insert time. Our guess is to implement throw JDBC SQL Adapter in EnsLib the java pattern with PrepareStatement.addBatch() then executeBatch().

Do you have any idea to improve the insert treatment ?

Guillaume Rongier · Jan 23, 2019 go to post

Thanks Marc,

Your code work great.

We gain a factor ten with this implementation.

Below our current implementation.

/// Structure of pParms is Caché Multidimensional Array/// Where :/// pParms indicate the number of row in batch/// pParms(integer) indicate the number of parameters in the row/// pParms(integer,integerParam) indicate the value of the parameter whose position is integerParam.Method ExecuteUpdateBatchParmArray(Output pNumRowsAffected As %Integer, pUpdateStatement As %String, pParms...) As %Status{set tStatus = $$$OKtry{set pSQLStatement=pUpdateStatement// JDBCGwy is an instance of the JDBC Gateway object. EnsLib.SQL.OutboundAdapter instantiates this automatically and stores a reference to it in ..%Connection.%JGProxy// Prepare the SQL statementSet pHS=..%JGProxy.prepareStatement(..%ConnHandle,pSQLStatement)// executeParametersBatch expects tArgs to be a $LIST, with the following format:// ParamCount, ParamSets, Type1, Param1, Type2, Param2, Type3, Param3, Type11,Param11… TypeNN,ParamNN// // ParamCount is the number of parameters the query expects (in this example 2) // ParamSets is the number of rows we will be inserting in this batch// Type1, Type2, ..., TypeN is an integer indicating the JDBC data type for the corresponding Param value (e.g. Param1, Param2, ..., ParamN)// Param1, Param2, ..., ParamN is the value for the query parameterset nbParam = pParms(1,1)set nbBatch = pParms(1)set $LIST(tArgs,1)=nbParam // How many parameters ("?") in itset $LIST(tArgs,2)=nbBatch // We will insert nbBatch rows in this batchset i = 2for k=1:1:nbBatch {for l=1:1:pParms(1,k){set i = i +1set $LIST(tArgs,i)=12 // The JDBC data type for varchar is 12set i = i +1set $LIST(tArgs,i)=pParms(1,k,l) // Value for column Field1VarChar}}// Perform the batch insert// tResultCodes is a $LIST of integers indicating success/failure for each row in the batchset tResultCodes = ..%JGProxy.executeParametersBatch(pHS,tArgs)//Todo, Read list set pNumRowsAffected = $LISTLENGTH(tResultCodes)set pNumRowsAffected = nbBatch // Remove Statement to avoid CURSOR Leaksset sc = ..%JGProxy.removeStatement(pHS)k tArgs}catch exp{Set tStatus = exp.AsStatus()}Quit tStatus}
Guillaume Rongier · Jul 29, 2019 go to post

Thanks for sharing this knowledge on ObjectScript language.

I haven't heard of SOLID Principle before, I'll apply it on my next code.

BTW : can you share your sildes for an easier walkthrough ?

Guillaume Rongier · Aug 1, 2019 go to post

Hi Julian,

On what version of InterSystems product you are working on ?

If it's on Ensemble, check out ENSDEMO namespaces, you will have an example of what you are looking for with this production : Demo.DICOM.Production.StorageLocal

If you are on Iris for health you can install EnsDemo with the help of this git : https://github.com/grongierisc/InstallEnsDemoHealth

Guillaume Rongier · Jan 9, 2020 go to post

Studio :

  • Like :
    • It's fast
    • Find in files directly on the server side
    • Intuitive
    • Debugging is not perfect but manageable
  • Dislike :
    • Only on windows
    • No easy to use with other third party file or software

Atelier :

  • Like :
    • Easy to use with source control
  • Dislike :
    • File synchronization with server especially BPL, DT, RecorMap
    • Complicate to configure
    • Slow

VS Code with Dmitriy's plugin

  • Like :
    • Modern UI
    • Crossplatform
    • Lite
    • Intuitive with no file synchronization concern
    • Fast
  • Dislike :
    • Can't edit, manage CSP files
    • No find in file on server side
    • Debugging not easy to use

Serenji :

I haven't tried it yet because you need to install some classes on the server side, but sound very promising for debugging.

Conclusion :

My main IDE is VSCode with Dmitriys's plugin and some times Studio for find in files, csp managment and debugging.

Do you expect to be writing code in a web-based editor five years from now?

Yes, why not, already a lot of people do it on Jupyter Notebook. 

Guillaume Rongier · May 31, 2017 go to post

Hi, Have you try to type your SQL parameters :

set param(i,"SqlType") = 12//12 for SQL_VarChar

I hope this will help you.

Guillaume Rongier · Jan 7, 2019 go to post

Hi Eduardo,

My comprehension of your use of Git and Ensemble is that all devs build on the same sever like this :

This is not the way to go.

I recommend to use this model where every devs have there own server :

Guillaume Rongier · Jan 22, 2019 go to post

My guess is that you are concatenating string with the operator "&" (AND) instead of "_".

If you do so, ObjectScript will cast your string as boolean false, the result of (false and false and false) equals 0, the result you see in your question.

Method PatientInfo(ID As%String) As%Status
{ #dim status as%Status=$$$OK
  SET myquery="SELECT GUID, IDType,IDValue FROM MergeHyland.TypeTwoDimesionCollection WHERE GUID ="_ID
  SET rset=##class(%ResultSet.SQL).%Prepare(myquery,.err,"")
    WHILE rset.%Next() {
    WRITE !,rset.GUID _ ":" _ rset.IDType_ ":" _ rset.IDValue
    }
  WRITE"End of data"
    return status
}
Guillaume Rongier · Aug 19, 2021 go to post

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

 

ClassBatch.Example.SqlInsertOperationExtendsEns.BusinessOperation
{

 

ParameterADAPTER = "EnsLib.SQL.OutboundAdapter";

 

PropertyAdapterAsEnsLib.SQL.OutboundAdapter;

 

ParameterINVOCATION = "Queue";

 

MethodSetResultSetView(pRequestAsEns.StringRequest, OutputpResponseAsEns.StringResponse) As%Status
{
    settStatus = $$$OK
    
    try{
                    
        setpResponse = ##class(Ens.StringResponse).%New()
    
        setSqlInsertView = "INSERT into ODS_Products (ID,ProductName,Date_Alimentation) values (?,?,TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'))"

 

        setparam(1) = 1
        setparam(1,"SqlType")=$$$SqlInteger

 

        setparam(2) = ##class(%PopulateUtils).Name()
        setparam(2,"SqlType")=$$$SqlVarchar
            
        setparam(3) = $ZDATETIME($NOW(),3)
        setparam(3,"SqlType")=$$$SqlVarchar

 

        setparam = 3
            
        $$$ThrowOnError(..Adapter.ExecuteUpdateBatchParamArray(.nrows,SqlInsertView,.param))
                                
    }
    catchexp
    {
        SettStatus = exp.AsStatus()
    }

 

    QuittStatus
}

 

XData MessageMap
{
<MapItems>
    <MapItemMessageType="Ens.StringRequest">
        <Method>SetResultSetView</Method>
    </MapItem>
</MapItems>
}

 

}

Or with the %SQLGatewayConnection :

    //Create new Gateway connection object
   setgc=##class(%SQLGatewayConnection).%New()
   Ifgc=$$$NULLOREFquit$$$ERROR($$$GeneralError,"Cannot create %SQLGatewayConnection.")
       
   //Make connection to target DSN
   spDSN="Samples"
   susr="_system"
   spwd="SYS"
   setsc=gc.Connect(pDSN,usr,pwd,0)
   If$$$ISERR(sc) quitsc
   ifgc.ConnectionHandle=""quit$$$ERROR($$$GeneralError,"Connection failed")
       
   setsc=gc.AllocateStatement(.hstmt)
   if$$$ISERR(sc) quitsc
       
   //Prepare statement for execution
   setpQuery= "select * from Sample.Person"
   setsc=gc.Prepare(hstmt,pQuery)
   if$$$ISERR(sc) quitsc
     //Execute statement
   setsc=gc.Execute(hstmt)
   if$$$ISERR(sc) quitsc
Guillaume Rongier · Jan 16, 2020 go to post

Hi Kevin,

You can use IOAddr property from EnsLib.TCP.CountedInboundAdapter

IOAddr property come in this format : 57777<-127.0.0.1:54844

To extract the source IP adresse you can parse it like that :

setfullIOAddr = ..Adapter.IOAddr //57777<-127.0.0.1:54844

 

setremoteIP = $P($P(fullIOAddr,"-",2),":",1) //127.0.0.1
Guillaume Rongier · Jan 30, 2020 go to post

Hi Blaise,

You can pass %OnNew expection to %New with this code snippet :

ClassTest.PersitenceTestExtends%Persistent
{

 

PropertymandatoryAs%String [ Required ];

 

Method%OnNew(mandatoryAs%String = "") As%Status
{

 

set ..mandatory = mandatory

 

$$$ThrowOnError(..%ValidateObject())

 

Quit$$$OK
}

 

}

 Now when you call the new method :

USER>set test = ##class(Test.PersitenceTest).%New()

  If $isobject($get(newerror))=1 Throw newerror
                                 ^
<THROW>%Construct+9^Test.PersitenceTest.1 *%Exception.StatusException ERROR #5659: Property 'Test.PersitenceTest::mandatory(10@Test.PersitenceTest,ID=)' required

Now you can catch you %OnNew expection anywhere :

ClassMethodtest() As%Status
{
try {
settest = ##class(Test.PersitenceTest).%New()
}
catchex {
Returnex.AsStatus()
}
Return$$$OK
}
Guillaume Rongier · Jan 30, 2020 go to post

Hi Lucas,

All security settings are store in the %SYS databases.

You can access then with this query :

select * from Security.System

Or with this procedure

select * from Security.System_List()

If you want to do this in COS :

s ResultSet=##class(%ResultSet).%New("Security.System:List")
d ResultSet.Execute()
While ResultSet.Next() {
	zw ResultSet.Data("Name")
}

Like that you don't have to use the export function who only create files.

Now you can parse data and build your report directly from an homemade object.

Guillaume Rongier · May 25, 2020 go to post

Hi Tom, You can have a look at this documentation : https://cedocs.intersystems.com/latest/csp/docbook/Doc.View.cls?KEY=EDICOM_filestorage_production to help you to create a dicom production to send document to pacs.

If you are not on Ensemble, you have in the community installer for ENSDEMO production : https://openexchange.intersystems.com/package/irishealth-ensdemo

But in short, you have to create an EnsLib.DICOM.Document Fill it with information about the patient, ect. e.g

  Set pDocOut = ##class(EnsLib.DICOM.Document).%New()
  // Set patient data
  $$$THROWONERROR(tSC, pDocOut.SetValueAt("Toto^Toto", "DataSet.PatientName"))
  $$$THROWONERROR(tSC, pDocOut.SetValueAt(1, "DataSet.PatientID"))

  //Set pdf in EncapsulatedDocument
  $$$THROWONERROR(tSC, pDocOut.SetValueAt("application/pdf","DataSet.MIMETypeOfEncapsulatedDocument"))
  /// File is you binary pdf document
  IF '$IsObject(file) $$$THROWONERROR(tSC, $$$ERROR($$$FailedToNewClass, "%Stream.TmpBinary"))

  // DICOM Standard PS3.5: The Value Field containing Pixel Data, like all other Value Fields in DICOM, shall be an even number of bytes in length
  // Thanks Michel Liberado for this hack
  IF (($L(binary) # 2) '= 0) {
          // Any char would be fine but I want to have the End Of Transmission ASCII character
          $$$THROWONERROR(tSC, file.Write($CHAR(4)))
  }

  $$$THROWONERROR(tSC, pDocOut.SetValueAt(file,"DataSet.EncapsulatedDocument"))

Once you have your pDocOut you can send it to business process Demo.DICOM.Process.Storage

Guillaume Rongier · Jun 22, 2020 go to post

Hi Klaus,

You have to proxy IRIS to use https. To do so, use this git :

Guillaume Rongier · Oct 8, 2020 go to post

Don't forget that SuperServer port changed from 51773 to 1972 cf : https://irisdocs.intersystems.com/iris20203/csp/docbook/Doc.View.cls?KEY=GCRN_upgrade20203#GCRN_install_port

docker run --rm --name my-iris -d --publish 9091:1972 --publish 9092:52773 intersystemsdc/iris-community:2020.3.0.221.0-zpm

docker run --rm --name my-iris -d --publish 9091:51773 --publish 9092:52773 intersystemsdc/iris-community:2020.2.0.204.0-zpm

docker run --rm --name my-iris -d --publish 9091:1972 --publish 9092:52773 intersystemsdc/irishealth-community:2020.3.0.221.0-zpm

docker run --rm --name my-iris -d --publish 9091:51773 --publish 9092:52773 intersystemsdc/irishealth-community:2020.2.0.204.0-zpm

Guillaume Rongier · Oct 20, 2020 go to post

Hi,

SuperServer port since 2020.3 is 1972 :

docker run --rm --name my-iris -d --publish 9091:1972 --publish 9092:52773 intersystemsdc/iris-community:2020.3.0.221.0-zpm

docker run --rm --name my-iris -d --publish 9091:1972 --publish 9092:52773 intersystemsdc/iris-community:2020.4.0.521.0-zpm

docker run --rm --name my-iris -d --publish 9091:1972 --publish 9092:52773 intersystemsdc/irishealth-community:2020.3.0.221.0-zpm

docker run --rm --name my-iris -d --publish 9091:1972 --publish 9092:52773 intersystemsdc/irishealth-community:2020.4.0.521.0-zpm
Guillaume Rongier · Oct 23, 2020 go to post

I agree with you in most cases.


The most important announcement for me would be Embedded Python.
This will bring to the community the power of python libraries, it's a giant step forward.

Guillaume Rongier · Oct 25, 2020 go to post

Hi Ravikumar,

All can be done in InterSystems IRIS with almost no code.

Have a look at this demo to convert HL7v2 to FHIR : https://openexchange.intersystems.com/package/FHIR-HL7v2-SQL-Demo

For JSON/XML to FHIR, you can have a look here : https://github.com/grongierisc/HL7ToJson

For HL7 SIU, the same can be achieved with the first link.

For CDA/FHIR you have example here : https://github.com/grongierisc/FHIRaaS/blob/master/src/Interop/BP/CCDAT…

If you need more help, let me know.