User comments | InterSystems Developer Community

Guillaume Rongier · Feb 16, 2022 go to post

You will find here a demo of a quarkus rest crud api with iris as a database.

https://github.com/grongierisc/quarkus-iris

It's not using the Hibernate ORM but this shouldn't be an issue.

Guillaume Rongier · Feb 8, 2022 go to post

You still need the iris hibernate dialect.

You can find it here : https://github.com/intersystems/quickstarts-java

https://github.com/intersystems/quickstarts-java/tree/master/src/org/hibernate

Guillaume Rongier · Feb 8, 2022 go to post

If you are using Ensemble you can use EnsLib.SQL.Snapshot.

This helper class can persist ResutSet in global :

Set rset1 = ##class(%ResultSet).%New()
set sc = rset1.Prepare("Select * FROM Ens_Util.Log")
Set:+sc sc = rset1.Execute()

set snap = ##class(EnsLib.SQL.Snapshot).CreateFromResultSet(rset1)
set glb = snap.%GblRef

zw @glb

glb :

%Ensemble("12@EnsLib.SQL.Snapshot")=21
%Ensemble("12@EnsLib.SQL.Snapshot",1,1)=1
%Ensemble("12@EnsLib.SQL.Snapshot",1,2)=""
%Ensemble("12@EnsLib.SQL.Snapshot",1,3)=445
%Ensemble("12@EnsLib.SQL.Snapshot",1,4)=""
%Ensemble("12@EnsLib.SQL.Snapshot",1,5)=""
%Ensemble("12@EnsLib.SQL.Snapshot",1,6)="Ens.Director"
%Ensemble("12@EnsLib.SQL.Snapshot",1,7)="StartProduction"
%Ensemble("12@EnsLib.SQL.Snapshot",1,8)=""
%Ensemble("12@EnsLib.SQL.Snapshot",1,9)="Production 'Connector.Production' starting..."
%Ensemble("12@EnsLib.SQL.Snapshot",1,10)="2022-02-08 14:29:33.724"
%Ensemble("12@EnsLib.SQL.Snapshot",1,11)=""
%Ensemble("12@EnsLib.SQL.Snapshot",1,12)=4
%Ensemble("12@EnsLib.SQL.Snapshot",2,1)=2
%Ensemble("12@EnsLib.SQL.Snapshot",2,2)="Ens.Actor"
%Ensemble("12@EnsLib.SQL.Snapshot",2,3)=618
%Ensemble("12@EnsLib.SQL.Snapshot",2,4)=""
%Ensemble("12@EnsLib.SQL.Snapshot",2,5)=""
%Ensemble("12@EnsLib.SQL.Snapshot",2,6)="Ens.Job"
%Ensemble("12@EnsLib.SQL.Snapshot",2,7)="Start"
%Ensemble("12@EnsLib.SQL.Snapshot",2,8)=""
%Ensemble("12@EnsLib.SQL.Snapshot",2,9)="ConfigItem 'Ens.Actor' started in job 618"
%Ensemble("12@EnsLib.SQL.Snapshot",2,10)="2022-02-08 14:29:33.978"
%Ensemble("12@EnsLib.SQL.Snapshot",2,11)=""
%Ensemble("12@EnsLib.SQL.Snapshot",2,12)=4
%Ensemble("12@EnsLib.SQL.Snapshot","ColIDs")=12
%Ensemble("12@EnsLib.SQL.Snapshot","ColIDs","configname")=2
%Ensemble("12@EnsLib.SQL.Snapshot","ColIDs","id")=1
%Ensemble("12@EnsLib.SQL.Snapshot","ColIDs","job")=3
%Ensemble("12@EnsLib.SQL.Snapshot","ColIDs","messageid")=4
%Ensemble("12@EnsLib.SQL.Snapshot","ColIDs","sessionid")=5
%Ensemble("12@EnsLib.SQL.Snapshot","ColIDs","sourceclass")=6
%Ensemble("12@EnsLib.SQL.Snapshot","ColIDs","sourcemethod")=7
%Ensemble("12@EnsLib.SQL.Snapshot","ColIDs","stack")=8
%Ensemble("12@EnsLib.SQL.Snapshot","ColIDs","text")=9
%Ensemble("12@EnsLib.SQL.Snapshot","ColIDs","timelogged")=10
%Ensemble("12@EnsLib.SQL.Snapshot","ColIDs","tracecat")=11
%Ensemble("12@EnsLib.SQL.Snapshot","ColIDs","type")=12
%Ensemble("12@EnsLib.SQL.Snapshot","ColNames")=12
%Ensemble("12@EnsLib.SQL.Snapshot","ColNames",1)="ID"
%Ensemble("12@EnsLib.SQL.Snapshot","ColNames",2)="ConfigName"
%Ensemble("12@EnsLib.SQL.Snapshot","ColNames",3)="Job"
%Ensemble("12@EnsLib.SQL.Snapshot","ColNames",4)="MessageId"
%Ensemble("12@EnsLib.SQL.Snapshot","ColNames",5)="SessionId"
%Ensemble("12@EnsLib.SQL.Snapshot","ColNames",6)="SourceClass"
%Ensemble("12@EnsLib.SQL.Snapshot","ColNames",7)="SourceMethod"
%Ensemble("12@EnsLib.SQL.Snapshot","ColNames",8)="Stack"
%Ensemble("12@EnsLib.SQL.Snapshot","ColNames",9)="Text"
%Ensemble("12@EnsLib.SQL.Snapshot","ColNames",10)="TimeLogged"
%Ensemble("12@EnsLib.SQL.Snapshot","ColNames",11)="TraceCat"
%Ensemble("12@EnsLib.SQL.Snapshot","ColNames",12)="Type"
%Ensemble("12@EnsLib.SQL.Snapshot","ColSizes")=12
%Ensemble("12@EnsLib.SQL.Snapshot","ColTypes")=12
%Ensemble("12@EnsLib.SQL.Snapshot","ColTypes",1)="BIGINT"
%Ensemble("12@EnsLib.SQL.Snapshot","ColTypes",2)="VARCHAR"
%Ensemble("12@EnsLib.SQL.Snapshot","ColTypes",3)="VARCHAR"
%Ensemble("12@EnsLib.SQL.Snapshot","ColTypes",4)="INTEGER"
%Ensemble("12@EnsLib.SQL.Snapshot","ColTypes",5)="INTEGER"
%Ensemble("12@EnsLib.SQL.Snapshot","ColTypes",6)="VARCHAR"
%Ensemble("12@EnsLib.SQL.Snapshot","ColTypes",7)="VARCHAR"
%Ensemble("12@EnsLib.SQL.Snapshot","ColTypes",8)="VARCHAR"
%Ensemble("12@EnsLib.SQL.Snapshot","ColTypes",9)="VARCHAR"
%Ensemble("12@EnsLib.SQL.Snapshot","ColTypes",10)="TIMESTAMP"
%Ensemble("12@EnsLib.SQL.Snapshot","ColTypes",11)="VARCHAR"
%Ensemble("12@EnsLib.SQL.Snapshot","ColTypes",12)="INTEGER"

If you are not using Ensemble, I guess you will have to build this kind of helper class by your self.

Guillaume Rongier · Feb 8, 2022 go to post

If you don't want to create new data on the FHIR protocol, you must use the PUT verb with an ID instead of POST.

PUT creates the resource with the specified ID if the ID does not exist, otherwise it replaces the pre-existing data.

POST always creates a new resource with a new ID, that's why the ID is not mandatory when POSTing.

For automatic transformations from HL7/CDA to FHIR, there is the possibility to define the ID for some resources and thus to avoid duplication.

Below is an example of code to transform an HL7 payload to SDA by specifying the ID of the patient in order to avoid duplicating this resource, after that you can transform this SDA to FHIR with no duplication of patient.

/// This is a custom business process that transforms an HL7 message to SDA format (an internal healthcare data format for InterSystems IRIS for Health).
/// To use this class, add a business process with this class to the production and configure the target. The default target will send the SDA to a component
/// that converts the data to FHIR.
/// 
Class FHIRDemo.HL7TransformProcess Extends Ens.BusinessProcess [ ClassType = persistent ]
{

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

Property TargetConfigName As Ens.DataType.ConfigName [ InitialExpression = "HS.FHIR.DTL.Util.HC.SDA3.FHIR.Process" ];

/// Transforms an HL7 message to SDA, an internal healthcare format for InterSystems IRIS for Health.
Method OnRequest(pRequest As EnsLib.HL7.Message, Output pResponse As Ens.Response) As %Status
{
	set tSC = $$$OK
	try {
		$$$ThrowOnError(##class(HS.Gateway.HL7.HL7ToSDA3).GetSDA(pRequest,.tSDA))
		$$$LOGINFO(tSDA.Read())

		Set tQuickStream = ##class(HS.SDA3.QuickStream).%New()
		$$$ThrowOnError(tQuickStream.CopyFrom(tSDA))
		
		Set tResponse = ##class(HS.Message.XMLMessage).%New()
		Do tResponse.AdditionalInfo.SetAt(tQuickStream.%Id(),"QuickStreamId")
		Do tResponse.AdditionalInfo.SetAt($P(pRequest.GetValueAt("PID:3:1"),"^"),"PatientResourceId")
	
        Set tSC = ..SendRequestSync(..TargetConfigName,tResponse,.pResponse)
	} catch ex {
		set tSC = ex.AsStatus()
	}
	quit tSC
}

Storage Default
{
<Data name="HL7TransformProcessDefaultData">
<Subscript>"HL7TransformProcess"</Subscript>
<Value name="1">
<Value>TargetConfigName</Value>
</Value>
</Data>
<DefaultData>HL7TransformProcessDefaultData</DefaultData>
<Type>%Storage.Persistent</Type>
}

}

Guillaume Rongier · Jan 31, 2022 go to post

Hi Evgeny, I confirm that irispip is not working, if you want to install python package you shall use pip3 or /usr/irissys/bin/irispython -m pip

Guillaume Rongier · Jan 30, 2022 go to post

Hi Steve, have a look a this github repo, it has a lot of examples :

https://github.com/grongierisc/iris-python-template

Here is what you are looking for call python methods from objectscript and vice versa :

/// embedded python example
Class ObjectScript.Embbeded.Python Extends %SwizzleObject
{

/// HelloWorld with a parameter
ClassMethod HelloWorld(name As %String = "toto") As %Boolean [ Language = python ]
{
    print("Hello",name)
    return True
}

/// Description
Method compare(modèle, chaine) As %Status [ Language = python ]
{
    import re

    # compare la chaîne [chaîne] au modèle [modèle]
    # affichage résultats
    print(f"\nRésultats({chaine},{modèle})")
    match = re.match(modèle, chaine)
    if match:
        print(match.groups())
    else:
        print(f"La chaîne [{chaine}] ne correspond pas au modèle [{modèle}]")
}

/// Description
Method compareObjectScript(modèle, chaine) As %Status
{
    w !,"Résultats("_chaine_","_modèle_")",!
    set matcher=##class(%Regex.Matcher).%New(modèle)                             
    set matcher.Text=chaine
    if matcher.Locate() {
        write matcher.GroupGet(1)
    }
    else {
        w "La chaîne ["_chaine_"] ne correspond pas au modèle ["_modèle_"]"
    }
}

/// Description
Method DemoPyhtonToPython() As %Status [ Language = python ]
{
    # expression régulières en python
    # récupérer les différents champs d'une chaîne
    # le modèle : une suite de chiffres entourée de caractères quelconques
    # on ne veut récupérer que la suite de chiffres
    modèle = r"^.*?(\d+).*?$"

    # on confronte la chaîne au modèle
    self.compare(modèle, "xyz1234abcd")
    self.compare(modèle, "12 34")
    self.compare(modèle, "abcd")
}

Method DemoPyhtonToObjectScript() As %Status [ Language = python ]
{
    # expression régulières en python
    # récupérer les différents champs d'une chaîne
    # le modèle : une suite de chiffres entourée de caractères quelconques
    # on ne veut récupérer que la suite de chiffres
    modèle = r"^.*?(\d+).*?$"

    # on confronte la chaîne au modèle
    self.compareObjectScript(modèle, "xyz1234abcd")
    self.compareObjectScript(modèle, "12 34")
    self.compareObjectScript(modèle, "abcd")
}

/// Description
Method DemoObjectScriptToPython() As %Status
{
    // le modèle - une date au format jj/mm/aa
    set modèle = "^\s*(\d\d)\/(\d\d)\/(\d\d)\s*$"
    do ..compare(modèle, "10/05/97")
    do ..compare(modèle, " 04/04/01 ")
    do ..compare(modèle, "5/1/01")
}

}
/// embedded python example
Class ObjectScript.Embbeded.Python Extends %SwizzleObject
{

/// HelloWorld with a parameter
ClassMethod HelloWorld(name As %String = "toto") As %Boolean [ Language = python ]
{
    print("Hello",name)
    return True
}

/// Description
Method compare(modèle, chaine) As %Status [ Language = python ]
{
    import re

    # compare la chaîne [chaîne] au modèle [modèle]
    # affichage résultats
    print(f"\nRésultats({chaine},{modèle})")
    match = re.match(modèle, chaine)
    if match:
        print(match.groups())
    else:
        print(f"La chaîne [{chaine}] ne correspond pas au modèle [{modèle}]")
}

/// Description
Method compareObjectScript(modèle, chaine) As %Status
{
    w !,"Résultats("_chaine_","_modèle_")",!
    set matcher=##class(%Regex.Matcher).%New(modèle)                             
    set matcher.Text=chaine
    if matcher.Locate() {
        write matcher.GroupGet(1)
    }
    else {
        w "La chaîne ["_chaine_"] ne correspond pas au modèle ["_modèle_"]"
    }
}

/// Description
Method DemoPyhtonToPython() As %Status [ Language = python ]
{
    # expression régulières en python
    # récupérer les différents champs d'une chaîne
    # le modèle : une suite de chiffres entourée de caractères quelconques
    # on ne veut récupérer que la suite de chiffres
    modèle = r"^.*?(\d+).*?$"

    # on confronte la chaîne au modèle
    self.compare(modèle, "xyz1234abcd")
    self.compare(modèle, "12 34")
    self.compare(modèle, "abcd")
}

Method DemoPyhtonToObjectScript() As %Status [ Language = python ]
{
    # expression régulières en python
    # récupérer les différents champs d'une chaîne
    # le modèle : une suite de chiffres entourée de caractères quelconques
    # on ne veut récupérer que la suite de chiffres
    modèle = r"^.*?(\d+).*?$"

    # on confronte la chaîne au modèle
    self.compareObjectScript(modèle, "xyz1234abcd")
    self.compareObjectScript(modèle, "12 34")
    self.compareObjectScript(modèle, "abcd")
}

/// Description
Method DemoObjectScriptToPython() As %Status
{
    // le modèle - une date au format jj/mm/aa
    set modèle = "^\s*(\d\d)\/(\d\d)\/(\d\d)\s*$"
    do ..compare(modèle, "10/05/97")
    do ..compare(modèle, " 04/04/01 ")
    do ..compare(modèle, "5/1/01")
}

}

Guillaume Rongier · Jan 26, 2022 go to post

Great article and very useful.

In one of my project, I have almost done the same but instead of using env var, I use an mounted xml file of default settings :

LoadSettings.mac in Interoperability NameSpace

ROUTINE LoadSettings

#Include Ensemble

UseXMLVariables() PUBLIC {

	Try {
		// mounted file of default settings
		set tFileName = "/usr/irissys/conf/DefaultSettings.xml"
		if ##class(%File).Exists(tFileName) {
			do ##class(Ens.Config.DefaultSettings).%Import(tFileName)
			write !,"File : "_tFileName_" loaded the defaultsettings",!
		}
	} Catch e {
		s ^%zStartupError=e.AsStatus()
	}
}

%ZSTART

ROUTINE %ZSTART

#include %occStatus
SYSTEM() PUBLIC {
	/*
		Initial plan to use this as startup configuration.
	*/
	Try {
		New $namespace
		Set $namespace ="IRISAPP"

		Do UseXMLVariables^LoadSettings

	} Catch {}
}

/usr/irissys/conf/DefaultSettings.xml

<Export generator="IRIS" version="26" zv="IRIS for UNIX (Ubuntu Server LTS for x86-64 Containers) 2020.4 (Build 521U)" ts="2021-05-10 07:39:07">
<Document name="Ens.Config.DefaultSettings.ESD">
<defaultSettings>
<item production="*" item="FHIR_STU3_Default_Operation" class="*" setting="URL" value="http://fhirserver:52773/fhir/stu3/"><Deployable>true</Deployable></item>
</defaultSettings>
</Document></Export>

Guillaume Rongier · Jan 25, 2022 go to post

check this repo https://github.com/grongierisc/iris-healthtoolkit-service, it may suit your need.

Guillaume Rongier · Nov 30, 2021 go to post

Hi Mike,

Indeed, there is no HealthConnect (HealthShare Connect) Community Edition, but with IRIS for Health Community edition you can do the same as HealthConnect and even more (FHIR server).

Guillaume Rongier · Nov 27, 2021 go to post

Until the author update the repo, you can change this line with this image :

containers.intersystems.com/intersystems/iris-community:2021.1.0.215.3

Guillaume Rongier · Nov 19, 2021 go to post

@Ben Spead

With Embedded Python, code can (and should?) not be stored in the databases and still be executed on the server side with the irispython interpreter.

Here is a GitHub link that demonstrates the use of embedded python with a backend on the Flask micro framework + Iris as a database.

It is true that Embedded python will change a lot of our paradigms, best practices, reflexes.

That's why we, as ObjectScript and IRIS experts, must try to use it as much as possible.

We will have to go out of our comfort zone and it is essential, it is up to us to go towards the python community because the opposite will not happen.

Regarding the [language = python] tag, I'm not saying it's useless. It can be useful in some cases. I just think that it should not become the norm.

PS: My previous answer is indeed provocative and this is to draw attention to Embedded Python which is not a feature but a major evolution (Révolution ?).

Guillaume Rongier · Nov 19, 2021 go to post

Yes, you can easily gain a factor from 5 to 10 in insert time.

I your case, you can expect your batch to be processed in less than 30 minutes or so.

Example of code can be found here :

https://github.com/grongierisc/BatchSqlOutboundAdapter/blob/master/src/…

BTW : This adaptor only works with JDBC drivers.

Guillaume Rongier · Nov 19, 2021 go to post

The king is dead, long live the king! is a traditional phrase that is proclaimed at the advent of a new monarch in France.

May I say: IRIS is dead, long live IRIS!

This is what I feel with the arrival of Embedded Python.

The old monarch spoke ObjectScript, he allowed us to accomplish great things but now let's stop looking back, let's move forward. Let's induct the new monarch who speaks Python. Let's stop worshipping his father and let's make room for the new generation.

That's why I think, we must stop coding in ObjectScript, we must strive to use Python, everywhere in IRIS and this without the wrapper ([language = python]).

Why be so radical?

Coding in Python is to show by example and in a language that the uninitiated know and thus prove the extent of the possibilities of IRIS
Coding in Python is to start answering and finding solutions to the new problems that we will encounter: a. How to integrate .py files in our CICD pipes b. How to integrate PyPI with ZPM c. How to elegantly expose future APIs coded on the Flash Framework with IRIS and CSP gateways
Coding in Python encourages InterSystems to produce new packages in Python rather than ObjectScript.

Choosing your programming language in IRIS is a political act.

Coding in ObjectScript is to be conservative.
Coding in Python is to be liberal.

Choose your side.

Guillaume Rongier · Nov 18, 2021 go to post

I add the powershell version :

Invoke-WebRequest -Uri 'https://login.intersystems.com/login/SSO.UI.Login.cls?referrer=https%253A//wrc.intersystems.com/wrc/login.csp' -SessionVariable session -Method POST -Body 'UserName=<Your Login>&Password=<Your Password>' 

Invoke-WebRequest -WebSession $session -Uri https://wrc.intersystems.com/wrc/WRC.StreamServer.cls?FILE=/wrc/Live/ServerKits/IRIS-2020.1.1.408.0-win_x64.exe -outfile "iris.exe"

Guillaume Rongier · Oct 22, 2021 go to post

Henrique, I love your work because technically it's clean, always up to date and it brings a fresh wind. With a creativity and skill everything is possible. Congratulations to both of you (Henrique and José).

Guillaume Rongier · Oct 21, 2021 go to post

The difference between a stored procedure and SQL inserts is that the business logic remains on the application side and not on the database side to keep the principles (storage/logic/representation) separate.

Nevertheless, the problem doesn't seem to be at the SQL level but rather at the BPL level, so maybe it's due to the conversion of XML to object?
If it is the case, use different technique to parse the XML, like the SAX Parser which avoids to mount all the XML document in memory.
https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cl…

Guillaume Rongier · Oct 21, 2021 go to post

Where do you struggle ? Is it in the BPL, in the Operation or during the insert statement to SQL Server ?

If the bottleneck is in the insert statement check out this community SQL adapter (work only on JDBC). https://openexchange.intersystems.com/package/ETL-Interoperability-Adapter

This adapter provide you an access to insert batch statement, that can speed up the insert speed up to 10 times even 100 times.

In the github repository you have some example how to use it : https://github.com/grongierisc/BatchSqlOutboundAdapter/blob/master/src/CLS/Grongier/Example/SqlInsertOperation.cls

Guillaume Rongier · Oct 21, 2021 go to post

In addition to this trendy post, I propose to add this link to a training on github about the interoperability framework.

https://community.intersystems.com/post/ensemble-interoperability-train…

Guillaume Rongier · Oct 6, 2021 go to post

Bravo for this initiative.

For your information, this is at least the 5th or 6th time that I return to this page to use this magic command :)

Guillaume Rongier · Oct 6, 2021 go to post

Hi Esther,

This link is working http://localhost:52775/csp/sys/UtilHome.csp?$NAMESPACE=IRISAPP

If you are not working with the docker version of this training, you may be need to adjust the port from 52775 to 52773 and adapt also the namespace.

Guillaume Rongier · Sep 8, 2021 go to post

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 go to post

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 :

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 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

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

{

<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 · Aug 18, 2021 go to post

New version 2.0.1 :

Change logs :

Complete rewrite in Angular 12 + Angular Materials
New features :
Guess columns
Customizable columns :
- Names and Types
New module to handle asynchronous jobs ( from HSMOD.REST.Async ) :
- https://github.com/grongierisc/iris-csvgen-ui/blob/master/src/Grongier/Rest/Async/API.cls

Demo new feature :

Demo

Guillaume Rongier · Jul 7, 2021 go to post

Is Amir's solution not satisfactory?

In some cases, it is preferable to use a < code > activity that directly calls the transformation and assigns the status code to the status variable, which will be processed by the try/catch.

<code> Set status = $classmethod(tTransformClass, "Transform", request, .normalizedRequest </code>

Guillaume Rongier · Jun 28, 2021 go to post

Hi,

Great news about Direct Messages.

In the meantime, is there any plan to make a dark mode for the community front end (as githut, stakoverflow, and so on does) ?

Guillaume Rongier · Jun 24, 2021 go to post

Hi Muhammad,

We have our own fhir repository :

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

But if you still want to use HAPI FHIR from a production you can use this adaptor :

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

Guillaume Rongier · Jun 23, 2021 go to post

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.

https://docs.intersystems.com/components/csp/docbook/Doc.View.cls?KEY=PAGE_ides

For database administration, you can use the management portal.

https://docs.intersystems.com/irislatest/csp/docbook/Doc.View.cls?KEY=PAGE_administration

For 100% SQL administration, I recommend DbBeaver.

https://community.intersystems.com/post/dbeaver-officially-supports-intersystems-iris

Guillaume Rongier · Jun 17, 2021 go to post

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 :

https://github.com/grongierisc/iris-r-gateway-template

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 · Jun 2, 2021 go to post

Ready to cook !

I'm such a fan of my cat that he has his own Instagram:

https://www.instagram.com/cat_philibert/

User statistics