%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
"
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