Written by

CJ H

Question CJ H · Nov 10, 2017

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

#Caché #SQL

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

Add reply

Comments

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

0 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