Question
· Aug 17, 2023

Custom task that exports query result in a csv file

Hello!

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?

Thanks!!!

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

Hello @ARTHUR L A SILVA 

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