All
Accepted answers
Sylvain Guilbaud · Aug 24, 2023 go to post

Hello @ED Coder,

you can also use a "low-code" approach using the DTL language with the REMOVE action :

Class utils.HL7.transfo.removeSegment Extends Ens.DataTransformDTL [ DependsOn = EnsLib.HL7.Message ]

{
Parameter IGNOREMISSINGSOURCE = 0;
Parameter REPORTERRORS = 1;
Parameter TREATEMPTYREPEATINGFIELDASNULL = 0;
XData DTL [ XMLNamespace = "http://www.intersystems.com/dtl" ]
{
<transform sourceClass='EnsLib.HL7.Message' targetClass='EnsLib.HL7.Message' sourceDocType='2.6:ADT_A01' targetDocType='2.6:ADT_A01' create='copy' language='objectscript' >
<assign value='' property='target.{EVN}' action='remove' />
<assign value='' property='target.{DG1()}' action='remove' />
</transform>
}
}

Sylvain Guilbaud · Aug 24, 2023 go to post

Thanks for this idea !
That would be a very good way to simplify the connection to IRIS from Power Apps / Power Automate.

Sylvain Guilbaud · Aug 24, 2023 go to post

That's also a possibility if you have this option to get another disk which will allow you to change the default IRISAUDIT database directory.

Sylvain Guilbaud · Aug 24, 2023 go to post

That's the perfect answer with the appropriate API to use !! 😀
Thanks @Vitaliy Serdtsev 

USER>zn "%sys"
%SYS>w ##class(SYS.Database).%OpenId("/is/iris/mgr/irisaudit").Size
11
%SYS>w ##class(%SYS.Audit).Erase(1)                                               
1
%SYS>w ##class(SYS.Database).%OpenId("/is/iris/mgr/irisaudit").Size
1

And in messages.log you have :

08/24/23-17:31:00:649 (23676) 0 [Database.FullExpansion] Expansion completed for database /is/iris/mgr/irisaudit/. Expanded by 10 MB.
08/24/23-17:33:38:885 (48001) 0 [Generic.Event] Dismounted database /is/iris/mgr/irisaudit/ (SFN 4)
08/24/23-17:33:39:955 (48001) 0 [Database.MountedRW] Mounted database /is/iris/mgr/irisaudit/ (SFN 4) read-write.
Sylvain Guilbaud · Aug 28, 2023 go to post

The solution is to use the Schemas / Usersfilters in the connection settings :

And adding "%" in the "to exclude" part :

Sylvain Guilbaud · Aug 28, 2023 go to post

I agree.
Filtering "schemas / users" by excluding "%" it not working very well indeed as it masks all the schemas' names and then considers all the tables in the "SQLUser" default schema...

Sylvain Guilbaud · Aug 28, 2023 go to post

Actually excluding all the "%" schemas in the connection setting is not working properly as it masks all the schemas' names. You have directly the names of all the tables and when you try to open it, you get an error as the query use SQLUser as the default schema.

Sylvain Guilbaud · Aug 28, 2023 go to post

Hi @Muhammad Waseem 
Streamlit now runs better. But when I give a key, it raises the following error :

RuntimeError: [91mYour system has an unsupported version of sqlite3. Chroma requires sqlite3 >= 3.35.0.[0m [94mPlease visit https://docs.trychroma.com/troubleshooting#sqlite to learn how to upgrade.[0m

Traceback:

File "/usr/local/lib/python3.11/site-packages/streamlit/runtime/scriptrunner/script_runner.py", line 552, in _run_script exec(code, module.__dict__)File "/opt/irisappS/streamlit/app/streamlitAPP.py", line 208, in <module> main()File "/opt/irisappS/streamlit/app/streamlitAPP.py", line 153, in main init_doc()File "/opt/irisappS/streamlit/app/streamlitAPP.py", line 117, in init_doc vectorstore = irisChatGPT.docLoader(st.session_state["OPENAI_API_KEY"]) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^File "/opt/irisappS/streamlit/app/irisChatGPT.py", line 94, in docLoader vectordb = Chroma(persist_directory='/opt/irisappS/streamlit/app/vectors', embedding_function=embedding) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^File "/usr/local/lib/python3.11/site-packages/langchain/vectorstores/chroma.py", line 80, in __init__ import chromadbFile "/usr/local/lib/python3.11/site-packages/chromadb/__init__.py", line 69, in <module> raise RuntimeError(


Sylvain Guilbaud · Aug 29, 2023 go to post

Now with embedded Python, you can have a quite simple code using pandas :


/// Convert an Excel file to a CSV file
ClassMethod XLStoCSV(source As %String = "/data/sample.xlsx") As %Status [ Language = python ]
{
import pandas as pd
read_file = pd.read_excel(source)
read_file.to_csv(source+'.csv', index = None, header=True)
}
Sylvain Guilbaud · Aug 29, 2023 go to post

NB : for column-oriented storage specified at the table level via the statement WITH STORAGETYPE = COLUMNAR , it should be noted that IRIS leaves itself free to choose for you the most commonly optimal storage (in row or in column), according to the types of data.

Example : 

the following statement :


CREATE TABLE a.addressV1 (
        city varchar(50),
        zip varchar(15),
        country varchar(15)
    )
    WITH STORAGETYPE = COLUMNAR

Will not create any column-oriented storage, due to the risk of too disparate data, due to the number of characters allowed in each column (15 or 50) : 


Class a.addressV1 Extends %Persistent [ ClassType = persistent, DdlAllowed, Final, Owner = {_SYSTEM}, ProcedureBlock, SqlRowIdPrivate, SqlTableName = addressV1 ]

{

Property city As %Library.String(COLLATION = "EXACT", MAXLEN = 50, STORAGEDEFAULT = "ROW") [ SqlColumnNumber = 2 ];

Property zip As %Library.String(COLLATION = "EXACT", MAXLEN = 15, STORAGEDEFAULT = "ROW") [ SqlColumnNumber = 3 ];

Property country As %Library.String(COLLATION = "EXACT", MAXLEN = 15, STORAGEDEFAULT = "ROW") [ SqlColumnNumber = 4 ];

Parameter STORAGEDEFAULT = "columnar";

Parameter USEEXTENTSET = 1;

while the example given in the article, retains a column (and only one) in column-oriented storage, since having only 5 characters allowed for the zip column.




 CREATE TABLE a.addressV2 (
        city varchar(50),
        zip varchar(5),
        country varchar(15)
    )
    WITH STORAGETYPE = COLUMNAR

Class a.addressV2 Extends %Persistent [ ClassType = persistent, DdlAllowed, Final, Owner = {_SYSTEM}, ProcedureBlock, SqlRowIdPrivate, SqlTableName = addressV2 ]

{

Property city As %Library.String(COLLATION = "EXACT", MAXLEN = 50, STORAGEDEFAULT = "ROW") [ SqlColumnNumber = 2 ];

Property zip As %Library.String(COLLATION = "EXACT", MAXLEN = 5) [ SqlColumnNumber = 3 ];
Property country As %Library.String(COLLATION = "EXACT", MAXLEN = 15, STORAGEDEFAULT = "ROW") [ SqlColumnNumber = 4 ];

Parameter STORAGEDEFAULT = "columnar";
Parameter USEEXTENTSET = 1;
Sylvain Guilbaud · Aug 29, 2023 go to post

Good to know.
I had no idea of this quite combersome workflow with the information in this thread of discussion.
Maybe some other Python libraries could be of help here.

Sylvain Guilbaud · Aug 30, 2023 go to post

This was typically my first thinking when I said "by creating a user in IRIS with restricted rights, this list can be reduced, but the interest of the %All role is lost."

Sylvain Guilbaud · Aug 30, 2023 go to post

To activate all system AUDIT events, simply execute the following SQL query from the namespace %SYS :


update security.events set enabled=1 where flags = 1

example :


set tRes = ##class(%SQL.Statement).%ExecDirect(,"update security.events set enabled=1 where flags = 1")

if tRes.%SQLCODE=0 {

set ^["USER"]TRACE("%SYS Security.Events")=tRes.%ROWCOUNT_" successfully enabled"

} else {

set ^["USER"]TRACE("%SYS Security.Events")=tRes.%Message_" SQLCODE:"_tRes.%SQLCODE

}
Sylvain Guilbaud · Aug 31, 2023 go to post

Hi @Anup Thakkar,

the simplest way to always stay in the current century is : 

$ZDATEH("05/26/23",,,6)

You just need to use year-opt = 6 to get all dates with two-digit years in the current century .

w $zdt($ZDATEH("05/26/23",,,6),3)
2023-05-26

w $zdt($ZDATEH("05/26/1923",,,6),3)
1923-05-26
Sylvain Guilbaud · Sep 4, 2023 go to post

Hi @Yuri Marx,

at the very first topic, I will put at the top of your list :

0. better performances

It can be seen as obvious, but IRIS is **really** faster than Caché.

Sylvain Guilbaud · Sep 6, 2023 go to post

Furthermore, the Management Portal will tell you the current mode of your IRIS instance, which sometimes allows you to avoid handling errors by confusing your different instances.

Below you see immediately that this instance is a LIVE one : 


Sylvain Guilbaud · Sep 8, 2023 go to post

The answer has been given in French here by @Lorenzo Scalese 

You can create a custom class query like this: 

Class dc.Frank
{

Query QueryAFO() As %Query(ROWSPEC = "key1:%String,key2:%String,key3:%String,key4:%String,key5:%String,key6:%String,datavalue:%String") [ SqlProc ]
{
}

ClassMethod QueryAFOExecute(ByRef qHandle As %Binary) As %Status
{
    Set qHandle("node") = $Name(^AFO)
    Quit $$$OK
}

ClassMethod QueryAFOFetch(
	ByRef qHandle As %Binary,
	ByRef Row As %List,
	ByRef AtEnd As %Boolean) As %Status [ PlaceAfter = QueryAFOExecute ]
{
    Set sc = $$$OK
    Set qHandle("node") = $Query(@qHandle("node"), 1, data)

    If qHandle("node") = "" Set Row = "", AtEnd = $$$YES Quit $$$OK
    ; feeds the key x fields based on the subscripts of the global
    For i=1:1:$QLength(qHandle("node")) Set $List(Row, i) = $QSubscript(qHandle("node"), i)

    If i < 6 {  ; if we do not have 6 subscripts, we feed the rest with an empty string
        For j = i+1:1:6 Set $List(Row, j) = ""
    }

    Set $List(Row, 7) = data, AtEnd = $$$NO
    Quit sc
}

ClassMethod QueryAFOClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = QueryAFOExecute ]
{
	Kill qHandle Quit $$$OK
}

/// just for some test data
ClassMethod set()
{
    s ^AFO("Site","Ville")="66722,3743"
    s ^AFO("Site","Ville","111BB","OBT")=",MMM,XXX,"
    s ^AFO("Site","Ville","111OW","OBT")=",XXX,MMM,"
    s ^AFO("Site","Ville","AANVRBIBS","zzz")    =    "1^^1"
    s ^AFO("Site","Ville","AANVRBIBS","zzz","*","dut")    =    "*afhalen waar gevonden"
    s ^AFO("Site","Ville","AANVRBIBS","zzz","*","eng")    =    "*Pickup where found"
    s ^AFO("Site","Ville","AANVRBIBS","zzz","*","fre")    =    "*Lieu où trouvé"
}

}

You can then easily exploit it with the following query:

select *
from dc.Frank_QueryAFO()

In terminal mode, you can also simply use this line to display the results:

Do ##class(dc.Frank).QueryAFOFunc().%Display()

For my part, I carried out the test on IRIS 2023.2, there should be no incompatibility, but if you notice a problem do not hesitate to respond with the error message.