New post
Article
· May 16, 2024 1m read

How to run SQL in terminal

#Tips & Tricks #Caché #Ensemble #InterSystems IRIS #InterSystems IRIS for Health

InterSystems FAQ rubric

Too run SQL in Terminal use $system.SQL.Shell().

Please refer to the sample below.

SAMPLES>do $System.SQL.Shell()
SQL Command Line Shell
---------------------------------------------------- The command prefix is currently set to: <>.
Enter q to quit, ? for help.
SAMPLES>>select * from Sample.Vendor
1. select * from Sample.Vendor Vendor Balance Contact DaysClear DiscDays DiscRate LastInvDate LastPayDate MinPayment Name NetDays PayFlag TaxReportingAddress_City Address_State Address_Street Address_Zip
： *If you press Enter without entering anything on the first line, it will switch to multi-line input mode.
USER>d $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
Enter q to quit, ? for help.
USER>> << entering multiline statement mode, 'GO' to execute >>
1>>set transaction isolation level read committed
2>>go

For details, please refer to the document below.

How to use SQLShell [IRIS]
How to use SQLShell

Discussion (1)2
Log in or sign up to continue
Sylvain Guilbaud · Feb 18

You can also use the ":sql" alias to launch the SQL Shell :

USER>:sql
SQL Command Line Shell
----------------------------------------------------

The command prefix is currently set to: <<nothing>>.
Enter <command>, 'q' to quit, '?' for help.
[SQL]USER>>select sysdate
2.      select sysdate

| Expression_1 |
| -- |
| 2025-02-18 13:39:24 |

1 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0928s/35,642/155,490/1ms
          execute time(s)/globals/cmds/disk: 0.0001s/3/408/0ms
                                query class: %sqlcq.USER.cls6
---------------------------------------------------------------------------
[SQL]USER>>quit

USER>

And just type ":?" to get the list of all the alias :

USER>:?
 :<number>    Recall command # <number>
 :py          Do $system.Python.Shell()
 :mdx         Do $system.DeepSee.Shell()
 :sql         Do $system.SQL.Shell()
 :tsql        Do $system.SQL.TSQLShell()
 :alias       Create/display aliases
 :unalias     Remove aliases
 :history     Display command history
 :clear       Clear history buffer
 :?           Display help
 Ctrl+R       Reverse incremental search

USER>
2 0