· Aug 17, 2023

Custom task that exports query result in a csv file


I'm new to Caché systems and I have a question...

I need to develop a custom task that daily exports the result of a query to a csv file in the database directory (I use the UNIX version).

Does anyone have any code or help to solve this case?


Product version: Caché 2018.1
Discussion (2)1
Log in or sign up to continue
/// example of an extra light output to CSV 
Class dc.SQLExport Extends %CSP.Page
ClassMethod content() As %Status
  set sep=";"
  set sqlStatement="SELECT ...... FROM ....."
    ,query = ##class(%ResultSet).%New()
    ,sc = query.Prepare(sqlStatement)
  set:sc sc=query.Execute()
  quit:'sc sc
  set cols=query.GetColumnCount()
  for col=1:1:cols { if col>1 write sep
    write query.GetColumnHeader(col)
  write !
  while query.Next() {
    for col=1:1:cols { if col>1 write sep
      write query.GetData(col)
    write !
  quit $$$OK
/// filename should end with ".csv"
ClassMethod toFile(filename As %String) As %Status
  open filename:"WNS":1 
  else   quit $system.Status.Error(5005,filename)
  use filename
  set sc=..content()
  close filename 
  quit sc


You create a your custom task and schedule this task in task manager if it's required. Follow the below steps to schedule the task.

  1. Create a new class definition with extends of %SYS.Task.Definition. Override the OnTask() method and necessary logic inside the method
  2. Goto System Management portal > System Operation>Task Manager>NewTask
    1. Add unique task name
    2. Select namespace
    3. Assign the task type ( which is your class definition)
    4. Select output file when task running
    5. Provide your ouputfile.csv in the output file
    6. schedule the execution date and time.
    7. Finish the task
Class Samples.TaskMgr.SQLExportTask Extends %SYS.Task.Definition

Parameter TaskName As STRING = "ExportQueryToCSV";

Method OnTask() As %Status
    set statement = ##class(%SQL.Statement).%New()
    /// Place your sql query
    set sql = "Select Name,dob,Phone From Samples_DB.Person"
    set tSC = statement.%Prepare(sql)
    if $$$ISERR(tSC) Q $$$OK
    set result = statement.%Execute()
    #dim meta As %SQL.StatementMetadata= result.%GetMetadata()
    for i=1:1:meta.columnCount {
        if i>1 w ","
        write meta.columns.GetAt(i).colName
    write $$$NL
    while result.%Next()
        write result.Name,",",result.dob,",",result.Phone,$$$NL
    return $$$OK

Task scheduler