Question
Andy Stobirski · Jan 16

%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 https://docs.intersystems.com/ens201815/csp/docbook/Doc.View.cls?KEY=RCOS_fzdatetime
    Set mgr.TimeFormat = 2 // format https://docs.intersystems.com/ens201815/csp/docbook/DocBook.UI.Page.cls?KEY=RCOS_fzdatetime#RCOS_fzdatetime_tformat
    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)
        quit
    } 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()
    Quit
}

 

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. 

Cheers

Andy

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

Regarding:

Set mgr.StringQuote = ""

I think it should be:

Set mgr.StringQuote = """"

Otherwise you set the StringQuote to null.

Regarding the date/time export, what's the datatype of the columns (class properties) you are exporting?

Enrico

Thanks for the reply

Set mgr.StringQuote = """"

That didn't work.

This is my table class

Class GMECC.DocmanConnect.Tables.ConnectDocs Extends %Persistent
{

    Property StatusCode As %Integer;
    Property DocumentType As %String(MAXLEN = 100);
    Property StatusLastUpdated As %DateTime;
    Property Closed as %Library.Boolean;

}

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

That did the trick! Thanks so much for taking the time to reply.

I noticed that the String Quote is only applied if the specified delimeter is contained within the text field - it universally applied.