· Dec 19, 2019

Query result save to csv

Query result save to csv

Discussion (6)0
Log in or sign up to continue

This code will output arbitrary query to CSV.

/// w $System.Status.GetErrorText(##class(!!!).ToCSV())
ClassMethod ToCSV(file = {##class(%File).NormalizeDirectory(##class(%SYS.System).TempDirectory())_ "out"}, query As %String = "SELECT 1,2,'a'", args...) As %Status
    #dim sc As %Status = $$$OK
    // Cant't do $zcvt($e(file,*-4,*), "l") as it can bring unexpected effect on case-sensitive fs
    // Possible solution is to make file byref but it should be done in application code
    set:$e(file,*-4,*)=".csv" file = $e(file, 1, *-4)

    set dir = ##class(%File).GetDirectory(file)
    set exists = ##class(%File).DirectoryExists(dir)
    if (exists=$$$NO) {
        set success = ##class(%File).CreateDirectoryChain(dir, .code)
        set:success=$$$NO sc = $$$ERROR($$$GeneralError, "Unable to create directory: '" _ dir _ "', reason: " _ code)
    quit:$$$ISERR(sc) sc

    #dim rs As %SQL.StatementResult
    set rs = ##class(%SQL.Statement).%ExecDirect(,query, args...)
    if rs.%SQLCODE=0 {
       do rs.%DisplayFormatted("CSV", file)
    } else {
        set sc = $$$ERROR($$$SQLError, rs.%SQLCODE, rs.%Message)
    quit sc

There are two options:

  1. Use SQL Gateway to create Linked Tabled from Oracle (if there are a lot of tables it might be better to create a linkage with a view). After that query Linked Table same as any normal InterSystems IRIS table using the code snippet above.
  2. Write a service with the EnsLib.SQL.InboundAdapter to query Oracle database. In that case you need to implement a CSV writer.