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 :
.png)
NB : $SYSTEM.Version.SystemMode() will return a null string if it has never been assigned any mode. It is a very good practice to initialize a new IRIS instance immediately after its creation.
Hi @Yuri Marx,
and today, you can add to this list :
And this slide is more than 2 years old...
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é.
.png)
You're welcome @Anup Thakkar
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-26SQL Alchemy with IRIS is a great way to simplify the bridge between IRIS and Python objects.
Thanks @Heloisa Paiva for this article and @Dmitry Maslennikov for your IRIS dialect for SQLAlchemy.
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
}
Hi @Muhammad Waseem,
same result for me.
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."
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.
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;
Thanks @Iryna Mykhailova for this brilliant article !
Very clear and cleverly presented.
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)
}
Thank you @Jeffrey Drumm
I missed the correct syntax !
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 chromadb
File "/usr/local/lib/python3.11/site-packages/chromadb/__init__.py", line 69, in <module> raise RuntimeError(
.png)
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.
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...
The solution is to use the Schemas / Users filters in the connection settings :
.png)
And adding "%" in the "to exclude" part :
.png)
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
1And 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.
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.
Thanks for this idea !
That would be a very good way to simplify the connection to IRIS from Power Apps / Power Automate.
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>
}
}.png)
.png)
Hi @Taher C,
I would suggest to create REST APIs in IRIS and consume it from PowerApps/PowerAutomate.
You'll find here some example of how to send http request from PowerAutomate :
https://www.spguides.com/rest-api-in-power-automate/
NB: Hubic is the historical name of the project at CFAO, which has no equivalence with InterSystems Ensemble or IRIS 😉
Hi @Sandeep K C
would it be possible for you to share your code in CSS.CSP.Login and IBA.CSP.Page (after cleansing of all confidential information) ?
Very good demo of Python native API in interactive mode
This app is a very good example of how to use the IRIS native Python API to run an interactive routine from a pure Python code.
Very useful.
If you want to use it by connecting to an external instance (ie: other than your local container), you'll have to install the %ZX routine as this is the function used in the rcc.py code :
ans=iris.function("","%ZX",what+" quit 0")To install %ZX routine or your remote IRIS instance, you can either execute from the Management Portal > System Explorer > SQL :
CREATE PROCEDURE %Zrcc.X()
LANGUAGE OBJECTSCRIPT
{
set %rcc=##class(%Routine).%New("%ZX.int")
set %rccline="x(%rcc) try {return $xecute(%rcc)} catch %rcc {return %rcc.DisplayString()}"
do %rcc.WriteLine(%rccline)
do %rcc.Save()
quit %rcc.Compile()
}Or simply add it from ZPM :
USER> zpm "install nacl-server"
Then from your local container, just execute rcc.py :
% docker compose exec iris python3 src/rcc.py >>> serverIP [127.0.0.1]: host.docker.internal >>> serverPORT [1972]: 51770 >>> namespace [USER]: >>> username [_SYSTEM]: >>> password [SYS]: Connected to Instance I4H on Server DEMOSERVER.EXTERNAL.COM Select Demo to exercise 0 = free ObjectScript 1 = $ZV from Server 2 = Actual Time in Server 3 = TimeZone Offset of Server 4 = Server Architecture*Vendor*Model 5 = List Global in ZWRITE style * = Terminate demo >>> take a choice [1]:
HI Muhammad,
I'm not able to run streamlit :
.png)
I've restarted the streamlit container with no success.
Any idea ?
Hi Muhammad,
thanks for this very interesting app ; it wide opens a lot of possibilities 😀
I'm trying to run it locally, but I'm facing this error :
%SYS>zn "user"
USER>zw ^ChatGPTKey
USER>set chat = ##class(dc.irisChatGPT).%New()
USER>do chat.SetAPIKey("--- my key ---")
USER>zw ^ChatGPTKey
^ChatGPTKey(1)="--- my key ---"
USER>write chat.irisDocs("Give me details of %$PIECE function with syntax")
You exceeded your current quota, please check your plan and billing details.
WRITE chat.irisDocs("Give me details of %$PIECE function with syntax")
^
<THROW> *%Exception.PythonException 230 ^^0^ <class 'UnboundLocalError'>: local variable 'ret' referenced before assignment -
USER>Then after a re-run I get the result successfully ; any idea ?
USER>write chat.irisDocs("Give me details of %$PIECE function with syntax")
The %$PIECE function is used to extract a substring from a string of text. The syntax for the %$PIECE function is %$PIECE(string, delimiter, piece_number). The string is the text from which you want to extract a substring. The delimiter is the character or characters that separate the pieces of the string. The piece_number is the number of the piece you want to extract.The docs is not always linked to IRIS :
USER>write chat.irisDocs("Give me details of $zn function with syntax")
The $zn function is a MongoDB operator that returns the index of a given value in an array. The syntax for the $zn function is: {$zn: [<array>, <value>]}.
USER>write chat.irisDocs("Give me details of zn function with syntax")
The zn function is a mathematical function that takes two arguments, x and n, and returns the remainder of x divided by n. The syntax for the zn function is zn(x, n).
USER>write chat.irisDocs("Give me details of SET function with syntax")
The SET function is a built-in function in Microsoft Excel that allows you to assign a value to a variable. The syntax for the SET function is: SET(variable, value).
USER>write chat.irisDocs("Give me details of %kill function with syntax")
The %kill function is used to terminate a SAS session. The syntax for the %kill function is %kill;
USER>write chat.irisDocs("Give me details of kill function with syntax")
The kill function is a command line utility used to terminate a process. The syntax for the kill function is: kill [signal] PID, where signal is an optional argument that specifies the signal to be sent to the process and PID is the process ID of the process to be terminated.Very efficient example showing how to export global from a simple CSP page
This application shows you how a simple CSP page can allow you to export in XML any global.
Very efficient and simple.
Thanks @Robert Cemper