|
IRISLIB database
|
|
|
IRISLIB database
|
|
Common data used by both SQL import and export classes. More...
Public Member Functions | |
| ResetExImData () | |
| Common data used by both SQL import and export classes. More... | |
Public Attributes | |
| Charset | |
| Character Set to use for import or export. More... | |
| ClassName | |
| Class to use for import/export. More... | |
| ColumnNames | |
| Names (SQL field names) of columns. More... | |
| ColumnTypes | |
| Data types of columns. More... | |
| ColumnWidths | |
| Widths of columns for FixedWidth files. More... | |
| DateFormat | |
| Format for dates (passed to $ZDT). More... | |
| Delimiter | |
| Delimiter character or NULL for fixed width fields. More... | |
| FileName | |
| File (on server) to use for import or export. More... | |
| HasHeaders | |
| If true, file has column headers in first row. More... | |
| IQN | |
| Internal Qualified Table Name. More... | |
| NoCheck | |
| If true, import will insert with NOCHECK. More... | |
| StringQuote | |
| Character used to quote strings or NULL for no quoting. More... | |
| TableName | |
| Table to use for import/export. More... | |
| Terminator | |
| User defined record terminators (upto 8 charectors). More... | |
| TimeFormat | |
| Format for time (passed to $ZTime). More... | |
| TimeStampFormat | |
| Format for datetime values (ODBC or T-SQL). More... | |
Private Attributes | |
| __RoutineName | |
| Name of generated routine. More... | |
Common data used by both SQL import and export classes.
Example of using the import/export classes:
If Wizard = "Export" { set mgr = ##class(SQL.Export.Mgr).New() }
Else { Set mgr = ##class(SQL.Import.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.FileName = "c:\export.txt"
Set mgr.TableName = SchemaName_"."_TableName
Set mgr.IQN=$$$BuildIQN(SchemaName,TableName) <-- $$$BuildIQN is defined in msql.INC
Set mgr.ClassName = classname
Set mgr.Delimiter = $c(9) <-- tab
Set mgr.StringQuote = "" <-- double quotes
Set mgr.DateFormat = 1 <-- MM/DD/{YY}YY
Set mgr.TimeFormat = 1 <-- hh:mm:ss
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
Do mgr.ColumnNames.Insert(colname) <-- insert a column name
Do mgr.ColumnTypes.Insert(datatype) <-- insert a column datatype
For Export:
Set result = mgr.GenerateExportRoutine()
If result '= 1 { Write !,"Error generating export routine: ",result Quit }
Set sta = mgr.OpenExport()
If $$$ISERR(sta) {
Set ErrMsg = "Unable to open export file"
[...LogYourError here ]
} Else {
Set sta = mgr.GetExportSize(.size)
If size = 0 {
[...LogYourError here "No data to export"]
} Else {
Set tSC = $$$OK
Do {
Set tSC = mgr.ExportRows(.rows,.done)
Set total = total + rows
If $$$ISERR(tSC) [Do ..LogYourError here] Quit
} While done = 0
If $$$ISOK(tSC) {
Set tmsg = "Completed at "_$zdt($h)
} Else {
Set tmsg ="Error occurred during export."
}
Set statusmsg = "Exported: "_total_" rows"
Write !,tmsg,!,statusmsg
}
Do mgr.CloseExport()
}
Do mgr.DeleteExportRoutine()
For Import:
Set mgr.DeferIndices = 1
Set result = mgr.GenerateImportRoutine()
If result '= 1 { Write !,"Error generating import routine: ",result Quit }
Set sta = mgr.OpenImport()
If $$$ISERR(sta) {
Set ErrMsg = "Unable to open import file"
[...LogYourError here ]
} Else {
If mgr.HasHeaders = 1 { Set sta = mgr.ReadHeader(.header,.size) }
Set tSC = $$$OK
Do {
Set tSC = mgr.ImportRows(.rows,.inserted,.bytes,.done,.numerr,total)
Set total = total + rows
Set totalinserted = totalinserted + inserted
Set totalbytes = totalbytes + bytes
If $$$ISERR(tSC) [...LogYourError here ] Quit
} While done = 0
Do mgr.BuildIndices()
If mgr.ErrorCount() > 0 {
[...LogYourError here ]
[number of error count is mgr.ErrorCount()
} Else {
If $$$ISOK(tSC) {
Set tmsg = "Completed at "_$zdt($h)
} Else {
Set tmsg ="Error occurred during import."
}
Set statusmsg = "Imported: "_totalinserted_" rows"
Write !,tmsg,!,statusmsg
}
Do mgr.CloseImport(0)
}
Do mgr.DeleteImportRoutine()
| ResetExImData | ( | ) |
Common data used by both SQL import and export classes.
Example of using the import/export classes:
If Wizard = "Export" { set mgr = ##class(SQL.Export.Mgr).New() }
Else { Set mgr = ##class(SQL.Import.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.FileName = "c:\export.txt"
Set mgr.TableName = SchemaName_"."_TableName
Set mgr.IQN=$$$BuildIQN(SchemaName,TableName) <-- $$$BuildIQN is defined in msql.INC
Set mgr.ClassName = classname
Set mgr.Delimiter = $c(9) <-- tab
Set mgr.StringQuote = "" <-- double quotes
Set mgr.DateFormat = 1 <-- MM/DD/{YY}YY
Set mgr.TimeFormat = 1 <-- hh:mm:ss
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
Do mgr.ColumnNames.Insert(colname) <-- insert a column name
Do mgr.ColumnTypes.Insert(datatype) <-- insert a column datatype
For Export:
Set result = mgr.GenerateExportRoutine()
If result '= 1 { Write !,"Error generating export routine: ",result Quit }
Set sta = mgr.OpenExport()
If $$$ISERR(sta) {
Set ErrMsg = "Unable to open export file"
[...LogYourError here ]
} Else {
Set sta = mgr.GetExportSize(.size)
If size = 0 {
[...LogYourError here "No data to export"]
} Else {
Set tSC = $$$OK
Do {
Set tSC = mgr.ExportRows(.rows,.done)
Set total = total + rows
If $$$ISERR(tSC) [Do ..LogYourError here] Quit
} While done = 0
If $$$ISOK(tSC) {
Set tmsg = "Completed at "_$zdt($h)
} Else {
Set tmsg ="Error occurred during export."
}
Set statusmsg = "Exported: "_total_" rows"
Write !,tmsg,!,statusmsg
}
Do mgr.CloseExport()
}
Do mgr.DeleteExportRoutine()
For Import:
Set mgr.DeferIndices = 1
Set result = mgr.GenerateImportRoutine()
If result '= 1 { Write !,"Error generating import routine: ",result Quit }
Set sta = mgr.OpenImport()
If $$$ISERR(sta) {
Set ErrMsg = "Unable to open import file"
[...LogYourError here ]
} Else {
If mgr.HasHeaders = 1 { Set sta = mgr.ReadHeader(.header,.size) }
Set tSC = $$$OK
Do {
Set tSC = mgr.ImportRows(.rows,.inserted,.bytes,.done,.numerr,total)
Set total = total + rows
Set totalinserted = totalinserted + inserted
Set totalbytes = totalbytes + bytes
If $$$ISERR(tSC) [...LogYourError here ] Quit
} While done = 0
Do mgr.BuildIndices()
If mgr.ErrorCount() > 0 {
[...LogYourError here ]
[number of error count is mgr.ErrorCount()
} Else {
If $$$ISOK(tSC) {
Set tmsg = "Completed at "_$zdt($h)
} Else {
Set tmsg ="Error occurred during import."
}
Set statusmsg = "Imported: "_totalinserted_" rows"
Write !,tmsg,!,statusmsg
}
Do mgr.CloseImport(0)
}
Do mgr.DeleteImportRoutine()
Reset the export/import data.
| Charset |
Character Set to use for import or export.
"" is Device Default.
| ClassName |
Class to use for import/export.
| ColumnNames |
Names (SQL field names) of columns.
| ColumnTypes |
Data types of columns.
| ColumnWidths |
Widths of columns for FixedWidth files.
| DateFormat |
Format for dates (passed to $ZDT).
| Delimiter |
Delimiter character or NULL for fixed width fields.
| FileName |
File (on server) to use for import or export.
"
| HasHeaders |
If true, file has column headers in first row.
| IQN |
Internal Qualified Table Name.
$$$BuildIQN(SchemaName,TableName) or SchemaName_"."_$Translate(Tablename,".",$c(2))
| NoCheck |
If true, import will insert with NOCHECK.
|
private |
Name of generated routine.
| StringQuote |
Character used to quote strings or NULL for no quoting.
| TableName |
Table to use for import/export.
| Terminator |
User defined record terminators (upto 8 charectors).
| TimeFormat |
Format for time (passed to $ZTime).
| TimeStampFormat |
Format for datetime values (ODBC or T-SQL).
1.9.1