Question
· Mar 3, 2022

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
Discussion (10)1
Log in or sign up to continue

from session use the SQL Shell

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]PROMT>>SELECT whatever single line SQL

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.

You can create a unix script like this :

NAMESPACE='DEMO'
irissession SERVER <<EOM
znspace "${NAMESPACE}"
Do ^RoutineWithSQL
Halt
EOM

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.

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))

Display the contents of this object on the current device

• 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="")="")

Display the contents of the result object. If formatted display is available then format the results using the requested format and, if appropriate, open the formatted results using the host OS. The output is directed to one or more files and messages are placed in a result set object. All file names used are returned in an array.

Parameters

Name Description
pFormat

The format applied to the result content. This parameter is also used to determine the file name extension.

Supported formats are:

-1 %Display() format
0 XML
1 HTML
2 PDF (requires a renderer such as FOP)
99 TXT
100 CSV

If pFormat is specified as any number not listed above then it will default to TXT.

pFormat can also be specified as XML, HTML, PDF, TXT or CSV.

pFileName

The base file name to be used to generate actual file names used for output. If no value is specified then a file name will be generated, using the TEMP folder defined for the Cache instance. This value is not expected to include an extension. An extension is added to this value to form the actual file used. Also, if nested results exist then a number is appended to the file name specified to produce a unique name for each result.

pMessages

Instance of a system result set class. If no value is passed then the system message result class is instantiated. This parameter is passed by reference. It is up to the caller to process the result set oref that is returned. pMessages.Count() returns the number of messages contained in the result set. pMessages.%Display() will display the messages on the current device. pMessages.%DisplayFormatted() is also implemented and can be used to display the messages using the selected format.

pFilesUsed

This pass-by-reference parameter will contain the number of files used to display the result content and the name of each file. pFilesUsed is the number of files and pFilesUsed(file_number) is the name of the file. The sequence of the files is the same sequence as the results are processed. For simple result objects, there is a single file. For context objects that can contain result set sequences, the results are output in the order they are returned and the files used are present in pFilesUsed in that same order.

pTranslateTable

This is the translate table used for the output files when the format is CSV or TXT. This parameter is optional.

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 :

...
set mysqlstat="select * from file.Log where ConfigName='hhhhhhhhhhh"
set sr=##class(%SQL.Statement).%ExecDirect(,mysqlstat)
if sr.%SQLCODE=0 {
  set file="/tmp/temp.txt"  ;or any existing directory path + file name
  open file:"wns":1 Else  Write "could not open file",!
  If $Test {
    use file
    do sr.%Display()
    close file
  }
}
halt
EOM

You should preferably put all the code between znspace... and halt in a routine or class method, and just call it from the script.