Skip to main content

%SQL.Migration.Import

class %SQL.Migration.Import extends %Library.RegisteredObject, %SQL.Migration.Util

Imports Objects from Relational Databases

Method Inventory

Methods

method Connect(dsn As %String, localuser As %String = "", isJDBC As %Boolean = 0) as %Status
Connect to the client system via an SQL Gateway Connection (ODBC or JDBC), and initializes some structures

Arguments:
  • dsn - is the name of the SQL Gateway Connection to connect to
  • localuser - obsolete
  • isJDBC - 1/0 flag. If this SQL Gateway connection is via JDBC use 1, otherwise use 0. 0 (ODBC) is the default parameter value
    • method CopyData(TbOwner As %String, TbName As %String, ScrubRoutine As %String) as %Status
    CopyData (for ODBC connection) - Copies the data from the source system to this system
  • 1 - get list of fields on source table (through gateway)
  • 2 - construct select list for source
  • 3 - construct update list for dest
  • 4 - select loop on source
    * for each, insert into destination
    * for each Blob on source:
    * while whole blob not fetched
    * fetch blob block from source
    * add blob to destination blob
  • 5 - create indices, ....or anything

    Arguments:
  • TbOwner - Name of the owner/schema of the target table
  • TbName - Name of the target table
  • ScrubRoutine - Name of a routine to call to scrub/modify the incoming data

    DATA SCRUBBING:
    For each data import process, a Routine can be speficied to modify field values prior to inserting them into InterSystems IRIS. This also allows to skip specific rows of data, preventing their insertion into InterSystems IRIS. The user-written Data scrubbing routine must provide 2 entry points.
  • Metadata(TbOwner,TbName,colNums,colSqlTypes,colStreamTypes)
  • Data(colData)

    Metadata is called once for each table to copy, to pass information about the TableOwner, TableName, ColumnNames and Datatypes. It is a good place to perform the processing that needs to be done once per table.
    Arguments:
  • TbOwner - The SQL Schema in the origin datasource
  • TbName - The SQL Table name
  • colNums- An array 1..N. One element per column in the table. colNum(i)="Column_Name"
  • colSqlTypes - An array 1..N One element per column in the table. The value is the SQL Datatype in the source Database. Values are ODBC Values as defined in %SQLMigration.inc:
    #define SQLCHAR 1
    #define SQLBIGINT -5
    #define SQLBINARY -2
    #define SQLBIT -7
    #define SQLDATE 9
    #define SQLDECIMAL 3
    #define SQLDOUBLE 8
    #define SQLFLOAT 6
    #define SQLGUID -11
    #define SQLINTEGER 4
    #define SQLLONGVARCHAR -1
    #define SQLLONGVARBINARY -4
    #define SQLNUMERIC 2
    #define SQLREAL 7
    #define SQLSMALLINT 5
    #define SQLTINYINT -6
    #define SQLTIME 10
    #define SQLTIMESTAMP 11
    #define SQLVARBINARY -3
    #define SQLVARCHAR 12
  • colStreamTypes - An array 1..N. One element per column in the table. The value is
    0: if the column data is not a stream
    1: if the column data is contained in a %CharacterStream
    2: if the column data is contained in a %BinaryStream
    Return value: none (Quit with no argument)

    Data is called once for each row fetched from the origin datasource. This function must return 1 to insert this Row, and 0 if the Row should not be inserted into InterSystems IRIS.
    Arguments:
  • colData() - Is an array 1..N of values. colData(i) is the column Data in ODBC format if the column is not a Stream (a Date is represented in the YYYY-MM-DD Format. An empty String is represented by a $c(0) character, and a NULL value is represented as ""). For BLOB columns, represented in InterSystems IRIS as Streams, it is an ID to an Open %CharacterStream or %BinaryStream as specified in the colStreamTypes array that was passed to the Metadata function. To change the value of a column 'i', the Data function can simply modify the value in colData(i). To change the value of a Blob column, the Data function can modify the Stream, or modify the Stream reference. It must return in colData(i) a $zobjint() of an oref to a Stream.
    • method CopyDataJ(TbOwner As %String, TbName As %String, ScrubRoutine As %String) as %Status
    CopyDataJ (for JDBC connection) - Copies the data from the source system to this system
  • 1 - get list of fields on source table (through gateway)
  • 2 - construct select list for source
  • 3 - construct update list for dest
  • 4 - select loop on source
    * for each, insert into destination
    * for each Blob on source:
    * while whole blob not fetched
    * fetch blob block from source
    * add blob to destination blob
  • 5 - create indices, ....or anything

    Arguments:
  • TbOwner - Name of the owner/schema of the target table
  • TbName - Name of the target table
  • ScrubRoutine - Name of a routine to call to scrub/modify the incoming data

    DATA SCRUBBING:
    For each data import process, a Routine can be speficied to modify field values prior to inserting them into InterSystems IRIS. This also allows to skip specific rows of data, preventing their insertion into InterSystems IRIS. The user-written Data scrubbing routine must provide 2 entry points.
  • Metadata(TbOwner,TbName,colNums,colSqlTypes,colStreamTypes)
  • Data(colData)

    Metadata is called once for each table to copy, to pass information about the TableOwner, TableName, ColumnNames and Datatypes. It is a good place to perform the processing that needs to be done once per table.
    Arguments:
  • TbOwner - The SQL Schema in the origin datasource
  • TbName - The SQL Table name
  • colNums- An array 1..N. One element per column in the table. colNum(i)="Column_Name"
  • colSqlTypes - An array 1..N One element per column in the table. The value is the SQL Datatype in the source Database. Values are ODBC Values as defined in %SQLMigration.inc:
    #define SQLCHAR 1
    #define SQLBIGINT -5
    #define SQLBINARY -2
    #define SQLBIT -7
    #define SQLDATE 9
    #define SQLDECIMAL 3
    #define SQLDOUBLE 8
    #define SQLFLOAT 6
    #define SQLGUID -11
    #define SQLINTEGER 4
    #define SQLLONGVARCHAR -1
    #define SQLLONGVARBINARY -4
    #define SQLNUMERIC 2
    #define SQLREAL 7
    #define SQLSMALLINT 5
    #define SQLTINYINT -6
    #define SQLTIME 10
    #define SQLTIMESTAMP 11
    #define SQLVARBINARY -3
    #define SQLVARCHAR 12
  • colStreamTypes - An array 1..N. One element per column in the table. The value is
    0: if the column data is not a stream
    1: if the column data is contained in a %CharacterStream
    2: if the column data is contained in a %BinaryStream
    Return value: none (Quit with no argument)

    Data is called once for each row fetched from the origin datasource. This function must return 1 to insert this Row, and 0 if the Row should not be inserted into InterSystems IRIS.
    Arguments:
  • colData() - Is an array 1..N of values. colData(i) is the column Data in ODBC format if the column is not a Stream (a Date is represented in the YYYY-MM-DD Format. An empty String is represented by a $c(0) character, and a NULL value is represented as ""). For BLOB columns, represented in InterSystems IRIS as Streams, it is an ID to an Open %CharacterStream or %BinaryStream as specified in the colStreamTypes array that was passed to the Metadata function. To change the value of a column 'i', the Data function can simply modify the value in colData(i). To change the value of a Blob column, the Data function can modify the Stream, or modify the Stream reference. It must return in colData(i) a $zobjint() of an oref to a Stream.
    • method CopyTableFKeys(FkTbOwner As %Library.String, FkTbName As %Library.String) as %Status
    Copy Foreign Keys from remote DB via ODBC to InterSystems IRIS for the given FK Table.
    Arguments:
  • FkTbOwner - Name of the owner/schema of the foreign key table
  • FkTbName - Name of the target table to get Foreign Keys from
    • method CopyTableFKeysJ(FkTbOwner As %Library.String, FkTbName As %Library.String, verbose As %Library.Boolean = 0) as %Status
    Copy Foreign Keys from remote DB via JDBC to InterSystems IRIS for the given FK Table.
    Arguments:
  • FkTbOwner - Name of the owner/schema of the foreign key table
  • FkTbName - Name of the target table to get Foreign Keys from
    • method CopyTableStruct(TbOwner As %String, TbName As %String, verbose As %Boolean = 0) as %Status
    Cleanup previous version of table (Table,Index,Foreign Keys), re-create table with Fields, Unique and Primary Key constraints, and Index Definitions
    Arguments:
  • TbOwner - Name of the owner/schema of the table
  • TbName - Name of the target table
  • verbose - 1/0 flag. If 1 display output to screen. Default os 0.
    • method CopyView(VOwner As %String, VName As %String) as %Status
    Copy One View and its dependencies via ODBC
    Arguments:
  • VOwner - Name of the owner/schema of the view
  • VName - Name of the target view
    • method CopyViewJ(VOwner As %String, VName As %String) as %Status
    Copy One View and its dependencies via JDBC
    Arguments:
  • VOwner - Name of the owner/schema of the view
  • VName - Name of the target view
    • method CopyViewRecursive(VOwner As %String, VName As %String, hstmt As %String)
    Find dependencies. Copy each dependency View via ODBC
    Arguments:
  • VOwner - Name of the owner/schema of the view
  • VName - Name of the target view
    • method CopyViewRecursiveJ(VOwner As %String, VName As %String)
    Find dependencies. Copy each dependency View via JDBC
    Arguments:
  • VOwner - Name of the owner/schema of the view
  • VName - Name of the target view
    • method Disconnect() as %Status
    Disconnect from the SQL Gateway (ODBC)
    method ErrorCount() as %Integer
    Returns the current number of import errors.
    method Gethdbc() as %String
    Returns Database handle for current connection ("" or 0 if not connected)

    Inherited Members

    Inherited Properties

    Inherited Methods

    FeedbackOpens in a new tab