· Jan 16, 2023

%SQL.Export.Mgr not formatting exported data correctly

Hi Guys

I've been using the example code %SQL.ExportMgr taken from here (an example from the intersystems community) an am having a few problems with it.

The code I have is below, and I'm having the following problems:

  • Regardless of the dateformat property I use (e.g.  Set mgr.DateFormat = 1),  date data is always exported with format in YYYY-MM-DD
  • Regardless of the timeformat property I use (e.g.     Set mgr.TimeFormat = 2), ,  timedata is always exported with format in hh:mm;ss
  • The stringquote doesn't seem to work at all - I would expect, when it is used all text fields to be wrapped in the specified character
ClassMethod Test()
    do ..ExportTable("GMECC_DocmanConnect_Tables","ConnectDocs")

ClassMethod ExportTable(SchemaName, TableName)
    set mgr = ##class(%SQL.Export.Mgr).%New()
    Set mobj = ##class(%SQL.Manager.API).%New()
    Set ok = mobj.CheckIdentifier(.SchemaName)
    Set ok = mobj.CheckIdentifier(.TableName)
    Set classname = mobj.FindClassName(SchemaName_"."_TableName)
    Set mgr.TableName = SchemaName_"."_TableName
    Set mgr.ClassName = classname
    Set mgr.FileName = "E:\temp\"_mgr.ClassName_".txt"
    // define the export 
    Set mgr.Delimiter = ","	//$c(9) // tab
    Set mgr.StringQuote = "" // double quotes
    Set mgr.DateFormat = 1 // format
    Set mgr.TimeFormat = 2 // format
    Set mgr.TimeStampFormat = 1 // ODBC format (Import only)
    Set mgr.NoCheck = 1 // disable validation (Import only)
    Set mgr.HasHeaders = 1 // import file contains column headers
    // add the columns to export
    Do mgr.ColumnNames.Insert("Closed") 
    Do mgr.ColumnTypes.Insert("%Library.Boolean") 
    Do mgr.ColumnNames.Insert("DocumentType") 
    Do mgr.ColumnTypes.Insert("%Library.String") 
    Do mgr.ColumnNames.Insert("StatusCode") 
    Do mgr.ColumnTypes.Insert("%Library.Integer") 
    Do mgr.ColumnNames.Insert("StatusLastUpdated") 
    Do mgr.ColumnTypes.Insert("%Library.DateTime") 		
    //begin the export
    set rows = 0
    set done = 0
    set total = 0
    Set result = mgr.GenerateExportRoutine()
    If result '= 1 Write !,"Error generating export routine: ",result Quit
    Set sta = mgr.OpenExport()
    If $$$ISERR(sta) {

        w !, $SYSTEM.Status.GetErrorText(sta)
    } Else {
        Set sta = mgr.GetExportSize(.size)
        If size = 0 {
        } Else {
        Set tSC = $$$OK
        Do {
            Set tSC = mgr.ExportRows(.rows,.done)
            Set total = total + rows
            If $$$ISERR(tSC) Quit
        } While done = 0
        If $$$ISOK(tSC) {
            Set tmsg = "Completed at "_$ZDATETIME($HOROLOG)
        } Else {
            Set tmsg ="Error occurred during export."
            Set statusmsg = "Exported: "_total_" rows"
            Write !,tmsg,!,statusmsg
        Do mgr.CloseExport()
    Do mgr.DeleteExportRoutine()


Finally, not really an error as the code still works, but when I step through the code and hit the line 

" Set result = mgr.GenerateExportRoutine()" (mrg is defined at the top the TableToExport Method())

I get the following pop message, which when I clear, the code continues to execute:

"cn_iptcp://localhost:51773/GMMHTIE/%SQL.Export.Mgr.1.INT" does not exist on the Server

Any thoughts, feedback or advice would be greatfully received. 



Product version: IRIS 2021.1
$ZV: IRIS for Windows (x86-64) 2021.2.1 (Build 654U) Fri Mar 18 2022 06:09:35 EDT
Discussion (4)2
Log in or sign up to continue

It is necessary to take into account the following points:

  • according to the documentation should be
    // add the columns to export
    Do mgr.ColumnNames.Insert("Closed")
    Do mgr.ColumnTypes.Insert("N")
    Do mgr.ColumnNames.Insert("DocumentType")
    Do mgr.ColumnTypes.Insert("S")
    Do mgr.ColumnNames.Insert("StatusCode")
    Do mgr.ColumnTypes.Insert("N")
    Do mgr.ColumnNames.Insert("StatusLastUpdated")
    Do mgr.ColumnTypes.Insert("TS")
  • StringQuote only affects when escaping the corresponding characters, for example:
    • Set mgr.StringQuote $c(34) // double quotes
      a"b -> "a""b"
    • Set mgr.StringQuote $c(39) // '
      a'b -> 'a''b'
  • DateFormat and TimeFormat are applicable only for fields of type D and T, respectively, but you have a field of type TS
  • TimeStampFormat is applicable only for import, but not for export