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 Caché. This also allows to skip specific rows of data, preventing their insertion into Caché. 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 Caché.
    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 Caché 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 Caché. This also allows to skip specific rows of data, preventing their insertion into Caché. 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 Caché.
    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 Caché 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 Cache 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 Cache 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