Awesome, how do you position this tool compared to irissqlcli if it had a web version?

I love it <3, for the more you are using the community driver DB-API :)

Wow, what a useful thread! Thank you.

I especially like the fact that you offer solution in each IRIS language : SQL, Python, ObjectScript.

SELECT {fn WEEK('2004-02-25')} AS Week, {fn DAY('2004-02-25')} as Day,{fn DAYOFWEEK('2004-02-25')} as DayOfWeek,{fn Year('2004-02-25')} as Year,{fn Quarter('2004-02-25')} as Quarter,{fn DAYOFYEAR('2004-02-25')} as DayOfYear

Doc : https://docs.intersystems.com/iris20223/csp/documatic/%25CSP.Documatic.c...

not all your requirements but it's a start

Great article, if your are looking for an approach without objectscript and making use of "irispyhton", check this code :

python code :

import pandas as pd

from sqlalchemy import create_engine,types

engine = create_engine('iris+emb:///')

df = pd.read_csv("/irisdev/app/notebook/Notebooks/date_panda.csv")
# change type of FullDate to date
df['FullDate'] = pd.to_datetime(df['FullDate'])
df.head()

df.to_sql('DateFact', engine, schema="Demo" ,if_exists='replace', index=True,
        dtype={'DayName': types.VARCHAR(50), 'FullDate': types.DATE, 'MonthName': types.VARCHAR(50),
        'MonthYear': types.INTEGER, 'Year': types.INTEGER})

requirements.txt :

pandas
sqlalchemy==1.4.22
sqlalchemy-iris==0.5.0
irissqlcli

date_panda.csv

ID,DayName,FullDate,MonthName,MonthYear,Year
1,Monday,1900-01-01,January,190001,1900
2,Tuesday,1900-01-02,January,190001,1900
3,Wednesday,1900-01-03,January,190001,1900
4,Thursday,1900-01-04,January,190001,1900
5,Friday,1900-01-05,January,190001,1900
6,Saturday,1900-01-06,January,190001,1900
7,Sunday,1900-01-07,January,190001,1900
8,Monday,1900-01-08,January,190001,1900
9,Tuesday,1900-01-09,January,190001,1900

Thank for the feedback.

I think I will continue to use my sunglasses to check the community at night for a while

Hi @Raj Singh , it does support float even is the readme is not up to date.

If you are looking for an objectscript / embedded python approach Alex Woodhead solution will fit you well.

If you are looking for an python only approach my module may help you.

Sure you can, for example to retrieve all the patient with an observation based on the code : 8302-2

[FHIR EndPoint]/Patient?_has:Observation:patient:code=8302-2

More info here : https://www.hl7.org/fhir/search.html#has

For the python part have a look at this git :

https://github.com/LucasEnard/fhir-client-python

It's a simple example how to use python client to interact with a FHIR server.

For fhir-py to create the same query use raw search :

https://github.com/grongierisc/fhir-py#raw-parameters

Last but not least, check out our neat implementation of SQL On FHIR aka FHIR SQL Builder

Demo here : https://github.com/grongierisc/iris-fhirsqlbuilder

How to setup a secure connection :

1. Generate a key pair :

openssl req -x509 -nodes -days 1 -newkey rsa:2048 -subj /CN=* -keyout /irisdev/app/server.key -out /irisdev/app/server.crt

2. Configure IRIS super server with SSL/TLS :

https://docs.intersystems.com/iris20223/csp/docbook/Doc.View.cls?KEY=GTL...

3. Use certificate to connect :

python3 -m irissqlcli iris://SuperUser:SYS@localhost:33782/USER -c server.crt

If I understand correctly, the trick is to load the "src/gbl/SYS.xml" file that holds the configuration of git-source-control module.

If we create a new file like a BP or a DTL, we still have to add it to the source control module with the UI.
Then, if we do so, we have to update the "src/gbl/SYS.xml" file with the new file.

Am I right ?

Great post thanks.

If you want to go further check this git :

• https://github.com/grongierisc/kong-ee-training

I'm not tottaly agree with you.

Imagine you are new to iris, you just wish to play with our SQL engine.

With your proposal, you have to know how to connect to a terminal and know the magic command (zzq, BTW i was not aware of this one) to run a query.

Then, you can't do that remotely or with out SSH.

To finish, with your example, you don't show us how to do it from a Shell.

In my point of view, this app is a game changer.

Let me explain why.

Imagine you have an DDL statement that you want to execute on a database.
Example:

File: misc/init.sql

CREATE TABLE test.formation (
    name varchar(50) NULL,
    room varchar(50) NULL
);

INSERT INTO test.formation
(name, room)
VALUES('formation1', 'salle1');

You can execute this statement in the irissqlcli app by using the following command:

irissqlcli iris://_SYSTEM@localhost:51776/USER -W < misc/init.sql

Before this app, you had to use the terminal to execute this command.

cat <<EOF | iris session iris

do \$SYSTEM.SQL.Schema.ImportDDL("/irisdev/app/misc/init.sql",.log)
if log'="" { write "Error: ",log }
h
EOF

Let's compare the two commands:

One last thing, if I want to extract the data from a table, I can use the following command:

irissqlcli iris://_SYSTEM@localhost:51776/USER -W -e "SELECT * FROM test.formation" --csv

This command will return the following result:

"name","room"
"formation1","salle1"

Try to do the same thing with the terminal ;).

cat <<EOF | iris session iris
do ##class(%SQL.Statement).%ExecDirect(,"SELECT * FROM test.formation").%DisplayFormatted("CSV",,,.filesUsed)
do ##class(%Stream.FileCharacter).%OpenId(filesUsed(1)).OutputToDevice()
h
EOF

Cool, this can be really useful while waiting for OpenTelemetry and SAM.

The big advantage of this, is that you don't need other tool/server/docker.