Command line script to get data from database table using sql
I want to have a script that can run from the usual unix, linux, or aix command line. It has to be able to get into an irissession and use set statements to get data using sql. It seems like I'm in a catch-22. When I use the irissession SERVER command at the command line, I can't run a script. When I put the irissession SERVER command in a script, it won't run anything in the script after that.
My goal for this script is to get this information and put it into a file which I can then parse.
Product version: IRIS 2020.1
from session use the SQL Shell
or
PROMPT>do $system.SQL.Shell() SQL Command Line Shell ---------------------------------------------------- The command prefix is currently set to: <<nothing>>. Enter <command>, 'q' to quit, '?' for help. [SQL]PROMPT>> << entering multiline statement mode, 'GO' to execute >> 1>>
Let me step back and work with just the two prompts that don't seem to play well together.
I run the irissession SERVER command and there I can use the set statements, one using a set and the other calling a class. This output comes to the screen. I cannot run these if I don't run the irissession command. I can't run unix commands like grep or output to a file if I do.
I'd like to be able to have a script that gets access to this data and then gets out. My script can run the irissession command but after that nothing in the script gets accessed.
while you can (clumsily) script terminal sessions, it might be easier to connect to your db via odbc and use isql, or maybe even use the newer python capabilities to get your data out via a python script
You can create a unix script like this :
Where ^RoutineWithSQL will do all the stuff to produce the SQL results (using %ResultSet, or %SQL.Statement, ...).
You can choose to capture the output in unix, or better, let the routine create a file and output the results to the file.
This solved my first issue, getting the data from a script. I want to get it into a file. My last set statement outputs using variable.%Display(). I've been searching the documentation but haven't found how to write output to a text file. It seems like I can't find the command for it, which I think would look like variable.%OutputCommand(file path).
Hi Jonathan : old-school script is easiest :
Nowadays, you can also use %File :
Set file=##class(%File).%New("file.txt") Write file.Size Do file.Open("WSN") Do file.WriteLine("This is a line of text")
If you are using %SQL.Statement, there are other methods that directly output to files :
(https://docs.intersystems.com/iris20212/csp/documatic/%25CSP.Documatic.c...)
method %Display(pDelimiter As %String = $Char(9))
• method %DisplayFormatted(pFormat As %String = -1, pFileName As %String(MAXLEN="")="", ByRef pMessages As %SQL.Manager.Messages = "", ByRef pFilesUsed As %String(MAXLEN="")=0, pTranslateTable As %String(MAXLEN="")="")
I'm having trouble getting the shell script get the output, which will be multiple lines, into a file.
Right now I have this
NAMESPACE='XXXXX'
irissession XXXXX <<EOM
znspace="${NAMESPACE}"
set mysqlstat="select * from file.Log where ConfigName='hhhhhhhhhhh"
set sr=##class(%SQL.Statement).%ExecDirect(,mysqlstat) if sr.%SQLCODE=0 do sr.%Display()
halt
EOM
This works great to get output to a screen. I tried the statements from your last reply but still get screen output, nothing to the file, and an occasional objection to my syntax. I have a lot of experience with the shell scripting but the SQL and objectscript are relatively new. It's getting the output into a flat file that is the missing link for me. Once there, I can parse, search, and format output all day.
Can you try this :
You should preferably put all the code between znspace... and halt in a routine or class method, and just call it from the script.
or try this :