New post
Question
· Nov 10, 2017

Best way to run a SQL script and output the result to a file

#SQL #Caché

I have a Cache-compatible sql script file and each query is separate by white space.

How could I ask Cache to execute all the queries in this file and dump the result to a specified file?

Thanks.

Discussion (2)0
Log in or sign up to continue
Robert Cemper · Nov 10, 2017

something similar:

start ;
 open infile:"R":0 else  write "input file not found",! quit
 open outfile:"WNS":0 else  write "error creating output file",! quit 
 set $Zt="end"
 for line=1:1:5 {
  use infile read sql use 0
  if $e(sql,1,6)'="SELECT" continue
  set rs=##class(%ResultSet).%New()
  set sc=rs.Prepare(sql)
  set:sc sc=rs.Execute()
  if 'sc write "bad SQL statement",! quit
  set cols=rs.GetColumnCount()
  use outfile
;; fill in headers if required
  while rs.Next() {
   for cols=1:1:cols write rs.GetData(cols),$c(9)
   write !
  }
  write !,"###",!
  use 0
 }
 
end set $ZT=""
 close infile,outfile quit
 
!! NOT TESTED !!
1 0
Eduard Lebedyuk · Nov 10, 2017

I have a Cache-compatible sql script file and each query is separate by white space.

How do you escape white spaces in a query?

 

Anyway, the general approach is:

set file = ##class(%Stream.FileCharacter).%New()
do file.LinkToFile(filename)
while 'file.AtEnd {
  set query = file.ReadLine() // ???
  set rs = ##class(%SQL.Statement).%ExecDirect(, query)
  set sc = rs.%DisplayFormatted(format, outfile)
}

Where:

  • filename - file with queries
  • format - one of  XML, HTML, PDF, TXT or CSV.
  • outfile - file to write results to

I assumed query separation by newline.

Also outfile needs to change between queries as %DisplayFormatted recreates it.

0 0