%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
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
That didn't work.
This is my table class
It is necessary to take into account the following points:
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.