Skip to main content

%SQL.ExImData

abstract class %SQL.ExImData

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()

Property Inventory

Method Inventory

Properties

property Charset as %String;
Character Set to use for import or export. "" is Device Default.
Property methods: CharsetDisplayToLogical(), CharsetGet(), CharsetIsValid(), CharsetLogicalToDisplay(), CharsetLogicalToOdbc(), CharsetNormalize(), CharsetSet()
property ClassName as %String (TRUNCATE = 1);
Class to use for import/export.
Property methods: ClassNameDisplayToLogical(), ClassNameGet(), ClassNameIsValid(), ClassNameLogicalToDisplay(), ClassNameLogicalToOdbc(), ClassNameNormalize(), ClassNameSet()
property ColumnNames as list of %String (TRUNCATE = 1);
Names (SQL field names) of columns.
Property methods: ColumnNamesBuildValueArray(), ColumnNamesCollectionToDisplay(), ColumnNamesCollectionToOdbc(), ColumnNamesDisplayToCollection(), ColumnNamesDisplayToLogical(), ColumnNamesGet(), ColumnNamesGetObject(), ColumnNamesGetObjectId(), ColumnNamesGetSwizzled(), ColumnNamesIsValid(), ColumnNamesLogicalToDisplay(), ColumnNamesLogicalToOdbc(), ColumnNamesNormalize(), ColumnNamesOdbcToCollection(), ColumnNamesSet(), ColumnNamesSetObject(), ColumnNamesSetObjectId()
property ColumnTypes as list of %String (TRUNCATE = 1);
Data types of columns.

  • D - Date
  • TS - TimeStamp
  • N - Numeric
  • S - String
  • T - Time
Property methods: ColumnTypesBuildValueArray(), ColumnTypesCollectionToDisplay(), ColumnTypesCollectionToOdbc(), ColumnTypesDisplayToCollection(), ColumnTypesDisplayToLogical(), ColumnTypesGet(), ColumnTypesGetObject(), ColumnTypesGetObjectId(), ColumnTypesGetSwizzled(), ColumnTypesIsValid(), ColumnTypesLogicalToDisplay(), ColumnTypesLogicalToOdbc(), ColumnTypesNormalize(), ColumnTypesOdbcToCollection(), ColumnTypesSet(), ColumnTypesSetObject(), ColumnTypesSetObjectId()
property ColumnWidths as list of %Integer;
Widths of columns for FixedWidth files.
Property methods: ColumnWidthsBuildValueArray(), ColumnWidthsCollectionToDisplay(), ColumnWidthsCollectionToOdbc(), ColumnWidthsDisplayToCollection(), ColumnWidthsDisplayToLogical(), ColumnWidthsGet(), ColumnWidthsGetObject(), ColumnWidthsGetObjectId(), ColumnWidthsGetSwizzled(), ColumnWidthsIsValid(), ColumnWidthsLogicalToDisplay(), ColumnWidthsNormalize(), ColumnWidthsOdbcToCollection(), ColumnWidthsSet(), ColumnWidthsSetObject(), ColumnWidthsSetObjectId()
property DateFormat as %Integer;
Format for dates (passed to $ZDT).
Property methods: DateFormatDisplayToLogical(), DateFormatGet(), DateFormatIsValid(), DateFormatLogicalToDisplay(), DateFormatNormalize(), DateFormatSet()
property Delimiter as %String (TRUNCATE = 1);
Delimiter character or NULL for fixed width fields.
Property methods: DelimiterDisplayToLogical(), DelimiterGet(), DelimiterIsValid(), DelimiterLogicalToDisplay(), DelimiterLogicalToOdbc(), DelimiterNormalize(), DelimiterSet()
property FileName as %String (TRUNCATE = 1);
File (on server) to use for import or export."
Property methods: FileNameDisplayToLogical(), FileNameGet(), FileNameIsValid(), FileNameLogicalToDisplay(), FileNameLogicalToOdbc(), FileNameNormalize(), FileNameSet()
property HasHeaders as %Integer [ InitialExpression = 0 ];
If true, file has column headers in first row.
Property methods: HasHeadersDisplayToLogical(), HasHeadersGet(), HasHeadersIsValid(), HasHeadersLogicalToDisplay(), HasHeadersNormalize(), HasHeadersSet()
property IQN as %String (TRUNCATE = 1);
Internal Qualified Table Name. $$$BuildIQN(SchemaName,TableName) or SchemaName_"."_$Translate(Tablename,".",$c(2))
Property methods: IQNDisplayToLogical(), IQNGet(), IQNIsValid(), IQNLogicalToDisplay(), IQNLogicalToOdbc(), IQNNormalize(), IQNSet()
property NoCheck as %Integer [ InitialExpression = 0 ];
If true, import will insert with %NOCHECK.
Property methods: NoCheckDisplayToLogical(), NoCheckGet(), NoCheckIsValid(), NoCheckLogicalToDisplay(), NoCheckNormalize(), NoCheckSet()
property StringQuote as %String (TRUNCATE = 1);
Character used to quote strings or NULL for no quoting.
Property methods: StringQuoteDisplayToLogical(), StringQuoteGet(), StringQuoteIsValid(), StringQuoteLogicalToDisplay(), StringQuoteLogicalToOdbc(), StringQuoteNormalize(), StringQuoteSet()
property TableName as %String (TRUNCATE = 1);
Table to use for import/export.
Property methods: TableNameDisplayToLogical(), TableNameGet(), TableNameIsValid(), TableNameLogicalToDisplay(), TableNameLogicalToOdbc(), TableNameNormalize(), TableNameSet()
property Terminator as %String (TRUNCATE = 1);
User defined record terminators (upto 8 charectors).
Property methods: TerminatorDisplayToLogical(), TerminatorGet(), TerminatorIsValid(), TerminatorLogicalToDisplay(), TerminatorLogicalToOdbc(), TerminatorNormalize(), TerminatorSet()
property TimeFormat as %Integer;
Format for time (passed to $ZTime).
Property methods: TimeFormatDisplayToLogical(), TimeFormatGet(), TimeFormatIsValid(), TimeFormatLogicalToDisplay(), TimeFormatNormalize(), TimeFormatSet()
property TimeStampFormat as %Integer;
Format for datetime values (ODBC or T-SQL).
Property methods: TimeStampFormatDisplayToLogical(), TimeStampFormatGet(), TimeStampFormatIsValid(), TimeStampFormatLogicalToDisplay(), TimeStampFormatNormalize(), TimeStampFormatSet()

Methods

method ResetExImData()
Reset the export/import data.

Subclasses

FeedbackOpens in a new tab